How send SP parameters to the function, which creates dataReader

  • Thread starter Thread starter simon
  • Start date Start date
S

simon

Always when I need data reader in my programs, I simply have functions,
which creates it for me:

Dim rdr As SqlDataReader
dim sql as string

sql="myStoredProcedure"
rdr = createDataReader(sql, False)

And the functions are:

Function createDataReader(ByVal sqlStr As String, Optional ByVal type As
Boolean = False) As SqlDataReader
Dim oCmd As SqlCommand
Dim myReader As SqlDataReader

oCmd = New SqlCommand(sqlStr, createConnection)
If type = False Then
oCmd.CommandType = CommandType.StoredProcedure
End If
myReader = oCmd.ExecuteReader(CommandBehavior.CloseConnection)
Return myReader
End Function

Function createConnection() As SqlConnection
Dim myConn As SqlConnection
myConn = New
SqlConnection(ConfigurationSettings.AppSettings("appStrConnection"))
myConn.Open()
createConnection = myConn
End Function


It works fine, but what if my stored procedure has parameters. You don't
know how many and you don't know the type of them.
Does anybody know how to change this function, that it will except the x
parameters of x type?

I think that in program you create some array type, fill it with parameters
and then send this array to function and in function create as many
parameters as array size is or something similar. Any idea?

Thank you for your help,
Simon
 
You can create an ArrayList or any collection of Parameter objects, then
just loop through the collection and add them to the Command object's
parameters collection
SqlCommand cmd = new SqlCommand("SomeSelectStatement", SomeConnection);
for(int i = 0; i < ArrayList.Count-1; i++){
cmd.Parameters.Add((SqlParameter)myArrayList);

}
 
well I create an array of parameters and send it to the function which
creates the dataReader:

Dim arrayOfPar As ArrayList
Dim par1 As SqlParameter
Dim par2 As SqlParameter
par1 = New SqlParameter("@mediaId", SqlDbType.Int)
par1.Value = 1
arrayOfPar.Add(par1) ' HERE I GET AN ERROR MESSAGE
par2 = New SqlParameter("@datumStart", SqlDbType.DateTime)
par2.Value = date.Now
arrayOfPar.Add(par2)

rdr = createDataReader(sql, False,arrayOfPar)

How I declare the array of arameters in my function?

Function createDataReader(ByVal sqlStr As String, Optional ByVal type As
Boolean = False, Optional ByVal arrayOfPar As ArrayList = Nothing) As
SqlDataReader

Dim oCmd As SqlCommand
Dim myReader As SqlDataReader
Dim i As Int16
oCmd = New SqlCommand(sqlStr, createConnection)
If type = False Then
oCmd.CommandType = CommandType.StoredProcedure
If Not arrayOfPar Is Nothing Then
For i = 0 To arrayOfPar.Count() - 1
oCmd.Parameters.Add(arrayOfPar(i))
Next
End If
End If
myReader = oCmd.ExecuteReader(CommandBehavior.CloseConnection)
Return myReader
End Function

Function createConnection() As SqlConnection
Dim myConn As SqlConnection
myConn = New
SqlConnection(ConfigurationSettings.AppSettings("appStrConnection"))
myConn.Open()
createConnection = myConn
End Function

What is wrong here?
I get an error message:
Exception Details: System.NullReferenceException: Object reference not set
to an instance of an object.

Thank you,
Simon


William Ryan eMVP said:
You can create an ArrayList or any collection of Parameter objects, then
just loop through the collection and add them to the Command object's
parameters collection
SqlCommand cmd = new SqlCommand("SomeSelectStatement", SomeConnection);
for(int i = 0; i < ArrayList.Count-1; i++){
cmd.Parameters.Add((SqlParameter)myArrayList);

}


simon said:
Always when I need data reader in my programs, I simply have functions,
which creates it for me:

Dim rdr As SqlDataReader
dim sql as string

sql="myStoredProcedure"
rdr = createDataReader(sql, False)

And the functions are:

Function createDataReader(ByVal sqlStr As String, Optional ByVal type As
Boolean = False) As SqlDataReader
Dim oCmd As SqlCommand
Dim myReader As SqlDataReader

oCmd = New SqlCommand(sqlStr, createConnection)
If type = False Then
oCmd.CommandType = CommandType.StoredProcedure
End If
myReader = oCmd.ExecuteReader(CommandBehavior.CloseConnection)
Return myReader
End Function

Function createConnection() As SqlConnection
Dim myConn As SqlConnection
myConn = New
SqlConnection(ConfigurationSettings.AppSettings("appStrConnection"))
myConn.Open()
createConnection = myConn
End Function


It works fine, but what if my stored procedure has parameters. You don't
know how many and you don't know the type of them.
Does anybody know how to change this function, that it will except the x
parameters of x type?

I think that in program you create some array type, fill it with parameters
and then send this array to function and in function create as many
parameters as array size is or something similar. Any idea?

Thank you for your help,
Simon
Click to expand...
Click to expand...
 
arrayOfPar.Add(par1) ' HERE I GET AN ERROR MESSAGE

You have not created an instace of arrayOfPar ...
Dim arrayOfPar As ArrayList
Click to expand...

Should read:

Dim arrayOfPar As ArrayList = New ArrayList

You can't add something to something that doesn't exist!

simon said:
well I create an array of parameters and send it to the function which
creates the dataReader:

Dim arrayOfPar As ArrayList
Dim par1 As SqlParameter
Dim par2 As SqlParameter
par1 = New SqlParameter("@mediaId", SqlDbType.Int)
par1.Value = 1
arrayOfPar.Add(par1) ' HERE I GET AN ERROR MESSAGE
par2 = New SqlParameter("@datumStart", SqlDbType.DateTime)
par2.Value = date.Now
arrayOfPar.Add(par2)

rdr = createDataReader(sql, False,arrayOfPar)

How I declare the array of arameters in my function?

Function createDataReader(ByVal sqlStr As String, Optional ByVal type As
Boolean = False, Optional ByVal arrayOfPar As ArrayList = Nothing) As
SqlDataReader

Dim oCmd As SqlCommand
Dim myReader As SqlDataReader
Dim i As Int16
oCmd = New SqlCommand(sqlStr, createConnection)
If type = False Then
oCmd.CommandType = CommandType.StoredProcedure
If Not arrayOfPar Is Nothing Then
For i = 0 To arrayOfPar.Count() - 1
oCmd.Parameters.Add(arrayOfPar(i))
Next
End If
End If
myReader = oCmd.ExecuteReader(CommandBehavior.CloseConnection)
Return myReader
End Function

Function createConnection() As SqlConnection
Dim myConn As SqlConnection
myConn = New
SqlConnection(ConfigurationSettings.AppSettings("appStrConnection"))
myConn.Open()
createConnection = myConn
End Function

What is wrong here?
I get an error message:
Exception Details: System.NullReferenceException: Object reference not set
to an instance of an object.

Thank you,
Simon


William Ryan eMVP said:
You can create an ArrayList or any collection of Parameter objects, then
just loop through the collection and add them to the Command object's
parameters collection
SqlCommand cmd = new SqlCommand("SomeSelectStatement", SomeConnection);
for(int i = 0; i < ArrayList.Count-1; i++){
cmd.Parameters.Add((SqlParameter)myArrayList);

}


simon said:
Always when I need data reader in my programs, I simply have functions,
which creates it for me:

Dim rdr As SqlDataReader
dim sql as string

sql="myStoredProcedure"
rdr = createDataReader(sql, False)

And the functions are:

Function createDataReader(ByVal sqlStr As String, Optional ByVal type As
Boolean = False) As SqlDataReader
Dim oCmd As SqlCommand
Dim myReader As SqlDataReader

oCmd = New SqlCommand(sqlStr, createConnection)
If type = False Then
oCmd.CommandType = CommandType.StoredProcedure
End If
myReader = oCmd.ExecuteReader(CommandBehavior.CloseConnection)
Return myReader
End Function

Function createConnection() As SqlConnection
Dim myConn As SqlConnection
myConn = New
SqlConnection(ConfigurationSettings.AppSettings("appStrConnection"))
myConn.Open()
createConnection = myConn
End Function


It works fine, but what if my stored procedure has parameters. You don't
know how many and you don't know the type of them.
Does anybody know how to change this function, that it will except the x
parameters of x type?

I think that in program you create some array type, fill it with parameters
and then send this array to function and in function create as many
parameters as array size is or something similar. Any idea?

Thank you for your help,
Simon
Click to expand...
Click to expand...
Click to expand...
 
Back
Top