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 one or more tables, edit existing rows, and delete rows, all with the appropriate SQL statements.
To illustrate the use of the ExecuteNonQuery statement, you'll use Visual Studio .NET to create a simple form that will display the current records in a listbox. This will be a very simple user interface to keep the focus on SQL rather than on interaction with the control.
Choose whichever language you feel most comfortable using, and name the project BugHistoryHandEdits. Drag a listbox onto the form and make it wide. Add a textbox below and three buttons to the right, as shown in Figure 20-1.
Name the ListBox lbBugs and the textbox txtDescription. Clear the
Text property of the TextBox. Name the three buttons btnAdd, btnEdit,
and btnDelete. Stretch the three buttons and modify their text fields
to say Add Record, Edit Record,
and Delete Record, respectively. You may want to
set their backColor to pale green, yellow, and red. Add a textbox and
be sure to set its text field to blank.
Fill the listbox with a stored procedure: spBugsNoHistory, as shown in Example 20-1. (See Sidebar 20-1.)
Example 20-1. SpBugsNoHistory
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
Double-click on each button in turn to create skeleton Click event handlers. In these 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, always edit or delete the last record in the table. (In a real application, the user would indicate which record to modify.) The complete C# source code is shown in Example 20-2, and the complete VB.NET equivalent is shown in Example 20-3. Be certain to add the requisite using statements in C# or the imports statement in VB.NET.
Example 20-2. Hand-edited code (C#)
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
namespace BugHistoryHandEdits
{
public class Form1 : System.Windows.Forms.Form
{
private System.Windows.Forms.Button btnAdd;
private System.Windows.Forms.Button btnEdit;
private System.Windows.Forms.Button btnDelete;
private System.Windows.Forms.TextBox txtDescription;
private System.Windows.Forms.ListBox lbBugs;
private System.ComponentModel.Container components = null;
public Form1( )
{
InitializeComponent( );
PopulateListBox( );
}
// return a DataReader object based on the sproc
private void PopulateListBox( )
{
lbBugs.Items.Clear( );
// connection string to connect to the Bugs Database
string connectionString =
"server=YourServer; uid=sa; pwd=YourPW; " +
" database=WindForm_Bugs";
// 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;
DataSet bugDataSet = new DataSet( );
SqlDataAdapter bugDataAdapter = new SqlDataAdapter( );
bugDataAdapter.SelectCommand = command;
bugDataAdapter.TableMappings.Add("Table","Bugs");
bugDataAdapter.Fill(bugDataSet);
DataTable bugTable = bugDataSet.Tables[0];
foreach (DataRow row in bugTable.Rows)
{
lbBugs.Items.Add(row["BugID"] + ") " +
row["Description"] + " [ " +
row["ProductDescription"] +
" ]. Reported by: " + row["reporter"]);
}
}
protected override void Dispose( bool disposing )
{
if( disposing )
{
if (components != null)
{
components.Dispose( );
}
}
base.Dispose( disposing );
}
#region Windows Form Designer generated code
#endregion
/// <summary>
/// The main entry point for the application.
/// </summary>
[STAThread]
static void Main( )
{
Application.Run(new Form1( ));
}
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);
}
private void btnAdd_Click(object sender, System.EventArgs e)
{
string cmd = @"Insert into bugs values (1,'0.1', '" +
txtDescription.Text + @"',1)";
UpdateDB(cmd);
}
private void btnDelete_Click(object sender, System.EventArgs e)
{
string cmd =
@"delete from bugs where bugid =
(select max(BugID) from bugs)";
UpdateDB(cmd);
}
// 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=YourPW;" +
" database=WindForm_Bugs";
// 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;
command.ExecuteNonQuery( );
// clear the text box
txtDescription.Text = "";
PopulateListBox( );
return;
}
}
}Example 20-3. Hand-edited code (VB.NET)
Imports System.Data.SqlClient
Public Class Form1
Inherits System.Windows.Forms.Form
#Region " Windows Form Designer generated code "
Public Sub New( )
MyBase.New( )
'This call is required by the Windows Form Designer.
InitializeComponent( )
PopulateListBox( )
'Add any initialization after the InitializeComponent( ) call
End Sub
#End Region
Private Sub PopulateListBox( )
lbBugs.Items.Clear( )
' connection string to connect to the Bugs Database
Dim connectionString As String = "server=YourServer; uid=sa; " + _
" pwd=YourPW; database=WindForm_Bugs"
' Create connection object, initialize with
' connection string. Open it.
Dim connection As New _
System.Data.SqlClient.SqlConnection(connectionString)
connection.Open( )
' Create a SqlCommand object and assign the connection
Dim command As New System.Data.SqlClient.SqlCommand( )
command.Connection = connection
' set the stored procedure to get the bug records
command.CommandText = "spBugsNoHistory"
command.CommandType = CommandType.StoredProcedure
Dim bugDataSet As New DataSet( )
Dim bugDataAdapter As New SqlDataAdapter( )
bugDataAdapter.SelectCommand = command
bugDataAdapter.TableMappings.Add("Table", "Bugs")
bugDataAdapter.Fill(bugDataSet)
Dim bugTable As DataTable = bugDataSet.Tables(0)
Dim row As DataRow
For Each row In bugTable.Rows
lbBugs.Items.Add(row("BugID") & " " & _
row("Description") & " [ " & row("ProductDescription") & _
" ]. Reported by: " & row("reporter"))
Next
End Sub
Private Sub btnAdd_Click(ByVal sender As System.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)
End Sub
Private Sub btnEdit_Click(ByVal sender As System.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)
End Sub
Private Sub btnDelete_Click(ByVal sender As System.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)
End Sub
Private Sub UpdateDB(ByVal cmd As String)
' connection string to connect to the Bugs Database
Dim connectionString As String = "server=YourServer; uid=sa; " + _
" pwd=YourPW; database=WindForm_Bugs"
' Create connection object, initialize with
' connection string. Open it.
Dim connection As New _
System.Data.SqlClient.SqlConnection(connectionString)
connection.Open( )
' Create a SqlCommand object and assign the connection
Dim command As New System.Data.SqlClient.SqlCommand( )
command.Connection = connection
command.CommandText = cmd
command.ExecuteNonQuery( )
' clear the text box
txtDescription.Text = ""
PopulateListBox( )
Return
End Sub
End ClassFor each of the three buttons, execute the same steps in the Click event handler:
Create the SQL string.
Create a Connection object and a Command object.
Set the Command object's CommandText property to the SQL statement you've created.
Execute the SQL statement.
Rebind the data to update the display.
All three event handlers require identical steps 2 through 5, 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 is as follows:
private void UpdateDB(string cmd)
Private Sub UpdateDB(cmd As String)
Create your connection string and Connection object as you have in the examples in the previous chapter. 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 19, 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 TextBox:
string cmd = @"Insert into bugs values (1,'0.1', '" + TxtDescription.Text + @"',1)";
Dim cmd As String = "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.
Pass this cmd string to the UpdateDB method, where you create a connection to the database, and execute the passed-in command by calling ExecuteNonQuery.
Dim connection As New System.Data.SqlClient.SqlConnection(connectionString) connection.Open( ) Dim command As New System.Data.SqlClient.SqlCommand( ) command.Connection = connection command.CommandText = cmd command.ExecuteNonQuery( )
Finally, empty the contents of the TextBox update the ListBox to reflect the change:
PopulateListBox( );
The three event handlers are identical except for the particular SQL statement executed. Note that the Edit and Delete buttons are hardwired to operate on the record with the highest BugID. This was done only to keep the example very simple.