SQLite.NET Check if column exists in table

Question

I have created following method that Adds a column in an already existing SQLite table

public async void AddColumnMyNewColumn()
{
    SQLiteAsyncConnection conn = new SQLiteAsyncConnection(path);
    await conn.ExecuteAsync("ALTER TABLE MyTable ADD COLUMN MyNewColumn bit DEFAULT 'False';");
}

It creates a new column MyNewColumn in MyTable.

Next time when AddColumnMyNewColumn method is called, then it throws an error.

How to check if this column is already created??

I've cheched this,and this, but I cant put these things together to get something like this..

public async void AddColumnMyNewColumn()
{
    SQLiteAsyncConnection conn = new SQLiteAsyncConnection(path);
    bool columnExists;

    //Check if column exists & set columnExists accordingly

    if(!columnExists)
        await conn.ExecuteAsync("ALTER TABLE MyTable ADD COLUMN MyNewColumn bit DEFAULT 'False';");
}

score 6 · Accepted Answer · edited May 23 '17 at 10:33

6

Solution 1. Using SQLite.Net

You are using SQLite.Net, so your Tables are mapped to C# Classes, right?

And if you add a Property to your C# Class you can just call this

SQLiteAsyncConnection conn = new SQLiteAsyncConnection(path); 

await conn.CreateTableAsync<MyTableClass>();

and your new property will be added as a column to your Table, all previous data will not be changed

Solution 2. Using a query

(to list all columns of a table) and then you could add your column manually)

SQLite Schema Information Metadata

edited May 23 '17 at 10:33

Community

1
1

answered Jan 28 '15 at 14:49

CodeNoob

984
8
8

Nice answer. I have one more issue regarding the same. Please Check here http://stackoverflow.com/questions/28208685/sqlite-net-c-sharp-equivalent-for-sqlite-bit-datatype – Gk_999 Jan 29 '15 at 06:58
what happens if you rename an existing column and execute createtableasync method? will this create a new column and keep old one or update the name of the column? or even same question if you change the type of the existing column? – Emil Feb 16 '16 at 22:16

xleon · Answer 2 · 2019-01-13T04:55:49.037

5

With SQLite.net it´s easy to know if a column exists, in case you really need it:

var tableInfo = connection.GetTableInfo("YourTable");
var columnExists = tableInfo.Any(x => x.Name.Equals("ColumnName"));

But as mentioned before, connection.CreateTable<T>() or connection.CreateTableAsync<T>() will add the new columns for you, so you don´t need to execute sql statements.

edited Jan 13 '19 at 04:55

answered Jun 23 '16 at 09:24

xleon

6,201
3
36
52

SQLite.NET Check if column exists in table

2 Answers2