SqlCommand Parameters

Today i will discuss the sqlCommand Parameters which i have not used before this, and i was unaware of the power of the sqlCommand Parameter. Before this i was using the dynamic query by concatenating the string and then executing that query to fetch, update delete the record from database.
I have read the following 2 article Dynamic SQL Needs to die and Dynamic SQL Can Die Automatically and i find these article very useful.
Lets start by Example then i will give the conclusion at the end.
i have created simple table name tblUser in the northwind database and table name is tblUser and is shown below.

and i have following one value in the database for my testing.


The user interface is quite simple which consist of User name , password and login button. In the image below i have set the value of the user name and password which are use name is "asim" and Password "123'3". As you can see in my example input , for user i have not set the password field property " TextMode" to Password.So that user can see the input clearly.

No when i execute the sqlcommand for dynamic query which use the concatenation of the values with the string and generate the query you can see clearly in the image blog that it will give Exception of "Incorrect syntax near '3'.\r\nUnclosed quotation mark after the character string"," and this exception often occure if we don't convert single quote to double in our code.

And when i use sqlCommand with same parameters which contain single quote in the password there is not exception in the execution of the command and i got the smoth execution of the command and result. As you can see in the image blow.

Now i am happy to knew the power of sqlCommand and ready to use it in the my new projects. The Benefits of using the sqlCommand are as fellow taken from Dynamic SQL Needs to die.

1) Single Quote.

If I forgot one of the apostrophes. The code would compile and everything would appear to be fine. However, once I executed the command, it would blow up and result in a Syntax error. This is an easy mistake to make no matter how careful you are. Also, pretend you had 15 values in the Where clause. It'd be a feat indeed to get it right the first time. Another common thing that comes up is that you can just write a function that changes the single quotes to double quotes so it can be used. Well, you'll have to call this on every SQL statement to safely assume it will work. You'll probably forget to do it at least once, and other programmers may forget it or not know that you have this function when they maintain your code.

2) Data Type

Another example is Date fields. Different RDBMS implementations require a different Date Format. It's very easy to forget about this and forget one of the critical formatting chunks. Again, if you are totally careful you probably won't make many mistakes here, but your new programmers probably will. Someone editing your code probably will etc.Another problem is fields that have an apostrophe in them. This happens so often it's amazing.

3) It's a preformance killer.

Your query won't be able to take advantage of cached execution plans. That means that the same query can hit the db over and over again and each time the Server won't be able to reuse plans it's already cached.