Previous posts in this series:
– Mono I: Getting Started
– Mono II: Basic Navigation
– Mono III: Shared Libraries
– Mono IV: Webservices
– Mono V: Content and Resource Files
– Mono VI: File Access
Not only do you want to be able to write to the file system, for more complex applications you will want to write to a relational database.
Windows Phone 7
There is currently no support for Microsoft’s own lightweight database engine, SQL CE. However, there is a port of Sqlite that will work nicely with Isolated Storage.
– Go to CodePlex and download either the source code or just the assemblies for the Sqlite project (http://wp7sqlite.codeplex.com). If you download the assemblies you just need to add them as a reference to your WP7 application project. I prefer to have the source code handy in case something goes wrong. In this case simply add the two Sqlite projects to your solution and add a reference to both projects to your WP7 application project.
– Once you’ve added the sqlite references, all you need to do is write a bit of code to create the database, tables and insert records. Of course you’ll want to validate that the data exists. The following code was taken, and modifies slightly, from the documentation on Codeplex but illustrates all of these operations.
private void CreateSqlLite(string connectionString)
{
using (SqliteConnection conn = new SqliteConnection(connectionString)) {
conn.Open();
using (SqliteCommand cmd = conn.CreateCommand()) {
cmd.CommandText = "CREATE TABLE test ( [id] INTEGER PRIMARY KEY, [col] INTEGER UNIQUE, [col2] INTEGER, [col3] REAL, [col4] TEXT, [col5] BLOB)";
cmd.ExecuteNonQuery();
cmd.Transaction = conn.BeginTransaction();
cmd.CommandText = "INSERT INTO test(col, col2, col3, col4, col5) VALUES(@col, @col2, @col3, @col4, @col5);SELECT last_insert_rowid();";
cmd.Parameters.Add("@col", DbType.Int32);
cmd.Parameters.Add("@col2", DbType.Int32);
cmd.Parameters.Add("@col3", DbType.Double);
cmd.Parameters.Add("@col4", DbType.AnsiString);
cmd.Parameters.Add("@col5", DbType.Object);
for (int i = 0; i < 100; i++)
{
cmd.Parameters["@col"].Value = i;
cmd.Parameters["@col2"].Value = i;
cmd.Parameters["@col3"].Value = i * 0.515;
cmd.Parameters["@col4"].Value = "สวัสดี な. あ · か · さ · た · な · は · ま · や · ら · わ. 形容詞 hello " + i;
cmd.Parameters["@col5"].Value = Encoding.UTF8.GetBytes("สวัสดี");
object s = cmd.ExecuteScalar();
}
cmd.Transaction.Commit();
cmd.Transaction = null;
cmd.CommandText = "SELECT * FROM test";
using (SqliteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
//var bytesValue = (byte[])reader.GetValue(5);
//var intValue reader.GetInt32(0);
//var doubleValue = reader.GetDouble(3);
//var stringValue = reader.GetString(4);
}
}
conn.Close();
}
}
}
The only thing left for you is to call this method supplying the connection string to use. For example the following will create a database file called test.db within IsolatedStorage on the device.
CreateSqlLite("Version=3,uri=file:test.db");
iOS
– The MonoTouch sdk includes support for Sqlite so all you need to do is reference Mono.Data.Sqlite (right-click the Android application project and select Add References) and System.Data.
– Now from your code you can pass in the full path (within the Personal special folder) of the database file you want to create and work with.
string dbPath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Personal), "test.db");
if (File.Exists(dbPath)) File.Delete(dbPath);
CreateSqlLite("Data Source=" + dbPath);
Note I: that this uses exactly the same CreateSqlLite method that the WP7 application uses.
Android
– The MonoDroid sdk includes support for Sqlite so all you need to do is reference Mono.Data.Sqlite (right-click the Android application project and select Add References) and System.Data.
– Now from your code you can pass in the full path (within the Personal special folder) of the database file you want to create and work with.
string dbPath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Personal), "test.db"); if (File.Exists(dbPath)) File.Delete(dbPath);
CreateSqlLite("Data Source=" + dbPath);
Note I: that this uses exactly the same CreateSqlLite method that the WP7 application uses.
Note II: Be careful if you’re using “clever” Visual Studio addins like resharper as they can be overly helpful. When I added a reference to System.Data it decided to add a reference to System.EnterpriseServices which is a .NET 4 library and clearly not designed to work with MonoDroid. This caused me no end of frustration – I ended up going through all the references in my MonoDroid projects looking for references that were not MonoDroid specific.