This article has been excerpted from book "A Programmer's Guide to ADO.NET in C#".
The way the architecture works with the DataAdapter is that you can make any changes you want to the filled DataSet, and it won't affect the database until you call the Update method. When Update is called, the DataAdapter will attempt to execute each query (UPDATE, INSERT, DELETE) on every row of the DataSet that has been updated, inserted, and deleted. For example, if you call Delete on a row on the DataSet, then when Update is called on the DataAdapter, the DeleteCommand of the DataAdapter will be called using the particular row in the DataSet.
Note: Keep in mind that this Update is different than a SQL UPDATE statement.
To insert, update, and delete data using a DataAdapter, you create an OleDbCommand object with INSERT, UPDATE, and DELETE statements and set the InsertCommand, UpdateCommand, and DeleteCommand properties of the data adapter to OleDbCommand. You can avoid building Command objects manually by using CommandBuilder.
As you saw in the DataTable, DataRow, and DataColumn sections at the beginning of this article, you can use the Delete method of a DataRow to delete a row. The AddNew method of the DataTable adds a row to a DataTable. To change an existing row's data, you can directly assign a row's value. To save data to the data source, you need to call the DataAdapter's Update method.
Note: The Tables member of DataSet represents all DataTable objects attached to a DataSet.
Listing 5-46 Creates a new DataRow, sets the data of DataRow members, and adds rows to the table using the Add method of DataTable.Rows.
Listing 5-46. Adding a DataRow to a DataTable
DataRow row = ds.Tables["Customers"].NewRow();
row["CustomerId"] = "TstID ";
row["ContactName "] = "Lana Jackob ";
row["CompanyName"] = "Mindcracker Inc. ";
ds.Tables["Customers"].Rows.Add(row);
Listing 5-47 deletes data by using the DataRow's Delete method.
Listing 5-47. Deleting a Data Row From a Data Table
DataRow row1 = ds.Tables["Customers"].Rows.Find("TstID");
row1.Delete( );
In this example, I'll show you how to add edit, and remove, data from the Northwind database. I'll display the result of the Customers table in a DataGrid after adding, updating, and deleting data from the table. To test this source code create a Windows application, drop a DataGrid, and three buttons- InsertCommand, UpdateCommand, and DeleteCommand – to the form. Listing 5-48 show the code on the InsertCommand button-click event. As you can see from listing 5-48, I created a filled DataSet and created a new DataRow using the DataTable.NewRow method. After creating a DataRow, I set its column values and called the Data Adapter's Update method and displayed data in the DataGrid. I added new rows to the Customers table with CustomerId, CustomerName, and CompanyName (named TstID, Lana Jackob, and Mindcracker Inc., respectively).
Listing 5-48. Adding data using a Data Adapter's Update method
private void InsertCommand_click(object sender, System.EventArgs e)
{
string ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data source = C:/Northwind.mdb";
OleDbConnection conn = new OleDbConnection(ConnectionString );
DataRow row;
DataSet ds = new DataSet();
try
{
conn.Open();
OleDbDataAdapter adapter = new OleDbDataAdapter
("SELECT * FROM Customers", conn);
OleDbCommandBuilder cmdBuilder = new OleDbCommandBuilder(adapter);
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
adapter.Fill(ds, "Customers");
row = ds.Tables["Customers"].NewRow( );
row["CustomersId"] = "TstID";
row["ContactName"] = "Lana Jackob";
row["CompanyName"] = "Mindcracker Inc. ";
ds.Tables ["customers"].Rows.Add(row);
adapter.Update(ds, "Customers");
dataGrid1.DataSource = ds.DefaultViewManager;
}
catch(OleDbException exp)
{
MessageBox.Show(exp.Message.ToString());
}
if (conn.State == ConnectionState.Open)
conn.Close();
}
Listing 5-49 shows an example that edits row data. The Find method returns the row with CustomerId = TstID. After that I assign values of the row and call the DataAdapter's Update method.
Listing 5-49. Updating data using the DataAdapter's Update method
private void UpdateCommand_click(object sender, System.EventArgs e)
{
string ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data source =C:/Northwind.mdb";
OleDbConnection conn = new OleDbConnection(ConnectionString);
DataSet ds = new DataSet();
try
{
conn.Open();
OleDbDataAdapter adapter = new OleDbDataAdapter ( "SELECT * FROM Customers", conn);
OleDbCommandBuilder cmdBuilder = new OleDbCommandBuilder(adapter);
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
adapter.Fill(ds, "Customers");
DataRow row1= ds.Tables["Customers"].Rows.Find("TstID");
row1["ContactName "] = "Stacy Monter ";
row1["CompanyName"] = "Monter Inc. ";
adapter.Update(ds, "Customers");
dataGrid1.DataSource = ds.DefaultViewManager;
}
catch(OleDbException exp)
{
MessageBox.Show(exp.Message.ToString());
}
if (conn.State == ConnectionState.Open)
conn.Close();
}
Deleting a row is simple. Listing 5-50 deletes the row with the value TstID. As you can see from the code, I called the Find method to find the row and called the Delete method of the DataRow. After that I called the DataAdapter's Update method to save the changes to the database table.
Listing 5-50. Deleting data using the DataAdapter's Update method
private void DeleteCommand_Click(object sender, System.EventArgs e)
{
string ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=c:\\ Northwind.mdb";
OleDbConnection conn = new OleDbConnection(ConnectionString);
DataSet ds = new DataSet();
try
{
conn.Open();
OleDbDataAdapter adapter = new OleDbDataAdapter
("SELECT * FROM Customers", conn);
OleDbCommandBuilder cmdBuilder = new OleDbCommandBuilder(adapter);
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
adapter.Fill(ds, "Customers");
DataRow row1 = ds.Tables["Customers"].Rows.Find("TstID");
row1.Delete();
adapter.Update(ds, "Customers");
dataGrid1.DataSource = ds.DefaultViewManager;
}
catch (OleDbException exp)
{
MessageBox.Show(exp.Message.ToString());
}
if(conn.State == ConnectionState.Open)
conn.Close();
}
Conclusion
Hope this article would have helped you in understanding Updating the Database Using the Update Method in ADO.NET. See my other articles on the website on ADO.NET.
 |
This essential guide to Microsoft's ADO.NET overviews C#, then leads you toward deeper understanding of ADO.NET. |