CODEWITHSUNDEEP
c#.netsqlite
user2783193
user2783193

Reputation: 1012

SQLite simple insert query

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.

Upvotes: 19

Views: 85300

Answers (4)

Maghalakshmi Saravana
Maghalakshmi Saravana

Reputation: 813

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
}

Upvotes: -3

Viraj_Zanda
Viraj_Zanda

Reputation: 101

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.

Upvotes: 1

LS_ᴅᴇᴠ
LS_ᴅᴇᴠ

Reputation: 11151

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

Upvotes: 58

Izikon
Izikon

Reputation: 902

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

Upvotes: -4

Related Questions