Reducing storage of strongly typed lists using InferTypedSchema
We were chatting the other day about storing collections of strongly typed data in a database and I mentioned how I once used the really simply DataSet method to store into session. It works storing into a database too.
There are lots of ways to skin this cat, but here's what I've done. The idea is to get your data into a DataSet, then use the built-in WriteXML to make your XML to store in the database column.
For example, here I have my data in a DataTable anyway so it's even easier:
private static string GetPropertiesXML(DataTable dt)
{
DataSet ds = dt.DataSet;
if (ds == null)
{
ds = new DataSet("SiteData");
ds.Tables.Add(dt);
}
StringWriter sw = new StringWriter();
ds.WriteXml(sw, XmlWriteMode.IgnoreSchema);
return sw.ToString();
}
And to read it back into the DataTable:
// move the XML from string into StringReader
StringReader sr = new StringReader(raw);
// create dataset
DataSet ds = new DataSet();
ds.ReadXml(sr, XmlReadMode.InferTypedSchema);
return ds.Tables[0];
The trick here that I wanted to mention is the use of InferTypedSchema. You can store the schema in the XML, but you end up with a lot of XML which although will perfectly round-trip with your strong types it is very likely to be too large to fit in your database!! I had a table with 1 row of 20 columns and it exceeded the 4000 byte limit for nvarchar.
So, using InferTypedSchema is very handy here. You don't need to write out a schema, and on the way back in it will create types inferred by the column values. So, if you had "hello" it would become a string, "false" would become a bool and "25" would become a byte.
There are a few gotchas if you use this method though, hinted at above. If you store an integer it will come back as the smallest integer type that the number will fit - and then it isn't an int type.
So when I use my int properties I use this method:
public int IntegerSetting(string key)
{
try
{
object o = this[key];
if (o is byte)
return (int)(byte)o;
if (o is short)
return (int)(short)o;
return (int)o;
}
catch
{
return 0;
}
}
For reference, the indexer you can see above simply accesses the aforementioned single row DataTable:
public object this[string key]
{
get { return dtProps.Rows[0][key]; }
set { dtProps.Rows[0][key] = value; }
}