{"id":3574,"date":"2024-05-11T21:13:05","date_gmt":"2024-05-11T14:13:05","guid":{"rendered":"https:\/\/www.sqlitetutorial.net\/?page_id=3574"},"modified":"2024-10-25T19:59:26","modified_gmt":"2024-10-25T12:59:26","slug":"create-tables","status":"publish","type":"page","link":"https:\/\/www.sqlitetutorial.net\/sqlite-csharp\/create-tables\/","title":{"rendered":"SQLite C#: Creating Tables"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to create a new table in a SQLite database from a C# program using ADO.NET.<\/p>\n\n\n\n

Steps for creating tables<\/h2>\n\n\n\n

To create a new table in a SQLite database from a C# program using ADO.NET, you follow these steps:<\/p>\n\n\n\n

First, open a new database connection.<\/p>\n\n\n\n

Next, construct a CREATE TABLE<\/a> statement.<\/p>\n\n\n\n

Then, create a SqliteCommand<\/code> object that accepts the CREATE TABLE<\/code> statement.<\/p>\n\n\n\n

After that, execute the statement by calling the ExecuteNonQuery()<\/code> method of the SqliteCommand<\/code> object.<\/p>\n\n\n\n

Finally, close the database connection.<\/p>\n\n\n\n

Creating a new table<\/h2>\n\n\n\n

The following C# program creates a table called books<\/code> in a SQLite database:<\/p>\n\n\n

using<\/span> Microsoft.Data.Sqlite;\n\nvar<\/span> sql = @\"CREATE TABLE authors(\n    id INTEGER PRIMARY KEY,\n    first_name TEXT NOT NULL,\n    last_name TEXT NOT NULL\n)\"<\/span>;\n\ntry<\/span>\n{\n    using<\/span> var<\/span> connection = new<\/span> SqliteConnection(@\"Data Source=C:\\db\\pub.db\"<\/span>);\n    connection.Open();\n\n    using<\/span> var<\/span> command = new<\/span> SqliteCommand(sql, connection);\n    command.ExecuteNonQuery();\n\n    Console.WriteLine(\"Table 'authors' created successfully.\"<\/span>);\n\n}\ncatch<\/span> (SqliteException ex)\n{\n    Console.WriteLine(ex.Message);\n}<\/code><\/span>Code language:<\/span> C#<\/span> (<\/span>cs<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works.<\/p>\n\n\n\n
First, construct a CREATE<\/code> TABLE<\/code> statement that creates a table:<\/p>\n\n\n
var<\/span> sql = @\"CREATE TABLE author(\n    id INTEGER PRIMARY KEY,\n    first_name TEXT NOT NULL,\n    last_name TEXT NOT NULL\n);\"<\/span>;<\/code><\/span>Code language:<\/span> C#<\/span> (<\/span>cs<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, open a new connection to the SQLite database file C:\\db\\pub.db<\/code>:<\/p>\n\n\n
using<\/span> var<\/span> connection = new<\/span> SqliteConnection(@\"Data Source=C:\\db\\pub.db\"<\/span>);\nconnection.Open();<\/code><\/span>Code language:<\/span> C#<\/span> (<\/span>cs<\/span>)<\/span><\/small><\/pre>\n\n\n
The using<\/code> statement will close the database connection automatically.<\/p>\n\n\n\n
Third, execute the CREATE TABLE<\/code> statement by creating a new SqliteCommand<\/code> object and calling the ExecuteNonQuery()<\/code> method:<\/p>\n\n\n
using<\/span> var<\/span> command = new<\/span> SqliteCommand(sql, connection);\ncommand.ExecuteNonQuery();<\/code><\/span>Code language:<\/span> C#<\/span> (<\/span>cs<\/span>)<\/span><\/small><\/pre>\n\n\n
The using<\/code> statement will also close the command object automatically.<\/p>\n\n\n\n
After that, show a message indicating that the table has been created successfully:<\/p>\n\n\n
Console.WriteLine(\"Table 'authors' created successfully.\"<\/span>);<\/code><\/span>Code language:<\/span> C#<\/span> (<\/span>cs<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, display an error message if any exception occurs:<\/p>\n\n\n
Console.WriteLine(ex.Message);<\/code><\/span>Code language:<\/span> C#<\/span> (<\/span>cs<\/span>)<\/span><\/small><\/pre>\n\n\n
Verifying the table creation<\/h2>\n\n\n\n
First, open the terminal on the Unix-like systems or Command Prompt on Windows.<\/p>\n\n\n\n
Second, connect to sqlite.db<\/code> database file using the sqlite3<\/code> program:<\/p>\n\n\n
sqlite3 C:\\db\\pub.db<\/code><\/span>Code language:<\/span> C#<\/span> (<\/span>cs<\/span>)<\/span><\/small><\/pre>\n\n\n
Please note that you need to replace the path to the SQLite database with yours. Also, Note the sqlite3<\/code>\u00a0program needs to be in the\u00a0PATH<\/code>\u00a0environment variable.<\/p>\n\n\n\n
Third, run the .tables<\/code> command to list all tables in the database:<\/p>\n\n\n
.tables<\/code><\/span>Code language:<\/span> C#<\/span> (<\/span>cs<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
authors<\/code><\/span>Code language:<\/span> C#<\/span> (<\/span>cs<\/span>)<\/span><\/small><\/pre>\n\n\n
The output indicates that the authors<\/code> table has been created successfully.<\/p>\n\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Call the ExecuteNonQuery()<\/code> method of the SqliteCommand<\/code> object to execute the CREATE<\/code> TABLE<\/code> statement to create a new table in an SQLite database.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful ?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t