ExecuteSprocAccessor with parameters

Topics: Data Access Application Block
May 28, 2010 at 7:48 PM

Sirs:
I've been struggling with the ExecuteSprocAccessor syntax available in the Enterprise Library 5.0 DAAB for hours now, I can't get it working. I just need a little help.

I have a stored procedure that returns one column, and one row. The column (SOPNUMBE) is a char(31).

I have created a class with the same name as the table (SOP10100) and it has one property (my SOPNUMBE field)

The task is to use ExecuteSprocAccessor to assign the query (FP_SOP10100_SEL_byID) resultset to the class and using parameters, as advertised.

Here's my code:

    Function getOrder(ByVal strSopnumber As String, ByVal intSoptype As Int16) As SOP10100
        Try

            Dim db As Database
            db = EnterpriseLibraryContainer.Current.GetInstance(Of Database)("TWO")

            Dim myParams(1) As SqlParameter

            Dim myParam As New SqlParameter
            myParam.ParameterName = "@sopnumbe"
            myParam.Value = strSopnumber
            myParams(0) = myParam

            myParam = New SqlParameter
            myParam.ParameterName = "@soptype"
            myParam.Value = intSoptype
            myParams(1) = myParam

            Dim s As SOP10100 = db.ExecuteSprocAccessor(Of SOP10100)("FP_SOP10100_SEL_byID", myParams)
            Return s
        Catch ex As Exception
            Throw ex
        End Try

    End Function

I'm currently getting:
Unable to cast object of type '<Execute>d__0[WindowsApplication1.SOP10100]' to type 'WindowsApplication1.SOP10100'.

Grrrrr. Can anyone offer assistance?

May 31, 2010 at 2:14 AM

The ExecureSprocAccessor returns an IEnumerable.  So you should be declaring the s variable as IEnumerable(Of SOP10100).

However, if you're only returning a single value, you're better off using the ExecuteScalar rather than ExecuteSprocAccessor.  Besides, if you chose to add another property in your SOP10100 class, this will create problem since the ExecuteSprocAccessor will not be able to properly map the values to your class since your query returns only one while your class has more than one properties.

 

Sarah Urmeneta
Global Technology & Solutions
Avanade, Inc.
entlib.support@Avanade.com

Jun 1, 2010 at 4:12 PM

Progress, (thanks!) but it just got me to a different error. Actually, I got two suggestions.

Yours:

            Dim s As IEnumerable(Of SOP10100)
            s = db.ExecuteSprocAccessor(Of SOP10100)("dbo.FP_SOP10100_SEL_byID", myParams)

Another way:

            Dim s As SOP10100 = db.ExecuteSprocAccessor(Of SOP10100)("FP_SOP10100_SEL_byID", myParams).SingleOrDefault

(note the '.SingleOrDefault')

Both ways get me this:

Failed to convert parameter value from a SqlParameter to a String.
Object must implement IConvertible.

I tried (over several hours) to get this working. I changed the parameter to a Generic Collection and that got rid of the "IConvertible" error, but now I'm getting 

The number of parameters does not match number of values for stored procedure

There are a lot of complaints about this error, but few solutions. I also tried passing a sqlCommand.parameters collection with the same results

My current code, using a Generic List of SQLParameters:

 
    Function getOrder2(ByVal strSopnumber As String, ByVal intSoptype As Int16) As SOP10100
        Try
            Dim db As Database
            db = EnterpriseLibraryContainer.Current.GetInstance(Of Database)("TWO")
           

            Dim myParams As New List(Of SqlParameter)
            Dim myParam As New SqlParameter
            myParam.ParameterName = "@sopnumbe"
            myParam.Value = strSopnumber
            myParams.Add(myParam)

            myParam = New SqlParameter
            myParam.ParameterName = "@soptype"
            myParam.Value = intSoptype
            myParams.Add(myParam)


            Dim s As SOP10100 = db.ExecuteSprocAccessor(Of SOP10100)("dbo.FP_SOP10100_SEL_byID", myParams).SingleOrDefault
            Return s
        Catch ex As Exception
            Throw ex
        End Try
    End Function

 

Jun 1, 2010 at 6:32 PM
Edited Jun 1, 2010 at 6:36 PM

More progress...

The call now goes through to SQL...

It works if I supply a comma separated list of parameters. The overload calls for a "ParamArray ParamaterValues{} as Object"...

But the SOP10100 class is not getting populated. When I stop the code and look at it, it's empty. It's not getting populated by the code.

    Function getOrder5(ByVal strSopnumber As String, ByVal intSoptype As Int16) As SOP10100
       Try
            Dim db As Database
           db = EnterpriseLibraryContainer.Current.GetInstance(Of Database)("TWO")
            Dim s As SOP10100 = db.ExecuteSprocAccessor(Of SOP10100)("dbo.FP_SOP10100_SEL_byID", "ORDST2225", 2).SingleOrDefault
           Return s
        Catch ex As Exception
           Throw ex
       End Try
    End Function

 

Jun 2, 2010 at 1:54 AM

Have you verified that using the parameters you supplied, you're able to get a result?  Just to double check, run a sql profiler, run your app in debug mode and right after it finishes executing the call to ExecuteSprocAccessor, check the sql profiler, look for the sql statement which executes your stored procedure and copy paste it to sql management studio and then execute it.  Check if it returns a result.

 

Sarah Urmeneta
Global Technology & Solutions
Avanade, Inc.
entlib.support@Avanade.com

Jun 2, 2010 at 6:22 AM

The other thing you should check with this overload is if the result column name coming back from the database matches EXACTLY (including case) with the name of your object's property. If they don't match exactly, they won't get mapped without supplying an explicit output mapper, and you'd get the symptoms you describe.

 

Jun 2, 2010 at 4:29 PM

Appreciate the help. I'm seeing the same thing that you're seeing... but all the on-line examples are just copies of the Microsoft examples... and they don't run. Really. The code samples that you have above don't run.

I did open Profiler and I'm seeing the data access happen.

I altered the stored proc to return a constant, the parameters are not really used. (I'll need them in the production code, but for now I'm trying to simplify)

In testing, I've found that if I alter my stored proc to return no rows, the code crashes (that's good, it means I have a good connection to the db) but *no matter what the fields are* the results are the same, I get the default class and not the populated class.

So...

I'm thinking that the 'mapping' is not working as advertised and I'm trying to get the iRowMapper to work. The code that I have below crashes with

The expression must contain a MemberAccessExpression to a property ( t =>t.Property).

This appears to be a Lambda expression, but I can't make it work. VB crashes and says that 't' isn't defined.

My stored proc (note that there is no real data access, it returns a constant):

CREATE PROCEDURE dbo.FP_SOP10100_SEL_byID     
    
@sopnumbe varchar(31),     
@soptype smallint     
    
AS     
select convert(int,2) as SOPTYPE
 

Note that this is a complete code example, it includes the class needed.

 

Imports Microsoft.Practices.EnterpriseLibrary.Data
Imports Microsoft.Practices.EnterpriseLibrary.Common.Configuration
Public Class Form1
    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Try
            Dim db As Database
            db = EnterpriseLibraryContainer.Current.GetInstance(Of Database)("TWO")

            Dim rowmapper As IRowMapper(Of SOP10100)
            rowmapper = MapBuilder(Of SOP10100).MapNoProperties _
                .Map(Function(x) x.SOPTYPE).ToColumn("SOPTYPE") _
                .Build()

            Dim s = db.ExecuteSprocAccessor(Of SOP10100)("dbo.FP_SOP10100_SEL_byID", rowmapper, "ORDST2225", 2).SingleOrDefault
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

        Me.Close()

    End Sub

End Class

    Public Class SOP10100
        Public SOPTYPE As Int32 = 1
    End Class
 

Jun 2, 2010 at 4:35 PM

Ok... got it.

In my class I has coded 'Public SOPTYPE As Int32'... but it needed to be 'Public PROPERTY SOPTYPE as Int32'.

Thanks so much for your time.