How can I remove a column from my SQLite table with sqlite-net-pcl that comes with Xamarin Forms?

Question

I have a table that was created with this C# code:

public class ViewHistory
{
    [PrimaryKey]
    public string YYMMDD { get; set; }
    public int UtcNow { get; set; }
    public int Assign { get; set; }
    public int Learn { get; set; }
    public int Practice { get; set; }
    public int Quiz { get; set; }

}

and then

db2.CreateTable<ViewHistory>();

I would like to check if the Assign column exists and then remove the Assign column if it exists. So far the suggestions I have seen have all been either quite old or have suggested creating a new table and also don't include any check to see if it exists.

Now with the latest release of sqlite-net-pcl (1.5.2) is there any way I can drop a column? If not can someone recommend a way to do this that's just using the C# features that are available to me with the PCL or with SQL that I could execute.

I saw this on SO but it doesn't really help me:

Delete column from SQLite table

score 2 · Accepted Answer · answered May 04 '19 at 20:41

SQLite does not support ALTER TABLE x DROP COLUMN x so you need to create a new table and copy data.

You can do all this via a single multi-line SQL statement and execute it, but this will walk you though the steps using the ORM as much as possible:

Note: This assumes that your model has be updated and does not include that column anymore and your current database might or might not have it...

var conn = new SQLiteConnection(.....
conn.CreateTable<ViewHistory>();
~~~

if (0 < conn.ExecuteScalar<int>("SELECT COUNT(*) AS colcount FROM pragma_table_info('ViewHistory') WHERE name='Assign'"))
{
    try
    {
        conn.BeginTransaction();
        conn.Execute("ALTER TABLE ViewHistory RENAME TO backup;");
        conn.CreateTable<ViewHistory>();

        // Column map and copy data 
        var columns = conn.GetMapping<ViewHistory>(CreateFlags.None).Columns;
        var columnNames = string.Join(",", columns.Select(p => p.Name));
        conn.Execute($"INSERT INTO ViewHistory ({columnNames}) SELECT {columnNames} FROM backup;");

        conn.Execute("DROP TABLE backup;");
        conn.Commit();
        conn.Execute("VACUUM");
    }
    catch (Exception ex)
    {
        conn.Rollback();
        throw ex;
    }
}

Note: Typically I just use "DB Browser for SQLite", make all the table/column alterations to the database and copy the "DB Log output" that contains all the SQL statements and paste that into a single SQLConnnection.Exceute statement...

How can I remove a column from my SQLite table with sqlite-net-pcl that comes with Xamarin Forms?

1 Answers1