CODEWITHSUNDEEP
c#sqlitewindows-runtimesqlite.net
Gk_999
Gk_999

Reputation: 518

SQLite.NET Check if column exists in table

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';");
}

Upvotes: 1

Views: 4978

Answers (3)

Danny
Danny

Reputation: 506

okay, so for the past few hours been trying to fix this issue.. i have a couple of newly added columns in my model. but the previously created sqlite database tables are not updated with the newly added columns. and my API would just crash.. so my previous function that crashed looks like this..

public void FinishedConvertingFile(string path, string beforeSize, string afterSize)
{
    //create table data
    var file = new ConvertedFiles()
    {
        FullPath = path,
        File = Path.GetFileName(path),
        AfterSize = afterSize,
        BeforeSize = beforeSize
    };

    //create db connection
    var db = new SQLiteConnection(_db);

    // Insert the file record into the database
    db.Insert(file);
}

but this would just crash on the db.Insert(file); line if the db is created previously and the column does not exist. So to mitigate this issue, i changed it to this..

    public void FinishedConvertingFile(string path, string beforeSize, string afterSize)
    {
        //create table data
        var file = new ConvertedFiles()
        {
            FullPath = path,
            File = Path.GetFileName(path),
            AfterSize = afterSize,
            BeforeSize = beforeSize
        };

        //create db connection
        var db = new SQLiteConnection(_db);


        //get all the column types
        var columns = typeof(ConvertedFiles).GetProperties();

        //create loop that ensures the column exists in the table
        foreach (var column in columns)
        {
            //calls the EnsureColumnExists function to check if the column exists, if it doesnt it will create it.
            EnsureColumnExists(db, nameof(ConvertedFiles), column.Name, GetSQLiteType(column.PropertyType));
        }

        // Insert the file record into the database
        db.Insert(file);
    }

This way, it will check if every column exists and if it does not, it will create it on the table.

Here is the EnsureColumnExists function

private void EnsureColumnExists(SQLiteConnection db, string tableName, string columnName, string columnType)
{
    var columns = db.GetTableInfo(tableName);
    if (!columns.Any(c => c.Name == columnName))
    {
        var query = $"ALTER TABLE {tableName} ADD COLUMN {columnName} {columnType}";
        db.Execute(query);
    }
}

and here is the GetSQLiteType function..

    private string GetSQLiteType(Type type)
    {

        if (type == typeof(string))
            return "TEXT";
        else if (type == typeof(int))
            return "INTEGER";
        else
            throw new NotImplementedException($"Unsupported type: {type}");
    }

just make sure you include your datatype onto the GetSQLiteType function, or handle that as you wish if the type does not exist.

Upvotes: 0

xleon
xleon

Reputation: 6365

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.

Upvotes: 5

CodeNoob
CodeNoob

Reputation: 984

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

Upvotes: 6

Related Questions