The file system has let me down (writing XML directly to the DB)

[darrel]

After months of trying to track down a bug where, randomly, on rare
occasions, our XML files only get half-written from our application, we're
throwing up our hands and moving it into the DB.

So, instead of writing our XML files to the filesystem, I'm just going to
send the XML straight to a field in the DB and just grab it from there when
needed.

I have a few questions regarding that:

- preferred method? Right now, I'm using a FileStream:

Dim fs As New
System.IO.FileStream(System.Web.HttpContext.Current.Server.MapPath("/mjb05/rulesXML/"
& Trim(DS.Tables(0).Rows(rowCount)("filename").ToString) & ".xml"),
IO.FileMode.Create, IO.FileAccess.Write, IO.FileShare.Read)

If I'm going to send the text string right to the DB, what should I be
using? Stringbuilder and then just pass the XMLwriter to that?

- caching

One reason we liked the XML was that the server could easily cache it (we
access the XML on each page of our site). And the cache would only update
when the file was updated. If we store it in the database, we'd have to do a
query to see if it's been updated. Is that a big deal? Should we just cache
it every 5 minutes or so? Other preferred method?

-Darrel

 

[Joerg Jooss]

Thus wrote darrel,

After months of trying to track down a bug where, randomly, on rare
occasions, our XML files only get half-written from our application,
we're throwing up our hands and moving it into the DB.

So, instead of writing our XML files to the filesystem, I'm just going
to send the XML straight to a field in the DB and just grab it from
there when needed.

I have a few questions regarding that:

- preferred method? Right now, I'm using a FileStream:

Dim fs As New
System.IO.FileStream(System.Web.HttpContext.Current.Server.MapPath("/m
jb05/rulesXML/" &
Trim(DS.Tables(0).Rows(rowCount)("filename").ToString) & ".xml"),
IO.FileMode.Create, IO.FileAccess.Write, IO.FileShare.Read)

If I'm going to send the text string right to the DB, what should I be
using? Stringbuilder and then just pass the XMLwriter to that?

If your database has a native XML datatype and it's supported by its ADO.NET
provider, I'd use that -- one example is SQL Server (Express) 2005. You can
also get away using CLOBs or BLOBs if you don't care about the XML content
at database level (i.e. don't need to search within an XML document). That
should work with any old database.

- caching

One reason we liked the XML was that the server could easily cache it
(we access the XML on each page of our site). And the cache would only
update when the file was updated. If we store it in the database, we'd
have to do a query to see if it's been updated. Is that a big deal?
Should we just cache it every 5 minutes or so? Other preferred method?

Again, SQL Server 2005 comes to the rescue. Using a SqlCacheDependency, you
can easily implement an automatic refresh of your cached objects. See http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvs05/html/querynotification.asp.

You can also emulate this feature using database triggers in other databases.

Cheers,