hackingchemist hackingchemist - 1 month ago 16
ASP.NET (C#) Question

SqlCommand VarChar or Int?

I am wondering which is the "correct" way of adding parameters to a SQLCommand. It seems to me its best to match the data types. However, is parsing the string to Int better than using the .ToString() method performance wise? Does it go against good practice? SQL server doesn't seem to care. id is of type int in the table.

command.CommandText = "SELECT name FROM table WHERE id=@ID";
command.Parameters.Add("@ID", System.Data.SqlDbType.Int).Value = int.Parse(Request.QueryString["id"]);


OR

command.CommandText = "SELECT name FROM table WHERE id=@ID";
command.Parameters.Add("@ID", System.Data.SqlDbType.VarChar).Value = Request.QueryString["id"].ToString();

Answer

int type in SQL Server is mapped in Int32 in CLR side. That's why the "correct" way is adding a parameter which is SqlDbType.Int. Period.

However, is parsing the string to Int better than using the .ToString() method performance wise?

Sending a wrong parameter typed value as a parameter might generate more serious problems than a performance. Looks like "performance" is not even a real case in your example since you never mentioned any performance issue in your work.

SQL server doesn't seem to care

But as a developer, you should.