Using ExecuteNonQuery() | Sams Teach Yourself ADO.NET in 24 Hours

for RuBoard

Using `ExecuteNonQuery()`

The ExecuteNonQuery() method of the Command object is used to send a SQL command to the data source for processing. Any SQL command you want to send to the data source that will not return any data (or more specifically , any data that you don't plan to use) should be sent using the ExecuteNonQuery() method.

In the next several sections, you'll see how to use the ExecuteNonQuery() method to insert, delete, and modify data.

Inserting New Data

Recall from Hour 3, "Using T-SQL: A Crash Course," that inserting new data into your data source is done using the SQL INSERT statement. One of the easiest ways to add data to a table in your data source is to build the INSERT statement dynamically and then send it to the data source using the ExecuteNonQuery() method of the Command object.

Gathering the information to put into the database is highly dependent on your application, of course. The example in Listings 7.1 (VB .NET) and 7.2 (C#) use a Web form to collect the information from the user and send it to some server-side code that builds the INSERT statement and sends it to the database.

Directly connecting to a data source from your presentation layer is referred to as two- tier development. This is a quick-and-dirty development method for small applications or quick applications with a short life span that will require little or no maintenance. Two-tier applications generally do not scale very well, and because all of the source code for the application is in the presentation tier, there are some additional security risks. To learn more about using ADO.NET in more advanced development methods such as N-tier development, see Hour 21, "Optimizing Data Access Using Tiered Development," and Hour 22, "Modifying Data in an N-Tier Application."

Listing 7.1 Dynamically Building a SQL Statement Adding Records to a SQL Database

 <% @Page Debug="true" EnableViewState="false" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <HTML> <HEAD>     <LINK rel="stylesheet" type="text/css" href="Main.css">     <!-- End Style Sheet -->     <script language="VB" runat="server" >       Sub Page_Load(Source as Object, E as EventArgs)          if IsPostBack and Page.IsValid then             Dim conn as new SqlConnection( _                  "Initial Catalog=Northwind;Server=(local);UID=sa;PWD=;")             'Build the SQL string             'Use StringBuilder object for better performance             Dim strBuilder as new StringBuilder()             strBuilder.Append("INSERT INTO Employees ")             strBuilder.Append("(FirstName, LastName, Title, HireDate) VALUES(")             strBuilder.Append("'" + Request("txtFirstName") + "',")             strBuilder.Append("'" + Request("txtLastName") + "',")             strBuilder.Append("'" + Request("txtTitle") + "',")             strBuilder.Append("'" + Request("txtDateHired") + "')")             Dim sSQL as string = strBuilder.ToString()             Dim cmd as SqlCommand = new SqlCommand(sSQL, conn)             conn.Open()             cmd.ExecuteNonQuery()             conn.Close()             txtFirstName.Text = ""             txtLastName.Text = ""             txtTitle.Text = ""             txtDateHired.Text = ""          end if          LoadData()       End Sub       private Sub LoadData()          Dim conn as SqlConnection = new SqlConnection( _                "Initial Catalog=Northwind;Server=(local);UID=sa;PWD=;")          Dim cmd as SqlCommand = new SqlCommand( _            "SELECT FirstName, LastName, Title, HireDate FROM Employees", conn)          Dim adapt as SqlDataAdapter = new SqlDataAdapter(cmd)          Dim dsEmployees as DataSet = new DataSet()          conn.Open()          adapt.Fill(dsEmployees, "Employees")          conn.Close()          employees.DataSource = dsEmployees          employees.DataBind()       End Sub     </script> </HEAD> <BODY> <h1>Adding a Record to the Database</h1> <hr> <asp:Label id=msg runat=server /> <form runat="server" id=form1 name=form1>    <asp:DataGrid id="employees" runat="server"></asp:DataGrid>    <br>    Add a new record:<br>    <table>    <tr>        <td>First Name: </td>        <td><asp:textbox runat="server"                  id="txtFirstName" EnableViewState="false">            </asp:textbox></td>        <td><asp:RequiredFieldValidator runat="server"               ControlToValidate="txtFirstName"               InitialValue=""               ErrorMessage="You must enter the first name."/></td>    </tr>    <tr>        <td>Last Name: </td>        <td><asp:textbox runat="server" id="txtLastName"></asp:textbox></td>        <td><asp:RequiredFieldValidator runat="server"               ControlToValidate="txtLastName"               InitialValue=""               ErrorMessage="You must enter the last name."/></td>    </tr>    <tr>        <td>Title: </td>        <td><asp:textbox runat="server" id="txtTitle"></asp:textbox></td>    </tr>       <tr>        <td>Date Hired: </td>        <td><asp:textbox runat="server" id="txtDateHired"></asp:textbox></td>    </tr>    </table>    <input type="submit" id=submit1 name=submit1> </form> <hr> </BODY> </HTML>

Listing 7.2 C# Code Used to Add Records to a SQL Database

 <script language="C#" runat="server" >       void Page_Load(Object Source, EventArgs E)       {          if(IsPostBack && Page.IsValid)          {            SqlConnection conn = new SqlConnection(                 "Initial Catalog=Northwind;Server=(local);UID=sa;PWD=;");            //Build the SQL string            //Use StringBuilder object for better performance            StringBuilder strBuilder = new StringBuilder();            strBuilder.Append("INSERT INTO Employees ");            strBuilder.Append("(FirstName, LastName, Title, HireDate) VALUES(");            strBuilder.Append("'" + Request["txtFirstName"] + "',");            strBuilder.Append("'" + Request["txtLastName"] + "',");            strBuilder.Append("'" + Request["txtTitle"] + "',");            strBuilder.Append("'" + Request["txtDateHired"] + "')");            string sSQL = strBuilder.ToString();            SqlCommand cmd = new SqlCommand(sSQL, conn);            conn.Open();            cmd.ExecuteNonQuery();            conn.Close();            txtFirstName.Text = "";            txtLastName.Text = "";            txtTitle.Text = "";            txtDateHired.Text = "";          }          LoadData();       }       private void LoadData()       {          SqlConnection conn = new SqlConnection(                "Initial Catalog=Northwind;Server=(local);UID=sa;PWD=;");          SqlCommand cmd = new SqlCommand(            "SELECT FirstName, LastName, Title, HireDate FROM Employees", conn);          SqlDataAdapter adapt = new SqlDataAdapter(cmd);          DataSet dsEmployees = new DataSet();          conn.Open();          adapt.Fill(dsEmployees, "Employees");          conn.Close();          employees.DataSource = dsEmployees;          employees.DataBind();       } </script>

Most of the preceding code is provided to give you some context in a realistic usage of ADO.NET. The ADO.NET code exists inside the <script . . .> </script> tags. Let's analyze this code in detail step by step:

 Dim conn as new SqlConnection( _      "Initial Catalog=Northwind;Server=(local);UID=sa;PWD=;")

This code creates a Connection object that will be used to connect to the database server. In this example, we're connecting to the standard Northwind database on a SQL server. For more information about connecting to a database, see Hour 5, "Connecting to a Data Source."

 'Build the SQL string 'Use StringBuilder object for better performance Dim strBuilder as new StringBuilder() strBuilder.Append("INSERT INTO Employees ") strBuilder.Append("(FirstName, LastName, Title, HireDate) VALUES(") strBuilder.Append("'" + Request("txtFirstName") + "',") strBuilder.Append("'" + Request("txtLastName") + "',") strBuilder.Append("'" + Request("txtTitle") + "',") strBuilder.Append("'" + Request("txtDateHired") + "')") Dim sSQL as string = strBuilder.ToString()

This block of code creates a StringBuilder object used to concatenate the SQL query string that we're going to send to the database server. We use the StringBuilder object because it performs string concatenation much more quickly than applying the " + " operator to strings. The SQL query string can be built using any technique you prefer, so long as you have a well-formatted SQL query when you are done.
```
 Dim cmd as SqlCommand = new SqlCommand(sSQL, conn) 
```
Then we create a new Command object by passing it the SQL string we've just built and the Connection object from step 1.
```
 conn.Open() cmd.ExecuteNonQuery() conn.Close() 
```
We open the connection to the database, send the query to the database server where it will be executed, and then close the connection to the database. Notice that the connection is opened at the very last possible moment before we need it and then closed as soon as we're done. This is done because database connections are a relatively expensive resource.
```
 txtFirstName.Text = "" txtLastName.Text = "" txtTitle.Text = "" txtDateHired.Text = "" 
```
This last set of code just prepares the objects on the Web form to add another record. The LoadData() method in Listing 7.1 is code you have seen before. It simply retrieves the set of employees to display on the Web form.

Deleting Data

Deleting data in a two-tier environment is done much the same as in the example in the preceding section. You build a SQL DELETE string dynamically, based on the item a user selects. The Web form in Listing 7.3 displays a ListBox Web control loaded with data from the Northwind Employees table. The C# server-side code is provided in Listing 7.4. If the user selects an item in the ListBox and clicks the Submit button, the record is deleted from the database.

Listing 7.3 Dynamically Building a SQL Statement to Delete Records from a SQL Database

 <% @Page Debug="true" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <HTML> <HEAD>     <LINK rel="stylesheet" type="text/css" href="Main.css">     <!-- End Style Sheet -->     <script language="VB" runat="server" >       Sub Page_Load(Source as Object, E as EventArgs)          LoadData()       End Sub       Sub cmdDelete_OnClick(Source as Object, E as EventArgs)          Dim selectedEmployee as Int32 = Int32.Parse(Request("lstEmployees"))          if selectedEmployee > 0 then             Dim conn as SqlConnection = new SqlConnection( _                  "Initial Catalog=Northwind;Server=(local);UID=sa;PWD=;")             'Build the SQL string             Dim sSQL as string = ("DELETE FROM Employees " + _                         "WHERE EmployeeID = " + selectedEmployee.ToString())             Dim cmd as SqlCommand = new SqlCommand(sSQL, conn)             conn.Open()             cmd.ExecuteNonQuery()             conn.Close()          end if          LoadData()       End Sub       Sub LoadData()          Dim conn as SqlConnection = new SqlConnection( _                "Initial Catalog=Northwind;Server=(local);UID=sa;PWD=;")          Dim cmd as SqlCommand = new SqlCommand( _            "SELECT FirstName + ' ' + LastName as Name, EmployeeID" + _            " FROM Employees", conn)          Dim adapt as SqlDataAdapter = new SqlDataAdapter(cmd)          Dim dsEmployees as DataSet = new DataSet()          conn.Open()          adapt.Fill(dsEmployees, "Employees")          conn.Close()          lstEmployees.DataSource = dsEmployees          lstEmployees.DataMember = "Employees"          lstEmployees.DataTextField = "Name"          lstEmployees.DataValueField = "EmployeeID"          lstEmployees.DataBind()       End Sub     </script> </HEAD> <BODY> <h1>Deleting a Record</h1> <hr> <form runat="server" id=form1 name=form1>    <asp:Label id=msg runat=server /><br><br>    <asp:ListBox id="lstEmployees" EnableViewState="true" runat="server">    </asp:ListBox>    <br>    <asp:Button id="cmdDelete" OnClick="cmdDelete_OnClick"                Text="Delete" runat=server/> </form> <hr> </BODY> </HTML>

Listing 7.4 C# Code Without Web Form Used to Delete Records from a SQL Database

 <script language="C#" runat="server" >       void Page_Load(Object Source, EventArgs E)       {          LoadData();       }       void cmdDelete_OnClick(Object Source, EventArgs E)       {          int selectedEmployee = Int32.Parse(Request["lstEmployees"]);          if(selectedEmployee > 0)          {             SqlConnection conn = new SqlConnection(                  "Initial Catalog=Northwind;Server=(local);UID=sa;PWD=;");             //Build the SQL string             string sSQL = ("DELETE FROM Employees " +                         "WHERE EmployeeID = " + selectedEmployee.ToString());             SqlCommand cmd = new SqlCommand(sSQL, conn);             conn.Open();             cmd.ExecuteNonQuery();             conn.Close();          }          LoadData();       }       private void LoadData()       {          SqlConnection conn = new SqlConnection(                "Initial Catalog=Northwind;Server=(local);UID=sa;PWD=;");          SqlCommand cmd = new SqlCommand(            "SELECT FirstName + ' ' + LastName as Name, EmployeeID" +            " FROM Employees", conn);          SqlDataAdapter adapt = new SqlDataAdapter(cmd);          DataSet dsEmployees = new DataSet();          conn.Open();          adapt.Fill(dsEmployees, "Employees");          conn.Close();          lstEmployees.DataSource = dsEmployees;          lstEmployees.DataMember = "Employees";          lstEmployees.DataTextField = "Name";          lstEmployees.DataValueField = "EmployeeID";          lstEmployees.DataBind();       } </script>

If you recall from Hour 3, in order to delete a record from the database, you only need to uniquely identify the record you want to delete in the WHERE portion of your DELETE query. In this example, the ListBox is populated with the name (which is visible) and EmployeeID (which is hidden from the user). In this case, and in most cases, the ID field uniquely identifies the record we want to remove. The EmployeeID value is passed from the ListBox to the SQL query, which is then applied to the database, removing the record. The only differences between this example and the one provided in Listings 7.1 and 7.2 are the way the data is displayed and collected on the Web form, and the exact syntax of the SQL query.

Modifying Data

Modifying data using two-tier methodology is done in much the same way as the other examples in this chapter. You build an UPDATE SQL string dynamically based on user selections and then pass the string to the data source where it is executed. However, the example in this section provides a new twist.

Rather than displaying a Web form to the user, the example in Listing 7.5 (VB .NET) and Listing 7.6 (C#) exists as an intermediate form. This Web form is called from other pages that pass information to it along the query string. The Web form accepts the input, creates an UPDATE string, sends it to the SQL database, and then returns to the calling page. This is a nice way to segregate functionality needed by more than one Web form in an application.

There are a number of different ways to modify data. The two-tier example in Listing 7.5 is one of the easiest. In Hour 8, "Using the DataReader and DataAdapter," you'll learn how to use the DataAdapter object to automatically apply data changes back to the database.

Listing 7.5 Dynamically Building a SQL Statement to Update Records in a SQL Database

 <% @Page Debug="true" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <HTML> <HEAD>     <LINK rel="stylesheet" type="text/css" href="Main.css">     <!-- End Style Sheet -->     <script language="VB" runat="server" >       Sub Page_Load(Source as Object, E as EventArgs)           Dim EmployeeID as Int32 = Int32.Parse(Request("EmployeeID"))           Dim firstName as string = Request("FirstName")           Dim lastName as string = Request("LastName")           Dim title as string = Request("Title")           Dim hireDate as string = Request("HireDate")          if EmployeeID > 0 then             Dim conn as SqlConnection = new SqlConnection( _                  "Initial Catalog=Northwind;Server=(local);UID=sa;PWD=;")             'Build the SQL string             Dim sSQL as string = "UPDATE Employees " + _                            "SET " + _                            "FirstName='"" + firstName + "', " + _                            "LastName='" + lastName + "', " + _                            "Title='" + title + "', " + _                            "HireDate='" + hireDate + "' " + _                            "WHERE EmployeeID = " + EmployeeID.ToString()             Dim cmd as SqlCommand = new SqlCommand(sSQL, conn)             conn.Open()             cmd.ExecuteNonQuery()             conn.Close()             'Return to the previous page             Response.Redirect( Request.ServerVariables("HTTP_REFERER") )          end if       End Sub     </script> </HEAD> <BODY> <h1>Updating Database</h1> <hr> <p> Since this page is designed to process data and then return where it came fom, this UI should never display unless we're debugging.</p> <form runat="server" id=form1 name=form1>    <asp:Label id=msg runat=server /><br><br> </form> <hr> </BODY> </HTML>

Listing 7.6 C# Code Without Web Form Used to Update Records in a SQL Database

 <script language="C#" runat="server" >       void Page_Load(Object Source, EventArgs E)       {           int EmployeeID = Int32.Parse(Request["EmployeeID"]);           string firstName = Request["FirstName"];           string lastName = Request["LastName"];           string title = Request["Title"];           string hireDate = Request["HireDate"];          if(EmployeeID > 0)          {             SqlConnection conn = new SqlConnection(                  "Initial Catalog=Northwind;Server=(local);UID=sa;PWD=;");             //Build the SQL string             string sSQL = "UPDATE Employees " +                            "SET " +                            "FirstName=" + "'" + firstName + "', " +                            "LastName=" + "'" + lastName + "', " +                            "Title=" + "'" + title + "', " +                            "HireDate='" + hireDate + "' " +                            "WHERE EmployeeID = " + EmployeeID.ToString();             SqlCommand cmd = new SqlCommand(sSQL, conn);             conn.Open();             cmd.ExecuteNonQuery();             conn.Close();             //Return to the previous page             Response.Redirect( Request.ServerVariables["HTTP_REFERER"] );          }       } </script>

for RuBoard