SQLite-WinRT: Support for NULL columns added

Small update to the SQLite WinRT wrapper we published at http://sqlwinrt.codeplex.com a few months ago. This update adds support for storing NULL values into a row through the new methods BindNullParameterAt(int index) and BindNullParameterWithName(string name) added to the Statement class.

The following code sample shows how to store a null value into a column in a row, and then how to check if a null value has been returned to you in the data returned from a SELECT:

var db = new SQLiteWinRTPhone.Database(
    ApplicationData.Current.LocalFolder, "sqlite.db");

await db.OpenAsync();

string sql = @"
    CREATE TABLE IF NOT EXISTS
        Customer(Id     INTEGER PRIMARY KEY NOT NULL,
                    Name   VARCHAR(140),
                    NullableInt INTEGER);";

await db.ExecuteStatementAsync(sql);

try
{
    sql = @"INSERT INTO Customer(Id, Name, NullableInt) 
            VALUES (@id, @name, @nullInt);";
    using (var custstmt = await db.PrepareStatementAsync(sql))
    {
        custstmt.BindIntParameterWithName("@id", 11);
        custstmt.BindNullParameterWithName("@name"); 
        custstmt.BindNullParameterAt(3);
        await custstmt.StepAsync();
    }
}
catch (Exception ex)
{
    var result = Database.GetSqliteErrorCode(ex.HResult);
    Debug.WriteLine("INSERT failed with error " + result);
} 

// Right now read the rows out again
try
{
    sql = "SELECT Id, Name, NullableInt FROM Customer;";
    using (var custstmt = await db.PrepareStatementAsync(sql))
    {
        // Use StepAsync to execute a prepared statement
        while (await custstmt.StepAsync())
        {
            var Id = custstmt.GetIntAt(0);
            var Name = "NULL";
            if (custstmt.GetColumnType(1) != ColumnType.Null)
            {
                Name = custstmt.GetTextAt(1);
            }
            int? NullableInt = null;
            if (custstmt.GetColumnType(2) != ColumnType.Null)
            {
                NullableInt = custstmt.GetIntAt(2);
            }
            Debug.WriteLine("Got Customer: " + Id + " " 
                + Name + " NullableInt: " 
                + (NullableInt.HasValue ? NullableInt.Value.ToString() : "null"));
        }
    }
}
catch (Exception ex)
{
    var result = Database.GetSqliteErrorCode(ex.HResult);
    MessageBox.Show("SELECT failed with error " + result);
} 

Notice that you use BindNullParameterxxx to store a null value – using BindTextParameterWithName(string name, string value) and passing a NULL for the value doesn’t work and causes an exception to be thrown.

Notice also that to check if a column returned to you contains NULL, you use the Statement.GetColumnType(int index) method to find out if the column type is ColumnType.Null.

So, please go and download the source code again from https://sqlwinrt.codeplex.com/SourceControl/latest – sorry, no NuGet package for this. Please give it a try and give us any feedback. For more on how to use the wrapper, see my earlier post SQLite-WinRT: Database programming on Windows Phone and Windows 8.

SQLite Using NULL Demo code: SQLiteNULLdemo.zip

One thought on “SQLite-WinRT: Support for NULL columns added

  1. Pingback: Windows Store Developer Links – 2013-08-17 | Dan Rigby

Leave a Reply

Your email address will not be published. Required fields are marked *