none
Using Schema to determine CHAR or VARCHAR? RRS feed

  • Question

  •  

    When using a data table to retrieve a schema, is there a way to tell the difference between a CHAR and a VARCHAR column?

     

    DataTable dtSchema;

    SqlConnection conn = new SqlConnection(sqlConnectString);

    conn.Open();

    dtSchema = conn.GetSchema("Tables");

    conn.Close();

     

    for (int i = 0; i < dtSchema.Rows.Count; i++)

    {

    foreach (DataColumn col in dt.Columns)

    {

    Console.WriteLine("\tDataType = {0}", col.DataType.ToString());

    Console.WriteLine("\tMaxLength = {0}", col.MaxLength);

    }

    }

     

    NOTE: Code abbreviated for this post.

     

    In each case I get "System.String" returned. I would like to be able to tell between the column types.

     

    Thanks!

    Michael

    Friday, June 27, 2008 6:26 PM

Answers

  • By querying "Tables" you get the list of tables without columns. If you want to get provider types you probably want to run the following code snippet:

     

    Code Snippet

    SqlConnection conn = new SqlConnection("...");

    conn.Open();

    DataTable dtSchema = conn.GetSchema("DataTypes");

    conn.Close();

    foreach (DataRow dt in dtSchema.Rows)

    {

    if (dtSchema.Rows.IndexOf(dt) == 0)

    {

    foreach (DataColumn col in dtSchema.Columns)

    {

    Console.Write("{0}\t", col);

    }

    }

    foreach (DataColumn col in dtSchema.Columns)

    {

    Console.Write("{0}, ", dt[col]);

    }

    Console.WriteLine();

    }

     

     

    Please clarify what schema are you interested in.
    Friday, June 27, 2008 7:37 PM
  • OK, I think I understand how Microsoft approached this. I realize how powerful this is, but as an old salty VB'er (I started with version 1 with ODBC/RDO and then ADO and was even one of the 15 customers who owned VB for DOS--ah, the good ole days.) Anyway, in case anyone is interested, I give you the code. There might be more efficient ways to extract the information, but this is what I settled on. I would love to thank other coders who wrote articles for which this is based, but I read about 50 pages via SearchDotNet and I don't have that info anymore.

     

    using System;

    using System.Data;

    using System.Data.SqlClient;

     

    namespace RetrieveDatabaseSchema

    {

        class Program

        {

            static void Main(string[] args)

            {

                // Retrieve table list using Connection.GetSchema()

                string tableName = "CMIS2005";

                using (SqlConnection conn = new SqlConnection("Data Source=(local);Integrated security=SSPI;Initial Catalog=" + tableName + ";"))

                {

                    // open the connection

                    conn.Open();

     

                    // get all the available schema types

                    Console.WriteLine("--------------------------------- Schema Types");

                    DataTable schemaDataTable = conn.GetSchema(System.Data.Common.DbMetaDataCollectionNames.MetaDataCollections);

                    foreach (DataRow row in schemaDataTable.Rows)

                    {

                        Console.WriteLine(row[0].ToString());

                    }

                    Console.WriteLine("\n");

     

                    // get the Tables and Columns schema

                    DataTable dtSchema = conn.GetSchema("Tables");

                    DataTable dtColSchema = conn.GetSchema("Columns");

     

                    // loop through the Tables and Columns

                    for (int i = 0; i < dtSchema.Rows.Count; i++)

                    {

                        Console.WriteLine("--------------------------------- {0}", dtSchema.RowsIdea["TABLE_NAME"]);

     

                        // get the Column data

                        DataRow[] dr = dtColSchema.Select("TABLE_NAME='" + dtSchema.RowsIdea["TABLE_NAME"] + "'");

     

                        for (int j = 0; j < dr.Length; j++)

                        {

                            Console.WriteLine("{0}\t{1}\t{2}\t{3}\t{4}\t{5}\t{6}",

                                dr[j]["COLUMN_NAME"],

                                dr[j]["DATA_TYPE"],

                                dr[j]["ORDINAL_POSITION"],

                                dr[j]["COLUMN_DEFAULT"],

                                dr[j]["IS_NULLABLE"],

                                dr[j]["CHARACTER_MAXIMUM_LENGTH"],

                                dr[j]["NUMERIC_PRECISION"]);

                        }

                    }

                }

     

                Console.WriteLine("\nPress any key to continue.");

                Console.ReadKey();

            }

        }

    }

     

    Thursday, July 3, 2008 3:36 PM

All replies

  • By querying "Tables" you get the list of tables without columns. If you want to get provider types you probably want to run the following code snippet:

     

    Code Snippet

    SqlConnection conn = new SqlConnection("...");

    conn.Open();

    DataTable dtSchema = conn.GetSchema("DataTypes");

    conn.Close();

    foreach (DataRow dt in dtSchema.Rows)

    {

    if (dtSchema.Rows.IndexOf(dt) == 0)

    {

    foreach (DataColumn col in dtSchema.Columns)

    {

    Console.Write("{0}\t", col);

    }

    }

    foreach (DataColumn col in dtSchema.Columns)

    {

    Console.Write("{0}, ", dt[col]);

    }

    Console.WriteLine();

    }

     

     

    Please clarify what schema are you interested in.
    Friday, June 27, 2008 7:37 PM
  •  

    As stated, the code was abbreviated. I am iterating each col and can see the available properties.
    Friday, June 27, 2008 7:39 PM
  • Abbreviation does not match the question. Please let me know the schema you're interested in.

    Friday, June 27, 2008 8:11 PM
  •  

    Maybe I am not following where you are trying to take me. Here is the entire code. Basically, I would like to know if a specific column is VARCHAR or CHAR. col.DataType.ToString() returns "String".

     

    string sqlConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=YOURDATABASENAME;";

    DataTable dtSchema;

     

    // Retrieve table list using Connection.GetSchema()

    SqlConnection conn = new SqlConnection(sqlConnectString);

    conn.Open();

    dtSchema = conn.GetSchema("Tables");

    conn.Close();

     

    for (int i = 0; i < dtSchema.Rows.Count; i++)

    {

    Console.WriteLine("--------------------------------- {0}", dtSchema.RowsIdea["TABLE_NAME"]);

    string sqlSelect = "SELECT * FROM " + dtSchema.RowsIdea["TABLE_SCHEMA"] + "." + dtSchema.RowsIdea["TABLE_NAME"];

    SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);

    DataTable dt = new DataTable();

    da.FillSchema(dt, SchemaType.Source);

     

    // output column max length using DataAdapter.FillSchema() results

    foreach (DataColumn col in dt.Columns)

    {

    Console.WriteLine("-------- {0}", col.ColumnName);

    Console.WriteLine("\tMaxLength = {0}", col.MaxLength);

    Console.WriteLine("\tAllowDBNull = {0}", col.AllowDBNull);

    Console.WriteLine("\tDataType = {0}", col.DataType.ToString());

    if (col.DefaultValue == null)

    Console.WriteLine("\tDefaultValue = {0}", col.DefaultValue.ToString());

    else

    Console.WriteLine("\tNO DEFAULT");

    Console.WriteLine("\tOrdinal = {0}", col.Ordinal.ToString());

    Console.WriteLine("\tReadOnly = {0}", col.ReadOnly.ToString());

    Console.WriteLine("\tUnique = {0}", col.Unique.ToString());

    }

    }

     

    Console.WriteLine("\nPress any key to continue.");

    Console.ReadKey();

    }

    Monday, June 30, 2008 2:28 PM
  • OK, I think I understand how Microsoft approached this. I realize how powerful this is, but as an old salty VB'er (I started with version 1 with ODBC/RDO and then ADO and was even one of the 15 customers who owned VB for DOS--ah, the good ole days.) Anyway, in case anyone is interested, I give you the code. There might be more efficient ways to extract the information, but this is what I settled on. I would love to thank other coders who wrote articles for which this is based, but I read about 50 pages via SearchDotNet and I don't have that info anymore.

     

    using System;

    using System.Data;

    using System.Data.SqlClient;

     

    namespace RetrieveDatabaseSchema

    {

        class Program

        {

            static void Main(string[] args)

            {

                // Retrieve table list using Connection.GetSchema()

                string tableName = "CMIS2005";

                using (SqlConnection conn = new SqlConnection("Data Source=(local);Integrated security=SSPI;Initial Catalog=" + tableName + ";"))

                {

                    // open the connection

                    conn.Open();

     

                    // get all the available schema types

                    Console.WriteLine("--------------------------------- Schema Types");

                    DataTable schemaDataTable = conn.GetSchema(System.Data.Common.DbMetaDataCollectionNames.MetaDataCollections);

                    foreach (DataRow row in schemaDataTable.Rows)

                    {

                        Console.WriteLine(row[0].ToString());

                    }

                    Console.WriteLine("\n");

     

                    // get the Tables and Columns schema

                    DataTable dtSchema = conn.GetSchema("Tables");

                    DataTable dtColSchema = conn.GetSchema("Columns");

     

                    // loop through the Tables and Columns

                    for (int i = 0; i < dtSchema.Rows.Count; i++)

                    {

                        Console.WriteLine("--------------------------------- {0}", dtSchema.RowsIdea["TABLE_NAME"]);

     

                        // get the Column data

                        DataRow[] dr = dtColSchema.Select("TABLE_NAME='" + dtSchema.RowsIdea["TABLE_NAME"] + "'");

     

                        for (int j = 0; j < dr.Length; j++)

                        {

                            Console.WriteLine("{0}\t{1}\t{2}\t{3}\t{4}\t{5}\t{6}",

                                dr[j]["COLUMN_NAME"],

                                dr[j]["DATA_TYPE"],

                                dr[j]["ORDINAL_POSITION"],

                                dr[j]["COLUMN_DEFAULT"],

                                dr[j]["IS_NULLABLE"],

                                dr[j]["CHARACTER_MAXIMUM_LENGTH"],

                                dr[j]["NUMERIC_PRECISION"]);

                        }

                    }

                }

     

                Console.WriteLine("\nPress any key to continue.");

                Console.ReadKey();

            }

        }

    }

     

    Thursday, July 3, 2008 3:36 PM