SQLite的简单插入查询 [英] SQLite simple insert query

问题描述

我尝试使用SQLite作为我的存储。我使用的NuGet和using语句，以及添加引用的DLL。

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

我

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

像这样的

和我发送的查询TXT

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

我的数据库是正确的，且与有效数据通过本书的实例就可以了。但例外总是扔在这条线的code执行的查询：

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逻辑错误或丢失的数据库

SQL logic error or missing database

无法识别标记：22cf

unrecognized token: "22cf"

如果本 22cf 是通过 book.Id GUID字符串的一部分。

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

推荐答案

永远不要将您的数据在你的发言！

使用prepared语句和绑定参数：

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

这篇关于SQLite的简单插入查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！