Map Data With ADO.NET

XtremeData

LANGUAGES: C#

ASP.NET VERSIONS: 1.0 | 1.1

Map Data With ADO.NET

Move data with a flexible XML-configuration document.

By Dan Wahlin

Maps provide instructions that direct people from point Ato point B. I rely a great deal on maps created by Web sites such as MapBlast!as I travel to different consulting and training engagements. Without them, I'dsurely get lost. (Even with them, occasionally I still get lost.) I'mnot going to discuss roadmaps here, but I will explain how you can use ADO.NET classesto create "maps" that move data from point A to point B while avoiding dataloss in the process. I'll show you how to create a flexible XML-configurationdocument that allows you to map XML data to relational table fields.

Mapping XML element and attribute data to relational tablefields is a fairly common task in the world of e-commerce, and you have achoice of many different mapping products. If you require a custom solution,you can take different routes. One popular route is the eXtensible StylesheetLanguage Transformation (XSLT) language, which can transform XML data into astructure compatible with one or more database tables. You can find a codesample at http://www.XMLforASP.NET/codeSection.aspx?csID=37that uses this technique to migrate a Yahoo! Store XML document into adatabase. In addition to XSLT, you can use several other XML document-parsingand database-specific techniques to migrate XML data into a database. Ratherthan focus on these solutions, I'll demonstrate how you can use native ADO.NETclasses, such as SqlDataAdapter and DataSet, along with a few helper classes tomap data with minimal effort.

Use ADO.NET's Mapping Classes

The .NET platform provides a couple of built-in classescapable of generating data maps named DataTableMapping and DataColumnMapping.You can find these classes in the System.Data.Common namespace. By using theseclasses, you can map the XML document shown in Figure 1 to the respective fieldsin the Northwind database's Customers table (also shown in Figure 1) throughthe DataSet and DataAdapter classes.

Figure 1. Mapping XML element and attribute data to table fields can bea challenging task depending on how you attempt it. Using ADO.NET mappingclasses can simplify this process greatly. If you haven't used DataSets to workwith XML before, you can use the DataSet class's ReadXml method to load XML.

Several XML element names shown in Figure 1 match upclosely (aside from case) with the corresponding field names in the Customerstable. But attributes such as idand elements such as company aren'tas simple to match. The DataColumnMapping class can generate a mapping betweenthese items after loading the XML document into a DataSet and creating aninstance of a DataAdapter. This is accomplished by using one of theDataColumnMapping class's constructors or by assigning values to itsSourceColumn and DataSetColumn properties. Here is the constructor signature:

public DataColumnMapping(

   string sourceColumn,

   string dataSetColumn

);

To map the XML document's company element to the Customerstable's CompanyName field, you can use this code:

DataColumnMapping colMap =

    newDataColumnMapping("CompanyName","company");

After you create the column-mapping object, you canassociate it with a DataTableMapping class (used to map DataSet table names todatabase table names) through a property named ColumnMappings. Then, you canhook the DataTableMapping class to a DataAdapter through a property namedTableMappings. The DataTableMapping class has a constructor similar to theDataColumnMapping class, except instead of accepting column names (as shownearlier) it accepts source table names and DataSet table names (see Figure 2).

//Create column mapping class

DataColumnMapping colMap =

    newDataColumnMapping("CompanyName","company");

//Create table mapping class

DataTableMapping tableMap =

    newDataTableMapping("Customers","customer");

//Add DataColumnMapping class into column

//mappings collection

tableMap.ColumnMappings.Add(colMap);

//Add table mapping object to DataAdapter

SqlDataAdapter da = new SqlDataAdapter();

da.TableMappings.Add(tableMap);

//Remainder of code would create appropriate

//Command object to insert or update data in db

Figure 2. This code demonstrates how you can associatea DataColumnMapping object with a DataTableMapping object. The differentmanaged-provider DataAdapter objects (such as SqlDataAdapter) expose aTableMappings property that can accept DataTableMapping objects.

Although this approach makes mapping DataSet columns todatabase table fields fairly straightforward, the mapping information ishard-coded into the application's source code. If the schema for the XMLdocument loaded into the DataSet never changes, there's no problem. But if theschema does change occasionally, you must recompile and deploy the codecontaining the mapping details.

Increase Flexibility

To make the process of mapping XML data to database fieldsmore flexible, you can store the mapping information in an XML configurationfile instead of hard-coding it into the application itself. Figure 3 contains asample mapping-configuration document that maps the different XML elements andattributes in Figure 1 to the Customers table fields. The crucial parts of thedocument are the dataSetColumn and sourceColumn attributes found on thecolumnMapping element. These nodes are used to create DataColumnMapping objectsdynamically.

          sourceTable="Customers">                  sourceColumn="CustomerID" />              sourceColumn="ContactName" />              sourceColumn="CompanyName" />              sourceColumn="ContactTitle" />              sourceColumn="Region" />              sourceColumn="PostalCode" />              sourceColumn="Country" />              sourceColumn="Phone" />              sourceColumn="Fax"/>    Figure 3. By creating an XML-mapping configurationfile, you can make any data-mapping program more flexible. The data shown heremaps the XML document in Figure 1 to the Northwind database's Customers table.   You can read the mapping information in Figure 3 inseveral ways. I used the API exposed by the XmlTextReader because it offers afast, forward-only stream. Given that the XML-mapping document is quite small,I easily could have used the XmlDocument or XPathNavigator classes. By usingthe XmlTextReader API, however, you don't have to be as concerned about futuredocument size because XmlTextReader is capable of reading large documentswithout tying up a lot of memory.   To create the DataTableMapping and associatedDataColumnMapping objects, walk through the XML-mapping document by calling theXmlTextReader's Read method. As tableMapping element nodes are found, a newinstance of the DataTableMapping class is created. Each columnMapping elementnode causes a DataColumnMapping instance to be created and associated with theDataTableMapping object. The attributes found on both elements are used tocreate the mapping between the source XML document and database-table fields.   For the sake of reusability, create a static method namedMapXmlToSource and add it to a class named MapXmlData. This method reads theXML-mapping file with the XmlTextReader (see Figure 4). The code found withinthis method adds each DataTableMapping object (more than one could be created,of course) into an ArrayList, which is returned from MapXmlToSource. Thereturned ArrayList can be enumerated through to access the individualDataTableMapping objects. Then, you can add each of these objects to aDataAdapter through its TableMappings property.   public static ArrayList MapXmlToSource(string  xmlMappingPath) {    ArrayList mappings =new ArrayList();    XmlTextReader reader =null;    DataTableMapping map =null;    //Read through tablemapping XML document    try {        reader = newXmlTextReader(xmlMappingPath);        while(reader.Read()) {            if(reader.NodeType == XmlNodeType.Element) {                //ReadtableMapping element                if(reader.Name == "tableMapping") {                    map =new DataTableMapping();                    reader.MoveToAttribute("dataSetTable");                    map.DataSetTable = reader.Value;                    reader.MoveToAttribute("sourceTable");                    map.SourceTable = reader.Value;                 reader.MoveToElement();                }                //ReadcolumnMapping element                if(reader.Name == "columnMapping") {                    DataColumnMapping colMapping =                      newDataColumnMapping();                     //Access mapping column attributes                    reader.MoveToAttribute("dataSetColumn");                    colMapping.DataSetColumn = reader.Value;                    reader.MoveToAttribute("sourceColumn");                    colMapping.SourceColumn = reader.Value;                    map.ColumnMappings.Add(colMapping);                    reader.MoveToElement();                }                       }            if(reader.NodeType == XmlNodeType.EndElement &&                reader.Name== "tableMapping") {                //AddDataTableMapping into collection                mappings.Add(map);            }        }    }    catch (Exception exp){        throw newException("Error reading mapping file: " +          exp.Message);    }    finally {        reader.Close();    }    return mappings;}Figure 4. XML-mapping information can be read quicklyand used to create DataTableMapping and DataColumnMapping classes by using theXmlTextReader class located in the System.Xml namespace.   Put it All Together Now that you've seen the different classes you can use tomap XML data to relational data, here's a step-by-step approach for putting theclasses together to move XML data into a database table. The code available inthis article's download uses the following steps to read the XML data into aDataSet and map it to the Customers table in the Northwind database.   First, load the source XML into a DataSet by calling theReadXml method. You can load the XML document from a local or remote locationusing this method. Next, create a SqlDataAdapter and call the MapXmlToSourcestatic method to get the ArrayList containing one or more DataTableMappingobjects. Then hook up the DataTableMapping(s) to the SqlDataAdapter through theTableMappings property. Next, create a SqlCommand object to be used as the dataadapter's InsertCommand. This command has several parameters that identify thesource-data columns in the DataSet. Although the downloadable code uses a SQLstatement for simplicity's sake, you certainly can use a stored procedure,which I recommend. Lastly, call the Data Adapter's Update method. Figure 5shows the code that performs these steps.   private void btnSubmit_Click(object sender,  System.EventArgs e) {    string connStr =       ConfigurationSettings.AppSettings["XMLforASPDSN"];    string xmlPath =Server.MapPath("XML/Customers.xml");    string xmlMappingPath=      Server.MapPath("XML/TableMappings.xml");    SqlConnection conn =null;      DataSet ds = newDataSet("Customers");    ds.ReadXml(xmlPath);    //Create DataAdapter    SqlDataAdapter da =new SqlDataAdapter();    //Get table/columnmappings from XML file    ArrayList mappings =      MapXmlData.MapXmlToSource(xmlMappingPath);    //Associate tablemappings with DataAdapter          foreach(DataTableMapping tableMap in mappings) {        da.TableMappings.Add(tableMap);    }    //Change to a storedproc in a "real" app    string insertSql =@"INSERT INTO Customers       (CustomerID,CompanyName,ContactName,ContactTitle,       Address,City,Region,PostalCode,Country,Phone,Fax)       VALUES(@CustomerID,@CompanyName,@ContactName,       @ContactTitle,@Address,@City,@Region,@PostalCode,       @Country,@Phone,@Fax)";    try {                  conn = new SqlConnection(connStr);        SqlCommand cmd =new SqlCommand(insertSql,conn);        cmd.Parameters.Add("@CustomerID",          SqlDbType.NChar,5,"CustomerID");        cmd.Parameters.Add("@CompanyName",          SqlDbType.NVarChar,40,"CompanyName");         cmd.Parameters.Add("@ContactName",          SqlDbType.NVarChar,30,"ContactName");        cmd.Parameters.Add("@ContactTitle",          SqlDbType.NVarChar,30,"ContactTitle");        cmd.Parameters.Add("@Address",          SqlDbType.NVarChar,60,"Address");        cmd.Parameters.Add("@City",          SqlDbType.NVarChar,15,"City");        cmd.Parameters.Add("@Region",          SqlDbType.NVarChar,15,"Region");        cmd.Parameters.Add("@PostalCode",          SqlDbType.NVarChar,10,"PostalCode");         cmd.Parameters.Add("@Country",          SqlDbType.NVarChar,15,"Country");        cmd.Parameters.Add("@Phone",          SqlDbType.NVarChar,24,"Phone");        cmd.Parameters.Add("@Fax",          SqlDbType.NVarChar,24,"Fax");        da.InsertCommand =cmd;        da.Update(ds,"Customers");    }    catch (Exception exp){        lblOutput.Text =exp.Message;    }    finally {        if (conn.State !=ConnectionState.Closed)          conn.Close();    }    lblOutput.Text ="XML data mapped to " +      "databasesuccessfully!";}Figure 5. Although several steps are required still,you can greatly simply mapping XML data to relational database-table fields byusing the DataTableMapping and DataColumnMapping classes.   Although the data-mapping solution I showed you in thisarticle is one of many different options, it's a viable solution especiallybecause you can rely on native ADO.NET classes to map data from point A topoint B. This is useful when you need to move XML documents into a database orother data source. With large XML documents, of course, you should runperformance tests because the DataSet does tie up memory. This type of mappingtechnique also can be useful when you need to synch up two or more distributeddatabase tables. I only wish I had a real-life equivalent to ensure I don't getlost when reading maps as I try to navigate to places I've never been. I guessit's time to invest in a GPS system.   The sample code in thisarticle is available for download.   Dan Wahlin is a Microsoft Most Valuable Professional inASP.NET and XML Web Services. He also is the president of Wahlin Consulting andfounder of the XML for ASP.NET Developers Web site (http://www.XMLforASP.NET), whichfocuses on using XML and Web Services in the .NET platform. He also is acorporate trainer and speaker, and he teaches XML and ASP.NET training coursesaround the United States. Dan co-authored Professional Windows DNA (Wrox) and ASP.NETTips, Tutorials & Code (Sams), and he wrote XML for ASP.NET Developers (Sams). E-mailDan at [email protected].   Tell us what you think! Please send any comments aboutthis article to [email protected] include the article title and author.