Chapter 12. ADO Data Updates

Chapter 11 focused on retrieving data from the database and managing the complexity of related tables. All of the examples focused on displaying data. In many applications, however, you will also want to allow the user to update the data in the database.

There are two aspects to writing web applications that allow users to update data. The first aspect is providing the user with a user interface that facilitates modifying the data. The second is to provide the programmatic support for the update: how do you insert new records, or modify or delete existing records once you know what changes you want to make? This chapter focuses on this second aspect; how you write the code to update the data in the database. To simplify the examples, this chapter shows very little of the user interface. Many of the examples will use hard-coded changes; others will use a very crude and simple form for updating the data tables.

Updating data in a database is very simple if you only update a single table, but once you update related tables, things get complicated. This chapter will explore how transactions can be used to ensure the integrity of your data. In addition, if your program will be used by more than one user at a time, you will encounter issues with concurrency; is it possible for one user’s changes to overwrite the changes of another user. This chapter also explores how you manage concurrency issues, and shows some of the powerful support available in the class library to simplify this difficult task.

Updating with SQL

The simplest way to update the database is to generate a SQL Insert, Update, or Delete statement, and execute it using the Command object’s ExecuteNonQuery method. For example, you can insert a few records into the Bugs table, edit existing rows, and delete rows, all with the appropriate SQL statements.

To illustrate this, you’ll use Visual Studio to create a simple form to display the current records in a grid. Choose whichever language you feel most comfortable using, and name the project BugHistoryHandEdits. In addition to the DataGrid control, you’ll add three buttons to allow the user to add, edit, or delete a record, and you’ll also add a text field for the description, as shown in Figure 12-1. Table 12-1 shows the properties that you should set for the example to work.

The data entry page

Figure 12-1. The data entry page

Tip

The data entry page in Figure 12-1 is a quick and dirty application with a crude user interface. While this may seem to have little relevance to real-world applications at first glance, the truth is that this is exactly the kind of starter program programmers often use to prove an approach or to experiment with an alternative. In a final product, the user interface will certainly be more attractive (for example, you might allow the user to click on the various fields and edit them in place), but the back-end functionality will likely be unchanged. (Manipulation of grids and the more attractive components for a user interface will be explored in detail in Chapter 13.)

Table 12-1. Non-default properties of the BugHistoryHandEdits controls

Control

Property

Value

DataGrid

AlternatingItemStyle.Backcolor

LightGray

BorderColor

Blue

BorderStyle

Solid

BorderWidth

4px

HeaderStyle.BackColor

BlanchedAlmond

HeaderStyle.Font.Bold

True

Add Button

ID

btnAdd

Text

Add Record

Edit Button

ID

btnEdit

Text

Edit Record

Delete Button

ID

btnDelete

Text

Delete Record

TextBox

ID

TxtDescription

Next, you’ll write Click event handlers for the buttons, and in these event handlers you will interact with the database, executing the SQL statements needed to add a record, edit a record, or delete a record. To simplify the user interface even further, you’ll always edit or delete the last record in the table. (In a real application, of course, the user would indicate which record to modify.) The complete C# source code is shown in Example 12-1, and the complete VB.NET source code is shown in Example 12-2. Code not automatically generated by Visual Studio .NET is shown in boldface. Note that in order to keep the example as simple as possible, the code has no error checking.

Example 12-1. C# source for the data entry page 

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Text;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;

namespace BugHistoryHandEdits
{
   public class WebForm1 : System.Web.UI.Page
   {
      // the three buttons
      protected System.Web.UI.WebControls.Button btnAdd;
      protected System.Web.UI.WebControls.Button btnEdit;
      protected System.Web.UI.WebControls.Button btnDelete;

      // text box to get user input
      protected System.Web.UI.WebControls.TextBox TxtDescription;

      // the data grid to display the contents of the bug table
      protected System.Web.UI.WebControls.DataGrid DataGrid1;
   
      public WebForm1(  )
      {
         Page.Init += new System.EventHandler(Page_Init);
      }

      // when you load the page bind the data from the db
      private void Page_Load(
         object sender, System.EventArgs e)
      {
         BindData(  );   
      }

      // bind the grid to the DataReader produced by
      // the sproc and then update the data
      private void BindData(  )
      {
         DataGrid1.DataSource = CreateBugDataReader(  );
         DataGrid1.DataBind(  );
      }
  
      // return a DataReader object based on the sproc
      private SqlDataReader CreateBugDataReader(  )
      {

         // connection string to connect to the Bugs Database
         string connectionString = 
            "server=yourServer; uid=sa; pwd=yourPassword; 
         database=ProgASPDotNetBugs";

         // Create connection object, initialize with 
         // connection string. Open it.
         System.Data.SqlClient.SqlConnection connection = 
            new System.Data.SqlClient.SqlConnection(
         connectionString);
         connection.Open(  );

         // Create a SqlCommand object and assign the connection
         System.Data.SqlClient.SqlCommand command = 
            new System.Data.SqlClient.SqlCommand(  );
         command.Connection=connection;

         // set the stored procedure to get the bug records
         command.CommandText="spBugsNoHistory";
         command.CommandType=CommandType.StoredProcedure;

         // return the data reader
         return command.ExecuteReader(
            CommandBehavior.CloseConnection);
      }

      private void Page_Init(object sender, EventArgs e)
      {
         InitializeComponent(  );
      }

      #region Web Form Designer generated code
      /// <summary>
      /// Required method for Designer support - do not modify
      /// the contents of this method with the code editor.
      /// </summary>
      private void InitializeComponent(  )
      {    
         this.btnAdd.Click += 
            new System.EventHandler(this.btnAdd_Click);
         this.btnEdit.Click += 
            new System.EventHandler(this.btnEdit_Click);
         this.btnDelete.Click += 
            new System.EventHandler(this.btnDelete_Click);
         this.Load += 
            new System.EventHandler(this.Page_Load);

      }
      #endregion

      // event handler for the edit button 
      // edit the last record based on the user's input
      private void btnEdit_Click(object sender, System.EventArgs e)
      {
         string cmd = @"Update bugs set description = '" +
            TxtDescription.Text + 
               @"' where bugid = (select max(BugID) from bugs)";

         UpdateDB(cmd);
         BindData(  );
      }

      // delete the last record in the table
      private void btnDelete_Click(object sender, System.EventArgs e)
      {
         string cmd = 
            @"delete from bugs where bugid = 
         (select max(BugID) from bugs)";

         UpdateDB(cmd);
         BindData(  );
      }

      // add a new record to the table
      // pick up the description field from the text box
      private void btnAdd_Click(object sender, System.EventArgs e)
      {
         string cmd = @"Insert into bugs values (1,'0.1', '" +  
            TxtDescription.Text + @"',1)";

         UpdateDB(cmd);
         BindData(  );
      }

      // common routine for all database updates
      private void UpdateDB(string cmd)
      {
         // connection string to connect to the Bugs Database
         string connectionString = 
            "server=yourServer; uid=sa; 
         pwd=YourPassword; database=ProgASPDotNetBugs";

         // Create connection object, initialize with 
         // connection string. Open it.
         System.Data.SqlClient.SqlConnection connection = 
            new System.Data.SqlClient.SqlConnection(connectionString);
         connection.Open(  );

         // Create a SqlCommand object and assign the connection
         System.Data.SqlClient.SqlCommand command = 
            new System.Data.SqlClient.SqlCommand(  );
         command.Connection=connection;
         command.CommandText=cmd;

         // clear the text box 
         TxtDescription.Text = "";

         // execute the sproc
         command.ExecuteNonQuery(  );
         return;
      }
   }   
}

Example 12-2. VB.NET source for the data entry page 

               Imports System.Data.SqlClient

Public Class WebForm1
    Inherits System.Web.UI.Page
      Protected WithEvents DataGrid1 As System.Web.UI.WebControls.DataGrid
      Protected WithEvents btnAdd As System.Web.UI.WebControls.Button
      Protected WithEvents btnEdit As System.Web.UI.WebControls.Button
      Protected WithEvents btnDelete As System.Web.UI.WebControls.Button
      Protected WithEvents TxtDescription As System.Web.UI.WebControls.TextBox

#Region " Web Form Designer Generated Code "

    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough(  )> _
    Private Sub InitializeComponent(  )

    End Sub

    Private Sub Page_Init(ByVal sender As System.Object, _
                          ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: This method call is required by the Web Form Designer
        'Do not modify it using the code editor.
        InitializeComponent(  )
    End Sub

#End Region

    Private Sub Page_Load(ByVal sender As System.Object, _
                          ByVal e As System.EventArgs) Handles MyBase.Load
         BindData(  )
    End Sub

   ' bind the grid to the DataReader produced by
   ' the sproc and then update the data
   Private Sub BindData(  )
      DataGrid1.DataSource = CreateBugDataReader(  )
      DataGrid1.DataBind(  )
   End Sub

   ' return a DataReader object based on the sproc
   Private Function CreateBugDataReader(  ) As SqlDataReader
      ' connection string to connect to the Bugs Database
      Dim connectionString As String = _
         "server=yourServer; uid=sa; pwd=yourPassword; " & _
         "database=ProgASPDotNetBugs"

      ' Create connection object, initialize with 
      ' connection string. Open it.
      Dim connection As SqlDbConnection = New SqlDbConnection(connectionString)
      connection.Open(  )

      ' Create a SqlCommand object and assign the connection
      Dim command As New SqlDbCommand(  )
      command.Connection = connection

      ' set the stored procedure to get the bug records
      command.CommandText = "spBugsNoHistory"
      command.CommandType = CommandType.StoredProcedure

      ' return the data reader
      Return command.ExecuteReader(CommandBehavior.CloseConnection)
   End Function

   ' event handler for the edit button 
   ' edit the last record based on the user's input
   Private Sub btnEdit_Click(ByVal sender As Object, _
                       ByVal e As System.EventArgs) Handles btnEdit.Click
      Dim cmd As String = "Update bugs set description = '" & _
                          TxtDescription.Text & _
                          "' where bugid = (select max(BugID) from bugs)"
      UpdateDB(cmd)
      BindData(  )
   End Sub

   ' delete the last record in the table
   Private Sub btnDelete_Click(ByVal sender As Object, _
               ByVal e As System.EventArgs) Handles btnDelete.Click
      Dim cmd As String = _
             "delete from bugs where bugid = (select max(BugID) from bugs)"
      UpdateDB(cmd)
      BindData(  )
   End Sub

   ' add a new record to the table
   ' pick up the description field from the text box
   Private Sub btnAdd_Click(ByVal sender As Object, _
                          ByVal e As System.EventArgs) Handles btnAdd.Click
      Dim cmd As String = "Insert into bugs values (1,'0.1', '" & _
            TxtDescription.Text + "',1)"
      UpdateDB(cmd)
      BindData(  )
   End Sub

   ' common routine for all database updates
   Private  Sub UpdateDB(ByVal cmd As String) 
      ' connection string to connect to the Bugs Database
      Dim connectionString As String = _
         "server=yourServer; uid=sa; pwd=yourPassword; " & _
         "database=ProgASPDotNetBugs"

      ' Create connection object, initialize with 
      ' connection string. Open it.
      Dim connection As SqlDbConnection = New SqlDbConnection(connectionString)
      connection.Open(  )

      ' Create a SqlCommand object and assign the connection
      Dim command As New SqlDbCommand(  )
      command.Connection = connection
      command.CommandText = cmd

      ' clear the text box 
      TxtDescription.Text = ""

      ' execute the sproc
      command.ExecuteNonQuery(  )
      Return
   End Sub

End Class

For each of the three event handlers for the Click event, you will want to execute the same steps:

  1. Create the SQL string.

  2. Create a connection object and a command object.

  3. Set the command object’s CommandText property to the SQL statement you’ve created.

  4. Execute the SQL statement.

  5. Rebind the data to update the display.

All three event handlers require identical steps 2 through 4, so this work is factored out into a common method, UpdateDB, to which you pass the command string you want executed. The syntax of the UpdateDB method in C# is: 

private void UpdateDB(string cmd)

and in VB.NET is:

Private Sub UpdateDB(cmd As String)

You create your connection string and connection object as you have in previous examples. You then set the command object’s CommandText property to the string passed in as a parameter and execute the query with the ExecuteNonQuery method: 

command.CommandText=cmd;  
command.ExecuteNonQuery(  );

Remember that ExecuteNonQuery, as you saw in Chapter 11, is used when you do not expect to get back a result set. The return value is the number of records affected, which you pass back to the calling program.

The SQL statement for adding a record is a simple Insert statement. In this example, you’ll hardwire the values for the Product, Version, and Reporter fields, but you’ll pick up the text for the Description field from the text box: 

string cmd = @"Insert into bugs values (1,'0.1', '" +
   TxtDescription.Text + @"',1)";

Tip

C# Tip: The @ symbol creates a verbatim string, allowing you to pass in single quotation marks without escaping them.

You pass this cmd string to the UpdateDB method as described previously, and then you update the label with the number of rows affected. Finally, you call BindData, which rebinds the data grid with data from the database, and updates the label to display your progress. 

int numRowsAdded = UpdateDB(cmd);

lblMessage.Text = "Added " + numRowsAdded.ToString(  ) + " rows.";
BindData(  );

The three event handlers are identical except for the particular SQL statement executed. The call to BindData rebinds the data grid to the data extracted from the database. BindData in turn calls CreateBugDataReader, which creates an SqlDataReader from the result set returned by the spBugsNoHistory stored procedure. This is a simple stored procedure to retrieve only the few fields from Bugs, lkProduct, and People that we care about for this example program: 

CREATE PROCEDURE spBugsNoHistory  as
Select b.BugID, b.Description,p.ProductDescription, 
r.FullName as reporter
from  
bugs b 
join lkProduct p on b.Product = p.ProductID  
join People r on b.Reporter = r.PersonID
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.140.197.136