C# SQLite 简单插入查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19479166/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQLite simple insert query
提问by user2783193
I'm trying to use SQLite as my storage. I've added reference dll using nuget and using statement as well.
我正在尝试使用 SQLite 作为我的存储。我还使用 nuget 和 using 语句添加了参考 dll。
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
我像这样发送查询txt
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"
SQL 逻辑错误或数据库丢失
无法识别的令牌：“22cf”
where this 22cfis part of passed book.Idguid string.
这22cf是传递的book.Idguid 字符串的一部分。
回答by Izikon
You should use (') when sending string into INSERT statement VALUES (@{0},'@{1}','@{2}','@{3}','@{4}','@{5}','@{6}','@{7}',{8}) you should also catch SQLExeprion
将字符串发送到 INSERT 语句 VALUES (@{0},'@{1}','@{2}','@{3}','@{4}','@{ 5}','@{6}','@{7}',{8}) 你也应该捕获 SQLExeprion
回答by LS_???
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);
}
}
回答by Maghalakshmi Saravana
To insert the records into the SqliteDB:
将记录插入到 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
}
