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