public DataTable ConvertDataReaderToDataTable(DataTableReader dataReader)
{
DataTable dataTable = new DataTable();
for (int i = 0; i < dataReader.FieldCount; i++)
{
DataColumn mydc = new DataColumn();
mydc.DataType = dataReader.GetFieldType(i);
mydc.ColumnName = dataReader.GetName(i);
dataTable.Columns.Add(mydc);
}
while (dataReader.Read())
{
DataRow mydr = dataTable.NewRow();
for (int i = 0; i < dataReader.FieldCount; i++)
{
mydr[i] = dataReader[i].ToString();
}
dataTable.Rows.Add(mydr);
mydr = null;
}
dataReader.Close();
return(dataTable);
}
protected override IEnumerable <IDataObject> GetDataInternal()
{
var str = ConnectionString ?? MapConfig.Current.Variables["ConnectionString"].ToString();
using (var connection = new SqlConnection(str))
{
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = new SqlCommand(ActualQuery, connection);
DataSet dataset = new DataSet();
adapter.Fill(dataset);
using (DataTableReader reader = dataset.CreateDataReader())
{
while (reader.Read())
{
DataObject result = new DataObject();
for (int fieldIndex = 0; fieldIndex < reader.FieldCount; fieldIndex++)
{
result.SetValue(reader.GetName(fieldIndex), reader.GetValue(fieldIndex));
}
yield return(result);
}
}
}
}
private void retornaDetalhesPedidoXML(int id)
{
try
{
VendasDAO dao = new VendasDAO();
DataTable dt = dao.buscarPedido(id);
DataTableReader dtr = dt.CreateDataReader();
Response.ContentType = "text/xml";
XmlWriter xPedido = new XmlTextWriter(Response.OutputStream, Encoding.GetEncoding("ISO-8859-1"));
xPedido.WriteStartDocument();
xPedido.WriteStartElement("DETALHES");
while (dtr.Read())
{
xPedido.WriteStartElement("DETALHE");
for (int campo = 0; campo < dtr.FieldCount; campo++)
{
xPedido.WriteElementString(
dtr.GetName(campo),
dtr[campo].ToString());
}
xPedido.WriteEndElement();
}
xPedido.WriteEndElement();
xPedido.Close();
}
catch (Exception ex)
{
Response.Clear();
Response.Write(string.Format("{{\"erro\":\"{0}\"}}", ex.Message));
}
}
// <Snippet1>
private void TestGetFieldType(DataTableReader reader)
{
for (int i = 0; i < reader.FieldCount; i++)
{
Console.WriteLine(reader.GetName(i) + ":" +
reader.GetFieldType(i).FullName);
}
}
// <Snippet1>
private static void DisplayColumnNames(DataTableReader reader)
{
// Given a DataTableReader, display column names.
for (int i = 0; i < reader.FieldCount; i++)
{
Console.WriteLine("{0}: {1}", i, reader.GetName(i));
}
}
/// <summary>
/// 將傳入的datatable 對應到傳入的型別上，執行完回傳T，裡面是已經對應好並轉型完成的型別
/// </summary>
/// <typeparam name="T">Model class</typeparam>
/// <param name="dt">要對應的表格</param>
/// <returns>回傳對應好的 T </returns>
public static T DataTableRefToOne <T>(DataTable dt) where T : new()
{
DataTableReader dtr = new DataTableReader(dt);
T item = new T();
while (dtr.Read())
{
for (int i = 0; i < dtr.FieldCount; i++)
{
PropertyInfo property = item.GetType().GetProperty(dtr.GetName(i));
SetPropertyValue(item, dtr[dtr.GetName(i)].ToString(), property);
}
}
return(item);
}
public void GetNameTest()
{
DataTableReader rdr = dt.CreateDataReader();
for (int i = 0; i < dt.Columns.Count; ++i)
{
Assert.AreEqual(dt.Columns[i].ColumnName, rdr.GetName(i), "#1_" + i);
}
}
public void GetNameTest()
{
DataTableReader rdr = _dt.CreateDataReader();
for (int i = 0; i < _dt.Columns.Count; ++i)
{
Assert.Equal(_dt.Columns[i].ColumnName, rdr.GetName(i));
}
}
/// <summary>
/// Extension <see cref="DataTableReader"/>. Détermine si une colonne existe dans un jeu de données.
/// </summary>
/// <remarks>Des faux positifs peuvent se produire pour certaines langues (voir la documentation de la classe <see cref="DataTableReader"/> pour plus de détails).</remarks>
/// <param name="reader">Le jeu de données SQL à vérifier.</param>
/// <param name="columnName">Le nom de la colonne à vérifier.</param>
/// <returns>Vrai si la colonne existe, Faux si elle n'existe pas.</returns>
public static bool ColumnExists(this DataTableReader reader, string columnName)
{
for (int i = 0; i < reader.FieldCount; i++)
{
if (reader.GetName(i).Equals(columnName, StringComparison.InvariantCultureIgnoreCase))
{
return(true);
}
}
return(false);
}
public static string ToJson(this DataTableReader obj)
{
var sb = new StringBuilder();
sb.Append("[");
var isFirstRow = true;
while (obj.Read())
{
if (!isFirstRow)
{
sb.Append(",");
}
sb.Append("{");
for (var i = 0; i < obj.FieldCount; i++)
{
if (obj.GetValue(i) == null)
{
continue;
}
if (obj.GetValue(i) is DBNull)
{
continue;
}
if (i > 0)
{
sb.Append(", ");
}
sb.Append("\"" + obj.GetName(i) + "\":");
if (obj.GetValue(i).GetType().Name == "DateTime")
{
sb.Append("\"" + obj.GetDateTime(i).ToString("o") + "\"");
}
else
{
sb.Append(CleanupJsonData(obj.GetValue(i).ToString()));
}
}
sb.Append("}");
isFirstRow = false;
}
sb.Append("]");
return(sb.ToString());
}
// <Snippet1>
private static void DisplayItems(DataTableReader reader)
{
int rowNumber = 0;
while (reader.Read())
{
Console.WriteLine("Row " + rowNumber);
for (int i = 0; i < reader.FieldCount; i++)
{
Console.WriteLine("{0}: {1}", reader.GetName(i), reader[i]);
}
rowNumber++;
}
}
public static Func <DataTableReader, T> GetReader <T>(this DataTableReader reader)
{
Delegate resDelegate;
List <string> readerColumns = new List <string>();
for (int index = 0; index <= reader.FieldCount - 1; index++)
{
readerColumns.Add(reader.GetName(index));
}
var readerParam = Expression.Parameter(typeof(DataTableReader), "reader");
// var readerGetValue = reader.GetType().GetMethod("GetValue");
var readerGetValue = typeof(DataTableReader).GetMethod("GetValue");
var dbNullValue = typeof(System.DBNull).GetField("Value");
var dbNullExp = Expression.Field(null, dbNullValue);
List <MemberBinding> memberBindings = new List <MemberBinding>();
foreach (var prop in typeof(T).GetProperties())
{
object defaultValue = null;
if (prop.PropertyType.IsValueType)
{
defaultValue = Activator.CreateInstance(prop.PropertyType);
}
else if (prop.PropertyType.Name.ToLower().Equals("string"))
{
defaultValue = string.Empty;
}
if (readerColumns.Contains(prop.Name))
{
var indexExpression = Expression.Constant(reader.GetOrdinal(prop.Name));
var getValueExp = Expression.Call(readerParam, readerGetValue, new Expression[] { indexExpression });
var testExp = Expression.NotEqual(dbNullExp, getValueExp);
var ifTrue = Expression.Convert(getValueExp, prop.PropertyType);
var ifFalse = Expression.Convert(Expression.Constant(defaultValue), prop.PropertyType);
MemberInfo mi = typeof(T).GetMember(prop.Name)[0];
MemberBinding mb = Expression.Bind(mi, Expression.Condition(testExp, ifTrue, ifFalse));
memberBindings.Add(mb);
}
}
var newItem = Expression.New(typeof(T));
var memberInit = Expression.MemberInit(newItem, memberBindings);
var lambda = Expression.Lambda <Func <DataTableReader, T> >(memberInit, new ParameterExpression[] { readerParam });
resDelegate = lambda.Compile();
return((Func <DataTableReader, T>)resDelegate);
}
//protected System.Collections.Hashtable buildHeadersOrder(List<string> headersStrings, DataTable dt, int firstRowIndex)
// {
// try
// {
// System.Collections.Hashtable ret = new System.Collections.Hashtable();
// for (int i = 0; i < headersStrings.Count; i++)
// {
// for (int j = 0; j < dt.Columns.Count; j++)
// {
// if (dt.Rows[8][j].ToString().Equals(headersStrings[i]))
// {
// ret.Add(i, j);
// }
// }
// }
// return ret;
// }
// catch (Exception ex)
// {
// return null;
// }
// }
private static void PrintTable(DataTable dt)
{
DataTableReader dtReader = dt.CreateDataReader();
while (dtReader.Read())
{
for (int i = 0; i < dtReader.FieldCount; i++)
{
MyLogger.Instance.Write(
dtReader.GetName(i).Trim() + " . "
+ dtReader.GetValue(i).ToString().Trim());
}
}
dtReader.Close();
}
// <Snippet1>
private static void TestGetTypeName()
{
DataTable table = GetCustomers();
using (DataTableReader reader = new DataTableReader(table))
{
for (int i = 0; i < reader.FieldCount; i++)
{
Console.WriteLine("{0}: {1}", reader.GetName(i),
reader.GetDataTypeName(i));
}
}
Console.WriteLine("Press Enter to finish.");
Console.ReadLine();
}
private static void PrintTable(DataTable dt)
{
DataTableReader dtReader = dt.CreateDataReader();
while (dtReader.Read())
{
for (int i = 0; i < dtReader.FieldCount; i++)
{
Console.Write("{0} = {1} ",
dtReader.GetName(i).Trim(),
dtReader.GetValue(i).ToString().Trim());
}
Console.WriteLine();
}
dtReader.Close();
}
private static string PrintTable(DataTable dt)
{
DataTableReader dtReader = dt.CreateDataReader();
StringBuilder result = new StringBuilder();
while (dtReader.Read())
{
for (int i = 0; i < (dtReader.FieldCount > 20 ? 20 : dtReader.FieldCount); i++)
{
result.AppendFormat("{0}: {1}",
dtReader.GetName(i).Trim(),
dtReader.GetValue(i).ToString().Trim());
result.AppendLine();
}
result.AppendLine();
}
dtReader.Close();
return(result.ToString());
}
private static void ListInventory(SqlDataAdapter dAdapter)
{
// Now, get the new .NET 2.0 DataTableReader type.
DataTableReader dtReader =
dsCarInventory.Tables["Inventory"].CreateDataReader();
// The DataTableReader works just like the DataReader.
while (dtReader.Read())
{
for (int i = 0; i < dtReader.FieldCount; i++)
{
Console.Write("{0} = {1} ",
dtReader.GetName(i).Trim(),
dtReader.GetValue(i).ToString().Trim());
}
Console.WriteLine();
}
dtReader.Close();
}
private static void PrintTable(DataTable dt)
{
Console.WriteLine("\n***** Rows in DataTable *****");
// Now, get the new .NET 2.0 DataTableReader type.
DataTableReader dtReader = dt.CreateDataReader();
// The DataTableReader works just like the DataReader.
while (dtReader.Read())
{
for (int i = 0; i < dtReader.FieldCount; i++)
{
Console.Write("{0} = {1} ",
dtReader.GetName(i).Trim(),
dtReader.GetValue(i).ToString().Trim());
}
Console.WriteLine();
}
dtReader.Close();
}
/// <summary>
/// Extension <see cref="DataTableReader"/>. Convertit un jeu de données SQL en un dictionnaire de valeurs typées.
/// Les colonnes n'étant pas du type spécifié (ou d'une classe fille) ne sont pas retournées.
/// </summary>
/// <typeparam name="T">Le type des valeurs du dictionnaire. N'importe quel type intégral, éventuellement nullable.</typeparam>
/// <param name="reader">Le jeu de données SQL à convertir.</param>
/// <param name="dbNullToDefault">Optionnel. Permet de convertir la valeur <see cref="DBNull.Value"/> dans la valeur par défaut du type cible.</param>
/// <returns>Un dictionnaire de valeurs, où la clé est le nom de la colonne.</returns>
public static IDictionary <string, T> ToDynamicDictionnary <T>(this DataTableReader reader, bool dbNullToDefault = false)
{
// Si T est nullable, récupère le type sous-jacent car c'est lui qui sera récupéré au DBType
Type typeOfT = typeof(T);
if (typeOfT.IsGenericType && typeOfT.GetGenericTypeDefinition() == typeof(Nullable <>))
{
typeOfT = Nullable.GetUnderlyingType(typeOfT);
}
Dictionary <string, T> values = new Dictionary <string, T>();
for (int i = 0; i < reader.FieldCount; i++)
{
if (reader.GetFieldType(i) == typeOfT || reader.GetFieldType(i).IsSubclassOf(typeOfT))
{
values.Add(reader.GetName(i), reader.IsDBNull(i) && dbNullToDefault ? default(T) : (T)reader[i]);
}
}
return(values);
}
public List <T> Map(DataTable dataTable)
{
List <T> entities = new List <T>();
using (DataTableReader reader = dataTable.CreateDataReader())
{
Object[] values = new object[reader.FieldCount];
while (reader.Read())
{
reader.GetValues(values);
T entity = Activator.CreateInstance <T>();
for (int i = 0; i < reader.FieldCount; i++)
{
MapPrimative(values[i], reader.GetName(i), entity);
}
entities.Add(entity);
}
}
return(entities);
}
/// <summary>
/// This function allows the "Remote Data Store" to return an element's chosen baseline definition.
/// </summary>
/// <param name="pEID">The element ID to get the baselines list.</param>
/// <param name="pWhich">The baseline you want the definition (AssignedCurrent, AssignedFuture) .</param>
/// <returns>The list of baselines assigned to elementID.</returns>
public DataContainer mGetElementSpecificBaselineDefinition(string pEID, string pWhich)
{
using (DataTable lElementTable = new DataTable("ElementSpecificBaselineDefinition"))
{
lElementTable.Locale = CultureInfo.InvariantCulture;
lock (_SQLWrapper)
{
_SQLWrapper.mExecuteQuery(String.Format(CultureInfo.InvariantCulture, "SELECT * FROM {0} WHERE ElementID == '{1}'", _elementsDataStore, pEID), lElementTable);
}
DataTableReader lElTableReader = new DataTableReader(lElementTable);
lElTableReader.Read();
string lBLVersion = string.Empty;
for (int i = 0; i < lElTableReader.FieldCount; i++)
{
if (lElTableReader.GetName(i) == pWhich + "Baseline")
{
lBLVersion = lElTableReader.GetValue(i).ToString().Trim();
break;
}
}
return(mGetBaselineDefinition(lBLVersion));
}
}
/// <summary>
/// Charge les statistiques associées à une édition de tournoi.
/// </summary>
/// <param name="edition">Edition de tournoi.</param>
/// <exception cref="ArgumentNullException">L'argument <paramref name="edition"/> est <c>Null</c>.</exception>
public void LoadEditionsStatistics(Edition edition)
{
if (edition == null)
{
throw new ArgumentNullException(nameof(edition));
}
if (edition.StatisticsAreCompute)
{
return;
}
string query = "select * from edition_player_stats where edition_ID = @edition";
using (DataTableReader subReader = SqlTools.ExecuteReader(query, new SqlParam("@edition", DbType.UInt32, edition.ID)))
{
while (subReader.Read())
{
ulong playerId = subReader.GetUint64("player_ID");
for (int i = 0; i < subReader.FieldCount; i++)
{
string columnName = subReader.GetName(i);
if (columnName == "edition_ID" || columnName == "player_ID")
{
continue;
}
edition.AddPlayerStatistics(playerId, Tools.GetEnumValueFromSqlMapping <StatType>(columnName), Convert.ToUInt32(subReader[columnName]));
}
if (Config.GetBool(AppKey.ComputeStatisticsWhileLoading))
{
_dataLoadingProgressEventHandler?.Invoke(new DataLoadingProgressEvent(100 * ++_currentDataCount / _totalDataCount));
}
}
}
}
// <Snippet1>
private static void GetAllValues(DataTableReader reader)
{
// Given a DataTableReader, retrieve the value of
// each column, and display the name, value, and type.
// Make sure you have called reader.Read at least once before
// calling this procedure.
// Loop through all the columns.
object value = null;
for (int i = 0; i < reader.FieldCount; i++)
{
if (reader.IsDBNull(i))
{
value = "<NULL>";
}
else
{
value = reader.GetValue(i);
}
Console.WriteLine("{0}: {1} ({2})", reader.GetName(i),
value, reader.GetFieldType(i).Name);
}
}
public IHttpActionResult Get(string datasource, string siteName)
{
ReportDataProviderBase reportingDataProvider = ApiContainer.Configuration.GetReportingDataProvider();
var cachingPolicy = new CachingPolicy
{
ExpirationPeriod = TimeSpan.FromHours(1)
//must find cache expiration node
};
Item dataSourceItem = Database.GetDatabase("core").GetItem(new ID(datasource));
var reportSQLQuery = dataSourceItem.Fields["{0AA8B742-BBDF-4405-AB8D-6FAC7E79433B}"].Value;
NameValueCollection parameters = HttpUtility.ParseQueryString(this.Request.RequestUri.Query);
var from = DateTime.ParseExact(parameters["dateFrom"], "dd-MM-yyyy", new DateTimeFormatInfo());
var to = DateTime.ParseExact(parameters["dateTo"], "dd-MM-yyyy", new DateTimeFormatInfo());
string dateFrom = from.ToString("yyyy-MM-dd");
string dateTo = to.ToString("yyyy-MM-dd");
if (from.Equals(to) && parameters["dateTo"].Length <= 10)
{
dateFrom = from.ToString("yyyy-MM-dd 00:00:00");
dateTo = to.ToString("yyyy-MM-dd 23:59:59");
}
reportSQLQuery = reportSQLQuery.Replace("@StartDate", "'" + dateFrom + "'");
reportSQLQuery = reportSQLQuery.Replace("@EndDate", "'" + dateTo + "'");
string hashedSiteName = "0";
if (siteName != "all")
{
var encoder = new Hash32Encoder();
hashedSiteName = encoder.Encode(siteName);
reportSQLQuery = reportSQLQuery.Replace("@SiteNameIdOperator", "=");
}
else
{
reportSQLQuery = reportSQLQuery.Replace("@SiteNameIdOperator", "!=");
}
reportSQLQuery = reportSQLQuery.Replace("@SiteNameId", hashedSiteName);
var query = new ReportDataQuery(reportSQLQuery);
DataTableReader reader = reportingDataProvider.GetData("reporting", query, cachingPolicy).GetDataTable().CreateDataReader();
var data = new ReportData();
int counter = 0;
while (reader.Read())
{
var row = new Dictionary <string, string>();
for (int i = 0; i < reader.FieldCount; i++)
{
row.Add(reader.GetName(i), reader[i].ToString());
}
data.AddRow(row);
counter++;
}
var responce = new ReportResponse
{
data = data,
TotalRecordCount = counter
};
return(new JsonResult <ReportResponse>(responce, new JsonSerializerSettings {
ContractResolver = new CamelCasePropertyNamesContractResolver()
}, Encoding.UTF8, this));
}
public string GetName(int i)
{
return(_reader.GetName(i));
}
/// <summary>
/// 数据集转实体
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="table">数据源</param>
/// <returns></returns>
public static List <T> ToEntityList <T>(this DataTable table)
{
List <T> _EntityList = new List <T>();
if (table == null || table.Rows.Count == 0)
{
return(_EntityList);
}
DataTableReader dataRecord = table.CreateDataReader();
DynamicMethod method = new DynamicMethod(string.Format("{0}.ToEntityList{1}", typeof(Common).FullName, Guid.NewGuid()),
typeof(List <T>), new Type[] { typeof(IDataRecord) }, typeof(Common), true);
ILGenerator il = method.GetILGenerator();
LocalBuilder listBuilder = il.DeclareLocal(typeof(List <T>));
LocalBuilder itemBuilder = il.DeclareLocal(typeof(T));
System.Reflection.Emit.Label exitLabel = il.DefineLabel();
System.Reflection.Emit.Label loopLabel = il.DefineLabel();
//初始化List变量
il.Emit(OpCodes.Newobj, typeof(List <T>).GetConstructor(Type.EmptyTypes));
il.Emit(OpCodes.Stloc, listBuilder);
il.MarkLabel(loopLabel);
il.Emit(OpCodes.Ldarg_0);
il.Emit(OpCodes.Callvirt, DataReader_Read);
il.Emit(OpCodes.Brfalse, exitLabel);
//初始化空的T实例
il.Emit(OpCodes.Newobj, typeof(T).GetConstructor(Type.EmptyTypes));
il.Emit(OpCodes.Stloc_S, itemBuilder);
for (int i = 0; i < dataRecord.FieldCount; i++)
{
PropertyInfo propertyInfo = typeof(T).GetProperty(dataRecord.GetName(i));
System.Reflection.Emit.Label endIfLabel = il.DefineLabel();
if (propertyInfo != null && propertyInfo.GetSetMethod() != null)
{
il.Emit(OpCodes.Ldarg_0);
il.Emit(OpCodes.Ldc_I4, i);
il.Emit(OpCodes.Callvirt, isDBNullMethod);
il.Emit(OpCodes.Brtrue, endIfLabel);
il.Emit(OpCodes.Ldloc, itemBuilder);
il.Emit(OpCodes.Ldarg_0);
il.Emit(OpCodes.Ldc_I4, i);
il.Emit(OpCodes.Callvirt, getValueMethod);
Type propType = propertyInfo.PropertyType;
Type underType = Nullable.GetUnderlyingType(propType);
Type unboxType = underType != null ? underType : propType;
//if (propType.IsValueType)
//{
// il.Emit(OpCodes.Unbox_Any, dataRecord.GetFieldType(i));
//}
if (unboxType == typeof(byte[]) || unboxType == typeof(string))
{
il.Emit(OpCodes.Castclass, propType);
}
else
{
il.Emit(OpCodes.Unbox_Any, dataRecord.GetFieldType(i));
if (underType != null)
{
il.Emit(OpCodes.Newobj, propType.GetConstructor(new[] { underType }));
}
}
il.Emit(OpCodes.Callvirt, propertyInfo.GetSetMethod());
il.MarkLabel(endIfLabel);
}
}
il.Emit(OpCodes.Ldloc_S, listBuilder);
il.Emit(OpCodes.Ldloc_S, itemBuilder);
il.Emit(OpCodes.Callvirt, typeof(List <T>).GetMethod("Add"));
il.Emit(OpCodes.Br, loopLabel);
il.MarkLabel(exitLabel);
il.Emit(OpCodes.Ldloc, listBuilder);
il.Emit(OpCodes.Ret);
Func <IDataRecord, List <T> > func = (Func <IDataRecord, List <T> >)method.CreateDelegate(typeof(Func <IDataRecord, List <T> >));
_EntityList = func(dataRecord);
return(_EntityList);
}
private void mapQueryResults(DataTable queryResults, DataTable XLMapping, ExcelWorksheet XLWS)
{
DataTableReader queryResultsReader = queryResults.CreateDataReader();
string fldName = string.Empty;
while (queryResultsReader.Read())
{
DataTableReader mappingReader = XLMapping.CreateDataReader();
fldName = String.Empty;
for (int i = 0; i < queryResults.Columns.Count - 1; i++)
{
fldName += queryResultsReader[i].ToString();
}
fldName += "Total";
if (queryResultsReader.GetName(queryResults.Columns.Count - 1).ToLower() == "total")
{
while (mappingReader.Read())
{
string xlsCell = mappingReader["xlsCell"].ToString();
string xlsTitle = mappingReader["xlsTitle"].ToString();
if (xlsTitle.ToLower() == "#linelist")
{
mapLineList(queryResults, xlsCell, XLWS, false);
}
else if (xlsTitle.ToLower() == fldName.ToLower())
{
XLWS.Cells[xlsCell].Value
= Convert.ToInt32(queryResultsReader["Total"].ToString());
XLWS.Cells[xlsCell].Style.Numberformat.Format = "#,##0";
}
}
}
else if (queryResults.Rows.Count == 1)
{
while (mappingReader.Read())
{
for (int i = 0; i < queryResults.Columns.Count; i++)
{
string xlsCell = mappingReader["xlsCell"].ToString();
string xlsTitle = mappingReader["xlsTitle"].ToString();
if (xlsTitle.ToLower() == "#linelist")
{
mapLineList(queryResults, xlsCell, XLWS, false);
}
fldName = queryResultsReader.GetName(i);
if (xlsTitle.ToLower() == fldName.ToLower())
{
int result;
if (int.TryParse(queryResultsReader[i].ToString(), out result))
{
XLWS.Cells[xlsCell].Value = result;
//XLWS.Cells[xlsCell].Style.Numberformat.Format = "#,##0";
}
else
{
XLWS.Cells[xlsCell].Value = queryResultsReader[i].ToString();
}
}
}
}
}
else
{
while (mappingReader.Read())
{
string xlsCell = mappingReader["xlsCell"].ToString();
string xlsTitle = mappingReader["xlsTitle"].ToString();
if (xlsTitle.ToLower() == "#linelist")
{
mapLineList(queryResults, xlsCell, XLWS, false);
}
if (xlsTitle.ToLower() == "#linelist_")
{
mapLineList(queryResults, xlsCell, XLWS, true);
}
}
}
}
}
protected override void InitForm()
{
label1.Tag = DateField;
label2.Tag = DateField;
IgnoreReportSet = true;
base.InitForm();
dtpBegin.Value = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1, 0, 0, 0);
dtpEnd.Value = DateTime.Now.Date.AddHours(23).AddMinutes(59).AddSeconds(59);
dtpYM.Value = new DateTime(DateTime.Now.Date.Year, DateTime.Now.Date.Month, 1);
dtpYM.CustomFormat = SystemInfo.YMFormat;
label1.Enabled = ((DateFlag == 1) || (DateFlag == 2));
dtpBegin.Enabled = label1.Enabled;
dtpEnd.Enabled = label1.Enabled;
label2.Enabled = (DateFlag == 3);
dtpYM.Enabled = label2.Enabled;
label1.Visible = label1.Enabled;
dtpBegin.Visible = dtpBegin.Enabled;
dtpEnd.Visible = dtpEnd.Enabled;
label2.Visible = label2.Enabled;
dtpYM.Visible = dtpYM.Enabled;
bool HasEmpNo = false;
bool HasEmpName = false;
bool HasDepartID = false;
bool HasDepartName = false;
DataTableReader dr = null;
try
{
if (db.IsOpen)
{
db.Open(SystemInfo.ConnStr);
}
dr = db.GetDataReader(Pub.GetSQL(DBCode.DB_000001, new string[] { "606", ReportView }));
for (int i = 0; i < dr.FieldCount; i++)
{
if (dr.GetName(i).ToLower() == "empno")
{
HasEmpNo = true;
}
if (dr.GetName(i).ToLower() == "empname")
{
HasEmpName = true;
}
if (dr.GetName(i).ToLower() == "departid")
{
HasDepartID = true;
}
if (dr.GetName(i).ToLower() == "departname")
{
HasDepartName = true;
}
}
}
catch (Exception E)
{
Pub.ShowErrorMsg(E);
}
finally
{
if (dr != null)
{
dr.Close();
}
dr = null;
}
if (HasEmpNo && !HasEmpName)
{
empFlag = 1;
}
else if (!HasEmpNo && HasEmpName)
{
empFlag = 2;
}
else if (HasEmpNo && HasEmpName)
{
empFlag = 3;
}
if (HasDepartID && !HasDepartName)
{
departFlag = 1;
}
else if (!HasDepartID && HasDepartName)
{
departFlag = 2;
}
else if (HasDepartID && HasDepartName)
{
departFlag = 3;
}
label4.Enabled = empFlag > 0;
txtEmp.Enabled = label4.Enabled;
btnSelectEmp.Enabled = label4.Enabled;
label3.Enabled = departFlag > 0;
txtDepart.Enabled = label3.Enabled;
btnSelectDepart.Enabled = label3.Enabled;
label4.Visible = label4.Enabled;
txtEmp.Visible = txtEmp.Enabled;
btnSelectEmp.Visible = btnSelectEmp.Enabled;
label3.Visible = label3.Enabled;
txtDepart.Visible = txtDepart.Enabled;
btnSelectDepart.Visible = btnSelectDepart.Enabled;
if (DateFlag == 1)
{
dtpBegin.Format = DateTimePickerFormat.Custom;
dtpEnd.Format = DateTimePickerFormat.Custom;
dtpBegin.CustomFormat = "yyyy-MM-dd HH:mm:ss";
dtpEnd.CustomFormat = "yyyy-MM-dd HH:mm:ss";
}
if (label1.Enabled)
{
txtEmp.Top = dtpYM.Top;
label4.Top = txtEmp.Top + 4;
btnSelectEmp.Top = txtEmp.Top + 1;
txtDepart.Top = txtEmp.Top + 30;
label3.Top = txtDepart.Top + 4;
btnSelectDepart.Top = txtDepart.Top + 1;
}
else if (label2.Enabled)
{
label2.Top = label1.Top;
dtpYM.Top = dtpBegin.Top;
txtEmp.Top = dtpYM.Top + 30;
label4.Top = txtEmp.Top + 4;
btnSelectEmp.Top = txtEmp.Top + 1;
txtDepart.Top = txtEmp.Top + 30;
label3.Top = txtDepart.Top + 4;
btnSelectDepart.Top = txtDepart.Top + 1;
}
else if (!label1.Enabled && !label2.Enabled)
{
txtEmp.Top = 10;
label4.Top = txtEmp.Top + 4;
btnSelectEmp.Top = txtEmp.Top + 1;
txtDepart.Top = txtEmp.Top + 30;
label3.Top = txtDepart.Top + 4;
btnSelectDepart.Top = txtDepart.Top + 1;
}
}
private GuidCollection GetSoftwareBulletins()
{
GuidCollection bulletin_collection = new GuidCollection();
DataTable bulletins = GetExistingBulletins();
Console.WriteLine("# {0} bulletins returned by the stored procedure execution.", bulletins.Rows.Count);
DataTable excluded_bulletins = GetExcludedBulletins();
Console.WriteLine("# {0} bulletin names found in the exclusion table.", excluded_bulletins.Rows.Count);
if (bulletins.Rows.Count == 0)
{
return(bulletin_collection);
}
try {
using (DataTableReader sqlRdr = bulletins.CreateDataReader()) {
#region Get position of the used field
int pos_released = -1;
int pos_res_guid = -1;
int pos_bulletin = -1;
int pos_severity = -1;
int pos_vendor = -1;
for (int i = 0; i < sqlRdr.FieldCount; i++)
{
string field_name = sqlRdr.GetName(i).ToLower();
if (field_name == "released")
{
pos_released = i;
}
if (field_name == "_resourceguid")
{
pos_res_guid = i;
}
if (field_name == "bulletin")
{
pos_bulletin = i;
}
if (field_name == "severity")
{
pos_severity = i;
}
if (field_name == "vendor")
{
pos_vendor = i;
}
}
bool field_init = false;
if (pos_severity != -1 && pos_res_guid != -1 && pos_released != -1 && pos_bulletin != -1)
{
field_init = true;
}
#endregion
if (config.Debug)
{
Console.WriteLine("# Field positions are:\n\tBulletin={0}\n\tReleased={1}\n\tResourceGuid={2}\n\tSeverity={3}\n\tVendor={4}", pos_bulletin, pos_released, pos_res_guid, pos_severity, pos_vendor);
}
if (field_init)
{
while (sqlRdr.Read())
{
Guid bguid = sqlRdr.GetGuid(pos_res_guid);
String bull_name = sqlRdr.GetString(pos_bulletin);
String sev = sqlRdr.GetString(pos_severity);
DateTime dt = sqlRdr.GetDateTime(pos_released);
String bull_vendor = string.Empty;
if (pos_vendor != -1)
{
bull_vendor = sqlRdr.GetString(pos_vendor).ToLower();
}
bool row_excluded = false;
#region // Break if the current bulletin is excluded
foreach (DataRow r in excluded_bulletins.Rows)
{
if (r[0].ToString() == bull_name)
{
row_excluded = true;
break;
}
}
if (row_excluded)
{
continue;
}
#endregion
if ((sev.ToLower() == config.Severity.ToLower() || config.Severity == "*") && dt >= config.Released_After && dt <= config.Released_Before)
{
if (pos_vendor == -1 || config.Vendor_Name == bull_vendor || config.Vendor_Name == "*")
{
if (config.Debug)
{
Console.WriteLine("\tWe have a match: {0} from {1}", bull_name, bull_vendor);
}
bulletin_collection.Add(bguid);
}
}
}
}
else
{
Console.WriteLine("Failed to find the required fields in the provided data table. Not doing anything.");
}
}
} catch (Exception e) {
Console.WriteLine("Error: {0}\nException message = {1}\nStack trace = {2}.", e.Message, e.InnerException, e.StackTrace);
}
Console.WriteLine("{0} bulletins match the {1} severity and will be checked for policies.", bulletin_collection.Count, config.Severity);
return(bulletin_collection);
}
public bool Example()
{
using (DataTableReader reader = Table.CreateDataReader()) {
while (reader.Read())
{
// Access via the fields name or ordinal position
var aValue = (int)reader [0];
var bValue = (int)reader ["idAnotherTable"];
if (aValue < 0 || bValue < 0 || aValue.GetType() != typeof(int) || bValue.GetType() != typeof(int))
{
throw new Exception("An Issue");
}
// Access should be preferred through strongly typed values
var isNull = reader.IsDBNull(0);
if (isNull)
{
throw new Exception("An Issue");
}
var aString = reader.GetString(1);
if (aString.Length == 0 || aString.GetType() != typeof(string))
{
throw new Exception("An Issue");
}
var anInt = reader.GetInt32(2);
if (anInt == 0 || anInt.GetType() != typeof(int))
{
throw new Exception("An Issue");
}
var aDecimal = reader.GetDecimal(3);
if (aDecimal == 0 || aDecimal.GetType() != typeof(decimal))
{
throw new Exception("An Issue");
}
// Access the .NET field type or the data providers type
Type aType = reader.GetFieldType(0);
Type bType = reader.GetProviderSpecificFieldType(0);
if (aType != typeof(int) || bType != typeof(int))
{
throw new Exception("An Issue");
}
// Access field name and ordinal position
var fieldName = reader.GetName(1);
int position = reader.GetOrdinal("StringField");
if (fieldName != "StringField" || position != 1)
{
throw new Exception("An Issue");
}
}
return(true);
}
}