CODEWITHSUNDEEP
c#sqlitesystem.data.sqlite
Brian Heilig
Brian Heilig

Reputation: 648

How to escape string in System.Data.SQLite without prepared statements

How can I escape strings in System.Data.SQLite without using parameters? I'm building a potentially large INSERT query like so.

string sql = "INSERT INTO Request (Col1, Col2, Col3) VALUES ";
bool hasValue = false;
foreach (TestItem item in order.Tests)
{
    if (hasValue)
        sql += ", ";
    hasValue = true;

    sql = sql + String.Format("('{1}', '{2}', '{3}')", sql, a, b, c);
}

using (SQLiteCommand command = new SQLiteCommand(sql, _database))
    command.ExecuteNonQuery();

I could break each insert into a separate query, but wouldn't it be more efficient to escape the strings myself? I could generate query parameters dynamically, but couldn't the parameter list get too large?

Upvotes: 1

Views: 956

Answers (1)

ryancdotnet
ryancdotnet

Reputation: 2260

If efficiency is your concern, I would look at performing multiple inserts separately, with parameters (for safety), but within a Transaction. SQLite performance is terrible when inserting row by row, but if you can wrap all inserts inside a transaction, you will see much better performance.

Here's a good article on this:

https://www.jokecamp.com/blog/make-your-sqlite-bulk-inserts-very-fast-in-c/

And for futher clarification, the article deep links to this FAQ direct from SQLite, which I find helpful:

http://www.sqlite.org/faq.html#q19

Upvotes: 2

Related Questions