SQLite INSERT 语句
我创建了以下插入方法,该方法运行得相当好,但我知道它可能会更有效。有人可以告诉我如何将此方法转换为使用参数和/或提高其效率吗?
public static void SQLiteTableINSERT(string tableName)
{
int colCount = 0;
using (SQLiteConnection Conn = new SQLiteConnection(SQLiteConn.Conn))
{
using (SQLiteTransaction sqliteTrans = Conn.BeginTransaction())
{
using (SQLiteCommand cmd = Conn.CreateCommand())
{
DataTableColumnNames();
string query = "INSERT INTO " + tableName + "(";
foreach (string name in DtColumns)
{
query += "[" + name + "]";
++colCount;
if (colCount < DtColumns.Count())
query += ",";
}
query += ")";
query += " VALUES(";
for (int i = 0; i < LocalDataSet.LocalDs.Tables[0].Rows.Count; ++i)
{
cmd.CommandText = query;
foreach (DataColumn col in LocalDataSet.LocalDs.Tables[0].Columns)
{
string temp = LocalDataSet.LocalDs.Tables[0].Rows[i][col, DataRowVersion.Current].ToString();
if (temp == "True")
cmd.CommandText += 1;
else if (temp == "")
cmd.CommandText += 0;
if (temp != "True" && temp != "")
cmd.CommandText += "'" +temp + "'";
cmd.CommandText += ",";
}
cmd.CommandText = cmd.CommandText.Remove(cmd.CommandText.LastIndexOf(","));
cmd.CommandText += ")";
cmd.ExecuteNonQuery();
}
}
sqliteTrans.Commit();
}
}
}
I have created the following insert method which works fairly well but I know it could be more efficient. Could someone show me how to convert this method to use parameters and/or increase its efficiency?
public static void SQLiteTableINSERT(string tableName)
{
int colCount = 0;
using (SQLiteConnection Conn = new SQLiteConnection(SQLiteConn.Conn))
{
using (SQLiteTransaction sqliteTrans = Conn.BeginTransaction())
{
using (SQLiteCommand cmd = Conn.CreateCommand())
{
DataTableColumnNames();
string query = "INSERT INTO " + tableName + "(";
foreach (string name in DtColumns)
{
query += "[" + name + "]";
++colCount;
if (colCount < DtColumns.Count())
query += ",";
}
query += ")";
query += " VALUES(";
for (int i = 0; i < LocalDataSet.LocalDs.Tables[0].Rows.Count; ++i)
{
cmd.CommandText = query;
foreach (DataColumn col in LocalDataSet.LocalDs.Tables[0].Columns)
{
string temp = LocalDataSet.LocalDs.Tables[0].Rows[i][col, DataRowVersion.Current].ToString();
if (temp == "True")
cmd.CommandText += 1;
else if (temp == "")
cmd.CommandText += 0;
if (temp != "True" && temp != "")
cmd.CommandText += "'" +temp + "'";
cmd.CommandText += ",";
}
cmd.CommandText = cmd.CommandText.Remove(cmd.CommandText.LastIndexOf(","));
cmd.CommandText += ")";
cmd.ExecuteNonQuery();
}
}
sqliteTrans.Commit();
}
}
}
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
您确实应该将其切换为使用准备好的语句,然后将数据绑定到该准备好的语句中的参数。这里解释了 C/C++ 的基础知识:
http://www.sqlite.org/cintro.html< /a>
我怀疑您正在使用 dotConnect,因此您可能需要参考以下具体示例:
http://www.devart.com/dotconnect/sqlite/docs/Parameters.html
同样的原则适用。您可以将 SQL 编写为单个字符串常量,以便在源代码中轻松阅读、查看和修改。然后,将该 SQL 命令与一组数据元素一起发送到 SQLite 以替换每个参数。这种技术使您的代码更加清晰,并有助于避免 SQL 注入攻击或混淆。
You really should switch this to using a prepared statement, then binding the data to parameters in that prepared statement. The basics are explained here for C/C++:
http://www.sqlite.org/cintro.html
I suspect that you are using dotConnect so you might want to refer to this for your specific example:
http://www.devart.com/dotconnect/sqlite/docs/Parameters.html
Same principle applies. You write the SQL as a single string constant that is easy to read, review and modify in your source code. Then you send that SQL command to SQLite along with a set of data elements to replace each of the parameters. This technique makes your code clearer and helps to avoid SQL injection attacks, or mixups.