VB.NET adding command parameters dynamically

[mnarewec]

Hi folks,
I want to create a procedure where by it will take a sqlcommand, a string, and property.
I want to use this procedure to add parameters to my sql command of type stored procedures since I have a lot parameters to pass into the store procedure.

Here is current my procedure to call store procedure to insert a record:

[CODE=vbnet]Public Function AddPreviousAddr ess() As Integer
Try

cnnCPSS.Open()
cmdCPSS = New SqlCommand("pr_ tblNearestRelat ive_Insert", cnnCPSS)
cmdCPSS.Command Type = CommandType.Sto redProcedure
cnnCPSS.Open()
cmdCPSS = New SqlCommand("pr_ tblPreviousAddr ess_Insert", cnnCPSS)
cmdCPSS.Command Type = CommandType.Sto redProcedure
cmdCPSS.Paramet ers.Add("@Matte rNumber", MatterNumber)
cmdCPSS.Paramet ers.Add("@CIF", CIF)
cmdCPSS.Paramet ers.Add("@Secti onNumber", SectionNumber)
cmdCPSS.Paramet ers.Add("@LotNu mber", LotNumber)
cmdCPSS.Paramet ers.Add("@Stree tName", StreetName)
cmdCPSS.Paramet ers.Add("@Subur b_Village", Suburb_Village)
cmdCPSS.Paramet ers.Add("@Town" , Town)
cmdCPSS.Paramet ers.Add("@SubPr ovince", SubProvince)
cmdCPSS.Paramet ers.Add("@Provi nce", Province)
cmdCPSS.Paramet ers.Add("@PostC ode", PostCode)
cmdCPSS.Paramet ers.Add("@TimeT hereYears", TimeThereYears)
cmdCPSS.Paramet ers.Add("@TimeT hereMonths", TimeThereMonths )
Return cmdCPSS.Execute NonQuery()
Catch ex As Exception
Throw New Exception(ex.Me ssage)
Return 0
Finally
cnnCPSS.Close()
End Try
End Function
[/CODE]
After the new procedure it will be like this:
[CODE=vbnet]Public Function AddPreviousAddr ess() As Integer
Try
cnnCPSS.Open()
cmdCPSS = New SqlCommand("pr_ tblPreviousAddr ess_Insert", cnnCPSS)
cmdCPSS.Command Type = CommandType.Sto redProcedure
AddSQLParameter s(cmdCPSS, "MatterNumb er", "CIF", "SectionNumber" , "LotNumber" , _
"StreetName ", "Suburb_Village ", "Town", "SubProvinc e", "Province", _
"PostCode", "TimeThereYears ", "TimeThereMonth s")
Return cmdCPSS.Execute NonQuery()

Catch ex As Exception
Throw New Exception(ex.Me ssage)
Return 0
Finally
cnnCPSS.Close()
End Try
End Function

Private Sub AddSQLParameter s(ByVal cmd As SqlCommand, ByVal ParamArray cols As String())
Try
Dim i As Integer
For i = 0 To UBound(cols)
cmd.Parameters. Add("@" & cols(i), SqlDbType.Char, 0, cols(i))
Next
Catch ex As Exception
Throw New Exception(ex.Me ssage)
End Try
End Sub
[/CODE]
This "AddSQLParamete rs" is not working as expected.

Pls provide help or suggest what to do achieve this idea.

Thanks.

[Shashi Sadasivan]

Hi folks,
I want to create a procedure where by it will take a sqlcommand, a string, and property.
I want to use this procedure to add parameters to my sql command of type stored procedures since I have a lot parameters to pass into the store procedure.

Here is current my procedure to call store procedure to insert a record:

[CODE=vbnet]Public Function AddPreviousAddr ess() As Integer
Try

cnnCPSS.Open()
cmdCPSS = New SqlCommand("pr_ tblNearestRelat ive_Insert", cnnCPSS)
cmdCPSS.Command Type = CommandType.Sto redProcedure
cnnCPSS.Open()
cmdCPSS = New SqlCommand("pr_ tblPreviousAddr ess_Insert", cnnCPSS)
cmdCPSS.Command Type = CommandType.Sto redProcedure
cmdCPSS.Paramet ers.Add("@Matte rNumber", MatterNumber)
cmdCPSS.Paramet ers.Add("@CIF", CIF)
cmdCPSS.Paramet ers.Add("@Secti onNumber", SectionNumber)
cmdCPSS.Paramet ers.Add("@LotNu mber", LotNumber)
cmdCPSS.Paramet ers.Add("@Stree tName", StreetName)
cmdCPSS.Paramet ers.Add("@Subur b_Village", Suburb_Village)
cmdCPSS.Paramet ers.Add("@Town" , Town)
cmdCPSS.Paramet ers.Add("@SubPr ovince", SubProvince)
cmdCPSS.Paramet ers.Add("@Provi nce", Province)
cmdCPSS.Paramet ers.Add("@PostC ode", PostCode)
cmdCPSS.Paramet ers.Add("@TimeT hereYears", TimeThereYears)
cmdCPSS.Paramet ers.Add("@TimeT hereMonths", TimeThereMonths )
Return cmdCPSS.Execute NonQuery()
Catch ex As Exception
Throw New Exception(ex.Me ssage)
Return 0
Finally
cnnCPSS.Close()
End Try
End Function
[/CODE]
After the new procedure it will be like this:
[CODE=vbnet]Public Function AddPreviousAddr ess() As Integer
Try
cnnCPSS.Open()
cmdCPSS = New SqlCommand("pr_ tblPreviousAddr ess_Insert", cnnCPSS)
cmdCPSS.Command Type = CommandType.Sto redProcedure
AddSQLParameter s(cmdCPSS, "MatterNumb er", "CIF", "SectionNumber" , "LotNumber" , _
"StreetName ", "Suburb_Village ", "Town", "SubProvinc e", "Province", _
"PostCode", "TimeThereYears ", "TimeThereMonth s")
Return cmdCPSS.Execute NonQuery()

Catch ex As Exception
Throw New Exception(ex.Me ssage)
Return 0
Finally
cnnCPSS.Close()
End Try
End Function

Private Sub AddSQLParameter s(ByVal cmd As SqlCommand, ByVal ParamArray cols As String())
Try
Dim i As Integer
For i = 0 To UBound(cols)
cmd.Parameters. Add("@" & cols(i), SqlDbType.Char, 0, cols(i))
Next
Catch ex As Exception
Throw New Exception(ex.Me ssage)
End Try
End Sub
[/CODE]
This "AddSQLParamete rs" is not working as expected.

Pls provide help or suggest what to do achieve this idea.

Thanks.

Hi,
Id this code working for you?

cmdCPSS.Paramet ers.Add("@Matte rNumber", MatterNumber)

the best function method i found was parameter.Add(p aramString, SqlDBType)
What you may need is

parameters.AddW ithValue(paramS tring, objectvalue);

Going ahead with dynamically adding parameters:
1. a function can only have a limited number of parameter being passed to it.
The only way to change this is dynamically generate code for the function you want, and then compile it, and then call it (not a good idea...and im totally going out of the way)

2. You would have to repetitively call the Parameters.AddW ithValue method until all params are exhausted.

Have you tried using arrays?
I may use Dictionary<stri ng,object> here
Fill the dictionary with the parameter string and value(as object).
once done, iterate through the dictionary, and for each entry call the param.AddWithVa lue and pass the string object value contained for that index in the dictionary.

Hope this makes some sense.

PS: Dictionary might be a bit slow here as they are meant to be for random searches rather than iteration. (but it will do the work)

[Shashi Sadasivan]

Hi,
Id this code working for you?

cmdCPSS.Paramet ers.Add("@Matte rNumber", MatterNumber)

the best function method i found was parameter.Add(p aramString, SqlDBType)
What you may need is

parameters.AddW ithValue(paramS tring, objectvalue);

Going ahead with dynamically adding parameters:
1. a function can only have a limited number of parameter being passed to it.
The only way to change this is dynamically generate code for the function you want, and then compile it, and then call it (not a good idea...and im totally going out of the way)

2. You would have to repetitively call the Parameters.AddW ithValue method until all params are exhausted.

Have you tried using arrays?
I may use Dictionary<stri ng,object> here
Fill the dictionary with the parameter string and value(as object).
once done, iterate through the dictionary, and for each entry call the param.AddWithVa lue and pass the string object value contained for that index in the dictionary.

Hope this makes some sense.

PS: Dictionary might be a bit slow here as they are meant to be for random searches rather than iteration. (but it will do the work)

[mnarewec]

Hi,
Id this code working for you?
Quote:
Originally Posted by
cmdCPSS.Paramet ers.Add("@Matte rNumber", MatterNumber)

Yes it is. I am using VS 2003

[Shashi Sadasivan]

well...
.Net 1.1 does not support Generics unfortunately (Dictionary falls in that category)

you will have to use arraylist in that case.

However you would have a source for the parameter strings?
It would be better if you added it to the parameter list at that time itself

[mnarewec]

well...
.Net 1.1 does not support Generics unfortunately (Dictionary falls in that category)

you will have to use arraylist in that case.

However you would have a source for the parameter strings?
It would be better if you added it to the parameter list at that time itself

Actually this works.

The input Parameters are actually, properties of the class (i.e. fields of the table). THerefore, eventhough the parameters (properties) are added before the values are being known, when the properties are set later on the parameters values changes and so it works