Populating a Silverlight DataGrid with Data from MDB

• Download source code - 1.46 MB

Introduction

This sample code illustrates an easy way to expose data stored in an Access MDB (or any other OLEDB source) and display it in a Silverlight DataGrid with minimal coding.

Background

Recently, I had to migrate my existing application from ASP.NET to a Silverlight UI. The application uses MS Access (MDB) as the data store. With LINQ to SQL classes, accessing data from SQL Server is easy. However, when it comes to data in other data stores such as MySQL or MS Access, there is no easy way.

After completing the migration, I felt that the lessons would be useful for the community - hence this article.

Using the Code

The zip file contains two projects:

1. ASP.NET Web Project
2. Silverlight project

The Web Project has a WCF service. This service exposes two methods:

[ServiceContract]
public interface IAccess
{
    [OperationContract]
    void ExecuteNonQuery(string strQuery);
    [OperationContract]
    string ExecuteQuery(string strQuery);
}

The ExecuteQuery method runs the specified query and returns the resultset as a string (XML format):

public string ExecuteQuery(string strQuery)
{
    DataTable dt = new DataTable();
    dt.TableName="data";
    OleDbDataAdapter oDA = new OleDbDataAdapter(strQuery,strConnection);
    oDA.Fill(dt);
    StringWriter sw = new StringWriter(); 
    dt.WriteXml(sw);
    return sw.ToString();
}

The Silverlight project has a XAML page (MainPage.xaml) where the user can input the SQL query. On clicking the "Query" button, the XAML page calls the WCF service ExecuteQuery method, gets the result (as XML), and populates the datagrid.

private void btnQueryDatabase_Click(object sender, System.Windows.RoutedEventArgs e)
{
    // TODO: Add event handler implementation here.
    RunQueryOnServer();
}

private void RunQueryOnServer()
{
    AccessClient ac = new AccessClient();
    ac.ExecuteQueryCompleted += 
      new EventHandler<ExecuteQueryCompletedEventArgs>(ac_ExecuteQueryCompleted);
    this.Cursor = Cursors.Wait;
    ac.ExecuteQueryAsync(txtQuery.Text);
}

void ac_ExecuteQueryCompleted(object sender, ExecuteQueryCompletedEventArgs e)
{
    dGrid.Columns.Clear();
    dGrid.ItemsSource = null;
    StringReader sr = new StringReader(e.Result);
    XDocument xDoc = XDocument.Load(sr);
    if (xDoc.Descendants("data").Count() > 0)
    {
        XElement xEl = xDoc.Descendants("data").ToList()[0];
        foreach (XElement xe in xEl.Elements())
        {
            DataGridTextColumn dg = new DataGridTextColumn();
            dg.Header = xe.Name.LocalName;
            Binding bnd = new Binding();
            bnd.Converter = new XMLValueConvertor();
            bnd.ConverterParameter = xe.Name.LocalName;
            dg.Binding = bnd;
            dGrid.Columns.Add(dg);
        }
        dGrid.ItemsSource = xDoc.Descendants("data").ToList();
    }
    this.Cursor = Cursors.Arrow;
}

Please note that we use a Binding Converter to extract the element value from the XML element during the population of the grid.

public object Convert(object value, Type targetType, object parameter, 
                      System.Globalization.CultureInfo culture)
{
    try
    {
        XElement xEl = (XElement)value;
        return xEl.Element(parameter.ToString()).Value;
    }
    catch
    {
        return "";
    }
}

public object ConvertBack(object value, Type targetType, 
       object parameter, System.Globalization.CultureInfo culture)
{
    throw new NotImplementedException();
}

Points of Interest

Before executing the application, you would have to modify the web.config to point the connection string to a valid database (and driver). This code is intended to serve as an example only. I have not added enough validation to trap errors or invalid inputs.

I would like to receive comments, suggestions, and constructive criticism about this, if you have any, or any improvements; please let me know so I can include them.

History

• Version 1 - November 24^th 2009.