Changing database records in ADO.NET - Part 5

Naimishforu
Posted by in ADO.NET category on for Beginner level | Points: 250 | Views : 6273 red flag
Rating: 5 out of 5  
 1 vote(s)

We will see overview of Changing database records.

Introduction


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!!!!!

Page copy protected against web site content infringement by Copyscape

About the Author

Naimishforu
Full Name: Naimish Pandya
Member Level: Bronze
Member Status: Member,MVP
Member Since: 1/22/2011 7:38:35 AM
Country: India
Thanks, Naimish http://dotnetinterview.zxq.net/ http://naimishpandya.blogspot.in/
http://naimishpandya.blogspot.in/

Login to vote for this post.

Comments or Responses

Posted by: Tripati_tutu on: 3/7/2011 | Points: 25
good one...
Posted by: Naimishforu on: 3/7/2011 | Points: 25
Thanks....
Posted by: CS1401 on: 7/4/2011 | Points: 25
fine
Posted by: Naimishforu on: 7/4/2011 | Points: 25
Thanks :)

Login to post response

Comment using Facebook(Author doesn't get notification)