I also like a more direct route to do data access. Below is an example of how I do this. You will see three classes, a new DB Controller Class (replaces DNN's), a Controller Class, and a Info Class. You would just reference and use the object and controller like any other class. Also the SQL scripting is the same as any DNN SQLProvider scripts...
DBControllerBase
Imports System.Configuration
Imports DotNetNuke.Entities.Modules
Imports DotNetNuke.Framework.Providers
Namespace CompanyName.DNN.Modules.SqlDataProvider.ControllerBase
Public MustInherit Class DBControllerBase
#Region "Private variables"
Private Const PROVIDER_TYPE As String = "data"
Private _connectionString As String
Private _databaseOwner As String
Private _objectQualifier As String
Private _prefix As String
Private _providerConfiguration As ProviderConfiguration = ProviderConfiguration.GetProviderConfiguration(PROVIDER_TYPE)
Private _providerPath As String
Public Sub New()
initDBInfo()
End Sub
#End Region
#Region "Private methods"
Private Sub initDBInfo()
Dim provider As Provider = DirectCast(_providerConfiguration.Providers(_providerConfiguration.DefaultProvider), Provider)
_connectionString = ConfigurationManager.AppSettings("SiteSqlServer").ToString()
_providerPath = provider.Attributes("providerPath")
_objectQualifier = provider.Attributes("objectQualifier")
If (_objectQualifier <> "") AndAlso (Not _objectQualifier.EndsWith("_")) Then
_objectQualifier += "_"
End If
_databaseOwner = provider.Attributes("databaseOwner")
If (_databaseOwner <> "") AndAlso (Not _databaseOwner.EndsWith(".")) Then
_databaseOwner += "."
End If
_prefix = DatabaseOwner + ObjectQualifier
End Sub
#End Region
#Region "Public properties"
Public ReadOnly Property ConnectionString() As String
Get
Return _connectionString
End Get
End Property
Public ReadOnly Property ProviderPath() As String
Get
Return _providerPath
End Get
End Property
Public ReadOnly Property ObjectQualifier() As String
Get
Return _objectQualifier
End Get
End Property
Public ReadOnly Property DatabaseOwner() As String
Get
Return _databaseOwner
End Get
End Property
Public ReadOnly Property Prefix() As String
Get
Return _prefix
End Get
End Property
#End Region
End Class
End Namespace
Controller Class (consumes DBController class above)
Imports Microsoft.ApplicationBlocks.Data
Imports CompanyName.DNN.Modules.SqlDataProvider.ControllerBase
Imports DotNetNuke.Common.Utilities
Public Class UserController
Inherits DBControllerBase
Public Function GetAllUserInfoForPortalAsTataTable(ByVal PortalId As Integer) As DataTable
Return SqlHelper.ExecuteDataset(ConnectionString, Prefix & "CompanyName_GetAllUserInfoForSinglePortal", PortalId).Tables(0)
End Function
Public Function GetAllUserInfoForPortalAsArrayList(ByVal PortalId As Integer) As ArrayList
Return CBO.FillCollection(SqlHelper.ExecuteReader(ConnectionString, Prefix & "CompanyName_GetAllUserInfoForSinglePortal", PortalId), GetType(UserInfo))
End Function
Public Function GetAllUserInfoForPortalAsList(ByVal PortalId As Integer) As List(Of UserInfo)
Return CBO.FillCollection(Of UserInfo)(SqlHelper.ExecuteReader(ConnectionString, Prefix & "CompanyName_GetAllUserInfoForSinglePortal", PortalId))
End Function
Public Function GetUserInfoForSingleIP(ByVal IPRegEx As String) As UserInfo
Return CBO.FillObject(Of UserInfo)(SqlHelper.ExecuteReader(ConnectionString, Prefix & "CompanyName_GetUserInfoForSingleIP", IPRegEx))
End Function
Public Sub AddUserInfo(ByVal ipui As UserInfo)
SqlHelper.ExecuteNonQuery(ConnectionString, Prefix & "CompanyName_AddUserInfo", ipui.PortalId, ipui.IPRegEx, ipui.Username)
End Sub
Public Sub DeleteUserInfo(ByVal ipui As UserInfo)
SqlHelper.ExecuteNonQuery(ConnectionString, Prefix & "CompanyName_DeleteUserInfo", ipui.PortalId, ipui.IPRegEx, ipui.Username)
End Sub
End Class
Info Class
Public Class UserInfo
Private m_PortalId As Integer
Public Property PortalId() As Integer
Get
Return m_PortalId
End Get
Set(ByVal value As Integer)
m_PortalId = value
End Set
End PropertyS
Private m_Username As String
Public Property Username() As String
Get
Return m_Username
End Get
Set(ByVal value As String)
m_Username = value
End Set
End Property
End Class
Sample Script
CREATE TABLE {databaseOwner}{objectQualifier}CompanyName_IP_Login(
[PortalId] [int] NOT NULL,
[Username] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_CompanyName_IP_Login] PRIMARY KEY CLUSTERED
(
[PortalId] ASC,
[IPRegEx] ASC,
[Username] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}CompanyName_GetAllUserInfoForSinglePortal
@PortalId int
AS
SELECT
PortalId, Username
FROM
{databaseOwner}{objectQualifier}CompanyName_IP_Login
WHERE
PortalId = @PortalId
GO
Etc, etc, etc
I hope this helps. If you would like a copy of a functioning solution (or a C# version) please feel free to email me: robertc@dnnguru.com