Mono.Data.Sqlite & System.Data in MonoTouch 1.2 [Preview] - Jonathan Pryor's web log

Main

Mono.Data.Sqlite & System.Data in MonoTouch 1.2 [Preview]

One of the new features that will be present in MonoTouch 1.2 is inclusion of the System.Data and Mono.Data.Sqlite assemblies. This is a preview release of System.Data et. al; it may not fully work. Known limitations are at the end of this post.

What Does This Mean?

It means that the following assemblies will be included in MonoTouch 1.2, and thus usable by MonoTouch applications:

• System.Data.dll: Reduced; see below.
• System.Transactions.dll: Unchanged.
• Mono.Data.Tds.dll: Unchanged.
• Mono.Data.Sqlite.dll: Unchanged, but see below.

Example?

Sure:

using System;
using System.Data;
using System.IO;
using Mono.Data.Sqlite;

class Demo {
    static void Main (string [] args)
    {
        var connection = GetConnection ();
        using (var cmd = connection.CreateCommand ()) {
            connection.Open ();
            cmd.CommandText = "SELECT * FROM People";
            using (var reader = cmd.ExecuteReader ()) {
                while (reader.Read ()) {
                    Console.Error.Write ("(Row ");
                    Write (reader, 0);
                    for (int i = 1; i < reader.FieldCount; ++i) {
                        Console.Error.Write(" ");
                        Write (reader, i);
                    }
                    Console.Error.WriteLine(")");
                }
            }
            connection.Close ();
        }
    }		

    static SqliteConnection GetConnection()
    {
        var documents = Environment.GetFolderPath (
                Environment.SpecialFolder.Personal);
        string db = Path.Combine (documents, "mydb.db3");
        bool exists = File.Exists (db);
        if (!exists)
            SqliteConnection.CreateFile (db);
        var conn = new SqliteConnection("Data Source=" + db);
        if (!exists) {
            var commands = new[] {
                "CREATE TABLE People (PersonID INTEGER NOT NULL, FirstName ntext, LastName ntext)",
                "INSERT INTO People (PersonID, FirstName, LastName) VALUES (1, 'First', 'Last')",
                "INSERT INTO People (PersonID, FirstName, LastName) VALUES (2, 'Dewey', 'Cheatem')",
                "INSERT INTO People (PersonID, FirstName, LastName) VALUES (3, 'And', 'How')",
            };
            foreach (var cmd in commands)
                using (var c = conn.CreateCommand()) {
                    c.CommandText = cmd;
                    c.CommandType = CommandType.Text;
                    conn.Open ();
                    c.ExecuteNonQuery ();
                    conn.Close ();
                }
        }
        return conn;
    }

    static void Write(SqliteDataReader reader, int index)
    {
        Console.Error.Write("({0} '{1}')", 
                reader.GetName(index), 
                reader [index]);
    }
}

The above code creates the Documents/mydb.db3 SQLite database, populates it if it doesn't already exist, then executes a SQL query against the database using normal, standard, ADO.NET mechanisms.

What's Missing?

Functionality is missing from System.Data.dll and Mono.Data.Sqlite.dll.

Functionality missing from System.Data.dll consists of:

• Anything requiring System.CodeDom (e.g. System.Data.TypedDataSetGenerator)
• XML config file support (e.g. System.Data.Common.DbProviderConfigurationHandler)
• System.Data.Common.DbProviderFactories (depends on XML config file support)
• System.Data.OleDb
• System.Data.Odbc
• The System.EnterpriseServices.dll dependency was removed from System.Data.dll, resulting in the removal of the SqlConnection.EnlistDistributedTransaction(ITransaction) method.

Meanwhile, Mono.Data.Sqlite.dll suffered no source code changes, but instead may be host to a number of runtime issues (the primary reason this is a preview release). Mono.Data.Sqlite.dll binds SQLite 3.5. iPhoneOS, meanwhile, ships with SQLite 3.0. Suffice it to say, some things have changed between the two versions. ;-)

Thus, the real question is this: what's missing in SQLite 3.0? The following functions are used by Mono.Data.Sqlite.dll but are missing from iPhoneOS's SQLite:

• sqlite3_column_database_name
• sqlite3_column_database_name16
• sqlite3_column_origin_name
• sqlite3_column_origin_name16
• sqlite3_column_table_name
• sqlite3_column_table_name16
• sqlite3_key
• sqlite3_rekey
• sqlite3_table_column_metadata

Where are these functions used (i.e. what can't you use from Mono.Data.Sqlite)? These appear to be related to database schema querying, e.g. determining at runtime which columns exist on a given table, such as Mono.Data.Sqlite.SqliteConnection.GetSchema (overriding DbConnection.GetSchema) and Mono.Data.Sqlite.SqliteDataReader.GetSchemaTable (overriding DbDataReader.GetSchemaTable). In short, it seems that anything using DataTable is unlikely to work.

Why Provide Mono.Data.Sqlite?

Why not? We realize that there are pre-existing SQLite solutions, but felt that many people would prefer to use the ADO.NET code they're already familiar with. Bringing System.Data and Mono.Data.Sqlite to MonoTouch permits this.

What About Data Binding?

Data binding with e.g. a UITableView is not currently implemented.

Conclusion

I suck at conclusions. :-)

Hope you enjoy this preview!

blog comments powered by Disqus