2007-10-29

Using SqlParameter Class

Represents a parameter to a SqlCommand and optionally its mapping to DataSet columns. This class cannot be inherited.

You should use parameters to filter queries in a secure manner. But I recommend to use parameters when you try to pass the datetime value in your query.

The process of using parameter contains two steps:

  • create SqlParameter object and insert there value with applicable properties
  • define the parameter in the SqlCommand command string, and assign the SqlParameter object to the SqlCommand object. When the SqlCommand executes, parameters will be replaced with values specified by the SqlParameter object.

Sample Code

Imports Namespace: System.Data.SqlClient

' Insert string
Dim sql As String = " INSERT INTO tblZipCode([ZIPCODE], [STATE], [CITY], [TestDate]) VALUES(@ZIPCODE, @STATE, @CITY), @TestDate"

' Create sql parameter
Dim param(3) As SqlParameter

param(0) = New SqlParameter("@ZIPCODE", SqlDbType.VarChar)
param(0).Value = "60000"

param(1) = New SqlParameter("@STATE", SqlDbType.VarChar)
param(1).Value = "Statename"

param(2) = New SqlParameter("@CITY", SqlDbType.VarChar)
param(2).Value = "Cityname"

' Recommend to use sql param when you try to send datetime value
param(3) = New SqlParameter("@TestDate", SqlDbType.DateTime)
param(3).Value = DateTime.Now

' Create Connection string
Dim sConnection As New SqlConnection("server=(local);uid=sa;pwd=pass;database=db")
sConnection.Open()

' Create Sql Command
Dim command As SqlCommand = sConnection.CreateCommand()
command.CommandText = sql

' Add Parameter to command
command.Parameters.AddRange(param)

' Execute command
Dim nResult As Integer = command.ExecuteNonQuery()

If nResult > 0 Then
Console.WriteLine("Insert completed")
End If

sConnection.Close()
command.Dispose()

4 comments:

Anonymous said...

Thanks for your sample code.
I tried to follow what you posted but an error message pop-up, "addrange is not a member of System.Data.SqlClient.SqlParameterCollection".
How can I fix it?

thanks.
K.H.

Jeffrey said...

' Insert string
Dim sql As String = " INSERT INTO tblZipCode([ZIPCODE], [STATE], [CITY], [TestDate]) VALUES(@ZIPCODE, @STATE, @CITY), @TestDate"

' Create Connection string
Dim sConnection As New SqlConnection("server=(local);uid=sa;pwd=pass;database=db")
sConnection.Open()

' Create Sql Command
Dim command As SqlCommand = sConnection.CreateCommand()
command.CommandText = sql

' Add Parameter to command
command.Parameters.AddWithValue("ZIPCODE", "60000")
command.Parameters.AddWithValue("STATE", "Statename")
command.Parameters.AddWithValue("CITY", "Cityname")
command.Parameters.AddWithValue("TestDate", DateTime.Now)

' Execute command
Dim nResult As Integer = command.ExecuteNonQuery()

If nResult > 0 Then
Console.WriteLine("Insert completed")
End If

sConnection.Close()
command.Dispose()

Anonymous said...

Just want to saу уour artіcle is aѕ astoniѕhing.

The сlаritу іn your publish iѕ simply nice and i сan
supрose you are a profеssional on this
ѕubject. Well togethег with уour permission let me to grasp
your RSS feed to stay uр tο date with approaching ρost.
Thanks 1,000,000 and please continuе the rewaгding
work.

Αlsο ѵiѕіt my weblog: hcg meal plan

Anonymous said...

Whеn somеοne ωritеs an poѕt he/she maintains the іdea оf
a user in his/hеr brain that how a user can undеrstand it.
Therefοrе that's why this article is amazing. Thanks!

Here is my webpage ... safe weight loss