SQLite INSERT语句 [英] SQLite INSERT Statement

问题描述

我创建了以下插入方法，该方法效果很好，但我知道它可能更有效.有人可以告诉我如何将这种方法转换为使用参数和/或提高效率吗?

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

    }
}

推荐答案

您确实应该将其切换为使用预处理语句，然后将数据绑定到该预处理语句中的参数.这里介绍了C/C ++的基础知识:

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

我怀疑您使用的是dotConnect，因此您可能希望在具体示例中参考以下内容:

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

适用相同的原则.您将SQL作为单个字符串常量编写，可以在源代码中轻松读取，查看和修改.然后，将该SQL命令与一组数据元素一起发送到SQLite，以替换每个参数.这种技术可以使您的代码更清晰，并有助于避免SQL注入攻击或混淆.

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.

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