The use of data adapter.?

data adapter:-The DataAdapter serve as a bridge between a DataSet and data source for retrieving and saving data. The DataAdapter provides this bridge by using Fill to load data from the data source into the DataSet and using Update to send changes made in the DataSet back to the data source.  The data adapter objects connect a command objects to a Dataset object. They provide the means for the exchange of data between the data store and the tables in the DataSet.An OleDbDataAdapter object is used with an OLE-DB provider
A SqlDataAdapter object uses Tabular Data Services with MS SQL Server.

Namespace:  System.Data.Common
Assembly:  System.Data (in System.Data.dll)

Data adapter Flow  Architecture

There are two constructor:

Property  of Data Adapter?

there are following main property:

Method  of Data Adapter?

there are following main method:-

Event of Data Adapter?

There are two events:-

Basic method of Data Adapter?

There are three basic method in data adopter:-

(i)Fill:-Fill  method executes the SelectCommand to fill the DataSet object with data from the data source,Depending on whether there is a primary key in the DataSet, the ‘fill’ can also be used to update an existing table in a DataSet with changes made to the data in the original datasource.

(ii)FillSchema:-Fill Schema method executes the SelectCommand to extract the schema of a table from the data source and  creates an empty table in the DataSet object with all the corresponding constraints.

(iii0Update:-Update method executes the InsertCommand, UpdateCommand, or DeleteCommand to update the original data source with the changes made to the content of the DataSet.

(iv) Dispose :- releases all the resources

The steps involved to fill a dataset?

There are following  steps  to  fill  a  dataset:-

(i). Create a connection object.
(ii). Create an adapter by passing the string query and the connection object as parameters.
{iii). Create a new object of dataset.
(iv). Call the Fill method of the adapter and pass the dataset object.

We can  check that some changes have been made to dataset since it was loaded?

There are following way to check that some changes have been made to dataset since it was loaded:-

(i). GetChanges: gives the dataset that has changed since newly loaded or since Accept changes has been executed.
{ii}. HasChanges: this returns a status that tells if any changes have been made to the dataset since accept changes was executed.

How can we add/remove row’s in “DataTable” object of “DataSet”?

Using NewRow method we can add row's in a data table  object of dataset.Remove method of the DataRowCollection is used to remove a ‘DataRow’ object from ‘DataTable’.
RemoveAt method of the DataRowCollection is used to remove a ‘DataRow’ object from ‘DataTable’ per the index specified in the DataTable.

The underlying database can be updated directly by passing SQL INSERT/UPDATE/ DELETE statements, or stored procedure calls, through to the managed provider. It

We can also be updated using a DataSet.

1. Create and fill the DataSet with one or more DataTables

2. Call DataRow.BeginEdit on a DataRow

3. Make changes to the row’s data

4. Call DataRow.EndEdit

5. Call SqlDataAdapter.Update to update the underlying database

6. Call DataSet.AcceptChanges (or DataTable.AcceptChanges orDataRow.AcceptChanges)

Example :-UPDATING THE DATABASE USING A DATASET

To update the database directly, we can use the SqlCommand object, which allows us to execute SQL INSERT, UPDATE, and DELETE statements against a database.

Using System;
using System.Data;
using System.Data.SqlClient;
public class empdel {
public static void Main() {
SqlConnection con = new SqlConnection(
@"server=(local)\computer;database=emp;trusted_connection=yes"
);
string sql = "DELETE FROM emp WHERE emp_name = 'Ashok'";
SqlCommand cmd = new SqlCommand(sql, con);
con.Open();
int i = cmd.ExecuteNonQuery();
con.Close();
Console.WriteLine("{0} record(s) deleted.", i);
}
}

DataSet ds = new DataSet();

SqlDataAdapter da = new SqlDataAdapter ("Emp", this.Connection);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
da.SelectCommand.Parameters.AddWithValue ("@EId", EId);
da.TableMappings.Add ("Table", ds.xval.TableName);
da.Fill (ds); 

DataSet ds = new DataSet();
ds.Tables.Add(dt1);
ds.Tables.Add(dt2);
ds.Tables.Add(dt3);
ds.Tables.Add(dt4);
ds.Tables.Add(dt5);
..................
.................
..................
ds.Tables.Add(dtn);; 

Dataset object in ADO .NET

The DataSet object is a disconnected storage.DataSet is a disconnected  in-memory representation of data. It can be considered as a local copy of the relevant portions of the database. The DataSet is persisted in memory and the data in it can be manipulated and updated independent of the database. It represents related tables, constraints, and relationships among the tables. DataSet reads and writes data and schema as XML documents.The DataSet object is a disconnected storage.It is used for manipulation of relational data. The DataSet is filled with data from the storeWe fill it with data fetched from the data store. Once the work is done with the dataset, connection is reestablished and the changes are reflected back into the store.

ADO.NET DataSet can contain more than one table and also contains information about table relationships, the columns they contain, and any   constraints that apply. Each table in a DataSet can contain multiple rows. Once data is retrieved from the database into a DataSet object, an application can disconnect from the database before processing the DataSet. This is an important feature of the ADO.NET DataSet. It gives us an in-memory, disconnected copy of a portion of the database which we can process without retaining an active connection to the database server.

using System;
using System.Data;
using System.Data.SqlClient;
public class empcount {
public static void Main() {
// change the following connection string, as necessary...
string con =
@"server=(local)\kamal;database=Emp;trusted_connection=yes";
DataSet ds = new DataSet("EmpDataSet");
SqlDataAdapter sad;
string sl;
sl = "SELECT COUNT(*) AS cnt FROM employee";
sad = new SqlDataAdapter(sl, con);
sad.Fill(ds, "emp_count");
sl = "SELECT COUNT(*) AS cnt FROM compocation";
sad = new SqlDataAdapter(sl, con);
sad.Fill(ds, "comploc_count");
int totalemp = (int) ds.Tables["emp_count"].Rows[0]["cnt"];
int comploc = (int) ds.Tables["comploc_count"].Rows[0]["cnt"];
Console.WriteLine(
"There are {0} employee, {1} complocation .",
totalemp ,
 comploc,

);
}
}

1)StateChange

To display the previous and current Connection states for each of the two Connection objects: Select OleDbConnection1 in the Class Name combobox of the editor and the StateChange event in the Method Name combobox.

private void oleDbConnection1_StateChange (object sender,StateChangeEventArgs
 e)
 {
string s;
 s = "The Connection State is changing from " + e.OriginalState.ToString() +
 " to " + e.CurrentState.ToString();
 MessageBox.Show(s);
 }
 private void SqlDbConnection1_StateChange (object sender, StateChangeEventArgs
 e)
 {
 string s1;
 s1 = "The Connection State is changing from " +
 e.OriginalState.ToString() +
 " to " + e.CurrentState.ToString();
 MessageBox.Show(s1);
}

 Add the code to connect the event handlers to the:-

ConnectionProperties sub:

 this.oleDbConnection1.StateChange += newSystem.Data.StateChangeEventHandler(this.oleDbConnection1_StateChange);

this.SqlDbConnection1.StateChange += new System.Data.StateChangeEventHandler(this.SqlDbConnection1_StateChange);

. Save and run the program. Change the Connection Type and then click the Test button.

The application displays two MessageBoxes as the Connection is opened and closed.

2)InfoMessage

The SQLCONNECTION OBJECT

A SqlConnection is an object,as any other C# object.  we  just declare and instantiate the SqlConnection all at the same time, as shown below:

SqlConnection con = new SqlConnection(
    "Data Source=(local);Initial Catalog=Emp;Integrated Security=sspi");

The SqlConnection object instantiated above  by using  a constructor with a single argument of type string and this argument is called a connection string. 

There are four Connection String Parameter Name:-

1)Data Source: Data Source Identifies the server and it  Could be local machine, machine domain name, or IP Address.

2)Initial Catalog: Database name

3)Integrated Security: Integrated Security set to SSPI to make connection with user's Windows login.

4)User ID:  Name of user configured in SQL Server.

5)Password: Password matching SQL Server User ID.

Using a SqlConnection

The aim of creating a SqlConnection object is so we can enable other ADO.NET code to work with a database and  SqlCommand and a SqlDataAdapter take it a connection object as a parameter.  The sequence of operations occurring in the lifetime of a SqlConnection are given below:

1. Instantiate the SqlConnection.
2. Open the connection.
3. Pass the connection to other ADO.NET objects.
4. Perform database operations with the other ADO.NET objects.
5. Close the connection.

The Open() Method:

The Open() method of the Connection object establishes a connection  to the data source.,because database connections are a very expensive resource memory-wise, so we should only call the Open() method just before we're ready to retrieve the data. This ensures that the connection is not open any longer than it needs to be.

The Close() Method

After we are done retrieving data,we should call the Close() method of the Connection object. This closes the connection to the database.

using System;
using System.Data;
using System.Data.SqlClient;

class sqlConnectivityexample
{
    static void Main()
    {
      
        SqlConnection conn = new SqlConnection(
            "Data Source=(local);Initial Catalog=Emp;Integrated Security=SSPI");

        SqlDataReader rdr = null;

        try
        {
            // 2. Open the connection
            conn.Open();

            // 3. Pass the connection to a command object
            SqlCommand cmd = new SqlCommand("select * from emp", conn);

            //
            // 4. Use the connection
            //

            // get query results
            rdr = cmd.ExecuteReader();

            // print the CustomerID of each record
            while (rdr.Read())
            {
                Console.WriteLine(rdr[0]);
            }
        }
        finally
        {
            // close the reader
            if (rdr != null)
            {
                rdr.Close();
            }

            // 5. Close the connection
            if (conn != null)
            {
                conn.Close();
            }
        }
    }
}

The SQLCOMMAND OBJECT

Creating a SqlCommand Object

1)prepare command string
 string updateString = @"
     update Emp
     set EmpName = 'Ashish'
     where CategoryName = 'Raj'";
 
2) Instantiate a new command with command text only
 SqlCommand cmd = new SqlCommand(updateString,conn);
 
 
3) Call ExecuteNonQuery to send command
 cmd.ExecuteNonQuery();

1) delete command string
 string deleteString = @"
     delete from Emp
     where EmpName = 'Ashish'";
 
2) Instantiate a new command
 SqlCommand cmd = new SqlCommand();
 
3) Set the CommandText property
 cmd.CommandText = deleteString;
 
4) Set the Connection property
 cmd.Connection = conn;
 
5)Call ExecuteNonQuery to send command
 cmd.ExecuteNonQuery();

    For getting a single value we can use  count, sum, average, or other aggregated value from a data set.

1) Instantiate a new command
 SqlCommand cmd = new SqlCommand("select count(*) from Categories", conn);
 
2) Call ExecuteNonQuery to send command
 int count = (int)cmd.ExecuteScalar();

1. Introduction of ADO.NET

2. THE ADO.NET  Architecture

3. Component classes

4. The SQLCOMMAND OBJECT

5. Command Constructors

6. The SQLCONNECTION OBJECT

7. Handling Connection Events

8. Dataset object in ADO .NET

9. The steps involved to fill a dataset?

10. Data adapter