Click here to Skip to main content
15,867,308 members
Articles / Programming Languages / C#
Article

ADO.NET disconnected data

Rate me:
Please Sign up or sign in to vote.
1.82/5 (4 votes)
11 May 20063 min read 45.2K   453   20   3
Shows the basics of working with ADO.NET disconnected data.

Introduction

The ADO.NET library have a neat disconnected data, this feature is quite handy when you want to have mobile users with a laptop or PDA be able to use a application while on the road. Particular applicable is when there is no readily Internet access available to the user. The application of disconnected data can be applied by your company’ needs.

Background (optional)

I’ve always been a fan of the DataSet class because it enables me to work disconnected with data, and great for a 3-tier implementation. The added benedit is that one could in theory persist common data to a local disk, for mobile users. Recently I created a small Smartphone 2003 hobby project with the dotnet Compact Compact 1.1 (“CF”). The idea was to create a app on the Smartphone 2003 device to enable me to enter transactions while out-of-town in a similar was as MS Money 2003. The entered transactions must be able to import into MS Money 2003. On the dotnet framework 1.1 I built an app which can translate my mobile transactions to a import data format which MS Money 2003 accepts – in that case Quicken QIFs.

I chose for a common data format, which is well supported in Dotnet and the CF: disconnected Dataset.

Prerequistes

In this article I will presume some fluency with the dotnet framework 1.1, VS2003.NET and ADO.NET in general. This example should work in the dotnet framework 2.0 as well though as such was untested.

Using the code

Basically to use disconnected records in addition to a XML schema, you need to have a separate XML file containing your disconnected data. Don’t worry; you can have ADO.NET generate a schema file for you.

There are 3 ways to generate a XML Schema for ado.net:
· You're a guru so you know how to make one yourself.
· Use a existing database query, returning a DataSet and save the schema using the WriteXmlSchema method.
· Programmatically create a DataTable, add DataColumns to it, then save its DataSet as a new XML schema.

To actually use the disconnected data you should:
· Instantiate a new DataSet object.
· First use the ReadXmlSchema mthod to get the schema, then use ReadXml method of DataSet to get hold of your disconnected data.

Let me give a run down of the code:
· InitializeNewDisconnectedData() manually creates a disconnected DataSet for you to experiment with.
· With GetMusicLibrary() you get the disconnected data for use in your application. I return a dataview, another of my favourite ADO.NET objects.
· RefreshLibraryGrid() is for refreshing the display grid of your music library.
· A helper function GetAssemblyPath() lets you save the schema and data XML in your application executable path.
· SaveLibrary() is used to commit changes back to the disconnected DataSet.

Sample code fragment InitializeNewDisconnectedData():

C#
/// <summary>
/// Initializes a new disconnected data source to file.
/// This initilizes a new disconnected dataset, saves it to local storage.
/// 
/// This is code for a example useage of disconnected data.
/// Example article is on http://codeproject.com, author Michiel Erasmus.
/// </summary>
private void InitializeNewDisconnectedData()
{
    try
    {
        DataSet dsDisconnectDS1;
        DataTable dtDisconnectInit1 = 
                  new DataTable("tblArtistSongs");

        DataColumn dcColumn1 = new DataColumn("ArtistSongID", 
                        System.Type.GetType("System.Int32"));
        dcColumn1.Unique = true;
        dcColumn1.AutoIncrement = true;
        dcColumn1.AutoIncrementStep = 1;
        dcColumn1.AutoIncrementSeed = 1;
        dtDisconnectInit1.Columns.Add(dcColumn1);

        dcColumn1 = new DataColumn("Artist", 
                    System.Type.GetType("System.String"));
        dtDisconnectInit1.Columns.Add(dcColumn1);
        dcColumn1 = new DataColumn("Albumname", 
                    System.Type.GetType("System.String"));
        dtDisconnectInit1.Columns.Add(dcColumn1);
        dcColumn1 = new DataColumn("Song", 
                    System.Type.GetType("System.String"));
        dtDisconnectInit1.Columns.Add(dcColumn1);

        if(dtDisconnectInit1.DataSet == null)
        {
            string strOuptPathSchema = 
               GetAssemblyPath() + @"\MyMusicLibrary.xsd";
            string strOuptPathData = 
               GetAssemblyPath() + @"\MyMusicLibrary.xml";

            dsDisconnectDS1 = new DataSet("MusicLibrary");
            dsDisconnectDS1.Tables.Add(dtDisconnectInit1);
            dsDisconnectDS1.WriteXmlSchema(strOuptPathSchema);
            dsDisconnectDS1.WriteXml(GetAssemblyPath() + 
                                     @"\MyMusicLibrary.xml");
        }

    }
    catch(Exception ex1)
    {
        throw new Exception(ex1.Message + " " + ex1.StackTrace, ex1);
    }
}

Using the GetMusicLibrary(), you can retrieve the persisted disconnected data from a storage device.

C#
/// <summary>
/// Gets the music library.
/// </summary>
/// <returns>the music library</returns>
private DataView GetMusicLibrary()
{
    DataView dvData1 = null;

    try
    {
        string strOuptPathSchema = GetAssemblyPath() + 
                                   @"\MyMusicLibrary.xsd";
        string strOuptPathData = GetAssemblyPath() + 
                                 @"\MyMusicLibrary.xml";

        // if the schema wasnt created then make it now.
        if(!System.IO.File.Exists(strOuptPathSchema))
        {
            this.InitializeNewDisconnectedData();
        }

        DataSet dsDisconnectDS1 = new DataSet("MusicLibrary");
        dsDisconnectDS1.ReadXmlSchema(strOuptPathSchema);
        dsDisconnectDS1.ReadXml(strOuptPathData);

        dvData1 = dsDisconnectDS1.Tables[0].DefaultView;
    }
    catch(Exception ex1)
    {
        throw new Exception(ex1.Message + " " + 
                            ex1.StackTrace, ex1);
    }

    return dvData1;
}

If you have a GUI you could refresh the display (optional).

C#
/// <summary>
/// Refreshes the library grid.
/// </summary>
private void RefreshLibraryGrid()
{
    try
    {
        grdLibrary.DataSource = null;
        Application.DoEvents();
        grdLibrary.DataSource = GetMusicLibrary();
    }
    catch(Exception ex1)
    {
        MessageBox.Show(ex1.Message + " " + ex1.StackTrace, 
                        "run in debug mode!", 
                        MessageBoxButtons.OK, 
                        MessageBoxIcon.Error);
    }
}

Points of Interest

This example can be extended quite big; one could build relationships etc and then have a quite complete disconnected databased application for your mobile users. As I said earlier all depends on your company’ needs.

History

11-May-2006: First version of this article.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Web Developer
Netherlands Netherlands
Michiel Erasmus is a 30-something South African information analyst in Nederland. I have worked in financial leasing systems, reporting tools, as well as good RUP, UML and analyses experience.
In my spare time I do flying combat flight sims, RPG-games, fitness, or most often conjuring a new scheme about how to become filthy stinking rich.. Wink | ;) .

Comments and Discussions

 
QuestionDo you still have the source for your PDA project? Pin
mitch.blades20-Nov-07 7:35
mitch.blades20-Nov-07 7:35 
GeneralFormatting of article tekst Pin
LockonFlamingCliffs11-May-06 4:19
LockonFlamingCliffs11-May-06 4:19 
GeneralRe: Formatting of article tekst Pin
Jon Rista12-May-06 21:50
Jon Rista12-May-06 21:50 

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.