Sample C# Code : Database Column Information retrieval

[Kreij]

Hi all, and welcome back to old Uncle Kriej's random code snippets.

I was working with Sql Server Express and I needed to query my database for a table's column information (column name and column type). Now this is a realitively easy thing to do if you are using strongly typed DataSets, but alas when you are pushing the free versions (Express) beyond their intended limitations you have to get creative.

For instance, VC# and SQL Server Express (SSE) is only supposed to support databases on the local machine. Not so. You can put SSE anywhere and get to it, you just lose some of the fancy functionality of the IDE and have to resort to coding things yourself (like DataSets).

Now, constructing strongly typed DataSets by hand is not something I would wish on anyone, so one is left to make do with untyped DataSets (or buy the real versions of SSE and VS)

As always there are ways around almost any limitation that MS puts on the free dev tools as long as you are willing to do some research and a bit more coding.

One way to get database column information is as follows ..

SqlConnection sqlCon = new SqlConnection("MyDataBaseConnectionString");

SqlCommand sqlCom = new SqlCommand(
"select syscolumns.name, systypes.name " +
"from syscolumns " +
"inner join systypes " +
"on syscolumns.xtype=systypes.xtype " +
"where syscolumns.id=object_id('MyDataBaseTable')", sqlCon);

sqlCom.Connection.Open();
SqlDataReader sqlRd = sqlCom.ExecuteReader(CommandBehavior.CloseConnecti on);
while (sqlRd.Read())
{
dbFieldDataGridView.Rows.Add(
new object[] { sqlRd.GetString(0), sqlRd.GetString(1) });
}
sqlRd.Close();


The above code uses a DataReader to query the system tables (syscolumns and systypes) in SSE for the column name and column type and then puts them in columns in a DataGridView.

The problem I had, is that I did not want the SQL native datatypes (varchar etc.) I wanted the corresponding System Types (System.String, System.Int32, etc.) and I also wanted to be able to get at the data in the database, not just the table's column information

With untyped DataSets there is no underlying Schema data (XLS file). It's in the Schema file where the column information in a strongly typed DataSet resides.

So to get around this little nuisance, MS was kinid enought to add a property and an enumeration to the SqlDataAdapter class (or DataAdapter if not using SSE). The property and enumeration are both called "MissingSchemaAction". Here's some more code ...

DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(
"select * from MyDataBaseTable", sqlCon);
da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
da.Fill(ds);

foreach (DataColumn dc in ds.Tables[0].Columns)
{
dbFieldDataGridView.Rows.Add(
new object[] { dc.ColumnName, dc.DataType.ToString() });
}


The above snippet does exactly the same as the first with two notable exceptions.
1) It returns the system types instead of the SQL native types and
2) It returns the table's key primary information (AddWithKey). You can just do a "MissingSchemaAction.Add" if you do not care about the primary key information from the table.

Anyway, I hope this little tidbit of information helps someone out there if they are using untyped DataSets and need the underlying table schema information.

[WhoWhatHuh]

I think your sql command should be like this:

SqlCommand sqlCom = new SqlCommand(
"select syscolumns.name, systypes.name " +
"from syscolumns " +
"inner join systypes " +
"on syscolumns.xtype=systypes.xtype AND syscolumns.xusertype=systypes.xusertype" +
"where syscolumns.id=object_id('MyDataBaseTable')", sqlCon);

Without the xusertype you could get multple rows for the same column name.

Either way, thank you for your post. It pointed me in the right direction.

[Kreij]

Holy zombie thread. I forgot about even posting this. :laugh:

You may be right, but the implementation above worked perfectly for what I wanted it to do (which I know longer remember. lol)

Anyway, thanks for adding that tidbit in case it does make a difference for someone.