XmlSerializer to Serialize Class to Xml and Bulk Load Data

By Robbe D. Morris

Printer Friendly Version

Robbe Morris
Robbe & Melisa Morris
A web based analysis tool I've recently architected holds hundreds of data points client side in the browser from multiple tables.  These data points are stored, accessed, and altered through a series of rather complex arrays of arrays of arrays in JavaScript.  I think you get the picture.  The primary requirement we have with this tool is that if you change one data point, the math behind the analysis must filter its way up the hierarchy of data points, alter all of the analysis scores along the way, and display the results all without posting back to the server.  Thus, one small change can affect hundreds of different data points.
One of the challenges we had was being able to process a PagePostBack and save several hundred records across multiple tables within a reasonable amount of time.  Plus, as the tool obtained more and more users with the number of analysis models growing exponentionally, it wouldn't be long before the wait time for data updates became unacceptable.  We originally started out by following standard practices for populating classes, calling class methods with these classes as parameters which called stored procedures to save the data points one by one.  To enhance performance a bit, we shared an open connection across all of the method calls.  On larger models this resulted in waiting 15 seconds or so for the page to post to itself.  Not a horrible wait time but I wanted to do better.


In an attempt to cut our page post back time in half, I came up with the following methodology for bulk loading data classes to SQL Server.  It isn't a common approach so I figured I'd post it here for you to review.  The process is quite simple actually.  I created a small class with one method that all of my data classes can inherit.  That one method is called ToXml().  Its sole job is to return an Xml formatted string of its own class properties and their current values.  This enabled us to make creating an entire XmlDocument in string format of all of our records from their original data classes without having to write custom code to manually create the Xml strings.  After all of our classes were exported to a string, we could send the entire contents in one call to a SQL Server stored procedure.  This stored procedure would utilize the OPENXML functionality of SQL Server's Xml support to update all of the different records across multiple tables.  So rather than make several hundred calls to stored procedures, I could make one call.  In the end, I was able to accomplish my goal.  Our PagePostBack times were cut in half.
It is important to note that I am not advocating this methodology 100% of the time.  I realize saving several hundred data points across multiple tables all in one post back to the server is certainly not the norm.  However, if your application has similar requirements to the one I've outlined here, my solution is probably something you want to look at.
In the code samples below, you'll find the simple Serialization class that utilizes the XmlSerializer to serialize the class to an Xml formatted string.  You'll also see sample Xml output for these classes.  In addition, I've included a small sample of how you might populate the data classes and call the .ToXml() method.  Probably the most complex piece of the puzzle is the SQL Server utilization of the OPENXML functionality.  The syntax shown below demonstrates how to force SQL Server to process each group of XmlNodes, grab the designated primary key for each record, and update the individual record with the appropriate values.
Rather than have you strip out these few snippets of code from a more complex demonstration, I've just given you the absolute minimum as an example.  As I said, this isn't rocket science just a bit of an unorthodox approach to solve a problem.  My guess is that you can take it from here.
If you need more indepth coverage of SQL Server's OPENXML functionality, read this: Pass XML Instead of Arrays To SQL Server Procedure.  Another article of interest concerns dynamically populating your data classes from a DataTable at runtime using Custom Attributes on your classes.  Beats the heck out of hand coding all those class properties: Set Class Properties From DataTable Using Custom Attributes in ADO.NET.  As always, if you have any questions, please post them to our forums.
 
Serialization Class Source Code
using System;
using System.Xml.Serialization;
using System.Xml; 
using System.IO;
using System.Text;

namespace Custom
{
	 
   public class Serialization
   {
 
    public string ToXml()
    {

        StringWriter Output = new StringWriter(new StringBuilder());
        string Ret="";

        try
        {
            XmlSerializer s = new XmlSerializer(this.GetType());
            s.Serialize(Output,this);

            // To cut down on the size of the xml being sent to the database, we'll strip
            // out this extraneous xml.

            Ret = Output.ToString().Replace("xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\"","");
            Ret = Ret.Replace("xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\"",""); 
            Ret = Ret.Replace("<?xml version=\"1.0\" encoding=\"utf-16\"?>","").Trim();
        }
        catch (Exception) {  throw; }
            
        return Ret;
    }

  }
}
Sample Classes
 	
namespace Sample
{
   public class President : Custom.Serialization
   {
     public int PresidentID;
     public string FirstName;
     public string LastName;
   }

   public class FirstLady : Custom.Serialization
   {
     public int FirstLadyID;
     public int PresidentID;
     public string FirstName;
     public string LastName;
   }
}

Sample Code To Populate Class Properties With Data
using System;
using System.Text;

 private void SaveForm()
 {
        bool Ret=false;

       StringBuilder p = new StringBuilder();
       StringBuilder fl = new StringBuilder();
    
       try
       {
               // In a real world application, you would have iterated through the Request.Form
               // collection and appended numerous President and FirstLady records to the
               // Xml string.  I appended the root element to ensure that the top level node
               // is unique in the document.

              // Rather than clutter our code with more code to serialize the class to string,
              // we can just call the .ToXml() method on the class.

               p.Append("<root>");
               fl.Append("<root>");

               Sample.President  oPresident = new Sample.President();
               oPresident.PresidentID = 43;
               oPresident.FirstName = "George W.";
               oPresident.LastName = "Bush";
               p.Append(oPresident.ToXml());

               Sample.President  oPresident = new Sample.President();
               oPresident.PresidentID = 42;
               oPresident.FirstName = "Cigar";
               oPresident.LastName = "Aficionado";
               p.Append(oPresident.ToXml());

               Sample.President  oPresident = new Sample.President();
               oPresident.PresidentID = 41;
               oPresident.FirstName = "George H. W.";
               oPresident.LastName = "Bush";
               p.Append(oPresident.ToXml());

               Sample.President  oPresident = new Sample.President();
               oPresident.PresidentID = 40;
               oPresident.FirstName = "Ronald";
               oPresident.LastName = "Reagan";
               p.Append(oPresident.ToXml());
 
               Sample.FirstLady oFirstLady = new Sample.FirstLady();
               oFirstLady.FirstLadyID = 1;
               oFirstLady.PresidentID = 43;
               oFirstLady.FirstName = "Laura";
               oFirstLady.LastName = "Bush";
               fl.Append(oFirstLady.ToXml());

               Sample.FirstLady oFirstLady = new Sample.FirstLady();
               oFirstLady.FirstLadyID = 4;
               oFirstLady.PresidentID = 42;
               oFirstLady.FirstName = "The Root Of All";
               oFirstLady.LastName = "Evil";
               fl.Append(oFirstLady.ToXml());

               Sample.FirstLady oFirstLady = new Sample.FirstLady();
               oFirstLady.FirstLadyID = 2;
               oFirstLady.PresidentID = 41;
               oFirstLady.FirstName = "Barbara";
               oFirstLady.LastName = "Bush";
               fl.Append(oFirstLady.ToXml());

               Sample.FirstLady oFirstLady = new Sample.FirstLady();
               oFirstLady.FirstLadyID = 3;
               oFirstLady.PresidentID = 40;
               oFirstLady.FirstName = "One Class";
               oFirstLady.LastName = "Act";
               fl.Append(oFirstLady.ToXml());

               p.Append("</root>");
               fl.Append("</root>");
 
             Ret = SaveDataInBulk(p.ToString(),fl.ToString()); 
 
       }
       catch (Exception) { throw; }
       return Ret;
   }

Serialization Results In Xml Format

  Sample of one President class output from .ToXml()

<President>
  <PresidentID>43</PresidentID>
  <FirstName>George W.</FirstName>
  <LastName>Bush</LastName>
</President>

 Sample of two First Lady class outputs from .ToXml()

<FirstLady>
  <FirstLadyID>1</FirstLadyID>
  <PresidentID>43</PresidentID>
  <FirstName>Laura</FirstName>
  <LastName>Bush</LastName>
</FirstLady>
<FirstLady>
  <FirstLadyID>2</FirstLadyID>
  <PresidentID>41</PresidentID>
  <FirstName>Barbara</FirstName>
  <LastName>Bush</LastName>
</FirstLady>
 
		 
Use ADO.NET to call the SQL Server stored procedure.

        public bool SaveDataInBulk(string PresidentXml,string FirstLadyXml)
        {
  
            bool Ret = false;

            SqlConnection oConn = new SqlConnection();

            try
            {  
        
                oConn.ConnectionString = "SQL Server Connection String";
                oConn.Open();
				   
                SqlCommand oCmd = new SqlCommand("dbo.SaveDataInBulk",oConn);
        
                oCmd.CommandType=CommandType.StoredProcedure;
                oCmd.Parameters.Add(new SqlParameter("@PresidentXML",SqlDbType.NText, 1073741823));
                oCmd.Parameters.Add(new SqlParameter("@FirstLadyXML",SqlDbType.NText, 1073741823));
                oCmd.Parameters["@PresidentXML"].Value = PresidentXml;
                oCmd.Parameters["@FirstLadyXML"].Value = FirstLadyXml;
                oCmd.ExecuteNonQuery();
 		
                Ret = true;

            }
            catch (Exception) { throw;}
            finally { if (oConn.State == ConnectionState.Open) { oConn.Close(); } }
            return Ret;
        }

 
SQL Server Stored Procedure
 CREATE PROCEDURE dbo.SaveDataInBulk
(
    @PresidentXML ntext,
    @FirstLadyXML ntext
) 
AS

  declare @XmlHandle int
  declare @rc int

  select @rc=0

  begin transaction  SavePresidents

/*
    Use the OPENXML method to grab all President XmlNodes and
     map its child nodes to variables for use in our SQL.  I like to
     use the prefix "x" for these variables and name them the same
     as the property name from the .NET class.  This sample will
     update all of the presidents passed in by using the PresidentID
     as the unique key.  The update statement below this handles
     all of the first ladies.
*/

  EXEC sp_xml_preparedocument @XmlHandle output,@PresidentXML

   UPDATE President
      Set FirstName = xFirstName,
             LastName = xLastName
      FROM OPENXML (@XmlHandle, '/root/President',1) 
      WITH (xPresidentID int './PresidentID', 
                  xFirstName nvarchar(100) './FirstName', 
                  xLastName nvarchar(100) './LastName') 
    WHERE PresidentID = xPresidentID 

      if (@@ERROR <> 0) 
       BEGIN
         select @rc = @@Error   
         EXEC sp_xml_removedocument @XmlHandle
         Goto OnExit
       END              

  EXEC sp_xml_removedocument @XmlHandle

  EXEC sp_xml_preparedocument @XmlHandle output,@FirstLadyXML

   UPDATE FirstLady
      Set FirstName = xFirstName,
             LastName = xLastName,
             PresidentID = xPresidentID
      FROM OPENXML (@XmlHandle, '/root/FirstLady',1) 
      WITH (xFirstLadyID int './FirstLadyID', 
                  xPresidentID int './PresidentID',
                  xFirstName nvarchar(100) './FirstName', 
                  xLastName nvarchar(100) './LastName') 
    WHERE FirstLadyID = xFirstLadyID 

      if (@@ERROR <> 0) 
       BEGIN
         select @rc = @@Error   
         EXEC sp_xml_removedocument @XmlHandle
         Goto OnExit
       END              

  EXEC sp_xml_removedocument @XmlHandle

  

 OnExit:
 
 
     if (@rc = 0)
    BEGIN
       COMMIT TRANSACTION   SavePresidents
    END
   ELSE
    BEGIN
         ROLLBACK TRANSACTION  SavePresidents
    END
 
    RETURN @rc
GO


Robbe has been a Microsoft MVP in C# since 2004.  He is also the co-founder of NullSkull.com which provides .NET articles, book reviews, software reviews, and software download and purchase advice.