If you need to process the records one by one or iterate the records from database table for that you need to use
IDataReader instance. There are various implementation of
IDataReader class. We are using
System.Data.SqlClient.SqlDataReader class to iterate the records.
The IDataReader interface extends the
System.Data.IDataRecord interface. These interfaces declare the functionality to access both the data and the structure of the data contained in the result set.
Here is the example.
In this example we execute two select queries first query is for to get product details and display it's records on screen, and second query is for get employee_master details and display it's table's metadata details on screen.
C# Examples :
// Create a new SqlConnection object.
using (SqlConnection objCon = new SqlConnection())
{
// Configure the SqlConnection object's connection string.
objCon.ConnectionString =@"Data Source=JAYESH-PC\SQLEXPRESS;" +
"Initial Catalog=TempDatabase;" +
"User ID=sa;Password=ibmx206;";
using (SqlCommand objCommand = objCon.CreateCommand())
{
objCommand.CommandType = CommandType.Text;
objCommand.CommandText = "SELECT product_id,product_name FROM product_master " +
" ORDER BY product_name;SELECT * FROM employee_master";
objCon.Open();
// Execute the command and obtain a SqlReader.
using (SqlDataReader objReader = objCommand.ExecuteReader())
{
// Process the first set of results and display the
// content of the result set.
Response.Write("Prodct details (By Product Name).");
while (objReader.Read())
{
Response.Write(string.Format(" <br/> {0} - {1} ",
objReader["product_id"], // Use string index
objReader[1])); // Use ordinal index
}
Response.Write("<br/><br/>");
// Process the second set of results and display
objReader.NextResult();
Response.Write("Employee Details.");
for (int field = 0; field < objReader.FieldCount; field++)
{
Response.Write(string.Format("<br/><b> Column Name:</b>{0} <b>Data Type:</b>{1}",
objReader.GetName(field),
objReader.GetDataTypeName(field)));
}
}
}
}
VB.net Examples :
' Create a new SqlConnection object.
Using objCon As New SqlConnection()
' Configure the SqlConnection object's connection string.
objCon.ConnectionString = "Data Source=JAYESH-PC\SQLEXPRESS;" & "Initial Catalog=TempDatabase;" & "User ID=sa;Password=ibmx206;"
Using objCommand As SqlCommand = objCon.CreateCommand()
objCommand.CommandType = CommandType.Text
objCommand.CommandText = "SELECT product_id,product_name FROM product_master " & " ORDER BY product_name;SELECT * FROM employee_master"
objCon.Open()
' Execute the command and obtain a SqlReader.
Using objReader As SqlDataReader = objCommand.ExecuteReader()
' Process the first set of results and display the
' content of the result set.
Response.Write("Prodct details (By Product Name).")
While objReader.Read()
' Use string index
' Use ordinal index
Response.Write(String.Format(" <br/> {0} - {1} ", objReader("product_id"), objReader(1)))
End While
Response.Write("<br/><br/>")
' Process the second set of results and display
objReader.NextResult()
Response.Write("Employee Details.")
For field As Integer = 0 To objReader.FieldCount - 1
Response.Write(String.Format("<br/><b> Column Name:</b>{0} <b>Data Type:</b>{1}", objReader.GetName(field), objReader.GetDataTypeName(field)))
Next
End Using
End Using
End Using
Output :
You can access the SqlDataReder objects data using
Field name,
ordinal index and
typed data methods.
The typed data contains various methods
like GetBoolean,
GetByte, GetBytes, GetChar, GetChars, GetDateTime, GetDecimal, GetDouble, GetFloat, GetGuid, GetInt16,
GetInt32, GetInt64, GetString, GetValue, and GetValues.
Each of the these methods takes an
integer argument that identifies the
zero-based index of the column from which the data should be returned.
After the use of the data reader, you must have to call its
Close method so that you can use the database connection again
.
IDataReader extends System.IDisposable interface, so that each data reader class implements the Dispose method. Dispose automatically calls Close method.
This article is very useful for
.Net Beginners.
Note : Give Us your valuable feedback in comments. Give your suggestions in articles so we can update our articles accordingly that.
