Using XML Serialization with C# and SQL Server
Introduction
Serialization is the process of converting an object into a stream of bytes in order to store the object or transmit it to memory, a database, or a file. Its main purpose is to save the state of an object in order to be able to recreate it when needed. [MSDN]
The reverse process is called deserialization.
While Serialization is mostly known for data transmission, usually to web services, there are situations where serialized objects needs to be passed to SQL server to be processed and saved.
In this article, 2 scenarios where XML Serialization can be used togather with SQL server shall be demonstrated.
XML Serialization
XML serialization serializes the public fields and properties of an object, or the parameters and return values of methods, into an XML stream that conforms to a specific XML Schema definition language (XSD) document. [MSDN]
Serialization
The first step is to define a custom class decorated with the Serializable attribute.
[Serializable]
public class person
{
public string name { get; set; }
public string surname { get; set; }
public string country { get; set; }
}
Next is a generic method that serialize any object to XML string.
The method takes an object of type <T> and returns a serialized XML string.
public static String ObjectToXMLGeneric<T>(T filter)
{
string xml = null;
using (StringWriter sw = new StringWriter())
{
XmlSerializer xs = new XmlSerializer(typeof(T));
xs.Serialize(sw, filter);
try
{
xml = sw.ToString();
}
catch (Exception e)
{
throw e;
}
}
return xml;
}
person p = new person();
p.name = "Chervine";
p.surname = "Bhiwoo";
p.country = "Mauritius";
var xmlperson = Utils.ObjectToXMLGeneric<person>(p);
The above operation will return an XML like below
<?xml version="1.0" encoding="utf-16"?>
<person xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<name>Chervine</name>
<surname>Bhiwoo</surname>
<country>Mauritius</country>
</person>
Moreover, complex types can also be serialized as shown below:
person p = new person { name = "Chervine", surname = "Bhiwoo", country = "Mauritius" };
person p1 = new person { name = "a", surname = "a", country = "Mauritius" };
List<person> persons = new List<person>();
persons.Add(p);
persons.Add(p1);
var xmlperson = Utils.ObjectToXMLGeneric<List<person>>(persons);
The above operation will return an XML like below :
<?xml version="1.0" encoding="utf-16"?>
<ArrayOfPerson xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<person>
<name>Chervine</name>
<surname>Bhiwoo</surname>
<country>Mauritius</country>
</person>
<person>
<name>a</name>
<surname>a</surname>
<country>Mauritius</country>
</person>
</ArrayOfPerson>
Deserialization
Just like for Serialization, below is a generic method that takes an XML string as parameter and returns its corresponding object.
public static T XMLToObject<T>(string xml)
{
var serializer = new XmlSerializer(typeof(T));
using (var textReader = new StringReader(xml))
{
using (var xmlReader = XmlReader.Create(textReader))
{
return (T)serializer.Deserialize(xmlReader);
}
}
}
persons = Utils.XMLToObject < List<person>>(xmlperson);
Scenario 1: Saving serialized XML as an XML Object in SQL Server
In this scenario, the user could create a custom filter based on several parameters. Please find some background about the problem below.
The requirements was to allow the user to save the custom filters, so that, he just selects the filter from a list to filter information throughout the application.
*Applying the filter was easily achieved using LINQ. The challenge was to save the filter as each user could save multiple filters each having several conditions.
To save the filter, one of the solution was to serialize the filter object and save it directly in the database. When needed, the filter is extracted from the database, deserialized and applied using LINQ.*
The example below is a fictitious representation of the real problem encountered.
The goal is to serialize an object to XML and save it in the database.
Defining the table and stored procedure
In the table below, the serialized XML will be stored in the column "filters" which is of type "XML".
CREATE TABLE [dbo].[tbl_filters](
[FilterId] [int] IDENTITY(1,1) NOT NULL,
[UserID] [varchar](20) NULL,
[FilterDescription] [varchar](50) NULL,
[Filters] [xml] NULL,
PRIMARY KEY CLUSTERED
(
[FilterId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
GO
A stored Procedure has been used to do the insert. Here also, the parameter "filters" is of type "XML".
CREATE PROCEDURE [dbo].[add_filter]
-- Add the parameters for the stored procedure here
@user_id varchar(20),
@filter_description varchar(50),
@filters XML
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
INSERT INTO [dbo].[tbl_filters]
(
[UserID]
,[FilterDescription]
,[Filters]
)
VALUES
(@user_id,@filter_description ,@filters);
END
GO
The CustomFilter class has some description about a filter, and also several filters that could be applied in the application.
[Serializable]
public class CustomFilter
{
public int FilterID { get; set; }
public string UserID { get; set; }
public string Description { get; set; }
public List<String> Type { get; set; }
public List<String> Category { get; set; }
public List<String> Region { get; set; }
public List<String> Branch { get; set; }
public override bool Equals(object obj)
{
if (obj == null)
return false;
var t = obj as CustomFilter;
if (t == null)
return false;
if (FilterID == t.FilterID)
return true;
return false;
}
public override int GetHashCode()
{
int hash = 13;
hash += (hash * 31) + FilterID.GetHashCode();
return hash;
}
}
Create a method to pass XML parameters to the stored procedure
To pass XML parameters using SqlCommand, the parameter should be of type "SqlDbType.Xml".
The CreateFilter method serializes the object and passes it to the stored procedure.
public void CreateFilter(CustomFilter filter)
{
var FilterXML = Utils.ObjectToXMLGeneric<CustomFilter>(filter);
DBUtil db = new DBUtil();
SqlCommand cmd = new SqlCommand("add_filter");
cmd.Parameters.Add("@user_id", SqlDbType.VarChar).Value = filter.UserID;
cmd.Parameters.Add("@filter_description", SqlDbType.VarChar).Value = filter.Description;
cmd.Parameters.Add("@filters", SqlDbType.Xml).Value = FilterXML;
db.DBConnect();
var result = db.XmlInsertUpdate(cmd);
db.DBDisconnect();
}
The XmlInsertUpdate Method, takes the SqlCommand and execute the stored procedure.
public Boolean XmlInsertUpdate(SqlCommand cmd)
{
try
{
using (SqlConnection con = SQlConn)
{
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.ExecuteNonQuery();
}
}
catch (Exception e)
{
throw e;
}
return true;
}
Create a filter
The codes below creates a new CustomFilter object and pass it to the CreateFilter method discussed above.
CustomFilter f1 = new CustomFilter();
f1.UserID = "Chervine";
f1.Description = " Testing ";
f1.Branch = new List<string> { "Rose Belle", "Mahebourg" };
f1.Category = new List<string> { "Small", "Medium" };
f1.Region = new List<string> { "South" };
f1.Type = new List<string> { "Mass" };
CreateFilter(f1);
Below is how the object is saved in the database.
Retrieving all the filters from the database
The "GetAllFilters()" method, selects the records from the table, convert the serialized XML to objects again and returns a list of CustomFilter.
public List<CustomFilter> GetAllFilters()
{
string sql = "select FilterId,UserID,FilterDescription,Filters from tbl_filters";
DBUtil db = new DBUtil();
List<CustomFilter> filters = new List<CustomFilter>();
db.DBConnect();
SqlDataReader myReader = db.FetchData(sql);
while (myReader.Read())
{
var sFilter = myReader["filters"].ToString();
CustomFilter filter = Utils.XMLToObject<CustomFilter>(sFilter);
filters.Add(filter);
}
db.DBDisconnect();
return filters;
}
Scenario 2: Passing serialized XML to SQL Server and perform by selecting from XML Object in Stored Procedure
In this scenario, the user has a timesheet containing several tasks.
Instead of making several database calls to save each task, the complete timesheet object was serialized and passed to a stored procedure where the insert was performed.
In the stored procedure, a select is made in the XML object to get the required information which is saved in a specific table based on its type.
The model
A timesheet has a list of task for each day.
[Serializable]
public class Timesheet
{
public DateTime TimesheetDate { get; set; }
public List<Task> Tasks;
public Timesheet()
{
Tasks = new List<Task>();
}
public override bool Equals(object obj)
{
if (obj == null)
return false;
var t = obj as Timesheet;
if (t == null)
return false;
if (TimesheetDate == t.TimesheetDate)
return true;
return false;
}
public override int GetHashCode()
{
int hash = 13;
hash += (hash * 31) + TimesheetDate.GetHashCode();
return hash;
}
}
[Serializable]
public class Task
{
public int TaskID { get; set; }
public string TaskDescription { get; set; }
public string TaskType { get; set; }
public DateTime StartTime { get; set; }
public DateTime EndTime { get; set; }
public override bool Equals(object obj)
{
if (obj == null)
return false;
var t = obj as Task;
if (t == null)
return false;
if (TaskID == t.TaskID)
return true;
return false;
}
public override int GetHashCode()
{
int hash = 13;
hash += (hash * 31) + Task.GetHashCode();
return hash;
}
}
Creating and saving a timesheet
List<Task> tasks = new List<Task>();
tasks.Add(new Task { TaskType="Software Development", TaskDescription = "Analysis", StartTime = DateTime.Parse("23-10-2014 10:00:00"), EndTime = DateTime.Parse("23-10-2014 11:00:00") });
tasks.Add(new Task { TaskType = "Software Development", TaskDescription = "Coding", StartTime = DateTime.Parse("23-10-2014 11:00:00"), EndTime = DateTime.Parse("23-10-2014 13:00:00") });
tasks.Add(new Task { TaskType = "Software Development", TaskDescription = "Unit Test", StartTime = DateTime.Parse("23-10-2014 13:00:00"), EndTime = DateTime.Parse("23-10-2014 14:00:00") });
tasks.Add(new Task { TaskType = "Administrative", TaskDescription = "Meeting", StartTime = DateTime.Parse("23-10-2014 14:00:00"), EndTime = DateTime.Parse("23-10-2014 15:00:00") });
Timesheet DailyTimesheet = new Timesheet { TimesheetDate = DateTime.Today, Tasks = tasks };
CreateTimesheet(DailyTimesheet);
The CreateTimesheet method takes a timesheet object as parameter, serialize the object and pass it to the procedure "feed_timesheet".
public void CreateTimesheet(Timesheet timesheet)
{
var TimesheetXML = Utils.ObjectToXMLGeneric<Timesheet>(timesheet);
DBUtil db = new DBUtil();
SqlCommand cmd = new SqlCommand("feed_timesheet");
cmd.Parameters.Add("@Timesheet", SqlDbType.Xml).Value = TimesheetXML;
db.DBConnect();
var result = db.XmlInsertUpdate(cmd);
db.DBDisconnect();
}
Selecting from the XML and performing insert from Stored Procedure in SQL Server
From the procedure, the information must be filtered into "productive" and "non-productive" which goes into different tables.
The information is then inserted in the tables by selecting directly from the XML Object.
CREATE PROCEDURE [dbo].[feed_timesheet]
@Timesheet XML
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @TimesheetDate varchar(10);
SET @TimesheetDate = cast(@Timesheet.query('data(Timesheet/TimesheetDate)') as varchar);
INSERT INTO [dbo].[tbl_timesheet_productive]
(
[TimesheetDate],
[TaskDescription],
[StartTime],
[EndTime]
)
SELECT
@TimesheetDate,
cast(colx.query('data(TaskDescription) ') as varchar) as description,
cast(colx.query('data(StartTime) ') as varchar) as starttime,
cast(colx.query('data(EndTime) ') as varchar) as endtime
FROM @Timesheet.nodes('Timesheet/Tasks/Task') AS Tabx(Colx)
WHERE cast(colx.query('data(TaskType) ') as varchar ) = 'Software Development';
INSERT INTO [dbo].[tbl_timesheet_nonproductive]
(
[TimesheetDate],
[TaskDescription],
[StartTime],
[EndTime]
)
SELECT
@TimesheetDate,
cast(colx.query('data(TaskDescription) ') as varchar) as description,
cast(colx.query('data(StartTime) ') as varchar) as starttime,
cast(colx.query('data(EndTime) ') as varchar) as endtime
FROM @Timesheet.nodes('Timesheet/Tasks/Task') AS Tabx(Colx)
WHERE cast(colx.query('data(TaskType) ') as varchar ) = 'Administrative';
END
GO