<%@ Page Language="C#" MasterPageFile="~/howto/howto.master" %> <%@ Register TagPrefix=Acme Namespace=Acme %> <%@ Register TagPrefix="Acme" TagName="SourceRef" Src="~/util/SrcRef.ascx"%>

ADO.NET: Update a Database from a DataSet

This topic illustrates how to update data in a database using a DataSet. It is important to remember that you can also insert, update, and delete data in a database directly using a SqlCommand. Understanding the concepts covered in Populate a DataSet from a Database will help you understand the current topic.

Some of the topics covered in Populate a DataSet from a Database include retrieving data from a database and into a DataSet, and how the DataSet is separate and distinct from the database. Once the DataSet is loaded, you can modify the data, and the DataSet will track the changes.

The DataSet can be considered an in-memory cache of data retrieved from a database. The DataSet consists of a collection of tables, relationships, and constraints. In this example we will show how to use the Add method on the DataTable to add new data to a DataSet. The Add method takes either an array of the expected data columns, or a DataRow.

// Create a new Connection and SqlDataAdapter SqlConnection myConnection = new SqlConnection("server=(local)\\SQLExpress;Integrated Security=SSPI;database=northwind"); SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter("Select * from Customers", myConnection); DataSet myDataSet = new DataSet(); DataRow myDataRow; // Create command builder. This line automatically generates the update commands for you, so you don't // have to provide or create your own. SqlCommandBuilder mySqlCommandBuilder = new SqlCommandBuilder(mySqlDataAdapter); // Set the MissingSchemaAction property to AddWithKey because Fill will not cause primary // key & unique key information to be retrieved unless AddWithKey is specified. mySqlDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey; mySqlDataAdapter.Fill(myDataSet, "Customers"); myDataRow = myDataSet.Tables["Customers"].NewRow(); myDataRow["CustomerId"] = "NewID"; myDataRow["ContactName"] = "New Name"; myDataRow["CompanyName"] = "New Company Name"; myDataSet.Tables["Customers"].Rows.Add(myDataRow); ' Create a new Connection and SqlDataAdapter Dim myConnection as SqlConnection Dim mySqlDataAdapter as SqlDataAdapter myConnection = new SqlConnection("server=(local)\SQLExpress;Integrated Security=SSPI;database=northwind") mySqlDataAdapter = new SqlDataAdapter("Select * from Customers", myConnection) Dim myDataSet as DataSet = new DataSet() Dim myDataRow As DataRow ' Create command builder. This line automatically generates the update commands for you, so you don't ' have to provide or create your own. Dim myDataRowsCommandBuilder as SqlCommandBuilder = new SqlCommandBuilder(mySqlDataAdapter) ' Set the MissingSchemaAction property to AddWithKey because Fill will not cause primary ' key & unique key information to be retrieved unless AddWithKey is specified. mySqlDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey mySqlDataAdapter.Fill(myDataSet, "Customers") myDataRow = myDataSet.Tables("Customers").NewRow() myDataRow("CustomerId") = "NewID" myDataRow("ContactName") = "New Name" myDataRow("CompanyName") = "New Company Name" myDataSet.Tables("Customers").Rows.Add(myDataRow) // Create a new Connection and SqlDataAdapter SqlConnection * myConnection = new SqlConnection(S"server=(local)\\SQLExpress;Trusted_Connection=yes;database=northwind;provider=SQLNCLI"); SqlDataAdapter * mySqlDataAdapter = new SqlDataAdapter("Select * from Region", myConnection); DataSet * myDataSet = new DataSet(); DataRow * myDataRow; // Create command builder. This line automatically generates the update commands for you, so you don't // have to provide or create your own. SqlCommandBuilder * mySqlCommandBuilder = new SqlCommandBuilder(mySqlDataAdapter); // Set the MissingSchemaAction property to AddWithKey because Fill will not cause primary // key & unique key information to be retrieved unless AddWithKey is specified. mySqlDataAdapter->MissingSchemaAction = MissingSchemaAction::AddWithKey; mySqlDataAdapter->Fill(myDataSet, Customers"); DataRow * myDataRow = myDataSet->Tables->Item["Customers"]->NewRow(); myDataRow->Item["CustomerId"] = "NewID"; myDataRow->Item["ContactName"] = "New Name"; myDataRow->Item["CompanyName"] = "New Company Name"; myDataSet->Tables->Item["Customers"]->Rows->Add(myDataRow);

Note that the DataTable must return a DataRow through the NewRow method. The method returns a DataRow object with the appropriate schema of the DataTable. The new DataRow is independent of the table until it is added to the RowsCollection.

You can change data in a DataRow by accessing the DataRow. You can use the index of the row in the RowsCollection accessed through the Rows property:

myDataSet.Tables["Customers"].Rows[0]["ContactName"]="Peach"; myDataSet.Tables("Customers").Rows(0)("ContactName")="Peach" myDataSet->Tables->Item["Customers"]->Rows->Item[0]["ContactName"]="Peach";

You can also access a specific row by the Primary Key value:

DataRow myDataRow1 = myDataSet.Tables["Customers"].Rows.Find("ALFKI"); myDataRow1["ContactName"]="Peach"; Dim myDataRow1 As DataRow = myDataSet.Tables("Customers").Rows.Find("ALFKI") myDataRow1("ContactName")="Peach" DataRow * myDataRow1 = myDataSet->Tables->Item["Customers"]->Rows->Find("ALFKI"); myDataRow1->Item["ContactName"]="Peach";

where "ALFKI" is the value of the Primary Key "CustomerID" in the "Customers" table. When using the SqlDataAdapter, the Key is established from the database. You can also set the Key if you are not using the database through the PrimaryKey property.

Use the Delete method to remove the Row. Note that a logical deletion occurs in the DataSet, which only results in a hard deletion once the DataSet is updated to the database. Similarly you can use RejectChanges on the DataSet, in which case the Row is restored.

myDataSet.Tables["Customers"].Rows[0].Delete(); myDataSet.Tables("Customers").Rows(0).Delete() myDataSet->Tables->Item["Customers"]->Rows[0]->Delete();

The original and new values are maintained in the row. The RowChanging event allows you to access both original and new values to decide whether you want the edit to proceed. Because we maintain original and new values, we can establish scenarios such as optimistic locking and key changes.

Before submitting the update back to the database, you need to setup the InsertCommand, UpdateCommand, and DeleteCommand to reconcile the changes to the database. For limited scenarios you can use the SqlCommandBuilder to automatically generate those for you, as is shown in the following example:

SqlCommandBuilder mySqlCommandBuilder = new SqlCommandBuilder(mySqlDataAdapter); Dim myDataRowsCommandBuilder as SqlCommandBuilder = new SqlCommandBuilder(mySqlDataAdapter) SqlCommandBuilder * mySqlCommandBuilder = new SqlCommandBuilder(mySqlDataAdapter);

To submit the data from the DataSet into the database, use the Update method on the SqlDataAdapter.

mySqlDataAdapter.Update(myDataSet, "Customers"); mySqlDataAdapter.Update(myDataSet, "Customers") mySqlDataAdapter->Update(myDataSet, "Customers");

The following example demonstrates how to get data from a database using a SqlDataAdapter, modify the data within the DataSet, and then submit the data back to the database through the SqlDataAdapter.