getting the MSSQL Schema for a table

Question

I have the following C#.Net code that I am trying to get to return the stored procedure results along with the schema of those results. Below is how my code (simplified) currently looks...

Database db = DatabaseFactory.CreateDatabase();
DbCommand dbCommand = db.GetStoredProcCommand("MyStoredProcedure");
IDataReader drData = db.ExecuteReader(dbCommand);

DataTable tblSchema;
tblSchema  = drData.GetSchemaTable();

The GetSchemaTable is returning empty. I have read that I need to pass CommandBehavior.KeyInfo to the Executereader method but I am unclear how this would look in the way I have the code structured since I am passing the dbCommand to the Executereader.

Answer 1

If you are using System.Data.Common.DbCommand, then, i think you can call

IDataReader drData = dbCommand.ExecuteReader(CommandBehavior.KeyInfo);

[EDIT]

You can also use

DataSet ds = new DataSet();
db.LoadDataSet(dbCommand, ds, "tableName");

or

db.ExecuteDataSet

This is a link a found useful

Hope it answers your question

Answer 2

The best way to get a schema for a SQL Server stored procedure is to use the sql:

EXEC sp_help MyStoredProcedure

See http://msdn.microsoft.com/en-us/library/aa933429(SQL.80).aspx

Answer 3

Embarrassing old code time. This is what I used when I was first learning .NET 1.1, and the guy who taught me insisted on using DataSets/Tables instead of business objects. It's about 5 years old and ripped out of an old library, but given a table name, it'll give you a dataset containing the table schema.

public static DataSet GetTableSchema(string tableName)
{
    string query = string.Format("SELECT TOP 0 * FROM {0}", tableName);

    using (SqlConnection localSqlConn = new SqlConnection(GetConnectionString()))
    {
        DataSet ds = new DataSet();

        SqlCommand sqlCmd = new SqlCommand(query, localSqlConn);
        SqlDataAdapter sda = new SqlDataAdapter(sqlCmd);

        sda.FillSchema(ds, SchemaType.Source, tableName);
        sda.Fill(ds, tableName);

        sda.Dispose();

        return ds;
    }
}