This is not exactly something new or breaking any new boundaries but it could be a useful tip for anyone that wants to make use of SQLCE databases from a small .Net application. There are some great tools and utilities to create a new SQL compact edition database using some UI but what if you want to automate the process or allow a client app to create a new database ‘on the fly’ or when needed?
It turns out it is not really that hard at all. All you need is a reference to the System.Data.SqlServerCe assembly – which is installed when you run the Sql server compact installer. Then you simply need to use the following code (example):
string connStr = string.Format(“Data Source={0};File Mode=Shared Read;Persist Security Info=False”, sdfFilePath);
using (System.Data.SqlServerCe.SqlCeEngine SQLCEDB = new System.Data.SqlServerCe.SqlCeEngine(connStr))
{SQLCEDB.CreateDatabase();
}
That will create a new ‘blank’ database. What if you want to also create tables inside it? That is also very simple. I created a simple library to do that – it simply takes an input script and runs each command one by one. Each statement must be separated by a ‘GO’ statement that is on its own line.
e.g.
CREATE TABLE [TableA] (
[SomeID] int NOT NULL IDENTITY (1,1)
, [Desc] nvarchar(100) NOT NULL
);
GO
CREATE TABLE [TableB] (
[OtherID] int NOT NULL IDENTITY (1,1)
, [Desc] nvarchar(100) NOT NULL
);
GO
…
The class that handles it looks like this:
public delegate void CreateCmndResponseMsgDelegate(string createCMND, string message);
public class CreateSQLCEDB : BaseSQLCEDAL
{private List<string> createCMDs = new List<string>();
public int ErrorCount { get; set; }
public event CreateCmndResponseMsgDelegate CreateCmndResponseMsg;
private void RaiseCreateCmndResponseMsg(string createCMND, string message)
{if (CreateCmndResponseMsg != null)
{CreateCmndResponseMsg(createCMND, message);
}
}
public void CreateCommandsFromScript(string script)
{string[] createCmds = script.Split(new string[] { “\r\nGO\r\n” }, StringSplitOptions.RemoveEmptyEntries);
createCMDs.AddRange(createCmds);}
public bool RunCommands()
{ErrorCount = 0;
base.OpenConnection();
foreach (string cmndStr in createCMDs)
{try
{base.ExecuteNonQuery(cmndStr);
}
catch (Exception ex)
{ErrorCount++;
RaiseCreateCmndResponseMsg(cmndStr, ex.Message);}
}
base.CloseConnection();
return ErrorCount == 0;}
}
Note that the base class ‘BaseSQLCEDAL’ is simply a wrapper class to handle all interactions with SqlCeConnection, SqlCeCommand etc.
See CreateSQLCEDB for full example code.
0 Comments.