/// <summary> /// Check if an SqlDataReader contains a field /// </summary> /// <param name="reader">The reader</param> /// <param name="columnName">The column name</param> /// <returns></returns> public static bool ColumnExists(this System.Data.IDataReader reader, string columnName) { var schemaTable = reader.GetSchemaTable(); if (schemaTable != null) { schemaTable.DefaultView.RowFilter = "ColumnName= '" + columnName + "'"; } var dataTable = reader.GetSchemaTable(); return(dataTable != null && (dataTable.DefaultView.Count > 0)); }
} // End Function GetColumnNames public override System.Data.DataTable GetColumnNamesForTable(string strTableName) { strTableName = strTableName.Replace("'", "''"); System.Data.DataTable dtSchemaTable = null; using (System.Data.IDataReader idr = ExecuteReader("SELECT * FROM " + strTableName)) { dtSchemaTable = idr.GetSchemaTable(); idr.Close(); } return(dtSchemaTable); } // End Function GetColumnNamesForTable
public static IEnumerable <dynamic> DynamicSqlQuery(this Database database, string sql, params object[] parameters) { TypeBuilder builder = createTypeBuilder( "MyDynamicAssembly", "MyDynamicModule", "MyDynamicType"); using (System.Data.IDbCommand command = database.Connection.CreateCommand()) { try { database.Connection.Open(); command.CommandText = sql; command.CommandTimeout = command.Connection.ConnectionTimeout; foreach (var param in parameters) { command.Parameters.Add(param); } using (System.Data.IDataReader reader = command.ExecuteReader()) { var schema = reader.GetSchemaTable(); foreach (System.Data.DataRow row in schema.Rows) { string name = (string)row["ColumnName"]; //var a=row.ItemArray.Select(d=>d.) Type type = (Type)row["DataType"]; if (type != typeof(string) && (bool)row.ItemArray[schema.Columns.IndexOf("AllowDbNull")]) { type = typeof(Nullable <>).MakeGenericType(type); } createAutoImplementedProperty(builder, name, type); } } } finally { database.Connection.Close(); command.Parameters.Clear(); } } Type resultType = builder.CreateType(); var result = database.SqlQuery(resultType, sql, parameters); foreach (dynamic item in result) { yield return(item); } }
public static ArrayList GetColumnList(this System.Data.IDataReader reader) { var columnList = new ArrayList(); var readerSchema = reader.GetSchemaTable(); if (readerSchema == null) { throw new NullReferenceException("GetSchemaTable returned null"); } for (var i = 0; i < readerSchema.Rows.Count; i++) { columnList.Add(readerSchema.Rows[i]["ColumnName"].ToString().ToLowerInvariant()); } return(columnList); }
public static IEnumerable <dynamic> DynamicSqlQuery(this Database database, string sql, params object[] parameters) { List <dynamic> retorno = new List <dynamic>(); using (System.Data.IDbCommand command = database.Connection.CreateCommand()) { try { database.Connection.Open(); command.CommandText = sql; command.CommandTimeout = command.Connection.ConnectionTimeout; foreach (var param in parameters) { command.Parameters.Add(param); } using (System.Data.IDataReader reader = command.ExecuteReader()) { var schema = reader.GetSchemaTable(); while (reader.Read()) { dynamic obj = new JObject(); foreach (System.Data.DataRow row in schema.Rows) { string name = (string)row["ColumnName"]; obj[name] = reader[name].ToString(); } retorno.Add(obj); } } } finally { database.Connection.Close(); command.Parameters.Clear(); } } return(retorno); }
/// <summary> /// Translates reader to the datatable /// </summary> /// <param name="reader">The reader.</param> /// <returns></returns> public static System.Data.DataTable ConvertToTable(System.Data.IDataReader reader) { System.Data.DataTable _table = reader.GetSchemaTable(); System.Data.DataTable _dt = new System.Data.DataTable(); System.Data.DataColumn _dc; System.Data.DataRow _row; System.Collections.ArrayList _al = new System.Collections.ArrayList(); for (int i = 0; i < _table.Rows.Count; i++) { _dc = new System.Data.DataColumn(); if (!_dt.Columns.Contains(_table.Rows[i]["ColumnName"].ToString())) { _dc.ColumnName = _table.Rows[i]["ColumnName"].ToString(); _dc.Unique = Convert.ToBoolean(_table.Rows[i]["IsUnique"]); _dc.AllowDBNull = Convert.ToBoolean(_table.Rows[i]["AllowDBNull"]); _dc.DataType = (System.Type)_table.Rows[i]["DataType"]; _dc.ReadOnly = Convert.ToBoolean(_table.Rows[i]["IsReadOnly"]); _al.Add(_dc.ColumnName); _dt.Columns.Add(_dc); } } while (reader.Read()) { _row = _dt.NewRow(); for (int i = 0; i < _al.Count; i++) { _row[((System.String)_al[i])] = reader[(System.String)_al[i]]; } _dt.Rows.Add(_row); } return(_dt); }
private static bool ColumnExists(System.Data.IDataReader reader, string rowName) { try { if (string.IsNullOrEmpty(rowName)) { return(false); } foreach (System.Data.DataRow row in reader.GetSchemaTable().Rows) { if (((row["ColumnName"] as string) ?? "").Equals(rowName, StringComparison.CurrentCultureIgnoreCase)) { return(true); } } return(false); } catch { //uhhhhh...... doesn't exist. return(false); } }
private void OutputToFile(System.Data.IDataReader reader, string fileName, string separator, string terminator) { using (var s = System.IO.File.Create(fileName)) { using (var writer = new System.IO.StreamWriter(s)) { var schematable = reader.GetSchemaTable(); var schemacol_colname = schematable.Columns["ColumnName"]; var schemacol_ordinal = schematable.Columns["ColumnOrdinal"]; var schemacol_datatypename = schematable.Columns["DataTypeName"]; var schemacol_providertype = schematable.Columns["ProviderType"]; var schemacol_allowdbnull = schematable.Columns["AllowDBNull"]; var column_names = new string[schematable.Rows.Count]; if (!this.ExcludeHeaders) { for (int i = 0; i < schematable.Rows.Count; i++) { var row = schematable.Rows[i]; string colname = (string)row[schemacol_colname.Ordinal]; string datatypename = (string)row[schemacol_datatypename.Ordinal]; System.Type providertype = (System.Type)row[schemacol_providertype.Ordinal]; column_names[i] = colname; if (i > 0) { writer.Write(this.Separator); //Console.Write(this.Separator); } writer.Write("{0}", colname); //Console.Write("{0}", colname); } writer.Write(this.Terminator); //Console.Write(this.Terminator); } DownloadCosmos.recordCount = 0; object[] values = new object[reader.FieldCount]; while (reader.Read()) { int num_fields = reader.GetValues(values); for (int i = 0; i < num_fields; i++) { if (i > 0) { writer.Write(this.Separator); //Console.Write(this.Separator); } writer.Write(values[i]); //Console.Write(values[i]); } writer.Write(this.Terminator); //Console.Write(this.Terminator); DownloadCosmos.recordCount++; if (DownloadCosmos.recordCount % incr == 0) { Console.Write("."); } } } } }
private static List <TableFields> GetDeclaration(System.Data.IDataReader reader, List <TableFields> fields) { var table = reader.GetSchemaTable(); var actualFields = new List <TableFields> { }; var orderedFields = new List <TableFields> { }; TableFields item; String dateFormat = System.Configuration.ConfigurationManager.AppSettings.Get("DateFormat"); String dateTimeFormat = System.Configuration.ConfigurationManager.AppSettings.Get("DateTimeFormat"); string GetFormat(string fieldType, string fieldName) { if (fieldType.ToLower() == "date") { return(dateFormat); } else if (fieldType.ToLower() == "datetime") { // DateTime fields format string[] arrayTime = { "_at", "_to", "_on", "_do" }; if (arrayTime.Contains(fieldName.ToLower().Substring(fieldName.Length - 3))) { return(dateTimeFormat); } return(dateFormat); } else { return("auto"); } }; // if (dateFormat == null) { dateFormat = System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat.ShortDatePattern; } if (dateTimeFormat == null) { dateTimeFormat = System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat.ShortDatePattern + " " + System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat.LongTimePattern; } for (int i = 0; i < reader.FieldCount; i++) { // System.Data.DataRow row = table.Rows[i]; var row = table.Rows[i]; actualFields.Add(new TableFields { colId = i, name = row["ColumnName"].ToString(), type = GetVariableType(row["DataType"].ToString(), row["NumericScale"].ToString()) }); // ak by som nemal ziadnu definiciu predtym item = (fields == null ? null : fields.FirstOrDefault(x => x.name.ToUpper() == row["ColumnName"].ToString().ToUpper())); var colSize = row["ColumnSize"].ToString() == "" ? "0" : row["ColumnSize"].ToString(); if (item == null) { actualFields[i].title = actualFields[i].name; actualFields[i].minsize = Math.Max(Math.Min(10, int.Parse(colSize)), actualFields[i].name.Length); actualFields[i].order = 100 + i; actualFields[i].format = GetFormat(actualFields[i].type, actualFields[i].name); } else { //use Date / Datetime from config if (item.type.Substring(0, 4).ToLower() == "date" && actualFields[i].type == "DateTime") { actualFields[i].type = item.type; } actualFields[i].title = (item.title == null ? actualFields[i].name : item.title).Trim(); actualFields[i].minsize = item.minsize == 0 ? Math.Max(Math.Min(10, int.Parse(colSize)), actualFields[i].name.Length) : item.minsize; actualFields[i].order = item.order; actualFields[i].format = item.format == null ? (actualFields[i].type == "DateTime" ? dateFormat : "auto") : item.format; if (item.format != null) { actualFields[i].format = item.format; } else { actualFields[i].format = GetFormat(actualFields[i].name, actualFields[i].type); } } } int j = 0; int k = 0; foreach (var field in actualFields.OrderBy(o => o.order).ToList()) { orderedFields.Add(field); if (field.order != 0) { j++; orderedFields[k].order = j; } k++; } SimpleLog.WriteLog(JsonConvert.SerializeObject(orderedFields, Formatting.Indented)); return(orderedFields); }
private static List <TableFields> getDeclaration(System.Data.IDataReader reader, List <TableFields> fields) { var table = reader.GetSchemaTable(); var actualFields = new List <TableFields> { }; var orderedFields = new List <TableFields> { }; TableFields item; String dateFormat; switch (System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat.ShortDatePattern) { case "d.M.yyyy": case "d. M. yyyy": dateFormat = "d.m.yyyy"; break; default: dateFormat = System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat.ShortDatePattern; break; } for (int i = 0; i < reader.FieldCount; i++) { System.Data.DataRow row = table.Rows[i]; actualFields.Add(new TableFields { colId = i, name = row["ColumnName"].ToString(), type = getVariableType(row["DataType"].ToString(), row["NumericScale"].ToString()) }); // ak by som nemal ziadnu definiciu predtym item = (fields == null ? null : fields.FirstOrDefault(x => x.name.ToUpper() == row["ColumnName"].ToString().ToUpper())); if (item == null) { actualFields[i].title = actualFields[i].name; actualFields[i].minsize = Math.Max(Math.Min(10, int.Parse(row["ColumnSize"].ToString())), actualFields[i].name.Length); actualFields[i].order = 100 + i; actualFields[i].format = actualFields[i].type == "DateTime" ? dateFormat : "auto"; } else { actualFields[i].title = (item.title == null ? actualFields[i].name : item.title).Trim(); actualFields[i].format = item.format == null ? (actualFields[i].type == "DateTime" ? dateFormat : "auto") : item.format; actualFields[i].minsize = item.minsize == 0 ? Math.Max(Math.Min(10, int.Parse(row["ColumnSize"].ToString())), actualFields[i].name.Length) : item.minsize; actualFields[i].order = item.order; } } int j = 0; int k = 0; foreach (var field in actualFields.OrderBy(o => o.order).ToList()) { orderedFields.Add(field); if (field.order != 0) { j++; orderedFields[k].order = j; } k++; } SimpleLog.WriteLog(JsonConvert.SerializeObject(orderedFields, Formatting.Indented)); return(orderedFields); }
System.Data.DataTable System.Data.IDataReader.GetSchemaTable() { return(_dataReader.GetSchemaTable()); }