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 A to 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'd surely get lost. (Even with them, occasionally I still get lost.) I'm not going to discuss roadmaps here, but I will explain how you can use ADO.NET classes to create "maps" that move data from point A to point B while avoiding data loss in the process. I'll show you how to create a flexible XML-configuration document that allows you to map XML data to relational table fields.
Mapping XML element and attribute data to relational table fields is a fairly common task in the world of e-commerce, and you have a choice of many different mapping products. If you require a custom solution, you can take different routes. One popular route is the eXtensible Stylesheet Language Transformation (XSLT) language, which can transform XML data into a structure compatible with one or more database tables. You can find a code sample at http://www.XMLforASP.NET/codeSection.aspx?csID=37 that uses this technique to migrate a Yahoo! Store XML document into a database. In addition to XSLT, you can use several other XML document-parsing and database-specific techniques to migrate XML data into a database. Rather than focus on these solutions, I'll demonstrate how you can use native ADO.NET classes, such as SqlDataAdapter and DataSet, along with a few helper classes to map data with minimal effort.
Use ADO.NET's Mapping Classes
The .NET platform provides a couple of built-in classes capable of generating data maps named DataTableMapping and DataColumnMapping. You can find these classes in the System.Data.Common namespace. By using these classes, you can map the XML document shown in Figure 1 to the respective fields in the Northwind database's Customers table (also shown in Figure 1) through the DataSet and DataAdapter classes.
Figure 1. Mapping XML element and attribute data to table fields can be
a challenging task depending on how you attempt it. Using ADO.NET mapping
classes can simplify this process greatly. If you haven't used DataSets to work
with XML before, you can use the DataSet class's ReadXml method to load XML.
Several XML element names shown in Figure 1 match up closely (aside from case) with the corresponding field names in the Customers table. But attributes such as id and elements such as company aren't as simple to match. The DataColumnMapping class can generate a mapping between these items after loading the XML document into a DataSet and creating an instance of a DataAdapter. This is accomplished by using one of the DataColumnMapping class's constructors or by assigning values to its SourceColumn and DataSetColumn properties. Here is the constructor signature:
public DataColumnMapping(
string sourceColumn,
string dataSetColumn
);
To map the XML document's company element to the Customers table's CompanyName field, you can use this code:
DataColumnMapping colMap =
new DataColumnMapping("CompanyName","company");
After you create the column-mapping object, you can associate it with a DataTableMapping class (used to map DataSet table names to database table names) through a property named ColumnMappings. Then, you can hook the DataTableMapping class to a DataAdapter through a property named TableMappings. The DataTableMapping class has a constructor similar to the DataColumnMapping class, except instead of accepting column names (as shown earlier) it accepts source table names and DataSet table names (see Figure 2).
//Create column mapping class
DataColumnMapping colMap =
new DataColumnMapping("CompanyName","company");
//Create table mapping class
DataTableMapping tableMap =
new DataTableMapping("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 associate a DataColumnMapping object with a DataTableMapping object. The different managed-provider DataAdapter objects (such as SqlDataAdapter) expose a TableMappings property that can accept DataTableMapping objects.
Although this approach makes mapping DataSet columns to database table fields fairly straightforward, the mapping information is hard-coded into the application's source code. If the schema for the XML document loaded into the DataSet never changes, there's no problem. But if the schema does change occasionally, you must recompile and deploy the code containing the mapping details.
Increase Flexibility
To make the process of mapping XML data to database fields more flexible, you can store the mapping information in an XML configuration file instead of hard-coding it into the application itself. Figure 3 contains a sample mapping-configuration document that maps the different XML elements and attributes in Figure 1 to the Customers table fields. The crucial parts of the document are the dataSetColumn and sourceColumn attributes found on the columnMapping element. These nodes are used to create DataColumnMapping objects dynamically.
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 configuration file, you can make any data-mapping program more flexible. The data shown here maps the XML document in Figure 1 to the Northwind database's Customers table.
You can read the mapping information in Figure 3 in several ways. I used the API exposed by the XmlTextReader because it offers a fast, forward-only stream. Given that the XML-mapping document is quite small, I easily could have used the XmlDocument or XPathNavigator classes. By using the XmlTextReader API, however, you don't have to be as concerned about future document size because XmlTextReader is capable of reading large documents without tying up a lot of memory.
To create the DataTableMapping and associated DataColumnMapping objects, walk through the XML-mapping document by calling the XmlTextReader's Read method. As tableMapping element nodes are found, a new instance of the DataTableMapping class is created. Each columnMapping element node causes a DataColumnMapping instance to be created and associated with the DataTableMapping object. The attributes found on both elements are used to create the mapping between the source XML document and database-table fields.
For the sake of reusability, create a static method named MapXmlToSource and add it to a class named MapXmlData. This method reads the XML-mapping file with the XmlTextReader (see Figure 4). The code found within this method adds each DataTableMapping object (more than one could be created, of course) into an ArrayList, which is returned from MapXmlToSource. The returned ArrayList can be enumerated through to access the individual DataTableMapping objects. Then, you can add each of these objects to a DataAdapter through its TableMappings property.
public static ArrayList MapXmlToSource(string
xmlMappingPath) {
ArrayList mappings = new ArrayList();
XmlTextReader reader = null;
DataTableMapping map = null;
//Read through table mapping XML document
try {
reader = new XmlTextReader(xmlMappingPath);
while (reader.Read()) {
if (reader.NodeType == XmlNodeType.Element) {
//Read tableMapping element
if (reader.Name == "tableMapping") {
map = new DataTableMapping();
reader.MoveToAttribute("dataSetTable");
map.DataSetTable = reader.Value;
reader.MoveToAttribute("sourceTable");
map.SourceTable = reader.Value;
reader.MoveToElement();
}
//Read columnMapping element
if (reader.Name == "columnMapping") {
DataColumnMapping colMapping =
new DataColumnMapping();
//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") {
//Add DataTableMapping into collection
mappings.Add(map);
}
}
}
catch (Exception exp) {
throw new Exception("Error reading mapping file: " +
exp.Message);
}
finally {
reader.Close();
}
return mappings;
}
Figure 4. XML-mapping information can be read quickly and used to create DataTableMapping and DataColumnMapping classes by using the XmlTextReader class located in the System.Xml namespace.
Put it All Together
Now that you've seen the different classes you can use to map XML data to relational data, here's a step-by-step approach for putting the classes together to move XML data into a database table. The code available in this article's download uses the following steps to read the XML data into a DataSet and map it to the Customers table in the Northwind database.
First, load the source XML into a DataSet by calling the ReadXml method. You can load the XML document from a local or remote location using this method. Next, create a SqlDataAdapter and call the MapXmlToSource static method to get the ArrayList containing one or more DataTableMapping objects. Then hook up the DataTableMapping(s) to the SqlDataAdapter through the TableMappings property. Next, create a SqlCommand object to be used as the data adapter's InsertCommand. This command has several parameters that identify the source-data columns in the DataSet. Although the downloadable code uses a SQL statement for simplicity's sake, you certainly can use a stored procedure, which I recommend. Lastly, call the Data Adapter's Update method. Figure 5 shows 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 = new DataSet("Customers");
ds.ReadXml(xmlPath);
//Create DataAdapter
SqlDataAdapter da = new SqlDataAdapter();
//Get table/column mappings from XML file
ArrayList mappings =
MapXmlData.MapXmlToSource(xmlMappingPath);
//Associate table mappings with DataAdapter
foreach (DataTableMapping tableMap in mappings) {
da.TableMappings.Add(tableMap);
}
//Change to a stored proc 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 " +
"database successfully!";
}
Figure 5. Although several steps are required still, you can greatly simply mapping XML data to relational database-table fields by using the DataTableMapping and DataColumnMapping classes.
Although the data-mapping solution I showed you in this article is one of many different options, it's a viable solution especially because you can rely on native ADO.NET classes to map data from point A to point B. This is useful when you need to move XML documents into a database or other data source. With large XML documents, of course, you should run performance tests because the DataSet does tie up memory. This type of mapping technique also can be useful when you need to synch up two or more distributed database tables. I only wish I had a real-life equivalent to ensure I don't get lost when reading maps as I try to navigate to places I've never been. I guess it's time to invest in a GPS system.
The sample code in this article is available for download.
Dan Wahlin is a Microsoft Most Valuable Professional in ASP.NET and XML Web Services. He also is the president of Wahlin Consulting and founder of the XML for ASP.NET Developers Web site (http://www.XMLforASP.NET), which focuses on using XML and Web Services in the .NET platform. He also is a corporate trainer and speaker, and he teaches XML and ASP.NET training courses around the United States. Dan co-authored Professional Windows DNA (Wrox) and ASP.NET Tips, Tutorials & Code (Sams), and he wrote XML for ASP.NET Developers (Sams). E-mail Dan at [email protected].
Tell us what you think! Please send any comments about this article to [email protected]. Please include the article title and author.
