ARGH--Microsoft database edition
Matthew Martin
7/24/2015 10:15:00 AM
Tweetable
I ask because Microsoft has killed off SQL Server Compact Edition, which I had previously thought was the standard way to make a database for a non-web-based program in .Net. But there's no SQLServerCE assembly in Visual Studio 2013 or 2015, not even as a reference you can add--if you want it, you have to download and install the .dll file manually. Every non-trivial program necessarily involves storing and manipulating data, and I have a hard time believing that most non-web programs don't have relational, query-able data that calls for a database, so what exactly are these non-web programs using?
Ok, obviously Microsoft left a way to have a local non-web relational database--in fact, they've now extended SQL Server Express so that you can create local SQL Server databases that work offline for your applications, and this is the standard (only) way to add a local database in 2013-5 editions of Visual Studio. And at first I was quite pleased with this because you can use the same SQL Server client namespaces, such as
System.Data.SqlClient for a local database as you would use for a database on the web server, making it useful for development of web applications. In other words, local databases in .Net programs are now "real" SQL Server databases rather than an alternative made specifically for offline applications. There are advantages to this--for example, Compact Edition databases did not support stored procedures whereas real databases do. On the other hand, I'm not sure why developers of offline apps would care--stored procedures are really a way of handling data complexity, which is important on a server-side system with data streaming in from disparate sources, but unimportant in an offline app where you have full control over the format of the incoming data. And the difference is not benign: whereas Compact Edition databases were self-contained within the offline apps that used them, these new full databases have a hard dependency on SQL Server Express, an entire separate program which must be installed on every computer before they can run the offline app.
And that's what has me puzzled. I've installed a couple offline programs with local databases onto other peoples' computers, and in every case, on both new Windows 8 operating systems and older ones, it has prompted me to install SQL Server Express, thus revealing two things: 1)that this isn't just something that Microsoft has built into new operating systems, so that the dependency doesn't matter, and 2)no program developers are using Microsoft's new system for local databases. For comparison, this is now the code to create a database:
string filename = @"C:\Users\User\Documents\testdb.mdf";
if (!System.IO.File.Exists(filename))
{
string databaseName = System.IO.Path.GetFileNameWithoutExtension(filename);
using (var con = new SqlConnection("Data Source=.\\sqlexpress;Initial Catalog=master; Integrated Security=true;User Instance=True;"))
{
if (con.State != ConnectionState.Open) con.Open();
using (var command = con.CreateCommand())
{
command.CommandText =String.Format("CREATE DATABASE {0} ON PRIMARY (NAME={0}, FILENAME='{1}')", databaseName, filename);
command.ExecuteNonQuery();
command.CommandText =String.Format("EXEC sp_detach_db '{0}', 'true'", databaseName);
command.ExecuteNonQuery();
}
con.Close();
}
}But the bigger problem is this: SQL Server Express, as best I can tell, does not support transparent encryption. That's a big problem when you think about it. Sure, you can encrypt data before putting it into the database, but then the DBMS can't tell what it's value is when you query it, which severely complicates a lot of the functionality you'd like a program to be able to have (the only way to do "in the past year" type searches is with a web of surrogate keys; if encryption is done correctly, the only way to find a search term is to query and decrypt all the data one-by-one until it's found). Between the dependency on SQL Server and the lack of usable encryption, there's a strong temptation to continue using the old Compact Edition databases. But that's problematic: if Microsoft is no longer supporting Compact Edition, that means they aren't upgrading encryption algorithms and fixing security bugs.
In conclusion, it is now basically impossible to make a standards compliant offline application using only supported Microsoft frameworks. I get and agree with the web-first paradigm, but come on, that's ridiculous.