1

NOTE: This is not a duplicate of What does SqlCommand.Prepare() do and when should it be used? or SQLite/C# Connection Pooling and Prepared Statement Confusion or other stack overflow questions. I am not asking whether prepared statements are appropriate for my use case. I am asking how to use them together with IDisposable. END NOTE

I have a c# program that uses about 100 SQL statements. I am creating a SQLiteCommand inside a using block each time I need it:

string query = @"select ... ";
using (SQLiteCommand cmd = new SQLiteCommand(query, conn)) 
{
    cmd.Parameters.Add(new SQLiteParameter( ... ));
    using (SQLiteDataReader dr = cmd.ExecuteReader()) 
    {
        while (dr.Read()) {
        ...
        }
    }
}

I would like to improve performance by using prepared statements, so that each statement is compile once and used repeatedly thoughout the lifetime of the program.

My dilemma is that DbCommand implements I Disposable, and every example I see shows the command being disposed as soon as the SQL statement is executed.

Is it OK to not dispose of DbCommand after executing a SQL statement? What side effects would that have? Alternatively, if I do dispose of the command, how do I achieve the "compile once execute multiple times" flow.

Community
  • 1
  • 1
George
  • 2,436
  • 4
  • 15
  • 30
  • you will possibly run into things like `Object not set to Instance` if not disposed and or Instantiated properly, you can run into high memory usage and or memory leaks, the `GC` is not always immediate.. the using in my opinion is a much better approach also in stead of repeating a ton of duplicate code everywhere in your solution create a Custom Class that handles taking in the Sql as a Parameter, also if you are only returning data.. have a method return a `DataTable` etc.. if you need an example of how it's done I can post an example – MethodMan Nov 20 '15 at 21:09
  • Before going down this path, have you measured and compared the metrics of a prepared statement (and keeping it around in memory) vs. creating them when needed? – Clay Ver Valen Nov 20 '15 at 21:27

0 Answers0