This is part ten of a series about programming a SQL Server database using VB.NET and Visual Studio for people just starting to learn about programming databases. You can find the beginning here.
The idea behind this series is to demonstrate Visual Studio and SQL Server in easy steps so even beginners can use them. Too often technical articles either don't show beginners the latest tools, or else are written in language that only experienced programmers can understand. This series is different.
Part One developed a very simple app using the powerful DataGridView control but didn't include much explanation. That's because the next installments were all about the explanation of why it works. Now we're using DataGridView most of the time because it does work! The previous article, Using Data Sources in VB.NET used the high-level DataSource and showed both the advantages and the limitations, especially in a many-to-many application. This article will continue in that same direction and show a DataSource along with a parameterized query. Unfortunately, it's limited too, but I'll show one way around the limits.
Since this article is all about parameterized queries, let's start by understanding what they are.
A SQL query that returns a specific row from a database table might look something like this:
SELECT RecipeName
FROM Recipes
WHERE (RID = 22)
In my example database, this returns the RecipeName "Slime". You can make this more flexible by constructing a query using variables (as we did in the many-to-many segment) so that it can access any row, not just the Slime recipe. But this is still an inefficient way to write SQL. A more efficient way to do it is to use a SQL parameter, not a variable, like this:
SELECT RecipeName
FROM Recipes
WHERE (RID = @RID)
From the point of view of a developer, it's almost the same thing, but it's a lot better from the point of view of SQL Server. In the first example, the entire query has to be constructed and compiled by SQL Server every time. (Remember, SQL Server actually executes the query, not your program.) In the second example, SQL Server generates a query execution plan just once and then plugs the parameter value into it.
There are more advantages.
Another is that parameterized queries are the first line of defense against a type of virus attack called a SQL injection attack. If the contents of a text field are just passed to SQL Server and executed, then that text field can contain a complete new query that does something totally different. A parameter works because it's treated as a literal value rather than executable code. And it's also checked for type and length. A typical SQL injection string would have to be much longer, and the SqlParameter class would throw an exception.
And a third advantage is that you can create parameterized stored procedures and a fourth is that most programmers find it easier to avoid errors when they don't have to keep track of single and double quotes to construct SQL strings using VB.NET variables.
On the next page, the syntax of a parameterized SQL query is explained by coding the same query in two ways and measuring performance with Stopwatch.