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