Using the DataAdapter to add schema information about your table.
There are circumstances where you need to add schema information regarding a table to a DataTable object. You might want to know which columns define the primary key, or you want to know which column is an identify column. Well, here is one option.
Create a command object that the following query:
SqlCommand cmd = new SqlCommand();
cmd.Connection = sqlConnection;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SET FMTONLY ON; SELECT * FROM [tableName]; SET FMTONLY OFF;"
As you can see, I set the SQL FMTONLY setting to ON. When I then perform the select statement, the statement will not return any data, just the metadata. This is what we want, because we don't know how many rows will be in the result set when we perform this select statement. And we don't want to wait for the query to finish, right?
You then create the DataAdapter. The DataAdapter supports a property MissingSchemaAction. When you set the value for this property to MissingSchemaAction.AddWithKey, the DataAdapter will add all relevant information about the table to the DataTable object. After setting the property, you can fill the dataset in the normal manner:
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
// The dataadapter must add the schema information based on the database
da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
// Fill a DataSet using default values for DataTable names, etc
DataSet dsInfo = new DataSet();
da.Fill(dsInfo);
// Do something with all the new meta information
}
After running this code, your DataTable object will not only contain the basic columns, but also all information on key columns, identity columns and all other information available to the database.