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

Figure 20-1. Hand-edits form

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.

The Windows Form Designer generated code was cut from the listing to save space.

 

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

/// The main entry point for the application. ///

[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 Class

For each of the three buttons, execute the same steps in the Click event handler:

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 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)"

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.

Stored Procedures

A stored procedure is, essentially, a SQL method. Like a method, stored procedures have a name and, optionally, parameters. There is no return type for a stored procedure, though it is possible to get back values by either using parameters (like passing by reference in VB.NET or C#) or returning a recordset.

Stored procedure parameters are prepended with the at-sign (@) and marked with their type, each separated from the next by a comma, as shown here:

spAddBugWithTransactions
 @ProductID int,
 @Version varChar(50),
 @Description varChar(8000)

The value in parentheses is the size of the parameter. In the code shown, Version is a parameter of type varChar, which will hold up to 50 characters. For more information on stored procedures and SQL programming, see Transact-SQL Programming, by Kevin E. Kline, Lee Gould, and Andrew Zanevsky (O'Reilly).