I have been recently working on a pet application that downloads stock quotes and a bunch of fundamental data from Yahoo! Finance and puts the data into a nice little domain model. After some development I got tired of downloading the data with each run of the application. It really made testing and playing with the app a little bit annoying. Since this is a totally personal project carried out just for the purpose of good fun and exploration of market data available on the Web, I haven't paid much attention to design patterns, layers and other enterprisey stuff. Somewhere in the middle of development it turned obvious that I had to add persistence to an application that was not designed up-front and not prepared for adding such a functionality.
What saved me was a pretty well developed domain model with lots of small and well defined classes representing quotes, dividends, splits, key ratios and other pieces of the stock exchange mini-world. After a little bit of investigation I managed to change my application so that it had a single root object called Universe that held references to every other single domain object and was a perfect candidate for serialization. Then all I had to do to make my domain model ready to be persisted to a database was to decorate each of the domain classes with the [Serializable] attribute.
The next step was obviously to design the database schema. As the only thing that I was going to store in the database were big chunks of binary data (my serialized graphs of domain objects) that had some sort of identifier. The first idea that came to my mind was:
CREATE TABLE [Objects] (
[ID] VARCHAR(50) PRIMARY KEY,
[Bytes] VARBINARY(MAX) NOT NULL
);
and it turned out to satisfy all my needs.
Now all I needed was a database gateway that would enable me to save objects to the database, read and delete them. Reading objects from the database turned out to be a two step sequence of an embarrassingly simple SELECT statement and very rudimentary deserialization:
public static T GetObject<T>(string id) {
const string SqlStatement = "SELECT [Bytes] FROM [Objects] WHERE [ID] = @id";
T ret = default(T);
byte[] bytes = null;
// Read bytes from the database.
using (SqlConnection conn = new SqlConnection(ConnectionString)) {
conn.Open();
SqlCommand cmd = new SqlCommand(SqlStatement, conn);
cmd.Parameters.AddWithValue("id", id);
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
if (reader.Read()) {
bytes = reader.GetSqlBinary(0).Value;
}
}
// Deserialize read bytes.
if (bytes != null && bytes.Length > 0) {
IFormatter formatter = new BinaryFormatter();
using (MemoryStream s = new MemoryStream()) {
s.Write(bytes, 0, bytes.Length);
s.Seek(0, SeekOrigin.Begin);
ret = (T)formatter.Deserialize(s);
}
}
return ret;
}
Deleting objects from the database was even simpler:
public static void DeleteObject(string id) {
const string SqlStatement = "DELETE FROM [Objects] WHERE [ID] = @id";
using (SqlConnection conn = new SqlConnection(ConnectionString)) {
conn.Open();
SqlCommand cmd = new SqlCommand(SqlStatement, conn);
cmd.Parameters.AddWithValue("id", id);
cmd.ExecuteNonQuery();
}
}
Saving objects involved two steps: deleting the old version of an object (in case we were overwriting some old version of the object graph) and then inserting new BLOB to the database. Here goes the last snippet:
public static void SaveObject<T>(string id, T obj) {
const string SqlDeleteStatement = "DELETE FROM [Objects] WHERE [ID] = @id";
const string SqlInsertStatement = "INSERT INTO [Objects]([ID], [Bytes]) VALUES (@id, @bytes)";
byte[] bytes = null;
// Serialize the object to bytes.
IFormatter formatter = new BinaryFormatter();
using (Stream s = new MemoryStream()) {
formatter.Serialize(s, obj);
bytes = new byte[s.Length];
s.Seek(0, SeekOrigin.Begin);
s.Read(bytes, 0, Convert.ToInt32(s.Length));
}
// Delete any previously existing object with the same id
// and save the bytes to the database. Execute both
// statements in one transaction.
using (SqlConnection conn = new SqlConnection(ConnectionString)) {
conn.Open();
SqlTransaction trans = conn.BeginTransaction(IsolationLevel.Serializable);
try {
SqlCommand delCmd = new SqlCommand(SqlDeleteStatement, conn, trans);
delCmd.Parameters.AddWithValue("id", id);
SqlCommand insCmd = new SqlCommand(SqlInsertStatement, conn, trans);
insCmd.Parameters.AddWithValue("id", id);
insCmd.Parameters.AddWithValue("bytes", new SqlBinary(bytes));
delCmd.ExecuteNonQuery();
insCmd.ExecuteNonQuery();
trans.Commit();
} catch {
trans.Rollback();
throw;
}
}
}
When I got back to the office the next day I picked up Fowler's Patterns of Enterprise Application Architecture and learned that my beautiful and elegant solution already had a name: the Serialized LOB pattern. How sweet :).
By the way, extracting relevant data from HTML markup is itself a very interesting topic and recently .NET community was equipped with a very powerful tool that makes it a whole lot easier to parse Web pages: the HTML Agility Pack library, available for free along with the source code at CodePlex. This is so interesting and so exciting that it probably deserves a post of its own.
Posted
11-08-2006 12:52 PM
by
Marcin Hoppe