003 ADO.Net DataAdopter

DataAdopter 
                 DataAdopter is essential component in ADO.net. DataAdopter fetch data from database by connecting database with connection string . DataAdopter also update database with the DataSet . SqlCommand / OleDbCommand  is used to fetch data from database . After fetching data, DataAdopter fill the data into the DataSet.

                  SqlDataAdapter da = new SqlDataAdapter(SQL command, connection string);

 
                 It is also possible to Execute stored procedure in the DataAdopter. You will see it in the later example. Instance of a DataAdopter is created with the new operator. During instance creation, two parameter is required, SQL command and connection string and SqlCommand ,  maybe insert command ,update command , delete command . Connection string, convey information to connect  a  database with security. Here is some most commonly used attribute of a  DataAdopter.

• Constructor

1. DataAdopter : Initialize new instance of a DataAdopter with parameter. 

• Method

1. Fill  : DataAdopter  fill  a DataSet with the data  fetch from database. 
2. Update : Insert or delete or update statement to the database. 
3. Dispose : Release all resources of DataAdopter.

1) DataSet  fill by DataAdopter
 

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= xxxxx;Password=xxxxxxxx;";

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

                             SqlDataAdapter da = new SqlDataAdapter(cmd, conStr);

                             DataSet ds = new DataSet();

                             da.Fill(ds);

                            for (int i = 0; i < ds.Tables.Count; i++)

                           {

                                for (int l = 0; l < ds.Tables[i].Rows.Count; l++)

                                    {

                                      Console.WriteLine(ds.Tables[i].Rows[l][0].ToString());

                                   }

               }

                          Console.ReadKey();

                   }

           }

}

 Output :
Student 1
Student 2
Student 3
Student 4
Student 5

2) DataAdopter with Store 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.xx; initial catalog=xxxxx;Connect Timeout=6000; User ID= xxxxxxxx;Password=xxxx;";

              string cmd = "StoreProcedure1";

              SqlDataAdapter da = new SqlDataAdapter(cmd, conStr);

              DataSet ds = new DataSet();

              da.Fill(ds);

               for (int i = 0; i < ds.Tables.Count; i++)

               {

                           for (int l = 0; l < ds.Tables[i].Rows.Count; l++)

                               {

                                 Console.WriteLine(ds.Tables[i].Rows[l][0].ToString());

                           }

                 }

           Console.ReadKey();

           }

    }

}

 Output :
Student 1  Girl
Student 2  Boys
Student 3  Girl
Student 4  Boys
Student 5  Girl

3) Fill a DataSet from multiple DataAdopter
 

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= xxxx;Password=xxxxx;";

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

                   SqlDataAdapter da = new SqlDataAdapter(cmd1, conStr);

                   DataSet ds = new DataSet();

                   da.Fill(ds);

                   string cmd2 = "select top 5 * from tbl_event";

                   SqlDataAdapter da1 = new SqlDataAdapter(cmd2, conStr);

                   da.Fill(ds);

                    for (int i = 0; i < ds.Tables.Count; i++)

                    {

                             for (int l = 0; l < ds.Tables[i].Rows.Count; l++)

                              {

                                     Console.WriteLine(ds.Tables[i].Rows[l][0].ToString());

                              }

                    }

                        Console.ReadKey();

           }

       }

}

 Output :
Student 1 
Student 2 
Student 3 
Student 4 
Student 5  
Event 1
Event 2
Event 3
Event 4
Event 5

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