SqlDataAdapter Object:
The SqlDataAdapter serves as a bridge between a DataSet and SQL Server for retrieving and manipulating data. SqlDataAdapter is an intermediary object that populates an ADO.NET DataSet object with data that is retrieved from a SQL Server database and then updates the database to reflect the changes (such as inserts, updates, and deletes) that are made to the data by using the DataSet object.
The InsertCommand, the UpdateCommand, and the DeleteCommand properties of the SqlDataAdapter object update the database with the data modifications that are run on a DataSet object. These properties are SqlCommand objects that specify the INSERT, the UPDATE, and the DELETE Transact-SQL commands that are used to post the dataset modifications to the target database. The SqlCommand objects that are assigned to these properties can be created manually in code or automatically generated by using the SqlCommandBuilder object.
The SqlDataAdapter provides this bridge by mapping Fill method, which changes the data in the DataSet to match the data in the data source, and Update method, which changes the data in the data source to match the data in the DataSet, using the appropriate Transact-SQL statements against the data source. The update is performed on a by-row basis. For every inserted, modified, and deleted row, the Update method determines the type of change that has been performed on it (Insert, Update, or Delete). Depending on the type of change, the Insert, Update, or Delete command template executes to propagate the modified row to the data source. When the SqlDataAdapter fills a DataSet, it creates the necessary tables and columns for the returned data if they do not already exist.
You may have the SqlDataAdapter create the schema of the DataSet, including primary key information, before filling it with data using FillSchema method.
We have seen SqlDataReader to retrieve/change the records in the connected environment in the previous articles. Now we would see how to retrieve/change data in the disconnected environment using the SqlDataApdapter.
SqlDataAdapter opens the connection and fills the DataTable or DataSet and closes the connection. Similarly while updating the SQL Server SqlDataAdapter opens the connection, update the data source and disconnect the connection.
e.g.
// 1. create the connection object
SqlConnection conn = new SqlConnection("Server=(local); DataBase=Northwind; Integrated Security=SSPI");
// 2. create new DataSet
DataSet dsCustomers = new DataSet();
// 3. create SqlDataAdapter with select command and connection
SqlDataAdapter daCustomers = new SqlDataAdapter("select * from Customers", conn);
// 4. create the command builder to generate the update, insert, delete command to update the changes to database
SqlCommandBuilder cmdBldr = new SqlCommandBuilder(daCustomers);
// 4. fill the dataset
daCustomers.Fill(dsCustomers, "Customers");
//5. Make the changes(edit, delete, and addtion) into the DataSet records.
//5a. to edit the existing row values
DataTable dtCustomer = dsCustomers.Tables["Customers"];
DataRow dr = dtCustomer.Rows[0];
dr.BeginEdit();
dr["CompanyName"] = " ABC Inc";
dr.EndEdit();
//5b. to delete the existing row
dr = dtCustomer.Rows[1];
dr.Delete();
//5a. to add new row
dr = dtCustomer.NewRow();
dr["CompanyName"] = " ABC Inc";
//add other fields value.
dtCustomer.Rows.Add(dr);
//6. push the changes ToolBar database
daCustomers.Update(dsCustomers, "Customers");
Summary:
SqlDataAdapter create a bridge to fill a DataSet and Update changes back to the database. You dont open and close the SqlConnection because the SqlDataAdapter does it automatically. An SqlCommandBuilder creates insert, update, and delete commands based on the changes in the DataSet. Fill method is used to populate a DataSet with data. Update method is used to push changes from DataSet back to a database.