VB.NET Parameters Array SQL Server

[stustarz]

Hello all!

 

Basically im trying to execute a stored procedure which accepts imput parameters, currently i have all the datareader code in a function:

 

Public Function ReadData(ByVal varSQLString As String)

       Try

           Dim dataConString As String

           dataConString = "Data Source=(local);" & _
                           "Initial Catalog=myDatabase;" & _
                           "Integrated Security=SSPI"

           With dataCommand
               .Connection = New SqlClient.SqlConnection(dataConString)
               .Connection.Open()
               .CommandText = varSQLString
           End With

           varDataReader = dataCommand.ExecuteReader()

       Catch varException As Exception
           MessageBox.Show(varException.Message)
       End Try

   End Function

 

I call this function using:

 

ReadData("sp_StoredProcedure")

 

If the stored procedure accepts input parameters then I would like a way to intergrate this into my function and pass them using the call i have already. I have tried creating an array and passing the array to the function, then using the function to loop through the array adding each parameter, but to no avail.

 

Can anyone give me a bit of advice as to how to go about doing this, sometimes the stored procedure will have 0 parameters and some have 2 or 3, so i need it as dynamic as possible

 

Thanks Stu

[sizer]

you can implement three functions with same name and different args ( AKA polymorfism )

, it is better solution that looping thru the array ...

 

Example

 

...
...

public Function Hello()
MessageBox.Show("Hello Mister")
End Function

public Function Hello(strFirstName as String)
MessageBox.Show("Hello Mister "  + strFirstName)
End Function

public Function Hello(strFirstName As String, strLastName As String)
MessageBox.Show("Hello Mister " + strFirstName + " " + strLastName)
End Function

...
...

 

:D

[stustarz]

HI

 

Thanks for the idea! But i was hoping there could be something a bit easier, anyone else have any ideas?

 

Thanks

 

Stu

[mocella]

Create a second ReadData(ByVal varSQLString As String, sqlParamArray() As SqlParameter)

 

This way, you only create one overload for your function and you can pass as many params as you want and of all types and directions and not have to worry about it.

 

This is right from MSDN - they also show an invalid paramaters adding as one line of code, but that's not supported:

 
Public Sub CreateMySqlCommand(myConnection As SqlConnection, _
mySelectQuery As String, myParamArray() As SqlParameter)
   Dim myCommand As New SqlCommand(mySelectQuery, myConnection)
   myCommand.CommandText = "SELECT CustomerID, CompanyName FROM Customers WHERE Country = @Country AND City = @City"
   myCommand.UpdatedRowSource = UpdateRowSource.Both
   myCommand.Parameters.Add(myParamArray) 'this does not work!!!!!
   Dim j As Integer
   For j = 0 To myCommand.Parameters.Count - 1
      myCommand.Parameters.Add(myParamArray(j))
   Next j
   Dim myMessage As String = ""
   Dim i As Integer
   For i = 0 To myCommand.Parameters.Count - 1
       myMessage += myCommand.Parameters(i).ToString() & ControlChars.Cr
   Next i
   Console.WriteLine(myMessage)
End Sub

[stustarz]

Hi

 

This is a lot more like what i had already, the problem i was having was adding the parameters into the array cause the sqlparameter requires two arguments, and i couldnt seem to get it to work.

 

Thanks very much for this!

 

Ill check MSDN a bit more about this

 

Thanks again for the tips!

 

Stu

[stustarz]

Hello all again,

 

Im still having problems here!

 

Trying to call the function, currently i have the below code. A few questions:

Do i pass an array to the function? If so, why doesnt what i have below work? Im presuming ive added the parameter correctly to the array but it just doesnt want to work!

 

Dim varSQLParamsArray()
varSQLParamsArray(0) = "'" & "@strTeamFilter'" & ",'" & varTxtTeamFilter & "'"

ReadDataParams("sp_TextTeamFilter", varSQLParamsArray)

 

Thanks again all

 

Stu