Serializing and storing lightweight objects to SQL Server 2005
The other day there was a posting in the SqlServer.XML newsgroup asking about Serialization. Here's an example doing this the simple (e.g., most trivial) way. I'm not going to add a lot of other text to this, we'll see if the code really does speak for itself. We'll start with a SQL Script that sets up the table and some procedures to use. (SETUP.SQL)
/* This code cleans up and sets up the database for our application to use. */
-- use some database that you don't really care about.
use scratch
go
-- cleanup, don't worry if these generate errors, they should the first time
drop procedure dbo.member_insert
drop procedure dbo.member_get
drop index xpMembers on dbo.members
drop table dbo.members
go
drop xml schema collection memberSchema
go
-- Create a schema that describes and can
-- be used to validate the serialized XML.
create xml schema collection memberSchema as
'http://www.w3.org/2001/XMLSchema-instance" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xsd="http://www.w3.org/2001/XMLSchema" attributeFormDefault="unqualified" elementFormDefault="qualified">'
go
-- create a table for holding the serialized instances
create table dbo.members(pkid tinyint identity(1,1) primary key,member xml(memberSchema))
go
-- Put a primary XML index on that table to make XQuerys against the serialized instance faster
create primary xml index xpMembers on dbo.members(member)
go
-- create a stored procedure that returns a serialized instance by its primary key value
create procedure dbo.member_get(@PKID tinyint) as select member from dbo.members where PKID = @PKID
go
-- create a procedure for inserting the serialized instance into the database
create procedure dbo.member_insert(@member xml(memberSchema)) as begin
-- supress affect record count
set nocount on
-- gracefully deal with exceptions...
begin try
-- start a transaction that gives us a clean rollback if this insert fails
begin tran
-- create a table variable that we can use simply capture the primary key value
-- for a just inserted data in a reliable fashion.
declare @i table(pkid tinyint)
-- insert the serialized instance into the database and then capture it's primary key value into @i
insert into dbo.members(member)
output inserted.pkid into @i
values (@member)
-- update the serialized instance inserting the primary key value into it.
-- I'm doing this to prove a point: storing a serialized instance to SQL Server as XML
-- means that the data or even the serialized instance could be modified outside of the
-- control of an application or system unless you're careful with the table and procedure
-- permissions. We can limit the potential damage here by forcing the XML instance to
-- comply with a schema (which we've done here.)
update dbo.members
set member.modify('replace value of (/ExampleMember/PKID)[1] with sql:column("PKID")')
where pkid = (select pkid from @i)
-- save this work
commit
end try
begin catch
-- okay, so some error occured. rollback the current work and report the error.
rollback
declare @en int
declare @em nvarchar(255)
declare @es int
set @en = error_number()
set @em = error_message()
set @es = error_severity()
raisError(@em,@es,1,@en)
end catch
end
go
The objective of the newsgroup post that brought all this up was a simple question: can one XML serialize a TextBox to SQL Server? The direct answer is no, but you could use a simple class like this to hold the datum entered in that TextBox... (EXAMPLEMEMBER.CS)
using System;
namespace SerialzeTextBox
{
public class ExampleMember {
/* Note that we'd probably not want to do this in most
* cases since we wouldn't want the application to be
* able to change a primary key value. However, due to
* limitations in the XML serialize class, you need to
* have every field you want to serialize and deserialize
* exposed at a public level (you could use properties, too).
* If you don't want public exposure of these values, you'll
* need to dig into reflection and rolling your own serializer.
* That's out of scope for what I want to show here. */
public string Name = null;
public byte PKID = byte.MinValue;
/* Here's another limitation of the standard XML
* serializer we need to deal with: the class must have
* a non-parameterized constructor. */
public ExampleMember() {}
public ExampleMember(string name) { Name = name; }
}
}
We also need a something that does the serialization and deserialization work. I already have one that works on a fairly generic basis. (LITESERIALIZATION.CS)
using System;
using System.Xml.Serialization;
using System.IO;
using System.Text;
namespace SerialzeTextBox
{
public static class LiteSerializer
{
public static string Serialize(object Member) {
// create a working space to use
using (MemoryStream b = new MemoryStream()) {
// Create a serializer for the passed-in type
XmlSerializer xs = new
XmlSerializer(Member.GetType());
// serialize the object into the working space
xs.Serialize(b, Member);
// return the lexical xml for the serialized instance
return Encoding.UTF8.GetString(b.ToArray());
}
}
public static object Deserialize(string Serialized,
Type ReturnType) {
// create a working space filled with the bytes
// of the serialized instance.
using (MemoryStream b =
new MemoryStream(Encoding.UTF8.GetBytes(Serialized))) {
// create a deserializer
XmlSerializer xs = new XmlSerializer(ReturnType);
// return the deserialized instance
return xs.Deserialize(b);
}
}
}
}
All that's really left is a driver application. Fire up Visual Studio and start a C# Windows Form project. Drag a TextBox control, three Label controls and two button controls onto the form. The first Label should be prompt for the Textbox. The other two Label's should align under the TextBox -- we'll use them to caption and show the inserted Primary Key value. Here's the guts for Form1.cs
using System;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace SerialzeTextBox
{
public partial class Form1 : Form {
// Remember to update this string to point at your server and database of preference.
private const string CONNSTR = "Data Source=.;Initial Catalog=scratch;Integrated Security=True";
public Form1() {
InitializeComponent();
// set up the form with something other default values...
this.Text = "Serialize to database";
label1.Text = "Member Name:";
label2.Text = "PKID:";
label3.Text = "Not Assigned";
button1.Text = "Save";
button2.Text = "Restore";
}
// wire-up to the "save" button.
private void button1_Click(object sender, EventArgs e) {
// create an instance of the ExampleMember class
// with the value from the TextBox control.
ExampleMember m = new ExampleMember(textBox1.Text);
// Get a connection to the database and then setup
// a command to execute our insert.
SqlConnection conn = new SqlConnection(CONNSTR);
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "dbo.member_insert";
cmd.CommandType = CommandType.StoredProcedure;
// Set the parameter value with the XML serialization
// of the object we want to serialize...
cmd.Parameters.AddWithValue("@member",
LiteSerializer.Serialize(m));
// Connect and execute the command. We're done.
conn.Open();
using (conn)
using (cmd)
cmd.ExecuteNonQuery();
label3.Text = "Unknown, click restore";
textBox1.Text = string.Empty;
}
private void button2_Click(object sender, EventArgs e) {
ExampleMember m;
// Get a connection to the database and then setup
// a command to execute our select.
SqlConnection conn = new SqlConnection(CONNSTR);
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "dbo.member_get";
cmd.CommandType = CommandType.StoredProcedure;
// Set a parameter indicating we want the first
// serialized into the database. You can be more
// creative than this, it is just an example...
cmd.Parameters.AddWithValue("@PKID", 1);
// Connect and execute...
conn.Open();
using (conn)
using (cmd) {
// Get the serialized value from the database
// using the executeScalar, then deserialize that
// into an object.
m = (ExampleMember)
(LiteSerializer.Deserialize(
(string)(cmd.ExecuteScalar())
, typeof(ExampleMember)));
}
// Update the form showing the deserialized values
textBox1.Text = m.Name;
label3.Text = m.PKID.ToString();
}
}
}
If you're interested in the whole project, you can download it here.