Click here to Skip to main content
15,867,488 members
Articles / Web Development / ASP.NET

Populating a Silverlight DataGrid with Data from MDB

Rate me:
Please Sign up or sign in to vote.
5.00/5 (11 votes)
24 Nov 2009CPOL2 min read 74.2K   2.3K   23   21
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:

C#
[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):

C#
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.

C#
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.

C#
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)


Written By
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

 
QuestionPopulate Datagrid Silverlight Pin
carlos_lv827-Apr-17 12:22
carlos_lv827-Apr-17 12:22 
QuestionConnectivity with Ms Access Pin
nirajzambad7-Aug-12 20:36
nirajzambad7-Aug-12 20:36 
QuestionError 1 The type or namespace name 'WebContextBase' could not be found (are you missing a using directive or an assembly reference?) Pin
Roger C Moore28-Mar-12 11:07
Roger C Moore28-Mar-12 11:07 
AnswerRe: Error 1 The type or namespace name 'WebContextBase' could not be found (are you missing a using directive or an assembly reference?) Pin
Roger C Moore30-Mar-12 13:23
Roger C Moore30-Mar-12 13:23 
QuestionI have a question regarding the connection process? [modified] Pin
Micahel Scott12-Aug-11 10:48
Micahel Scott12-Aug-11 10:48 
QuestionFormatting Convertors Pin
Christosst26-May-11 23:00
Christosst26-May-11 23:00 
Generalxmlvalueconvertor Pin
jibin.r3-Mar-11 0:20
jibin.r3-Mar-11 0:20 
GeneralRe: xmlvalueconvertor Pin
Christosst26-May-11 22:52
Christosst26-May-11 22:52 
GeneralLooks good Pin
Mycroft Holmes11-Jan-11 17:05
professionalMycroft Holmes11-Jan-11 17:05 
GeneralCode works on PC; Need help loading to ISP Service. Pin
KentsCode18-Dec-10 8:18
KentsCode18-Dec-10 8:18 
GeneralThis is a great article!! Pin
Luis Fernando Vasquez13-Sep-10 6:32
Luis Fernando Vasquez13-Sep-10 6:32 
GeneralGreat Job^^ Pin
netkid922-Jul-10 0:42
netkid922-Jul-10 0:42 
GeneralUnhandled Error in Silverlight Application [modified] Pin
Member 317721011-Apr-10 14:41
Member 317721011-Apr-10 14:41 
GeneralRe: Unhandled Error in Silverlight Application Pin
raelango11-Apr-10 19:10
raelango11-Apr-10 19:10 
GeneralRe: Unhandled Error in Silverlight Application Pin
Member 317721012-Apr-10 2:48
Member 317721012-Apr-10 2:48 
QuestionWhere is Edit Data? Pin
yamadakun18-Mar-10 13:51
yamadakun18-Mar-10 13:51 
GeneralThank you Pin
sriprabu12-Mar-10 1:03
sriprabu12-Mar-10 1:03 
GeneralRe: Thank you Pin
raelango12-Mar-10 2:27
raelango12-Mar-10 2:27 
Generalexcellent article Pin
Awais Hamid12-Jan-10 22:52
Awais Hamid12-Jan-10 22:52 
GeneralRe: excellent article Pin
raelango13-Jan-10 0:34
raelango13-Jan-10 0:34 
GeneralVery Good... Thank You Pin
MBrooker7-Dec-09 6:01
MBrooker7-Dec-09 6:01 

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

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