{"id":3580,"date":"2024-05-12T07:59:02","date_gmt":"2024-05-12T00:59:02","guid":{"rendered":"https:\/\/www.sqlitetutorial.net\/?page_id=3580"},"modified":"2024-10-25T20:00:34","modified_gmt":"2024-10-25T13:00:34","slug":"insert","status":"publish","type":"page","link":"https:\/\/www.sqlitetutorial.net\/sqlite-csharp\/insert\/","title":{"rendered":"SQLite C#: Inserting Data"},"content":{"rendered":"\n

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

Steps for inserting data into a table using ADO.NET<\/h2>\n\n\n\n

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

Second, construct an INSERT<\/a>\u00a0statement that inserts a row into a table.<\/p>\n\n\n\n

Next, create a\u00a0SqliteCommand<\/code>\u00a0object that accepts the\u00a0INSERT<\/code>\u00a0statement.<\/p>\n\n\n\n

Then, bind values to the parameters of the INSERT<\/code> statement.<\/p>\n\n\n\n

After that, execute the statement by calling the\u00a0ExecuteNonQuery()<\/code>\u00a0method of the\u00a0SqliteCommand<\/code>\u00a0object.<\/p>\n\n\n\n

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

Inserting one row into a table<\/h2>\n\n\n\n

The following program inserts one row into the authors<\/code> table:<\/p>\n\n\n

using<\/span> Microsoft.Data.Sqlite;\n\nvar<\/span> sql = \"INSERT INTO authors (first_name, last_name) \"<\/span> +\n    \"VALUES (@first_name, @last_name)\"<\/span>;\n\nvar<\/span> firstName = \"Jane\"<\/span>;\nvar<\/span> lastName = \"Doe\"<\/span>;\n\ntry<\/span>\n{\n    \/\/ Open a new database connection<\/span>\n    using<\/span> var<\/span> connection = new<\/span> SqliteConnection(@\"Data Source=C:\\db\\pub.db\"<\/span>);\n    connection.Open();\n\n\n    \/\/ Bind parameters values<\/span>\n    using<\/span> var<\/span> command = new<\/span> SqliteCommand(sql, connection);\n\n    command.Parameters.AddWithValue(\"@first_name\"<\/span>, firstName);\n    command.Parameters.AddWithValue(\"@last_name\"<\/span>, lastName);\n\n\n    \/\/ Execute the INSERT statement<\/span>\n    var<\/span> rowInserted = command.ExecuteNonQuery();\n\n    Console.WriteLine($\"The author '{firstName}<\/span> {lastName}<\/span>' has been created successfully.\"<\/span>);\n\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 an INSERT<\/code> statement that inserts a row into the authors<\/code> table:<\/p>\n\n\n
var<\/span> sql = \"INSERT INTO authors (first_name, last_name) \"<\/span> +\n    \"VALUES (@first_name, @last_name)\"<\/span>;<\/code><\/span>Code language:<\/span> C#<\/span> (<\/span>cs<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, declare and initialize variables for storing the author’s first name and last name:<\/p>\n\n\n
var<\/span> firstName = \"Jane\"<\/span>;\nvar<\/span> lastName = \"Doe\"<\/span>;<\/code><\/span>Code language:<\/span> C#<\/span> (<\/span>cs<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, open a database connection to the C:\\db\\pub.db<\/code> file:<\/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
Fourth, create a new SqliteCommand<\/code> object and bind values to the corresponding parameters:<\/p>\n\n\n
using<\/span> var<\/span> command = new<\/span> SqliteCommand(sql, connection);\n\ncommand.Parameters.AddWithValue(\"@first_name\"<\/span>, firstName);\ncommand.Parameters.AddWithValue(\"@last_name\"<\/span>, lastName);<\/code><\/span>Code language:<\/span> C#<\/span> (<\/span>cs<\/span>)<\/span><\/small><\/pre>\n\n\n
Fifth, execute the INSERT<\/code> statement by calling the ExecuteNonQuery()<\/code> method of the SqliteCommand<\/code> object:<\/p>\n\n\n
var<\/span> rowInserted = command.ExecuteNonQuery();<\/code><\/span>Code language:<\/span> C#<\/span> (<\/span>cs<\/span>)<\/span><\/small><\/pre>\n\n\n
The ExecuteNonQuery()<\/code> method returns the number of rows inserted. <\/p>\n\n\n\n
Sixth, display a message indicating that the program has inserted a new row into the authors<\/code> table successfully:<\/p>\n\n\n
Console.WriteLine($\"The author '{firstName}<\/span> {lastName}<\/span>' has been created successfully.\"<\/span>);<\/code><\/span>Code language:<\/span> C#<\/span> (<\/span>cs<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, show the error message if there is any exception occurred during the insertion:<\/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 insert<\/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, launch the sqlite3<\/code> command to connect to the pub.db<\/code> file located in the C:\\db<\/code> directory (or any directory of your choice)<\/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
Note that sqlite3<\/code> program needs to be in the PATH<\/code> environment variable or you’ll encounter an error:<\/p>\n\n\n
'sqlx'<\/span> is<\/span> not recognized as<\/span> an internal<\/span> or external command,\noperable program or batch file.<\/code><\/span>Code language:<\/span> C#<\/span> (<\/span>cs<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, retrieve data from the authors<\/code> table:<\/p>\n\n\n
SELECT * FROM authors;<\/code><\/span>Code language:<\/span> C#<\/span> (<\/span>cs<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
id  first_name  last_name\n--  ----------  ---------\n1<\/span>   Jane        Doe<\/code><\/span>Code language:<\/span> C#<\/span> (<\/span>cs<\/span>)<\/span><\/small><\/pre>\n\n\n
The output indicates that the author has been inserted successfully.<\/p>\n\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Call the ExecuteNonQuery()<\/code> method of the SqliteConnection<\/code> object to execute an INSERT<\/code> statement that inserts a new row into a table.<\/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