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;") conn.Open() ' 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 cmd.Parameters.AddWithValue("@username", "blake") ' 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" ' cmd.Parameters.Add(param) Dim dr As System.Data.SqlClient.SqlDataReader = cmd.ExecuteReader While dr.Read MessageBox.Show(dr("username").ToString) End While ' Close the data reader, we always want to do this when we're done. dr.Close() End Using conn.Close() End Using
The prefix of a parameter with SQL Server is @. This may change depending on what provider you use (for instance, MySql uses a ?).