XML and Database Mapping in .NET

The designers of the .NET runtime put a lot of thought into the issue of binding XML to a database. The path from XML to database and back again starts with ADO.NET, .NET's database persistence layer, which can be thought of as analogous to Java's JDBC.

The System.Data.SqlClient namespace has all the classes you'd expect to see, like the SqlConnection, SqlCommand, and DataReader (analogous to the Java Connection, SqlCommand, and ResultSet respectively). The classes in this namespace, whose names all begin with Sql, are meant to work with Microsoft's database of choice, SQL Server. There is an entire suite of classes in the namespace System.Data.OleDb, whose names start with OleDb, which work with any OLE DB provider. If you've used JDBC, you already know basically how to use these classes.

Along with these classes, ADO.NET also introduces the DataSet. A DataSet instance represents an entire database, including the ability to track changes made to individual data elements and to persist them to the underlying database when necessary. The DataSet can maintain its state while disconnected from the actual database.

The DataSet can be used to build a data model without writing any SQL. It can also be used to persist the data model to an XML Schema or to read an existing XML Schema and dynamically build the data model. The DataSet can also read and write its data to XML.

On With the Show

You'll recall that in previous articles I used a dog show to demonstrate generating classes in Java and C# from an XML schema. This time, since we're building a database, the database schema for the dog show will require the following schema:

Normally if you were building a database like this, you would use SQL or have access to a graphical entity relationship modeling tool. With the .NET DataSet class, however, you can build a database schema dynamically. The following code demonstrates one way to do that:

using System;
using System.Data;

public class MakeDataSet {
    public static void Main(string [] args) {
        DataSet dataSet = new DataSet("DogShow");

        // create Show table
        DataTable showTable = dataSet.Tables.Add("Show");
        DataColumn showIdColumn = 
            showTable.Columns.Add("Id", typeof(Int32));
        showTable.Columns.Add("Name", typeof(String));
        showTable.PrimaryKey = 
            new DataColumn [] {showIdColumn};

        // create Breed table
        DataTable breedTable = dataSet.Tables.Add("Breed");
        DataColumn breedIdColumn = 
            breedTable.Columns.Add("Id", typeof(Int32));
        breedTable.Columns.Add("Name", typeof(String));
        breedTable.PrimaryKey = 
            new DataColumn [] {breedIdColumn};

        //  create Dog table
        DataTable dogTable = dataSet.Tables.Add("Dog");
        DataColumn dogIdColumn = 
            dogTable.Columns.Add("Id", typeof(Int32));
        dogTable.Columns.Add("Name", typeof(String));
        DataColumn dogBreedIdColumn = 
            dogTable.Columns.Add("BreedId", typeof(Int32));
        dogTable.PrimaryKey = 
            new DataColumn [] {dogIdColumn};

        // create foreign key relationship
        dataSet.Relations.Add("DogBreed", breedIdColumn, 
            dogBreedIdColumn);

        // create Judge table
        DataTable judgeTable = dataSet.Tables.Add("Judge");
        DataColumn judgeIdColumn = 
            judgeTable.Columns.Add("Id", typeof(Int32));
        judgeTable.Columns.Add("FirstName", typeof(String));
        judgeTable.Columns.Add("LastName", typeof(String));
        judgeTable.PrimaryKey = 
            new DataColumn [] {judgeIdColumn};

        // create ShowRing table
        DataTable showRingTable = 
            dataSet.Tables.Add("ShowRing");
        DataColumn showRingIdColumn = 
            showRingTable.Columns.Add("Id", typeof(Int32));
        showRingTable.Columns.Add("Name", typeof(String));
        showRingTable.PrimaryKey = 
            new DataColumn [] {showRingIdColumn};

        // create Judging table
        DataTable judgingTable = 
            dataSet.Tables.Add("Judging");
        judgingTable.Columns.Add("ShowTime", 
            typeof(DateTime));
        DataColumn judgingBreedIdColumn = 
            judgingTable.Columns.Add("BreedId", 
                typeof(Int32));
        DataColumn judgingJudgeIdColumn = 
            judgingTable.Columns.Add("JudgeId", 
                typeof(Int32));
        DataColumn judgingShowRingIdColumn = 
            judgingTable.Columns.Add("ShowRingId", 
                typeof(Int32));
        DataColumn judgingShowIdColumn = 
            judgingTable.Columns.Add("ShowId", 
                typeof(Int32));

        // create foreign key relationships
        dataSet.Relations.Add("JudgingBreed", 
            breedIdColumn, judgingBreedIdColumn);
        dataSet.Relations.Add("JudgingJudge", 
            judgeIdColumn, judgingJudgeIdColumn);
        dataSet.Relations.Add("JudgingShowRing", 
            showRingIdColumn, judgingShowRingIdColumn);
        dataSet.Relations.Add("JudgingShow", 
            showIdColumn, judgingShowIdColumn);
    }
}

Pages: 1, 2

Next Page