SQLite example

Endpoint
1

Looking to store your application data in a SQL database ? SQLlite is one way to do that.

Requires the Microsoft.Data.Sqllite Nuget.

using Microsoft.Data.Sqlite;

var id = 1;
var dataFile = "sqltest.db";

//Create a new database file

using (var connection = new SqliteConnection($"Data Source={dataFile}"))
{
    connection.Open();

    var command = connection.CreateCommand();

    //Create a new table
    command.CommandText = "CREATE TABLE IF NOT EXISTS user (id INTEGER PRIMARY KEY, name TEXT)";

    command.ExecuteNonQuery();

    //Insert a new row
    command.CommandText = "INSERT INTO user (name) VALUES ('World 1')";

    command.ExecuteNonQuery();

    //query the database
    command.CommandText =    @"SELECT name FROM user WHERE id = $id";

    command.Parameters.AddWithValue("$id", id);

    //print the result
    using (var reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            var name = reader.GetString(0);

            Console.WriteLine($"Hello, {name}!");
        }
    }
}

//delete the data file when done
FileInfo dbFile = new FileInfo(dataFile);
try
{
    if (dbFile.Exists)
    {
        dbFile.Delete();
    }
}
catch(Exception ex)
{
    Console.WriteLine("Unable to delete the data file"+ex.Message);
}
3 Likes