Deborah's Developer MindScape






         Tips and Techniques for Web and .NET developers.

Archive for Data

July 10, 2009

Populating a Business Object from a DataTable

Filed under: C#,Data,OOP,VB.NET @ 3:43 pm

Most business applications have business objects such as customer, order, or invoice. Often, the data access layer (DAL) provides the data and your code needs to use that data to manually populate a business object.

This post describes how to manually populate a business object from a DataTable. It uses the Customer class defined here.

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

In C#:

public static List<Customer> Retrieve()
{
   DataTable dt = Dac.ExecuteDataTable("CustomerRetrieveAll", null);

    List<Customer> customerList = new List<Customer>();

    foreach (DataRow dr in dt.Rows)
    {
        customerList.Add(new Customer()
                    {
                        CustomerId = (int)dr["CustomerId"],
                        LastName = (string)dr["LastName"],
                        FirstName = (string)dr["FirstName"]
                    });
    }

    return customerList;
}

In VB:

Public Shared Function Retrieve() As List(Of Customer)

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

    Dim customerList As New List(Of Customer)

    For Each dr As DataRow In dt.Rows
       customerList.Add(New Customer With _
                    {.CustomerId = CType(dr("CustomerID"), Integer), _
                     .LastName = dr("LastName").ToString, _
                     .FirstName = dr("FirstName").ToString}) 
    Next

    Return customerList
End Function

The Retrieve method is public and static (shared in VB). It is public so it can be called from the user interface code. It is static/shared because it does not use or retain any state. This allows the function to be called without creating an instance of the class containing the function.

This function calls the ExecuteDataTable method from here, which returns a DataTable. It then creates a new list and adds a new customer to the list for each row in the DataTable.

The resulting list of customers is returned. The user interface code can then use this list to bind to a control such as a grid or combo box. You can also search, sort, filter, or work with this list using LINQ or Lambda expressions.

NOTE: If the fields in the DataTable match the properties of the business object, you could use reflection to map the fields to the properties. Though reflection does have a performance hit, it provides a more general solution that could be used by any business object.

Enjoy!

DAL: DataTable Visualizer

Filed under: C#,Data,Debugging,VB.NET @ 3:05 pm

This post details how to use the DataTable Visualizer. The DataTable Visualizer is a debugging tool that allows you to see and edit the contents of a DataTable while you are debugging.

NOTE: There is also a DataSet Visualizer that works the same way for DataSets instead of DataTables.

To use the DataTable Visualizer:

  1. Set a break point on any line of code after you have a DataTable populated.
     
  2. Run your application until it stops at the break point.
     
  3. Hover over your DataTable variable.
    You should get a tooltip that displays a magnifying glass. image
    Note: Though this example shows C# code, this technique works the same in VB.
     
  4. Click on the arrow next to the magnifying glass to display the drop down menu.
    image
  5. Select the "DataTable Visualizer" menu option.
    The DataTable contents is then displayed:
    image

This allows you to view your DataTable and optionally update the data in the table for debugging or testing.

Enjoy!

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.

DAL: Retrieve a DataTable using a Stored Procedure

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

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.

DAL: Data Access Layer

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

One of the common ways to access data in a .NET application is to use the drag and drop TableAdapter tools or the new Entity Framework tools. But what if you want to write your data access code yourself?

There are many ways to access data in an application without using the drag and drop tools:

SQL Server

  • Retrieve a DataTable using a stored procedure (example)
     
  • Retrieve a DataTable using a SQL string (example)
     
  • Access a DataReader using a stored procedure (example)
     
  • Access a DataReader using a SQL string (example)
     
  • Retrieve a populated business object using a DataContext
     
  • Save data using a stored procedure (example)
     
  • Save data using a SQL string (example)
  • … 
     

Microsoft Access

  • Retrieve a DataTable using Microsoft Access (example)
     
  • Update a Microsoft Access database (example)

Data Provider Factory

  • Retrieve a DataTable using any database (example)

Regardless of the technique you choose, you should consider writing the code in a separate project, often referred to as a data access component (DAC) or a data access layer (DAL). Placing this code in a separate project allows you to more readily reuse the code in any future solution. This separation is also good programming practice because it encapsulates all of your data access code in one place.

If you are a beginner with VB or C#, one of the most common practices for adding data access code in an application is to put the code directly in the Form Load or Selected Index Changed or Button Clicked events. The application then ends up with data access code and SQL statements sprinkled throughout the application. This creates a lot of repeated code and makes maintenance more difficult.

Even if you don’t want to create a separate project for your data access code, you should minimally put it in its own function so it can be handled in one place in your application.

Enjoy!

« Previous Page

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