SQLite C#: Creating Tables

Summary: in this tutorial, you will learn how to create a new table in a SQLite database from a C# program using ADO.NET.

Steps for creating tables

To create a new table in a SQLite database from a C# program using ADO.NET, you follow these steps:

First, open a new database connection.

Next, construct a CREATE TABLE statement.

Then, create a SqliteCommand object that accepts the CREATE TABLE statement.

After that, execute the statement by calling the ExecuteNonQuery() method of the SqliteCommand object.

Finally, close the database connection.

Creating a new table

The following C# program creates a table called books in a SQLite database:

using Microsoft.Data.Sqlite;

var sql = @"CREATE TABLE authors(
    id INTEGER PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL
)";

try
{
    using var connection = new SqliteConnection(@"Data Source=C:\db\pub.db");
    connection.Open();

    using var command = new SqliteCommand(sql, connection);
    command.ExecuteNonQuery();

    Console.WriteLine("Table 'authors' created successfully.");

}
catch (SqliteException ex)
{
    Console.WriteLine(ex.Message);
}Code language: C# (cs)

How it works.

First, construct a CREATE TABLE statement that creates a table:

var sql = @"CREATE TABLE author(
    id INTEGER PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL
);";Code language: C# (cs)

Second, open a new connection to the SQLite database file C:\db\pub.db:

using var connection = new SqliteConnection(@"Data Source=C:\db\pub.db");
connection.Open();Code language: C# (cs)

The using statement will close the database connection automatically.

Third, execute the CREATE TABLE statement by creating a new SqliteCommand object and calling the ExecuteNonQuery() method:

using var command = new SqliteCommand(sql, connection);
command.ExecuteNonQuery();Code language: C# (cs)

The using statement will also close the command object automatically.

After that, show a message indicating that the table has been created successfully:

Console.WriteLine("Table 'authors' created successfully.");Code language: C# (cs)

Finally, display an error message if any exception occurs:

Console.WriteLine(ex.Message);Code language: C# (cs)

Verifying the table creation

First, open the terminal on the Unix-like systems or Command Prompt on Windows.

Second, connect to sqlite.db database file using the sqlite3 program:

sqlite3 C:\db\pub.dbCode language: C# (cs)

Please note that you need to replace the path to the SQLite database with yours. Also, Note the sqlite3 program needs to be in the PATH environment variable.

Third, run the .tables command to list all tables in the database:

.tablesCode language: C# (cs)

Output:

authorsCode language: C# (cs)

The output indicates that the authors table has been created successfully.

Summary

• Call the ExecuteNonQuery() method of the SqliteCommand object to execute the CREATE TABLE statement to create a new table in an SQLite database.