ADO.Net : Get table definition from SQL server tables

Question

I am using C# to write a method that returns the following information about a table: column names, column types, column sizes, foreign keys.

Can someone point me in the right direction on how to accomplish this ?

Answer 1

This really depends on how you communicate with your database. If you are using LinqToSQL or another similar ORM this would be pretty easy but if you want to get these values via a query I'd suggest you use the INFORMATION_SCHEMA views as these are fast and easy to query.

e.g.

select * from information_schema.columns where table_name = 'mytable'

Answer 2

To get the FK and Schema you should be able to use:

DA.FillSchema() 

DS.Table("Name").PrimaryKey 

OR calling sp_fkey using the method demonstrated below

Code Snippet from AND Another Link

    private void LoanSchema()
    {

         private List<String> tablesList = new List<String>();
         private Dictionary<String, String> columnsDictionary = new Dictionary<String, String>();

          string connectionString = "Integrated Security=SSPI;" +
          "Persist Security Info = False;Initial Catalog=Northwind;" +
          "Data Source = localhost";
          SqlConnection connection = new SqlConnection();
          connection.ConnectionString = connectionString;
          connection.Open();

          SqlCommand command = new SqlCommand();
          command.Connection = connection;
          command.CommandText = "exec sp_tables";
          command.CommandType = CommandType.Text;

          SqlDataReader reader = command.ExecuteReader();

           if (reader.HasRows)
           {
               while (reader.Read())
                  tablesList.Add(reader["TABLE_NAME"].ToString());
           }
           reader.Close();

           command.CommandText = "exec sp_columns @table_name = '" +
           tablesList[0] + "'";
           command.CommandType = CommandType.Text;
           reader = command.ExecuteReader();

            if (reader.HasRows)
            {
                while (reader.Read())
                          columnsDictionary.Add(reader["COLUMN_NAME"].ToString(), reader["TYPE_NAME"].ToString());
             }
}

Answer 3

You can use the SqlDataAdapter.FillSchema() method.

Alternatively you can use the SqlDataAdapter.Fill() method after setting the MissingSchemaAction property of the SqlDataAdapter to AddWithKey. But if you only want the schema you must ensure that your query returns no rows. This can be accomplished by adding a statement like WHERE 1=2 to your query.

Answer 4

If you are using MS SQL Server then You should definately have a look at SMO namespace (server management objects).

There are objects which You can use in .net responsible for all kinds of things in a database (including but not limited to tables, columns, constraints etc.)