Until I figure out how to use an ORM package, which might be soon, I will continue to do direct database CRUD code. One thing I have heard which helps make code more secure is using SQL Parameters. Well this was a good idea until I tried to figure it out. Over the last couple of years I have just tried to find the code to get it to work. With a project I just started yesterday it I decided to figure out what was going on and now I want others to know as some stuff on the web, tutorial wise, can be quite confusing.
Getting Started
I am only going to show you them method that was used to do the database stuff and for the sake of this blog post I am going to assume you know how to use the data reader. If you don't please let me know and I will make a post on it specifically. There are basically 3 lines I want to cover with you and explain what they are doing to help you understand what is going on and how to use parameters in your SQL Queries. First though the code.
Now that you have had a chance to review the code a bit lets take a look out our query.
string query = "SELECT * FROM TestTable WHERE ID = @id";
This is a normal select statement but notice the WHERE ID = @id this the @id signifies it is a parameter to be used and what ever is passed to it will be like normal query without it. Think of it as a variable that you would use.
SqlCommand command = new SqlCommand(query, conn);
This is an object we use that will help us build up our query and execute it against the database.
command.Parameters.Add("id", SqlDbType.Int).Value = id;
This here is the main part of what we are after. First, we are using the SqlCommand object and in the object it has a Parameters collection that holds all the parameters a query might have. Since it is a collection we can just call the Add method and the first parameter that metho takes tells the object the parameter name is id for the @id in our query string. The second parameter the method takes is they datatype which it uses a Struct called SqlDbType to help you fill that it so it is fairly easy to do. Next we can give the sql parameter a value in this case the id we passed to the method.
Please take a moment to really compare and look at what is going on by understanding what it is doing you can better know how to use it so you don't need to look it up everytime.
Wrap-Up
This was meant to be quick so you can get an idea of what is going on. I am including a sample project with the post please feel free to download it. I have included sql scripts to create the database, create the table and add data to the table. Please just run those scripts then edit the connString variable with the write server name for you sql server. Just to note I did this in .NET 3.5.
DatabaseParameter.zip (7.41 kb)
{ 1 comment… read it below or add one }
Hi,
It is not safe to instantiate a SqlConnection object outside the “using” block.