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.

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

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.