1. About Table Schema
When we are working on Ado.net 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
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
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:
|
1 2 |
//Sample 01: For accessing the required functionality using System.Data.SqlClient; |
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:
|
1 2 3 |
//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 IntelliSense reveals the Connection string name which we kept as the application property. The below screenshot shows that:
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:
|
1 2 3 4 |
//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); |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
//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 ADO.NET Table Schema Information Example From Google Drive
Categories: Ado.Net
Tags: ADO.Net, CommandBehavior, ExecuteReader(), SqlCommand, SqlConnection
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.