ASP.NET

I really liked the way that the Subsonic scaffolding worked when I started testing it, but it had several drawbacks that made it unworkable - it insisted on scaffolding the entire database (I work on databases with 25000 objects) and that just took too long. And, I could not get it to bind to so types of grids.

 So, I copied everything good about it into my own data access classes. This is based on the Microsoft Enterprise Framework 3.1

You need two classes:

Imports Microsoft.VisualBasic
Imports System.Data


Public Class commandParameter
    Public ParamName As String
    Public ParamValue As Object
    Public ParamType As DbType


    Sub New(ByVal paramName As String, ByVal paramValue As Object, ByVal paramType As DbType)
        Me.ParamName = paramName
        Me.ParamValue = paramValue
        Me.ParamType = paramType
    End Sub


End Class

Imports Microsoft.VisualBasic
Imports System.Data.SqlClient
Imports System.Data
Imports Microsoft.Practices.ObjectBuilder
Imports Microsoft.Practices.EnterpriseLibrary.Data
Imports Microsoft.Practices.EnterpriseLibrary.Common


Public Class storedProcedure
    Dim _storedProcName As String
    Dim _db As String
    Public commandParameters As System.Collections.Generic.List(Of commandParameter)


    Sub New(ByVal StoredProcName As String, ByVal DB As String)
        _storedProcName = StoredProcName
        _db = DB
        commandParameters = New System.Collections.Generic.List(Of commandParameter)
    End Sub


    Sub execute()


        Dim db As Database = DatabaseFactory.CreateDatabase(_db)
        Dim dbCommand As Common.DbCommand = db.GetStoredProcCommand(_storedProcName)


        For Each cp In commandParameters
            db.AddInParameter(dbCommand, cp.ParamName, cp.ParamType, cp.ParamValue)
        Next
        db.ExecuteNonQuery(dbCommand)


    End Sub
    Function getReader() As SqlDataReader
        Dim db As Database = DatabaseFactory.CreateDatabase(_db)
        Dim dbCommand As Common.DbCommand = db.GetStoredProcCommand(_storedProcName)


        For Each cp In commandParameters
            db.AddInParameter(dbCommand, cp.ParamName, cp.ParamType, cp.ParamValue)
        Next
        Return db.ExecuteReader(dbCommand)



    End Function
    Function getTable() As DataTable
        Dim db As Database = DatabaseFactory.CreateDatabase(_db)
        Dim dbCommand As Common.DbCommand = db.GetStoredProcCommand(_storedProcName)


        For Each cp In commandParameters
            db.AddInParameter(dbCommand, cp.ParamName, cp.ParamType, cp.ParamValue)
        Next
        Return db.ExecuteDataSet(dbCommand).Tables(0)



    End Function
End Class

 And you call it like this:

oDR = DLL.FP_blog_SEL_byBlogID(BlogId, "mydbname").getReader 

DLL class:

Public Class DLL


Public Shared Function FP_blog_SEL_byBlogID(ByVal BlogId As Int64, ByVal DB As String) As storedProcedure
        Dim sp As New storedProcedure("FP_blog_SEL_byBlogID", DB)
        sp.commandParameters.Add(New commandParameter("@blogID", BlogId, DbType.Int64))
        Return sp
End Function


End Class