This article will present code on how to get schema of a database table. Getting the schema at run time provides finds a lot of utility in features where you want to take some action based on the data type that a field stores. This technique is used in dynamically mapping the tables or populating stored procedure parameters.

The technique is very simple. There is GetSchemaTable method available that returns a DataTable describing the meatadata or schema of a data base table. The important part is to understand what does each row and column in the returned table means. Each row in the table represent each data field in the database table. And each column has predefined name corresponding to different attribute of the fields. Some of these column names are ColumnName, DataType, IsUnique, etc. Please refer to the framework documentation to get complete list of these column names and their usage.

using System;
using System.Data;
using System.Data.SqlClient;
namespace DumpDbSchema
{
	class Class1
	{
		[STAThread]
		static void Main(string[] args)
		{
			string strQuery = "SELECT * FROM Employees";
			string strConn = "user id=sa;password=foo;initial catalog=northwind;data source=localhost;";
			SqlConnection conn = new SqlConnection(strConn);
			SqlCommand cmd = new SqlCommand(strQuery, conn);
			conn.Open();
			SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
			// Get the schema table.
			DataTable tblSchema = rdr.GetSchemaTable();
			conn.Close();
			rdr.Close();
			int nColumnCount = tblSchema.Columns.Count;
			foreach (DataRow dr in tblSchema.Rows)
			{
				Console.WriteLine("{0}: {1}", dr["ColumnName"], dr["DataType"]);
			}
		}
	}
}