Passing an array of to a Sub

[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?

 

[Mythran]

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

 

[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