The dataset extracted
from the database is useful for viewing records but can also be used to change
them. However, the changes made to the dataset must then be uploaded to the
database to be saved permanently. So changing records in ADO.NET is a two-stage
process.
The rows in the
dataset are stateful and will track any changes, deletions, or additions. When
the database is being updated, the state of each row is checked, and any
changed rows are extracted to be uploaded to the database.
The following
program creates a form to display the contents of a dataset populated from the
EasyNomad database.
Each element in
the form is initialized. This includes defining column names, referencing event
handlers, and filling in any necessary text.
The form allows
you to create new records and view, update, or delete existing records.
The
InsertCommand, UpdateCommand, and DeleteCommand properties of the DataAdapter
are used to manage changes at the data source.
And these
properties must be set before the Update method of the DataAdapter is called,
depending on what changes were made to the data in the dataset.
The
InitialiseDbCommands method sets the InsertCommand, UpdateCommand, and
DeleteCommand properties of the DataAdapter, and this is called in the form
constructor.
First you need to
specify the connection details. Here you use the SelectCommand method's
Connection property, which is used to return data from the data source.
private void InitialiseDbCommands()
{
// Reuse the SelectCommand's Connection
OleDbConnection connection = (OleDbConnection)CustomersDataAdaptor.SelectCommand.Connection;
// Create an explicit, reusable insert
CustomersDataAdaptor.InsertCommand = connection.CreateCommand();
CustomersDataAdaptor.InsertCommand.CommandText = "Insert into Customers " +
"(CustomerID, FirstName, LastName, BillingAddress, City,_
StateOrProvince, PostalCode, Country,_
Title, PhoneNumber, CompanyName) " +
"values(@CustomerID, @FirstName, @LastName,_
@BillingAddress, @City, @StateOrProvince, @PostalCode,_
@Country, @Title, @PhoneNumber, @CompanyName)";
AddParms(CustomersDataAdaptor.InsertCommand,
"CustomerID", "FirstName", "LastName", "BillingAddress",_
"City", "StateOrProvince", "PostalCode", "Country", "Title",_
"PhoneNumber", "CompanyName");
The CreateCommand method creates a new
command and assigns it to the InsertCommand property.
The CommandText property sets the Insert
SQL statement to execute at the data source.
Current information about the modified
row is passed to the InsertCommand object through the Parameters collection,
which takes place in the AddParms method.
private void InitialiseDbCommands()
{
// Reuse the SelectCommand's Connection
OleDbConnection connection = (OleDbConnection)CustomersDataAdaptor.SelectCommand.Connection;
// Create an explicit, reusable insert
CustomersDataAdaptor.InsertCommand = connection.CreateCommand();
CustomersDataAdaptor.InsertCommand.CommandText =
"Insert into Customers " +
"(CustomerID, FirstName, LastName, BillingAddress,_
City, StateOrProvince, PostalCode, Country, Title,_
PhoneNumber, CompanyName) " +
"values(@CustomerID, @FirstName, @LastName,_
@BillingAddress, @City, @StateOrProvince, @PostalCode,_
@Country, @Title, @PhoneNumber, @CompanyName)";
AddParms(CustomersDataAdaptor.InsertCommand,
"CustomerID", "FirstName", "LastName", "BillingAddress",_
"City", "StateOrProvince", "PostalCode", "Country", "Title",_
"PhoneNumber", "CompanyName");
The following
program makes extensive use of the AddParms method. This user-defined method
reads information from the fields in the dataset and passes it to a command
object.
// fill the list box with columns from the Customers table
private void AddParms(OleDbCommand cmd, params string[] cols)
{
// Add each parameter
foreach (String column in cols)
{
cmd.Parameters.Add(
"@" + column, OleDbType.Char, 0, column);
}
}
The UpdateCommand
and DeleteCommand properties are set in a similar fashion to the InsertCommand
property.
// Create an explicit update command
CustomersDataAdaptor.UpdateCommand = connection.CreateCommand();
CustomersDataAdaptor.UpdateCommand.CommandText = "update Customers set FirstName = @FirstName where CustomerID = @CustomerID";
AddParms(CustomersDataAdaptor.UpdateCommand,"FirstName", "CustomerID");
// Create an explicit delete command
CustomersDataAdaptor.DeleteCommand = connection.CreateCommand();
CustomersDataAdaptor.DeleteCommand.CommandText = "delete from customers where CustomerID_
= @CustomerID";
AddParms(CustomersDataAdaptor.DeleteCommand,"CustomerID");
To add a new
entry to the dataset, you must type the information for that entry into the
relevant fields and then click the New button.
The program
describes the name and location of each element of the Windows Form.
The description
for the New button specifies that it should be handled by calling the
New_Click method.
//
this.New.Location = new System.Drawing.Point(200, 248);
this.New.Name = "New";
this.New.TabIndex = 1;
this.New.Text = "New";
this.New.Click += new System.EventHandler(this.New_Click);
//
The New_Click
method creates a new row in the CustomersDataTable, with a column for each
field in the table. The value for each column is set to the value in the
corresponding text box on the form.
When all values
are set, the new row can be added to the CustomersDataTable.
Private void New_Click(object sender, System.Event
{
// create a new row, populate it
DataRow newRecord = CustomersDataTable.NewRow();
newRecord["CustomerID"] = CustomerID.Text;
newRecord["FirstName"] = FirstName.Text;
newRecord["LastName"] = LastName.Text;
newRecord["BillingAddress"] = BillingAddress.Text;
newRecord["City"] = City.Text;
newRecord["PostalCode"] = PostalCode.Text;
newRecord["StateOrProvince"] = StateOrProvince.Text;
newRecord["Country"] = Country.Text;
newRecord["Title"] = Title.Text;
newRecord["PhoneNumber"] = PhoneNumber.Text;
newRecord["CompanyName"]= CompanyName.Text;
// add the new row to the table
CustomersDataTable.Rows.Add(newRecord);
The new record
created in the dataset is inserted into the database itself by passing in the
updated dataset and the table name.
Then the
AcceptChanges method of the CustomersDataSet object is called to commit all the
changes made to CustomersDataSet since it was loaded or the last time
AcceptChanges was called.
// add the new row to the table
CustomersDataTable.Rows.Add(newRecord);
//update the database
try
{
CustomersDataAdaptor.Update(CustomersDataSet,"Customers");
CustomersDataSet.AcceptChanges();
}
To delete a row
from the dataset using the Windows Form, you simply select the row to be
deleted and click the Delete button.
The form then
confirms that the record has been deleted.
The Delete_Click
method is the event handler called when a user clicks the Delete button.
The method first
selects the row that is to be deleted from the dataset and creates a message
letting the user know that the row is being deleted.
The Delete method
removes the row.
The deletions are
passed to the database by passing the dataset and table name to the Update
method.
The AcceptChanges
method commits all of the changes that have been made to the dataset.
Private void Delete_Click(object sender, System.EventsArg e)
{
// get the selected row
DataRow targetRow = CustomersDataTable.Rows[ListOfCustomers.SelectedIndex];
// prepare message for user
string msg = targetRow["FirstName"] + " deleted. ";
// delete the selected row
targetRow.Delete();
//update the database
try
{
CustomersDataAdaptor.Update(CustomersDataSet, "Customers");
CustomersDataSet.AcceptChanges();
// repopulate the list box without the deleted record
AddRecordsToLB():
}
}
If you want to
change an existing record, rather than delete one or add a new one, you use the
Update button. In this example, we've selected the records for Anna
Ivanova and then entered new information in the text boxes.
Clicking the Update
button sends this new information to the dataset.
When the dataset
has been updated, a confirmation appears on the form.
The
UpdateCustomers_Click method is called when the Update button is
clicked.
This method gets
the row selected in the list box and creates a new DataRow object called
targetRow containing the row in the CustomersDataTable that you wish to change.
And then it
informs you that it is being updated.
When you want
your application to handle other events apart from the current event, you call
DoEvents.
The update click
event is still processing. You call DoEvents so that your form will repaint
itself, and the updating message appears as the text for label12.
To change the
information in a data row, the BeginEdit method is called.
The new
information is entered into the selected row's FirstName column with this code.
Finally, the
EndEdit method is used to close off the editing process.
private void UpdateCustomers_Click(object sender, System.EventArgs e)
{
//get the selected row
DataRow targetRow = CustomersDataTable.Rows[ListOfCustomers.SelectedIndex];
// inform the user
label12.Text = "Updating.";
Application.DoEvents();
// edit the row
targetRow.BeginEdit();
targetRow["FirstName"] = FirstName.Text;
targetRow.EndEdit();
}
Thanks and Have Fun!!!!!