title: When should "SqlDbType" and "size" be used when adding SqlCommand Parameters?
body: <p>There is a related question to this:</p>
  
  <p><a href="https://stackoverflow.com/questions/293311/whats-the-best-method-to-pass-parameters-to-sqlcommand">What&#39;s the best method to pass parameters to SQLCommand?</a></p>
  
  <p>But I am wanting to know what the differences are and if there are any problems with the different ways.</p>
  
  <p>I usually use a structure something like this:</p>
  
  <pre><code>using (SqlConnection conn = new SqlConnection(connectionString))
  using (SqlCommand cmd = new SqlCommand(SQL, conn))
  {
       cmd.CommandType = CommandType.Text;
       cmd.CommandTimeout = Settings.Default.reportTimeout;
       cmd.Parameters.Add("type", SqlDbType.VarChar, 4).Value = type;
  
       cmd.Connection.Open();
  
       using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
       {
           adapter.Fill(ds);
       }
        //use data                    
  }
  </code></pre>
  
  <p>Now there are several ways to add the cmd parameters and I am wondering which is best:</p>
  
  <pre><code>cmd.Parameters.Add("@Name", SqlDbType.VarChar, 20).Value = "Bob";
  cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = "Bob";
  cmd.Parameters.Add("@Name").Value = "Bob";
  cmd.Parameters.AddWithValue("@Name", "Bob");
  </code></pre>
  
  <p>Having the length of the field in the passing of the varchars I assume is not preferable as it's a magic value which may be changed later at the database. Is this correct? Does it cause any issue passing a varchar in this way (performance or other), I assume it defaults to varchar(max) or the database equivalent. I am reasonably happy this will work.</p>
  
  <p>The part that concerns me more is the loss of the SqlDbType enum if I am using the third or fourth options I listed above I am not supplying a type at all. Are there cases where this won't work I can imagine problems with varchar being incorrectly cast to char or vice versa or perhaps issues with decimal to money.... </p>
  
  <p>In terms of the database the field type I would say is much less likely to change than the length so is it worth retaining?</p>