I’m going to provide a simple example and explain the benefits (and need). Let’s assume that you have table called “user_table” with one field that is “username”. This table resides in a database called “WebServices”. The below would be a way to execute a parameterized query that searches the table for a specific user, returns a data reader and then iterates over it message boxing the username (in a WinForms app).
Reasons why this is important to you:
' 1.) Create and open the connection, the using will handle the "Dispose" call.
Using conn As New System.Data.SqlClient.SqlConnection("server=localhostSQLExpress;database=WebServices;trusted_connection=yes;")
' 2.) Create a SQL command that's assigned to the connection, again, the using will handle the "Dispose" call when your done.
Using cmd As System.Data.SqlClient.SqlCommand = conn.CreateCommand
' The SQL statement we want to execute with the parameter. @ is the parameter prefix for SQL Server/Express/Compact
cmd.CommandText = "select * from user_table where username=@username"
' We can add this two ways, one where we give the command as much detail about @username as possible
' and one where we just provide a value and the command interprets what it should be
' Method two, specifically create a parameter, doing this is more code but will give you more flexibility especially
' when defining parameters that require precision on numeric values, etc.
' Dim param As New System.Data.SqlClient.SqlParameter("@username", SqlDbType.NVarChar, 100)
' param.Value = "blake"
Dim dr As System.Data.SqlClient.SqlDataReader = cmd.ExecuteReader
' Close the data reader, we always want to do this when we're done.
The prefix of a parameter with SQL Server is @. This may change depending on what provider you use (for instance, MySql uses a ?).