Get Table Schema Information Using Ado.Net

Example Application Screenshot

1. About Table Schema

When we are working on sometimes we need to get the table schema information like Column name and data type of that column. In this ADO.Net example, we will explore how to get the schema data using SqlCommand & CommandBehavior Enum . Have a look at the below screenshot:

Discounts Table in Pubs DB
Discounts Table in Pubs DB

Here, we will display the table schema marked as 2 for the table discounts which marked as 1.

2. About the Table Schema ADO.Net Example

The screenshot of the ADO.Net Example is below:

Example ADO.Net Application Screenshot
Example ADO.NET Application Screenshot

The item marked as 1 is a multi-line text-box control used to show the table schema shown in section 1 of this example. The Get Table Schema button (Marked as 2) displays the schema data of the discount table in the multi-line text box. Before using this example, one need to setup the connection string to the Pubs database. Setting the connection string for the NorthWnd database is shown in the below video. We can follow the same method to set the connection string for the Pubs database as the table DISCOUNTS resides in it. When you create Connection String name it as PUBSDB.

Video 1: Forming the Connection String

3. Code For Table Schema Information

Now our setup is ready. In this section, we will write C# Ado.Net code to retrieve the Discounts Table schema information. Note, if you do not have Pubs DB, you can mimic this example with your own table.

3.1 SqlClient Namespace

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

3.2 Database Connection

Next, we get a connection to the Pubs database by making use of the connection string PubsDB. Recall, we formed it in the past section. The SqlConnection object is set up using the connection string which we stored as the application property. Once the connection object is ready, we call the Open method on the SqlConnection object to connect to the Pubs database. Below is the code:

When you are typing the above code, the IntelliSense reveals the Connection string name which we kept as the application property. The below screenshot shows that:

Intellisense for ConnectionString property
IntelliSense for ConnectionString property

3.3 Get Schema Information Using ADO.NET CommandBehavior

ADO.Net has CommandBehavior enumeration constants which tells how we want to get the result from DB. The SchemaOnly value tells ADO.Net DB Engine to get only the column schema data and skip the column value data.  After the making connection to the DB, we create SqlCommand object on the DISCOUNTS table of the PUBS database. Then, we call ExecuteReader method on the Cmd object by passing the argument CommandBehavior.SchemaOnly. This tells the command object we need only the schema data on it. In our case, it is schema information of Discounts table. The code is below:

3.4 Iterate Schema Information

Now we have the Schema_Reader of SqlDataReader  which we can to iterate through to get the column schema of the DISCOUNTS table. Inside the iteration loop, we read the schema of the columns and display it in the multi-line text box. The code for this is below:

Source code : Download ADO.NET Table Schema Information Example From Google Drive


Do you like this Example? Please comment about it for others!!

This site uses Akismet to reduce spam. Learn how your comment data is processed.