Working with parameterized SQL operations
Overview
Developers just starting out writing data-centric desktop applications generally will write code to interact with databases directly in a windows form with idea how to properly read, add, edit, delete or search for data that is not prone to runtime errors, writing incorrect data which can lead countless wasted hours, days or even weeks attempting to figure out why there are problems.
This article will provide direction on writing proper code to work with backend database beginning at the user interface and continue to work with classes to separate user interface from backend data operations. While doing so will introduce several methods to assist with figuring out how to remedy SQL issues. To keep things simple only a INSERT and a SELECT WHERE are wired up.
In a conventional desktop application, DataSet and DataTable objects are used to interact with a database and an application while in this article concrete classes are used e.g. Customer, ContactType are used. Does this mean a developer should discard using DataSet or DataTable containers? Absolutely not, the idea here is to create lightweight containers for working with data, when we get to the actual read and insert operations it really does not matter what the container is.
Typical starting point
The task is to provide the ability to add new customers to a SQL-Server database table. A design such as the following is created.
At first glance, this looks acceptable yet there are flaws, let’s look at the underlying table.
The fields in green highlight should be constrained to valid values e.g. for contact title perhaps
The same would go for country and standings while active status would be constrained to yes/no (bit type for SQL-Server). Later on, reference tables will be introduced as a first step to fixing this issue by using ComboBox controls to constrain choices for country, contact title and standings.
Then there is join date which in the user interface is obtained from a regular TextBox while a DateTimePicker would be a much better choice.
Beginner effort at writing SQL for an INSERT operation
Public Class Form1
Private Sub Button1_Click(sender As Object, e As EventArgs) _
Handles Button1.Click
Dim cn As New SqlClient.SqlConnection
cn.ConnectionString = "Data Source=KARENS-PC;" &
"Initial Catalog=WorkingWithDataTips_1;Integrated Security=True"
Dim cmd As New SqlClient.SqlCommand
cmd.Connection = cn
cmd.CommandText = "INSERT INTO Customers " &
"(CompanyName,ContactName,ContactTitle," &
"StreetAddress,City,PostalCode,Country,Phone," &
"Standing,JoinDate,ActiveStatus) " &
"VALUES ('" & TextBox1.Text & "','" & TextBox2.Text & "','" & TextBox3.Text &
"','" & TextBox4.Text & "','" & TextBox5.Text & "','" & TextBox6.Text & "','" &
TextBox7.Text & "','" & TextBox7.Text & "','" &
TextBox8.Text & "','" & TextBox9.Text & "','" & TextBox10.Text & "')"
cn.Open()
cmd.ExecuteNonQuery()
MessageBox.Show("Customer added")
End Sub
End Class
Problems with the code listed above.
- Each TextBox names have no meaning to what they hold which can make things very difficult to manage when writing the INSERT statement.
- Objects used to create a connection to the database and perform the insert operation are in a button click event were instead this code should be placed into a class method where the method is called from the button click event. When passing in values from the controls in our form with parameters that are typed means that TextBox values cannot be passed in “as is” but instead must be converted to the proper type.
- For those fields that are of type string, if one or more apostrophes are entered into the text box such as O’Neal this leads to a syntax error where using command parameters will handle cases were one or more apostrophes are entered.
- There is no exception handling if there is a runtime error the application crashes.
- If after adding the new record an edit is required the primary key is not available unless data is refreshed. When there is a chance the user needs to edit data after an add there should be zero reasons to query the backend table to get the new primary key.
With the above points, consider what might happen if the following data is entered using the code above.
- Company name and contact name both have an apostrophe will cause a syntax error in the INSERT statement.
- Contact title has a value “Owwbner” which should have been “Owner”, this means that if later on a SELECT statement is done searching for all owners this record will not be included.
- Postal code, the max length in the table is 10 characters, what do you think will happen?
- Join date is not valid for the current locale (it might be valid for other locales but for this example it’s invalid)
- Active is prone to the same issues as contact title and join date.
Revised code
The following is a revised version of the form which changed contact title and country to ComboBox controls where values are read from reference tables in the database.
Code for database operations has been moved to several classes.
Customer class represents all field from the customer table in the database. This permits passing a single object of a customer to the add new customer method as shown below.
Public Function Add(pCustomer As Customer) As Boolean
mHasException = False
Dim insertStatement As String =
<SQL>
INSERT INTO dbo.Customers
(CompanyName
,ContactName
,ContactTitle
,StreetAddress
,City
,PostalCode
,Country
,Phone
,JoinDate
,ActiveStatus
)
VALUES
(@CompanyName
,@ContactName
,@ContactTitle
,@StreetAddress
,@City
,@PostalCode
,@Country
,@Phone
,@JoinDate
,@ActiveStatus
);
SELECT CAST(scope_identity() AS int);
</SQL>.Value
Using cn = New SqlConnection With {.ConnectionString = ConnectionString}
Using cmd = New SqlCommand With {.Connection = cn}
cmd.CommandText = insertStatement
cmd.Parameters.AddWithValue("@CompanyName",pCustomer.CompanyName)
cmd.Parameters.AddWithValue("@ContactName",pCustomer.ContactName)
cmd.Parameters.AddWithValue("@ContactTitle",pCustomer.ContactTitle)
cmd.Parameters.AddWithValue("@StreetAddress",pCustomer.StreetAddress)
cmd.Parameters.AddWithValue("@City",pCustomer.City)
cmd.Parameters.AddWithValue("@PostalCode",pCustomer.PostalCode)
cmd.Parameters.AddWithValue("@Country",pCustomer.Country)
cmd.Parameters.AddWithValue("@Phone",pCustomer.Phone)
cmd.Parameters.AddWithValue("@JoinDate",pCustomer.JoinDate)
cmd.Parameters.AddWithValue("@ActiveStatus",pCustomer.ActiveStatus)
Try
cn.Open()
pCustomer.CustomerIdentifier = Convert.ToInt32(cmd.ExecuteScalar())
Return True
Catch ex As Exception
mHasException = True
mLastException = ex
Return False
End Try
End Using
End Using
End Function
Code Review
- The SQL INSERT statement, in this case, was created in SQL-Server Management Studio using Red-Gate SQL-Tool belt. The same can be done by creating a .sql file in Visual Studio to construct the SQL INSERT statement which can then be tested out. Notice the SELECT statement following the INSERT statement separated by a semi-colon, this gets the new primary key on a successful insert and sets the CustomerIdentifier property of the Customer so that on returning to the caller it has the new primary key which could be used to add a record to a DataGridView so that it can then be edited without a need to refresh/re-read the customer table. See below for the initial version done in SQL-Server Management Studio.
- Note in the INSERT statement VALUES section that each parameter is the same name of the column name prefixed with @. Combine how XML literals were used to construct the INSERT statement this makes it easy to create the parameters for the command object.
- To create each parameter, create one, copy it then paste it once for each field (there will be errors but ignore them).
- For each item in the VALUES section of the INSERT statement, copy the parameter name and place it into the AddWithValue method followed by typing in (in this case) pCustomer which then brings up members for the customer item, select the proper one. Repeat this for each parameter.
- Next, double check your work e.g. do I have just one parameter per AddWithValue?
- Important note, since the customer class is strongly typed only valid types can be passed in e.g. a string cannot be passed in for JoinDate for instance while that could easily be done working with TextBox controls.
USE WorkingWithDataTips_1
DECLARE @CompanyName AS nvarchar(40) = 'Test company'
DECLARE @ContactName AS nvarchar(30) = 'Karen Payne'
DECLARE @ContactTitle AS nvarchar(30) = 'Owner'
DECLARE @StreetAddress AS nvarchar(60) = '111 Blue jay way'
DECLARE @City AS nvarchar(15) = 'Portland'
DECLARE @PostalCode AS nvarchar(10) = '99999'
DECLARE @Country AS nvarchar(15) = 'USA'
DECLARE @Phone AS nvarchar(24) = '55566678890'
DECLARE @JoinDate AS datetime2(7) = GETDATE()
DECLARE @ActiveStatus AS BIT = 1
INSERT INTO dbo.Customers
(CompanyName
,ContactName
,ContactTitle
,StreetAddress
,City
,PostalCode
,Country
,Phone
,JoinDate
,ActiveStatus
)
VALUES
(@CompanyName
,@ContactName
,@ContactTitle
,@StreetAddress
,@City
,@PostalCode
,@Country
,@Phone
,@JoinDate
,@ActiveStatus
)
In the first version of the add new customer, each TextBox by default permits more data than is allowed for most of the fields in the customer table. To remedy this there is a method which obtains the max length for each of the string columns, places them into a Dictionary.
Public Function GetColumnMaxLengths As Dictionary(Of string, Integer)
Dim colInformation As new Dictionary(Of string, Integer)
mHasException = False
Dim selectStatement As String =
<SQL>
SELECT
COLUMN_NAME,
CHARACTER_MAXIMUM_LENGTH,ORDINAL_POSITION,
IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE
table_name = 'Customers' AND DATA_TYPE ='nvarchar'
</SQL>.Value
Using cn = New SqlConnection With {.ConnectionString = ConnectionString}
Using cmd = New SqlCommand With {.Connection = cn}
cmd.CommandText = selectStatement
cn.Open()
Dim reader As SqlDataReader = cmd.ExecuteReader()
While reader.Read()
colInformation.Add(reader.GetString(0), reader.GetInt32(1))
End While
End Using
End Using
Return colInformation
End Function
In Form Shown event the following code calls the method above.
Dim custOperations As New CustomerOperations
Dim colInformation = custOperations.GetColumnMaxLengths()
Dim tbList = Controls.OfType(of TextBox).ToList()
Dim colName as String = ""
For Each tb As TextBox In tbList
colName = tb.Name.Replace("txt", "")
If colInformation.ContainsKey(colName)
tb.MaxLength = colInformation(colName)
End If
Next
Once the dictionary is loaded a for-each iterates each TextBox, searches the dictionary for the TextBox name without the prefix of txt, when found sets the MaxLength of the TextBox to the max length of the corresponding field. For this to happen each TextBox starts with ‘txt’ followed by the field name it represents.
Expect the unexpected
While coding and testing leave exception handling out along by setting “Common Language Runtime Exceptions” set to on which is done by selecting from Visual Studio’s debug menu, Windows, Exception Settings.
By doing so any exceptions that are thrown Visual Studio will break on the issue, unlike wrapping code which might fail in a try/catch statement. If you must wrap code which might fail in a try/catch be sure to set a break-point in the first line within the catch block of the try/catch statement.
Once finish coding and acceptance testing wrap all code which is prone to any issues with a try/catch which includes opening a connection to a database.
A perfectly good question might be, how do I know if SQL-Server running or if the server name is correct? To check if SQL-Server process is running the ServiceController class is available for this and to check if the connection is good a simple try/catch wrapped around a connection would do the trick.
Imports System.Data.SqlClient
Imports System.ServiceProcess
Public Class ConnectionHelper
Public Function IsServerConnected(connectionString As String) As Boolean
Using connection As New SqlConnection(connectionString)
Try
connection.Open()
Return True
Catch e As SqlException
Return False
End Try
End Using
End Function
''' <summary>
''' Determine if SQL-Server service is running
''' </summary>
''' <param name="pServiceName"></param>
''' <returns></returns>
Public function SqlServerIsRunning(pServiceName As String) As Boolean
Dim serviceControllers() As ServiceController
Try
serviceControllers = ServiceController.GetServices(pServiceName)
Return serviceControllers.
FirstOrDefault(
Function(item) item.ServiceName = "MSSQLSERVER") IsNot Nothing
Catch ex As Exception
Return False
End Try
End function
End Class
The downside is neither are perfect and for a one-off time this may happen is simply complicates the application code. Example for implementing these checks.
Public Function CountryList(Optional pAddSelectText As Boolean = False) As List(Of Country)
Dim listCountries As new List(Of Country)
if pAddSelectText
listCountries.Add(new Country() With{.Id = 0,.Name = "Select"})
End If
mHasException = False
Using cn = New SqlConnection With {.ConnectionString = ConnectionString}
Using cmd = New SqlCommand With {.Connection = cn}
cmd.CommandText = "SELECT id,CountryName FROM dbo.Countries"
Try
Dim helper1 As New ConnectionHelper
if helper1.SqlServerIsRunning(DatabaseServer) And helper1.IsServerConnected(ConnectionString)
cn.Open()
Dim reader As SqlDataReader = cmd.ExecuteReader()
While reader.Read()
listCountries.Add(new Country() With
{
.id = reader.GetInt32(0),
.Name = reader.GetString(1)
})
End While
End If
Catch ex As Exception
mHasException = True
mLastException = ex
End Try
End Using
End Using
Return listCountries
End Function
Since this may be a one-off simply use a try/catch
Public Function CountryList(Optional pAddSelectText As Boolean = False) As List(Of Country)
Dim listCountries As new List(Of Country)
if pAddSelectText
listCountries.Add(new Country() With{.Id = 0,.Name = "Select"})
End If
mHasException = False
Using cn = New SqlConnection With {.ConnectionString = ConnectionString}
Using cmd = New SqlCommand With {.Connection = cn}
cmd.CommandText = "SELECT id,CountryName FROM dbo.Countries"
Try
cn.Open()
Dim reader As SqlDataReader = cmd.ExecuteReader()
While reader.Read()
listCountries.Add(new Country() With
{
.id = reader.GetInt32(0),
.Name = reader.GetString(1)
})
End While
Catch ex As Exception
mHasException = True
mLastException = ex
End Try
End Using
End Using
Return listCountries
End Function
If your customer(s) are not readily available to check out root causes for issues such as if SQL-Server is running or the instance name of the server is not available the next best thing is to have a utility which uses SQL Server Management Objects to run diagnostics to determine if the SQL Server process is running and the instance name is available.
The best overall option is to have solid testing prior to distributing your application along with knowing your customer's environment and having a good installation were usually the free installation programs are not enough for a robust application. You don’t want sales of your application to be hindered by an inferior installation.
General usage.
Usually adding new records is part of an application where the main screen would show existing data with a method to add new records. Developers seem to think the best course of action is to add records directly to a DataGridView while this is fine there is more code needed than adding new records in a modal dialog form. An argument might be that users don’t want to have a dialog, enter information, press okay, close the dialog, re-open the dialog to add another record. This would be undesirable and there is a better way which is to setup an event in the add new customer form which the calling form can subscribe too.
Step 1 is to create classes which can push customer information to the calling form.
'
' Classes responsible for event listening from
' the add new record form to the main form.
'
Public Class CustomerEventArgs
Inherits EventArgs
Public Customer as Customer
Public Sub New(pCustomer As Customer)
MyBase.New()
Customer = pCustomer
End Sub
End Class
Public Class CustomerEvent
Public Event EventStart(e As Object, args As CustomerEventArgs)
Public Sub GenerateCustomerEvent(pCustomer As Customer)
Dim args As New CustomerEventArgs(pCustomer)
RaiseEvent EventStart(Me, args)
End Sub
End Class
In the add form declare the event using WithEvents.
In the calling form we subscribe to the event EventStart (since writing this it’s been renamed to PushEvent) which in turn passes the new customer to the main form which is listening via HandleNewCustomerRecord event.
Note in HandleNewCustomerRecord args.Customer.Items, this is from a partial class of Customer.
Public Partial Class Customer
Public ReadOnly Property Items() As Object()
get
Return _
new Object() _
{
CustomerIdentifier,
CompanyName,
ContactName,
ContactTitle,
StreetAddress,
City,
PostalCode,
Country,
Phone,
JoinDate,
ActiveStatus
}
End get
End Property
End Class
Rows.Add in HandleNewCustomerRecord passes the object array to form a new record. It’s important to have the properties in Items property to be in the exact same order as the DataColumns in the receiving DataTable else a runtime exception may be raised if data type doesn’t match or no error yet data is shown in the wrong cells of the DataGridView.
Special note on implementing the adding of records from a modal form. Using the screenshot below note the highlighted classes in WindosApp1b. These classes don’t reside in WindosApp1b but instead reside in WindosApp1a. This is possible by right clicking on WindosApp1b, select add existing item. Select a file, select the “add as link” on the add button, select shortcut and press Add. This means code is shared yet if a change is made in either project it’s the same as making the change in both projects. One must use caution when using “Add as link” else a code change can break another project sharing the code base.
Adding a batch of customers at one time really should be done with BULK INSERT or MERGE but there may be times when a developer needs to perform the inserts one by one. Most common mistakes are performing inserts one by one when BULK INSERT or MERGE should have been used or when inserts are done where a for-each loop is used where within the for-each loop a command and parameters are created for each iteration within the for-each loop. The proper method is to create a connection, a command object and create parameters not using AddWithValue but Add method as shown below. Other than this AddWithValue is the proper way to go unless it fails with a data type such as image or blob.
''' <summary>
''' Add a batch of customers.
''' On success each customer key is set.
''' </summary>
''' <param name="pCustomerList"></param>
Public function AddCustomers(pCustomerList As List(Of Customer)) As Boolean
mHasException = False
Dim insertStatement As String =
<SQL>
INSERT INTO dbo.Customers
(CompanyName
,ContactName
,ContactTitle
,StreetAddress
,City
,PostalCode
,Country
,Phone
,JoinDate
,ActiveStatus
)
VALUES
(@CompanyName
,@ContactName
,@ContactTitle
,@StreetAddress
,@City
,@PostalCode
,@Country
,@Phone
,@JoinDate
,@ActiveStatus
);
SELECT CAST(scope_identity() AS int);
</SQL>.Value
Using cn = New SqlConnection With {.ConnectionString = ConnectionString}
Using cmd = New SqlCommand With {.Connection = cn}
cmd.CommandText = insertStatement
cmd.Parameters.Add(New SqlParameter() With
{
.ParameterName = "@CompanyName",
.DbType = DbType.String
})
cmd.Parameters.Add(New SqlParameter() With
{
.ParameterName = "@ContactName",
.DbType = DbType.String
})
cmd.Parameters.Add(New SqlParameter() With
{
.ParameterName = "@ContactTitle",
.DbType = DbType.String
})
cmd.Parameters.Add(New SqlParameter() With
{
.ParameterName = "@StreetAddress",
.DbType = DbType.String
})
cmd.Parameters.Add(New SqlParameter() With
{
.ParameterName = "@City",
.DbType = DbType.String
})
cmd.Parameters.Add(New SqlParameter() With
{
.ParameterName = "@PostalCode",
.DbType = DbType.String
})
cmd.Parameters.Add(New SqlParameter() With
{
.ParameterName = "@Country",
.DbType = DbType.String
})
cmd.Parameters.Add(New SqlParameter() With
{
.ParameterName = "@Phone",
.DbType = DbType.String
})
cmd.Parameters.Add(New SqlParameter() With
{
.ParameterName = "@JoinDate",
.DbType = DbType.Date
})
cmd.Parameters.Add(New SqlParameter() With
{
.ParameterName = "@ActiveStatus",
.DbType = DbType.Boolean
})
Try
cn.Open()
For Each customer As Customer In pCustomerList
cmd.Parameters("@CompanyName").Value = customer.CompanyName
cmd.Parameters("@ContactName").Value = customer.ContactName
cmd.Parameters("@ContactTitle").Value = customer.ContactTitle
cmd.Parameters("@StreetAddress").Value = customer.StreetAddress
cmd.Parameters("@City").Value = customer.City
cmd.Parameters("@PostalCode").Value = customer.PostalCode
cmd.Parameters("@Country").Value = customer.Country
cmd.Parameters("@Phone").Value = customer.Phone
cmd.Parameters("@JoinDate").Value = customer.Phone
cmd.Parameters("@ActiveStatus").Value = customer.ActiveStatus
customer.CustomerIdentifier = Convert.ToInt32(cmd.ExecuteScalar())
Next
Return True
Catch ex As Exception
mHasException = True
mLastException = ex
return False
End Try
End Using
End Using
End function
Misconception
Parameters can-not be used as replacements for table names as shown below. Many have tried yet SQL-Server does not permit this.
Reusing parameters in a SQL statement.
Parameters are not restricted to being used once in a statement, for instance in the following UPDATE statement a new field has been added to a database table where it's not a primary key and is not auto-incrementing. The parameter (and this can be done in C# code too) @MaxSurrogateKey sets a row to 0 for the first row, increments by one and sets the next row to 1 and so on. This should open up possibilities when writing your SQL statements.
DECLARE @MaxSurrogateKey INT = -1;
UPDATE IncrementDemo
SET @MaxSurrogateKey = RowPosition = @MaxSurrogateKey + 1
WHERE RowPosition IS NULL;
SELECT id ,
FirstName ,
LastName ,
RowPosition
FROM dbo.IncrementDemo;
Issues with SQL statements
If when proper diligence is taken to write SQL statements there still can be issues. One option is to use a method that will write out your parameterized SQL statement to a log file. Included in the source code for this article is a project named PeekerForCommands. In short, there is a language extension method for peeking at a full SQL statement that has been parameterized.
Example using a SQL INSERT
Public Function Add(pCustomer As Customer) As Boolean
mHasException = False
Dim insertStatement As String =
<SQL>
INSERT INTO dbo.Customers
(CompanyName
,ContactName
,ContactTitle
,StreetAddress
,City
,PostalCode
,Country
,Phone
,JoinDate
,ActiveStatus
)
VALUES
(@CompanyName
,@ContactName
,@ContactTitle
,@StreetAddress
,@City
,@PostalCode
,@Country
,@Phone
,@JoinDate
,@ActiveStatus
);
SELECT CAST(scope_identity() AS int);
</SQL>.Value
Using cn = New SqlConnection With {.ConnectionString = ConnectionString}
Using cmd = New SqlCommand With {.Connection = cn}
cmd.CommandText = insertStatement
cmd.Parameters.AddWithValue("@CompanyName",pCustomer.CompanyName)
cmd.Parameters.AddWithValue("@ContactName",pCustomer.ContactName)
cmd.Parameters.AddWithValue("@ContactTitle",pCustomer.ContactTitle)
cmd.Parameters.AddWithValue("@StreetAddress",pCustomer.StreetAddress)
cmd.Parameters.AddWithValue("@City",pCustomer.City)
cmd.Parameters.AddWithValue("@PostalCode",pCustomer.PostalCode)
cmd.Parameters.AddWithValue("@Country",pCustomer.Country)
cmd.Parameters.AddWithValue("@Phone",pCustomer.Phone)
cmd.Parameters.AddWithValue("@JoinDate",pCustomer.JoinDate)
cmd.Parameters.AddWithValue("@ActiveStatus",pCustomer.ActiveStatus)
Console.WriteLine(cmd.ActualCommandTextByNames)
Try
cn.Open()
pCustomer.CustomerIdentifier = Convert.ToInt32(cmd.ExecuteScalar())
Return True
Catch ex As Exception
mHasException = True
mLastException = ex
Return False
End Try
End Using
End Using
End Function
Note the Console.WriteLine echoes the result from ActualCommandTextByNames which is the language extension and will produce the following in the IDE Output window. In this case, there were no checks for the maximum length of PostalCode and its apparent truncation would happen if the statement were to be executed. This leads the developer to ensure the maximum length is not exceeded. Also, note how company name and contact name values have apostrophes escaped which is done by the parameters.
INSERT INTO dbo.Customers
(CompanyName
,ContactName
,ContactTitle
,StreetAddress
,City
,PostalCode
,Country
,Phone
,JoinDate
,ActiveStatus
)
VALUES
('Jane''s coffee cafe'''
,'Jane O''Neal'
,'Accounting Manager'
,'111 Peach way'
,'Portland'
,'99999999999999999999999999'
,'Argentina'
,'6669994565'
,'4/14/2018 4:45:27 PM'
,False
);
SELECT CAST(scope_identity() AS int);
Conclusion
Using parameters for your command object removes many problems from interacting with data from properly formatting data to preventing SQL-Injection. Creating SQL statements without string concatenation allows statements to be first created in an SQL file in Visual Studio or SQL-Server Management Studio and dropped into code via XML-literals as demonstrated in the code samples presented.
Named parameters
A named parameter means they can be supplied in any order when using parameters in your SQL statements in your C# project. All of the examples shown have named parameters in the same order as they appear in the SQL statements when working with SQL-Server. When working with Oracle which supports named parameters they are seen in ordinal position unless setting BindByName property to true for the command object using Oracle’s data class Oracle.ManagedDataAccess.Client.
Working with MS-Access using the OleDb data provider all parameters are in ordinal position, which means parameters for a command object must be in the same order as with the VALUES fields.
The following example
Public Function AddNewCustomerRow(
ByVal pName As String,
ByVal pContact As String,
ByVal pContactTitle As String,
ByVal pAddress As String,
ByVal pCity As String,
ByVal pPostalCode As String,
ByVal pJoinDate As DateTime,
ByRef pIdentfier As Integer) As Boolean
Dim Success As Boolean = True
Try
Using cn As New OleDbConnection(Builder.ConnectionString)
Using cmd As New OleDbCommand With {.Connection = cn}
cmd.CommandText =
<SQL>
INSERT INTO Customer
(
CompanyName,
ContactName,
ContactTitle,
Address,
City,
PostalCode,
JoinDate
)
Values
(
@CompanyName,
@ContactName,
@ContactTitle,
@Address,
@City,
@PostalCode,
@JoinDate
)
</SQL>.Value
cmd.Parameters.AddWithValue("@CompanyName", pName)
cmd.Parameters.AddWithValue("@ContactName", pContact)
cmd.Parameters.AddWithValue("@ContactTitle", pContactTitle)
cmd.Parameters.AddWithValue("@Address", pAddress)
cmd.Parameters.AddWithValue("@City", pCity)
cmd.Parameters.AddWithValue("@PostalCode", pPostalCode)
cmd.Parameters.AddWithValue("@JoinDate", pJoinDate)
cn.Open()
cmd.ExecuteNonQuery()
cmd.CommandText = "Select @@Identity"
pIdentfier = CInt(cmd.ExecuteScalar)
End Using
End Using
Catch ex As Exception
mHasException = True
mLastException = ex
Success = False
End Try
Return Success
End Function
Is equivalent to
Public Function AddNewCustomerRow(
ByVal pName As String,
ByVal pContact As String,
ByVal pContactTitle As String,
ByVal pAddress As String,
ByVal pCity As String,
ByVal pPostalCode As String,
ByVal pJoinDate As DateTime,
ByRef pIdentfier As Integer) As Boolean
Dim Success As Boolean = True
Try
Using cn As New OleDbConnection(Builder.ConnectionString)
Using cmd As New OleDbCommand With {.Connection = cn}
cmd.CommandText =
<SQL>
INSERT INTO Customer
(
CompanyName,
ContactName,
ContactTitle,
Address,
City,
PostalCode,
JoinDate
)
Values
(
?,
?,
?,
?,
?,
?,
?
)
</SQL>.Value
cmd.Parameters.AddWithValue("?", pName)
cmd.Parameters.AddWithValue("?", pContact)
cmd.Parameters.AddWithValue("?", pContactTitle)
cmd.Parameters.AddWithValue("?", pAddress)
cmd.Parameters.AddWithValue("?", pCity)
cmd.Parameters.AddWithValue("?", pPostalCode)
cmd.Parameters.AddWithValue("?", pJoinDate)
cn.Open()
cmd.ExecuteNonQuery()
cmd.CommandText = "Select @@Identity"
pIdentfier = CInt(cmd.ExecuteScalar)
End Using
End Using
Catch ex As Exception
mHasException = True
mLastException = ex
Success = False
End Try
Return Success
End Function
Both examples are the same and both require much more attention than SQL-Server named parameters.
Here is a clear example of issues with ordinal-based parameters. Two parameters are not in order; both are strings which means the issue may go unnoticed for a long time. If the two fields for the parameters were different types then a decent unit test would flush this out.
Public Function AddNewCustomerRow(
ByVal pName As String,
ByVal pContact As String,
ByVal pContactTitle As String,
ByVal pAddress As String,
ByVal pCity As String,
ByVal pPostalCode As String,
ByVal pJoinDate As DateTime,
ByRef pIdentfier As Integer) As Boolean
Dim Success As Boolean = True
Try
Using cn As New OleDbConnection(Builder.ConnectionString)
Using cmd As New OleDbCommand With {.Connection = cn}
cmd.CommandText =
<SQL>
INSERT INTO Customer
(
CompanyName,
ContactName,
ContactTitle,
Address,
City,
PostalCode,
JoinDate
)
Values
(
@CompanyName,
@ContactName,
@ContactTitle,
@Address,
@City,
@PostalCode,
@JoinDate
)
</SQL>.Value
cmd.Parameters.AddWithValue("@CompanyName", pName)
cmd.Parameters.AddWithValue("@ContactTitle", pContactTitle)
cmd.Parameters.AddWithValue("@ContactName", pContact)
cmd.Parameters.AddWithValue("@Address", pAddress)
cmd.Parameters.AddWithValue("@City", pCity)
cmd.Parameters.AddWithValue("@PostalCode", pPostalCode)
cmd.Parameters.AddWithValue("@JoinDate", pJoinDate)
cn.Open()
cmd.ExecuteNonQuery()
cmd.CommandText = "Select @@Identity"
pIdentfier = CInt(cmd.ExecuteScalar)
End Using
End Using
Catch ex As Exception
mHasException = True
mLastException = ex
Success = False
End Try
Return Success
End Function
Next steps
Once familiar with parameterized queries learn to wrap add, update and delete operations that involve multiple tables (tables that have relations and depend on each other) in transitions. Below is taken from an MSDN code sample which uses transactions for multiple INSERT operations.
Public Function AddNewCustomer(pCustomer As customer) As Boolean
mHasException = False
dim contactInsertStatement =
<SQL>
INSERT INTO dbo.Contact
(FirstName
,LastName)
VALUES
(@FirstName
,@LastName);
SELECT CAST(scope_identity() AS int);
</SQL>.Value
Dim contactDevicesInsertStatement =
<SQL>
INSERT INTO dbo.ContactContactDevices
(ContactIdentifier
,PhoneTypeIdenitfier
,PhoneNumber
,Active)
VALUES
(@ContactIdentifier
,@PhoneTypeIdenitfier
,@PhoneNumber
,@ACTIVE);
SELECT CAST(scope_identity() AS int);
</SQL>.Value
Dim customerInsertStatement =
<SQL>
INSERT INTO dbo.Customers
(CompanyName
,ContactName
,ContactIdentifier
,ContactTypeIdentifier
,Street
,City
,PostalCode
,CountryIdentfier
,Phone
,ModifiedDate)
VALUES
(@CompanyName
,@ContactName
,@ContactIdentifier
,@ContactTypeIdentifier
,@Street
,@City
,@PostalCode
,@CountryIdentfier
,@Phone
,@ModifiedDate);
SELECT CAST(scope_identity() AS int);
</SQL>.Value
Dim contactIdentifier As Integer = 0
Dim contactDeviceIdentifier as Integer = 0
Using cn As New SqlConnection With{.ConnectionString = ConnectionString}
cn.Open()
Dim sqlTran As SqlTransaction = cn.BeginTransaction()
Using cmd As New SqlCommand With
{
.Connection = cn,
.CommandText = contactInsertStatement,
.Transaction = sqlTran
}
Try
cmd.Parameters.AddWithValue("@FirstName",pCustomer.ContactFirstName)
cmd.Parameters.AddWithValue("@LastName",pCustomer.ContactLastName)
contactIdentifier = CInt(cmd.ExecuteScalar())
cmd.CommandText = contactDevicesInsertStatement
cmd.Parameters.Clear()
cmd.Parameters.AddWithValue("@ContactIdentifier",contactIdentifier)
cmd.Parameters.AddWithValue("@PhoneTypeIdenitfier",pCustomer.PhoneTypeIdenitfier)
cmd.Parameters.AddWithValue("@PhoneNumber",pCustomer.PhoneNumber)
cmd.Parameters.AddWithValue("@Active",True)
contactDeviceIdentifier = CInt(cmd.ExecuteScalar())
cmd.CommandText = customerInsertStatement
cmd.Parameters.Clear()
cmd.Parameters.AddWithValue("@CompanyName",pCustomer.CompanyName)
cmd.Parameters.AddWithValue("@ContactName",pCustomer.ContactName)
cmd.Parameters.AddWithValue("@ContactIdentifier",contactIdentifier)
cmd.Parameters.AddWithValue("@ContactTypeIdentifier",pCustomer.ContactTypeIdentifier)
cmd.Parameters.AddWithValue("@Street",pCustomer.Street)
cmd.Parameters.AddWithValue("@City",pCustomer.City)
cmd.Parameters.AddWithValue("@PostalCode",pCustomer.PostalCode)
cmd.Parameters.AddWithValue("@Phone",pCustomer.PhoneNumber)
cmd.Parameters.AddWithValue("@CountryIdentfier",pCustomer.CountryIdentifier)
cmd.Parameters.AddWithValue("@ModifiedDate",Now)
pCustomer.CustomerIdentifier = CInt(cmd.ExecuteScalar())
pCustomer.ContactIdentifier = contactIdentifier
pCustomer.DevicesIdentifier = contactDeviceIdentifier
sqlTran.Commit()
return True
Catch sex As SqlException
sqlTran.Rollback()
mHasException = True
Return False
Catch ex As Exception
mHasException = True
mLastException = ex
Return False
End Try
End Using
End Using
End Function
Source code
https://code.msdn.microsoft.com/Working-with-parameterized-4f806d26
Summary
Using parameters prevents SQL-Injection attacks, for instance without parameters a hacker can inject their condition into your WHERE statement to get at user information or perform an INSERT into your database giving them access to your system. Another reason for using parameters is to escape string values, e.g. SELECT field-list FROM Customers WHERE LastName = ‘O’ Reilly’, in this case, we have invalid syntax for the condition. Some developer might simply use String.Replace to fix this. Keeping with strings, this means more work on the developers part plus clutters the code which composes the SQL statement. What applies to strings applies to numbers e.g. no apostrophes are required, you never have to think about this. Dates and other types are configured for you also.
SQL-Injection example
Parameterized SQL performs better, no string concatenation, as mentioned above, no escaping of data. A more generic query form is presented to the database, so it's likely already hashed and stored as a pre-compiled execution plan.
Having this information provides fewer errors and more secure application. Ignoring parameters opens the doors to attacks from hackers and error-prone applications. Take the time to work parameters into your data-centric applications which in the end is what knowledgeable developer do no matter if they are just starting out to the season developer.
See also
SqlCommand.Parameters Property
Configuring Parameters and Parameter Data Types