PAEVISS – Programmers Against Embedded Values In SQL Statements

First there was PAFSO – Programmers Against the File System Object
Then there was PAUES – Programmers Against the Use of the End Statement

Now Experts Exchange brings you PAEVISS - Programmers Against Embedded Values In SQL Statements

With ADO.NET there is sometimes a need to insert, update or query the database using a command object.

Using Connection As SqlConnection = New SqlConnection(ConnectionString)
                      	Connection.Open()
                      	Dim CmdText As String = "INSERT INTO [Person] (FirstName, LastName) " & _
                                      "VALUES ('" & txtFirstname.Text & "', '" & txtLastname.Text & ")"
                      
                      	Using cmd As SqlCommand = New SqlCommand(CmdText, Connection)
                      		cmd.ExecuteNonQuery()
                      	End Using
                      	Connection.Close()
                      End Using

Select all Open in new window

While code like this will work sometimes, it isn’t ideal for a number of reasons:

Instead, let's look at how you can use parameterized queries.

Using Connection As SqlConnection = New SqlConnection(ConnectionString)
                      	Connection.Open()
                      	Dim CmdText As String = "INSERT INTO [Person] (FirstName, LastName) " & _
                      		"VALUES (@lastname, @firstname)"
                      
                      	Using cmd As SqlCommand = New SqlCommand(CmdText, Connection)
                      		With cmd.Parameters
                      			.AddWithValue("@lastname", txtLastname.Text)
                      			.AddWithValue("@firstname", txtFirstname.Text)
                      		End With
                      		cmd.ExecuteNonQuery()
                      	End Using
                      	Connection.Close()
                      End Using

Select all Open in new window

In the command text, we’ve added @lastname and @firstname as placeholders or parameters, which then must be added, along with their values, to the parameters collection of the command object.  The "AddWithValue" method is convenient for adding parameters with their values.  .NET 1.x users will not have access to this method.  There is an overload of "Add" that allows you to specify a parameter value, but this was deprecated by the AddWithValue method as explained in the MSDN documentation.

You may notice that this looks similar to the syntax that is used for stored procedures.   In fact, the use of parameters can have an effect at the server level to increase execution plan reuse, and thus boost performance slightly.  (However, if your SQL Server database has forced parameterization, then every query submitted to the database is compiled with parameters.)

There are so many benefits, and so few drawbacks, that I recommend parameterized queries as a best practice.  Avoid the messy, dangerous practice of embedding values into SQL command strings.