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; } } }