SQLite simple insert query

Question

I'm trying to use SQLite as my storage. I've added reference dll using nuget and using statement as well.

I have

private void SetConnection()
{
            sql_con = new SQLiteConnection
                ("Data Source=c:\\Dev\\MYApp.sqlite;Version=3;New=False;Compress=True;");
}

private void ExecuteQuery(string txtQuery)
{
            SetConnection();
            sql_con.Open();
            sql_cmd = sql_con.CreateCommand();
            sql_cmd.CommandText = txtQuery;
            sql_cmd.ExecuteNonQuery();
            sql_con.Close(); 
}

and I'm sending query txt like this

public void Create(Book book)
{
            string txtSqlQuery  = "INSERT INTO Book (Id, Title, Language, PublicationDate, Publisher, Edition, OfficialUrl, Description, EBookFormat) ";
            txtSqlQuery += string.Format("VALUES (@{0},@{1},@{2},@{3},@{4},@{5},@{6},@{7},{8})", 
                        book.Id, book.Title, book.Language, book.PublicationDate, book.Publisher, book.Edition, book.OfficialUrl, book.Description, book.EBookFormat);
                   try
                   {
                       ExecuteQuery(txtSqlQuery);
                   }
                   catch (Exception ex )
                   {
                       throw new Exception(ex.Message);
                   }    
}

My db is correctly created and passed book instance with valid data is ok. But exception is thrown always on executing query on this line of code:

sql_cmd.ExecuteNonQuery();

I obviously doing something wrong here but I cannot see.

Update: thrown exception message is

SQL logic error or missing database

unrecognized token: "22cf"

where this 22cf is part of passed book.Id guid string.

I noticed that you are not using formal parameters to your SQL command. This is not very much advisable, because you will have to worry about presenting data into a SQL statement (i.e formatting dates, decimal separators, etc...). — Gerardo Lima, Oct 21 '13 at 08:35
@ user2783193 can you please tell how the argument `book` looks? Is the argument to `Create` method a list? — Aim, Nov 21 '22 at 09:15

score 56 · Answer 1 · edited May 17 '19 at 14:38

56

Don't EVER insert your data in your statement!

Use prepared statements and bind parameters:

public void Create(Book book) {
    SQLiteCommand insertSQL = new SQLiteCommand("INSERT INTO Book (Id, Title, Language, PublicationDate, Publisher, Edition, OfficialUrl, Description, EBookFormat) VALUES (?,?,?,?,?,?,?,?,?)", sql_con);
    insertSQL.Parameters.Add(book.Id);
    insertSQL.Parameters.Add(book.Title);
    insertSQL.Parameters.Add(book.Language);
    insertSQL.Parameters.Add(book.PublicationDate);
    insertSQL.Parameters.Add(book.Publisher);
    insertSQL.Parameters.Add(book.Edition);
    insertSQL.Parameters.Add(book.OfficialUrl);
    insertSQL.Parameters.Add(book.Description);
    insertSQL.Parameters.Add(book.EBookFormat);
    try {
        insertSQL.ExecuteNonQuery();
    }
    catch (Exception ex) {
        throw new Exception(ex.Message);
    }    
}

edited May 17 '19 at 14:38

jjxtra

20,415
16
100
140

answered Oct 21 '13 at 08:40

LS_ᴅᴇᴠ

10,823
1
23
46

1

This example does not work if parameters are not string type. – Igor Levicki Jan 09 '16 at 16:10
2

That's not correct: [SqlParameterCollection.Add](https://msdn.microsoft.com/en-us/library/ms136439.aspx) accepts Object values. Of course, implementations will convert .NET types to appropriate database types. SQLite implementation accepts string types but also numeric types, null, ... – LS_ᴅᴇᴠ Jan 11 '16 at 01:57
This is SQLiteParameterCollection.Add(), but you are right -- it does accept Object. I must have made some mistake when I was testing. In any case, IntelliSense is not showing that version of Add() once you type the opening parenthesis (it shows it only when you type . before Add). – Igor Levicki Jan 16 '16 at 10:22
How can I do nested or recursive additions if the object is complex? – AbsoluteSith Nov 17 '16 at 08:20
6

This does not seem to be valid. You get a casting error complaining that your object is not of type `SQLiteParameter`. – Herohtar Jun 19 '19 at 16:36
Silly question, a few years too late, but if you wanted to add multiple records in one `INSERT` while using prepared statements, how would you do that? This seems to be hardcoded to one `INSERT` at a time, even though you can insert multiple rows in one `INSERT` statement – JPMC Mar 17 '20 at 04:51
Hi JPMC, standard (and probably faster) way of doing this is using transactions: create a prepared statement once like above, start with `BEGIN`, execute same prepared statement many times, using different parameters at will, and finally finish with `COMMIT`. – LS_ᴅᴇᴠ Mar 17 '20 at 10:48

Viraj Sandaruwan · Answer 2 · 2021-12-28T06:53:07.007

Update most recent version using Dapper (Dapper.2.0.90) and SQLite (System.Data.SQLite.Core.1.0.114.2)

using System.Data.SQLite;
using Dapper;

connection string (DB name - 'Store.db')

connectionString="Data Source=.\Store.db;Version=3;"

Person save method in my application

    public static void SavePerson(PersonModel person)
    {
        using (IDbConnection cnn = new SQLiteConnection(connectionString))
        {
            cnn.Execute("insert into Person (FirstName, LastName) values (@FirstName, @LastName)", new { person.FirstName, person.LastName} );
        }
    }

person model

public class PersonModel
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }

    public string FullName
    {
        get
        {
            return $"{ FirstName } { LastName }";
        }
    }
}

This answer includes the code methods I used and use in my applications.

Maghalakshmi Saravana · Answer 3 · 2020-02-25T04:40:25.157

To insert the records into the SqliteDB:

using System.Data.SQLite;

    private void btnInsert_Click(object sender, RoutedEventArgs e)
    {
     string connection = @"Data Source=C:\Folder\SampleDB.db;Version=3;New=False;Compress=True;";
     SQLiteConnection sqlite_conn = new SQLiteConnection(connection);
     string stringQuery ="INSERT INTO _StudyInfo"+"(Param,Val)"+"Values('Name','" + snbox.Text + "')";//insert the studyinfo into Db
     sqlite_conn.Open();//Open the SqliteConnection
     var SqliteCmd = new SQLiteCommand();//Initialize the SqliteCommand
     SqliteCmd = sqlite_conn.CreateCommand();//Create the SqliteCommand
     SqliteCmd.CommandText = stringQuery;//Assigning the query to CommandText
     SqliteCmd.ExecuteNonQuery();//Execute the SqliteCommand
     sqlite_conn.Close();//Close the SqliteConnection
    }

score -4 · Answer 4 · answered Oct 20 '13 at 15:32

-4

You should use (') when sending string into INSERT statement VALUES (@{0},'@{1}','@{2}','@{3}','@{4}','@{5}','@{6}','@{7}',{8}) you should also catch SQLExeprion

answered Oct 20 '13 at 15:32

Izikon

902
11
23

Great answer, I would probably use named params tho as suggested here http://stackoverflow.com/questions/809246/adding-parameters-in-sqlite-with-c-sharp Why? because in some of my sql statement you might be using the same parameter a few different times. – infocyde May 29 '14 at 00:09

SQLite simple insert query

4 Answers4

Linked

Related