BindTextParameterWithName when text is null

Jun 25, 2013 at 7:40 PM
When calling BindTextParameterWithName when the parameter is null, it throws and ArgumentNullException. What is the correct way to call this when trying to insert nulls into text columns? Surely this is a valid thing to do as not every text column is required to have a value. What do you do in this case?
Coordinator
Aug 7, 2013 at 9:57 AM
I have just posted an update which adds two new methods to Statement: BindNullParameterAt(int index) and BindNullParameterWithName(string name). You can use this to insert a null into any column.

The code then looks like this:
        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.BindNullParameterWithName("@nullInt");
                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);
            //throw new ApplicationException("SELECT failed with error " + result);
            MessageBox.Show("SELECT failed with error " + result);
        } 
Hope that gives you what you need.

Andy