SQLite INSERT Statement

Question

I have create the following insert method by researching the internet but I know it needs some work since it quite bulky and unsightly. I have tested it and it works fairly well but I know it could work better. If possible could someone show me how to convert this method to use parameters and/or increase its efficency?

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

        }
    }

Thanks, Nathan

Answer 1

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.