SqlDataReader
SqlDataReader provides a way to reading a forward-only (read-only) stream of rows from a SQL Server Database. SqlDataReader class has no direct constructor. ADO.NET developers need to call the SqlCommand object's ExecuteReader method to create a SqlDataReader. When SqlDataReader is being used, its associated SqlConnection cannot perform any other operation without closing it. SqlDataReader Close() method close the SqlDataReader object. We should always call the Close() method after finishing using the DataReader object.
DataReader Read() method is used to obtain a row from the results of the query. We can access each column of the returned row by passing the name or ordinal reference of the column to the DataReader. DataReader can retrieve multiple result sets. DataReader also can retrieve schema information about the current result set using the GetSchemaTable method.
The SqlDataReader Connection property gets the associated SqlConnection. Depth property gets a value that indicates the depth of nesting for the current row. FieldCount property gets the number of columns in the current row. HasRows property value indicates whether the DataReader contains one or more rows. IsClosed property provides a Boolean value that indicates specified SqlDataReader instance has been closed or not. Item[32] provides the value of a specified column in its native format by column ordinal. Item[String] provide the specified column in its native format by column name. RecordsAffected property provides the number of rows changed, inserted, or deleted by execution of the SQL statement.
There are many useful methods exist in SqlDataReader class such as Close(), Dispose(), Finalize, GetBoolean, GetByte, GetChar, GetData, GetDateTime, GetDouble, GetEnumerator, GetDecimal, GetFloat, GetGuid, GetInt32, GetName, GetOrdinal, GetSqlDateTime, GetSqlDecimal, GetSqlString, GetXmlReader, IsCommandBehavior, IsDBNull, NextResult, NextResultAsync(), Read, ReadAsync(), ToString, etc. The GetString method gets the value of the specified column as a string. The GetTimeSpan method retrieves a specified column value as a TimeSpan object.
The following c# example source code describes more about DataReader.
DataReaderExample.aspx
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Configuration" %>
<!DOCTYPE html>
<script runat="server">
protected void Page_Load(object sender, System.EventArgs e) {
if (!Page.IsPostBack) {
SqlConnection MyConnection;
SqlCommand MyCommand;
SqlDataReader MyReader;
MyConnection = new SqlConnection();
MyConnection.ConnectionString = ConfigurationManager.ConnectionStrings["AppConnectionString1"].ConnectionString;
MyCommand = new SqlCommand();
MyCommand.CommandText = "SELECT TOP 10 * From PRODUCTS";
MyCommand.CommandType = CommandType.Text;
MyCommand.Connection = MyConnection;
MyCommand.Connection.Open();
MyReader = MyCommand.ExecuteReader(CommandBehavior.CloseConnection);
GridView1.DataSource = MyReader;
GridView1.DataBind();
MyCommand.Dispose();
MyConnection.Dispose();
}
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>DataReader example: how to use DataReader in asp.net</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
</div>
</form>
</body>
</html>
