C# Table Schema from SqlDataReader

By: Rajesh P.S.

The SqlDataReader Object in C# provides a stream-based, forward-only, read-only retrieval of query results from the Data Source without the ability to update the data.

ExecuteReader() method

When the ExecuteReader() method is called on the SqlCommand Object in C#, it instantiates a SqlClient.SqlDataReader Object. This SqlDataReader Object allows you to efficiently read and access the retrieved data from the Data Source.

SqlDataReader sqlReader = sqlCmd.ExecuteReader();

While the SqlDataReader is open and actively reading data, you have the ability to retrieve schema information about the current result set using the GetSchemaTable() method of the SqlDataReader. By invoking GetSchemaTable(), you can obtain a DataTable object that is populated with rows and columns containing the schema information for the current result set.

The returned DataTable from GetSchemaTable() provides valuable information about the columns in the result set, including details such as column names, data types, sizes, and other relevant metadata. This schema information can be useful for understanding and manipulating the structure of the result set within your application.

Continue Reading...

Full Source C#

using System; using System.Windows.Forms; using System.Data; using System.Data.SqlClient; namespace WindowsApplication1 { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void button1_Click(object sender, EventArgs e) { string connetionString = null; SqlConnection sqlCnn ; SqlCommand sqlCmd ; string sql = null; 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); SqlDataReader sqlReader = sqlCmd.ExecuteReader(); DataTable schemaTable = sqlReader.GetSchemaTable(); foreach (DataRow row in schemaTable.Rows) { foreach (DataColumn column in schemaTable.Columns) { MessageBox.Show (string.Format("{0} = {1}", column.ColumnName, row[column])); } } sqlReader.Close(); sqlCmd.Dispose(); sqlCnn.Close(); } catch (Exception ex) { MessageBox.Show("Can not open connection ! "); } } } }

It's important to note that the GetSchemaTable() method can only be called while the SqlDataReader is open and actively reading data. Once the SqlDataReader is closed, the schema information is no longer available.

Conclusion

The SqlDataReader Object in C# provides a stream-based, forward-only, read-only retrieval of query results. The ExecuteReader() method in the SqlCommand Object instantiates the SqlDataReader. While the SqlDataReader is open, you can retrieve schema information about the current result set using the GetSchemaTable() method, which returns a DataTable populated with schema details.

Next > C# Table Schema from OleDbDataReader

Related Links