Click here to Skip to main content
Click here to Skip to main content

Populating a Silverlight DataGrid with Data from MDB

, 24 Nov 2009 CPOL
Rate this:
Please Sign up or sign in to vote.
This is a sample project to access Microsoft Access MDB data in Silverlight via OLEDB and dynamically populate a DataGrid.

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 24th 2009.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

raelango
Architect Atom8 IT Solutions (P) Ltd
India India
I have been programming since 1991. I consider programming as my passion. I founded Atom8 IT Solutions (P) Ltd., in April 2009 and have been developing applications in Microsoft Technologies (mainly C#, Silverlight).
 
My specialization has been on the US healthcare domain (Medical Transcription, Patient Financial Services, EMRs).

Comments and Discussions

 
QuestionConnectivity with Ms Access Pinmembernirajzambad7-Aug-12 21:36 
QuestionError 1 The type or namespace name 'WebContextBase' could not be found (are you missing a using directive or an assembly reference?) Pinmemberjohn_172628-Mar-12 12:07 
AnswerRe: Error 1 The type or namespace name 'WebContextBase' could not be found (are you missing a using directive or an assembly reference?) Pinmemberjohn_172630-Mar-12 14:23 
QuestionI have a question regarding the connection process? [modified] PinmemberMicahel Scott12-Aug-11 11:48 
QuestionFormatting Convertors PinmemberChristosst27-May-11 0:00 
Generalxmlvalueconvertor Pinmemberjibin.r3-Mar-11 1:20 
GeneralRe: xmlvalueconvertor PinmemberMember 288647726-May-11 23:52 
GeneralLooks good PinmemberMycroft Holmes11-Jan-11 18:05 
GeneralCode works on PC; Need help loading to ISP Service. PinmemberKentsCode18-Dec-10 9:18 
GeneralThis is a great article!! PinmemberLuis Fernando Vasquez13-Sep-10 7:32 
GeneralGreat Job^^ Pinmembernetkid922-Jul-10 1:42 
GeneralUnhandled Error in Silverlight Application [modified] PinmemberMember 317721011-Apr-10 15:41 
GeneralRe: Unhandled Error in Silverlight Application Pinmemberraelango11-Apr-10 20:10 
GeneralRe: Unhandled Error in Silverlight Application PinmemberMember 317721012-Apr-10 3:48 
QuestionWhere is Edit Data? Pinmemberyamadakun18-Mar-10 14:51 
GeneralThank you Pinmembersriprabu12-Mar-10 2:03 
GeneralRe: Thank you Pinmemberraelango12-Mar-10 3:27 
Generalexcellent article PinmemberAwais Hamid12-Jan-10 23:52 
GeneralRe: excellent article Pinmemberraelango13-Jan-10 1:34 
GeneralVery Good... Thank You PinmemberMBrooker7-Dec-09 7:01 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.141223.1 | Last Updated 24 Nov 2009
Article Copyright 2009 by raelango
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid