When should “SqlDbType” and “size” be used when adding SqlCommand Parameters?

by

In my experience, I would make sure I do these things:

  • make sure it’s you that defines the data type for the parameter. ADO.NET does a decent job at guessing, but in some cases, it can be terribly off – so I would avoid this method:

    cmd.Parameters.Add("@Name").Value = "Bob";
cmd.Parameters.AddWithValue("@Name", "Bob");

    Letting ADO.NET guess the type of the parameter by the value passed is tricky, and if it’s off for any reason, those are really tricky bugs to track and find! Imagine what happens when you pass in a DBNull.Value – what datatype should ADO.NET pick for that?

    Just be explicit – say what type it is you want!

  • if you’re using string parameters, make sure to explicitly define the length – so I would avoid this method, too:

    cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = "Bob";

    If you don’t provide a length, ADO.NET might default to some arbitrary value, or the length of the string passed in as a value, or something else – you’re never quite sure. And if your length doesn’t match what the stored proc really expects, you might see conversion and other unpleasant surprises. So if you define a string, define its length, too!

So in your case, the only approach that really works for me is this one here:

cmd.Parameters.Add("@Name", SqlDbType.VarChar, 20).Value = "Bob";

because it a) defines the data type to use explicitly, and b) defines the length of the string explicitly.

More Related Contents:

  1. Insert into C# with SQLCommand
  2. ExecuteReader requires an open and available Connection. The connection’s current state is Connecting
  3. MetadataException: Unable to load the specified metadata resource
  4. Get output parameter value in ADO.NET
  5. SQLite Database Locked exception
  6. Under what circumstances is an SqlConnection automatically enlisted in an ambient TransactionScope Transaction?
  7. Is it necessary to manually close and dispose of SqlDataReader?
  8. Populate data table from data reader
  9. When using Trusted_Connection=true and SQL Server authentication, will this affect performance?
  10. Difference with Parameters.Add and Parameters.AddWithValue
  11. SqlConnection SqlCommand SqlDataReader IDisposable
  12. Exception when AddWithValue parameter is NULL
  13. Calling Oracle stored procedure from C#?
  14. Is SqlCommand.Dispose() required if associated SqlConnection will be disposed?
  15. How to run multiple SQL commands in a single SQL connection?
  16. MultipleActiveResultSets=True or multiple connections?
  17. How to pass a table-value parameter
  18. How DataReader works?
  19. How to keep single SQL Server connection instance open for multiple request in C#?
  20. DataTable equivalent in Java [duplicate]
  21. How do I re-write a SQL query as a parameterized query?
  22. How do I translate a List into a SqlParameter for a Sql In statement? [duplicate]
  23. Check if a SQL table exists
  24. What is the difference between dataview and datatable?
  25. Replacing a DataReader with a DataTable
  26. Reading a date using DataReader
  27. What ‘length’ parameter should I pass to SqlDataReader.GetBytes()
  28. How do I connect to a SQL database from C#?
  29. .net connection pooling
  30. Do we have transactions in MS-Access?

Leave a Comment