>>>>Hi everybody,
>>>>
>>>>I found some code online and tested this code in the LinqPad:
>>>>
>>>>

>>>>string strConn, strSQL;
>>>>  strConn = @"Data Source=(local);Initial Catalog=SiriusSQL;" +
>>>>             "Trusted_Connection=Yes;";
>>>>  SqlConnection cn = new SqlConnection(strConn);
>>>>  cn.Open();
>>>>  strSQL = "SELECT * FROM dbo.items";
>>>>  SqlCommand cmd = new SqlCommand(strSQL, cn);
>>>>  SqlDataReader rdr;
>>>>  rdr = cmd.ExecuteReader(CommandBehavior.SchemaOnly);
>>>>  DataTable tbl = rdr.GetSchemaTable();
>>>>  
>>>>    foreach (DataRow r in tbl.Rows)
>>>>        {
>>>>          Console.WriteLine("------------------------------------------------------------------");
>>>>          foreach (DataColumn dc in tbl.Columns )
>>>>          {
>>>>            Console.WriteLine("Property: {0, -30} Value: {1}", dc.ColumnName, r[dc].ToString());
>>>>          }
>>>>        }
>>>>  rdr.Close();
>>>>  cn.Close();

>>>>
>>>>Now, I defined this class:
>>>>
>>>>

>>>>/// <summary>
>>>>   /// Column schema - some properties
>>>>   /// </summary>
>>>>    public class ColumnSchema
>>>>    {
>>>>       public String ColumnName { get; set; }
>>>>       public Type DataType { get; set; }
>>>>       public SqlDbType DbType { get; set; }
>>>>       public Int16 ColumnSize { get; set; }
>>>>       public Int16 Precision { get; set; }
>>>>       public Int16 Scale { get; set; }
>>>>    }

>>>>
>>>>And so I have this code:
>>>>
>>>>

>>>> using (SqlDataReader sqlDataReader = command.ExecuteReader(CommandBehavior.SchemaOnly))
>>>>                  {
>>>>                     DataTable tbl = sqlDataReader.GetSchemaTable();
>>>>                     foreach (DataRow r in tbl.Rows)
>>>>                     {
>>>>                        ColumnSchema colSchema = new ColumnSchema();
>>>>                        String readerColumnName = r["ColumnName"].ToString();
>>>>                        colSchema.ColumnName = readerColumnName;
>>>>                        colSchema.ColumnSize = r.Field<Int16>("ColumnSize");
>>>>                        colSchema.DataType = (Type)(r["DataType"].ToString());

>>>>
>>>>The last line is failing, of course.
>>>>
>>>>I am not sure how to get that DataType property to be the Type type. In the code above when I output all info, that was fine, as I output everything as a string. But how do I know the actual type of returned property and how can I set it for my new ColumnSchema properties?
>>>>
>>>>Thanks in advance.
>>>
>>>Naomi
>>>
>>>What .NET version are you using?
>>>With my version - 4.5 - the table created by GetSchemaTable has a string column called DataTypeName.
>>>I don't see any DataType column.
>>>
>>>http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.getschematable.aspx
>>
>>I see both DataTypeName and DataType. DataTypeName looks like it returns SqlDbType - may be I can use this property instead of what I use.
>>
>>I currently have this code
>>
>>

>>DataTable tbl = sqlDataReader.GetSchemaTable();
>>                  foreach (DataRow r in tbl.Rows)
>>                  {
>>                     ColumnSchema colSchema = new ColumnSchema();
>>                     String readerColumnName = r["ColumnName"].ToString();
>>                     colSchema.ColumnName = readerColumnName;
>>                     colSchema.ColumnSize = r.Field<Int16>("ColumnSize");
>>                     colSchema.DataType = (Type)(r["DataType"]);
>>                     colSchema.DbType = (SqlDbType)(Convert.ToInt32(r["ProviderType"]));
>>
>>                     columnTypes.Add(readerColumnName, colSchema);
>>                  }

>
>Seems unnecessarily complicated. Change ColumnSchema.ColumnSize to Int32 and:

           List<ColumnSchema> columnTypes;
>
>            using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.SchemaOnly))
>            {
>
>                 columnTypes = (from DataRow r in reader.GetSchemaTable().AsEnumerable()
>                                                  select new ColumnSchema
>                                                      {
>                                                          ColumnName = (string) r["ColumnName"],
>                                                          ColumnSize = (Int32) r["ColumnSize"],
>                                                          DbType = (SqlDbType) r["ProviderType"],
>                                                          DataType = (Type) r["DataType"]
>                                                      }).ToList();
>            }

In the TableSchema class we have the following:

 public class TableSchema
    {
        internal String tableName;
        internal Dictionary<String, ColumnSchema> columnTypeDictionary;

        public TableSchema(String table, Dictionary<String, ColumnSchema> columns)
        {
            tableName = table;
            columnTypeDictionary = columns;
        }

How should I adjust the above code to use Dictionary instead of List?

My current code (before I saw this) is:

private void AddTableSchema(String tableName, ref String returnMessage)
      {
         Logging.LogFormat(3, "Info: Did not find key value for '{0}' in schema dictionary ... Adding now...", tableName);

         Dictionary<String, ColumnSchema> columnTypes = new Dictionary<String, ColumnSchema>(StringComparer.OrdinalIgnoreCase);
         String commandText = String.Format("select * from dbo.{0} where 0 = 1;", tableName);
         SqlConnection siriusSqlConnection = this.sqlConnection;
         if (null == siriusSqlConnection)
            return;

         using (SqlCommand command = new SqlCommand(commandText, siriusSqlConnection))
         {
            try
            {
               using (SqlDataReader sqlDataReader = command.ExecuteReader(CommandBehavior.SchemaOnly))
               {
                  DataTable tbl = sqlDataReader.GetSchemaTable();
                  foreach (DataRow r in tbl.Rows)
                  {
                     ColumnSchema colSchema = new ColumnSchema();

                     colSchema.ColumnName = r["ColumnName"].ToString();
                     colSchema.ColumnSize = Convert.ToInt32(r["ColumnSize"]);
                     colSchema.DataType = sqlDataReader.GetFieldType((int)r["ColumnOrdinal"]);
                     colSchema.DbType = (SqlDbType)(Convert.ToInt32(r["ProviderType"]));
                     colSchema.Precision = Convert.ToInt16(r["NumericPrecision"]);
                     colSchema.Scale =  Convert.ToInt16(r["NumericScale"]);
                     colSchema.IsIdentity = r.Field<Boolean>("IsIdentity");
                     columnTypes.Add(colSchema.ColumnName, colSchema);
                  }
                  TableSchema schema = new TableSchema(tableName, columnTypes);
                  tableSchema.Add(tableName, schema);
                  
               }
            }
            catch (Exception ex)
            {
               returnMessage = ex.ToString();
            }
         }
      }