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);
}
private static void PrintTable(DataTable dataTable)
{
DataTableReader reader = dataTable.CreateDataReader();
//dataTable.Columns
foreach (DataColumn d in dataTable.Columns)
{
Console.Write(string.Format("{0,-20}", d.ColumnName));
}
Console.Write("\r\n\r\n");
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
if (reader.GetFieldType(i) == typeof(System.DateTime))
{
Console.Write(string.Format("{0,-20:dd-MM-yyyy}", reader.GetValue(i)));
}
else
{
Console.Write(string.Format("{0,-20}", reader.GetValue(i)));
}
}
Console.WriteLine();
}
}
// <Snippet1>
private void TestGetFieldType(DataTableReader reader)
{
for (int i = 0; i < reader.FieldCount; i++)
{
Console.WriteLine(reader.GetName(i) + ":" +
reader.GetFieldType(i).FullName);
}
}
/// <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);
}
/// <summary>
/// overloaded function that used DataTableReader instead of SqlDataReader
/// </summary>
/// <param name="reader"></param>
/// <param name="index"></param>
/// <param name="counter"></param>
/// <returns></returns>
private TableRow GenerateTableRow(DataTableReader reader, int index, ref int counter)
{
TableRow row = new TableRow();
row.CssClass = "table-row";
TableCell cell;
for (int i = 0; i < reader.FieldCount; i++)
{
cell = new TableCell();
switch (reader.GetFieldType(i).ToString())
{
case "System.Int32":
cell.Controls.Add(GenerateTextBox(reader.GetInt32(i).ToString(), counter++, true));
cell.Controls.Add(GenerateTextBoxValidator((TextBox)cell.Controls[0]));
cell.ToolTip = reader.GetInt32(i).ToString();
break;
case "System.String":
cell.Controls.Add(GenerateTextBox(reader.GetString(i), counter++, true));
cell.Controls.Add(GenerateTextBoxValidator((TextBox)cell.Controls[0]));
cell.ToolTip = reader.GetString(i).ToString();
break;
case "System.DateTime":
cell.Controls.Add(GenerateTextBox(reader.GetDateTime(i).ToString(), counter++, true));
cell.Controls.Add(GenerateTextBoxValidator((TextBox)cell.Controls[0]));
cell.ToolTip = reader.GetDateTime(i).ToString();
break;
case "System.Boolean":
cell.Controls.Add(GenerateCheckBox(reader.GetBoolean(i), true));
break;
}
cell.CssClass = "table-cell";
cell.ID = "row" + index + "cell" + i;
row.Controls.Add(cell);
}
TableCell actionCell = new TableCell();
actionCell.CssClass = "table-cell";
actionCell.Controls.Add(GenerateImgButton("/Resources/Icons/delete-icon.png", new ImageClickEventHandler(delete_btn_Click), "delete-btn" + index));
actionCell.Controls.Add(GenerateImage("/Resources/Icons/changed-icon.png", "changed" + index));
actionCell.ID = "row" + index + "cell-action";
row.Controls.Add(actionCell);
row.ID = "row" + index;
return(row);
}
public void GetProviderSpecificTests()
{
DataTableReader rdr = dt.CreateDataReader ();
while (rdr.Read ()) {
object[] values = new object [rdr.FieldCount];
object[] pvalues = new object [rdr.FieldCount];
rdr.GetValues (values);
rdr.GetProviderSpecificValues (pvalues);
for (int i = 0; i < rdr.FieldCount; ++i) {
Assert.AreEqual(values [i], pvalues [i], "#1");
Assert.AreEqual(rdr.GetValue (i), rdr.GetProviderSpecificValue (i), "#2");
Assert.AreEqual(rdr.GetFieldType (i), rdr.GetProviderSpecificFieldType (i), "#3");
}
}
}
/// <summary>
/// overloaded function using DataTableReader instead of SqlDataReader
/// </summary>
/// <param name="reader"></param>
/// <param name="counter"></param>
/// <returns></returns>
private TableFooterRow GenerateFooterRow(DataTableReader reader, ref int counter)
{
TableFooterRow row = new TableFooterRow();
TableCell cell;
for (int i = 0; i < reader.FieldCount; i++)
{
cell = new TableCell();
if (i > 0)
{
switch (reader.GetFieldType(i).ToString())
{
case "System.Int32":
cell.Controls.Add(GenerateTextBox("", counter++, false));
cell.ToolTip = "";
break;
case "System.String":
cell.Controls.Add(GenerateTextBox("", counter++, false));
cell.ToolTip = "";
break;
case "System.DateTime":
cell.Controls.Add(GenerateTextBox("", counter++, false));
cell.ToolTip = "";
break;
case "System.Boolean":
cell.Controls.Add(GenerateCheckBox(false, false));
break;
}
}
cell.CssClass = "table-footer-cell";
row.Controls.Add(cell);
}
return(row);
}
// <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 Type GetFieldType(int i)
{
return(_reader.GetFieldType(i));
}
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);
}
}
/// <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 WriteDataSetToExcelFile(DataSet ds, String ExcelFile)
{
DataTableReader dtr = null;
System.Data.DataTable dt = null;
DataTableReader dsr = null;
Application excelApplication = null;
Workbook excelWorkbook = null;
Worksheet excelWorksheet = null;
try
{
dtr = ds.CreateDataReader();
dt = dtr.GetSchemaTable();
dsr = dt.CreateDataReader();
Int32 ColumnSize = dtr.VisibleFieldCount;
String[] ColumnNames = new String[ColumnSize];
Int32 index = 0;
while (dsr.Read())
{
ColumnNames[index] = dsr.GetString(0);
index++;
}
excelApplication = new Application();
excelApplication.DisplayAlerts = false;
//excelApplication.Visible = true;
excelWorkbook = excelApplication.Workbooks.Add(Type.Missing);
excelWorksheet = (Worksheet)excelWorkbook.Sheets[1];
excelApplication.Calculation = XlCalculation.xlCalculationManual;
Int32 ColIdx = 1;
Int32 RowIdx = 1;
foreach (String ColumnName in ColumnNames)
{
excelWorksheet.Cells[RowIdx, ColIdx] = ColumnName;
ColIdx++;
}
ColIdx = 1;
RowIdx = 2;
Int32 Maxrows = ds.Tables[0].Rows.Count;
if (dtr.Read())
{
for (ColIdx = 1; ColIdx <= ColumnSize; ColIdx++)
{
if (dtr.GetFieldType(ColIdx - 1) == typeof(String))
{
((Range)excelWorksheet.Cells[RowIdx, ColIdx]).EntireColumn.NumberFormat = "@";
}
else if (dtr.GetFieldType(ColIdx - 1) == typeof(Decimal))
{
((Range)excelWorksheet.Cells[RowIdx, ColIdx]).EntireColumn.NumberFormat = "#,##0.00_);(#,##0.00)";
}
else if (dtr.GetFieldType(ColIdx - 1) == typeof(DateTime))
{
((Range)excelWorksheet.Cells[RowIdx, ColIdx]).EntireColumn.NumberFormat = "m/d/yyyy";
}
else
{
((Range)excelWorksheet.Cells[RowIdx, ColIdx]).EntireColumn.NumberFormat = "General";
}
excelWorksheet.Cells[RowIdx, ColIdx] = dtr.GetValue(ColIdx - 1);
}
RowIdx++;
}
while (dtr.Read())
{
for (ColIdx = 1; ColIdx <= ColumnSize; ColIdx++)
{
excelWorksheet.Cells[RowIdx, ColIdx] = dtr.GetValue(ColIdx - 1);
}
RowIdx++;
}
excelApplication.Calculation = XlCalculation.xlCalculationAutomatic;
excelWorkbook.SaveAs(ExcelFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlShared, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
catch (Exception e)
{
throw e;
}
finally
{
if (excelWorkbook != null)
{
excelWorkbook.Close(Type.Missing, Type.Missing, Type.Missing);
excelWorkbook = null;
}
if (excelApplication != null)
{
excelApplication.DisplayAlerts = true;
excelApplication.Quit();
excelApplication = null;
}
if (dsr != null)
{
dsr.Close();
dsr.Dispose();
dsr = null;
}
if (dt != null)
{
dt.Dispose();
dt = null;
}
if (dtr != null)
{
dtr.Close();
dtr.Dispose();
dtr = null;
}
}
}