/// <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());
}
