I have the idea of a small application I could write for the Windows Mobile. The application will only use its local SQL Server database, at least initially, and it is really simple to create a local database on the device. The only things I need are the physical location of the sdf file on the device and the connection string.
In my "DataBase" class I generate them
private string GetLocalDatabasePath()
{
string applicationPath = Path.GetDirectoryName(this.GetType().Assembly.GetName().CodeBase);
string localDatabasePath = applicationPath + Path.DirectorySeparatorChar +
"TTrack.sdf";
return localDatabasePath;
}
private string GetLocalConnectionString()
{
string localConnectionString = "Data Source=" +
GetLocalDatabasePath();
return localConnectionString;
}
To create a database I just check if the database file already exists, and if not - I create it. Also, for testing purposes, the delete database function is used.
internal void CreateDatabase()
{
if (!File.Exists(localDatabasePath))
{
using (SqlCeEngine engine = new SqlCeEngine(localConnectionString))
{
engine.CreateDatabase();
}
RunNonQuery(qryCreateTables);
}
}
internal void DeleteDatabase()
{
string dbPath = GetLocalDatabasePath();
if (File.Exists(dbPath))
{
File.Delete(dbPath);
}
}
The RunNonQuery bit is just the creation of tables in the database.
internal void RunNonQuery(string query)
{
string connString = GetLocalConnectionString();
using (SqlCeConnection cn = new SqlCeConnection(connString))
{
cn.Open();
SqlCeCommand cmd = cn.CreateCommand();
cmd.CommandText = query;
cmd.ExecuteNonQuery();
}
}
The query for now just creates the simplest possible "dummy" table
internal string qryCreateTables = "CREATE TABLE Users (" +
"UserID uniqueidentifier PRIMARY KEY DEFAULT NEWID() NOT NULL, " +
"Name NVARCHAR(50) NOT NULL )";The RunScalar, obviously, is used to run ExecuteScalar(). Some refactoring still required to improve the code.
internal int RunScalar(string query)
{
string connString = GetLocalConnectionString();
using (SqlCeConnection cn = new SqlCeConnection(connString))
{
cn.Open();
SqlCeCommand cmd = cn.CreateCommand();
cmd.CommandText = query;
return int.Parse(cmd.ExecuteScalar().ToString());
}
}
Now that I can create a database, I can run this simple bit of code to see if it is working.
DataBase db = new DataBase();
db.CreateDatabase();
db.RunNonQuery(db.qryInsertRecord);
MessageBox.Show(db.RunScalar(db.qryCountUsers).ToString());
Database gets created, a record gets inserted, a messagebox with "1" is shown. All is well.
Next, I decide to quickly create and run a simple test for database creation: If the database is present, I delete it, then create a new one, insert one record and test for the count of records indeed being one.
Here is the test I write in NUnit.
[Test]
public void CheckDatabaseCreation()
{
DataBase db = new DataBase();
db.SetFileName(@"\Program Files\TestDB\TTrack.sdf");
db.SetConnectionString(@"Data Source=\Program Files\TestDB\TTrack.sdf");
db.DeleteDatabase();
db.CreateDatabase();
db.RunNonQuery(db.qryInsertRecord);
int count = db.RunScalar(db.qryCountUsers);
Assert.AreEqual(count, 1);
}
This does not go as well, however:
What happened there? Oh, of course - the NUnit test runs on the desktop, but the code is supposed to run on the emulator (I don't use the actual device yet). So, it looks like I will have to work on the approach to testing ...
by
Evgeny. Also posted on
my website
