004 ADO.Net DataReader

DataReader
DataReader is a component .Net framework. DataReader is used for reading data. This is connection oriented forward only. An active open connection is needed to execute a reader, once a connection is assigned to the reader , until the DataReader  is  closed , you cannot  reuse this connection. DataReader instant cannot be created with new operator, Sqlcommand / Oledbcommand is executed with ExecuteReader  method  to create DataReader  instance.

                          There are two type of DataReader, SQL and Oleddb. SQL DataReader comes under namespace System.Sata.Sqlclient  ,Oledb Data Reader comes under  System.Data.OleDb. Below is the syntax of creating an instance of a DataReader.

                    SqlCommand cmd = new SqlCommand("SELECT * FROM FROM student;");

           SqlDataReader reader = cmd.ExecuteReader();

                          After the instance is created, DataReader  read data row by row. While and do while loop is used to read data row by row from Data reader.Reader.read() .Read method return true ,if  there is next row , if there is no next row , Data Reader return false. Data Reader read row by row  and column can be access  with the help of index or column name.

              SqlDataReader reader = cmd.ExecuteReader();

                           while(reader.Read())

                           {

                                 Console.Write(reader["FirstName"].ToString());

                           }

 
More than one set of data can be read from Data Reader. NextResult method will read  next result set of data . Finally Data Reader needed to close when reading ends with close methods .This close Data Reader and release the connection for re-use.

            SqlDataReader reader = cmd.ExecuteReader();

                      while (reader.Read())

                      {

                             while (reader.Read())

                            {

                            }

                            reader.NextResult();

                    }

You can retrieve the schema of the resultant set .GetSchemaTable()  method return the schema of the current result set. You can get current set name, row and column information attribute like AllowDbNull, Isunique, Data type name, column name, column size, Isidentity information can be retrieved also.

1) Data Reader with Read Data With Loop

using System;

using System.Data;

using System.Data.SqlClient; 

  

namespace ConsoleApplication1

{

             class Program

             {

                   static void Main(string[] args)

                   {

                      string conStr = "Data Source= xxx.xxx.xx.xx; initial catalog=xxxxxxx;Connect  Timeout=6000; User ID= xxxxxxxxxxx;Password=xxxxxxxxxxxx;";

                      string cmd = "Select top 5 * from tbl_student"; 

                       using (SqlConnection con = new SqlConnection(conStr))

                       {

                            SqlCommand command =new SqlCommand(cmd, con);

                            con.Open(); 

                            SqlDataReader reader = command.ExecuteReader();

                            while (reader.Read())

                             {

                                  Console.WriteLine(reader[0].ToString());

                              }

                             reader.Close();

                        }

                          Console.ReadKey();

              }

}

Output :
John
Shaym
Jatin
Greg
Nathan


2) Data Reader with Multiple Resultset
 

using System;

using System.Data;

using System.Data.SqlClient;

 

namespace ConsoleApplication1

{

          class Program

         {

              static void Main(string[] args)

             {

                                string conStr = "Data Source= xxx.xxx.xx.xx; initial catalog=xxxxx;Connect Timeout=6000; User ID= xxxxx;Password=xxxxxxxxx;";

                               string cmd = "Select top 5 * from tbl_student;Select top 5 * from tbl_event";

                              using (SqlConnection con = new SqlConnection(conStr))

                             {

                                 SqlCommand command =new SqlCommand(cmd, con);

                                 con.Open();

                                 SqlDataReader reader = command.ExecuteReader();

     

                                while (reader.Read())

                                {

                                      Console.WriteLine(reader[0].ToString());

                                }

                                  Console.WriteLine("--------------------------------------------------");

                                 reader.NextResult();

                                while (reader.Read())

                                {

                                       Console.WriteLine(reader[0].ToString());

                                 }

                                reader.Close();

                       }

                    Console.ReadKey();

               }

          }

}

Output :
John
Shaym
Jatin
Greg
Nathan
---------------------------------------------
Sport Winter Camp
Sport Summer Camp 
Under 16 Free hit
Unisex Long Jump

3) Data Reader with stored procedure

using System;

using System.Data;

using System.Data.SqlClient;

namespace ConsoleApplication1

{

              class Program

              {

                static void Main(string[] args)

                {

                          string conStr = "Data Source= xxx.xxx.xx.xxx; initial catalog=xxxxxxxx;Connect Timeout=6000; User ID= xxxxxxxxx;Password=xxxxxxxx;";

                          string cmd = "Your Procedure Name";

                           using (SqlConnection con = new SqlConnection(conStr))

                          {

                              SqlCommand command =new SqlCommand(cmd, con);

                              con.Open();

                              SqlDataReader reader = command.ExecuteReader();

                              while (reader.Read())

                              {

                                      Console.WriteLine(reader[0].ToString());

                               }

                    reader.Close();

                   }

             Console.ReadKey();

         }

      }

}

4) Data Reader with Get Table schema.
 

using System;

using System.Data;

using System.Data.SqlClient;

namespace ConsoleApplication1

{

          class Program

          {

             static void Main(string[] args)

              {

                    string conStr = "Data Source= xxx.xx.xx.xxx; initial catalog=xxxxxxx;Connect Timeout=6000; User ID= xxxxxx;Password=xxxxxxxx;";

                    string cmd = "select top 5 * from tbl_student";

                    using (SqlConnection con = new SqlConnection(conStr))

                   {

                             SqlCommand command =new SqlCommand(cmd, con);

                             con.Open();

                             SqlDataReader reader = command.ExecuteReader();

                             DataTable schemaTable = reader.GetSchemaTable();

                            foreach (DataColumn column in schemaTable.Columns)

                            {

                                  Console.WriteLine(column.ColumnName + ":" + schemaTable.Rows[0][column.ColumnName].ToString());

                            }

                     reader.Close();

                  }

             Console.ReadKey();

           }

       }

}

Output :
ColumnName:member_id
ColumnOrdinal:0
ColumnSize:4
NumericPrecision:10
NumericScale:255
IsUnique:False
IsKey:
BaseServerName:
BaseCatalogName:
BaseColumnName:member_id
BaseSchemaName:
BaseTableName:
DataType:System.Int32
AllowDBNull:False
ProviderType:8
IsAliased:
IsExpression:
IsIdentity:False
IsAutoIncrement:False
IsRowVersion:False
IsHidden:
IsLong:False
IsReadOnly:False
ProviderSpecificDataType:System.Data.SqlTypes.SqlInt32
DataTypeName:int
XmlSchemaCollectionDatabase:
XmlSchemaCollectionOwningSchema:
XmlSchemaCollectionName:
UdtAssemblyQualifiedName:
NonVersionedProviderType:8
IsColumnSet:False