Using WPF MVVM for Database Access

• Download source code - 81 KB
• Download demo - 29.4 KB

Introduction

I like to keep examples of useful code handy as they can be invaluable when beginning a new project. One of the examples I keep is a tiny database containing a few tables and Stored Procedures, with a corresponding WPF utility to access the database. The example was very standard, straight WPF calling DB procedures via SQLCommands. Over time, I realized the example had to be updated to reflect current coding practices. Specifically, I wanted the WPF utility to use the MVVM pattern and employ LINQ to SQL to access the database. The database itself was not changed. This article describes the resulting code, what needed to be added, and surprises along the way.

Recognizing that few will be interested in code that requires installing a database, I have tried to make it more appealing by demonstrating some unrelated issues. This includes gel buttons and seamless repeating background tiles. This is my initial attempt at MVVM. Let me know how I did.

Getting Started

The database xstoredb must be installed. Download either the source or exes. In the top directory is a file named CreateStoreDb.sql. Open this file with an editor and copy the contents to the clipboard. Run up SQL Server Management Studio and open a new query window. Paste the code on the clipboard into the query window and click the Execute button. This will create an unpopulated database named xstoredb. The database is a simple one, just two small tables and four Sstored Procedures. However, it provides features seen on the production databases.

After installing the xstoredb database, one should know the connection string necessary to connect with it. This connection string must be made available to the included ProductMvvm utility for it to successfully connect to xstoredb. Providing a correct connection string can be frustrating. Before you begin, you should have a good idea what the string should look like. The connection string is made available to ProductMvvm in the application configuration file. Open ProductMvvm.exe.config, you will see the connection string resides in the following XAML...

<connectionStrings>
  <add name="ProductMvvm.Properties.Settings.xstoredbConnectionString"
    connectionString="Data Source=DOUG-PC;Initial Catalog=xstoredb;Integrated Security=True"
    providerName="System.Data.SqlClient" />
</connectionStrings>

Modify the value of the connectionString attribute on line 10 to specify the connection string required to access the xstoredb database you installed. The value shown above specifies my home PC (DOUG-PC) using Windows Integrated Security. Only a single line in the config file requires modification. Once a valid connection string is provided, the ProductMvvm utility will run.

Run up ProductMvvm.exe to test the connection string. As the database is initially empty, no products will be shown. The database connection may be checked by clicking the DB Refresh button. The status field should display OK, indicating the utility was able to access the database. Fill in the product fields and use the Add button to create products in the database. A snapshot of the display after adding a few products is shown below. The operator is about to delete the third product selected in the blue product selector screen at the top of the display.

About the Display

<Window x:Class="ProductMvvm.Window1"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" 
    xmlns:vw="clr-namespace:ProductMvvm.Views"       
    Title="ProductMvvm" Height="550" Width="370" MinHeight="550"
    WindowStartupLocation="CenterScreen" Loaded="Window_Loaded">
    <Grid>
        <Grid.Background>
            <ImageBrush ImageSource="LightBrushedx.jpg" TileMode="Tile" 
                            ViewportUnits="Absolute" Viewport="0,0,200,200">
            </ImageBrush>
        </Grid.Background>

        <Grid.RowDefinitions>
            <RowDefinition Height="3*"></RowDefinition>
            <RowDefinition Height="Auto"></RowDefinition>
            <RowDefinition Height="7*"></RowDefinition>
        </Grid.RowDefinitions>
        <vw:ProductSelectionView Grid.Row="0"/>
        <GridSplitter Grid.Row="1" HorizontalAlignment="Stretch" 
            VerticalAlignment="Bottom" ResizeBehavior="PreviousAndNext"
            Height="5"/>
        <vw:ProductDisplay Grid.Row="2"/>
    </Grid>
</Window>

An examination of the Window1.xaml file reveals a simple grid with three rows. The rows contain a ProductSelectionView, a GridSplitter, and the ProductDisplayView. Note the definition of the vw namespace for the views and its use to reference the classes for these views. Two views are used to keep the code reasonably simple but allow illustration of multiple views interacting with each other in a decoupled fashion. The Window_Loaded event is just a placeholder, it is not used in the code. Also note the use of LightBrushedx.jpg as a repeating background tile for the grid. Examine the display to verify the tile is indeed seamless. The reader will have to forgive my artistry. I was trying for a brushed metal look. However, the tile is interesting enough to suggest potential.

ProductSelectionView is a listbox displaying the model names of all products. In the database, the ModelName of a product is defined to be Unique. The operator selects a single product by clicking on a model name in ProductSelectionView. A control click may be used to deselect a product. The presence or absence of a selection determines what database operations are permitted. A selection is required to Update or Delete a product. A selection is not permitted when a new product is being Added. The image above shows a product being deleted. Note: there is a selected product. The Add button is grayed out because a selection is active.

ProductDisplayView is the more complicated view. It includes an uncomfortable amount of functionality in order to limit this example to two views. ProductDisplayView contains command buttons to access the database, text fields to display a selected product, and a status field showing database status or error messages. The text fields displaying a product can present what looks like WPF's error checking. Actually, WPF error checking facilities were not employed. Home grown error checking was used to minimize operator distraction. Product text fields are not checked as the operator modifies them. Error checking is performed at the last possible moment when the operator clicks a button. The specific button clicked determines which, if any fields, are error checked. For example, a Delete only requires that a product is selected. Field error checking is not performed for Deletes as the contents of the fields is immaterial for the success of the requested operation.

The command buttons are briefly described below...

• DB Refresh - Re-initiates the utility. The database is accessed again to get all the current products. Use this button to confirm that product modifications are being entered in the database correctly.
• Clear - A convenience function. Any selected product is deselected, and all product text fields are cleared.
• Update - The selected product is updated on the database with the contents of the product text fields.
• Delete - The selected product is deleted from the database.
• Add - A new product is created on the database using the contents of the product text fields.

The Views

MVVM views typically avoid the use of named elements, bind to public data fields in the code, and make use of commanding. The two views in the ProductMvvm utility are standard MVVM. The XAML for ProductSelectionView is shown below to illustrate:

<UserControl x:Class="ProductMvvm.Views.ProductSelectionView"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    xmlns:vw="clr-namespace:ProductMvvm.Views"        
    xmlns:vm="clr-namespace:ProductMvvm.ViewModels"
    xmlns:foundation="clr-namespace:MvvmFoundation.Wpf">
        
    <UserControl.DataContext>
        <vm:ProductSelectionModel />
    </UserControl.DataContext>

    <Grid>
        <ListBox Margin="10" Background="LightSkyBlue"
            ItemsSource="{Binding DataItems}" 
            DisplayMemberPath="ModelName"
            SelectedItem="{Binding SelectedProduct}"
            foundation:CommandBehavior.RoutedEventName="SelectionChanged"
            foundation:CommandBehavior.TheCommandToRun="{Binding Path=ListBoxCommand}">
        </ListBox>
    </Grid>
</UserControl>

Both views are UserControls. Note the definition and usage of the vm namespace to set the DataContext for the UserControl. The associated ViewModel is being instantiated by the XAML. XAML instantiated ViewModels require parameterless constructors. The expected databindings are present. Something strange is going on with the command for the SelectedProduct and the foundation namespace. More on this later. The XAML for ProductDisplayView is very similar. Due to its length, it is not reproduced here. However, it employs the same principles.

About the Foundation

An MVVM application contends with a set of common problems that are frequently encountered. This is especially true when multiple Views and their associated ViewModels are present. Luckily, there are all kinds of MVVM Foundations or Frameworks written by gifted people which elegantly solve MVVM problems. The Foundations range from simple to all inclusive complex solutions that Do Everything But Eat (a.k.a. Debbie). My approach has been to put together a Foundation by selectively taking only what I need from simple, free solutions available on the web. None of the code in the Foundation directory was written by me. Josh Smith or Sacha Barber are the authors. I may have gone overboard with the Foundation. One could hand craft alternate solutions. But using the Ffoundation produced simple standardized code. I was also able to do more with just XAML. The problems solved include...

1. Messenger.cs - Supports decoupled messaging between ViewModels in a Publish Subscribe fashion. The message sender does not know which, if any, ViewModels are receiving the message.
2. RelayCommand.cs - Uses Delegates to relay a commands functionality to another object. Also supports CanExecute.
3. CommandBehavior.cs - Used to attach commands to any WPF element.

ViewModel Communication

ProductSelectionModel maintains a viewable collection of the products in the database. The model is self populating. It accesses the database to retrieve all the products. ProductDisplayModel does the remainder of database manipulation. It accesses the database as products are Added, Deleted, or Updated. The two ViewModels communicate with one another as the operator makes changes. For example, when the operator cancels a selection in the ProductSelection view, ProductDisplayModel is informed of the cancellation so it knows it can not perform a Delete or Update until another product is selected. Similarly, when a product is Deleted in ProductDisplayModel, ProductSelectionModel is informed so it can remove the deleted product from its viewable collection. Inter ViewModel communication is done through a Messenger object. Although it may seem like complicated behavior, we can get a quick summary of which messages a View Model is interested in by looking at the View Model's constructor. The constructor registers for the types of messages it wants to receive as well as the action to be performed upon receipt.

public ProductDisplayModel()
{
    Messenger messenger = App.Messenger;
    messenger.Register("ProductSelectionChanged", 
                 (Action<Product>)(param => ProcessProduct(param)));
    messenger.Register("SetStatus", (Action<String>)(param => stat.Status = param));
} //ctor

public ProductSelectionModel()
{
    dataItems = new MyObservableCollection();
    DataItems = App.StoreDB.GetProducts();      //populate yourself
    listBoxCommand = new RelayCommand(() => SelectionHasChanged());
    App.Messenger.Register("ProductCleared", (Action)(() => SelectedProduct=null));
    App.Messenger.Register("GetProducts",
        (Action)(() => DataItems = App.StoreDB.GetProducts()));
    App.Messenger.Register("UpdateProduct",
        (Action<Product>)(param => UpdateProduct(param)));
    App.Messenger.Register("DeleteProduct", (Action)(() => DeleteProduct()));
    App.Messenger.Register("AddProduct", (Action<Product>)(param => AddProduct(param)));
}

Modeling a Product

Several classes are used to represent a product retrieved from the database. A retrieved product consists of information from the Product and Category tables. The information is a mixture of string and numeric data. The XAML Views bind to fields in the Product class defined within the ViewModels folder in the file Product.cs. On error, XAML bindings fail silently. For example, when the operator enters string or illegal numeric data into a XAML element bound to a numeric data field, the binding fails without indicating an error. This may leave an unexpected value in the data field. A program usually encounters this kind of error far from the binding when some operation mysteriously fails.

To avoid XAML binding failures, all bound fields in the Product class are defined as strings. String fields accept whatever the operator types. Any possible XAML binding error is eliminated. The data fields will always contain what the operator entered. The use of string fields sidesteps any binding problems but introduces another. String fields cannot be used to accept or provide numeric database data. The Product class is great for binding data to views, but unacceptable for SQL operations. The SqlProduct class is used to satisfy SQL. This class has numeric fields as well as methods to convert to/from a Product object. The SqlProduct class is used by the single module responsible for accessing the database.

Two additional classes, LinqProduct and LinqCategory, are created by Visual Studio when the database is mapped. These classes are used within LINQ queries. Their use is transitory as they are immediately converted to Product objects. As one would be hard pressed to find these classes in the code, the code fragment below from GetProducts() is provided to illustrate their use.

MyObservableCollection<Product> products = new MyObservableCollection<Product>();
try
{
    LinqDataContext dc = new LinqDataContext();
    var query = from q in dc.LinqProducts
        select new SqlProduct{              //convert to SqlProduct objects
            ProductId = q.ProductID, ModelNumber = q.ModelNumber,
            ModelName=q.ModelName, UnitCost = (decimal)q.UnitCost,
            Description = q.Description, CategoryName = q.LinqCategory.CategoryName
        };
    foreach (SqlProduct sp in query)        //convert SqlProduct to Product
        products.Add(sp.SqlProduct2Product());
} //try

The use of multiple classes to represent a product may seem unexpected or overly complex. However, one can make their usage short-lived and isolated. The Product class is used all throughout the code. In comparison, SqlProduct, LinqProduct, and LinqCategory usage is confined to the StoreDb.cs file where they are used as a bridge to form Product objects.

Error Checking Product Displays

ProductDisplayViewModel checks data fields before invoking SQL to access the database. Any errors in fields required by the specific database operation are outlined in red, and an error message is displayed in the Status field. The image below illustrates an error display. The operator is attempting to update a product with an illegal Unit Cost and a missing Category field. The operator must fix the highlighted fields in error and resubmit the Update.

I chose not to use WPF's error checking facility as it was not a good fit with the way the application works. The class ProductDisplayModelStatus is responsible for error checking, and is part of the ViewModel for product display. This class checks specific product fields depending on which operation is invoked. Homegrown error checking adds complexity to both the XAML and ViewModel. However, it gives one the freedom to do any type of error checking.

The ProductDisplay View defines a BorderBrush around each TextBox used to display a Product value. After an operation is invoked, ProductDisplayModelStatus examines Product values and determines what brush should be used to outline the TextBox. This means ProductDisplay must provide bindable data fields for each TextBox's BorderBrush value. Fragments from the ProductDisplay View and ProductDisplayModelStatus class are shown below for just the UnitCost field.

<TextBox Margin="5" Grid.Row="2" Grid.Column="1"  
   BorderBrush="{Binding Path=Stat.UnitCostBrush}" BorderThickness="1"
   Text="{Binding Path=DisplayedProduct.UnitCost, UpdateSourceTrigger=PropertyChanged}">
</TextBox>

public class ProductDisplayModelStatus : INotifyPropertyChanged
{
    public event PropertyChangedEventHandler PropertyChanged;
    public void OnPropertyChanged(PropertyChangedEventArgs e)
    {
        if (PropertyChanged != null)
            PropertyChanged(this, e);
    }

    public SolidColorBrush UnitCostBrush
    {
        get { return unitCostBrush; }
        set { unitCostBrush = value; 
              OnPropertyChanged(new PropertyChangedEventArgs("UnitCostBrush")); }
    }

The Model

The xStoreDb database consists of the two tables shown above and four Stored Procedures. All access to the legacy database was done by invoking the Stored Procedures via the Store.cs file. Originally, this was done using SqlCommands. Before the SqlCommand logic could be replaced with LINQ to SQL, the database had to be mapped. I let Visual Studio do the mapping. Adding a LINQ to Sql classes item to the project opens up the O/R Designer. Dragging both database tables from the Server Explorer to the left pane of the O/R Designer mapped the tables. Subsequently, the Stored Procedures were dragged to the designer's right pane. If you try to drag a Stored Procedure to a table in the left pane, you will get a schema error. This completed mapping the database for LINQ to SQL.

Deciding how to employ LINQ to SQL is a personnel decision. While I had no qualms about using LINQ to retrieve database information, I preferred the safety of Stored Procedures to modify the database. Accordingly, I replaced the GetProducts() Stored Procedure with a LINQ query which has already been shown. The remaining Stored Procedures were invoked with LINQ rather than SqlCommands. Using LINQ to invoke Stored Procedures is surprisingly easy, and simplifies the code, making it more readable. All the LINQ modifications are in the file StoreDb.cs. I left the legacy SqlCommand code commented out to show the difference with the LINQ replacement. Both mechanisms for invoking DeleteProduct() are shown below:

//LEGACY SQLCOMMAND code to invoke Stored Procedure
public bool DeleteProduct(int productId)
{
    hasError = false;
    SqlConnection con = new SqlConnection(conString);
    SqlCommand cmd = new SqlCommand("DeleteProduct", con);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("@ProductId", SqlDbType.Int, 4);
    cmd.Parameters["@ProductId"].Value = productId;
    try
    {
        con.Open();
        int rows = cmd.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
        errorMessage = "DELETE error, " + ex.Message;
        hasError = true;
    }
    finally
    {
        con.Close();
    }
    return !hasError;
}// DeleteProduct()


//LINQ TO SQL replacement code
public bool DeleteProduct(int productId)
{
    hasError = false;
    try
    {
        LinqDataContext dc = new LinqDataContext();
        dc.DeleteProduct(productId);
    }
    catch (Exception ex)
    {
        errorMessage = "Delete error, " + ex.Message;
        hasError = true;
    }
    return !hasError;
}// DeleteProduct()

Gel Buttons

The appearance of the gel buttons is defined in the file GelButtonResourceDictionary.xaml. It embeds a style for button elements in a ResourceDictionary. This simplifies reuse of the style in other projects. Just drop the file into a project and merge it. In this project, the gel resource dictionary is merged into application resources in the App.xaml file. This defines the appearance of every button in the application. Merging a resource dictionary file only takes a few lines of XAML. App.xaml is reproduced below:

<Application x:Class="ProductMvvm.App"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    StartupUri="Window1.xaml">
    <Application.Resources">
            <ResourceDictionary">
                <ResourceDictionary.MergedDictionaries">
                    <ResourceDictionary Source="GelButtonResourceDictionary.xaml"/">
                </ResourceDictionary.MergedDictionaries">
            </ResourceDictionary">
    </Application.Resources>
</Application>

Seamless Tiles

The seamless tile represents a random pattern that goes on forever without repeating itself. Examples would be brushed metal, rust, marble, or wood grain. The tile was created in Photoshop. There are many tutorials on the Web showing techniques to make repeating tiles. Basically, you come up with a random pattern tile you like. The tile is not repeatable as the edges will not line up. The tile is then turned inside out to make it repeat. Now all the edges blend into one another, but there are visible seams in the interior of the tile. The tile is completed by employing Photoshop to heal any interior seams. So far, I have not seen any other WPF program do this.

The End

I use this project as an example. It's a starting point to creating better things. I have tried to tie together a lot of things I saw on the Web into one package. Putting it together, I was surprised at how often I had to go to multiple sites on the Web to get the MVVM working. Hopefully, it will help someone out. If you see something wrong or know of a way to improve the code, please let me know.

History

• 02-Nov-2010: Initial release.