Simple C# Model to SQL Server Stored Procedure Mapping using XML

October 4, 2018

When it comes to data access, I like to keep it simple. I prefer the database be exclusively responsible for database actions using stored procedures to execute queries and updates. I also like to avoid mapping lots of parameters in stored procedures. At other times, there’s a need for a way to send a list of something to either query or execute multiple inserts and updates. To accomplish these things, I’ve been using a pattern where a stored procedure implements a single XML parameter and parses the XML to facilitate various types of actions. I will demonstrate this pattern in the following demo.

In this demo, we will create a few classes: one to serialize the models into a XML string and another to make the connection to the database. We will need an accessible database to create a table and to create a few stored procedures. The stored procedures will accept a single XML parameter to manage the inserts and the updates. We will be using a C# unit test to run the example.

The C# Project Setup:

Open up Visual Studio and add a new project named “Common”. Change the Solution name to XmlDataAccessDemo and click OK.

Right-click the Common project and add a new class named CustomerModel with the following properties.

namespace Common.Models
{
    public class CustomerModel
    {
        public int CustomerId { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Salutation { get; set; }
    }
}

Right-click the Common project and add a new class named SqlConnectionFactory with the following code.

using System.Data;
using System.Data.SqlClient;

namespace Common.Factories
{
    public class SqlConnectionFactory
    {
        private readonly string _connectionString;

        public SqlConnectionFactory(string connectionString)
        {
            _connectionString = connectionString;
        }

        public SqlConnection Get()
        {
            var conn = new SqlConnection(_connectionString);
            if (conn.State == ConnectionState.Closed) conn.Open();
            return conn;
        }
    }
}

Right-click the Common project and add a new class named ModelXmlSerializerService with the following two methods. We will use this service to serialize a list of models into XML using the GetXml() method. For completeness, the class also includes another method called GetModel() that can be used to deserialize any XML back to into a C# model or list of models. This can be quite handy but will not be used in this demo.

using System.IO;
using System.Xml;
using System.Xml.Serialization;

namespace Common.Services
{
    public class ModelXmlSerializerService
    {
        public string GetXml<T>(T value)
        {
            var emptyNamepsaces = new XmlSerializerNamespaces(new[] { XmlQualifiedName.Empty });
            var serializer = new XmlSerializer(value.GetType());
            var settings = new XmlWriterSettings
            {
                Indent = true,
                OmitXmlDeclaration = true
            };

            using (var stream = new StringWriter())
            using (var writer = XmlWriter.Create(stream, settings))
            {
                serializer.Serialize(writer, value, emptyNamepsaces);
                return stream.ToString();
            }
        }

        public T GetModel<T>(string xml)
        {
            if (string.IsNullOrEmpty(xml))
            {
                return default(T);
            }

            var serializer = new XmlSerializer(typeof(T));
            var settings = new XmlReaderSettings();

            using (var textReader = new StringReader(xml))
            {
                using (var xmlReader = XmlReader.Create(textReader, settings))
                {
                    return (T)serializer.Deserialize(xmlReader);
                }
            }
        }
    }
}

SQL Server Table and Stored Procedure Setup:

In SQL Server create a table named Customer using the following script.

/****** Object:  Table [dbo].[Customer]    Script Date: 8/23/2018 1:40:43 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Customer](
	[CustomerId] [int] IDENTITY(1,1) NOT NULL,
	[FirstName] [varchar](50) NOT NULL,
	[LastName] [varchar](50) NOT NULL,
	[Salutation] [varchar](4) NOT NULL,
	[CreateDate] [datetime] NOT NULL,
	[ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Customer] ADD  CONSTRAINT [DF_Customer_CreateDate]  DEFAULT (getdate()) FOR [CreateDate]
GO

Now create three Stored Procedures (spInsertCustomer, spUpdateCustomer and spUpsertCustomer). Each stored procedure will accept an XML parameter that is an array of CustomerModel models.

/****** Object:  StoredProcedure [dbo].[spInsertCustomer] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[spInsertCustomer]
	@xml XML
AS
BEGIN
	SET NOCOUNT ON;

	INSERT INTO [dbo].[Customer]
          (     [FirstName]
		, [LastName]
		, [Salutation]
		, [ModifiedDate] )
	SELECT
		  x.r.value('FirstName[1]', 'varchar(50)')
		, x.r.value('LastName[1]', 'varchar(50)')
		, x.r.value('Salutation[1]', 'varchar(4)')
		, GetDate()
	FROM @xml.nodes('//ArrayOfCustomerModel/CustomerModel') as x(r)
	WHERE x.r.value('CustomerId[1]', 'int') = 0
END


/****** Object:  StoredProcedure [dbo].[spUpdateCustomer] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[spUpdateCustomer]
	@xml XML
AS
BEGIN
	SET NOCOUNT ON;

UPDATE c
SET    
    c.FirstName = x.FirstName
  , c.[LastName] = x.LastName
  , c.[Salutation] = x.Salutation
  , c.[ModifiedDate] = GetDate()
FROM Customer c
	JOIN (SELECT  
	  x.r.value('CustomerId[1]', 'int') AS CustomerId
	, x.r.value('FirstName[1]', 'varchar(50)') AS FirstName
	, x.r.value('LastName[1]', 'varchar(50)') AS LastName
	, x.r.value('Salutation[1]', 'varchar(4)') AS Salutation
FROM @xml.nodes('//ArrayOfCustomerModel/CustomerModel') as x(r)) x
	ON x.CustomerId = c.CustomerId

END

/****** Object:  StoredProcedure [dbo].[spUpsertCustomer]    ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[spUpsertCustomer]
	@xml XML
AS
BEGIN
	SET NOCOUNT ON;

exec spUpdateCustomer @xml
exec spInsertCustomer @xml


END

Back to the C# Project:

Create a unit test project to run the example. Right-click the solution and select Add >> New Project to add a Unit Test Project (.NET Framework) to the solution called IntegrationTests.

Add a reference to the Common project in the IntegrationTests project. Right-click the IntegrationTests project >> select Add >> Reference… On the Projects node, check the Common project and click OK.

This demo will be using Dapper for data access, but feel free to use any data access library of your choosing. To add the Dapper library to the project, right-click the IntegrationTests project and select Manage NuGet Packages…select the Browse tab and type “Dapper”. Select Dapper in the list and on the right pane click Install.

Rename the UnitTest1.cs test class to CustomerDataAccessTests.cs, rename TestMethod1 to Upsert_WorksCorrectly and add the following code to the method. Change the connection string to point to your database. The idea is to create a list of models. Use the ModelXmlSerializerService to serialize the list of models into XML, create a connection to the database and execute the spUpsertCustomer SQL Server stored procedure passing the XML to load the Customer table. This is not intended to be an actual unit test. It’s just being used here as a mode of running our example.

using System.Collections.Generic;
using System.Data;
using System.Linq;
using Common.Factories;
using Common.Models;
using Common.Services;
using Dapper;
using Microsoft.VisualStudio.TestTools.UnitTesting;

namespace IntegrationTests
{
    [TestClass]
    public class CustomerDataAccessTests
    {
        [TestMethod]
        public void Upsert_WorksCorrectly()
        {
            var models = new List<CustomerModel>()
            {
                new CustomerModel
                {
                    CustomerId = 0,
                    FirstName = "Isaac",
                    LastName = "Brock",
                    Salutation = "Mr."
                },
                new CustomerModel
                {
                    CustomerId = 0,
                    FirstName = "Courtney",
                    LastName = "Barnett",
                    Salutation = "Ms."
                }
            };

            var service = new ModelXmlSerializerService();
            var xml = service.GetXml(models);

            var connectionString = @"Server=demo;Initial Catalog=sandbox;Integrated Security=SSPI";

            var factory = new SqlConnectionFactory(connectionString);

            using (var connection = factory.Get())
            {
                var p = new DynamicParameters();
                {
                    p.Add("@xml", xml);
                    var query = connection.Query<bool>("spUpsertCustomer",
                        p, commandType: CommandType.StoredProcedure).FirstOrDefault();
                    var result = query;
                }
            }

        }
    }
}

Running the Test:

Now place your cursor into the Upsert_WorksCorrectly TestMethod and select the Visual Studio Test menu > Run >> All Tests or press keys + R + T.

Since both CustomerModel models have a CustomerID = 0, the spInsertCustomer proc will insert the customers into the Customer table. If the CustomerId were excluded the from the model entirely, the ModelXmlSerializerService would automatically assign a default CustomerId of 0, giving the same result.

The ModelXmlSerializerService takes the following list of CustomerModel models and outputs the resulting XML below with a root node named “ArrayOfCustomerModel” which the stored procedures uses.

       var models = new List<CustomerModel>()
            {
                new CustomerModel
                {
                    CustomerId = 0,
                    FirstName = "Isaac",
                    LastName = "Brock",
                    Salutation = "Mr."
                },
                new CustomerModel
                {
                    CustomerId = 0,
                    FirstName = "Courtney",
                    LastName = "Barnett",
                    Salutation = "Ms."
                }
            };

<ArrayOfCustomerModel>
  <CustomerModel>
    <CustomerId>0</CustomerId>
    <FirstName>Isaac</FirstName>
    <LastName>Brock</LastName>
    <Salutation>Mr.</Salutation>
  </CustomerModel>
  <CustomerModel>
    <CustomerId>0</CustomerId>
    <FirstName>Courtney</FirstName>
    <LastName>Barnett</LastName>
    <Salutation>Ms.</Salutation>
  </CustomerModel>
</ArrayOfCustomerModel>

If you run a select query on the Customer table (select * from Customer), you should see two customers added.

Now change the code to execute an update by changing the CustomerIds in the models from 0 to 1 and 2, matching the CustomerIds in the database. Also change the model properties to something different (like shown below) and rerun the test.

            var models = new List<CustomerModel>()
            {
                new CustomerModel
                {
                    CustomerId = 1,
                    FirstName = "Richie",
                    LastName = "Cunningham",
                    Salutation = "Mr."
                },
                new CustomerModel
                {
                    CustomerId = 2,
                    FirstName = "Oprah",
                    LastName = "Winfrey",
                    Salutation = "Ms."
                }
            };

Rerun the select query on the Customer table to see the updated result matches the changes in the models.

That will conclude our demo of a Simple C# Model to SQL Server Stored Procedure Mapping using XML. This pattern of parsing XML in SQL can come in handy for many other uses. I have introduced it to a few other developers and DBAs who have continued to incorporate it in many valuable ways. I hope this has been informative and useful!

Tags: