none
How to use CommandBehavior.SchemaOnly/KeyInfo RRS feed

  • Question

  • Hi

    I notice that when call SqlCommand.ExecuteReader i can pass in CommandBehavior parameters, i have tried other 5 values and know how that affect the result. but i have no clue about schemaonly/keyinfo

    i have used this commandText = "select top 1 * from Production.Product";

    anyone can tell me how that works

    Thanks


    Zhongchen Zhou
    Thursday, November 25, 2010 11:57 AM

Answers

  • Hi there, I'm sure you have seen this:
    "CommandBehavior Enumeration"
    http://msdn.microsoft.com/en-us/library/system.data.commandbehavior.aspx

    When using SchemaInfo you will not get any data, just the metadata info for the table.
    When using KeyInfo, you will get the data and metadata with some added info for the primary key columns.

    If you run this you will get an idea on the differences I hope.

     

        static void Main(string[] args)
        {
          string cs = @"Data Source=<your server>;Integrated Security=SSPI;Initial Catalog=Northwind";
          using (SqlConnection con = new SqlConnection(cs))
          {
            con.Open();
            SqlCommand cmd = con.CreateCommand();
            cmd.CommandText = "SELECT * FROM Shippers";
    
            SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.SchemaOnly);
            DataTable dt = rdr.GetSchemaTable();
            foreach (DataRow r in dt.Rows)
            {
              Console.WriteLine("------------------------------------------------------------------");
              foreach (DataColumn dc in dt.Columns )
              {
                Console.WriteLine("Property: {0, -30} Value: {1}", dc.ColumnName, r[dc].ToString());
              }
            }
            // No rows, so nothing will be shown.
            while (rdr.Read())
            {
              Console.WriteLine(rdr[0].ToString());
            }
            rdr.Close();
    
            rdr = cmd.ExecuteReader(CommandBehavior.KeyInfo);
            dt = rdr.GetSchemaTable();
            foreach (DataRow r in dt.Rows)
            {
              Console.WriteLine("------------------------------------------------------------------");
              foreach (DataColumn dc in dt.Columns)
              {
                Console.WriteLine("Property: {0, -30} Value: {1}", dc.ColumnName, r[dc].ToString());
              }
            }
            // Rows, so data is available as well as the added KeyInfo
            while (rdr.Read())
            {
              Console.WriteLine(rdr[0].ToString());
            }
            rdr.Close();
            con.Close();
          }
        }
    

     

    For example, when using KeyInfo, the property IsKey will be true or false for the columns. When using SchemaInfo, there is nothing for these properties.


    This posting is provided "AS IS" with no warranties.
    • Marked as answer by zhongchen zhou Friday, November 26, 2010 12:28 AM
    Thursday, November 25, 2010 2:59 PM

All replies

  • Hi there, I'm sure you have seen this:
    "CommandBehavior Enumeration"
    http://msdn.microsoft.com/en-us/library/system.data.commandbehavior.aspx

    When using SchemaInfo you will not get any data, just the metadata info for the table.
    When using KeyInfo, you will get the data and metadata with some added info for the primary key columns.

    If you run this you will get an idea on the differences I hope.

     

        static void Main(string[] args)
        {
          string cs = @"Data Source=<your server>;Integrated Security=SSPI;Initial Catalog=Northwind";
          using (SqlConnection con = new SqlConnection(cs))
          {
            con.Open();
            SqlCommand cmd = con.CreateCommand();
            cmd.CommandText = "SELECT * FROM Shippers";
    
            SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.SchemaOnly);
            DataTable dt = rdr.GetSchemaTable();
            foreach (DataRow r in dt.Rows)
            {
              Console.WriteLine("------------------------------------------------------------------");
              foreach (DataColumn dc in dt.Columns )
              {
                Console.WriteLine("Property: {0, -30} Value: {1}", dc.ColumnName, r[dc].ToString());
              }
            }
            // No rows, so nothing will be shown.
            while (rdr.Read())
            {
              Console.WriteLine(rdr[0].ToString());
            }
            rdr.Close();
    
            rdr = cmd.ExecuteReader(CommandBehavior.KeyInfo);
            dt = rdr.GetSchemaTable();
            foreach (DataRow r in dt.Rows)
            {
              Console.WriteLine("------------------------------------------------------------------");
              foreach (DataColumn dc in dt.Columns)
              {
                Console.WriteLine("Property: {0, -30} Value: {1}", dc.ColumnName, r[dc].ToString());
              }
            }
            // Rows, so data is available as well as the added KeyInfo
            while (rdr.Read())
            {
              Console.WriteLine(rdr[0].ToString());
            }
            rdr.Close();
            con.Close();
          }
        }
    

     

    For example, when using KeyInfo, the property IsKey will be true or false for the columns. When using SchemaInfo, there is nothing for these properties.


    This posting is provided "AS IS" with no warranties.
    • Marked as answer by zhongchen zhou Friday, November 26, 2010 12:28 AM
    Thursday, November 25, 2010 2:59 PM
  • Hi Michael

    thanks for your reply, I didnot know DbDataReader.GetSchemaTable() before. I thought that the schema information will be returned as rows contained within DataReader, when i didnot see anything in DataReader, i thought i might need to specify the information in my query manually.

    Thanks


    Zhongchen Zhou
    Friday, November 26, 2010 12:32 AM
  • Michael,

    I came across this very helpful post. Now, do you know the type of each property? Say, I defined the following class:

    /// 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 now I want to set properties of this class in 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"].ToString());
    
                            
                         }

    And here the last line is obviously failing but I don't know how to use it correctly.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, July 29, 2013 7:21 PM
  • Looks like in 4.5 version of .NET this property (DataType) was removed. I found another way to get the type (through GetFieldType property passing ColumnOrdinal property), but this sounds a bit complex.

    Also, I am wondering why that property was removed?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, July 29, 2013 11:34 PM