public static string GetCreateSql(SqlDataReader reader, string tbName)
{
if (reader == null)
return null;
StringBuilder sb = new StringBuilder("Create table [" + tbName + "](");
DataTable dt = reader.GetSchemaTable();
if (dt == null || dt.Rows.Count <= 0)
return null;
foreach (DataRow row in dt.Rows)
{
string type = row["DataTypeName"].ToString().ToLower();
sb.Append("[" + row["ColumnName"] + "] " + type);
switch (type)
{
case "bigint":
case "bit":
case "datetime":
case "float":
case "image":
case "int":
case "money":
case "ntext":
case "real":
case "smalldatetime":
case "smallint":
case "smallmoney":
case "sql_variant":
case "text":
case "timestamp":
case "tinyint":
case "uniqueidentifier":
case "xml":
break;
case "decimal":
case "numeric":
sb.Append("(" + row["NumericPrecision"] + ", " + row["NumericScale"] + ")");
break;
case "binary":
case "char":
case "nchar":
case "nvarchar":
case "varbinary":
case "varchar":
if ((int)row["ColumnSize"] == int.MaxValue)
sb.Append("(max)");
else
sb.Append("(" + row["ColumnSize"] + ")");
break;
}
sb.Append(",");
}
sb.Remove(sb.Length - 1, 1);// 移除最后一个逗号
sb.Append(")");
return sb.ToString();
}
public static bool readerExists(SqlDataReader dr, string columnName)
{
dr.GetSchemaTable().DefaultView.RowFilter = "ColumnName= '" +
columnName + "'";
return (dr.GetSchemaTable().DefaultView.Count > 0);
}
private DataTable [] GetColumnMetaData()
{
DataTable [] columnMetaDataTables = new DataTable [2];
SqlCommand cmd = new SqlCommand("select @@trancount; " +
"set fmtonly on select * from " +
DestinationTableName + " set fmtonly off;" +
"exec sp_tablecollations_90 '" +
DestinationTableName + "'",
connection);
SqlDataReader reader = cmd.ExecuteReader();
int i = 0; // Skipping 1st result
do
{
if (i == 1)
{
columnMetaDataTables [i - 1] = reader.GetSchemaTable();
}
else if (i == 2)
{
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
columnMetaDataTables [i - 1] = new DataTable();
adapter.FillInternal(columnMetaDataTables [i - 1], reader);
}
i++;
} while (reader.IsClosed == false && reader.NextResult());
reader.Close();
return(columnMetaDataTables);
}
public static DataTable ConvertDataReaderToDataTable(SqlDataReader dataReader)
{
DataTable table = new DataTable();
DataTable schemaTable = dataReader.GetSchemaTable();
DataTable result;
try
{
foreach (DataRow row in schemaTable.Rows)
{
DataColumn column = new DataColumn {
DataType = row.GetType(),
ColumnName = row[0].ToString()
};
table.Columns.Add(column);
}
while (dataReader.Read())
{
DataRow row2 = table.NewRow();
for (int i = 0; i < schemaTable.Rows.Count; i++)
{
row2[i] = dataReader[i].ToString();
}
table.Rows.Add(row2);
}
schemaTable = null;
dataReader.Close();
result = table;
}
catch (Exception ex)
{
SystemError.SystemLog(ex.Message);
throw new Exception(ex.Message, ex);
}
return result;
}
/// <summary>
/// [static] PAB.Data.Utils.DataReaderConverters.ConvertDataReaderToDataTable
/// converts SqlDataReader to a DataTable
/// </summary>
/// <param name="reader">SqlDataReader</param>
/// <returns>System.Data.DataTable</returns>
public static DataTable ConvertDataReaderToDataTable(SqlDataReader 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.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;
}
/// <summary>
/// ��DataReader תΪ DataTable
/// </summary>
/// <param name="DataReader">DataReader</param>
public DataTable ConvertDataReaderToDataTable(SqlDataReader dataReader)
{
DataTable datatable = new DataTable();
try
{
DataTable schemaTable = dataReader.GetSchemaTable();
//��̬�����
foreach (DataRow myRow in schemaTable.Rows)
{
DataColumn myDataColumn = new DataColumn();
myDataColumn.DataType = System.Type.GetType("System.String");
myDataColumn.ColumnName = myRow[0].ToString();
datatable.Columns.Add(myDataColumn);
}
//�������
while (dataReader.Read())
{
DataRow myDataRow = datatable.NewRow();
for (int i = 0; i < schemaTable.Rows.Count; i++)
{
myDataRow[i] = dataReader[i].ToString();
}
datatable.Rows.Add(myDataRow);
myDataRow = null;
}
schemaTable = null;
}
finally
{
dataReader.Close();
dataReader.Dispose();
}
return datatable;
}
private static DbfHeader BuildHeader(SqlDataReader reader, out int geomOrdinal, out int colCount)
{
DbfHeader dbfHeader = new DbfHeader();
DataTable schema = reader.GetSchemaTable();
geomOrdinal = -1;
colCount = schema.Rows.Count - 1;
foreach (DataRow row in schema.Rows)
{
int oridinal = (int)row["ColumnOrdinal"];
int size = (int)row["ColumnSize"];
string name = row["ColumnName"] as string;
switch ((row["DataType"] as Type).Name)
{
case "String":
dbfHeader.AddCharacter(name, (byte)size);
break;
case "SqlGeometry":
geomOrdinal = oridinal;
break;
default:
throw new Exception(String.Format("'{0}' is not a recognized data type", (row["DataType"] as Type).Name));
}
}
if (geomOrdinal == -1)
throw new Exception("Geometry column was not found");
return dbfHeader;
}
private static DataTable attemptToGetSchemaTable(SqlString command, SqlDataReader reader)
{
try
{
return reader.GetSchemaTable();
}
catch (Exception e)
{
throw new InvalidResultSetException("The command [" + command.ToString() + "] did not return a valid result set", e);
}
}
public static bool ReaderContainsColumn(SqlDataReader reader, string col)
{
DataTable schema = reader.GetSchemaTable();
foreach (DataRow row in schema.Rows)
{
if (string.Compare(row["ColumnName"].ToString(), col, true) == 0)
{
return true;
}
}
return false;
}
//-----------googled Apr. 01 2011----------------------
//authors.aspalliance.com/stevesmith/articles/convertReadertoSet.asp
//---------------------------------------------------------------------
/// <summary>
/// Converts a SqlDataReader to a DataSet
/// <param name='reader'>
/// SqlDataReader to convert.</param>
/// <returns>
/// DataSet filled with the contents of the reader.</returns>
/// </summary>
public static DataSet convertDataReaderToDataSet(SqlDataReader reader)
{
DataSet dataSet = new DataSet();
do
{
// Create new data table
DataTable schemaTable = reader.GetSchemaTable();
DataTable dataTable = new DataTable();
if (schemaTable != null)
{
// A query returning records was executed
for (int i = 0; i < schemaTable.Rows.Count; i++)
{
DataRow dataRow = schemaTable.Rows[i];
// Create a column name that is unique in the data table
string columnName = (string)dataRow["ColumnName"]; //+ "<C" + i + "/>";
// Add the column definition to the data table
DataColumn column = new DataColumn(columnName, (Type)dataRow["DataType"]);
dataTable.Columns.Add(column);
}
dataSet.Tables.Add(dataTable);
// Fill the data table we just created
while (reader.Read())
{
DataRow dataRow = dataTable.NewRow();
for (int i = 0; i < reader.FieldCount; i++)
dataRow[i] = reader.GetValue(i);
dataTable.Rows.Add(dataRow);
}
}
else
{
// No records were returned
DataColumn column = new DataColumn("RowsAffected");
dataTable.Columns.Add(column);
dataSet.Tables.Add(dataTable);
DataRow dataRow = dataTable.NewRow();
dataRow[0] = reader.RecordsAffected;
dataTable.Rows.Add(dataRow);
}
}
while (reader.NextResult());
return dataSet;
}
private static SqlMetaData[] createMetaDataForResultset(SqlDataReader dataReader)
{
DataTable schema = dataReader.GetSchemaTable();
LinkedList<DataRow> columns = getDisplayedColumns(schema);
SqlMetaData[] meta = new SqlMetaData[columns.Count];
int columnCount = 0;
foreach (DataRow column in columns)
{
meta[columnCount] = createSqlMetaDataForColumn(column);
columnCount++;
}
return meta;
}
/// <summary>
/// 数据表转换
/// </summary>
/// <param name="dataReader"></param>
/// <returns></returns>
public static DataTable ConvertDataReaderToDataTable(SqlDataReader dataReader)
{
DataTable datatable = new DataTable("DataTable");
DataTable schemaTable = dataReader.GetSchemaTable();
//动态添加列
try
{
if (dataReader != null)
{
foreach (DataRow myRow in schemaTable.Rows)
{
DataColumn myDataColumn = new DataColumn();
myDataColumn.DataType = myRow["DataTypeName"].GetType();
myDataColumn.ColumnName = myRow[0].ToString();
datatable.Columns.Add(myDataColumn);
}
//添加数据
while (dataReader.Read())
{
DataRow myDataRow = datatable.NewRow();
for (int i = 0; i < schemaTable.Rows.Count; i++)
{
myDataRow[i] = dataReader[i].ToString();
}
datatable.Rows.Add(myDataRow);
myDataRow = null;
}
schemaTable = null;
dataReader.Close();
}
return datatable;
}
catch (Exception ex)
{
throw new Exception("转换出错出错!", ex);
}
}
/* private static void GetLiteralInfo (DataRow dataTypeRow, out string literalPrefix, out string literalSuffix) {
*
* Object tempValue = dataTypeRow[DbMetaDataColumnNames.LiteralPrefix];
* if (tempValue == DBNull.Value) {
* literalPrefix = "";
* }
* else {
* literalPrefix = (string)dataTypeRow[DbMetaDataColumnNames.LiteralPrefix];
* }
* tempValue = dataTypeRow[DbMetaDataColumnNames.LiteralSuffix];
* if (tempValue == DBNull.Value) {
* literalSuffix = "";
* }
* else {
* literalSuffix = (string)dataTypeRow[DbMetaDataColumnNames.LiteralSuffix];
* }
* }
*/
protected override DataTable GetSchemaTable(DbCommand srcCommand)
{
SqlCommand sqlCommand = srcCommand as SqlCommand;
SqlNotificationRequest notificationRequest = sqlCommand.Notification;
bool notificationAutoEnlist = sqlCommand.NotificationAutoEnlist;
sqlCommand.Notification = null;
sqlCommand.NotificationAutoEnlist = false;
try {
using (SqlDataReader dataReader = sqlCommand.ExecuteReader(CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo)){
return(dataReader.GetSchemaTable());
}
}
finally {
sqlCommand.Notification = notificationRequest;
sqlCommand.NotificationAutoEnlist = notificationAutoEnlist;
}
}
/// <summary>
/// ��DataReader תΪ DataTable
/// </summary>
/// <param name="DataReader">DataReader</param>
public static DataTable ConvertDataReaderToDataTable(SqlDataReader dataReader)
{
DataTable datatable = new DataTable();
DataTable schemaTable = dataReader.GetSchemaTable();
List<DataColumn> listCols = new List<DataColumn>();
//��̬�����
try
{
if (null != schemaTable)
{
foreach (DataRow drow in schemaTable.Rows)
{
string columnName = System.Convert.ToString(drow["ColumnName"]);
DataColumn column = new DataColumn(columnName, (Type)(drow["DataType"]));
column.Unique = (bool)drow["IsUnique"];
column.AllowDBNull = (bool)drow["AllowDBNull"];
column.AutoIncrement = (bool)drow["IsAutoIncrement"];
listCols.Add(column);
datatable.Columns.Add(column);
}
}
//�������
while(dataReader.Read())
{
DataRow myDataRow = datatable.NewRow();
for (int i = 0; i < listCols.Count; i++)
{
myDataRow[((DataColumn)listCols[i])] = dataReader[i];
}
datatable.Rows.Add(myDataRow);
}
schemaTable = null;
dataReader.Close();
return datatable;
}
catch(Exception ex)
{
Error.Log(ex.ToString());
throw new Exception("ת���������!",ex);
}
}
// этот метод создает ограничения на столбцы таблице на основе полученного объекта DataReader
private static void CreateSchemaFromReader(this DataTable table, SqlDataReader reader)
{
DataTable schemaTable = reader.GetSchemaTable(); // Метод Возвращает таблицу описывающую метаданные столбца объекта SqlDataReader.
foreach (DataRow schemaRow in schemaTable.Rows)
{
DataColumn column = new DataColumn((string)schemaRow["ColumnName"]); // создание столбца с именем столбца в источнике данных
column.AllowDBNull = (bool)schemaRow["AllowDbNull"]; // получение значения свойства AllowDBNull
column.DataType = (Type)schemaRow["DataType"]; // получение значения свойства DataType
column.Unique = (bool)schemaRow["IsUnique"]; // получение значения свойства Unique
column.ReadOnly = (bool)schemaRow["IsReadOnly"]; // получение значения свойства Readonly
column.AutoIncrement = (bool)schemaRow["IsIdentity"]; // получение значения свойства AutoIncrement
if (column.DataType == typeof(string)) // если поле типа string
column.MaxLength = (int)schemaRow["ColumnSize"]; // получить значение свойства MaxLength
if (column.AutoIncrement == true) // Если поле с автоинкрементом
{ column.AutoIncrementStep = -1; column.AutoIncrementSeed = 0; } // задать свойства AutoIncrementStep и AutoIncrementSeed
table.Columns.Add(column); // добавить созданный столбец в коллекцию Columns таблицы
}
}
protected override DataTable GetSchemaTable(DbCommand srcCommand)
{
DataTable table;
SqlCommand command = srcCommand as SqlCommand;
SqlNotificationRequest notification = command.Notification;
bool notificationAutoEnlist = command.NotificationAutoEnlist;
command.Notification = null;
command.NotificationAutoEnlist = false;
try
{
using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly))
{
return(reader.GetSchemaTable());
}
}
finally
{
command.Notification = notification;
command.NotificationAutoEnlist = notificationAutoEnlist;
}
return(table);
}
public System.Data.DataTable GetTable(System.Data.SqlClient.SqlDataReader _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.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);
}
internal static FilterDefs FillTerminalFilterFromReader(SqlDataReader reader)
{
var terminalFilter = new FilterDefs();
if (reader != null && !reader.IsClosed)
{
DataTable dt = reader.GetSchemaTable();
if (dt.Select("ColumnName='" + "FilterId" + "'").Length > 0)
if (!reader.IsDBNull(reader.GetOrdinal("FilterId")))
terminalFilter.FilterId = reader.GetInt32(reader.GetOrdinal("FilterId"));
if (dt.Select("ColumnName='" + "FilterName" + "'").Length > 0)
if (!reader.IsDBNull(reader.GetOrdinal("FilterName")))
terminalFilter.FilterName = reader.GetString(reader.GetOrdinal("FilterName"));
if (dt.Select("ColumnName='" + "Description" + "'").Length > 0)
if (!reader.IsDBNull(reader.GetOrdinal("Description")))
terminalFilter.Description = reader.GetString(reader.GetOrdinal("Description"));
if (dt.Select("ColumnName='" + "SQL" + "'").Length > 0)
if (!reader.IsDBNull(reader.GetOrdinal("SQL")))
terminalFilter.SQL = reader.GetString(reader.GetOrdinal("SQL"));
if (dt.Select("ColumnName='" + "FilterExpression" + "'").Length > 0)
if (!reader.IsDBNull(reader.GetOrdinal("FilterExpression")))
terminalFilter.FilterExpression = reader.GetString(reader.GetOrdinal("FilterExpression"));
if (dt.Select("ColumnName='" + "CreatedOn" + "'").Length > 0)
if (!reader.IsDBNull(reader.GetOrdinal("CreatedOn")))
terminalFilter.CreatedOn = reader.GetDateTime(reader.GetOrdinal("CreatedOn"));
if (dt.Select("ColumnName='" + "CreatedBy" + "'").Length > 0)
if (!reader.IsDBNull(reader.GetOrdinal("CreatedBy")))
terminalFilter.CreatedBy = reader.GetInt16(reader.GetOrdinal("CreatedBy"));
if (dt.Select("ColumnName='" + "VisibleToOthers" + "'").Length > 0)
if (!reader.IsDBNull(reader.GetOrdinal("VisibleToOthers")))
terminalFilter.VisibleToOthers = reader.GetByte(reader.GetOrdinal("VisibleToOthers"));
if (dt.Select("ColumnName='" + "ShownOnModules" + "'").Length > 0)
if (!reader.IsDBNull(reader.GetOrdinal("ShownOnModules")))
terminalFilter.ShownOnModules = reader.GetInt32(reader.GetOrdinal("ShownOnModules"));
}
return terminalFilter;
}
private void BuildCache(bool closeConnection)
{
SqlCommand sourceCommand = SourceCommand;
if (sourceCommand == null)
{
throw new InvalidOperationException("The DataAdapter.SelectCommand property needs to be initialized.");
}
SqlConnection connection = sourceCommand.Connection;
if (connection == null)
{
throw new InvalidOperationException("The DataAdapter.SelectCommand.Connection property needs to be initialized.");
}
if (dbSchemaTable == null)
{
if (connection.State == ConnectionState.Open)
{
closeConnection = false;
}
else
{
connection.Open();
}
SqlDataReader reader = (SqlDataReader)sourceCommand.ExecuteReader(CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo);
dbSchemaTable = reader.GetSchemaTable();
reader.Close();
if (closeConnection)
{
connection.Close();
}
BuildInformation(dbSchemaTable);
}
}
private static void processSchemaInfo(SqlDataReader reader, FlexResult result)
{
try
{
var st = reader.GetSchemaTable();
result.schema = st;
}
catch (Exception ex)
{
result.exceptions.Add(ex);
}
}
internal static string GetCreateTableScript(SqlDataReader reader, string tableName)
{
DataTable dt = reader.GetSchemaTable();
string snip = string.Empty;
StringBuilder sql = new StringBuilder();
sql.AppendFormat("CREATE TABLE {0}\r\n(\r\n", tableName);
for(int i = 0; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];
snip = GetColumnSql(dr);
sql.AppendFormat((i < dt.Rows.Count - 1) ? snip : snip.TrimEnd(',', '\r', '\n'));
}
sql.AppendFormat("\r\n)");
return sql.ToString();
}
private static void SqlDataReaderToXml(
SqlDataReader AReader,
//DataTable ADataTable,
XmlTextWriter AWriter,
Boolean ASkipNameColumn,
String ARowTag,
TRowSetMap AMap
)
{
const String ROW_TAG = "ROW";
const String METADATA_TAG = "METADATA";
const String FIELD_TAG = "FIELD";
const String NONAME_FIELD_PREFIX = "FIELD";
int
i,
start_field_index = ASkipNameColumn ? 1 : 0,
empty_name_field_no = 1;
String FieldName;
DataTable LDataTable = AReader.GetSchemaTable();
List<TFieldAlias> LFields = new List<TFieldAlias>();
TFieldAlias LField;
int FieldCount = AReader.FieldCount;
if(AMap.Fields.Length > 0)
{
foreach (String FieldMap in AMap.Fields.Split(new char[] {','}))
{
i = FieldMap.IndexOf('=');
if(i >= 0)
{
LField.Name = FieldMap.Substring(0, i);
FieldName = FieldMap.Substring(i + 1);
}
else
{
LField.Name = FieldMap;
FieldName = FieldMap;
}
for(i = start_field_index; i < FieldCount; i++)
{
if(FieldName.Equals(AReader.GetName(i), StringComparison.InvariantCultureIgnoreCase))
break;
}
if((i < 0) || (i >= FieldCount))
throw new SystemException("RowSet Field = [" + FieldName + "] not found.");
// LField.Name = XmlConvert.EncodeLocalName(LField.Name);
LField.FieldIndex = i;
LFields.Add(LField);
//AWriter.WriteStartAttribute(LField.Name);
//AWriter.WriteValue(SqlMetaDataToString(LDataTable.Rows[i]));
}
}
else
{
for(LField.FieldIndex = start_field_index; LField.FieldIndex < FieldCount; LField.FieldIndex++)
{
FieldName = AReader.GetName(LField.FieldIndex);
if(FieldName.Length == 0)
FieldName = NONAME_FIELD_PREFIX + (empty_name_field_no++).ToString();
LField.Name = FieldName;
//LField.Name = XmlConvert.EncodeLocalName(FieldName);
LFields.Add(LField);
//AWriter.WriteStartAttribute(LField.Name);
//AWriter.WriteValue(SqlMetaDataToString(LDataTable.Rows[LField.FieldIndex]));
}
}
AWriter.WriteStartElement(METADATA_TAG);
for (i = 0; i < LFields.Count; i++)
{
AWriter.WriteStartElement(FIELD_TAG);
AWriter.WriteStartAttribute(ATTRIBUTE_INDEX);
AWriter.WriteValue(i + 1);
AWriter.WriteStartAttribute(ATTRIBUTE_NAME);
AWriter.WriteValue(LFields[i].Name);
AWriter.WriteStartAttribute(ATTRIBUTE_TYPE);
AWriter.WriteValue(SqlMetaDataToString(LDataTable.Rows[LFields[i].FieldIndex]));
AWriter.WriteEndElement();
}
AWriter.WriteEndElement();
object Value;
while (AReader.Read())
{
AWriter.WriteStartElement(ARowTag ?? ROW_TAG);
for (i = 0; i < LFields.Count; i++)
{
Value = AReader.GetValue(LFields[i].FieldIndex);
if (Value != DBNull.Value) // NULL пропускаем
{
AWriter.WriteStartAttribute(XmlConvert.EncodeLocalName(LFields[i].Name));
try
{
AWriter.WriteValue(Value);
}
catch (InvalidCastException)
{
AWriter.WriteValue(Value.ToString());
}
AWriter.WriteEndAttribute();
}
}
AWriter.WriteEndElement();
}
}
protected override DataTable GetSchemaTable(DbCommand cmd)
{
using (SqlDataReader rdr = (SqlDataReader)cmd.ExecuteReader())
return(rdr.GetSchemaTable());
}
/// <summary>
/// This Perticular method is used for generating CSV files for all languages in that database
/// </summary>
/// <param name="DataReader">data reader object containg output of sql query</param>
/// <param name="FilePath">Path where to create CSV file</param>
/// <returns></returns>
private bool CreateCsvFile(SqlDataReader DataReader, string FilePath)
{
DataTable dt = DataReader.GetSchemaTable();
StringBuilder StrBld = new StringBuilder();
try
{
for (int i = 0; i < dt.Rows.Count; i++)
{
StrBld.Append(dt.Rows[i][0].ToString());
if (i < dt.Rows.Count - 1)
StrBld.Append(",");
}
WriteToCsvFile(StrBld, FilePath);
while (DataReader.Read())
{
StringBuilder StrBldObj = new StringBuilder();
for (int i = 0; i < dt.Rows.Count; i++)
{
StrBldObj.Append("\"" + DataReader[i] + "\"");
if (i < dt.Rows.Count - 1)
StrBldObj.Append(",");
}
WriteToCsvFile(StrBldObj, FilePath);
}
return true;
}
catch (Exception Ex)
{
Global.CreateExceptionString(Ex, null);
return false;
}
}
private bool LoadReaderIntoDataTable(SqlDataReader reader, DataTable table)
{
bool ret = true;
DataTable schemaTable = reader.GetSchemaTable();
try
{
foreach (DataRow row in schemaTable.Rows)
{
}
}
catch (Exception anyExp)
{
Utility.LastErrorMessage = anyExp.Message;
ret = false;
}
return ret;
}
public void GetSchemaTable ()
{
IDbConnection conn = ConnectionManager.Singleton.Connection;
ConnectionManager.Singleton.OpenConnection ();
IDbCommand cmd = null;
IDataReader reader = null;
DataTable schema;
DataRow pkRow;
try {
cmd = conn.CreateCommand ();
cmd.CommandText = "select id, fname, id + 20 as plustwenty from employee";
reader = cmd.ExecuteReader (CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo);
schema = reader.GetSchemaTable ();
reader.Close ();
AssertSchemaTableStructure (schema, "#A:");
Assert.AreEqual (3, schema.Rows.Count, "#A:RowCount");
pkRow = schema.Select ("ColumnName = 'id'") [0];
Assert.IsFalse (pkRow.IsNull ("ColumnName"), "#A:ColumnName_IsNull");
Assert.AreEqual ("id", pkRow ["ColumnName"], "#A:ColumnName_Value");
Assert.IsFalse (pkRow.IsNull ("ColumnOrdinal"), "#A:ColumnOrdinal_IsNull");
Assert.AreEqual (0, pkRow ["ColumnOrdinal"], "#A:ColumnOrdinal_Value");
Assert.IsFalse (pkRow.IsNull ("ColumnSize"), "#A:ColumnSize_IsNull");
Assert.AreEqual (4, pkRow ["ColumnSize"], "#A:ColumnSize_Value");
Assert.IsFalse (pkRow.IsNull ("NumericPrecision"), "#A:NumericPrecision_IsNull");
Assert.AreEqual (10, pkRow ["NumericPrecision"], "#A:NumericPrecision_Value");
Assert.IsFalse (pkRow.IsNull ("NumericScale"), "#A:NumericScale_IsNull");
Assert.AreEqual (255, pkRow ["NumericScale"], "#A:NumericScale_Value");
Assert.IsFalse (pkRow.IsNull ("DataType"), "#A:DataType_IsNull");
Assert.AreEqual (typeof (int), pkRow ["DataType"], "#A:DataType_Value");
Assert.IsFalse (pkRow.IsNull ("ProviderType"), "#A:ProviderType_IsNull");
Assert.AreEqual (8, pkRow ["ProviderType"], "#A:ProviderType_Value");
Assert.IsFalse (pkRow.IsNull ("IsLong"), "#A:IsLong_IsNull");
Assert.AreEqual (false, pkRow ["IsLong"], "#A:IsLong_Value");
Assert.IsFalse (pkRow.IsNull ("AllowDBNull"), "#A:AllowDBNull_IsNull");
Assert.AreEqual (false, pkRow ["AllowDBNull"], "#A:AllowDBNull_Value");
Assert.IsFalse (pkRow.IsNull ("IsReadOnly"), "#A:IsReadOnly_IsNull");
Assert.AreEqual (false, pkRow ["IsReadOnly"], "#A:IsReadOnly_Value");
Assert.IsFalse (pkRow.IsNull ("IsRowVersion"), "#A:IsRowVersion_IsNull");
Assert.AreEqual (false, pkRow ["IsRowVersion"], "#A:IsRowVersion_Value");
Assert.IsFalse (pkRow.IsNull ("IsUnique"), "#A:IsUnique_IsNull");
Assert.AreEqual (false, pkRow ["IsUnique"], "#A:IsUnique_Value");
Assert.IsFalse (pkRow.IsNull ("IsKey"), "#A:IsKey_IsNull");
Assert.AreEqual (true, pkRow ["IsKey"], "#A:IsKey_Value");
Assert.IsFalse (pkRow.IsNull ("IsAutoIncrement"), "#A:IsAutoIncrement_IsNull");
Assert.AreEqual (false, pkRow ["IsAutoIncrement"], "#A:IsAutoIncrement_Value");
Assert.IsTrue (pkRow.IsNull ("BaseSchemaName"), "#A:BaseSchemaName_IsNull");
Assert.AreEqual (DBNull.Value, pkRow ["BaseSchemaName"], "#A:BaseSchemaName_Value");
Assert.IsTrue (pkRow.IsNull ("BaseCatalogName"), "#A:BaseCatalogName_IsNull");
Assert.AreEqual (DBNull.Value, pkRow ["BaseCatalogName"], "#A:BaseCatalogName_Value");
Assert.IsFalse (pkRow.IsNull ("BaseTableName"), "#A:BaseTableName_IsNull");
Assert.AreEqual ("employee", pkRow ["BaseTableName"], "#A:BaseTableName_Value");
Assert.IsFalse (pkRow.IsNull ("BaseColumnName"), "#A:BaseColumnName_IsNull");
Assert.AreEqual ("id", pkRow ["BaseColumnName"], "#A:BaseColumnName_Value");
reader = cmd.ExecuteReader (CommandBehavior.SchemaOnly);
schema = reader.GetSchemaTable ();
reader.Close ();
AssertSchemaTableStructure (schema, "#B:");
Assert.AreEqual (3, schema.Rows.Count, "#B:RowCount");
pkRow = schema.Select ("ColumnName = 'id'") [0];
Assert.IsFalse (pkRow.IsNull ("ColumnName"), "#B:ColumnName_IsNull");
Assert.AreEqual ("id", pkRow ["ColumnName"], "#B:ColumnName_Value");
Assert.IsFalse (pkRow.IsNull ("ColumnOrdinal"), "#B:ColumnOrdinal_IsNull");
Assert.AreEqual (0, pkRow ["ColumnOrdinal"], "#B:ColumnOrdinal_Value");
Assert.IsFalse (pkRow.IsNull ("ColumnSize"), "#B:ColumnSize_IsNull");
Assert.AreEqual (4, pkRow ["ColumnSize"], "#B:ColumnSize_Value");
Assert.IsFalse (pkRow.IsNull ("NumericPrecision"), "#B:NumericPrecision_IsNull");
Assert.AreEqual (10, pkRow ["NumericPrecision"], "#B:NumericPrecision_Value");
Assert.IsFalse (pkRow.IsNull ("NumericScale"), "#B:NumericScale_IsNull");
Assert.AreEqual (255, pkRow ["NumericScale"], "#B:NumericScale_Value");
Assert.IsFalse (pkRow.IsNull ("DataType"), "#B:DataType_IsNull");
Assert.AreEqual (typeof (int), pkRow ["DataType"], "#B:DataType_Value");
Assert.IsFalse (pkRow.IsNull ("ProviderType"), "#B:ProviderType_IsNull");
Assert.AreEqual (8, pkRow ["ProviderType"], "#B:ProviderType_Value");
Assert.IsFalse (pkRow.IsNull ("IsLong"), "#B:IsLong_IsNull");
Assert.AreEqual (false, pkRow ["IsLong"], "#B:IsLong_Value");
Assert.IsFalse (pkRow.IsNull ("AllowDBNull"), "#B:AllowDBNull_IsNull");
Assert.AreEqual (false, pkRow ["AllowDBNull"], "#B:AllowDBNull_Value");
Assert.IsFalse (pkRow.IsNull ("IsReadOnly"), "#B:IsReadOnly_IsNull");
Assert.AreEqual (false, pkRow ["IsReadOnly"], "#B:IsReadOnly_Value");
Assert.IsFalse (pkRow.IsNull ("IsRowVersion"), "#B:IsRowVersion_IsNull");
Assert.AreEqual (false, pkRow ["IsRowVersion"], "#B:IsRowVersion_Value");
Assert.IsFalse (pkRow.IsNull ("IsUnique"), "#B:IsUnique_IsNull");
Assert.AreEqual (false, pkRow ["IsUnique"], "#B:IsUnique_Value");
Assert.IsTrue (pkRow.IsNull ("IsKey"), "#B:IsKey_IsNull");
Assert.AreEqual (DBNull.Value, pkRow ["IsKey"], "#B:IsKey_Value");
Assert.IsFalse (pkRow.IsNull ("IsAutoIncrement"), "#B:IsAutoIncrement_IsNull");
Assert.AreEqual (false, pkRow ["IsAutoIncrement"], "#B:IsAutoIncrement_Value");
Assert.IsTrue (pkRow.IsNull ("BaseSchemaName"), "#B:BaseSchemaName_IsNull");
Assert.AreEqual (DBNull.Value, pkRow ["BaseSchemaName"], "#B:BaseSchemaName_Value");
Assert.IsTrue (pkRow.IsNull ("BaseCatalogName"), "#B:BaseCatalogName_IsNull");
Assert.AreEqual (DBNull.Value, pkRow ["BaseCatalogName"], "#B:BaseCatalogName_Value");
Assert.IsTrue (pkRow.IsNull ("BaseTableName"), "#B:BaseTableName_IsNull");
Assert.AreEqual (DBNull.Value, pkRow ["BaseTableName"], "#B:BaseTableName_Value");
Assert.IsFalse (pkRow.IsNull ("BaseColumnName"), "#B:BaseColumnName_IsNull");
Assert.AreEqual ("id", pkRow ["BaseColumnName"], "#B:BaseColumnName_Value");
reader = cmd.ExecuteReader (CommandBehavior.KeyInfo);
schema = reader.GetSchemaTable ();
reader.Close ();
AssertSchemaTableStructure (schema, "#C:");
Assert.AreEqual (3, schema.Rows.Count, "#C:RowCount");
pkRow = schema.Select ("ColumnName = 'id'") [0];
Assert.IsFalse (pkRow.IsNull ("ColumnName"), "#C:ColumnName_IsNull");
Assert.AreEqual ("id", pkRow ["ColumnName"], "#C:ColumnName_Value");
Assert.IsFalse (pkRow.IsNull ("ColumnOrdinal"), "#C:ColumnOrdinal_IsNull");
Assert.AreEqual (0, pkRow ["ColumnOrdinal"], "#C:ColumnOrdinal_Value");
Assert.IsFalse (pkRow.IsNull ("ColumnSize"), "#C:ColumnSize_IsNull");
Assert.AreEqual (4, pkRow ["ColumnSize"], "#C:ColumnSize_Value");
Assert.IsFalse (pkRow.IsNull ("NumericPrecision"), "#C:NumericPrecision_IsNull");
Assert.AreEqual (10, pkRow ["NumericPrecision"], "#C:NumericPrecision_Value");
Assert.IsFalse (pkRow.IsNull ("NumericScale"), "#C:NumericScale_IsNull");
Assert.AreEqual (255, pkRow ["NumericScale"], "#C:NumericScale_Value");
Assert.IsFalse (pkRow.IsNull ("DataType"), "#C:DataType_IsNull");
Assert.AreEqual (typeof (int), pkRow ["DataType"], "#C:DataType_Value");
Assert.IsFalse (pkRow.IsNull ("ProviderType"), "#C:ProviderType_IsNull");
Assert.AreEqual (8, pkRow ["ProviderType"], "#C:ProviderType_Value");
Assert.IsFalse (pkRow.IsNull ("IsLong"), "#C:IsLong_IsNull");
Assert.AreEqual (false, pkRow ["IsLong"], "#C:IsLong_Value");
Assert.IsFalse (pkRow.IsNull ("AllowDBNull"), "#C:AllowDBNull_IsNull");
Assert.AreEqual (false, pkRow ["AllowDBNull"], "#C:AllowDBNull_Value");
Assert.IsFalse (pkRow.IsNull ("IsReadOnly"), "#C:IsReadOnly_IsNull");
Assert.AreEqual (false, pkRow ["IsReadOnly"], "#C:IsReadOnly_Value");
Assert.IsFalse (pkRow.IsNull ("IsRowVersion"), "#C:IsRowVersion_IsNull");
Assert.AreEqual (false, pkRow ["IsRowVersion"], "#C:IsRowVersion_Value");
Assert.IsFalse (pkRow.IsNull ("IsUnique"), "#C:IsUnique_IsNull");
Assert.AreEqual (false, pkRow ["IsUnique"], "#C:IsUnique_Value");
Assert.IsFalse (pkRow.IsNull ("IsKey"), "#C:IsKey_IsNull");
Assert.AreEqual (true, pkRow ["IsKey"], "#C:IsKey_Value");
Assert.IsFalse (pkRow.IsNull ("IsAutoIncrement"), "#C:IsAutoIncrement_IsNull");
Assert.AreEqual (false, pkRow ["IsAutoIncrement"], "#C:IsAutoIncrement_Value");
Assert.IsTrue (pkRow.IsNull ("BaseSchemaName"), "#C:BaseSchemaName_IsNull");
Assert.AreEqual (DBNull.Value, pkRow ["BaseSchemaName"], "#C:BaseSchemaName_Value");
Assert.IsTrue (pkRow.IsNull ("BaseCatalogName"), "#C:BaseCatalogName_IsNull");
Assert.AreEqual (DBNull.Value, pkRow ["BaseCatalogName"], "#C:BaseCatalogName_Value");
Assert.IsFalse (pkRow.IsNull ("BaseTableName"), "#C:BaseTableName_IsNull");
Assert.AreEqual ("employee", pkRow ["BaseTableName"], "#C:BaseTableName_Value");
Assert.IsFalse (pkRow.IsNull ("BaseColumnName"), "#C:BaseColumnName_IsNull");
Assert.AreEqual ("id", pkRow ["BaseColumnName"], "#C:BaseColumnName_Value");
reader = cmd.ExecuteReader ();
schema = reader.GetSchemaTable ();
reader.Close ();
AssertSchemaTableStructure (schema, "#D:");
Assert.AreEqual (3, schema.Rows.Count, "#D:RowCount");
pkRow = schema.Select ("ColumnName = 'id'") [0];
Assert.IsFalse (pkRow.IsNull ("ColumnName"), "#D:ColumnName_IsNull");
Assert.AreEqual ("id", pkRow ["ColumnName"], "#D:ColumnName_Value");
Assert.IsFalse (pkRow.IsNull ("ColumnOrdinal"), "#D:ColumnOrdinal_IsNull");
Assert.AreEqual (0, pkRow ["ColumnOrdinal"], "#D:ColumnOrdinal_Value");
Assert.IsFalse (pkRow.IsNull ("ColumnSize"), "#D:ColumnSize_IsNull");
Assert.AreEqual (4, pkRow ["ColumnSize"], "#D:ColumnSize_Value");
Assert.IsFalse (pkRow.IsNull ("NumericPrecision"), "#D:NumericPrecision_IsNull");
Assert.AreEqual (10, pkRow ["NumericPrecision"], "#D:NumericPrecision_Value");
Assert.IsFalse (pkRow.IsNull ("NumericScale"), "#D:NumericScale_IsNull");
Assert.AreEqual (255, pkRow ["NumericScale"], "#D:NumericScale_Value");
Assert.IsFalse (pkRow.IsNull ("DataType"), "#D:DataType_IsNull");
Assert.AreEqual (typeof (int), pkRow ["DataType"], "#D:DataType_Value");
Assert.IsFalse (pkRow.IsNull ("ProviderType"), "#D:ProviderType_IsNull");
Assert.AreEqual (8, pkRow ["ProviderType"], "#D:ProviderType_Value");
Assert.IsFalse (pkRow.IsNull ("IsLong"), "#D:IsLong_IsNull");
Assert.AreEqual (false, pkRow ["IsLong"], "#D:IsLong_Value");
Assert.IsFalse (pkRow.IsNull ("AllowDBNull"), "#D:AllowDBNull_IsNull");
Assert.AreEqual (false, pkRow ["AllowDBNull"], "#D:AllowDBNull_Value");
Assert.IsFalse (pkRow.IsNull ("IsReadOnly"), "#D:IsReadOnly_IsNull");
Assert.AreEqual (false, pkRow ["IsReadOnly"], "#D:IsReadOnly_Value");
Assert.IsFalse (pkRow.IsNull ("IsRowVersion"), "#D:IsRowVersion_IsNull");
Assert.AreEqual (false, pkRow ["IsRowVersion"], "#D:IsRowVersion_Value");
Assert.IsFalse (pkRow.IsNull ("IsUnique"), "#D:IsUnique_IsNull");
Assert.AreEqual (false, pkRow ["IsUnique"], "#D:IsUnique_Value");
Assert.IsTrue (pkRow.IsNull ("IsKey"), "#D:IsKey_IsNull");
Assert.AreEqual (DBNull.Value, pkRow ["IsKey"], "#D:IsKey_Value");
Assert.IsFalse (pkRow.IsNull ("IsAutoIncrement"), "#D:IsAutoIncrement_IsNull");
Assert.AreEqual (false, pkRow ["IsAutoIncrement"], "#D:IsAutoIncrement_Value");
Assert.IsTrue (pkRow.IsNull ("BaseSchemaName"), "#D:BaseSchemaName_IsNull");
Assert.AreEqual (DBNull.Value, pkRow ["BaseSchemaName"], "#D:BaseSchemaName_Value");
Assert.IsTrue (pkRow.IsNull ("BaseCatalogName"), "#D:BaseCatalogName_IsNull");
Assert.AreEqual (DBNull.Value, pkRow ["BaseCatalogName"], "#D:BaseCatalogName_Value");
Assert.IsTrue (pkRow.IsNull ("BaseTableName"), "#D:BaseTableName_IsNull");
Assert.AreEqual (DBNull.Value, pkRow ["BaseTableName"], "#D:BaseTableName_Value");
Assert.IsFalse (pkRow.IsNull ("BaseColumnName"), "#D:BaseColumnName_IsNull");
Assert.AreEqual ("id", pkRow ["BaseColumnName"], "#D:BaseColumnName_Value");
cmd = conn.CreateCommand ();
cmd.CommandText = "select id, fname, id + 20 as plustwenty from employee";
cmd.Prepare ();
reader = cmd.ExecuteReader (CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo);
schema = reader.GetSchemaTable ();
reader.Close ();
AssertSchemaTableStructure (schema, "#E:");
Assert.AreEqual (3, schema.Rows.Count, "#E:RowCount");
pkRow = schema.Select ("ColumnName = 'id'") [0];
Assert.IsFalse (pkRow.IsNull ("ColumnName"), "#E:ColumnName_IsNull");
Assert.AreEqual ("id", pkRow ["ColumnName"], "#E:ColumnName_Value");
Assert.IsFalse (pkRow.IsNull ("ColumnOrdinal"), "#E:ColumnOrdinal_IsNull");
Assert.AreEqual (0, pkRow ["ColumnOrdinal"], "#E:ColumnOrdinal_Value");
Assert.IsFalse (pkRow.IsNull ("ColumnSize"), "#E:ColumnSize_IsNull");
Assert.AreEqual (4, pkRow ["ColumnSize"], "#E:ColumnSize_Value");
Assert.IsFalse (pkRow.IsNull ("NumericPrecision"), "#E:NumericPrecision_IsNull");
Assert.AreEqual (10, pkRow ["NumericPrecision"], "#E:NumericPrecision_Value");
Assert.IsFalse (pkRow.IsNull ("NumericScale"), "#E:NumericScale_IsNull");
Assert.AreEqual (255, pkRow ["NumericScale"], "#E:NumericScale_Value");
Assert.IsFalse (pkRow.IsNull ("DataType"), "#E:DataType_IsNull");
Assert.AreEqual (typeof (int), pkRow ["DataType"], "#E:DataType_Value");
Assert.IsFalse (pkRow.IsNull ("ProviderType"), "#E:ProviderType_IsNull");
Assert.AreEqual (8, pkRow ["ProviderType"], "#E:ProviderType_Value");
Assert.IsFalse (pkRow.IsNull ("IsLong"), "#E:IsLong_IsNull");
Assert.AreEqual (false, pkRow ["IsLong"], "#E:IsLong_Value");
Assert.IsFalse (pkRow.IsNull ("AllowDBNull"), "#E:AllowDBNull_IsNull");
Assert.AreEqual (false, pkRow ["AllowDBNull"], "#E:AllowDBNull_Value");
Assert.IsFalse (pkRow.IsNull ("IsReadOnly"), "#E:IsReadOnly_IsNull");
Assert.AreEqual (false, pkRow ["IsReadOnly"], "#E:IsReadOnly_Value");
Assert.IsFalse (pkRow.IsNull ("IsRowVersion"), "#E:IsRowVersion_IsNull");
Assert.AreEqual (false, pkRow ["IsRowVersion"], "#E:IsRowVersion_Value");
Assert.IsFalse (pkRow.IsNull ("IsUnique"), "#E:IsUnique_IsNull");
Assert.AreEqual (false, pkRow ["IsUnique"], "#E:IsUnique_Value");
Assert.IsFalse (pkRow.IsNull ("IsKey"), "#E:IsKey_IsNull");
Assert.AreEqual (true, pkRow ["IsKey"], "#E:IsKey_Value");
Assert.IsFalse (pkRow.IsNull ("IsAutoIncrement"), "#E:IsAutoIncrement_IsNull");
Assert.AreEqual (false, pkRow ["IsAutoIncrement"], "#E:IsAutoIncrement_Value");
Assert.IsTrue (pkRow.IsNull ("BaseSchemaName"), "#E:BaseSchemaName_IsNull");
Assert.AreEqual (DBNull.Value, pkRow ["BaseSchemaName"], "#E:BaseSchemaName_Value");
Assert.IsTrue (pkRow.IsNull ("BaseCatalogName"), "#E:BaseCatalogName_IsNull");
Assert.AreEqual (DBNull.Value, pkRow ["BaseCatalogName"], "#E:BaseCatalogName_Value");
Assert.IsFalse (pkRow.IsNull ("BaseTableName"), "#E:BaseTableName_IsNull");
Assert.AreEqual ("employee", pkRow ["BaseTableName"], "#E:BaseTableName_Value");
Assert.IsFalse (pkRow.IsNull ("BaseColumnName"), "#E:BaseColumnName_IsNull");
Assert.AreEqual ("id", pkRow ["BaseColumnName"], "#E:BaseColumnName_Value");
reader = cmd.ExecuteReader (CommandBehavior.SchemaOnly);
schema = reader.GetSchemaTable ();
reader.Close ();
AssertSchemaTableStructure (schema, "#F:");
Assert.AreEqual (3, schema.Rows.Count, "#F:RowCount");
pkRow = schema.Select ("ColumnName = 'id'") [0];
Assert.IsFalse (pkRow.IsNull ("ColumnName"), "#F:ColumnName_IsNull");
Assert.AreEqual ("id", pkRow ["ColumnName"], "#F:ColumnName_Value");
Assert.IsFalse (pkRow.IsNull ("ColumnOrdinal"), "#F:ColumnOrdinal_IsNull");
Assert.AreEqual (0, pkRow ["ColumnOrdinal"], "#F:ColumnOrdinal_Value");
Assert.IsFalse (pkRow.IsNull ("ColumnSize"), "#F:ColumnSize_IsNull");
Assert.AreEqual (4, pkRow ["ColumnSize"], "#F:ColumnSize_Value");
Assert.IsFalse (pkRow.IsNull ("NumericPrecision"), "#F:NumericPrecision_IsNull");
Assert.AreEqual (10, pkRow ["NumericPrecision"], "#F:NumericPrecision_Value");
Assert.IsFalse (pkRow.IsNull ("NumericScale"), "#F:NumericScale_IsNull");
Assert.AreEqual (255, pkRow ["NumericScale"], "#F:NumericScale_Value");
Assert.IsFalse (pkRow.IsNull ("DataType"), "#F:DataType_IsNull");
Assert.AreEqual (typeof (int), pkRow ["DataType"], "#F:DataType_Value");
Assert.IsFalse (pkRow.IsNull ("ProviderType"), "#F:ProviderType_IsNull");
Assert.AreEqual (8, pkRow ["ProviderType"], "#F:ProviderType_Value");
Assert.IsFalse (pkRow.IsNull ("IsLong"), "#F:IsLong_IsNull");
Assert.AreEqual (false, pkRow ["IsLong"], "#F:IsLong_Value");
Assert.IsFalse (pkRow.IsNull ("AllowDBNull"), "#F:AllowDBNull_IsNull");
Assert.AreEqual (false, pkRow ["AllowDBNull"], "#F:AllowDBNull_Value");
Assert.IsFalse (pkRow.IsNull ("IsReadOnly"), "#F:IsReadOnly_IsNull");
Assert.AreEqual (false, pkRow ["IsReadOnly"], "#F:IsReadOnly_Value");
Assert.IsFalse (pkRow.IsNull ("IsRowVersion"), "#F:IsRowVersion_IsNull");
Assert.AreEqual (false, pkRow ["IsRowVersion"], "#F:IsRowVersion_Value");
Assert.IsFalse (pkRow.IsNull ("IsUnique"), "#F:IsUnique_IsNull");
Assert.AreEqual (false, pkRow ["IsUnique"], "#F:IsUnique_Value");
Assert.IsTrue (pkRow.IsNull ("IsKey"), "#F:IsKey_IsNull");
Assert.AreEqual (DBNull.Value, pkRow ["IsKey"], "#F:IsKey_Value");
Assert.IsFalse (pkRow.IsNull ("IsAutoIncrement"), "#F:IsAutoIncrement_IsNull");
Assert.AreEqual (false, pkRow ["IsAutoIncrement"], "#F:IsAutoIncrement_Value");
Assert.IsTrue (pkRow.IsNull ("BaseSchemaName"), "#F:BaseSchemaName_IsNull");
Assert.AreEqual (DBNull.Value, pkRow ["BaseSchemaName"], "#F:BaseSchemaName_Value");
Assert.IsTrue (pkRow.IsNull ("BaseCatalogName"), "#F:BaseCatalogName_IsNull");
Assert.AreEqual (DBNull.Value, pkRow ["BaseCatalogName"], "#F:BaseCatalogName_Value");
Assert.IsTrue (pkRow.IsNull ("BaseTableName"), "#F:BaseTableName_IsNull");
Assert.AreEqual (DBNull.Value, pkRow ["BaseTableName"], "#F:BaseTableName_Value");
Assert.IsFalse (pkRow.IsNull ("BaseColumnName"), "#F:BaseColumnName_IsNull");
Assert.AreEqual ("id", pkRow ["BaseColumnName"], "#F:BaseColumnName_Value");
reader = cmd.ExecuteReader (CommandBehavior.KeyInfo);
schema = reader.GetSchemaTable ();
reader.Close ();
AssertSchemaTableStructure (schema, "#G:");
Assert.AreEqual (3, schema.Rows.Count, "#G:RowCount");
pkRow = schema.Select ("ColumnName = 'id'") [0];
Assert.IsFalse (pkRow.IsNull ("ColumnName"), "#G:ColumnName_IsNull");
Assert.AreEqual ("id", pkRow ["ColumnName"], "#G:ColumnName_Value");
Assert.IsFalse (pkRow.IsNull ("ColumnOrdinal"), "#G:ColumnOrdinal_IsNull");
Assert.AreEqual (0, pkRow ["ColumnOrdinal"], "#G:ColumnOrdinal_Value");
Assert.IsFalse (pkRow.IsNull ("ColumnSize"), "#G:ColumnSize_IsNull");
Assert.AreEqual (4, pkRow ["ColumnSize"], "#G:ColumnSize_Value");
Assert.IsFalse (pkRow.IsNull ("NumericPrecision"), "#G:NumericPrecision_IsNull");
Assert.AreEqual (10, pkRow ["NumericPrecision"], "#G:NumericPrecision_Value");
Assert.IsFalse (pkRow.IsNull ("NumericScale"), "#G:NumericScale_IsNull");
Assert.AreEqual (255, pkRow ["NumericScale"], "#G:NumericScale_Value");
Assert.IsFalse (pkRow.IsNull ("DataType"), "#G:DataType_IsNull");
Assert.AreEqual (typeof (int), pkRow ["DataType"], "#G:DataType_Value");
Assert.IsFalse (pkRow.IsNull ("ProviderType"), "#G:ProviderType_IsNull");
Assert.AreEqual (8, pkRow ["ProviderType"], "#G:ProviderType_Value");
Assert.IsFalse (pkRow.IsNull ("IsLong"), "#G:IsLong_IsNull");
Assert.AreEqual (false, pkRow ["IsLong"], "#G:IsLong_Value");
Assert.IsFalse (pkRow.IsNull ("AllowDBNull"), "#G:AllowDBNull_IsNull");
Assert.AreEqual (false, pkRow ["AllowDBNull"], "#G:AllowDBNull_Value");
Assert.IsFalse (pkRow.IsNull ("IsReadOnly"), "#G:IsReadOnly_IsNull");
Assert.AreEqual (false, pkRow ["IsReadOnly"], "#G:IsReadOnly_Value");
Assert.IsFalse (pkRow.IsNull ("IsRowVersion"), "#G:IsRowVersion_IsNull");
Assert.AreEqual (false, pkRow ["IsRowVersion"], "#G:IsRowVersion_Value");
Assert.IsFalse (pkRow.IsNull ("IsUnique"), "#G:IsUnique_IsNull");
Assert.AreEqual (false, pkRow ["IsUnique"], "#G:IsUnique_Value");
Assert.IsFalse (pkRow.IsNull ("IsKey"), "#G:IsKey_IsNull");
Assert.AreEqual (true, pkRow ["IsKey"], "#G:IsKey_Value");
Assert.IsFalse (pkRow.IsNull ("IsAutoIncrement"), "#G:IsAutoIncrement_IsNull");
Assert.AreEqual (false, pkRow ["IsAutoIncrement"], "#G:IsAutoIncrement_Value");
Assert.IsTrue (pkRow.IsNull ("BaseSchemaName"), "#G:BaseSchemaName_IsNull");
Assert.AreEqual (DBNull.Value, pkRow ["BaseSchemaName"], "#G:BaseSchemaName_Value");
Assert.IsTrue (pkRow.IsNull ("BaseCatalogName"), "#G:BaseCatalogName_IsNull");
Assert.AreEqual (DBNull.Value, pkRow ["BaseCatalogName"], "#G:BaseCatalogName_Value");
Assert.IsFalse (pkRow.IsNull ("BaseTableName"), "#G:BaseTableName_IsNull");
Assert.AreEqual ("employee", pkRow ["BaseTableName"], "#G:BaseTableName_Value");
Assert.IsFalse (pkRow.IsNull ("BaseColumnName"), "#G:BaseColumnName_IsNull");
Assert.AreEqual ("id", pkRow ["BaseColumnName"], "#G:BaseColumnName_Value");
reader = cmd.ExecuteReader ();
schema = reader.GetSchemaTable ();
reader.Close ();
AssertSchemaTableStructure (schema, "#H:");
Assert.AreEqual (3, schema.Rows.Count, "#H:RowCount");
pkRow = schema.Select ("ColumnName = 'id'") [0];
Assert.IsFalse (pkRow.IsNull ("ColumnName"), "#H:ColumnName_IsNull");
Assert.AreEqual ("id", pkRow ["ColumnName"], "#H:ColumnName_Value");
Assert.IsFalse (pkRow.IsNull ("ColumnOrdinal"), "#H:ColumnOrdinal_IsNull");
Assert.AreEqual (0, pkRow ["ColumnOrdinal"], "#H:ColumnOrdinal_Value");
Assert.IsFalse (pkRow.IsNull ("ColumnSize"), "#H:ColumnSize_IsNull");
Assert.AreEqual (4, pkRow ["ColumnSize"], "#H:ColumnSize_Value");
Assert.IsFalse (pkRow.IsNull ("NumericPrecision"), "#H:NumericPrecision_IsNull");
Assert.AreEqual (10, pkRow ["NumericPrecision"], "#H:NumericPrecision_Value");
Assert.IsFalse (pkRow.IsNull ("NumericScale"), "#H:NumericScale_IsNull");
Assert.AreEqual (255, pkRow ["NumericScale"], "#H:NumericScale_Value");
Assert.IsFalse (pkRow.IsNull ("DataType"), "#H:DataType_IsNull");
Assert.AreEqual (typeof (int), pkRow ["DataType"], "#H:DataType_Value");
Assert.IsFalse (pkRow.IsNull ("ProviderType"), "#H:ProviderType_IsNull");
Assert.AreEqual (8, pkRow ["ProviderType"], "#H:ProviderType_Value");
Assert.IsFalse (pkRow.IsNull ("IsLong"), "#H:IsLong_IsNull");
Assert.AreEqual (false, pkRow ["IsLong"], "#H:IsLong_Value");
Assert.IsFalse (pkRow.IsNull ("AllowDBNull"), "#H:AllowDBNull_IsNull");
Assert.AreEqual (false, pkRow ["AllowDBNull"], "#H:AllowDBNull_Value");
Assert.IsFalse (pkRow.IsNull ("IsReadOnly"), "#H:IsReadOnly_IsNull");
Assert.AreEqual (false, pkRow ["IsReadOnly"], "#H:IsReadOnly_Value");
Assert.IsFalse (pkRow.IsNull ("IsRowVersion"), "#H:IsRowVersion_IsNull");
Assert.AreEqual (false, pkRow ["IsRowVersion"], "#H:IsRowVersion_Value");
Assert.IsFalse (pkRow.IsNull ("IsUnique"), "#H:IsUnique_IsNull");
Assert.AreEqual (false, pkRow ["IsUnique"], "#H:IsUnique_Value");
Assert.IsTrue (pkRow.IsNull ("IsKey"), "#H:IsKey_IsNull");
Assert.AreEqual (DBNull.Value, pkRow ["IsKey"], "#H:IsKey_Value");
Assert.IsFalse (pkRow.IsNull ("IsAutoIncrement"), "#H:IsAutoIncrement_IsNull");
Assert.AreEqual (false, pkRow ["IsAutoIncrement"], "#H:IsAutoIncrement_Value");
Assert.IsTrue (pkRow.IsNull ("BaseSchemaName"), "#H:BaseSchemaName_IsNull");
Assert.AreEqual (DBNull.Value, pkRow ["BaseSchemaName"], "#H:BaseSchemaName_Value");
Assert.IsTrue (pkRow.IsNull ("BaseCatalogName"), "#H:BaseCatalogName_IsNull");
Assert.AreEqual (DBNull.Value, pkRow ["BaseCatalogName"], "#H:BaseCatalogName_Value");
Assert.IsTrue (pkRow.IsNull ("BaseTableName"), "#H:BaseTableName_IsNull");
Assert.AreEqual (DBNull.Value, pkRow ["BaseTableName"], "#H:BaseTableName_Value");
Assert.IsFalse (pkRow.IsNull ("BaseColumnName"), "#H:BaseColumnName_IsNull");
Assert.AreEqual ("id", pkRow ["BaseColumnName"], "#H:BaseColumnName_Value");
cmd.CommandText = "select id, fname, id + 20 as plustwenty from employee where id = @id";
IDbDataParameter param = cmd.CreateParameter ();
param.ParameterName = "@id";
cmd.Parameters.Add (param);
param.DbType = DbType.Int32;
param.Value = 2;
reader = cmd.ExecuteReader (CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo);
schema = reader.GetSchemaTable ();
reader.Close ();
AssertSchemaTableStructure (schema, "#I:");
Assert.AreEqual (3, schema.Rows.Count, "#I:RowCount");
pkRow = schema.Select ("ColumnName = 'id'") [0];
Assert.IsFalse (pkRow.IsNull ("ColumnName"), "#I:ColumnName_IsNull");
Assert.AreEqual ("id", pkRow ["ColumnName"], "#I:ColumnName_Value");
Assert.IsFalse (pkRow.IsNull ("ColumnOrdinal"), "#I:ColumnOrdinal_IsNull");
Assert.AreEqual (0, pkRow ["ColumnOrdinal"], "#I:ColumnOrdinal_Value");
Assert.IsFalse (pkRow.IsNull ("ColumnSize"), "#I:ColumnSize_IsNull");
Assert.AreEqual (4, pkRow ["ColumnSize"], "#I:ColumnSize_Value");
Assert.IsFalse (pkRow.IsNull ("NumericPrecision"), "#I:NumericPrecision_IsNull");
Assert.AreEqual (10, pkRow ["NumericPrecision"], "#I:NumericPrecision_Value");
Assert.IsFalse (pkRow.IsNull ("NumericScale"), "#I:NumericScale_IsNull");
Assert.AreEqual (255, pkRow ["NumericScale"], "#I:NumericScale_Value");
Assert.IsFalse (pkRow.IsNull ("DataType"), "#I:DataType_IsNull");
Assert.AreEqual (typeof (int), pkRow ["DataType"], "#I:DataType_Value");
Assert.IsFalse (pkRow.IsNull ("ProviderType"), "#I:ProviderType_IsNull");
Assert.AreEqual (8, pkRow ["ProviderType"], "#I:ProviderType_Value");
Assert.IsFalse (pkRow.IsNull ("IsLong"), "#I:IsLong_IsNull");
Assert.AreEqual (false, pkRow ["IsLong"], "#I:IsLong_Value");
Assert.IsFalse (pkRow.IsNull ("AllowDBNull"), "#I:AllowDBNull_IsNull");
Assert.AreEqual (false, pkRow ["AllowDBNull"], "#I:AllowDBNull_Value");
Assert.IsFalse (pkRow.IsNull ("IsReadOnly"), "#I:IsReadOnly_IsNull");
Assert.AreEqual (false, pkRow ["IsReadOnly"], "#I:IsReadOnly_Value");
Assert.IsFalse (pkRow.IsNull ("IsRowVersion"), "#I:IsRowVersion_IsNull");
Assert.AreEqual (false, pkRow ["IsRowVersion"], "#I:IsRowVersion_Value");
Assert.IsFalse (pkRow.IsNull ("IsUnique"), "#I:IsUnique_IsNull");
Assert.AreEqual (false, pkRow ["IsUnique"], "#I:IsUnique_Value");
Assert.IsFalse (pkRow.IsNull ("IsKey"), "#I:IsKey_IsNull");
Assert.AreEqual (true, pkRow ["IsKey"], "#I:IsKey_Value");
Assert.IsFalse (pkRow.IsNull ("IsAutoIncrement"), "#I:IsAutoIncrement_IsNull");
Assert.AreEqual (false, pkRow ["IsAutoIncrement"], "#I:IsAutoIncrement_Value");
Assert.IsTrue (pkRow.IsNull ("BaseSchemaName"), "#I:BaseSchemaName_IsNull");
Assert.AreEqual (DBNull.Value, pkRow ["BaseSchemaName"], "#I:BaseSchemaName_Value");
Assert.IsTrue (pkRow.IsNull ("BaseCatalogName"), "#I:BaseCatalogName_IsNull");
Assert.AreEqual (DBNull.Value, pkRow ["BaseCatalogName"], "#I:BaseCatalogName_Value");
Assert.IsFalse (pkRow.IsNull ("BaseTableName"), "#I:BaseTableName_IsNull");
Assert.AreEqual ("employee", pkRow ["BaseTableName"], "#I:BaseTableName_Value");
Assert.IsFalse (pkRow.IsNull ("BaseColumnName"), "#I:BaseColumnName_IsNull");
Assert.AreEqual ("id", pkRow ["BaseColumnName"], "#I:BaseColumnName_Value");
reader = cmd.ExecuteReader (CommandBehavior.SchemaOnly);
schema = reader.GetSchemaTable ();
reader.Close ();
AssertSchemaTableStructure (schema, "#J:");
Assert.AreEqual (3, schema.Rows.Count, "#J:RowCount");
pkRow = schema.Select ("ColumnName = 'id'") [0];
Assert.IsFalse (pkRow.IsNull ("ColumnName"), "#J:ColumnName_IsNull");
Assert.AreEqual ("id", pkRow ["ColumnName"], "#J:ColumnName_Value");
Assert.IsFalse (pkRow.IsNull ("ColumnOrdinal"), "#J:ColumnOrdinal_IsNull");
Assert.AreEqual (0, pkRow ["ColumnOrdinal"], "#J:ColumnOrdinal_Value");
Assert.IsFalse (pkRow.IsNull ("ColumnSize"), "#J:ColumnSize_IsNull");
Assert.AreEqual (4, pkRow ["ColumnSize"], "#J:ColumnSize_Value");
Assert.IsFalse (pkRow.IsNull ("NumericPrecision"), "#J:NumericPrecision_IsNull");
Assert.AreEqual (10, pkRow ["NumericPrecision"], "#J:NumericPrecision_Value");
Assert.IsFalse (pkRow.IsNull ("NumericScale"), "#J:NumericScale_IsNull");
Assert.AreEqual (255, pkRow ["NumericScale"], "#J:NumericScale_Value");
Assert.IsFalse (pkRow.IsNull ("DataType"), "#J:DataType_IsNull");
Assert.AreEqual (typeof (int), pkRow ["DataType"], "#J:DataType_Value");
Assert.IsFalse (pkRow.IsNull ("ProviderType"), "#J:ProviderType_IsNull");
Assert.AreEqual (8, pkRow ["ProviderType"], "#J:ProviderType_Value");
Assert.IsFalse (pkRow.IsNull ("IsLong"), "#J:IsLong_IsNull");
Assert.AreEqual (false, pkRow ["IsLong"], "#J:IsLong_Value");
Assert.IsFalse (pkRow.IsNull ("AllowDBNull"), "#J:AllowDBNull_IsNull");
Assert.AreEqual (false, pkRow ["AllowDBNull"], "#J:AllowDBNull_Value");
Assert.IsFalse (pkRow.IsNull ("IsReadOnly"), "#J:IsReadOnly_IsNull");
Assert.AreEqual (false, pkRow ["IsReadOnly"], "#J:IsReadOnly_Value");
Assert.IsFalse (pkRow.IsNull ("IsRowVersion"), "#J:IsRowVersion_IsNull");
Assert.AreEqual (false, pkRow ["IsRowVersion"], "#J:IsRowVersion_Value");
Assert.IsFalse (pkRow.IsNull ("IsUnique"), "#J:IsUnique_IsNull");
Assert.AreEqual (false, pkRow ["IsUnique"], "#J:IsUnique_Value");
Assert.IsTrue (pkRow.IsNull ("IsKey"), "#J:IsKey_IsNull");
Assert.AreEqual (DBNull.Value, pkRow ["IsKey"], "#J:IsKey_Value");
Assert.IsFalse (pkRow.IsNull ("IsAutoIncrement"), "#J:IsAutoIncrement_IsNull");
Assert.AreEqual (false, pkRow ["IsAutoIncrement"], "#J:IsAutoIncrement_Value");
Assert.IsTrue (pkRow.IsNull ("BaseSchemaName"), "#J:BaseSchemaName_IsNull");
Assert.AreEqual (DBNull.Value, pkRow ["BaseSchemaName"], "#J:BaseSchemaName_Value");
Assert.IsTrue (pkRow.IsNull ("BaseCatalogName"), "#J:BaseCatalogName_IsNull");
Assert.AreEqual (DBNull.Value, pkRow ["BaseCatalogName"], "#J:BaseCatalogName_Value");
Assert.IsTrue (pkRow.IsNull ("BaseTableName"), "#J:BaseTableName_IsNull");
Assert.AreEqual (DBNull.Value, pkRow ["BaseTableName"], "#J:BaseTableName_Value");
Assert.IsFalse (pkRow.IsNull ("BaseColumnName"), "#J:BaseColumnName_IsNull");
Assert.AreEqual ("id", pkRow ["BaseColumnName"], "#J:BaseColumnName_Value");
reader = cmd.ExecuteReader (CommandBehavior.KeyInfo);
schema = reader.GetSchemaTable ();
reader.Close ();
AssertSchemaTableStructure (schema, "#K:");
Assert.AreEqual (3, schema.Rows.Count, "#K:RowCount");
pkRow = schema.Select ("ColumnName = 'id'") [0];
Assert.IsFalse (pkRow.IsNull ("ColumnName"), "#K:ColumnName_IsNull");
Assert.AreEqual ("id", pkRow ["ColumnName"], "#K:ColumnName_Value");
Assert.IsFalse (pkRow.IsNull ("ColumnOrdinal"), "#K:ColumnOrdinal_IsNull");
Assert.AreEqual (0, pkRow ["ColumnOrdinal"], "#K:ColumnOrdinal_Value");
Assert.IsFalse (pkRow.IsNull ("ColumnSize"), "#K:ColumnSize_IsNull");
Assert.AreEqual (4, pkRow ["ColumnSize"], "#K:ColumnSize_Value");
Assert.IsFalse (pkRow.IsNull ("NumericPrecision"), "#K:NumericPrecision_IsNull");
Assert.AreEqual (10, pkRow ["NumericPrecision"], "#K:NumericPrecision_Value");
Assert.IsFalse (pkRow.IsNull ("NumericScale"), "#K:NumericScale_IsNull");
Assert.AreEqual (255, pkRow ["NumericScale"], "#K:NumericScale_Value");
Assert.IsFalse (pkRow.IsNull ("DataType"), "#K:DataType_IsNull");
Assert.AreEqual (typeof (int), pkRow ["DataType"], "#K:DataType_Value");
Assert.IsFalse (pkRow.IsNull ("ProviderType"), "#K:ProviderType_IsNull");
Assert.AreEqual (8, pkRow ["ProviderType"], "#K:ProviderType_Value");
Assert.IsFalse (pkRow.IsNull ("IsLong"), "#K:IsLong_IsNull");
Assert.AreEqual (false, pkRow ["IsLong"], "#K:IsLong_Value");
Assert.IsFalse (pkRow.IsNull ("AllowDBNull"), "#K:AllowDBNull_IsNull");
Assert.AreEqual (false, pkRow ["AllowDBNull"], "#K:AllowDBNull_Value");
Assert.IsFalse (pkRow.IsNull ("IsReadOnly"), "#K:IsReadOnly_IsNull");
Assert.AreEqual (false, pkRow ["IsReadOnly"], "#K:IsReadOnly_Value");
Assert.IsFalse (pkRow.IsNull ("IsRowVersion"), "#K:IsRowVersion_IsNull");
Assert.AreEqual (false, pkRow ["IsRowVersion"], "#K:IsRowVersion_Value");
Assert.IsFalse (pkRow.IsNull ("IsUnique"), "#K:IsUnique_IsNull");
Assert.AreEqual (false, pkRow ["IsUnique"], "#K:IsUnique_Value");
Assert.IsFalse (pkRow.IsNull ("IsKey"), "#K:IsKey_IsNull");
Assert.AreEqual (true, pkRow ["IsKey"], "#K:IsKey_Value");
Assert.IsFalse (pkRow.IsNull ("IsAutoIncrement"), "#K:IsAutoIncrement_IsNull");
Assert.AreEqual (false, pkRow ["IsAutoIncrement"], "#K:IsAutoIncrement_Value");
Assert.IsTrue (pkRow.IsNull ("BaseSchemaName"), "#K:BaseSchemaName_IsNull");
Assert.AreEqual (DBNull.Value, pkRow ["BaseSchemaName"], "#K:BaseSchemaName_Value");
Assert.IsTrue (pkRow.IsNull ("BaseCatalogName"), "#K:BaseCatalogName_IsNull");
Assert.AreEqual (DBNull.Value, pkRow ["BaseCatalogName"], "#K:BaseCatalogName_Value");
Assert.IsFalse (pkRow.IsNull ("BaseTableName"), "#K:BaseTableName_IsNull");
Assert.AreEqual ("employee", pkRow ["BaseTableName"], "#K:BaseTableName_Value");
Assert.IsFalse (pkRow.IsNull ("BaseColumnName"), "#K:BaseColumnName_IsNull");
Assert.AreEqual ("id", pkRow ["BaseColumnName"], "#K:BaseColumnName_Value");
reader = cmd.ExecuteReader ();
schema = reader.GetSchemaTable ();
reader.Close ();
AssertSchemaTableStructure (schema, "#L:");
Assert.AreEqual (3, schema.Rows.Count, "#L:RowCount");
pkRow = schema.Select ("ColumnName = 'id'") [0];
Assert.IsFalse (pkRow.IsNull ("ColumnName"), "#L:ColumnName_IsNull");
Assert.AreEqual ("id", pkRow ["ColumnName"], "#L:ColumnName_Value");
Assert.IsFalse (pkRow.IsNull ("ColumnOrdinal"), "#L:ColumnOrdinal_IsNull");
Assert.AreEqual (0, pkRow ["ColumnOrdinal"], "#L:ColumnOrdinal_Value");
Assert.IsFalse (pkRow.IsNull ("ColumnSize"), "#L:ColumnSize_IsNull");
Assert.AreEqual (4, pkRow ["ColumnSize"], "#L:ColumnSize_Value");
Assert.IsFalse (pkRow.IsNull ("NumericPrecision"), "#L:NumericPrecision_IsNull");
Assert.AreEqual (10, pkRow ["NumericPrecision"], "#L:NumericPrecision_Value");
Assert.IsFalse (pkRow.IsNull ("NumericScale"), "#L:NumericScale_IsNull");
Assert.AreEqual (255, pkRow ["NumericScale"], "#L:NumericScale_Value");
Assert.IsFalse (pkRow.IsNull ("DataType"), "#L:DataType_IsNull");
Assert.AreEqual (typeof (int), pkRow ["DataType"], "#L:DataType_Value");
Assert.IsFalse (pkRow.IsNull ("ProviderType"), "#L:ProviderType_IsNull");
Assert.AreEqual (8, pkRow ["ProviderType"], "#L:ProviderType_Value");
Assert.IsFalse (pkRow.IsNull ("IsLong"), "#L:IsLong_IsNull");
Assert.AreEqual (false, pkRow ["IsLong"], "#L:IsLong_Value");
Assert.IsFalse (pkRow.IsNull ("AllowDBNull"), "#L:AllowDBNull_IsNull");
Assert.AreEqual (false, pkRow ["AllowDBNull"], "#L:AllowDBNull_Value");
Assert.IsFalse (pkRow.IsNull ("IsReadOnly"), "#L:IsReadOnly_IsNull");
Assert.AreEqual (false, pkRow ["IsReadOnly"], "#L:IsReadOnly_Value");
Assert.IsFalse (pkRow.IsNull ("IsRowVersion"), "#L:IsRowVersion_IsNull");
Assert.AreEqual (false, pkRow ["IsRowVersion"], "#L:IsRowVersion_Value");
Assert.IsFalse (pkRow.IsNull ("IsUnique"), "#L:IsUnique_IsNull");
Assert.AreEqual (false, pkRow ["IsUnique"], "#L:IsUnique_Value");
Assert.IsTrue (pkRow.IsNull ("IsKey"), "#L:IsKey_IsNull");
Assert.AreEqual (DBNull.Value, pkRow ["IsKey"], "#L:IsKey_Value");
Assert.IsFalse (pkRow.IsNull ("IsAutoIncrement"), "#L:IsAutoIncrement_IsNull");
Assert.AreEqual (false, pkRow ["IsAutoIncrement"], "#L:IsAutoIncrement_Value");
Assert.IsTrue (pkRow.IsNull ("BaseSchemaName"), "#L:BaseSchemaName_IsNull");
Assert.AreEqual (DBNull.Value, pkRow ["BaseSchemaName"], "#L:BaseSchemaName_Value");
Assert.IsTrue (pkRow.IsNull ("BaseCatalogName"), "#L:BaseCatalogName_IsNull");
Assert.AreEqual (DBNull.Value, pkRow ["BaseCatalogName"], "#L:BaseCatalogName_Value");
Assert.IsTrue (pkRow.IsNull ("BaseTableName"), "#L:BaseTableName_IsNull");
Assert.AreEqual (DBNull.Value, pkRow ["BaseTableName"], "#L:BaseTableName_Value");
Assert.IsFalse (pkRow.IsNull ("BaseColumnName"), "#L:BaseColumnName_IsNull");
Assert.AreEqual ("id", pkRow ["BaseColumnName"], "#L:BaseColumnName_Value");
} finally {
if (cmd != null)
cmd.Dispose ();
if (reader != null)
reader.Close ();
ConnectionManager.Singleton.CloseConnection ();
}
}
public void GetSchemaTableTest ()
{
cmd.CommandText = "Select type_decimal1 as decimal,id,10 ";
cmd.CommandText += "from numeric_family where id=1";
reader = cmd.ExecuteReader (CommandBehavior.KeyInfo);
DataTable schemaTable = reader.GetSchemaTable ();
DataRow row0 = schemaTable.Rows[0];
DataRow row1 = schemaTable.Rows[1];
Assert.AreEqual ("decimal", row0["ColumnName"], "#1");
Assert.AreEqual ("", schemaTable.Rows[2]["ColumnName"], "#2");
Assert.AreEqual (0, row0["ColumnOrdinal"], "#2");
Assert.AreEqual (17, row0["ColumnSize"], "#3");
if (ClientVersion == 7)
Assert.AreEqual (28, row0["NumericPrecision"], "#4");
else
Assert.AreEqual (38, row0 ["NumericPrecision"], "#4");
Assert.AreEqual (0, row0["NumericScale"], "#5");
Assert.AreEqual (false, row0["IsUnique"], "#6");
// msdotnet returns IsUnique as false for Primary key
// even though table consists of a single Primary Key
//Assert.AreEqual (true, row1["IsUnique"], "#7");
Assert.AreEqual (false, row0["IsKey"], "#8");
Assert.AreEqual (true, row1["IsKey"], "#9");
//Assert.AreEqual ("servername", row0["BaseServerName"], "#10");
//Assert.AreEqual ("monotest", row0["BaseCatalogName"], "#11");
Assert.AreEqual ("type_decimal1", row0["BaseColumnName"], "#12");
//Assert.IsNull(row0["BaseSchemaName"], "#13");
Assert.AreEqual ("numeric_family", row0["BaseTableName"], "#14");
Assert.AreEqual (typeof (Decimal), row0["DataType"], "#15");
Assert.AreEqual (true, row0["AllowDBNull"], "#16");
Assert.AreEqual (false, row1["AllowDBNull"], "#17");
//Assert.IsNull(row0["ProviderType"], "#18");
Assert.AreEqual (true, row0["IsAliased"], "#19");
Assert.AreEqual (false, row1["IsAliased"], "#20");
Assert.AreEqual (false, row0["IsExpression"], "#21");
Assert.AreEqual (false, row0["IsIdentity"], "#22");
Assert.AreEqual (false, row0["IsAutoIncrement"], "#23");
Assert.AreEqual (false, row0["IsRowVersion"], "#24");
Assert.AreEqual (false, row0["IsHidden"], "#25");
Assert.AreEqual (false, row0["IsLong"], "#26");
Assert.AreEqual (false, row0["IsReadOnly"], "#27");
Assert.AreEqual (true, schemaTable.Rows[2]["IsReadOnly"], "#27");
// Test Exception is thrown when reader is closed
reader.Close ();
try {
reader.GetSchemaTable ();
Assert.Fail ("#28 Exception shud be thrown" );
} catch (InvalidOperationException e) {
Assert.AreEqual (typeof (InvalidOperationException), e.GetType(),
"#29 Incorrect Exception");
}
}
private static List<String[]> getTableStringArray(SqlDataReader reader, SqlString PrintOnlyColumnNameAliasList)
{
DataTable schema = reader.GetSchemaTable();
List<String[]> results = new List<string[]>();
int numCols = schema.Rows.Count;
if (PrintOnlyColumnNameAliasList.ToString().Equals(""))
{
String[] header = new String[numCols];
for (int i = 0; i < numCols; i++)
{
DataRow row = schema.Rows[i];
header[i] = (row["ColumnName"].ToString());
}
results.Add(header);
}
else
{
var colListArray = SplitColumnNameList(ref PrintOnlyColumnNameAliasList);
var unquotedColList = new List<string>();
foreach(var colName in colListArray)
{
if (colName.Length != 0)
{
unquotedColList.Add(colName.Replace("]]", "]"));
}
}
results.Add(unquotedColList.ToArray());
}
while (reader.Read()) {
String[] rowData = new String[numCols];
for (int i = 0; i < reader.FieldCount; i++) {
if (reader.IsDBNull(i)) {
rowData[i] = NULL_STRING;
} else {
SqlDbType dbType = (SqlDbType)schema.Rows[i]["ProviderType"];
switch (dbType) {
case SqlDbType.Date:
rowData[i] = SqlDateToString(reader.GetDateTime(i));
break;
case SqlDbType.SmallDateTime:
rowData[i] = SmallDateTimeToString(reader.GetDateTime(i));
break;
case SqlDbType.DateTime:
rowData[i] = SqlDateTimeToString(reader.GetDateTime(i));
break;
case SqlDbType.DateTime2:
rowData[i] = SqlDateTime2ToString(reader.GetDateTime(i));
break;
case SqlDbType.DateTimeOffset:
rowData[i] = SqlDateTimeOffsetToString(reader.GetDateTimeOffset(i));
break;
case SqlDbType.Decimal:
rowData[i] = reader.GetSqlDecimal(i).ToString();
break;
case SqlDbType.Float:
rowData[i] = reader.GetSqlDouble(i).Value.ToString("0.000000000000000E+0");
break;
case SqlDbType.Timestamp:
case SqlDbType.Image:
case SqlDbType.VarBinary:
rowData[i] = SqlBinaryToString(reader.GetSqlBinary(i));
break;
default:
rowData[i] = reader.GetValue(i).ToString();
break;
}
}
}
results.Add(rowData);
}
return results;
}
private static void processSchemaInfo(SqlDataReader reader, FlexResult result)
{
try
{
result.schema = SQLColumnList.CreateFromSchemaTable(reader.GetSchemaTable());
}
catch (Exception ex)
{
result.exceptions.Add(ex);
}
}
/// <summary>
/// Populates instance of SqlResults using rows from SqlDataReader.
/// </summary>
/// <param name="reader">SqlDataReader whose rows are to be read.</param>
internal void Fetch(SqlDataReader reader)
{
do
{
if (reader.FieldCount > 0)
{
SqlResultType resultType = SqlResults.SqlResultTypeFromColumnName(reader.GetSchemaTable().Rows[1]["ColumnName"].ToString());
switch (resultType)
{
case SqlResultType.ShardMap:
while (reader.Read())
{
_ssm.Add(new SqlShardMap(reader, 1));
}
break;
case SqlResultType.Shard:
while (reader.Read())
{
_ss.Add(new SqlShard(reader, 1));
}
break;
case SqlResultType.ShardMapping:
while (reader.Read())
{
_sm.Add(new SqlMapping(reader, 1));
}
break;
case SqlResultType.ShardLocation:
while (reader.Read())
{
_sl.Add(new SqlLocation(reader, 1));
}
break;
case SqlResultType.SchemaInfo:
while (reader.Read())
{
_si.Add(new SqlSchemaInfo(reader, 1));
}
break;
case SqlResultType.StoreVersion:
while (reader.Read())
{
_version = new SqlVersion(reader, 1);
}
break;
case SqlResultType.Operation:
while (reader.Read())
{
_ops.Add(new SqlLogEntry(reader, 1));
}
break;
default:
// This code is unreachable, since the all values of the SqlResultType enum are explicitly handled above.
Debug.Assert(false);
break;
}
}
}
while (reader.NextResult());
}
private static string GenerateSchema(SqlDataReader reader,
out SqlParameter[] paramList)
{
System.Text.StringBuilder colDefs = new System.Text.StringBuilder();
paramList = new SqlParameter[reader.FieldCount];
int i = 0;
using (DataTable schema = reader.GetSchemaTable()) {
bool first = true;
foreach (DataRowView col in schema.DefaultView) {
bool isLong = (bool)col["IsLong"];
// First set up the parameter list entry; keep the parsed enum
// around so that we can use it later.
paramList[i] = new SqlParameter();
paramList[i].ParameterName = string.Format("@col{0}", i);
// This makes me cry, but if there is actually a better way to
// do this, it's hard to find. Extra crying for the try/catches
// with empty catch blocks.
string dbtStr = (string)col["DataTypeName"];
if (dbtStr.ToLower() == "sql_variant") {
// The parse-the-string-as-enum-value trick works except
// that sql_variant becomes Variant. Close. However,
// sql_variant types seem to be difficult to handle
// properly and thus are not supported.
throw new System.Exception("Columns of type sql_variant "
+ "are not supported by SqlSucker at this time");
}
SqlDbType dbt = (SqlDbType)System.Enum.Parse(typeof(SqlDbType),
dbtStr, true);
if (isLong) {
// Clearly, when we have a long type, the SQL server should
// keep calling it one thing, while we use a different enum
// value for SqlParameter (with the name of a deprecated
// data type!). Anything else would just make too much
// sense!
switch (dbt) {
case SqlDbType.NVarChar:
paramList[i].SqlDbType = SqlDbType.NText;
break;
case SqlDbType.VarChar:
paramList[i].SqlDbType = SqlDbType.Text;
break;
case SqlDbType.VarBinary:
paramList[i].SqlDbType = SqlDbType.Image;
break;
default:
paramList[i].SqlDbType = dbt;
break;
}
} else {
paramList[i].SqlDbType = dbt;
}
paramList[i].Direction = ParameterDirection.Input;
// So the story behind the rest of these is that they don't
// really have to be set for all data types, but it seems
// exceedingly horrible to exhaustively figure out which
// situations they're required for. Instead, we try to set
// them and ignore if it doesn't work. If something went wrong
// that matters, outCmd.Prepare() will throw in CopyReader()
// anyway.
try {
// XXX: Apparently long datatypes set ColumnSize to 2^31-1.
// This is fine for varchar and binary, but nvarchar needs
// it to be 2^30-1. So hack around that.
if (dbt == SqlDbType.Xml) {
// Um, this appears to magically work.
paramList[i].Size = -1;
} else if (isLong && dbt == SqlDbType.NVarChar) {
paramList[i].Size = ((int)col["ColumnSize"])/2;
} else {
paramList[i].Size = (int)col["ColumnSize"];
}
} catch { }
try {
paramList[i].Precision =
(byte)((System.Int16)col["NumericPrecision"]);
} catch { }
try {
paramList[i].Scale =
(byte)((System.Int16)col["NumericScale"]);
} catch { }
if (!first) {
colDefs.Append(", ");
}
string typeParams = "";
// Ew. Gross.
System.Type dt =
(System.Type)col["ProviderSpecificDataType"];
if (dt == typeof(SqlString) || dt == typeof(SqlBinary)) {
if (isLong) {
switch (dbt) {
case SqlDbType.Image:
case SqlDbType.NText:
case SqlDbType.Text:
// These are deprecated type names that are
// identical to varbinary(max), nvarchar(max)
// and varchar(max) (respectively), except that
// you don't have to specify a size. In fact, if
// you do, then it croaks with an error.
break;
default:
typeParams = "(max)";
break;
}
} else {
switch (dbt) {
case SqlDbType.Timestamp:
// This turns into a varbinary(8)
typeParams = "(8)";
break;
default:
typeParams = string.Format("({0})",
col["ColumnSize"]);
break;
}
}
} else if (dt == typeof(SqlDecimal)) {
typeParams = string.Format("({0},{1})",
col["NumericPrecision"], col["NumericScale"]);
}
if (col["ColumnName"].ToString().Length == 0) {
throw new System.Exception(string.Format(
"Result set column {0} needs to have a name",
col["ColumnOrdinal"]));
}
// Magical special cases that need to be handled
switch (dbt) {
case SqlDbType.Timestamp:
// timestamp is probably not what we actually want to use
// in the results because it'll become a timestamp for the
// new temporary table. Instead, store what the value was
// in a varbinary(8) column (the 8 is set above).
dbtStr = "varbinary";
break;
}
colDefs.AppendFormat("[{0}] {1}{2}",
col["ColumnName"].ToString().Replace("]", "]]"),
dbtStr, typeParams);
if (sendDebugInfo)
SqlContext.Pipe.Send(col["ColumnName"].ToString()+" "
+dbtStr+" size "+col["ColumnSize"]);
first = false;
i++;
}
}
return colDefs.ToString();
}
/// <summary>
/// Asynchronously populates instance of SqlResults using rows from SqlDataReader.
/// </summary>
/// <param name="reader">SqlDataReader whose rows are to be read.</param>
/// <returns>A task to await read completion</returns>
internal async Task FetchAsync(SqlDataReader reader)
{
Func<Action, Task> ReadAsync = async (readAction) =>
{
while (await reader.ReadAsync())
{
readAction();
}
};
do
{
if (reader.FieldCount > 0)
{
SqlResultType resultType = SqlResults.SqlResultTypeFromColumnName(reader.GetSchemaTable().Rows[1]["ColumnName"].ToString());
switch (resultType)
{
case SqlResultType.ShardMap:
await ReadAsync(() => _ssm.Add(new SqlShardMap(reader, 1)));
break;
case SqlResultType.Shard:
await ReadAsync(() => _ss.Add(new SqlShard(reader, 1)));
break;
case SqlResultType.ShardMapping:
await ReadAsync(() => _sm.Add(new SqlMapping(reader, 1)));
break;
case SqlResultType.ShardLocation:
await ReadAsync(() => _sl.Add(new SqlLocation(reader, 1)));
break;
case SqlResultType.SchemaInfo:
await ReadAsync(() => _si.Add(new SqlSchemaInfo(reader, 1)));
break;
case SqlResultType.StoreVersion:
await ReadAsync(() => _version = new SqlVersion(reader, 1));
break;
case SqlResultType.Operation:
await ReadAsync(() => _ops.Add(new SqlLogEntry(reader, 1)));
break;
default:
// This code is unreachable, since the all values of the SqlResultType enum are explicitly handled above.
Debug.Assert(false);
break;
}
}
}
while (await reader.NextResultAsync());
}
private static void SendTable(SqlDataReader reader, TRowSetMap Map)
{
//SqlDataRecord ReadRecord = new SqlDataRecord(DataReaderFields(reader));
DataTable LDataTable = reader.GetSchemaTable();
SqlDataRecord WriteRecord;
List<TFieldAlias> Fields = new List<TFieldAlias>();
TFieldAlias Field;
string FieldName;
int FieldCount = reader.FieldCount, WriteFieldCount = 0;
int i;
SqlMetaData[] WriteFields;
if(Map.Fields.Length > 0)
{
WriteFields = new SqlMetaData[0];
foreach (string FieldMap in Map.Fields.Split(new char[] {','}))
{
i = FieldMap.IndexOf('=');
if(i >= 0)
{
Field.Name = FieldMap.Substring(0, i);
FieldName = FieldMap.Substring(i + 1);
}
else
{
Field.Name = FieldMap;
FieldName = FieldMap;
}
for(i = 0; i < FieldCount; i++)
{
if(FieldName.ToUpper() == reader.GetName(i).ToUpper())
break;
}
if((i < 0) || (i >= FieldCount))
throw new SystemException("RowSet Field = [" + FieldName + "] not found.");
Field.FieldIndex = i;
Fields.Add(Field);
Array.Resize(ref WriteFields, ++WriteFieldCount);
//WriteFields[WriteFieldCount - 1] = SqlMetaData(LDataTable.Rows[WriteFieldCount - 1], Field.Name);
WriteFields[WriteFieldCount - 1] = SqlMetaData(LDataTable.Rows[Field.FieldIndex], Field.Name);
}
}
else
{
WriteFields = new SqlMetaData[FieldCount];
for (; WriteFieldCount < reader.FieldCount; WriteFieldCount++)
WriteFields[WriteFieldCount] = SqlMetaData(LDataTable.Rows[WriteFieldCount]);
}
WriteRecord = new SqlDataRecord(WriteFields);
try
{
SqlContext.Pipe.SendResultsStart(WriteRecord);
Object[] values = new Object[FieldCount];
while (reader.Read())
{
reader.GetValues(values);
if(Map.Fields.Length > 0)
{
for(i = 0; i < WriteFieldCount; i++)
WriteRecord.SetValue(i, values[Fields[i].FieldIndex]);
}
else
{
WriteRecord.SetValues(values);
}
SqlContext.Pipe.SendResultsRow(WriteRecord);
}
}
finally
{
SqlContext.Pipe.SendResultsEnd();
}
}
protected override DataTable GetSchemaTable(DbCommand srcCommand)
{
using (SqlDataReader rdr = (SqlDataReader)srcCommand.ExecuteReader(CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly))
return(rdr.GetSchemaTable());
}
/// <summary>
/// Instantiate the class by passing a SQL command string, optionally enables getting schema information,
/// pass a connection string name.
/// </summary>
/// <example>
/// <code>
/// using Mezzocode.Halide3;
/// ...
/// h3Reader reader = new h3Reader("select * from test", true, "Halide");
/// </code>
/// </example>
/// <param name="CommandText">A SQL command to execute.</param>
/// <param name="GetSchema">Determines if schema information should also be retured, enabling various other methods and properties.</param>
/// <param name="connectionName">Name of a connection string within the Web.Config file.</param>
public h3Reader(string CommandText, bool GetSchema, string connectionName)
{
SqlCommandString = CommandText;
try
{
ConnectionStringName = connectionName;
cn = new SqlConnection();
cn.ConnectionString = ConnectionString;
cn.Open();
cmd = new SqlCommand();
cmd.CommandTimeout = 0;
cmd.Connection = cn;
cmd.CommandText = CommandText;
if (GetSchema)
{
dr = cmd.ExecuteReader(CommandBehavior.SchemaOnly);
schemaTable = dr.GetSchemaTable();
dr.Close();
schemaAvailable = true;
}
dr = cmd.ExecuteReader();
}
catch (Exception err)
{
_lastSqlError = err;
}
}
public virtual bool GetInfo(SqlDataReader reader)
{
try
{
Type objType = this.GetObjectType();
PropertyInfo[] fields = objType.GetProperties();
_item.Clear();
foreach (PropertyInfo field in fields)
{
if (IsReservedKey(field.Name)) continue;
if (reader.GetSchemaTable().Select("columnname='" + field.Name + "'").Length == 0)
{
string url = string.Empty;
if (System.Web.HttpContext.Current != null) url = "\r\n[" + System.Web.HttpContext.Current.Request.UserHostAddress + "]" + System.Web.HttpContext.Current.Request.Url.ToString();
//LOG.Trace(LOG.ST.Day, "BaseObject.GetInfo_reader", string.Format("���ݿ�����[{0}]��[{1}]IDΪ[{2}]�ļ�¼��ȡ�쳣", this.ConnectString, this.TableName, this.ID));
continue;
}
object val = reader[field.Name];
if (field.PropertyType == typeof(DateTime) && val == DBNull.Value)
{
this[field.Name] = new DateTime(1970, 1, 1, 8, 0, 0);
}
if (val == DBNull.Value)
{
//LOG.Trace(LOG.ST.Day, "BaseObject.GetInfo_reader", string.Format("���ݿ�����[{0}]��[{1}]IDΪ[{2}]�ļ�¼�ֶ�[{3}]ΪNULL��", this.ConnectString, this.TableName, this.ID, field.Name));
continue;
}
if (field.PropertyType == typeof(double))
{
this[field.Name] = double.Parse(val.ToString());
}
else if (field.PropertyType == typeof(decimal))
{
this[field.Name] = decimal.Parse(val.ToString());
}
else
{
this[field.Name] = reader[field.Name];
}
}
}
catch (Exception ex)
{
RaiseError(ex, "BaseObject.GetInfo_reader", string.Format("���ݿ�����[{0}]��[{1}]IDΪ[{2}]�ļ�¼��ȡ�쳣", this.ConnectString, this.TableName, this.ID));
return false;
}
return true;
}
/// <summary>
/// Maps from data reader.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="reader">The reader.</param>
/// <param name="closeReader">if set to <c>true</c> [close reader].</param>
/// <returns></returns>
protected List <T> MapFromDataReader <T>(ref System.Data.SqlClient.SqlDataReader reader, bool closeReader)
{
method = MethodBase.GetCurrentMethod();
try
{
Type newType = typeof(T);
if (_currentType != newType)
{
_currentType = newType;
FindColumnNames(_currentType);
}
List <T> returnList = new List <T>();
object[] values = new object[reader.FieldCount];
Dictionary <string, int> columnsIds = new Dictionary <string, int>(reader.FieldCount);
List <string> schemaFieldsName = null;
while (reader.Read())
{
reader.GetValues(values);
T objTarget = Activator.CreateInstance <T>();
if (schemaFieldsName == null)
{
using (DataTable dtSchema = reader.GetSchemaTable())
{
schemaFieldsName = dtSchema.AsEnumerable().Select(x => x.Field <string>("ColumnName").ToUpper()).ToList();
}
}
foreach (MemberInfo member in _PropertiesToColumnsNames.Keys)
{
string columnName = _PropertiesToColumnsNames[member];
if (schemaFieldsName.Contains(columnName.ToUpper()))
{
int columnId = 0;
if (columnsIds.ContainsKey(columnName))
{
columnId = columnsIds[columnName];
}
else
{
columnId = reader.GetOrdinal(columnName);
columnsIds.Add(columnName, columnId);
}
PropertyInfo pMember = (PropertyInfo)member;
object value;
try
{ value = ConvertValueToType(values[columnId], pMember.PropertyType); }
catch { value = values[columnId]; }
pMember.SetValue(objTarget, value, null);
}
}
returnList.Add(objTarget);
}
if (closeReader)
{
reader.Dispose();
}
return(returnList.Count > 0 ? returnList : null);
}
catch (Exception ex)
{
log.escribirError(ex.Message, stackTrace.GetFrame(1).GetMethod().Name, method.Name, method.ReflectedType.Name);
throw new Exception(ex.InnerException.ToString());
}
finally
{
if (!reader.IsClosed)
{
reader.Close();
}
reader.Dispose();
}
}
public ApiResponse TableDefs()
{
DataSet dsSchemaExport = new DataSet();
Dictionary <string, object> defs = new Dictionary <string, object>();
List <string> cols = new List <string>();
System.Data.SqlClient.SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["AgmDataContext"].ConnectionString);
try
{
conn.Open();
System.Data.SqlClient.SqlCommand command =
new System.Data.SqlClient.SqlCommand("select * from utenti where idutente=21", conn);
System.Data.SqlClient.SqlDataReader sqlreader = command.ExecuteReader();
var schemaTable = sqlreader.GetSchemaTable();
dsSchemaExport.Tables.Add(Add(conn, "utenti"));
foreach (System.Data.DataRow col in schemaTable.Rows)
{
cols.Add(string.Format("{0} - {1}", col["ColumnName"], col["IsIdentity"]));
}
defs.Add("utenti", cols);
sqlreader.Read();
object[] resUtenti = new object[cols.Count];
sqlreader.GetValues(resUtenti);
defs.Add("utenti example", JsonConvert.SerializeObject(resUtenti));
sqlreader.Close();
command = new System.Data.SqlClient.SqlCommand("UPDATE utenti set utente=email where utente is NULL;", conn);
var resupd = command.ExecuteNonQuery();
defs.Add("update result1", resupd);
//command = new System.Data.SqlClient.SqlCommand("insert into rappcausali (idcausale,nome) values (10,'Permessi ex-festività')", conn);
//var resAlter = command.ExecuteNonQuery();
//defs.Add("update result1", resAlter);
//command = new System.Data.SqlClient.SqlCommand("ALTER TABLE utenti ADD isDeleted bit DEFAULT 0 NOT NULL", conn);
//var resAlter = command.ExecuteNonQuery();
//defs.Add("update result1", resAlter);
//command = new System.Data.SqlClient.SqlCommand("update utenti set utenti=1 where idutente=21", conn);
//var resAlter = command.ExecuteNonQuery();
//defs.Add("update result1", resAlter);
//command = new System.Data.SqlClient.SqlCommand("update utenti set email='*****@*****.**' where idutente=21", conn);
//var resAlter = command.ExecuteNonQuery();
//defs.Add("update result1", resAlter);
//command = new System.Data.SqlClient.SqlCommand("update utenti set email='*****@*****.**' where idutente=4", conn);
//resAlter = command.ExecuteNonQuery();
//defs.Add("update result2", resAlter);
//command = new System.Data.SqlClient.SqlCommand("update utenti set email='*****@*****.**' where idutente=3", conn);
//resAlter = command.ExecuteNonQuery();
//defs.Add("update result3", resAlter);
cols = new List <string>();
command = new System.Data.SqlClient.SqlCommand("select TOP 1 * from rappore where idutente=38", conn);
sqlreader = command.ExecuteReader();
schemaTable = sqlreader.GetSchemaTable();
dsSchemaExport.Tables.Add(Add(conn, "rappore"));
foreach (System.Data.DataRow col in schemaTable.Rows)
{
cols.Add(string.Format("{0} - {1}", col["ColumnName"], col["IsIdentity"]));
}
defs.Add("rappore", cols);
sqlreader.Close();
cols = new List <string>();
command = new System.Data.SqlClient.SqlCommand("select * from rappcausali", conn);
sqlreader = command.ExecuteReader();
schemaTable = sqlreader.GetSchemaTable();
dsSchemaExport.Tables.Add(Add(conn, "rappcausali"));
foreach (System.Data.DataRow col in schemaTable.Rows)
{
cols.Add(string.Format("{0} - {1}", col["ColumnName"], col["IsIdentity"]));
}
defs.Add("rappcausali", cols);
sqlreader.Close();
cols = new List <string>();
command = new System.Data.SqlClient.SqlCommand("select * from rappcausalispese", conn);
sqlreader = command.ExecuteReader();
schemaTable = sqlreader.GetSchemaTable();
dsSchemaExport.Tables.Add(Add(conn, "rappcausalispese"));
foreach (System.Data.DataRow col in schemaTable.Rows)
{
cols.Add(string.Format("{0} - {1}", col["ColumnName"], col["IsIdentity"]));
}
defs.Add("rappcausalispese", cols);
sqlreader.Close();
cols = new List <string>();
command = new System.Data.SqlClient.SqlCommand("select TOP 1 * from rappspese where idutente=38", conn);
sqlreader = command.ExecuteReader();
schemaTable = sqlreader.GetSchemaTable();
dsSchemaExport.Tables.Add(Add(conn, "rappspese"));
foreach (System.Data.DataRow col in schemaTable.Rows)
{
cols.Add(string.Format("{0} - {1}", col["ColumnName"], col["IsIdentity"]));
}
defs.Add("rappspese", cols);
sqlreader.Close();
cols = new List <string>();
command = new System.Data.SqlClient.SqlCommand("select TOP 1 * from rappdescrizioni where idutente=38", conn);
sqlreader = command.ExecuteReader();
schemaTable = sqlreader.GetSchemaTable();
dsSchemaExport.Tables.Add(Add(conn, "rappdescrizioni"));
foreach (System.Data.DataRow col in schemaTable.Rows)
{
cols.Add(string.Format("{0} - {1}", col["ColumnName"], col["IsIdentity"]));
}
defs.Add("rappdescrizioni", cols);
sqlreader.Close();
cols = new List <string>();
command = new System.Data.SqlClient.SqlCommand("select TOP 1 * from rappfestivi", conn);
sqlreader = command.ExecuteReader();
schemaTable = sqlreader.GetSchemaTable();
dsSchemaExport.Tables.Add(Add(conn, "rappfestivi"));
foreach (System.Data.DataRow col in schemaTable.Rows)
{
cols.Add(string.Format("{0} - {1}", col["ColumnName"], col["IsIdentity"]));
}
defs.Add("rappfestivi", cols);
sqlreader.Read();
defs.Add("rappfestivi example", sqlreader[1].ToString());
sqlreader.Close();
cols = new List <string>();
command = new System.Data.SqlClient.SqlCommand("select TOP 1 * from annunci", conn);
sqlreader = command.ExecuteReader();
schemaTable = sqlreader.GetSchemaTable();
dsSchemaExport.Tables.Add(Add(conn, "annunci"));
foreach (System.Data.DataRow col in schemaTable.Rows)
{
cols.Add(string.Format("{0} [{1}({2})] - {3}", col["ColumnName"], col["DataTypeName"], col["ColumnSize"], col["IsIdentity"]));
}
defs.Add("annunci", cols);
sqlreader.Read();
defs.Add("annunci example", sqlreader[4].ToString());
sqlreader.Close();
cols = new List <string>();
command = new System.Data.SqlClient.SqlCommand("select TOP 1 * from rappvociretributive", conn);
sqlreader = command.ExecuteReader();
schemaTable = sqlreader.GetSchemaTable();
dsSchemaExport.Tables.Add(Add(conn, "rappvociretributive"));
foreach (System.Data.DataRow col in schemaTable.Rows)
{
cols.Add(string.Format("{0} [{1}({2})] - {3}", col["ColumnName"], col["DataTypeName"], col["ColumnSize"], col["IsIdentity"]));
}
defs.Add("rappvociretributive", cols);
sqlreader.Close();
dsSchemaExport.WriteXml(HttpContext.Current.Server.MapPath("~/App_Data/tables.xml"), XmlWriteMode.WriteSchema);
dsSchemaExport.WriteXmlSchema(HttpContext.Current.Server.MapPath("~/App_Data/tables_schema.xsd"));
conn.Close();
}
catch (Exception e)
{
if (conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
}
return(new ApiResponse(false)
{
Errors = new ApiResponseError[] { new ApiResponseError()
{
Message = e.Message
} }
});
}
return(new ApiResponse(true)
{
Data = defs
});
}
// FAZ A CONVERSÃO DO DATAREADER LISTADO ACIMA EM UM DATATABLE
private DataTable eventoReaderToDT(SqlDataReader reader)
{
DataTable tbEsquema = reader.GetSchemaTable();
DataTable tbRetorno = new DataTable();
foreach (DataRow r in tbEsquema.Rows)
{
if (!tbRetorno.Columns.Contains(r["ColumnName"].ToString()))
{
DataColumn col = new DataColumn()
{
ColumnName = r["ColumnName"].ToString(),
Unique = Convert.ToBoolean(r["IsUnique"]),
AllowDBNull = Convert.ToBoolean(r["AllowDBNull"]),
ReadOnly = Convert.ToBoolean(r["IsReadOnly"])
};
tbRetorno.Columns.Add(col);
}
}
while (reader.Read())
{
DataRow novaLinha = tbRetorno.NewRow();
for (int i = 0; i < tbRetorno.Columns.Count; i++)
{
novaLinha[i] = reader.GetValue(i);
}
tbRetorno.Rows.Add(novaLinha);
}
return tbRetorno;
}
public override void VerifyDataStructure()
{
string tableName = TableName;
sql.SqlConnection conn = createConnection();
sql.SqlCommand cmd = new SqlCommand(
@"IF EXISTS(SELECT * FROM sysobjects
WHERE id = object_id(N'[dbo].["
+ tableName +
@"]')
AND (OBJECTPROPERTY(id, N'IsUserTable') = 1))
SELECT '1'
ELSE
SELECT '0'",
conn);
bool tableExists = false;
try
{
conn.Open();
tableExists = cmd.ExecuteScalar().ToString() == "1";
if (tableExists)
{
#region alter table
cmd.CommandText = "SELECT * FROM [" + tableName + "]";
sql.SqlDataReader r = null;
DataTable tbSchema = null;
try
{
r = cmd.ExecuteReader();
if (r.HasRows)
{
tbSchema = r.GetSchemaTable();
}
}
finally
{
if (r != null)
{
r.Close();
}
}
if (tbSchema == null)
{
cmd.CommandText = "DROP TABLE [" + tableName + "];" + this.getCreateSql(tableName);
cmd.ExecuteNonQuery();
}
else
{
//сначала сравним и только если разные - тогда перезапись таблицы
bool hasChanged = false;
for (int i = 0; i < this.Data.Columns.Count; i++)
{
DataRow[] rs = tbSchema.Select("ColumnName='" + this.Data.Columns[i].ColumnName + "'");
if (rs.Length > 0)
{
if (rs[0]["DataType"].ToString() != this.Data.Columns[i].DataType.ToString())
{
hasChanged = true;
break;
}
}
else
{
hasChanged = true;
break;
}
}
for (int i = 0; i < tbSchema.Rows.Count; i++)
{
if (!this.Data.Columns.Contains(tbSchema.Rows[i]["ColumnName"].ToString()))
{
hasChanged = true;
break;
}
}
if (hasChanged)
{
StringBuilder sb = new StringBuilder();
sb.Append(
@"BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION;");
sb.Append(this.getCreateSql("Tmp_" + tableName));
sb.Append(";SET IDENTITY_INSERT [Tmp_");
sb.Append(tableName);
sb.Append("] ON;INSERT INTO [Tmp_");
sb.Append(tableName);
sb.Append("](");
for (int i = 0; i < this.Data.Columns.Count; i++)
{
if (tbSchema.Select("ColumnName='" + this.Data.Columns[i].ColumnName + "'").Length > 0)
{
if (i > 0)
{
sb.Append(",");
}
sb.Append("[");
sb.Append(this.Data.Columns[i].ColumnName);
sb.Append("]");
}
}
sb.Append(") SELECT ");
for (int i = 0; i < this.Data.Columns.Count; i++)
{
DataRow[] rs = tbSchema.Select("ColumnName='" + this.Data.Columns[i].ColumnName + "'");
if (rs.Length > 0)
{
if (i > 0)
{
sb.Append(",");
}
if (rs[0]["DataType"].ToString() != this.Data.Columns[i].DataType.ToString())
{
sb.Append("CONVERT(");
sb.Append(getSqlDataType(this.Data.Columns[i].DataType, this.Data.Columns[i].ExtendedProperties["inputtype"].ToString()));
sb.Append(",");
if (rs[0]["DataType"].ToString() == "System.String" || this.Data.Columns[i].DataType.ToString() == "System.String")
{
sb.Append("CONVERT(nvarchar(255),");
}
}
sb.Append("[");
sb.Append(this.Data.Columns[i].ColumnName);
sb.Append("]");
if (rs[0]["DataType"].ToString() != this.Data.Columns[i].DataType.ToString())
{
sb.Append(")");
if (rs[0]["DataType"].ToString() == "System.String" || this.Data.Columns[i].DataType.ToString() == "System.String")
{
sb.Append(")");
}
}
}
}
sb.Append(" FROM [");
sb.Append(tableName);
sb.Append("] WITH (HOLDLOCK TABLOCKX);SET IDENTITY_INSERT [Tmp_");
sb.Append(tableName);
sb.Append("] OFF;DROP TABLE [");
sb.Append(tableName);
sb.Append("];EXECUTE sp_rename N'Tmp_");
sb.Append(tableName);
sb.Append("', N'");
sb.Append(tableName);
sb.Append("', 'OBJECT';COMMIT;");
cmd.CommandText = sb.ToString();
cmd.ExecuteNonQuery();
}
}
#endregion
}
else
{
#region create table
cmd.CommandText = getCreateSql(tableName);
cmd.ExecuteNonQuery();
#endregion
}
}
finally
{
conn.Close();
}
}
