Persisting .Net Objects to Your Database

When working with 3rd party xml web services (UPS, stamps.com, Chase, etc) it's often valuable to record the exact xml request and response communication between the service and integration client (your app).  This information may be needed for auditing or debugging unexpected exceptions.

Since web services are xml based, SQL Server's xml datatype is the ideal solution to write the exact xml request and response values to.

If you're implementing the services using HttpWebRequest and HttpWebResponse calls, you already have the xml in-hand, and you simply need to write that to the database.  However, if you are using proxy objects as the interface to the service, you need to do a little extra work to get the data in the xml format needed.

This posting is specifically addressing .Net objects created by web service proxies, but the same functionality could be used for persisting any object instance to the database.

To create the xml from a .Net object, we can use XML serialization.  This simply means creating an xml representation of the object instance.

           
  StringBuilder sb = new StringBuilder();
  StringWriter sw = new StringWriter(sb);
  
  string xmlResponse;    
  XmlSerializer responseSerializer = new XmlSerializer(typeof(YourTypeToSerialize));
  responseSerializer.Serialize(sw, ObjectInstance);
  xmlResponse = sb.ToString();

Once you have the xml available, you're ready to write it to the database. Using ADO.Net, we can create a SQLCommand, and add the xml as an input parameter.

  Sql Command cmd = new SqlCommand("YourStoredProcedure", YourConnection);
  cmd.CommandType = CommandType.StoredProcedure;

  cmd.Parameters.AddWithValue("@xmlResponse", xmlResponse);
  cmd.ExecuteNonQuery();

Your stored procedure should accept a parameter of type xml, and write to a table with an xml data field.

Then, should you ever need to look at what was sent to or from the web service, you have the exact xml available.  Even better, if you are using the proxy objects, you can use XML serialization to deserialize the xml and rehydrate those objects.