Passing an array of to a Sub

12 Jun 2006 4:25 PM

BobRoyAce

I currently have code similar to the following in a few places in my
application:

      cmd.Connection = New
SqlClient.SqlConnection(My.Settings.CERMITSConnectionString)
      cmd.CommandType = CommandType.StoredProcedure
      cmd.CommandText = "usp_Users_Insert"
      cmd.Parameters.AddRange(SQLParam)
      cmd.ExecuteNonQuery()

In this code I am executing a stored procedure (in this case
"usp_Users_Insert") and specifying an array of SqlClient.SqlParameter
objects (in this case SQLParam). What I want to do is create a function
that could replace the above code so that I could do something like
this:

ExecuteStoredProcReturnNumOfRecsAffected("usp_Users_Insert", SQLParam)

Now the array that I pass will contain varying numbers of parameters
depending on the stored procedure. How would I define such a function?

Public Function ExecuteStoredProcReturnNumOfRecsAffected(ByVal sSP as
string, ???) as Integer

Also, what if I wanted the function to be versatile enough to handle
executing stored procedures that have no parameters? Would it be better
to just create two different functions, one with a parameter array and
the other without one?

12 Jun 2006 4:36 PM

Mythran

Show quote Hide quote

"BobRoyAce" <b***@omegasoftwareinc.com> wrote in message
news:1150129551.045094.224220@f14g2000cwb.googlegroups.com...
>I currently have code similar to the following in a few places in my
> application:
>
>      cmd.Connection = New
> SqlClient.SqlConnection(My.Settings.CERMITSConnectionString)
>      cmd.CommandType = CommandType.StoredProcedure
>      cmd.CommandText = "usp_Users_Insert"
>      cmd.Parameters.AddRange(SQLParam)
>      cmd.ExecuteNonQuery()
>
> In this code I am executing a stored procedure (in this case
> "usp_Users_Insert") and specifying an array of SqlClient.SqlParameter
> objects (in this case SQLParam). What I want to do is create a function
> that could replace the above code so that I could do something like
> this:
>
> ExecuteStoredProcReturnNumOfRecsAffected("usp_Users_Insert", SQLParam)
>
> Now the array that I pass will contain varying numbers of parameters
> depending on the stored procedure. How would I define such a function?
>
> Public Function ExecuteStoredProcReturnNumOfRecsAffected(ByVal sSP as
> string, ???) as Integer
>
> Also, what if I wanted the function to be versatile enough to handle
> executing stored procedures that have no parameters? Would it be better
> to just create two different functions, one with a parameter array and
> the other without one?
>

Public Function ExecuteNonQuery( _
    ByVal ProcedureName As String, _
    ParamArray ByVal Params As SqlParameter() _
) As Integer
    ...
End Function

HTH :)

Mythran

12 Jun 2006 4:48 PM

Jim Wooley

You could accept a System.Data.Common.DBParameterCollection and pass into
it an instance of a SqlParameterCollection. That being said, you may want
to look into the Enterprise Library's Data Access block as it may already
do the kinds of things you are looking for.

Jim Wooley
http://devauthority.com/blogs/jwooley/default.aspx

Show quoteHide quote

> I currently have code similar to the following in a few places in my
> application:
>
> cmd.Connection = New
> SqlClient.SqlConnection(My.Settings.CERMITSConnectionString)
> cmd.CommandType = CommandType.StoredProcedure
> cmd.CommandText = "usp_Users_Insert"
> cmd.Parameters.AddRange(SQLParam)
> cmd.ExecuteNonQuery()
> In this code I am executing a stored procedure (in this case
> "usp_Users_Insert") and specifying an array of SqlClient.SqlParameter
> objects (in this case SQLParam). What I want to do is create a
> function that could replace the above code so that I could do
> something like this:
>
> ExecuteStoredProcReturnNumOfRecsAffected("usp_Users_Insert", SQLParam)
>
> Now the array that I pass will contain varying numbers of parameters
> depending on the stored procedure. How would I define such a function?
>
> Public Function ExecuteStoredProcReturnNumOfRecsAffected(ByVal sSP as
> string, ???) as Integer
>
> Also, what if I wanted the function to be versatile enough to handle
> executing stored procedures that have no parameters? Would it be
> better to just create two different functions, one with a parameter
> array and the other without one?
>