Using UpdateGrams in .NET

Updategrams offer a different approach to inserting data from XML documents into the database. An annotated XML schema describes the mappings from the XML types in the schema to the tables in the database. We simply reference the schema when we execute the insert and in return we do not have to write a single line of code to handle the conversion. Take a look at the next three methods and see for yourself.

public static InsertOrderWithUpdategram( string xml,

  string connectionString, string schemaPath )

{

  ExecuteUpdategram( GetUpdategram( xml, null ),

    connectionString, schemaPath );

}

public static string GetUpdategram( string before, string after )

{

  const String UPDATEGRAM_NAMESPACE =

    "urn:schemas-microsoft-com:xml-updategram";

  const String MS_SQL_NAMESPACE =

    "urn:schemas-microsoft-com:xml-sql";

  StringWriter sw = new StringWriter();

  XmlTextWriter updateGramWriter = new XmlTextWriter(sw);

  updateGramWriter.WriteStartElement("ROOT");

  updateGramWriter.WriteAttributeString( "xmlns", "updg", null,

    UPDATEGRAM_NAMESPACE );

  updateGramWriter.WriteAttributeString( "xmlns", "sql", null,

    MS_SQL_NAMESPACE );

  updateGramWriter.WriteStartElement( "sync", UPDATEGRAM_NAMESPACE );

  updateGramWriter.WriteStartElement( "updg", "before",

    UPDATEGRAM_NAMESPACE );

  updateGramWriter.WriteRaw( before );

  updateGramWriter.WriteEndElement(); // before

  updateGramWriter.WriteStartElement( "after", UPDATEGRAM_NAMESPACE );

  updateGramWriter.WriteRaw( after );

  updateGramWriter.WriteEndElement(); // after

  updateGramWriter.WriteEndElement(); // sync

  updateGramWriter.WriteEndElement(); // ROOT

  updateGramWriter.Close();

  sw.Close();

  return sw.ToString();

}

public static void ExecuteUpdategram( string updategram,

  string connectionString, string schemaPath )

{

    SqlXmlCommand cmd = new SqlXmlCommand( connectionString );

  cmd.CommandText = updategram;

  cmd.CommandType = SqlXmlCommandType.UpdateGram;

  cmd.SchemaPath = schemaPath;

  cmd.ExecuteNonQuery();

}

There is not a single line in these three methods that is specific to the XML type we are inserting. We could just rename InsertOrderWithUpdategram() to InsertWithUpdategram() and run every insert and every update and delete through that single method. Now that is re-useable code! Moreover, keeping all the mapping definition in the schema makes a lot of sense, too, because you have to modify the schema if your XML type definitions change. Otherwise you could no longer validate incoming documents, regenerate the serialization classes or typed DataSets you are using in your application. Now you might as well keep your database mappings in there as well.

Now there has to be a catch somewhere, right? And of course there is: The performance of this approach is not quite as good as fully customized code, but it is within reasonable range. After all, SQLXML does cache the mapping schema after it first analyzed it and it does issue INSERT statements very similar to the ones we are creating by hand. SQLXML may only add some conversions, depending on the data types in the database and datatype annotations in the mapping schema.

The real catch, however, is the flexibility we lose when we switch to updategrams. The example above is so simple because we chose not to work with SQL Server’s IDENTITY columns. If we are trying to insert data from into a table with an identity column and need to reference the generated identity to insert child element then things get quite a bit more complicated as we will see in 15.3.4.4. For now let’s just note that updategrams are great for update and delete operations, but do require some extra work for certain inserts.