Mark Wilson I am the creator of TopXML. I am available for international and local (Australia) contracts. I am a Solution Architect/Business Analyst. I have worked in IT in several countries (NZ, Australia, South Africa, UK) building and training teams for government and very large non-governmental organizations. I am ex-Microsoft Consulting Services. I wrote the first book on Microsoft XML published in 2000 called XML Programming with VB and ASP. Most recently I have been building tools for the SEO industry. Ask me for a 37 point SEO health-checkup for your website.
First posted :
03/24/2008
Times viewed :
251
Using SqlXmlAdapter in .NET
The last technique we examine in more detail is the insert (and update)
capability of the DataSet in combination with the SqlXmlAdapter. Of all the
insert techniques this one requires by far the least amount of code:
public static InsertWithDataSet( string xml, string connectionString,
string schemaPath )
{
DataSet ds = new DataSet();
SqlXmlCommand cmd = new SqlXmlCommand( connectionString );
cmd.SchemaPath = schemaPath;
SqlXmlAdapter adapter = new SqlXmlAdapter( cmd );
ds.ReadXml( xml );
adapter.Update(ds);
}
The code to let a DataSet object and a SqlXmlAdapter object collaborate to
insert data into SQL Server is very re-usable, since it does not require any
XML specific code. Also, the DataSet has built-in support to process more than
root object at a time. Again the mappings from XML to the database are defined
in a mapping schema so we do not have to write and maintain any code to parse
XML and compose a SQL statement. The SqlXmlAdapter() calls get GetChanges() on
the passed in DataSet to get one of those DiffGrams which it then hands off to
the SqlXmlCommand for execution. The SqlXmlCommand then transforms the DiffGram
into an Updategram and transforms that into SQL. Now you can already guess that
this doesn’t perform as well as directly composing an updategram with the extra
transformation step in the chain of execution.
Another disadvantage is that we now have the three representations of the
data in the XML document in memory: The original document, the DataSet object
and the DiffGram, but the real show stopper is that you cannot insert nested
XML types into the database if children require a foreign key from an
automatically generated IDENTITY. The DiffGram does not know how to propagate
the parent’s IDENTITY to the children and the complete insert fails. In SQLXML
release 3.0 there is no work-around for this problem. The only way to avoid
this problem is to not handle those types of inserts from a DataSet.
SQLXML offers one more technique to insert data into SQL Server: XML Bulk
Load. I want to mention it here because it has some nice features that are not
available through any other upload technique: Fully transacted upload, for
example, and automatic table generation if a table referenced in the source
document is not present in the database. We’re not going into detail here
because it is not accessible through a managed .NET class, only through COM
interop.
As you can derive from the name, the XML Bulk Load COM component is designed
for loading large feeds of XML data. In contrast to all the other techniques we
examined so far, BulkLoad does not require loading the complete source document
into memory. It also relies on an annotated schema to map the XML to the
database, i.e. we can build a very generic component to handle bulk uploads. On
the negative side Bulk Load also cannot handle nested types where children
require the IDENTITY of the parent as a foreign key.
