none
Use C# to get the column properties of a SQL table RRS feed

  • Question

  •  

    I am writing a test that will pull the properties (data type, length, primary/foreign key) for each column in a table and compare it to the expected design values.  I am having trouble finding a way to pull the column properties from the SQL table.  I thought GetSchema() would do it, but it doesn't (unless I'm using it wrong).  Any ideas?

     

    Thanks

    Monday, August 27, 2007 7:13 PM

All replies

  • I'm not really familiar with the GetShema() method; however, you can get the information you need by either selecting the desired columns from the information_schema.columns view or executing the sp_columns procedure and passing the name of the table.

     

    Monday, August 27, 2007 7:27 PM
  • The following snippet will return you a DataTable with the schema for the provided table:

    Code Snippet

    DataTable GetSchema(string oleDbConnectionString,string tableName) {

     

    OleDbConnection connection;

    DataTable schema;

     

    using(connection = new OleDbConnection(oleDbConnectionString)) {

     

    connection.Open();

    schema = connection.GetOleDbSchema(

    OleDbSchemaGuid.Tables,new object[] { null, null, null, tableName }

    );

    connection.Close();

     

    }

     

    return schema;

     

    }

     

     

    The following snippet will return you a DataTable with the schema for the provided table using the SqlClient provider:

    Code Snippet

    DataTable GetSchema(string connectionString,string tableName) {

     

    SqlConnection connection;

    string query;

    SqlCommand command;

    SqlDataReader reader;

    DataTable schema;

     

    using(connection = new SqlConnection(connectionString)) {

     

    connection.Open();

    query = String.Format(CultureInfo.InvariantCulture,"SELECT * FROM {0}",tableName);

     

    using(command = new SqlCommand(query,connection)) {

     

    using(reader = command.ExecuteReader()) {

    schema = reader.GetSchemaTable();

    }

     

    }

     

    connection.Close();

     

    }

     

    return schema;

     

    }

     

     

     

    HTH

    Tuesday, August 28, 2007 11:19 PM