Deborah's Developer MindScape

This post provides an implementation of a method that retrieves a DataTable from a SQL Server database using a stored procedure. Your application can then use the DataTable to populate your business objects or to bind directly to a UI control such as a Combo Box or Grid (such as the DataGridView).

NOTE: This post assumes you already have a stored procedure defined to select the required data from your database. For more information on stored procedures, see this link.

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

In C#:

public static class Dac
{

public static DataTable ExecuteDataTable(string storedProcedureName, 
                                     params SqlParameter[] arrParam) 
{
    DataTable dt = new DataTable();

    // 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);
            }

            // Define the data adapter and fill the dataset
            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
            {
                da.Fill(dt);
            }
        }
    }
    return dt;
}
}

In VB:

Public Class Dac

  Public Shared Function ExecuteDataTable(ByVal storedProcedureName _
        As String, _
        ByVal ParamArray arrParam() As SqlParameter) As DataTable
    Dim dt As DataTable

    ' 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)
                Next
            End If

            ' Define the data adapter and fill the dataset
            Using da As New SqlDataAdapter(cmd)
                dt = New DataTable
                da.Fill(dt)

            End Using
        End Using
    End Using

    Return dt
  End Function

End Class

The ExecuteDataTable function is in my Dac (data access component) class and defined to be 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 a instance of the class containing the function.

The function has two parameters. The first is the name of a stored procedure. Since this code returns a DataTable, it assumes that the stored procedure contains one Select statement. Here is an example:

CREATE PROCEDURE dbo.CustomerRetrieveAll
AS
    SELECT    
                CustomerId,
                LastName,
                FirstName
    FROM       
                Customer
    ORDER BY
                LastName + ', ' + FirstName

The second parameter is a parameter array that allows defining any parameters required by the stored procedure. In the CustomerRetrieveAll example, there are no parameters. But your stored procedure may have parameters to filter the data.

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

The first line of code defines a variable for the DataTable. It is used as the return value from the function.

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.

The third Using statement defines a DataAdapter. The Fill method of the DataAdapter fills the DataTable with the data returned from the stored procedure.

Since the code uses the Using statement, all of the objects are appropriately closed and disposed.

The resulting filled DataTable is then returned.

As an example, this is how you call this method.

In C#:

DataTable dt = Dac.ExecuteDataTable("CustomerRetrieveAll", null);

In VB:

Dim dt As DataTable = Dac.ExecuteDataTable("CustomerRetrieveAll", _
                                                           nothing)

If the stored procedure had parameters, you would call it like this:

In C#:

DataTable dt = Dac.ExecuteDataTable("CustomerRetrieveById",
                         new SqlParameter("@CustomerID", custId));

In VB:

Dim dt As DataTable = Dac.ExecuteDataTable("CustomerRetrieveById", _ 
                         new SqlParameter("@CustomerID", custId))

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.

EDIT: (2/1/10)

The VB code was missing an End Using statement. The code is now corrected.