Passing an array of to a Sub

  • Thread starter Thread starter BobRoyAce
  • Start date Start date
B

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?
 
BobRoyAce said:
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?
Click to expand...

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

HTH :)

Mythran
 
Back
Top