Data Bound Applications with ADO.NET and Custom Objects

Article
09/30/2019

Data Points

John Papa

Code download available at: Data Points 2007_02.exe(174 KB)

Contents

Test-Driving the Application
Implementing Custom Entities and Interfaces
Generics and ADO.NET
Data Sources
Binding Controls
Binding Code
Saving Data
Using Predicates to Find an Entity
Wrapping It Up

The Windows Forms binding controls are vastly improved descendents of the data binding controls of the past. They get you going quickly and handle the redundant tasks associated with setting up forms, and you can customize and extend their behavior significantly. Data can travel in a variety of containers, including DataSets and custom class entities, and the Windows® Forms binding tools allow you to bind to all of these types of objects. If you don't want to use a DataSet, you can create custom entities to be used as the data store for your application, and you can use List<T> and other collection types to store a set of your custom entities. These types of custom entities can easily be bound using the BindingSource and the BindingNavigator. In this column I'll demonstrate how to bind a custom list of business entities using the binding tools present in the Microsoft® .NET Framework 2.0, and I'll do so by writing a fully functional data-driven Windows Forms application.

I'll begin by describing the application, in particular its use of the DataGridView and the BindingSource and BindingNavigator binding controls. Then I'll walk through the lower tiers and demonstrate how they are architected and how the data is retrieved, persisted, accessed, and sent back to the database. All code for the sample application is included in this issue's download file.

Test-Driving the Application

The application will let the user view, add, delete, update, find, and navigate through records. It loads Northwind order data into the DataGridView, as shown in Figure 1. When an order is selected, the TextBoxes, ComboBoxes and other controls on the right side of the form are filled in with the selected order's information. All the controls are bound to the same data source via a BindingSource control.

Figure 1 Viewing Northwind Orders in a DataGridView

Figure 1** Viewing Northwind Orders in a DataGridView **(Click the image for a larger view)

The BindingNavigator control is the toolbar across the top of the form in Figure 1. It has standard navigation buttons for changing the order record the screen will display. The navigation buttons work in concert with the grid on the left which allows them to remain in sync with the current record. The toolbar also has buttons that execute event handlers for adding, deleting, and updating order information. Finally, the application lets you search for a specific order (note the binoculars).

The fields for the order record that are foreign key references can be displayed using ComboBox controls. For example, a ComboBox could be used to display a list of the salespeople (that is, employees). The salesperson for a specific order would be selected in the ComboBox. This is a better option than displaying the Employee ID, which likely has little significance to the user of the application. In Figure 1, notice that the employee name rather than the employee ID is displayed in a ComboBox. Customer name is also in a ComboBox.

Implementing Custom Entities and Interfaces

While the DataSet is a powerful tool in the data access arsenal, using custom classes to manage and represent that data model in an application is also powerful. The pros and cons of both are widely documented, and many people are planted firmly in either the DataSet camp or the custom class camp. In fact, both methods are viable in enterprise architectures. Furthermore, ADO.NET tools work with both DataSets and custom classes to create the entities to represent the data objects. The bottom line is you need some sort of data store to contain your data. In this application, I'll use custom entities.

The sample application includes two projects: one for the presentation of the data and another for the business logic and data access. When creating a custom entity in the lower layer, you have to create the properties for the entity. For example, the Customer class has a CustomerID property and a CompanyName property. Figure 2 shows the private fields and the public properties that represent the CustomerID and the CompanyName. While this code can become tedious to type, especially compared to using a DataSet, employing some of the many refactoring tools that generate properties on the fly, or even a code generation tool to generate the entire class, can make the creation of the class quite simple.

Figure 2 Customer Class Properties

public event PropertyChangedEventHandler PropertyChanged;

private string _CustomerID;
public string CustomerID
{
    get { return _CustomerID; } set { _CustomerID = value; }
}

private string _CompanyName;
public string CompanyName
{
    get { return _CompanyName; }
    set
    {
        if (!value.Equals(_CompanyName))
        {
            _CompanyName = value;
            if (PropertyChanged != null)
            {
                PropertyChanged(this, 
                    new PropertyChangedEventArgs("CompanyName"));
            }
        }
    }
}

Take a look at the code in Figure 2. The Customer class in this example implements the INotifyPropertyChanged interface that raises an event called PropertyChanged if the CompanyName property changes. Notice that the CompanyName property's set accessor checks to make sure the value has actually been changed before it sets the new value. If so, the PropertyChanged event is raised and the class can notify anyone listening about the change. In my application, the BindingSource will automatically update the controls on the form with the new value when notified of a change.

The application also contains three entity classes: Order, Customer, and Employee. All of them implement the INotifyPropertyChanged interface and contain Property accessors to handle the getting and setting of the values for the properties.

Generics and ADO.NET

Once the entities have been built, methods must be created to retrieve and save them. This application's entities implement a standard list of static methods including some or all of the following: GetEntity, GetEntityList, SaveEntity, and DeleteEntity.

You could create one class for the entities and a separate class to contain the data access methods. Generally, I only separate these classes if the architecture dictates that the entities are not tightly coupled with the methods that save and retrieve them. In this sample application, the methods are tightly coupled so I chose to put them in a single class and make the methods static.

Figure 3 shows the GetEntityList<Order> method which returns an Order entities list representing all orders in the Northwind database. (Of course, this could be filtered if we added a parameter for orders for a specific customer or a date range.) By using generics and returning a List<T> instead of an ArrayList, the code guarantees that any object contained in the List will be of type T. This also means you can access an entity's properties within in the List<T> without casting it to the entity's type, as you would have to do if the entity was stored in a non-generic list. For example, you could get the OrderID from the first order in the list using the following code:

List<Order> orderList = GetMyListOfOrders();
int orderID = orderList[0].OrderID;

Figure 3 Getting a List <Order>

public static List<Order> GetEntityList()
{
    List<Order> OrderList = new List<Order>();

    using (SqlConnection cn = new SqlConnection(Common.ConnectionString))
    {
        string proc = "pr_Order_GetList";
        using (SqlCommand cmd = new SqlCommand(proc, cn))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cn.Open();
            SqlDataReader rdr = cmd.ExecuteReader(
                CommandBehavior.CloseConnection);
            while (rdr.Read())
            {
                Order order = FillOrder(rdr);
                OrderList.Add(order);
            }
            if (!rdr.IsClosed) rdr.Close();
        }
    }
    return OrderList;
}

If the entities were stored in a non-generic list, the object would have to be cast to the type:

ArrayList orderList = GetMyListOfOrders();
int orderID = ((Order)orderList[0])).OrderID;

I could have created and filled a DataSet or a DataTable with the orders. However, I chose to use a SqlDataReader because I prefer the quicker access to the data it provides. As shown in Figure 3, I grab the data via the SqlDataReader, for each row instantiating and filling an Order entity, and then adding that entity to a List<Order>, and repeating the process for each row in the SqlDataReader. I could have used a DataTable and iterated through the DataRows as well. The performance difference was negligible, but there's really no advantage in this case to incurring the extra overhead of a DataTable since I am simply iterating through the rows and filling my own custom entity list. The FillOrder method executes the following code, which creates the instance of the Order and sets its properties from the SqlDataReader:

Order order = new Order();
order.OrderID = Convert.ToInt32(rdr["OrderID"]);
order.CustomerID = rdr["CustomerID"].ToString();
order.EmployeeID = Convert.ToInt32(rdr["EmployeeID"]);
order.OrderDate = Convert.ToDateTime(rdr["OrderDate"]);
order.ShipVia = rdr["ShipVia"].ToString();
order.ShipName = rdr["ShipName"].ToString();
order.ShipAddress = rdr["ShipAddress"].ToString();
order.ShipCity = rdr["ShipCity"].ToString();
order.ShipCountry = rdr["ShipCountry"].ToString();
return order;

Notice that in Figure 3 I passed CommandBehavior.CloseConnection to the ExecuteReader method. This causes the SqlConnection object to close as soon as the SqlDataReader closes.

There are static methods in the entities to insert, update, and delete data, too. I created a public static method called SaveEntity, which accepts an entity and determines whether it is new or existing, and then calls the appropriate stored procedure to perform the action query. The static AddEntity (that is shown in Figure 4) for the Customer class accepts the Order entity and then maps the values from the entity to the corresponding parameters of the stored procedure.

Figure 4 Adding an Entity

private static Order AddEntity(Order order)
{
    int orderID = 0;
    using (SqlConnection cn = new SqlConnection(Common.ConnectionString))
    {
        string proc = "pr_Order_Add";
        using (SqlCommand cmd = new SqlCommand(proc, cn))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@customerID", order.CustomerID);
            cmd.Parameters.AddWithValue("@employeeID", order.EmployeeID);
            cmd.Parameters.AddWithValue("@orderDate", order.OrderDate);
            cmd.Parameters.AddWithValue("@shipVia", order.ShipVia);
            cmd.Parameters.AddWithValue("@shipName", 
                GetValue(order.ShipName));
            cmd.Parameters.AddWithValue("@shipAddress", 
                GetValue(order.ShipAddress));
            cmd.Parameters.AddWithValue("@shipCity", 
                GetValue(order.ShipCity));
            cmd.Parameters.AddWithValue("@shipCountry", 
                GetValue(order.ShipCountry));
            cmd.Parameters.Add(new SqlParameter("@orderID", 
                SqlDbType.Int));
            cmd.Parameters["@orderID"].Direction = 
                ParameterDirection.Output;
            cn.Open();
            cmd.ExecuteNonQuery();
            orderID = Convert.ToInt32(cmd.Parameters["@orderID"].Value);
        }
        order = GetEntity(orderID);
    }
    return order;
}

ADO.NET and the custom entities are both vital pieces of this architecture. The retrieval methods use ADO.NET to get the data from the database; the custom entities are then filled and returned to the presentation layer. The SaveEntity and DeleteEntity methods accept custom entities and then pull values out of them to apply the changes against the database.

Data Sources

Now that I've shown how the custom entities are created, filled, and returned, let's walk through the presentation layer. Because I have Customer, Order, and Employee classes in my class library project, I can use them to create the bound controls on a form. The Data Sources window shows all data sources that are available to the project. In Figure 5 you can see that I've added the three entities created in the class library project to the Data Sources window of the UI project.

Figure 5 Data Source Window

Figure 5** Data Source Window **

Data sources can be obtained from a Web service, an object, or a database. In this case, I added object data sources since I am using class entities. I added the data source by going through its wizard, which prompts you to select the namespace and the class you want to add as a data source. If you are referencing the class library project already, the Customer, Employee, and Order classes will be displayed.

Once the data sources are added, they are displayed in the Data Sources window. The Order data source in Figure 5 shows all of the Order class's public properties. Next to each property name is an icon representing the type of control that will be used to display each property's value. The OrderDate property shows a DateTimePicker control and the ShipAddress shows a TextBox control. These controls can be changed by clicking on the property name's text in the Data Sources window and selecting a different control from the dropdown list. For example, I changed the control for the OrderID property to a Label control because I wanted it to be read-only.

I do not want properties that are reference fields to other entities, such as the Order.CustomerID property, to be displayed directly. Instead I want a more descriptive value to be displayed for them. For example, I changed the control type for the CustomerID property to a ComboBox with the intention of filling the list with all customer data and showing the appropriate customer's CompanyName property. I changed both the CustomerID and the EmployeeID properties to be ComboBoxes. You can also specify that you do not want to display a property on the form by selecting the [None] option from the list instead of a control type.

After setting up my properties and the controls I want them to be displayed in, I click on the Order entity in the Data Sources window and select Details from the list. This allows me to drag the Order data source to a form and automatically generate both the display controls and the binding controls (BindingSource and BindingNavigator) on the form. This is how the controls on the right-hand side of the form in Figure 1 were created.

Binding Controls

BindingSource is a non-visible control that is the link between the Order entity and the controls on the form. The current Order entity in BindingSource will be displayed in the controls on the right-hand side of the screen where they can be viewed or edited. In this application, it will be bound to a List<Order> entity I'll pass to it from the Order class's static method GetEntityList.

The BindingNavigator control was also created by dragging the Order data source to the form. This control displays a toolbar at the top of the form that by default has a handful of buttons to handle adding, saving, and deleting records, as well as navigation buttons. The BindingNavigator is in sync with the BindingSource control so that when a record is repositioned in either control, the other control will automatically reflect that position change. For example, if the user clicks on the Next button in the BindingNavigator control, the BindingSource control will change its current entity to be the next entity, and in turn the controls to the right will display the current entity's property values.

The BindingNavigator's buttons are also controls so they have properties and events you can set, too. I added event handlers for the Save and Delete buttons on the toolbar so I could add validation and other logic before changed data was persisted to the database through the entities' static data access methods previously created.

While the BindingNavigator control handles record movement and provides an easy way to save data changes, you don't necessarily need to include it. Once the control is automatically created by dragging a data source to the form, you can delete it from the form with no ill effects. Of course, then you'll have to write your own code to implement movement throughout the list of orders and the saving of changes. Whether or not you should really depends on your UI requirements.

The CustomerID's ComboBox is currently bound to the CustomerID property of the Order data source (through the BindingSource control). I still need to fill the ComboBox with a list of customers and display the customer's CompanyName. When I created my class library project, I created a Customer entity and exposed a GetEntityList<Customer> method from it. I then added a data source for the Customer entity. All I need to do is drag the Customer data source over the CustomerID ComboBox and drop it. This will cause a second BindingSource control to be created. The ComboBox uses this new BindingSource to load the list of customers for display. So the selected value of ComboBox is still bound to the Order's BindingSource control and the list of customers and their CompanyName properties are displayed in the control. This process is repeated to fill the list for the Employee ComboBox, too. Since I dropped the data source on ComboBox, it was smart enough to know that I did not need another BindingNavigator control.

However, I wanted to give users a second way, besides BindingNavigator's toolbar, to navigate the order records. So I went back to the Data Sources window, clicked on the Order data source, changed the selection to DataGridView and dragged the Order data source onto the form. This created a DataGridView control on the form that contained a column representing each of the Order entity's properties. I then removed most of the columns so that only the key information is displayed in the grid. No additional binding controls are created because there is already a BindingSource control on this form that binds the Order data source. At this point the current order for all of the controls is exactly the same since they all are linked to the Order's BindingSource control.

Binding Code

At this point I've designed the controls but I haven't written any code for the form. The BindingSource controls for the customers, employees, and orders are all set up and ready to go, but I have yet to pass them their data. This can easily be done by first calling each entity's static method-GetEntityList-and retrieving a List<T> of the entities. The List<T> is then converted to a BindingList<T> and set as the data source for each respective BindingSource control.

Figure 6 shows how each of the three BindingSource controls are set to their lists. This is all the code that's required in the UI to allow the user to navigate and view the data. I call the SetupBindings method in the form's constructor so the data is retrieved, bound, and displayed when the form is first loaded. The user can then move through the records using the navigation buttons on the BindingNavigator toolbar or by selecting a row in the DataGridView control. However, we have yet to write the event handlers that will allow users to make changes.

Figure 6 Setting the BindingSource Control's Data

private void SetupBindings()
{
    BindingList<Order> orderList = 
        new BindingList<Order>(Order.GetEntityList());
    orderBindingSource.DataSource = orderList;

    BindingList<Customer> customerList = 
        new BindingList<Customer>(Customer.GetEntityList());
    customerBindingSource.DataSource = customerList;

    BindingList<Employee> empList = 
        new BindingList<Employee>(Employee.GetEntityList());
    employeeBindingSource.DataSource = empList;
}

Saving Data

I want the application to allow users to delete the currently selected and displayed order by clicking on the Delete button on the toolbar. First, I set the Delete button's DeleteItem property to "none" to force it to use custom code to perform the deletion. Next, I add the event handler that will perform the delete, which calls a private method named Delete, shown in Figure 7.

Figure 7 Deleting the Current Order

private void Delete()
{
    Order order = orderBindingSource.Current as Order;
    int orderID = order.OrderID;
    DialogResult dlg = MessageBox.Show(
        string.Format("Are you sure you want to delete Order # {0}?", 
        orderID.ToString()));
    if (dlg == System.Windows.Forms.DialogResult.OK)
    {
        Order.DeleteEntity(order);
        orderBindingSource.RemoveCurrent();
        MessageBox.Show(string.Format(
            "Order # {0} was deleted.", orderID.ToString()));
    }
}

The Delete method grabs the current order from the BindingSource's Current property and casts it to the entity type Order. It then asks the user if he is sure he wants to delete the order. If the user clicks OK, the Order entity is passed to the DeleteEntity static method in the class library project. Finally, the order is removed from the BindingSource by executing the BindingSource control's RemoveCurrent method.

The BindingNavigator's toolbar also has an Add button that adds a new row to the DataGridView and clears out the controls on the right side of the form. The user can then enter and select values for the new order and click the Save button on the BindingNavigator toolbar. I added an event handler to this Save button that passes the order entity to the Order entity's SaveEntity static method. The user can also edit the current order record and click the same Save button to pass the edited order entity to the Order entity's SaveEntity static method. The SaveEntity method handles both inserts and updates by checking the value of the OrderID property of the Order entity. Once the entity has been inserted or updated, the DataGridView is reloaded by grabbing the list of orders again and resetting the BindingSource's data source to keep the data fresh.

Using Predicates to Find an Entity

Creating a friendly user interface is important. Users may want to jump to a specific order if they know the Order ID. I handled this by adding a ToolStripTextBox control to the BindingNavigator where the user can enter an entire or partial OrderID to locate. I then added a new toolbar button and an event handler that initiates the search for the order record. Figure 1 shows these controls as well as the tooltip text where the user can find an order by Order ID.

Finding a DataRow inside a DataTable is easy with the Select method or the Find method. But just because I am using custom entities does not mean I have to give up features like this. In this case, I have to find an Order entity in the List<Order> that begins with the value that the user enters in the search control. The List<T> exposes a handful of methods to assist in locating one or more items in its list including the Find, FindAll, FindIndex, FindLast, and FindLastIndex methods. I will use the Find method, which accepts a Predicate<T> as its sole argument.

The Predicate<T> must be a Predicate<Order> since I have a List<Order>. The Predicate is a delegate that searches for an Order entity in the List<Order> that matches the criteria I defined (that it begins with the search value entered in the search field). Before creating the Predicate, I first created a class to assist with the search (shown in Figure 8). The OrderFilter class accepts the order to search for in its constructor. This class also has two methods for finding a specific entity. Each of the methods returns a Boolean value indicating whether there is a match. These methods are the basis of the delegate I will pass to the Predicate.

Figure 8 OrderFilter Class

private class OrderFilter
{
    private int orderID = 0;
    
    public OrderFilter(int orderID)
    {
        this.orderID = orderID;
    }

    public bool MatchesOrderID(Order order)
    {
        return order.OrderID == orderID;
    }

    public bool BeginsWithOrderID(Order order)
    {
        return order.OrderID.ToString().StartsWith(orderID.ToString());
    }
}

The code that locates the order and repositions the BindingSource is shown in Figure 9. First, I grab a reference to the list of Order entities from the BindingSource to make the code easier to read. Then I create an instance of the OrderFilter class and initialize it with the order ID to search for. Next I create the Predicate and pass to it the OrderFilter class' BeginsWithOrderID method. Finally, I execute the List<Order>'s Find method and pass it the Predicate I just created. This will in turn iterate through the list of Order entities and pass them all to the OrderFilter.BeginsWithOrderID method. The first entity that returns true will be returned and then used to reposition the BindingSource to its index.

Figure 9 Locating the Order

private void toolBtnFindOrderNumber_Click(object sender, EventArgs e)
{
    List<Order> orderList = new List<Order>(
        orderBindingSource.DataSource as BindingList<Order>);
    OrderFilter orderFilter = new OrderFilter(
        Convert.ToInt32(toolTxtFindOrderNumber.Text));
    Predicate<Order> filterByOrderID = 
        new Predicate<Order>(orderFilter.BeginsWithOrderID);
    Order order = orderList.Find(filterByOrderID);
    if (order == null)
        MessageBox.Show("No matching Order found", 
                         "Not Found", MessageBoxButtons.OK);
    else
    {
        int index = orderBindingSource.IndexOf(order);
        orderBindingSource.Position = index;
    }
}

Wrapping It Up

In this column I demonstrated the power of the .NET Windows Forms binding controls. The binding controls can be used with existing entities or DataSets in your architecture to create a bound form rather quickly. If you find these controls lacking any features, you can customize the form to get the functionality you need. Whether you prefer custom entities or DataSets, both tools can be implemented in a .NET enterprise application using data binding.

Send your questions and comments for John to mmdata@microsoft.com.

John Papa is a senior .NET Consultant with ASPSOFT (aspsoft.com) and a baseball fanatic who spends most of his summer nights rooting for the Yankees with his family and his faithful dog, Kadi. John, a C# MVP, has authored several books on ADO, XML, and SQL Server, and can often be found speaking at industry conferences such as VSLive.

Additional resources