System.Data.SQLite

Good day guys, I have a question:

Is it the normal behaviour for the parameters in parameterized queries to only support 1 item?

Here's my scenario: I need to write an INSERT statement to add an array of doubles, but I do not know the number of elements beforehand. So I can't just declare fixed parameters like "@data1, @data2, @data3". It has to be flexible.

The code I tried (which failed to run) is something like:

public void StoreData(double[ data)
{            
    // Open DB connection
    
    using (IDbTransaction transaction = m_dbConn.BeginTransaction())
    {
        //Parameterized queries to speed up inserting
        using (IDbCommand command = m_dbConn.CreateCommand())
        {
            /* Use a StringBuilder to build the list of column names so it will come out like "column1, column2, column3", ready to be inserted into INSERT statement */

           /* Use a StringBuilder to build the list of column values so it will come out like "23.4, 15, 8.95", ready to be inserted into INSERT statement */

            command.Transaction = transaction;
            command.CommandText = "INSERT INTO '" + m_tableName + "' (@columnNames) VALUES (@columnValues)";
            IDataParameter columnNames = new SQLiteParameter("@columnNames");
            IDataParameter columnValues = new SQLiteParameter("@columnValues");
            command.Parameters.Add(columnNames);
            command.Parameters.Add(columnValues);

            columnNames.Value = string_columnNames;
            columnValues.Value = string_columnData;

            command.ExecuteNonQuery();
        }

        transaction.Commit();
    }
}

That code fails with the syntax error exception on @columnNames parameter. If i'm not mistaken, it's the presence of the comma that's tripping it up? It only accepts 1 value?

So how will you guys tackle this problem? I do not want to use an in-line command like:
"INSERT INTO " + m_tableName + " (" + stringColumnNames + ") VALUES (" + stringColumnValues + ")"

Obviously that will be slow as the data can be coming in up to 250000 points per second!

Would appreciate the community's thoughts on going about this the right way. Thanks!

 Please, could you post table schema?

SqlRanger:

 Please, could you post table schema?

The table schema varies with each instance, but it's straight forward:

Users specify how many data inputs are available. E.g. 5 inputs will cause 6 columns to be created (1 PK and 5 data columns). The columns store numeric data. In this case, every time a new input comes in, I can be assured there will be 5 data values to Insert.

It's customizing the query to be able to handle 10, 2, 64 data columns that I haven't figured out.

Thanks! Would love to hear yr thoughts.

Shawn

Regarding your schema design, IMHO I would say that it is not very appropriate to have one table per each posible number of inputs.If column1 and column2, etc, all mean the same thing, it would be more appropriate to have only one table with just PK and Value columns, and perhaps some additional meaning column such as date or something like that.

Anyway, you cannot specify column or tables as parameters, only values can be specified as parameters. So your code should be something like this:

pre{font-family:Courier New; font-size:10.0pt;}

public void StoreData(double[ data)
{                
    using (DbTransaction transaction = m_dbConn.BeginTransaction())
    {
        //Parameterized queries to speed up inserting
        using (DbCommand command = m_dbConn.CreateCommand())
        {
            StringBuilder columnNames = new StringBuilder();
            StringBuilder valueNames = new StringBuilder();

            DbParameter[ parameters = new DbParameter[data.Length];

            for (int columnIndex = 0; columnIndex < data.Length; columnIndex++)
            {
                if (columnIndex == 0)
                {
                    columnNames.Append("(");
                    valueNames.Append("(");
                }
                else
                {
                    columnNames.Append(", ");
                    valueNames.Append(", ");
                }

                string columnName = "Column" + (columnIndex + 1).ToString();
                columnNames.Append(columnName);

                string valueName = "@" + columnName;
                valueNames.Append(valueName);

                DbParameter param = command.CreateParameter();
                param.ParameterName = valueName;
                param.DbType = DbType.Double;
                param.Value = data[columnIndex];

            }
            columnNames.Append(")");
            valueNames.Append(")");

            command.Transaction = transaction;
            command.CommandText = "INSERT INTO " + m_tableName + columnNames.ToString() + " VALUES " + valueNames.ToString();
            command.Parameters.AddRange(parameters);

            command.ExecuteNonQuery();
        }

        transaction.Commit();
    }
} 

Regarding performance, parameterized queries improve performance only when they are reutilized. In your code, (and in my code), there is not parameterized query reutilization, the quey is executed only once, so you will not see any performance gain by using a parameterized query.

Transactions improve performance, but only when you execute many inserts, updates or deletes in a transaction. In your code (and in my code) there is not performance gain due to the transaction, because only one insert is executed within the transaction.

Jesús,

Thanks fr yr informative reply! Your code looks like what I need.

For my application, this schema works because I always SELECT the entire row, never the individual data. I'm assuming this is better than to do multiple JOINs if I split each column up?

Question: Can i reuse Command objects in different transactions? (Provided I set the right .Transaction property?)

My plan to improve performance is to separate the query building code into a method called during initialization. Then, SetData() will only do INSERTing with the pre-initialized query along with the latest values. Any pitfalls to using this approach?

Thanks again for yr gr8 help!