I made a little program: SqlCdm like but for SDF files.
Here is the code, and a simple test follow.
The usage of my program show this:
SqlCeCmd: [--create-database] [--shrink-database] --connection-string <connection_string> -f <sql_file>
if create-database flag is present, it will be created for you.
if shrink-database is present, the database will be shrinked.
WARNING: only one sqlfile can be provided
May be SqlCmd supported it? But any way here is the code
SqlCeCmd.cs:
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlServerCe;
using System.Data.Common;
using System.Data;
using System.IO;
namespace SqlCeCmd
{
/// <summary>
/// A basic SqlCeCmd to be an equivalent to SqlCmd but for sdf files.
/// </summary>
public static class SqlCeCmd
{
private static void showUsage()
{
Console.Out.WriteLine("SqlCeCmd: [--create-database] [--shrink-database] --connection-string <connection_string> -f <sql_file>");
Console.Out.WriteLine(" if create-database flag is present, it will be created for you.");
Console.Out.WriteLine(" if shrink-database is present, the database will be shrinked.");
Console.Out.WriteLine("WARNING: only one sqlfile can be provided");
}
/// <summary>
/// The main entry point for the application.
/// </summary>
[STAThread]
static void Main(string[] args)
{
if (args.Length == 0)
{
showUsage();
return;
}
// parsing arguments
string connectionString = "";
string sqlFile = "";
bool createDatabase = false;
bool shrinkDatabase = false;
bool nextIsConnectionString = false;
bool nextIsSqlFile = false;
foreach (string arg in args)
{
// ConnectionString
if (nextIsConnectionString)
{
connectionString = arg;
nextIsConnectionString = false;
}
if (arg.Equals("--connection-string"))
nextIsConnectionString = true;
// SqlFile
if (nextIsSqlFile)
{
sqlFile = arg;
nextIsSqlFile = false;
}
if (arg.Equals("-f"))
nextIsSqlFile = true;
if (arg.Equals("--create-database"))
createDatabase = true;
if (arg.Equals("--shrink-database"))
shrinkDatabase = true;
}
if (connectionString == "")
{
Console.Out.WriteLine("error: can't find --connection-string <connection_string>");
showUsage();
return;
}
if (!createDatabase && sqlFile == "")
{
Console.Out.WriteLine("error: can't find -f <sql_file>");
showUsage();
return;
}
// creating database?
if (createDatabase)
createSdfDatabase(connectionString);
if (shrinkDatabase)
shrinkSdfDatabase(connectionString);
// executing queies
if (sqlFile != "")
{
Console.Out.WriteLine("connectionString: " + connectionString);
Console.Out.WriteLine("sqlFile: " + sqlFile);
executeSqlFile(connectionString, sqlFile);
}
}
private static void createSdfDatabase(string connectionString)
{
Console.Out.WriteLine("Creating database: " + connectionString);
new SqlCeEngine(connectionString).CreateDatabase();
}
private static void shrinkSdfDatabase(string connectionString)
{
Console.Out.WriteLine("Shrinking database: " + connectionString);
new SqlCeEngine(connectionString).Shrink();
}
public static void executeSqlFile(String connectionString, String sqlFile)
{
IDbConnection cn = new SqlCeConnection(connectionString);
cn.Open();
string lastQuery = ""; // for debug only
try
{
foreach (string query in splitCaseInsensitive(readWholeFile(sqlFile), "go"))
{
if (!query.Trim().Equals(""))
{
lastQuery = query;
executeSqlQuery(cn, query);
}
}
}
catch (Exception e)
{
Console.Out.WriteLine("error: executing " + lastQuery);
Console.Out.WriteLine("-----------------------------");
Console.Out.WriteLine(e.StackTrace);
Console.Out.WriteLine("-----------------------------");
}
finally
{
cn.Close();
}
}
private static void executeSqlQuery(IDbConnection cn, string query)
{
IDbCommand cmd = new SqlCeCommand(query);
cmd.Connection = cn;
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
// ************************
// Util
// ************************
public static String[] split(String text, String delimiter)
{
return split(text, delimiter, false);
}
public static String[] splitCaseInsensitive(String text, String delimiter)
{
return split(text, delimiter, true);
}
private static String[] split(String text, String delimiter, bool caseSensitive)
{
List<String> splitted = new List<String>();
String remaining = text;
while (remaining.IndexOf(delimiter) > -1)
{
splitted.Add(leftMost(remaining, delimiter));
remaining = right(remaining, delimiter);
}
splitted.Add(remaining);
return splitted.ToArray();
}
/// <summary>
///
/// </summary>
/// <param name="expression">The string to split</param>
/// <param name="delimiter">The splitting delimiter</param>
/// <returns>The left most part of the string</returns>
public static string leftMost(string expression, string delimiter)
{
int index = expression.IndexOf(delimiter);
if (index > 0)
{
return expression.Substring(0, index);
}
return "";
}
/// <summary>
///
/// </summary>
/// <param name="expression">The string to split</param>
/// <param name="delimiter">The splitting delimiter</param>
/// <returns>Return the right part of an expression</returns>
public static string right(string expression, string delimiter)
{
int index = expression.IndexOf(delimiter);
if (index > -1 && index < (expression.Length - 1))
{
return expression.Substring(index + delimiter.Length, expression.Length - index - delimiter.Length);
}
return "";
}
/// <summary>
/// Read the whole file and return its content
/// </summary>
/// <param name="path">path for existing file used to read from</param>
/// <returns>The whole content of the file</returns>
public static string readWholeFile(string path)
{
StreamReader reader = File.OpenText(path);
string content = reader.ReadToEnd();
reader.Close();
return content;
}
}
}
TestSqlCeCmd.bat:
@echo off
:: Creating the Test.sql file
echo create table test (id int, test nvarchar(100)) > Test.sql
echo go >> Test.sql
echo create table test2 (id int, test nvarchar(100)) >> Test.sql
rm -f Test.sdf
SqlCeCmd.exe --create-database --connection-string "data source='Test.sdf'; mode=Exclusive; LCID=3084" -f "Test.sql"
:: an error should be raised here
:: SqlCeCmd.exe --connection-string "data source='Test.sdf'; mode=Exclusive; LCID=3084" -f Test.sql
pause