SQLite C#: Creating Tables

You organize your data. Now organize your savings.

Set savings goals, track your progress, and stay on course.

Get Savigo for iPhone ›

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);
}Copy

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
);";Copy

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();Copy

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();Copy

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.");Copy

Finally, display an error message if any exception occurs:

Console.WriteLine(ex.Message);Copy

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.dbCopy

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:

.tablesCopy

Output:

authorsCopy

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.