Wednesday, October 28, 2015

ADO.Net - Retrieve Table Schema Information using CommandBehavior.SchemaOnly

1. Introduction

When you are working on Ado.net sometimes you need to get the table schema information like Column name and data type of that column. In this example article, I will show getting the table schema information. Have a look at the below screenshot:

Ado.net Retrieve Table Schema
Fig1. Table Schema for Pubs.Discounts

Here, we are going to display the table schema marked as 2 for the table “discounts” which marked as 1.


2. About the example

The screenshot of the sample is shown below:

Ado.net Retrieve Table Schema
Fig 2. Example Application Screenshot

The item marked as 1 is a multiline textbox control used to display the table schema shown in section 1 of this article. The “Get Table Schema” button (Marked as 2) once clicked displays the schema information of the discount table in the multi-line textbox. Before using the sample, you should configure the connection string to the Pubs database. Setting the connection string for the “NorthWnd“ database is shown in the below video. The same procedure can be followed to set the connection string for the Pubs database (Name the connection string as PUBSDB) as the table DISCOUNTS resides in it.

Video 1: Forming the connection string



3. Code behind the form

1) First, the required namespace is used in the .cs file. Code is below:

 //Sample 01: For accessing the required functionality
using System.Data.SqlClient;

2) Next, connection to the Pubs database is established by making use of the connection string “PubsDB” formed in the previous section. The SqlConnection object is created using the connection string which is stored as the application property. Once the connection object is created, the Open method on the object is called to establish the connection to the Pubs database. Below is the code:

//Sample 2.1: Open Connection to SQL
SqlConnection Con = new SqlConnection(Properties.Settings.Default.PubsDB);
Con.Open();

When you are typing the above code, the intelli-sense displays the Connection string name when it is configured as the application property. The below screenshot shows the that:
Ado.net Retrieve Table Schema
Fig 3. Intellisence for ConnectionString property

3) After the successful connection, a SqlCommand object is created on the DISCOUNTS table of the PUBS database. Then, ExecuteReader method on the Cmd object is called by passing the parameter CommandBehavior.SchemaOnly. This informs the command object that we need only the schema information on the underlying command object in our case it is schema information of Discounts table. The code is given below:

//Sample 2.2: Create Command Object and get schema reader
SqlCommand Cmd = new SqlCommand("Select * from Discounts", Con);
SqlDataReader Schema_Reader = Cmd.ExecuteReader(CommandBehavior.SchemaOnly);

4) Now we have the Schema_Reader of SqlDataReader which we can to iterate through to get the column information of the DISCOUNTS table. Inside the iteration loop, the schema information is retrieved and displayed in the multiline textbox. The code for this is given below:

//Sample 2.3: Iterate through the Reader and get the Schema information
int Total_Column = Schema_Reader.FieldCount;
for (int i = 0; i < Total_Column; i++)
{
    string schema_inormation = string.Format("Column Name: {0}, Column Type: {1}",
        Schema_Reader.GetName(i), Schema_Reader.GetDataTypeName(i));
    txtSchemaInfo.AppendText(schema_inormation + Environment.NewLine);
}

//Sample 2.4 : Close all the objects
Schema_Reader.Close();
Con.Close();


Source code : Download

No comments:

Post a Comment

Leave your comment(s) here.

Like this site? Tell it to your Firend :)