In earlier version of the Enterprise Library Data Access Application Block, passing an array of parameters to a SQL stored procedure could be done like this:
Dim arParams(2) As SqlParameter
arParams(0) = (New SqlParameter("@personid", personChoice))
arParams(1) = (New SqlParameter("@sortdirection", sortdirection))
...
...
This doesn't work in The January 2006 version of DAAB.
Parameters can be passed individually in the 2006 version, like this:
Dim dbCommand As DbCommand = db.GetStoredProcCommand("GetPerson")
db.AddInParameter(dbCommand, "@personid", personChoice)
db.AddInParameter(dbCommand, "@sortdirection", sortdirection)
Dim productDataSet As DataSet = db.ExecuteDataSet(dbCommand)
But how is an array of parameters passed in? The documentation does not seem to provide an example.
This is a real elusive problem. I have scoured the Internet for about 2 hours now and have found absolutely nothing about senting parameter arrays to the new DAAB 2006. It was the "usual" way of using the old DAAB, but suddenly no one is writing about it.
...and the docs completely skip this issue.
Can anyone help in providing some code samples of using an array of parameter objects with the new DAAB?
If you use a data layer tier at all, then your code using the DAAB isn't directly connected with form data (for instance) that you might want to insert into the database.
same problem. i dont think there is a way to do it. the previous version had no porbs with it. so why not try the previous version. the problem with previous version is that connection string cannot be manullay set in wihtout the web.config
I'm not sure what the big mystery is. The new DAAB promotes the practice of adding the parameters directly to the DbCommand, but if you prefer the old way, just write your own helper function (pseudo-code):
Public Shared Function ExecuteDataset(ByVal commandType As CommandType, _
ByVal commandText As String, _
ByVal ParamArray commandParameters() As SqlParameter) As DataSet
Dim db As Database = DatabaseFactory.CreateDatabase()
Dim cmd As DbCommand
If commandType = CommandType.StoredProcedure Then
cmd = db.GetStoredProcCommand(commandText)
Else
cmd = db.GetSqlStringCommand(commandText)
End If
If Not commandParameters Is Nothing Then
For Each param As SqlParameter In commandParameters
db.AddInParameter(cmd, param.Name, param.Type, param.Value)
Next
End If
1 Dim db
As Database = DatabaseFactory.CreateDatabase("Your Connection") 2 Dim sData As String =
"abcdefg" 3 Dim iData As Integer = 123 4 Dim bData As Boolean =
True 5 Dim oDS2 As DataSet = db.ExecuteDataSet("testproc",
New Object() {sData, iData, bData}) 6 OR 7 Dim oDS1 As DataSet = db.ExecuteDataSet("testproc",
New Object() {"ABC", 321,
False})
You can also create the parameter array on the stack and pass it in instead of doing it directly on the call (just like you did), but it's an array of Object instead of SqlParameter (which is specific to SqlClient namespace).
Good point, Hoang. Personally, I am biased towards the dbCommand.AddInParameter approach for readability and maintainability, but then I rarely type lines like that out by hand, preferring to use code generation to spit out all that stuff for me. [:D]
MikeAtSoftwa...
Member
5 Points
1 Post
Parameter arrays in DAAB 2006
May 04, 2006 02:54 PM|LINK
In earlier version of the Enterprise Library Data Access Application Block, passing an array of parameters to a SQL stored procedure could be done like this:
Dim arParams(2) As SqlParameter
arParams(0) = (New SqlParameter("@personid", personChoice))
arParams(1) = (New SqlParameter("@sortdirection", sortdirection))
...
...
ds = SqlHelper.ExecuteDataset(SqlConn, CommandType.StoredProcedure, "GetProducts", arParams)
This doesn't work in The January 2006 version of DAAB.
Parameters can be passed individually in the 2006 version, like this:
Dim dbCommand As DbCommand = db.GetStoredProcCommand("GetPerson")
db.AddInParameter(dbCommand, "@personid", personChoice)
db.AddInParameter(dbCommand, "@sortdirection", sortdirection)
Dim productDataSet As DataSet = db.ExecuteDataSet(dbCommand)
But how is an array of parameters passed in? The documentation does not seem to provide an example.
Level 7 Solu...
Member
7 Points
4 Posts
Re: Parameter arrays in DAAB 2006
Jun 02, 2006 05:03 AM|LINK
Can anyone help in providing some code samples of using an array of parameter objects with the new DAAB?
If you use a data layer tier at all, then your code using the DAAB isn't directly connected with form data (for instance) that you might want to insert into the database.
sdbala
Member
55 Points
11 Posts
Re: Parameter arrays in DAAB 2006
Jul 26, 2006 03:36 AM|LINK
Triax
Member
511 Points
100 Posts
Re: Parameter arrays in DAAB 2006
Jul 26, 2006 03:33 PM|LINK
Public Shared Function ExecuteDataset(ByVal commandType As CommandType, _
ByVal commandText As String, _
ByVal ParamArray commandParameters() As SqlParameter) As DataSet
Dim db As Database = DatabaseFactory.CreateDatabase()
Dim cmd As DbCommand
If commandType = CommandType.StoredProcedure Then
cmd = db.GetStoredProcCommand(commandText)
Else
cmd = db.GetSqlStringCommand(commandText)
End If
If Not commandParameters Is Nothing Then
For Each param As SqlParameter In commandParameters
db.AddInParameter(cmd, param.Name, param.Type, param.Value)
Next
End If
Return db.ExecuteDataset(cmd)
End Function 'ExecuteDataset
Or did I misunderstand the question?
Noonan Consulting Inc.
EasyObjects.NET - The O/RM architecture for the Enterprise Library
Hoang
Member
38 Points
10 Posts
Re: Parameter arrays in DAAB 2006
Nov 28, 2006 07:10 PM|LINK
Here is how it should be:
1 Dim db As Database = DatabaseFactory.CreateDatabase("Your Connection")
2 Dim sData As String = "abcdefg"
3 Dim iData As Integer = 123
4 Dim bData As Boolean = True
5 Dim oDS2 As DataSet = db.ExecuteDataSet("testproc", New Object() {sData, iData, bData})
6 OR
7 Dim oDS1 As DataSet = db.ExecuteDataSet("testproc", New Object() {"ABC", 321, False})
You can also create the parameter array on the stack and pass it in instead of doing it directly on the call (just like you did), but it's an array of Object instead of SqlParameter (which is specific to SqlClient namespace).
It's just more generic now.
--
Hoang
Enterprise Library 2.0 Introduction Error "Enterprise Library 2.0"
Triax
Member
511 Points
100 Posts
Re: Parameter arrays in DAAB 2006
Nov 28, 2006 07:26 PM|LINK
Noonan Consulting Inc.
EasyObjects.NET - The O/RM architecture for the Enterprise Library