Posted 24 February 2012 - 03:20 PM
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?
Option 1)
string sql = "select * from table where fname = '" + firstName + "' and lname = '" + lastName + "'";
Option 2)
string sql = "select * from table where fname = @fname and lname = @lname";
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.
Spoiler
note: for all the following examples, I have a constant defined.
//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.
private static void Select() {
string cmdStr = "SELECT FirstName, LastName, Telephone FROM Person WHERE FirstName = @FirstName";
using (SqlConnection connection = new SqlConnection(ConnectionString))
using (SqlCommand command = new SqlCommand(cmdStr, connection)) {
command.Parameters.AddWithValue("@FirstName", "John");
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read()) {
string output = "First Name: {0} \t Last Name: {1} \t Phone: {2}";
Console.WriteLine(output, reader["FirstName"], reader["LastName"], reader["Telephone"]);
}
}
}
This also works for other SQL actions, like INSERT for example. This time, the magic's on lines 5 - 7.
private static void Insert() {
string cmdStr = "INSERT INTO Person (FirstName ,LastName ,Telephone) VALUES (@FirstName, @LastName, @Telephone)";
using (SqlConnection connection = new SqlConnection(ConnectionString))
using (SqlCommand command = new SqlCommand(cmdStr, connection)) {
command.Parameters.AddWithValue("@FirstName", "Bob");
command.Parameters.AddWithValue("@LastName", "Johnson");
command.Parameters.AddWithValue("@Telephone", "456-789-1230");
connection.Open();
int affectedRows = command.ExecuteNonQuery();
Console.WriteLine("Affected Rows: {0}", affectedRows);
}
}
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.
private static void SelectWithSP() {
string cmdStr = "pSelectPersonByFirstName";
using (SqlConnection connection = new SqlConnection(ConnectionString))
using (SqlCommand command = new SqlCommand(cmdStr, connection)) {
command.Parameters.AddWithValue("@FirstName", "John");
command.CommandType = CommandType.StoredProcedure;
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read()) {
string output = "First Name: {0} \t Last Name: {1} \t Phone: {2}";
Console.WriteLine(output, reader["FirstName"], reader["LastName"], reader["Telephone"]);
}
}
}
private static void InsertWithSP() {
string cmdStr = "pInsertPerson";
using (SqlConnection connection = new SqlConnection(ConnectionString))
using (SqlCommand command = new SqlCommand(cmdStr, connection)) {
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@FirstName", "Bob");
command.Parameters.AddWithValue("@LastName", "Johnson");
command.Parameters.AddWithValue("@Telephone", "456-789-1230");
connection.Open();
command.ExecuteNonQuery();
}
}
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.
MultiQuote
| 