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.
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 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 |
| |
| ||
| ||
| ||
| ||
| ||
Add Button |
ID |
|
Text |
| |
Edit Button |
ID |
|
Text |
| |
Delete Button |
ID |
|
Text |
| |
TextBox |
ID |
|
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.LoadBindData( )
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:
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)";
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
3.145.14.132