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