When Should "Sqldbtype" and "Size" Be Used When Adding SQLcommand Parameters

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

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.

What Size Value Should Be Used When Adding a Parameter of SqlDbType.Text?

The text datatype was deprecated in sql server 2005 in favor of varchar(max). You can read more about that datatype here. https://docs.microsoft.com/en-us/sql/t-sql/data-types/ntext-text-and-image-transact-sql?view=sql-server-2017

What SqlDbType should be used when creating a table?

You can't parameterize your table name or column names or any other database objects. You can only parameterize your values.

You can still using string concatenation it but getting your table name as an input is too risky in my opinion. You should either perform very stringent validation on the table name before putting it into the SQL, or have a white-listed set of valid table names, in order to avoid SQL Injection attacks.

Other than that, parameterized statements is only for DML Statements not DDL statements.

SqlCommand Parameters size confusion

For the types with fixes size you should omit this argument, simply:

sqlcommand.Parameters.Add("@LinkID", SqlDbType.Int).Value = linkID;

The size argument is only relevant for parameters with a type that can have variable size like varchar, nvarchar etc.

In SqlCommand.Parameter.Add() method, length Parameter is compulsory or not?

in this code length of parameter is compulsory or not?

I don't think it is compulsory. But it would be a good practice when you clarify it. SqlParameterCollection.Add(String, SqlDbType) takes SqlDbType as a second parameter and the length of parameter is not required. Just a tip; if your column is varchar(max), then you should use VarChar as a db type.

and if we are not using length parameter in this method any
performance or SQL injection related issue is occurred ?

Performance issue looks irrelevant because length is not must. And since you use parameterized sql in your queries, you should not worry about SQL Injection attacks.

SQLCommand.Parameters.Add - How to give decimal value size?

There's not an overload of Add that lets you set the decimal precision inline, so you either need to create a SQlParameter object and add it to the collection:

SqlParameter param = new SqlParameter("@myValue", SqlDbType.Decimal);
param.SourceColumn = "myValue";
param.Precision = 18;
param.Scale = 2;
SqlComm.Parameters.Add(param);

or keep a reference to the parameter after adding it:

SqlParameter param = SqlComm.Parameters.Add("@myValue", SqlDbType.Decimal, 0, "myValue");
param.Precision = 18;
param.Scale = 2;

or using the parameter constructor:

SqlComm.Parameters.Add(new SqlParameter(
    parameterName = "@myValue", 
    dbType = SqlDbType.Decimal,
    precision = 18,
    scale = 2,
    sourceColumn = "myValue"));

What size do you use for varchar(MAX) in your parameter declaration?

In this case you use -1.

SqlCommand Parameters Add vs. AddWithValue

Use Add if you want to make all explicit with a little bit more work. Use AddWithValue if you are lazy. AddWithValue will derive the type of the parameter of its value, so ensure that it's the correct type. You should, for example, parse a string to int if that is the correct type.

There is one reason to avoid Add: if your parameter type is int you must be careful with the overload that takes the parameter-name and an object since then another overload is chosen with the SqlDbType-enum.

From remarks (method overload is even obsolete now):

Use caution when you are using this overload of the
SqlParameterCollection.Add method to specify integer parameter values.
Because this overload takes a value of type Object, you must convert
the integral value to an Object type when the value is zero
... If you do not perform this conversion, the
compiler assumes that you are trying to call the
SqlParameterCollection.Add(string, SqlDbType) overload.

---

Related Topics