Tag Archives: sql compact

Dirty dirty database hacks

So I’m working on a program to help me manage my games when I run Pathfinder campaigns that allow me to create cities that have stores and citizens that work places and all sort of things.

The application is written in c# and was never intended to see the light of day, beyond my own personal usage as a DM on my laptop. My laptop, being a development machine, has SQL server 2008 installed on it so setting up the Microsoft entity data model systems to connect to that and make everything work nicely was easy. Then, as many applications for personal use tend to, somebody showed some interest in also using it.

This got me thinking, this person doesn’t develop. On her laptop, she has no real need for SQL server and I doubt she’d want to install it, setup an SQL instance, create the database, run the database scripts and finally setup her connection strings for the application just to use it. So it got me to thinking about whether there was a better way to manage it.

The next thought was to host an SQL server at home and have it exposed to the whole internet and hope that the thing never gets cracked. The problem with that was that I would then need to set up a webservice to manage files that are used for the campaigns (maps and such), which is well beyond the scope of my current project and so straight up discounted the option.

Finally, I was left with SQL CE. I had used it once or twice, but had rarely chosen it over a full SQL instance. There had never seemed too much of a point. So it seemed like my best option. I used my edmx file in visual studio to generate myself an SQL CE script and embedded it into my application. When I wanted to create a new database I just used the following code:

string connectionString = String.Format("DataSource=\"{0}world.sdf\"; Password=\"{1}\"", path, txtPassword.Text);
SqlCeEngine en = new SqlCeEngine(connectionString);
en.CreateDatabase();
string sql = WorldManager.getSQLCE();//.Split(split1, StringSplitOptions.RemoveEmptyEntries)[1];
string[] split = { "GO" };
string[] data = sql.Split(split, StringSplitOptions.RemoveEmptyEntries);
SqlCeConnection conn = new SqlCeConnection(connectionString);
conn.Open();
SqlCeCommand cmd = conn.CreateCommand();
foreach (String b in data)
{
	string b2 = b.Trim();
	if (b2.Last() == ';')
		b2 = b2.Substring(0, b2.Length - 1);
	cmd.CommandText = b2;
	try
	{
		cmd.ExecuteNonQuery();
	}
	catch (SqlCeException ex)
	{ }
}

Which of course, with my luck, didn’t want to work so well. So my next attempt was to try something mean and dirt (and I mean really dirty). I wouldn’t necessarily recommend that anyone tries or uses this method, at least not for databases…

So my final bright idea (which is surprisingly working quite well for me) involves pre-generating the database with Visual Studio’s Entity Data Model stuff, and embedding it into my executable. Pro-tip: this is a horrible solution. I then read the embedded sdf file and save it to disk. If you’re liable to change the schema, this is a terrible thing to do, but I’ll try and come up with a solution if and when I need to.

And here’s the code to read the sdf file from the executable.

public static byte[] getBaseDB()
{
	System.Reflection.Assembly thisExe;
	thisExe = System.Reflection.Assembly.GetExecutingAssembly();
	System.IO.Stream file = thisExe.GetManifestResourceStream("RoleplayData.WorldDefault.sdf");
	byte[] buffer = new byte[file.Length];
	file.Read(buffer, 0, (int)file.Length);
	return buffer;
}