title: SQLite/C# Connection Pooling and Prepared Statement Confusion
body: <p>I have been spending some time reading different best practices for databases and for SQLite specifically. While reading I found I was doing many things I shouldn't be doing and when attempting to fix these issues I became confused when thinking about some of the finer details of using SQLite with it's ADO implementation.</p>
  
  <p>My confusion stems specifically from prepared statements and connection pooling. </p>
  
  <p>While reading <a href="http://msdn.microsoft.com/en-us/library/ms971481.aspx" rel="noreferrer">http://msdn.microsoft.com/en-us/library/ms971481.aspx</a> I found that connections should only be opened for a transaction. Once the transaction is complete then the connection should be closed. I do not have a firm grasp as to why this is the case, but I have been working off the assumption that the author(s) know better then I. I understand that when a connection is closed it doesn't mean it has actually been closed. It simply means that it has been put back into the pool.</p>
  
  <p>Now to improve my queries and inserts I read about using prepared statements. <a href="https://stackoverflow.com/questions/1703203/in-sqlite-do-prepared-statements-really-improve-performance">In SQLite, do prepared statements really improve performance?</a> and <a href="http://petesbloggerama.blogspot.com/2007/02/sqlite-adonet-prepared-statements.html" rel="noreferrer">http://petesbloggerama.blogspot.com/2007/02/sqlite-adonet-prepared-statements.html</a> both seemed to indicate that when executing a query that will be done multiple times prepared statements are the way to go. I have also read that a prepared statement is specific to a connection and that once the connection is closed the prepared statement is lost.</p>
  
  <p>My confusion is this. If I am opening and closing my connection (which may or may not mean the connection is being closed due to the thread pool) then how much use am I really getting from a prepared statement? I can understand that if I have 1000 objects I need to save in a single transaction that the prepared statement can help a lot. However I don't believe I would see a benefit from saving a single object in a transaction because once I close the connection the prepared statement that was generated from the first object is now lost. Is this a true statement?</p>
  
  <p>My confusion is furthered by the fact that I believe a prepared statement is linked to the scope of my SQLiteCommand object.</p>
  
  <p>If I create a SQLiteCommand that represents a query that I will be executing often do I need to keep that SQLiteCommand in memory for the prepared statement to stay active?</p>
  
  <p>If I create a new SQLiteCommand with the same SQLite statement is it recognized that the new SQLiteCommand is the same as the previous and thus has a prepared statement that can be used?</p>
  
  <p>If I keep a SQLiteCommand in memory and change it's parameters and connection as I open and close the connection for different transactions am I essentially keeping a prepared statement alive between different connections?</p>
  
  <p>I am most likely over thinking things at this point but I hope you can help me better understand how these things interact so I can get the most benefit out of them.</p>