title: Pros and Cons of using SqlCommand Prepare in C#?
body: <p>When i was reading books to learn C# (might be some old <code>Visual Studio 2005</code> books) I've encountered advice to always use <code>SqlCommand.Prepare</code> everytime I execute SQL call (whether its' a <code>SELECT</code>/<code>UPDATE</code> or <code>INSERT</code> on SQL SERVER 2005/2008) and I pass parameters to it. <strong>But is it really so</strong>? </p>
  
  <ol>
  <li><p>Should it be done every time? Or just sometimes? </p></li>
  <li><p>Does it matter whether it's one parameter being passed or five or twenty? </p></li>
  <li><p>What boost should it give if any? Would it be noticeable at all (I've been using <code>SqlCommand.Prepare</code> here and skipped it there and never had any problems or noticeable differences). </p></li>
  </ol>
  
  <p>For the sake of the question this is my usual code that I use, but this is more of a general question. </p>
  
  <pre><code>public static decimal pobierzBenchmarkKolejny(string varPortfelID, DateTime data, decimal varBenchmarkPoprzedni, decimal varStopaOdniesienia) {
      const string preparedCommand = @"SELECT [dbo].[ufn_BenchmarkKolejny](@varPortfelID, @data, @varBenchmarkPoprzedni,  @varStopaOdniesienia) AS 'Benchmark'";
      using (var varConnection = Locale.sqlConnectOneTime(Locale.sqlDataConnectionDetailsDZP)) //if (varConnection != null) {
      using (var sqlQuery = new SqlCommand(preparedCommand, varConnection)) {
          sqlQuery.Prepare();
          sqlQuery.Parameters.AddWithValue("@varPortfelID", varPortfelID);
          sqlQuery.Parameters.AddWithValue("@varStopaOdniesienia", varStopaOdniesienia);
          sqlQuery.Parameters.AddWithValue("@data", data);
          sqlQuery.Parameters.AddWithValue("@varBenchmarkPoprzedni", varBenchmarkPoprzedni);
          using (var sqlQueryResult = sqlQuery.ExecuteReader())
              if (sqlQueryResult != null) {
                  while (sqlQueryResult.Read()) {
  
                  }
              }
      }
  }
  </code></pre>
  
  <p>Additional clarification: </p>
  
  <p>If i move <code>sqlQuery.Prepare()</code> like in code below exception is thrown that the size has to be explicitly declared, which basically leads me to thinking that having <code>sqlQuery.Prepare()</code> as first makes it useless? Can someone show the proper usage using my example?</p>
  
  <pre><code>public static decimal pobierzBenchmarkKolejny(string varPortfelID, DateTime data, decimal varBenchmarkPoprzedni, decimal varStopaOdniesienia) {
      const string preparedCommand = @"SELECT [dbo].[ufn_BenchmarkKolejny](@varPortfelID, @data, @varBenchmarkPoprzedni,  @varStopaOdniesienia) AS 'Benchmark'";
      using (var varConnection = Locale.sqlConnectOneTime(Locale.sqlDataConnectionDetailsDZP)) //if (varConnection != null) {
      using (var sqlQuery = new SqlCommand(preparedCommand, varConnection)) {
  
          sqlQuery.Parameters.AddWithValue("@varPortfelID", varPortfelID);
          sqlQuery.Parameters.AddWithValue("@varStopaOdniesienia", varStopaOdniesienia);
          sqlQuery.Parameters.AddWithValue("@data", data);
          sqlQuery.Parameters.AddWithValue("@varBenchmarkPoprzedni", varBenchmarkPoprzedni);
          sqlQuery.Prepare();
          using (var sqlQueryResult = sqlQuery.ExecuteReader())
              if (sqlQueryResult != null) {
                  while (sqlQueryResult.Read()) {
  
                  }
              }
      }
  }
  </code></pre>
  
  <p>How would I do that? By adding .size next to parameters and doing varPortfel.Lenght if it's a string etc? </p>