Deborah's Developer MindScape






         Tips and Techniques for Web and .NET developers.

July 7, 2009

DAL: Retrieve a DataTable using a SQL Statement

Filed under: C#,Data,VB.NET @ 5:27 pm

This post provides an implementation of a method that retrieves a DataTable from a SQL Server database using a SQL Statement. 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).

See this for a post detailing how to insert, update, or delete data using a SQL statement.

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

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

In C#:

public static class Dac
{

 public static DataTable ExecuteDataTable(string sqlStatement, 
                                     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.Text;
            cmd.CommandText = sqlStatement;

            // 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 sqlStatement _
        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.Text
            cmd.CommandText = sqlStatement

            ‘ 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

    Return dt
  End Function

End Class

The ExecuteDataTable function is in my Dac (data access component) class and is 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 SQL statement. Since this code returns a DataTable, it assumes that the SQL statment contains a single Select statement.

The second parameter is a parameter array that allows defining any parameters required by the SQL statement.

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 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.

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

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(
    "SELECT CustomerId, LastName, FirstName FROM Customer",
null);

In VB:

Dim dt As DataTable = Dac.ExecuteDataTable( _ 
     "SELECT CustomerId, LastName, FirstName FROM Customer"

                                                          
nothing)

If your SQL statement has parameters, you can call it like this:

In C#:

DataTable dt = Dac.ExecuteDataTable(
            
"SELECT CustomerId, LastName, " + 
             "FirstName FROM Customer " +   
             "WHERE CustomerId=@CustomerId",  
             new SqlParameter("@CustomerID",3));

In VB:

Dim dt As DataTable = Dac.ExecuteDataTable( _ 
             "SELECT CustomerId, LastName, " & _
             "FirstName FROM Customer " & _
             "WHERE CustomerId=@CustomerId", _
             New SqlParameter("@CustomerID", 3))

NOTE: See this for an easier way to write a SQL statement in VB using XML literals.

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.

2 Comments

  1.   bambarea — January 20, 2012 @ 3:29 am    Reply

    not good

  2.   Jadawin — January 31, 2013 @ 11:28 am    Reply

    Thank you very much. Just what I needed to insert into a test of some brownfield code that was giving me hassles.

RSS feed for comments on this post. TrackBack URI

Leave a comment

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