Deborah's Developer MindScape

This post provides an implementation of a method that saves data to a SQL Server database using a stored procedure. You application can use this method to insert, update, or delete rows in any database table.

NOTE: This post assumes you already have a stored procedure defined to insert, update, or delete the data from your database. For more information and examples of stored procedures, see this link.

The code is first shown in both VB and C# and then described in detail below.

NOTE: Be sure to set a reference to System.Data.SqlClient.

In C#:

public static class Dac
{

  public static int ExecuteNonQuery(string storedProcedureName,
                              params SqlParameter[] arrParam)
  {
    int retVal=0;
    SqlParameter firstOutputParameter = null;

    // Open the connection
    using (SqlConnection cnn = new SqlConnection(
           "Data Source=.\sqlexpress;Initial Catalog=AcmeRentals;
                                    Integrated Security=True"))
    {
        cnn.Open();

        // Define the command
        using (SqlCommand cmd= new SqlCommand())
        {
            cmd.Connection = cnn;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = storedProcedureName;

            // Handle the parameters
            if (arrParam != null)
            {
                foreach (SqlParameter param in arrParam)
                {
                    cmd.Parameters.Add(param);
                    if (firstOutputParameter == null && 
                     param.Direction==ParameterDirection.Output && 
                     param.SqlDbType == SqlDbType.Int)
                        firstOutputParameter = param;
                }
            }

            // Execute the stored procedure
            cmd.ExecuteNonQuery();

            // Return the first output parameter value
            if (firstOutputParameter != null)
                retVal = (int)firstOutputParameter.Value;
        }
    }
    return retVal;
  }
}

In VB:

Public Class Dac

  Public Shared Function ExecuteNonQuery( _
        ByVal storedProcedureName As String, _
        ByVal ParamArray arrParam() As SqlParameter) As Integer

    Dim retVal As Integer = 0
    Dim firstOutputParameter As SqlParameter = Nothing

    ' Open the connection
    Using cnn As New SqlConnection(
           "Data Source=.\sqlexpress;Initial Catalog=AcmeRentals;
                                    Integrated Security=True")
        cnn.Open()

        ' Define the command
        Using cmd As New SqlCommand
            cmd.Connection = cnn
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = storedProcedureName

            ' Handle the parameters
            If arrParam IsNot Nothing Then
                For Each param As SqlParameter In arrParam
                    cmd.Parameters.Add(param)
                    If firstOutputParameter Is Nothing AndAlso _
                     param.Direction = _
                             ParameterDirection.Output AndAlso _
                     param.SqlDbType = SqlDbType.Int Then
                        firstOutputParameter = param
                    End If
                next
            End If

            ' Execute the Query
            cmd.ExecuteNonQuery()

            ' Return the first output parameter value
            If firstOutputParameter IsNot Nothing Then
                retVal = CInt(firstOutputParameter.Value)
            End If
        End Using

    End Using

    Return retVal
End Function

End Class

The ExecuteNonQuery method is in my Dac (data access component) class and is defined public and static (shared in VB). It is public so it can be called from any other code. It is static/shared because it does not use or retain any state. That allows the function to be called without creating an instance of the class containing the function.

The function has two parameters. The first is the name of a stored procedure. Since this code calls the SQLCommand ExecuteNonQuery, it assumes that the stored procedure contains a non-query statement such as an Insert, Update, or Delete. See this link for example stored procedures.

The second parameter is a parameter array that allows defining any parameters required by the stored procedure. Insert and Update stored procedures for a table normally define a parameter for any field in the table that can be updated.

NOTE: A parameter array permits a variable number of arguments to be passed to a method. So this allows any number of parameters.

The function will return an integer value containing the first integer output parameter defined for the stored procedure. This provides a “quick and dirty” way to return an autonumber column defining the Id of a new row. You can change this as appropriate to handle your primary keys.

The first two statements set up for the return value.

The first Using statement defines the connection. You will need to replace the connection string in the example with a connection string appropriate for your database.

The connection is then opened. The connection is automatically closed at the end of the Using block.

The second Using statement defines the command. In this case, the command is a stored procedure and the CommandText property of the command defines the stored procedure name.

If parameters were passed in, the parameters are added to the command’s Parameters. As the parameters are processed, the code looks for the first output parameter that is an integer. The code will later return the value of this parameter, if it is found.

The code then calls the ExecuteNonQuery method of the command to execute the query.

Finally, if there was an integer output parameter found, its value is retrieved and returns.

As an example, this is how you call this method to insert a new Customer row.

In C#:

SqlParameter idParameter = new SqlParameter("@CustomerID", CustomerId);
idParameter.Direction = ParameterDirection.Output;

int retVal = Dac.ExecuteNonQuery("CustomerInsert",
              idParameter,
              new SqlParameter("@LastName", LastName),
              new SqlParameter("@FirstName", FirstName));

In VB:

Dim idParameter As SqlParameter = _
                        New SqlParameter("@CustomerId", CustomerId)
idParameter.Direction = ParameterDirection.Output

Dim retVal As Integer = Dac.ExecuteNonQuery("CustomerInsert", _
              idParameter, _
              New SqlParameter("@LastName", LastName), _
              New SqlParameter("@FirstName", FirstName))

This code first creates a parameter for the CustomerId. This parameter is defined as an output parameter so the autonumber field set by the database when the row is insert can be returned.

Here is an example of calling this method to update a Customer rows.

In C#:

In VB:

Dac.ExecuteNonQuery("CustomerUpdate", _
              New SqlParameter("@CustomerId", CustomerId), _
              New SqlParameter("@LastName", LastName), _
              New SqlParameter("@FirstName", FirstName))

The update example is much more straightforward because it does not require any return parameters.

Enjoy!

P.S. (Edited 8/21/09)

In the interest of having code that any reader can copy/paste into their own application without much external setup, I have removed the code that uses an application setting to define the connection string.

The removed text is here: "The connection string itself is stored in the configuration file using the Settings feature in Visual Studio. (See this for information on settings in C# and this for information on settings in VB.)"

Instead, a connection string is hard-coded into the example. Though I don't recommend this approach, it will help readers use these examples more easily.