sqlparametercollection as function argument

[lamy]

was thinking of putting something like this in my class :D

Private Sub Test()
  Dim sqlCmd As New SqlCommand
  Dim param As SqlParameterCollection = sqlCmd.Parameters

  param.Add("@Branch_ID", SqlDbType.VarChar, 6, Session("branch_id"))
  execSP("spTest", param)
End Sub

Public Sub execSP(ByVal spName As String, ByVal param As SqlParameterCollection)
  Dim com As New SqlCommand
  With com
     .CommandType = CommandType.StoredProcedure
     .Parameters.Clear()
           For Each p As SqlParameter In param
                 .Parameters.Add(p)
           Next
     ...
  End With
End Sub

 

but it gives this exception "The SqlParameter is already contained by another SqlParameterCollection."

 

anyone? :confused:

Edited January 24, 2006 by lamy

[lamy]

i did some changes, i got it working now, but i wonder if theres an alternative to this

 

heres the changes:

 

1 - adding the parameter (value doesnt show, wrong argument column, eheheh)

param.Add("@Branch_ID", SqlDbType.VarChar, 6).Value = Session("branch_id")

 

2 - parsing the collection and assigning it to command parameter (resolves the exception)

                   For Each p As SqlParameter In param
                       Dim par As New SqlParameter
                       With par
                           .ParameterName = p.ParameterName
                           .SqlDbType = p.SqlDbType
                           .Size = p.Size
                           .Value = p.Value
                       End With
                       .Parameters.Add(par)
                   Next

[Igor Sukhov]

To enforce a good design practice SqlParameterCollection.Add method would not allow to add a SqlParameter object into collection if it is already added (thus "OWNED") to another collection.

 

Whereas SqlParameter type explicitly implements IClonable interface you could just use its Clone method to create the exact copy of the parameter.

[Joe Mamma]

here is how. . . uses oracle, but the same thing. . .

look at the last post

This is Net 2.0

 

In 1.x

ParamDictionary would inherit from a hashtable

 

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=185380&SiteID=1

[lamy]

thanks Igor Sukhov and Joe Mamma, im trying out joe's post

 

Imports System
Imports System.Data.SqlClient
Imports System.Collections.Generic

Private Class ParamDictionary
   Inherits Dictionary(Of String, Object)
End Class

Private Sub Test()
       Dim param As New ParamDictionary()
       param("@Branch_ID") = "something"
       execSP("spTest", param)
End Sub

private Sub execSP(ByVal spName As String, ByVal param As ParamDictionary)
  Dim com As New SqlCommand
  With com
     ... suppose theres a connection ;)
     .CommandType = CommandType.StoredProcedure
     .Parameters.Clear()
           For Each p As String In param.Keys
                 .Parameters.AddWithValues(p, param(p))
           Next
     ... execute and return goes here (if as a function)
  End With
End Sub

 

but what if i wanted to set a size for each parameter and hows about a parameter output (direction)?

 

anyway, my first try works but i couldnt figure out how to pass a parameter as an output direction

 

this doesnt work

param.Add("@retval", SqlDbType.VarChar, 15).Direction = ParameterDirection.Output

[lamy]

i was able to resolve it, seems that i throws an exception if i dont put a value to it so i set a value to it

 

param.Add("@retval", SqlDbType.VarChar, 15).Direction = ParameterDirection.Output
param("@retval").Value = String.Empty

 

after testing Joe's post i made some changes to my codes, no need to create a new instance of SqlParameter, and the code is much shorter :D

 

           For Each p As SqlParameter In param
                 .Parameters.AddWithValues(p, param(p))
           Next