Schema Informations from SqlDataReader

By: Rajesh P.S.

The SqlDataReader Object in ADO.NET provides a stream-based, forward-only, read-only retrieval of query results from the Data Source. It is specifically designed for retrieving data and does not allow for data modification operations.

When the ExecuteReader method is executed in the SqlCommand Object, it instantiates a SqlClient.SqlDataReader Object, which represents the result set obtained from the execution of the SQL statement.

Dim sqlReader As SqlDataReader = sqlCmd.ExecuteReader()

While a SqlDataReader is open and active, you have the ability to retrieve schema information about the current result set using the GetSchemaTable method. The GetSchemaTable method returns a DataTable object that is populated with rows and columns containing the schema information for the current result set.

GetSchemaTable

By calling GetSchemaTable on the SqlDataReader Object, you can obtain valuable metadata about the columns in the result set, such as the column name, data type, size, and other relevant properties. This schema information can be utilized for various purposes, including dynamically generating UI elements, performing data validation, or making decisions based on the structure of the result set.

The DataTable returned by GetSchemaTable contains rows that represent the columns in the result set, with each column containing information about a specific aspect of the column's schema. You can access and analyze this schema information using the methods and properties provided by the DataTable object.

This capability to retrieve schema information with GetSchemaTable proves beneficial when you need to programmatically determine the structure of the result set without relying on hard-coded column names or assumptions. It allows for more dynamic and flexible data handling, empowering your application to adapt to varying result set structures.

Full Source VB.NET

Imports System.Data.SqlClient Public Class Form1 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim connetionString As String Dim sqlCnn As SqlConnection Dim sqlCmd As SqlCommand Dim sql As String connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password" sql = "Select * from product" sqlCnn = New SqlConnection(connetionString) Try sqlCnn.Open() sqlCmd = New SqlCommand(sql, sqlCnn) Dim sqlReader As SqlDataReader = sqlCmd.ExecuteReader() Dim schemaTable As DataTable = sqlReader.GetSchemaTable() Dim row As DataRow Dim column As DataColumn For Each row In schemaTable.Rows For Each column In schemaTable.Columns MsgBox(String.Format("{0} = {1}", column.ColumnName, row(column))) Next Next sqlReader.Close() sqlCmd.Dispose() sqlCnn.Close() Catch ex As Exception MsgBox("Can not open connection ! ") End Try End Sub End Class

Conclusion

The SqlDataReader Object in ADO.NET provides a means of retrieving query results from a Data Source. While the SqlDataReader is active, the GetSchemaTable method can be used to retrieve a DataTable object containing the schema information for the current result set. This schema information can be utilized to enhance data handling, decision-making, and overall flexibility in your application.

Next > Getting Schema Informations from OleDbDataReader

Related Topics

More Related Topics.....

Related Links