title: How to correctly and efficiently reuse a prepared statement in C# .NET (SQL Server)?
body: <p>I looked at lots of questions but evidently my <em>SO-fu</em> isn't up to the task, so here I am. I am trying to efficiently use prepared statements, and I don't just mean parameterizing a single statement, but compiling one for reuse many times. My question lies around the parameters and reuse and how to implement that correctly.</p>
  
  <p>Generally I follow this procedure (contrived example):</p>
  
  <pre><code>SqlConnection db = new SqlConnection(...);
  SqlCommand s = new SqlCommand("select * from foo where a=@a", db);
  s.Parameters.Add("@a", SqlDbType.VarChar, 8);
  s.Prepare();
  ...
  s.Parameters["@a"] = "bozo";
  s.Execute();
  </code></pre>
  
  <p>Super, that works. However, I don't want to do all of these steps (or the latter four) every time I run this query. That seems like it's counteracting the whole idea of prepared statements. In my mind I should only have to change the parameters and re-execute, but the question is how to do that?</p>
  
  <p>I tried <code>s.Parameters.Clear()</code>, but this actually removes the parameters themselves, not just the values, so I would essentially need to re-<code>Add</code> the parameters and re-<code>Prepare</code> again, which would seem to break the whole point as well. No thanks.</p>
  
  <p>At this point I am left with iterating through <code>s.Parameters</code> and setting them all to null or some other value. <strong>Is this correct?</strong> Unfortunately in my current project I have queries with ~15 parameters which need to be executed ~10,000 times per run. I can shunt this iteration off into a method but was wondering if there is a better way to do this (without stored procs).</p>
  
  <p>My current workaround is an extension method, <code>SqlParameterCollection.Nullify</code>, that sets all the parameters to null, which is fine for my case. I just run this after an execute.</p>
  
  <hr>
  
  <p>I found some virtually identical but (IMHO) unanswered questions:</p>
  
  <p><a href="https://stackoverflow.com/questions/6335687/prepared-statements-and-the-built-in-connection-pool-in-net">Prepared statements and the built-in connection pool in .NET</a></p>
  
  <p><a href="https://stackoverflow.com/questions/10703814/sqlite-c-connection-pooling-and-prepared-statement-confusion">SQLite/C# Connection Pooling and Prepared Statement Confusion</a> (Serge was so close to answering!)</p>
  
  <p>The best answer I could find is (1) common sense above and (2) this page:</p>
  
  <p><a href="http://msdn.microsoft.com/en-us/magazine/cc163799.aspx" rel="noreferrer">http://msdn.microsoft.com/en-us/magazine/cc163799.aspx</a></p>