I'm new to development with Visual Studio, but I was very surprised when I haven't found any command line tool to run SQL queries against Microsoft SQL Server Compact (or simply SQL CE). I wanted to do this in order to get rid of binary .sdf files in my Visual Studio project. These files can be easily edited in Visual Studio, but because they are binary you can't track changes and moreover every time you open it VS modifies something in the file :) So I decided to store SQL code creating database schema and importing initial data under source control and "compile" it into .sdf file every time during the build.
As I wrote above I haven't found any tool allowing you to execute SQL CE code from command line, but luckily it is very easy to create .sdf files and run SQL queries against them on C#. So wrote tiny C# utility creating .sdf file (if it does not exist), reading SQL code from a source file and running it. The only problem I faced was the limitation of SQL Server Compact due to which you can't run more then one query at once (you can't just execute "SELECT * FROM A; SELECT * FROM B", instead you have to split the string into two parts by semicolon and run "SELECT * FROM A" and "SELECT * FROM B" one after another). So I have to split queries from the source file manually and run the one by one. To avoid complicated parsing of SQL code and problems with escaped semicolons and semicolons inside quotes it assume that query separating semicolon is the last non-whitespace character in a line.
Here is the code I made:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlServerCe;
using System.IO;
namespace sqlcerun
{
class Program
{
static void Main(string[] args)
{
Console.WriteLine("SQL CE code executor");
if (args.Length != 2)
{
Console.WriteLine("2 parameters are required: 1 - database file name, 2 - source code file name.");
return;
}
string fileName = args[0];
string connectionString = "DataSource=\"" + fileName + "\"";
if (!File.Exists(fileName))
{
SqlCeEngine en = new SqlCeEngine(connectionString);
en.CreateDatabase();
en.Dispose();
}
SqlCeConnection conn = null;
try
{
conn = new SqlCeConnection(connectionString);
conn.Open();
StreamReader sr = new StreamReader(args[1]);
string query = "";
string line = sr.ReadLine();
while (line != null)
{
query += line;
if (line.TrimEnd().EndsWith(";"))
{
Console.WriteLine(query);
SqlCeCommand cmd = conn.CreateCommand();
cmd.CommandText = query;
cmd.ExecuteReader();
Console.WriteLine("OK");
query = "";
}
line = sr.ReadLine();
}
sr.Close();
}
catch (Exception e)
{
Console.Error.WriteLine(e.ToString());
Console.WriteLine("Failed!");
return;
}
finally
{
conn.Close();
}
Console.WriteLine("Done!");
}
}
}
The whole Visual Studio project and executable can be found at
http://code.google.com/p/sqlceutils/.
To run .sdf generation during the build I've added the following lines into C# project file (.csproj):
<Target Name="AfterBuild">
<Delete Files="$(OutDir)\db.sdf" />
<Exec Command="$(Utils)\sqlcerun.exe $(OutDir)\db.sdf Database\Schema.sql" />
<Exec Command="$(Utils)\sqlcerun.exe $(OutDir)\db.sdf Database\Data.sql" />
</Target>
If you found any problems with the code please comment.
