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.
Right-click the Common project and add a new class named SqlConnectionFactory with the following code.
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.
SQL Server Table and Stored Procedure Setup:
In SQL Server create a table named Customer using the following script.
Now create three Stored Procedures (spInsertCustomer, spUpdateCustomer and spUpsertCustomer). Each stored procedure will accept an XML parameter that is an array of CustomerModel models.
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.
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.
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.
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!