So far, you’ve retrieved data from a database, but you haven’t manipulated its records in any way. Using ADO.NET, it is of course possible to add records, change an existing record, or delete a record altogether.
In a typical implementation, you might work your way through the following steps:
Fill the tables for your
DataSet
using a stored procedure or SQL.
Display the data in various DataTable
objects
within your DataSet
by either binding to a control
or looping through the rows in the tables.
Change data in individual
DataTable
objects by adding, modifying, or
deleting DataRow
objects.
Invoke the GetChanges( )
method to create a second
DataSet
that features only the changes to the
data.
Check for errors in the second newly created
DataSet
by examining the
HasErrors
property. If there are errors, check the
HasErrors
property of each
DataTable
in the DataSet
. If
the table has errors, invoke the GetErrors( )
method of the DataTable
and get back an array of
DataRow
objects with errors. On each row you can
examine the RowError
property for specific
information about the error, which you can then resolve.
Merge the second Data
Set
with
the first.
Call the Update( )
method on the
DataAdapter
object and pass in the merged
DataSet
.
Invoke the AcceptChanges( )
method on the
DataSet
, or invoke RejectChanges( )
to cancel the changes.
This process gives you very fine control over the update to your data as well as an opportunity to fix any data that would otherwise cause an error.
In the following example, you’ll create a dialog box that displays the contents of the Customer table in Northwinds. The goal is to test updating a record, adding a new record, and deleting a record. As always, I’ll keep the code as simple as possible, which means eliminating many of the error-checking and exception-handling routines you might expect in a production program.
Figure 14-7 shows the somewhat crude but useful form I’ve built to experiment with these features of ADO.NET.
This form consists of a list box (lbCustomers
), a
button for Update (btnUpdate
), an associated text
box (txtCustomerName
), and a Delete button
(btnDelete
). There is also a set of eight text
fields that are used in conjunction with the New button
(btnNew
). These text fields represent eight of the
fields in the Customers table in the Northwind database. There is
also a label (lblMessage
) that you can use for
writing messages to the user (it currently says Press New, Update, or
Delete).
First, create the DataAdapter
object and the
DataSet
as private member variables, along with
the DataTable
:
private SqlDataAdapter DataAdapter; private DataSet DataSet; private DataTable dataTable;
This enables you to refer to these objects from various member methods. You start by creating strings for the connection and the command that will get you the table you need:
string connectionString = "server=localhost; uid=sa; pwd=; database=northwind"; string commandString = "Select * from Customers";
These strings are passed as parameters to the
SqlDataAdapter
constructor:
DataAdapter = new SqlDataAdapter(commandString, connectionString);
You can now create the DataSet
and fill it with
the SqlDataAdapter
object you’ve just
created:
DataSet = new DataSet( ); DataAdapter.Fill(DataSet,"Customers");
Display the table contents by calling the PopulateLB( )
method, which is a private method that fills the list box
from the contents of the single table in the
DataSet
:
dataTable = DataSet.Tables[0]; lbCustomers.Items.Clear( ); foreach (DataRow dataRow in dataTable.Rows) { lbCustomers.Items.Add( dataRow["CompanyName"] + " (" + dataRow["ContactName"] + ")" ); }
The form is now displayed, and you’re ready to update a record. Highlight a record and fill in a new customer name in the topmost text field. When you press Update, read the resulting name and put it into the chosen record.
The first task is to get the specific row the user wants to change:
DataRow targetRow = dataTable.Rows[lbCustomers.SelectedIndex];
Declare a new object of type DataRow
and
initialize it with a reference to the specific row in the
DataTable
’s Rows
collection that corresponds to the selected item in the list box.
Remember that DataTable
was declared as a member
variable and initialized in the PopulateLB( )
method shown in the previous section.
You can now display the name of the company you’re going to update:
lblMessage.Text = "Updating " + targetRow["CompanyName"]; Application.DoEvents( );
The call to the static method DoEvents( )
of the
Application
class causes the application to
process Windows messages and paint the screen with the message. If
you were to leave this line out, the current thread would dominate
the processor and the messages would not be printed until the button
handler completes its work.
Call BeginEdit( )
on the
DataRow
to put the row into editing mode. This
suspends events on the row so that you could, if you chose, edit a
number of rows at once without triggering validation rules (there are
no validation rules in this example). It is good form to bracket
changes on DataRows
with calls to
BeginEdit( )
and EndEdit( )
:
targetRow.BeginEdit( ); targetRow["CompanyName"] = txtCustomerName.Text; targetRow.EndEdit( );
The actual edit is to the column CompanyName
within the targetRow
object, which is set to the
text value of the text control txtCustomerName
.
The net effect is that the CompanyName
field in
the row is set to whatever the user put into that text box.
Notice that the column you want is indexed within the row by the name
of that column. In this case, the name will match the name that is
used in the database, but that is not required. When you created the
DataSet
, you could have used the
TableMappings( )
method to change the names of the
columns.
Having edited the column, you are ready to check to make sure there
are no errors. First, extract all the changes made to the
DataSet
(in this case, there will be only one
change) using the GetChanges( )
method, passing in
a DataRowState
enumeration to indicate that you
want only those rows that have been modified. GetChanges( )
returns a new DataSet
object:
DataSet DataSetChanged = DataSet.GetChanges(DataRowState.Modified);
Now you can check for errors. To simplify the code, I’ve
included a flag to indicate that all is OK. If you find any errors,
rather than trying to fix them you can just set the flag to
false
and not make the updates:
bool okayFlag = true; if (DataSetChanged.HasErrors) { okayFlag = false; string msg = "Error in row with customer ID "; foreach (DataTable theTable in DataSetChanged.Tables) { if (theTable.HasErrors) { DataRow[] errorRows = theTable.GetErrors( ); foreach (DataRow theRow in errorRows) { msg = msg + theRow["CustomerID"]; } } } lblMessage.Text = msg; }
First test to see whether the new data record set has any errors by
checking the HasErrors
property. If
HasErrors
is true
, there are
errors; set the Boolean okayFlag
to
false
, and then go on to discover where the error
lies. To do so, iterate through all the tables in the new database
(in this case, there is only one), and if a table has errors
you’ll get an array of all the rows in that table with errors
(shown here as the errorRows
array).
Then iterate through the array of rows with errors, handling each in turn. In this case, you just update the message on the dialog box, but in a production environment you might interact with the user to fix the problem.
If the okayFlag
is still true
after testing HasErrors
, there were no errors and
you are ready to update the database. First, merge the new
DataSet
back in with the original (presumably, in
a production program you’d be merging the fixed tables back in
with the original):
if (okayFlag) { DataSet.Merge(DataSetChanged);
You can now update the DataSet
:
DataAdapter.Update(DataSet,"Customers");
This causes the DataAdapter
object to create the
necessary command text to update the database. You can actually see
that text by accessing the CommandText
property of
the DataAdapter
object. You can display the
command in the message text:
lblMessage.Text = DataAdapter.UpdateCommand.CommandText; Application.DoEvents( );
You now must tell the DataSet
to accept the
changes and then repopulate the list box from the
DataSet
:
DataSet.AcceptChanges( ); PopulateLB( );
If okayFlag
were false
, there
would have been errors; in this example, we’d just reject
the
changes:
else DataSet.RejectChanges( );
The code for handling the Delete button is even simpler. First, get the target row:
DataRow targetRow = dataTable.Rows[lbCustomers.SelectedIndex];
and form the delete message:
string msg = targetRow["CompanyName"] + " deleted. ";
You don’t want to show the message until the row is deleted, but you need to get it now because after you delete the row it will be too late!
You’re now ready to delete the row and then update the database
and the DataSet
:
dataTable.Rows[lbCustomers.SelectedIndex].Delete( ); DataSet.AcceptChanges( ); DataAdapter.Update(DataSet,"Customers");
The first of these three lines marks the row for deletion. The second
accepts these changes in the DataSet
, and the
third updates the database with the changed
DataSet
.
Calling AcceptChanges( )
on the
DataSet
causes AcceptChanges( )
to be called on each table within the
DataSet
. The call to AcceptChanges( )
on each table in turn causes AcceptChanges( )
to be called on each row in those tables. Thus the one
call to DataSet.AcceptChanges( )
cascades down
through all the contained tables and rows.
You’re now ready to repopulate the list box and display a message indicating that the record was deleted:
PopulateLB( ); lblMessage.Text = msg; Application.DoEvents( );
Deleting records from the Customers database might cause an exception if the record deleted is constrained by database integrity rules. For example, if a customer has orders in the Orders table, you cannot delete the customer until you delete the orders. To solve this, the following example will create new Customer records that you can then delete at will.
To create
a new record, the user will fill in the fields and press the New
button. This will fire the btnNew_Click
event,
which is tied to the btnNew_Click
event handling
method:
btnNew.Click += new System.EventHandler (this.btnNew_Click);
In the event handler, you call DataTable.NewRow( )
, which asks the table for a new
DataRow
object:
DataRow newRow = dataTable.NewRow( );
This is very elegant because the new row that the
DataTable
produces has all the necessary
DataColumns
for this table. You can just fill in
the columns you care about, taking the text from the user interface
(UI):
newRow["CustomerID"] = txtCompanyID.Text; newRow["CompanyName"] = txtCompanyName.Text; newRow["ContactName"] = txtContactName.Text; newRow["ContactTitle"] = txtContactTitle.Text; newRow["Address"] = txtAddress.Text; newRow["City"] = txtCity.Text; newRow["PostalCode"] = txtZip.Text; newRow["Phone"] = txtPhone.Text;
Now that the row is fully populated, just add it back to the table:
dataTable.Rows.Add(newRow);
The table resides within the DataSet
, so all you
have to do is tell the DataAdapter
object to
update the database with the DataSet
:
DataAdapter.Update(DataSet,"Customers");
Next, update the user interface and tell the
DataSet
to accept the changes:
lblMessage.Text = DataAdapter.UpdateCommand.CommandText; Application.DoEvents( ); DataSet.AcceptChanges( );
You can now repopulate the list box with your new added row and clear the text fields so that you’re ready for another new record:
PopulateLB( ); ClearFields( );
ClearFields( )
is a private method that simply
sets all the text fields to empty strings. That method and the entire
program are shown in Example 14-6.
Example 14-6. Updating, deleting, and adding records
using System; using System.Drawing; using System.Collections; using System.ComponentModel; using System.Windows.Forms; using System.Data; using System.Data.SqlClient; namespace ProgrammingCSharpWindows.Form { public class ADOForm1 : System.Windows.Forms.Form { private System.ComponentModel.Container components; private System.Windows.Forms.Label label9; private System.Windows.Forms.TextBox txtPhone; private System.Windows.Forms.Label label8; private System.Windows.Forms.TextBox txtContactTitle; private System.Windows.Forms.Label label7; private System.Windows.Forms.TextBox txtZip; private System.Windows.Forms.Label label6; private System.Windows.Forms.TextBox txtCity; private System.Windows.Forms.Label label5; private System.Windows.Forms.TextBox txtAddress; private System.Windows.Forms.Label label4; private System.Windows.Forms.TextBox txtContactName; private System.Windows.Forms.Label label3; private System.Windows.Forms.TextBox txtCompanyName; private System.Windows.Forms.Label label2; private System.Windows.Forms.TextBox txtCompanyID; private System.Windows.Forms.Label label1; private System.Windows.Forms.Button btnNew; private System.Windows.Forms.TextBox txtCustomerName; private System.Windows.Forms.Button btnUpdate; private System.Windows.Forms.Label lblMessage; private System.Windows.Forms.Button btnDelete; private System.Windows.Forms.ListBox lbCustomers; private SqlDataAdapter DataAdapter; // the DataSet and table are members so that // we can access them from any member method private DataSet DataSet; private DataTable dataTable; public ADOForm1( ) { InitializeComponent( ); string connectionString = "server=Neptune; uid=sa; pwd=oWenmEany; database=northwind"; string commandString = "Select * from Customers"; DataAdapter = new SqlDataAdapter(commandString, connectionString); DataSet = new DataSet( ); DataAdapter.Fill(DataSet,"Customers"); PopulateLB( ); } // fill the list box with columns from the Customers table private void PopulateLB( ) { dataTable = DataSet.Tables[0]; lbCustomers.Items.Clear( ); foreach (DataRow dataRow in dataTable.Rows) { lbCustomers.Items.Add( dataRow["CompanyName"] + " (" + dataRow["ContactName"] + ")" ); } } public override void Dispose( ) { base.Dispose( ); components.Dispose( ); } private void InitializeComponent( ) { this.components = new System.ComponentModel.Container ( ); this.txtCustomerName = new System.Windows.Forms.TextBox ( ); this.txtCity = new System.Windows.Forms.TextBox ( ); this.txtCompanyID = new System.Windows.Forms.TextBox ( ); this.lblMessage = new System.Windows.Forms.Label ( ); this.btnUpdate = new System.Windows.Forms.Button ( ); this.txtContactName = new System.Windows.Forms.TextBox ( ); this.txtZip = new System.Windows.Forms.TextBox ( ); this.btnDelete = new System.Windows.Forms.Button ( ); this.txtContactTitle = new System.Windows.Forms.TextBox ( ); this.txtAddress = new System.Windows.Forms.TextBox ( ); this.txtCompanyName = new System.Windows.Forms.TextBox ( ); this.label5 = new System.Windows.Forms.Label ( ); this.label6 = new System.Windows.Forms.Label ( ); this.label7 = new System.Windows.Forms.Label ( ); this.label8 = new System.Windows.Forms.Label ( ); this.label9 = new System.Windows.Forms.Label ( ); this.label4 = new System.Windows.Forms.Label ( ); this.lbCustomers = new System.Windows.Forms.ListBox ( ); this.txtPhone = new System.Windows.Forms.TextBox ( ); this.btnNew = new System.Windows.Forms.Button ( ); this.label1 = new System.Windows.Forms.Label ( ); this.label2 = new System.Windows.Forms.Label ( ); this.label3 = new System.Windows.Forms.Label ( ); //@this.TrayHeight = 0; //@this.TrayLargeIcon = false; //@this.TrayAutoArrange = true; txtCustomerName.Location = new System.Drawing.Point (256, 120); txtCustomerName.TabIndex = 4; txtCustomerName.Size = new System.Drawing.Size (160, 20); txtCity.Location = new System.Drawing.Point (384, 245); txtCity.TabIndex = 15; txtCity.Size = new System.Drawing.Size (160, 20); txtCompanyID.Location = new System.Drawing.Point (136, 216); txtCompanyID.TabIndex = 7; txtCompanyID.Size = new System.Drawing.Size (160, 20); lblMessage.Location = new System.Drawing.Point (32, 368); lblMessage.Text = "Press New, Update or Delete"; lblMessage.Size = new System.Drawing.Size (416, 48); lblMessage.TabIndex = 1; btnUpdate.Location = new System.Drawing.Point (32, 120); btnUpdate.Size = new System.Drawing.Size (75, 23); btnUpdate.TabIndex = 0; btnUpdate.Text = "Update"; btnUpdate.Click += new System.EventHandler (this.btnUpdate_Click); txtContactName.Location = new System.Drawing.Point (136, 274); txtContactName.TabIndex = 11; txtContactName.Size = new System.Drawing.Size (160, 20); txtZip.Location = new System.Drawing.Point (384, 274); txtZip.TabIndex = 17; txtZip.Size = new System.Drawing.Size (160, 20); btnDelete.Location = new System.Drawing.Point (472, 120); btnDelete.Size = new System.Drawing.Size (75, 23); btnDelete.TabIndex = 2; btnDelete.Text = "Delete"; btnDelete.Click += new System.EventHandler (this.btnDelete_Click); txtContactTitle.Location = new System.Drawing.Point (136, 303); txtContactTitle.TabIndex = 19; txtContactTitle.Size = new System.Drawing.Size (160, 20); txtAddress.Location = new System.Drawing.Point (384, 216); txtAddress.TabIndex = 13; txtAddress.Size = new System.Drawing.Size (160, 20); txtCompanyName.Location = new System.Drawing.Point (136, 245); txtCompanyName.TabIndex = 9; txtCompanyName.Size = new System.Drawing.Size (160, 20); label5.Location = new System.Drawing.Point (320, 252); label5.Text = "City"; label5.Size = new System.Drawing.Size (48, 16); label5.TabIndex = 14; label6.Location = new System.Drawing.Point (320, 284); label6.Text = "Zip"; label6.Size = new System.Drawing.Size (40, 16); label6.TabIndex = 16; label7.Location = new System.Drawing.Point (40, 312); label7.Text = "Contact Title"; label7.Size = new System.Drawing.Size (88, 16); label7.TabIndex = 18; label8.Location = new System.Drawing.Point (320, 312); label8.Text = "Phone"; label8.Size = new System.Drawing.Size (56, 16); label8.TabIndex = 20; label9.Location = new System.Drawing.Point (120, 120); label9.Text = "New Customer Name:"; label9.Size = new System.Drawing.Size (120, 24); label9.TabIndex = 22; label4.Location = new System.Drawing.Point (320, 224); label4.Text = "Address"; label4.Size = new System.Drawing.Size (56, 16); label4.TabIndex = 12; lbCustomers.Location = new System.Drawing.Point (32, 16); lbCustomers.Size = new System.Drawing.Size (512, 95); lbCustomers.TabIndex = 3; txtPhone.Location = new System.Drawing.Point (384, 303); txtPhone.TabIndex = 21; txtPhone.Size = new System.Drawing.Size (160, 20); btnNew.Location = new System.Drawing.Point (472, 336); btnNew.Size = new System.Drawing.Size (75, 23); btnNew.TabIndex = 5; btnNew.Text = "New"; btnNew.Click += new System.EventHandler (this.btnNew_Click); label1.Location = new System.Drawing.Point (40, 224); label1.Text = "Company ID"; label1.Size = new System.Drawing.Size (88, 16); label1.TabIndex = 6; label2.Location = new System.Drawing.Point (40, 252); label2.Text = "Company Name"; label2.Size = new System.Drawing.Size (88, 16); label2.TabIndex = 8; label3.Location = new System.Drawing.Point (40, 284); label3.Text = "Contact Name"; label3.Size = new System.Drawing.Size (88, 16); label3.TabIndex = 10; this.Text = "Customers Update Form"; this.AutoScaleBaseSize = new System.Drawing.Size (5, 13); this.ClientSize = new System.Drawing.Size (584, 421); this.Controls.Add (this.label9); this.Controls.Add (this.txtPhone); this.Controls.Add (this.label8); this.Controls.Add (this.txtContactTitle); this.Controls.Add (this.label7); this.Controls.Add (this.txtZip); this.Controls.Add (this.label6); this.Controls.Add (this.txtCity); this.Controls.Add (this.label5); this.Controls.Add (this.txtAddress); this.Controls.Add (this.label4); this.Controls.Add (this.txtContactName); this.Controls.Add (this.label3); this.Controls.Add (this.txtCompanyName); this.Controls.Add (this.label2); this.Controls.Add (this.txtCompanyID); this.Controls.Add (this.label1); this.Controls.Add (this.btnNew); this.Controls.Add (this.txtCustomerName); this.Controls.Add (this.btnUpdate); this.Controls.Add (this.lblMessage); this.Controls.Add (this.btnDelete); this.Controls.Add (this.lbCustomers); } // handle the new button click protected void btnNew_Click (object sender, System.EventArgs e) { // create a new row, populate it DataRow newRow = dataTable.NewRow( ); newRow["CustomerID"] = txtCompanyID.Text; newRow["CompanyName"] = txtCompanyName.Text; newRow["ContactName"] = txtContactName.Text; newRow["ContactTitle"] = txtContactTitle.Text; newRow["Address"] = txtAddress.Text; newRow["City"] = txtCity.Text; newRow["PostalCode"] = txtZip.Text; newRow["Phone"] = txtPhone.Text; // add the new row to the table dataTable.Rows.Add(newRow); // update the db DataAdapter.Update(DataSet,"Customers"); // inform the user and accept the changes lblMessage.Text = DataAdapter.UpdateCommand.CommandText; Application.DoEvents( ); DataSet.AcceptChanges( ); // repopulate the list box PopulateLB( ); // clear all the text fields ClearFields( ); } // set all the text fields to empty strings private void ClearFields( ) { txtCompanyID.Text = ""; txtCompanyName.Text = ""; txtContactName.Text = ""; txtContactTitle.Text = ""; txtAddress.Text = ""; txtCity.Text = ""; txtZip.Text = ""; txtPhone.Text = ""; } // handle the update button click protected void btnUpdate_Click (object sender, System.EventArgs e) { // get the selected row DataRow targetRow = dataTable.Rows[lbCustomers.SelectedIndex]; // inform the user lblMessage.Text = "Updating " + targetRow["CompanyName"]; Application.DoEvents( ); // edit the row targetRow.BeginEdit( ); targetRow["CompanyName"] = txtCustomerName.Text; targetRow.EndEdit( ); // get each row that changed DataSet DataSetChanged = DataSet.GetChanges(DataRowState.Modified); // test to make sure all the changed rows are without errors bool okayFlag = true; if (DataSetChanged.HasErrors) { okayFlag = false; string msg = "Error in row with customer ID "; // examine each table in the changed DataSet foreach (DataTable theTable in DataSetChanged.Tables) { // if any table has errors, find out which rows if (theTable.HasErrors) { // get the rows with errors DataRow[] errorRows = theTable.GetErrors( ); // iterate through the errors and correct // (in our case, just identify) foreach (DataRow theRow in errorRows) { msg = msg + theRow["CustomerID"]; } } } lblMessage.Text = msg; } // if we have no errors if (okayFlag) { // merge the changes back into the original DataSet DataSet.Merge(DataSetChanged); // update the database DataAdapter.Update(DataSet,"Customers"); // inform the user lblMessage.Text = DataAdapter.UpdateCommand.CommandText; Application.DoEvents( ); // accept the changes and repopulate the list box DataSet.AcceptChanges( ); PopulateLB( ); } else // if we had errors, reject the changes DataSet.RejectChanges( ); } // handle the delete button click protected void btnDelete_Click (object sender, System.EventArgs e) { // get the selected row DataRow targetRow = dataTable.Rows[lbCustomers.SelectedIndex]; // prepare message for user string msg = targetRow["CompanyName"] + " deleted. "; // delete the selected row dataTable.Rows[lbCustomers.SelectedIndex].Delete( ); // accept the changes to the DataSet DataSet.AcceptChanges( ); // update the database DataAdapter.Update(DataSet,"Customers"); // repopulate the list box without the deleted record PopulateLB( ); // inform the user lblMessage.Text = msg; Application.DoEvents( ); } public static void Main(string[] args) { Application.Run(new ADOForm1( )); } } }
Figure 14-8 shows the filled-out form just before pressing the New button.
Figure 14-9 shows the form immediately after adding the new record.
Note that the new record is appended to the end of the list and the text fields are cleared.
3.21.106.7