Quickduck

Recently I showed you how to pull sql query result sets into object instances using the XmlReader and deserialization. This article aims to show you the opposite process - persisting an object instance into a database table using serialization and Sql Server xml parsing.

The same Channel database table and entity class will be used for all examples.

/// Represents a media channel (e.g. web, mobile, iphone, etc.)
public class Channel    {
  private int _id;
  private string _type, _description;

  public Channel(){}

  public Channel(string type, string description)  {
    _type = type;
    _description = description;
  }

  /// Get/Set the channel identifier
  public int Id {
    get { return _id; }
    set { _id = value; }
  }

  /// Get/Set the channel type (e.g. web, mobile, iphone, etc.)
  public string Type {
    get { return _type; }
    set { _type = value; }
  }

  /// Get/Set the description of the channel.
  public string Description {
    get { return _description; }
    set { _description = value; }
  }
}

The Stored Procedure

The sproc takes in an sql server xml datatype parameter. This is provided via a normal string representation of the xml. Within the SPROC you prepare the document for querying, pull out the values you require and perform the insert.

To understand the following SPROC, you need to have have a basic understanding of:

a) how your object instance xml serialization format looks

<?xml version="1.0" encoding="utf-16"?>
<Channel xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Id>0</Id>
  <Type>web</Type>
  <Description>800x600 or larger browsers</Description>
</Channel>'

and

b) an understanding of xpath and xquery for pulling the values out from the xml.

Check out the - Resources - Sql Xml - for some links that might help you out.

CREATE PROCEDURE [usp_PersistChannel]
	@xmldoc XML
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE
            @iDoc int,
            @type varchar(50),
            @description varchar(255)

    EXEC sp_xml_preparedocument
           @iDoc OUTPUT,
           @xmldoc,
            '‘

    SELECT
            @Type = [Type],
            @Description = [Description]
    FROM
            OPENXML (@iDoc, ‘/Channel’,3)
	WITH (  [Type] varchar(50) ‘./Type’,
	Description] varchar(255) ‘./Description’ )

    EXEC sp_xml_removedocument @idoc

    INSERT INTO Channel VALUES (@Type, @Description)                

    SELECT @@IDENTITY
END

Data Access Code

The following is a convenience method to help aid the serialization process of an object instance.

        /// Serialize an instance of an object to a string.
        public string Serialize(object instance)
        {
            StringBuilder sb = new StringBuilder();
            StringWriter sw = new StringWriter(sb);
            XmlSerializer serializer = new XmlSerializer(instance.GetType(), null, new Type[0], null, null);
            serializer.Serialize(sw, instance);
            return sb.ToString();
        }

Usage Examples:

        /// Persist a Channel.
        public void Persist(Channel channel)
        {
            channel.Id = Int32.Parse(
                                Database.ExecuteScalar(
                                   Database.GetStoredProcCommand("usp_PersistChannel", Serialize(channel))
                                ).ToString()
                             );
        }