Deborah's Developer MindScape

         Tips and Techniques for Web and .NET developers.

August 21, 2009

DAL: Save Data Using a SQL Statement

Filed under: C#,VB.NET @ 4:13 pm

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

See this for a post detailing how to retrieve data using a SQL statement.

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

        // Define the command
        using (SqlCommand cmd= new SqlCommand())
            cmd.Connection = cnn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = sqlStatement;

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

            // Execute the stored procedure

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


        ‘ Define the command
        Using cmd As New SqlCommand
            cmd.Connection = cnn
            cmd.CommandType = CommandType.Text
            cmd.CommandText = sqlStatement

            ‘ Handle the parameters
            If arrParam IsNot Nothing Then
                For Each param As SqlParameter In arrParam
                    If firstOutputParameter Is Nothing AndAlso _
                     param.Direction = _
                             ParameterDirection.Output AndAlso _
                     param.SqlDbType = SqlDbType.Int Then
                        firstOutputParameter = param
                    End If
            End If

            ‘ Execute the Query

            ‘ 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 SQL statement. Since this code calls the SQLCommand ExecuteNonQuery, it assumes that the SQL statement 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 SQL statement and the CommandText property of the command defines the text of the SQL statement.

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("Insert into Customer " +
              "(LastName, FirstName, LastUpdateDate) Values " +
              "(@LastName, @FirstName, GETDATE()) " +
              "Select @CustomerID = @@Identity",
              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("Insert into Customer " & _

              "(LastName, FirstName, LastUpdateDate) Values " & _
              "(@LastName, @FirstName, GETDATE()) " & _
              "Select @CustomerID = @@Identity", _
              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#:

Dac.ExecuteNonQuery("Update Customer " +
              "SET LastName = @LastName, " + 
              "FirstNAme = @FirstName, " +
              "LastUpdateDate = GETDATE()" +
              "WHERE CustomerId = @CustomerId",
              new SqlParameter("@CustomerID", CustomerId),
              new SqlParameter("@LastName", LastName),
              new SqlParameter("@FirstName", FirstName));

In VB:

Dac.ExecuteNonQuery("Update Customer " & _

              "SET LastName = @LastName, " & _
              "FirstNAme = @FirstName, " & _
              "LastUpdateDate = GETDATE()" & _
              "WHERE CustomerId = @CustomerId",
              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.


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.


  1.   seesharpgears — September 21, 2009 @ 2:51 am    Reply

    Hello, this is a good approach for wrapping up the database access, but I just wanted to note few things here: this solution is no flexible for usage with different SQL providers and it still returns ADO.NET’s standard objects like DataTables etc., meaning the developer who uses this code would still need to map the data fields into her C# objects manually, which is tedious and time-consuming. Just as a suggestion, you could read my post: Database Wrapper Classes which utilizes a framework for designing a wrapping the whole access to the database to four operations Select(), Update(), Delete() and Insert() and is capable of directly mapping the SQL columns to C# attributes, meaning that a SQL table Product corresponds to a C# class Product and the Select() query would return List directly, which makes it easier for OOP manipulation. Please read my approach and tell me what do you think. All comments are appreciated.

  2.   Ziming — March 17, 2010 @ 10:54 pm    Reply

    Very helpful in creating data access layer, after I have implemented the same method from your article, my work mate suggested to use enterprise library instead, could you advise what’s your opinion about this two methods?

  3.   Md. Asadur Rahman — August 2, 2013 @ 2:36 pm    Reply

    I was looking for such a thing that you have done but couldn’t. Thanks Allah, thank you.

    I expect your help but how can i get you.

  4.   DeborahK — August 2, 2013 @ 5:27 pm    Reply

    What type of help do you need, Asadur?

RSS feed for comments on this post. TrackBack URI

Leave a comment

© 2020 Deborah's Developer MindScape   Provided by WPMU DEV -The WordPress Experts   Hosted by Microsoft MVPs