Jun 2

Written by: Steve Gray
6/2/2010 10:46 AM 

This is new functionality in the Enterprise Library 5.0 Data Access Application Block, the ability to create a class (typically this mirrors a table), and then write a stored procedure and send the results of the stored proc directly to the class… no looping or enumerating.

This is a complete code example, if you’ll copy all this into your environment it’ll run.

First the stored proc:

IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_SCHEMA = N'dbo'
     AND SPECIFIC_NAME = N'FP_SOP10100_SEL_byID' 
)
   DROP PROCEDURE dbo.FP_SOP10100_SEL_byID
GO
CREATE PROCEDURE dbo.FP_SOP10100_SEL_byID      
      
@sopnumbe varchar(31),      
@soptype smallint      
      
AS      
select convert(int,2) as SOPTYPE 
GO
GRANT exec on FP_SOP10100_SEL_byID to public
  

 

Note that no real data access is involved, the proc returns a constant. Now the VB code:

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
            MsgBox(s.SOPTYPE)
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        Me.Close()
    End Sub
End Class
Public Class SOP10100
    Public Property SOPTYPE As Int32 = 1
End Class

 

The example above uses a ‘row mapper’, not strictly needed because our stored procedure has one column (SOPTYPE) and the class has one property (SOPTYPE). The example below is the same thing, without the mapper

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 s = db.ExecuteSprocAccessor(Of SOP10100)("dbo.FP_SOP10100_SEL_byID", "ORDST2225", 2).SingleOrDefault
            MsgBox(s.SOPTYPE)
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
End Class
Public Class SOP10100
    Public Property SOPTYPE As Int32 = 1
End Class