Sql Data Reader

Sql Data Reader:

Data Reader object allows you to perform forward only read only operations over the database. Data Reader is another connected class of Ado.net and is one of the two available data storages objects of Ado.net object model.

//Read the value of connectionstring form the 
web.config file and assign it to cnnstring variable.
cnnstring = ConfigurationManager.ConnectionStrings["cnn"].ConnectionString;
//Initialize the connection object.
SqlConnection cnn = new SqlConnection(cnnstring);
//pass the sql query to retrieve the records.
SqlCommand cmd = new SqlCommand("select * from employeedetails", cnn);
cnn.Open();
//Initialize the data reader object.
SqlDataReader reader = cmd.ExecuteReader();
//Read the values and bind to the controls.
while (reader.Read())
    {
       txtemployeeid.Text = reader.GetInt32(0).ToString();
       txtempname.Text = reader.GetString(1);
       txtphoneno.Text = reader.GetString(2);
       txtaddress.Text = reader.GetString(3);
       txtdob.Text = reader.GetString(4);
    }
//finally close the reader and connection.
reader.Close();
cnn.Close();

Multiple Active Result Sets:
Data Reader allows you to read the values in faster way, but if you are using multiple data reader objects then you will end up ‘invalidoperationexception’ error. In order to overcome this problem you can use MARS property of the data reader object. You need set ‘MultipleActiveResultSets = true’ in the connection string. ConnectionString:

<connectionStrings>
 <add name="cnn" connectionString="Data Source=.\SQLEXPRESS;
 AttachDbFilename=|DataDirectory|\Tutorial.mdf;Integrated Security=True;
 User Instance=True; MultipleActiveResultSets = True"/>
 </connectionStrings>

The following code explains about working with multiple active result sets.

//Read the value of connectionstring form the web.config
file and assign it to cnnstring variable.
cnnstring = ConfigurationManager.ConnectionStrings["cnn"].ConnectionString;
//Initialize the connection object.
SqlConnection cnn = new SqlConnection(cnnstring);
//pass the sql query to retrieve the records.
SqlCommand cmd = new SqlCommand("select * from employeedetails", cnn);
SqlCommand cd = new SqlCommand("select employeename from employeedetails" +
                         " where empolyeeid ='100' ", cnn);
cnn.Open();
//Initialize the data reader object.
SqlDataReader reader = cmd.ExecuteReader();
//Read the values and bind to the controls.
SqlDataReader empnamereader = cd.ExecuteReader();
    while (empnamereader.Read())
    {
      string employeename = empnamereader.GetString(0);
      Response.Write("<h1>Employee Name:" + employeename + "</h1>");
    }
    empnamereader.Close();
    while (reader.Read())
    {
        txtemployeeid.Text = reader.GetInt32(0).ToString();
        txtempname.Text = reader.GetString(1);
        txtphoneno.Text = reader.GetString(2);
        txtaddress.Text = reader.GetString(3);
        txtdob.Text = reader.GetString(4);
    }
//finally close the reader and connection.
reader.Close();
cnn.Close();