C# and SQLite – Inserting Data
In order to insert data into an SQLite database, the SQL ‘Insert’ statement needs to be used. The following example inserts a record into the same ‘person’ table that was used in the example for selecting data.
Inserting data works in a similar fashion as selecting data using parameters. Firstly, a check is made to see if the database file actually exists. If it doesn’t, a message is displayed and execution of the program is halted. If successfully found, a connection to the database is established, then the query parameters are defined as variables, which are bound in to the SQL statement before it is executed and a confirmation message is displayed. The execution of the SQL statement is wrapped in a ‘try-catch-finally’ block to catch any errors that may arise and close the database connection at the end.
// Database. string database = @"C:\Demo\DemoDB.db"; string password = @"DemoPW"; SqliteConnection connect; // Check if the database exists. if (!File.Exists(database)) { // Message confirming incorrect database location. Console.WriteLine("Error locating database."); // Stop program execution. System.Environment.Exit(1); } // Database connection. connect = new SqliteConnection("Data Source=" + database + ";" + "Password=" + password); try { // Connect to database. connect.Open(); } catch (Exception e) { // Message confirming unsuccessful database connection. Console.WriteLine("Database connection unsuccessful."); // Stop program execution. System.Environment.Exit(1); } try { // Query parameters. string firstname = "Fiona"; string lastname = "Jones"; string title = "Miss"; string dob = "1985-05-19"; // Query text incorporated into SQL command. var sqlInsert = connect.CreateCommand(); sqlInsert.CommandText = @" INSERT INTO person (firstname, lastname, title, dob) VALUES ($firstname, $lastname, $title, $dob) "; // Bind the parameters to the query. sqlInsert.Parameters.AddWithValue("$firstname", firstname); sqlInsert.Parameters.AddWithValue("$lastname", lastname); sqlInsert.Parameters.AddWithValue("$title", title); sqlInsert.Parameters.AddWithValue("$dob", dob); // Execute SQL. sqlInsert.ExecuteNonQuery(); // Confirm successful addition of person information. Console.WriteLine("Person information added successfully."); } catch (Exception e) { // Confirm error adding person information and exit. Console.WriteLine("Error adding person information."); System.Environment.Exit(1); } finally { // Close the database connection. connect.Close(); }
The contents of the ‘person’ table now looks as follows.
id | firstname | lastname | title | dob |
1 | Bob | Smith | Mr | 1980-01-20 |
2 | George | Jones | Mr | 1997-12-15 |
3 | Fred | Bloggs | Mr | 1975-05-07 |
4 | Alan | White | Mr | 1989-03-20 |
5 | Fiona | Jones | Miss | 1985-05-19 |