DotNetNuke 資料訪問層

資料訪問層（DAL）
    資料訪問層（DAL）必須實現資料提供者抽象類中宣告的方法。然而，每一個DAL提供者在實現這些方法時也許很不相同。這種處理允許提供者靈活的選擇他們自己的資料庫訪問協議（也就是說：.NET管理的OleDB, ODBC等）。同樣也允許提供者處理資料庫平臺之間的所有不同之處（例如：儲存過程，sql語言語法，@@IDENTITY）。

每一個資料提供者必須為其在web.config中的自定義屬性指定一個實現方法。

Imports System

Imports System.Data

Imports System.Data.SqlClient

Imports Microsoft.ApplicationBlocks.Data

Imports System.IO

Imports System.Web

Imports DotNetNuke

 

Namespace DotNetNuke.Data

 

    Public Class SqlDataProvider

 

        Inherits DataProvider

 

        Private Const ProviderType As String = "data"

 

        Private _providerConfiguration As ProviderConfiguration = ProviderConfiguration.GetProviderConfiguration(ProviderType)

        Private _connectionString As String

        Private _providerPath As String

        Private _objectQualifier As String

        Private _databaseOwner As String

 

        Public Sub New()

 

            ' Read the configuration specific information for this provider

            Dim objProvider As Provider = CType(_providerConfiguration.Providers(_providerConfiguration.DefaultProvider), Provider)

 

            ' Read the attributes for this provider

            _connectionString = objProvider.Attributes("connectionString")

 

            _providerPath = objProvider.Attributes("providerPath")

 

            _objectQualifier = objProvider.Attributes("objectQualifier")

            If _objectQualifier <> "" And _objectQualifier.EndsWith("_") = False Then

                _objectQualifier += "_"

            End If

 

            _databaseOwner = objProvider.Attributes("databaseOwner")

            If _databaseOwner <> "" And _databaseOwner.EndsWith(".") = False Then

                _databaseOwner += "."

            End If

 

        End Sub

 

        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

 

資料訪問方法必須涉及成簡單的查詢（例如 單一的select，insert，update，delete），以便於在所有的資料庫平臺上他們都能被實現。業務邏輯（例如條件分支，計算或區域性變數）應該在業務邏輯層實現，這樣才能從資料庫抽象出來並集中到一個應用程式（模組）裡處理。如果你經常用那些使你可以在資料庫層實現程式邏輯的富sql語言變數工作的話，這種資料庫訪問是相當簡單的。

DNN中的Sql server/msde 資料提供者用了儲存過程作為最好的資料訪問技術。

 

        ' links module

        Public Overrides Function GetLinks(ByVal ModuleId As Integer) As IDataReader

            Return CType(SqlHelper.ExecuteReader(ConnectionString, DatabaseOwner & ObjectQualifier & "GetLinks", ModuleId), IDataReader)

        End Function

        Public Overrides Function GetLink(ByVal ItemId As Integer, ByVal ModuleId As Integer) As IDataReader

            Return CType(SqlHelper.ExecuteReader(ConnectionString, DatabaseOwner & ObjectQualifier & "GetLink", ItemId, ModuleId), IDataReader)

        End Function

        Public Overrides Sub DeleteLink(ByVal ItemId As Integer)

            SqlHelper.ExecuteNonQuery(ConnectionString, DatabaseOwner & ObjectQualifier & "DeleteLink", ItemId)

        End Sub

        Public Overrides Sub AddLink(ByVal ModuleId As Integer, ByVal UserName As String, ByVal Title As String, ByVal Url As String, ByVal MobileUrl As String, ByVal ViewOrder As String, ByVal Description As String, ByVal NewWindow As Boolean)

            SqlHelper.ExecuteNonQuery(ConnectionString, DatabaseOwner & ObjectQualifier & "AddLink", ModuleId, UserName, Title, Url, MobileUrl, IIf(ViewOrder <> "", ViewOrder, DBNull.Value), Description, NewWindow)

        End Sub

        Public Overrides Sub UpdateLink(ByVal ItemId As Integer, ByVal UserName As String, ByVal Title As String, ByVal Url As String, ByVal MobileUrl As String, ByVal ViewOrder As String, ByVal Description As String, ByVal NewWindow As Boolean)

            SqlHelper.ExecuteNonQuery(ConnectionString, DatabaseOwner & ObjectQualifier & "UpdateLink", ItemId, UserName, Title, Url, MobileUrl, IIf(ViewOrder <> "", ViewOrder, DBNull.Value), Description, NewWindow)

        End Sub

 

       DNN用到儲存過程（儲存過程查詢）但是沒有用引數自動查詢的特性( CommandBuilder.DeriveParameters )，因此引數必須明確的定義。

 

        ' links module

        Public Overrides Function GetLinks(ByVal ModuleId As Integer) As IDataReader

            Return CType(OleDBHelper.ExecuteReader(ConnectionString, CommandType.StoredProcedure, ObjectQualifier & "GetLinks", _

                New OleDbParameter("@ModuleId", ModuleId)), IDataReader)

        End Function

        Public Overrides Function GetLink(ByVal ItemId As Integer, ByVal ModuleId As Integer) As IDataReader

            Return CType(OleDBHelper.ExecuteReader(ConnectionString, CommandType.StoredProcedure, ObjectQualifier & "GetLink", _

                New OleDbParameter("@ItemId", ItemId), _

                New OleDbParameter("@ModuleId", ModuleId)), IDataReader)

        End Function

        Public Overrides Sub DeleteLink(ByVal ItemId As Integer)

            OleDBHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, ObjectQualifier & "DeleteLink", _

                New OleDbParameter("@ItemId", ItemId))

        End Sub

        Public Overrides Sub AddLink(ByVal ModuleId As Integer, ByVal UserName As String, ByVal Title As String, ByVal Url As String, ByVal MobileUrl As String, ByVal ViewOrder As String, ByVal Description As String, ByVal NewWindow As Boolean)

            OleDBHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, ObjectQualifier & "AddLink", _

                New OleDbParameter("@ModuleId", ModuleId), _

                New OleDbParameter("@UserName", UserName), _

                New OleDbParameter("@Title", Title), _

                New OleDbParameter("@Url", Url), _

                New OleDbParameter("@MobileUrl", MobileUrl), _

                New OleDbParameter("@ViewOrder", IIf(ViewOrder <> "", ViewOrder, DBNull.Value)), _

                New OleDbParameter("@Description", Description), _

                New OleDbParameter("@NewWindow", NewWindow))

        End Sub

        Public Overrides Sub UpdateLink(ByVal ItemId As Integer, ByVal UserName As String, ByVal Title As String, ByVal Url As String, ByVal MobileUrl As String, ByVal ViewOrder As String, ByVal Description As String, ByVal NewWindow As Boolean)

            OleDBHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, ObjectQualifier & "UpdateLink", _

                New OleDbParameter("@ItemId", ItemId), _

                New OleDbParameter("@UserName", UserName), _

                New OleDbParameter("@Title", Title), _

                New OleDbParameter("@Url", Url), _

                New OleDbParameter("@MobileUrl", MobileUrl), _

                New OleDbParameter("@ViewOrder", IIf(ViewOrder <> "", ViewOrder, DBNull.Value)), _

                New OleDbParameter("@Description", Description), _

                New OleDbParameter("@NewWindow", NewWindow))

        End Sub

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12639172/viewspace-414920/，如需轉載，請註明出處，否則將追究法律責任。