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?<\/p>\n
It turns out it is not really that hard at all. All you need is a reference to the\u00a0System.Data.SqlServerCe assembly – which is installed when you run the Sql server compact installer. Then you simply need to use the following code (example):<\/p>\n
string<\/span> connStr = string<\/span>.Format(“Data Source={0};File Mode=Shared Read;Persist Security Info=False”, sdfFilePath);
\nusing<\/span> (System.Data.SqlServerCe.SqlCeEngine<\/span> SQLCEDB = new<\/span> System.Data.SqlServerCe.SqlCeEngine<\/span>(connStr))
\n{<\/p>\nSQLCEDB.CreateDatabase();<\/p>\n
}<\/p><\/blockquote>\n
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.<\/p>\n
e.g.<\/p>\n
CREATE TABLE<\/span> [TableA] (
\n[SomeID] int NOT NULL IDENTITY (1,1)
\n, [Desc] nvarchar(100) NOT NULL
\n);
\nGO<\/span>
\nCREATE TABLE<\/span> [TableB] (
\n[OtherID] int NOT NULL IDENTITY (1,1)
\n, [Desc] nvarchar(100) NOT NULL
\n);
\nGO<\/span>
\n…<\/p><\/blockquote>\nThe class that handles it looks like this:<\/p>\n
public delegate void<\/span> CreateCmndResponseMsgDelegate(string<\/span> createCMND, string<\/span> message);
\npublic class<\/span> CreateSQLCEDB<\/span> : BaseSQLCEDAL<\/span>
\n{<\/p>\nprivate List<\/span><string<\/span>> createCMDs = new List<\/span><string<\/span>>();
\npublic int<\/span> ErrorCount { get<\/span>; set<\/span>; }
\npublic event CreateCmndResponseMsgDelegate<\/span> CreateCmndResponseMsg;
\nprivate void<\/span> RaiseCreateCmndResponseMsg(string<\/span> createCMND, string<\/span> message)
\n{<\/p>\nif<\/span> (CreateCmndResponseMsg != null<\/span>)
\n{<\/p>\nCreateCmndResponseMsg(createCMND, message);<\/p>\n
}<\/p>\n
}
\npublic void<\/span> CreateCommandsFromScript(string<\/span> script)
\n{<\/p>\nstring<\/span>[] createCmds = script.Split(new string<\/span>[] { “\\r\\nGO\\r\\n” }, StringSplitOptions<\/span>.RemoveEmptyEntries);
\ncreateCMDs.AddRange(createCmds);<\/p>\n}
\npublic bool<\/span> RunCommands()
\n{<\/p>\nErrorCount = 0;
\nbase<\/span>.OpenConnection();
\nforeach<\/span> (string<\/span> cmndStr in<\/span> createCMDs)
\n{<\/p>\ntry<\/span>
\n{<\/p>\nbase<\/span>.ExecuteNonQuery(cmndStr);<\/p>\n
}
\ncatch<\/span> (Exception<\/span> ex)
\n{<\/p>\nErrorCount++;
\nRaiseCreateCmndResponseMsg(cmndStr, ex.Message);<\/p>\n}<\/p>\n
}
\nbase<\/span>.CloseConnection();
\nreturn<\/span> ErrorCount == 0;<\/p>\n}<\/p>\n
}<\/p><\/blockquote>\n
Note that the base class ‘BaseSQLCEDAL’ is simply a wrapper class to handle all interactions with SqlCeConnection,\u00a0SqlCeCommand etc.<\/p>\n