I've been noticing a trend for quite some time. Lots of people seem to write applications that use databases, but they have very little idea of how databases work. There are lots of tutorials out there with bad advice. For instance, if you see someone suggesting that you use string concatenation to build your queries, that's usually bad advice. Parameterized queries are a very good thing.

This is not intended to be a general "how to use a database" tutorial. This is specifically a tutorial on how to use SQL paramters in your program. It assumes you have a basic understanding of how to perform simple queries against your database, and are familiar with concepts such as queries and connection strings.

Parameters are nice for a number of reasons. Tell me, which looks better to you?

Of course, Option 2 looks better, especially if you understand it. Parameters are like SQL's version of variables. They always start with an @ symbol. They represent a value. Notice, for example, that I didn't have to quote my parameters, even though they represent strings. That's just like in C#, where you don't have to surround your string variables with quotes when you use them, because they represent a string rather than hardcoding a string constant.

So, not only is it more readable, and you never have to worry about if you have the right quotation marks, but it's also safer. Most people never think about their SQL being safe or unsafe, but it's a big problem. Read up on SQL Injection Attacks. SQL Server will take whatever you give it and execute it. So, it's entirely possible for someone to slip in some malicious SQL into your field, if you're not careful. But parameters take care of all that for you. It escapes SQL syntax, so that all parameter content is treated as a value, never a command.

One final important reason to use parameters: sometimes, it's almost impossible otherwise. For instance, adding data to a varbinary field. How do you insert binary into a query? Not easily. But you can simply use a byte[] as the value of a parameter, and it works beautifully.

Those are the reasons you should use SQL Parameters. But how do you do it? Well, you have to have a DbCommand object. Specifically for SQL Server, that'll be the SqlCommand class. For other database types, you might use OleDbCommand, ODBCCommand, OracleCommand, etc...but for this example we'll use SQL Server.

SqlCommand has a collection of parameters, appropriately named Parameters. This has a very handy method on it named "AddWithValue". It takes a string and an object. First, the name of the parameter, and next the value to be set to that parameter. The best way to understand this is to see it in action.

Open the spoiler tag if you'd like to set your database up the same way as mine.

//use http://www.connectionstrings.com to find a connection string that works for you
public const string ConnectionString = @"Data Source=.\Sqlexpress;Initial Catalog=Example;Integrated Security=SSPI;";

The following is a method that performs a SELECT. Note line 5: it's where the parameter is given a value.

You can also use parameters for Stored Procedure Queries. The difference is, you don't include the parameters as part of the command string, but you still add them to the command object. Here's an example of both an INSERT and a SELECT using Stored Procedures.

Now that you see how it's done, there's almost never a reason not to use parameters. They only add a tiny bit of extra code, but they grant you readability, maintainability, and security. There's really no downside. So, go forth and use parameters for the rest of your days.

Side note: ORMs often parameterize queries for you. I know from experience that LINQ to SQL and Entity Framework use parameters for all the queries they perform. I can't speak definitively for any of the rest, but I'd be highly surprised if they didn't.

A lot of problems people have with statements can be resolved by simply using parameters.

I've had people with questions about a Syntax Error in their statement and couldn't for the life of them find it. Of course, it's hard to see the error when all you're asked is what the problem is in a statement like this:

Now that's a pretty basic query, and of course there aren't any syntax errors in it. That is, unless your textboxes contain something like "Bill" and "O'Neal".

In that case, you've just created your own Syntax Error. Why? Well, because you just made your statement read:SELECT * FROM myTable WHERE FName = 'Bill' And LName = 'O'Neal'Notice the extra apostrophe
And this is the reason SQL injection works. The string is looked over and parsed and any commands that follow syntax can be executed regardless of what you want to happen. Parameters remove this ability and keep the statement safe.

The biggest complaint I hear from users is that it's more code and takes longer. Trust me, As was pointed out in this Tutorial, the ease of reading and following the statement with parameters, by far offsets the complexity of trying to generate a concatenated statement correctly the first time.

If you would like to see a mild example of what can be done with SQL Injection, there is a link in my sig. I say mild because this example doesn't actually cause damage to your database, which, is completely possible with injection.

Curtis, I was pointed in your direction from someone that responded to one of my posts. I'm having an issue where I need to use LIKE in my select statement. After I implemented your above select code, my select statement is now returning nothing when it has been returning at least 30 responses for the same db query before I tried the above code. Any suggestions?

Yes, I can explain your issue. The problem is that you're including the ' character as part of your parameter's value. You do not need to do this, and in fact, must not do this. By doing this, you're telling SQL to literally match something that includes an apostrophe at the beginning and end.

Yes, I can explain your issue. The problem is that you're including the ' character as part of your parameter's value. You do not need to do this, and in fact, must not do this. By doing this, you're telling SQL to literally match something that includes an apostrophe at the beginning and end.

If you do not have an adversity to using LINQ to SQL you could utilize a custom function in your C# application to pass the needed parameters that way. Here is a link to an example that performs the insert to the database using parameters. Just a thought. Hope you find a solution to your current issue.

I don't really see the point there. His issue was quite simple, he was including a quote mark in the parameter. Parameters are escaped, so that quote was treated as a literal quote rather than a string identifier. He's already solved it.

If you're going to go with LINQ to SQL (or EF) go all the way. You can insert into those tables quite easily if you're using the generated classes.

Is it just me doing something wrong, or is it MySQL, or can you simply not parameterize the table name?

I'm trying to teach myself database accessing from C# - While still following OOP concepts and abstract away the GUI from the database. Which I'm finding hard to do because the nature of the queries seems very tightly tied. But I thought I could put together a layer to act between the GUI and the database and give it methods for things like GetItem(string tablename, int id) - but that doesn't seem to work (lines 08-10). So that leaves lots of methods such as GetPerson, GetCar, GetGun and so on that are each hard coded with the table name. I can't believe it has to be that way. Am I missing something about parameterizing the table name to be used?

You actually can't parameterize identifiers. That's kinda like trying to use a string to refer to a variable name.

Parameters are actually somewhat restrictive. The absolute best way to deal with what you're doing is to completely separate your query from your code, using Stored Procedures. That way, all you have to do is include procedure names in your config file, and if you want to change the way the query is processed, you can change it in the database, and not have to recompile, as long as the output doesn't change.

Now, when it comes time to do a search I want the user to be able to enter as much or as little to narrow down the search.
So if they provide the Person's first name of Fred and a Pet name of Dino, then we should get the user "Fred Flintstone"

My plan was to prefill all search fields with "*", then overwrite that with the data they provide on the Window GUIs. So the query would basically read:SELECT * FROM people WHERE firstname = "fred" lastname = "*" street="*" phone="*"
and so on with the asterisks except where a criteria was given. Is this do-able or does it make no sense at all?

So I'm new enough to not know if this idea lends itself to a Stored Procedure. I haven't read and experimented far enough yet.
Step 1: Simple proof-of-concept for talking to MySQL with existing data
Step 2: Get where I can add data using hardcoded infor
Step 3: Get where I can add data using GUI values
Step 4: Parameterize the string-based interaction.

That's as far as I've gotten in my self teaching - using yours and other tutorials and on-line MySQL documentation.

I'll look into that. This tutorial is more geared towards C# development, but I think the concept would be good to expand upon. Maybe we can collaborate with several other language experts and make a "parameterization guide" that includes techniques from several languages.

Good article.Actually the most projects I've attended usually take advantage of string concatenation. We always get this problem that clients type in the character「'」 . Perhaps next time we'll try this way.
By the way. Is it a good idea to use Linq to Sql instead of ADO.NET? It seems like Linq is more powerful than ADO.NET ( personal think ).Do you have suggestion about when the good timing is to implement Linq in a project ??

Depends. Entity Framework would be the one I used, since LINQ to SQL isn't supported going forward (it was a stop-gap project they released while EF was being worked on). But I honestly think it depends on your comfort with LINQ and your database requirements.