神马文学网DNN研究之数据接口白皮书--似水年华2046
来源：百度文库 编辑：神马文学网 时间：2024/04/29 18:52:55
DNN研究之数据接口白皮书(1)- -
The ultimate goal of DotNetNuke is to create a portal framework which provides a solid foundation of building block services that developers can leverage to create a robust application. One of the key functions in any application is data access. The .NET Framework provides a variety of methods for performing data access and from an architecture perspective it feels a bit like navigating a complex maze when it comes to selecting the optimal solution to meet your needs. This whitepaper will attempt to reduce the complexity and provide justification for the data access strategy implemented in the DotNetNuke application.
介绍
DotNetNuke的 最终目标是创造一个门户框架，为开发者开发健壮的应用程序，提供一个坚实的基础。在任何应用程序中数据接口都是关键部分。。.net框架提供了多种数据接口方式，当你从结构上考虑为满足需求，选择最佳解决方案的时候，也许会感到一些困惑。在这里我们尝试减少复杂性，并为DNN选择最好的数据接口策略。
Although a variety of literature exists on the various data access methods in the .NET Framework, the majority of it is too high level to actually apply in real world situations. The various methods are usually discussed in terms of their specific strengths and weaknesses but at the end of the day, the developer is still left with lingering questions as to the best overall choice for their application. The ultimate dilemma is centered around the fact that each data access method is best suited to different application use cases. In theory this is great; however in practice, each developer is looking for a consistent data access strategy to apply across the entire enterprise.
策略
虽然在.net框架中已经存在多种数据接口方式的说明文档,但大多数对于实际应用来说层次是有些高了.我们通常在过去讨论这些接口方式的优缺点,但最终开发者仍旧会在他们的实际方案选择的接口方式留下问题.最终的困境是由于.net的每种数据接口方式都适合不同的应用程序用例.在理论上来说这是非常棒的,而实际上,每个开发者都在寻找通用的数据接口策略,来应用到自己的所有项目.
A consistent data access strategy has some key benefits to the organization. The fact that the data access pattern is consistently defined results in higher productivity as the developer is not required to waste time selecting a data access method for each task. This results in improved code maintainability as the pattern is implemented consistently in all application areas. The risk of poor data access choices are minimized and the integrity of the code is increased through the centralization of data access components.
通用的数据借口策略对团队来说有几个关键的好处.事实上当开发者不需要为每个工作都去选择数据接口模式的时候，极大的提高了效率。当借口模式被通用定义后，这样就提高了代码的可维护性。单一数据模式使用的风险被最小化了，并通过统一数据接口组件提高了代码的忠实性。
The concept of a consistent data access strategy certainly opposes the notion that each business requirement needs to be matched to an optimal data access method. The philosophy of choosing a specific data access method for each task should theoretically result in the best overall performance for your application ( assuming you made the appropriate selection in all cases ). However, this perceived benefit is far overshadowed by the liability in terms of inconsistent development practices. 通用数据借口策略的概念必然反对每个商业需求都需要最佳的数据接口模式这种观念。从理论上说应该为应用程序选择最佳的方式（假定你选择了最适合的方式）。但是，这样做对今后不同的开发是不利的
Falling back on traditional concepts known as the 80/20 rule, DotNetNuke has focused on providing a consistent data access strategy which achieves the optimal goals in 80 percent of application use cases. In the other 20 percent, it is up to the organization to decide whether the performance requirements necessitate the implementation of a different data access method for the task; while at the same time accepting the consequences outlined above.
退回到传统的80/20原则，DNN的通用数据借口策略已经满足了80%的用户需求。另外的20%，决定于团队是否根据需求来强制执行不同的数据接口方式，并愿意承担上面所提到的后果。
One of the key requirements of DotNetNuke is to provide an implementation of the application that supports multiple data stores.
需求
DNN的 关键需求就是一贯应用程序能满足不同的数据存储。
Due to the fact we require the ultimate in flexibility and performance in terms of communicating with external data stores, we chose to discard the generic data access approach and build the application to take advantage of the database-native feature set ( ie. .NET managed providers, proprietary SQL dialects, stored procedures, etc… ). The tradeoff that we made when choosing to go with a database-specific access class was that we would need to write a separate data access layer for each database platform we wanted to support and hence the application would contain more code. While the data access layers share much common code, each is clearly targeted for use with a specific database.
由于我门需要最终的柔韧性和外部存储数据执行数据交换，我们放弃了通常数据接口，并基于数据库本身特性(ie.,SQL语言，存储过程…)建立程序。我们需要为不同数据平台写自己的类。当数据层共享了通用代码，每个都可以轻易使用特定数据库。
In order to simplify the use of the database access classes we elected to use the Provider Pattern ( also known as the Factory Design Pattern as outlined by the Gang of Four – GOF ), with reflection being used to dynamically load the correct data access objects at runtime. The factory is implemented as follows: an abstract class is created with a method declared for each method that must be exposed by the database access classes. For each database that we want to support, we create a concrete class that implements the database specific code to perform each of the operations in the abstract class or "contract." To support the runtime determination of which concrete class to load, we also include an Instance() method which is the factory itself and relies on our generic Provider class to read a value from configuration file to determine which assembly to load using reflection. Due to the fact that reflection is very expensive in terms of application performance, we store the constructor of the data provider class in the cache.
为了简化数据类我们选择了Provider模式（Factory模式中已经提到），在运行时动态装载数据对象。Factory模式按照下面执行：声明一个抽象类，每个方法针对不同的数据层。对我们想支持的每个数据库，我们建立一个混合类来执行抽象类定义的方法或contact。为了支持运行时复合类装载，我门包含了一个Factory自己的Include()方法，并依赖于我们的 Provider类来读取配置文件决定装载哪个程序集。由于映射对程序性能来说是很重要的，我们缓存了数据provider类。
Why abstract classes instead of interfaces? This is due to the fact that interfaces are immutable ( static ) and as a result do not lend themselves to versioning. Because interfaces do not support implementation inheritance, the pattern that applies to classes does not apply to interfaces. Adding a method to an interface is equivalent to adding an abstract method to a base class; any class that implements the interface will break because the class does not implement the new method.
为什么抽象类代替了接口？这是由于接口是静态的，并不允许自身的改写。由于接口不能支持继承，用于类的模式不能用于接口。对于一个类来说添加接口和添加抽象类是一样的；执行接口的时候类会中断，因为类不能执行新的方法。
The following diagram shows how the business logic, factory, and databases access classes interact with each other. The key advantage of the solution built is that the database access class can be compiled after the business logic classes as long as the data access class implements the DataProvider abstract class methods. This means that should we want to create an implementation for another database, we do not need to change the business logic tier (or UI tier). The steps to create another implementation are:
下面的图显示了商务逻辑，Factoey模式，数据接口之间的关系。建立这样的解决方案的关键优点是只要数据类执行了DataProvider的抽象类的方法，数据接口类可以在上午逻辑层之后编译。这意味着 我们更换数据库的时候不用更换BLL层。
执行步骤如下：
1. 为新数据库建立数据接口类执行Dataprovider的抽象类；
2. 编译类为一个程序集；
3. 测试并部署新的数据程序集在一个 服务器上；
4. 修改配置文件指向新的数据接口类；
5. 在BLL层无需任何改变和重编译。
Create the database access classes for the new database which implement the DataProvider abstract class. Compile the class into an assembly. Test and deploy the new data assembly to a running server. Change the configuration file to point to the new database access class. No changes or recompiles need to be performed on the business logic components.
- 作者：evan2046 2005年06月1日, 星期三 11:34加入博采
DNN研究之数据接口白皮书(2)- -
The web.config file contains a number of critical sections to enable the DataProvider pattern. The first section registers the Providers and their corresponding ConfigurationSectionHandlers. Although in this instance we only have a single section specified for the DotNetNuke group, we could use the exact same method to configure other providers ( ie. abstract authentication providers, etc… ). The only catch is that the section name value must be implemented elsewhere in the web.config file.
配置
web.config文件包含几个关键部分设置DataProvide。首先是注册Provider和 相关的ConfigurationSectionHandlers。我们可以按照这个设置来自己 进行配置。重要的 是section name在其他地方应该是统一的。
sectionGroup>
configSections>
The following section is retained for legacy modules which rely on the old method of data access:
下面的设置保持了旧的数据接口方式：
appSettings>
And finally the meat of the Provider model. The section name within the group ( as described in the configSections above ) should contain a defaultProvider attribute which relates to a specific instance in thecollection below. The defaultProvider is used as the single switch for changing from one provider to another. If a default provider is not specified, the first item in the collection is considered the default.
主要的，包含在里的区应包含一个 默认的 Provider，即下面 的。默认的Proider用来 在不同的Provider之间互相切换。如果没有 指定缺省的Provider，第一个 将被 设为默认的。
The section also includes acollection specification where all of the implementations for are identified. At a bare minimum, each provider must include a name, type, and providerPath attribute ( name is generic but usually refers to the classname, type refers to the strong classname of the provider, and providerPath refers to the location where provider specific resources such as scripts can be found ). Each provider can also have any number of custom attributes as well.
区包含了集合，最少应该包括名称，类型，路径（名称通常用类名，类型指向强类名，路径指定脚本资源访问）
type = "DotNetNuke.Data.SqlDataProvider, DotNetNuke.SqlDataProvider"
connectionString = "Server=localhost;Database=DotNetNuke;uid=sa;pwd=;"
providerPath = "~\Providers\DataProvider\SqlDataProvider\"
objectQualifier = "DotNetNuke"
databaseOwner = "dbo"
/>
type = "DotNetNuke.Data.AccessDataProvider, DotNetNuke.AccessDataProvider"
connectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
providerPath = "~\ Providers\DataProvider\AccessDataProvider\"
objectQualifier = "DotNetNuke"
databaseFilename = "DotNetNuke.resources"
/>
providers>
data>
dotnetnuke>
The following specification rules are in effect for defining nodes within the “providers” collection.
The configuration section contains one or more , , or elements. The following rules apply when processing these elements:
It is not an error to declare an empty element. Providers inherit items from parent configuration statements. It is an error to redefine an item using if the item already exists or is inherited. It is an error to a non-existing item. It is not an error to , , and then the same item again. It is not an error to , >, and then the same item again. removes all inherited items and items previously defined, e.g. an declared before a is removed while an declared after a is not removed.
下面的 规则说明了provider节点的 设置。
配置区包括了一个或多个元素。下面是设置规则：
1. 对于空元素没有错误声明；
2. Providers继承父级配置中的声明；
3. 如果已经存在或者已经继承了将会有 错误发生；
4. 用于一个 不存在的元素将会出错；
5. 对于元素然后又不会出错；
6. 对于元素后又不会出错；
7. 移除了所以以前声明和继承的元素，例如在之前声明的将被移除，而在之后则不受影响。
Description
Adds a data provider.
Attributes
name – Friendly name of the provider.
type – A class that implements the required provider interface. The value is a fully qualified reference to an assembly.
providerPath - the location where provider specific resources such as scripts can be found
Other name/value pairs – Additional name value pairs may be present. All name/value pairs are the responsibility of the provider to understand.
Description
Removes a named data provider.
Attributes
name – Friendly name of the provider to remove.
Description
Removes all inherited providers.
Provider.vb类提供了所有从web.config载入Provider，并且执行的设置。这是一个 通用类，不止包含数据接入。
The Provider.vb class provides all of the implementation details for loading the provider information from the web.config file and applying the , , processing rules. It is a generic class which is not only applicable to data access.
Dataprovider.Vb是一个 抽象类，包含了DotNetNuke所有的数据接口方法。它包含了一个factory自身的Instance（）方法，根据web.config设置来装载相应的程序集。
The DataProvider.vb is the abstract class containing all data access methods for DotNetNuke. It contains an Instance() method which is the factory itself and loads the appropriate assembly at runtime based on the web.config specification.
' provider constants - eliminates need for Reflection later
Private Const [ProviderType] As String = "data" ' maps to in web.config
' create a variable to store the reference to the instantiated object
Private Shared objProvider As DataProvider
Public Shared Function Instance() As DataProvider
' does the provider reference already exist?
If objProvider Is Nothing Then
Dim strCacheKey As String = [ProviderType] & "provider"
' use the cache because the reflection used later is expensive
Dim objType As Type = CType(DataCache.GetCache(strCacheKey), Type)
If objType Is Nothing Then
' Get the provider configuration based on the type
Dim objProviderConfiguration As ProviderConfiguration = ProviderConfiguration.GetProviderConfiguration([ProviderType])
' The assembly should be in \bin or GAC, so we simply need to get an instance of the type
Try
' Get the typename of the Core DataProvider from web.config
Dim strTypeName As String = CType(objProviderConfiguration.Providers(objProviderConfiguration.DefaultProvider), Provider).Type
' use reflection to get the type of the class that implements the provider
objType = Type.GetType(strTypeName, True)
' insert the type into the cache
DataCache.SetCache(strCacheKey, objType)
Catch e As Exception
' Could not load the provider - this is likely due to binary compatibility issues
End Try
End If
' save the reference
objProvider = CType(Activator.CreateInstance(objType), DataProvider)
End If
Return objProvider
End Function
All data access methods are defined as MustOverride which means that any data provider derived from this class must provide implementations for these methods. This defines the abstract class contract between the Business Logic Layer and the Data Access Layer.
所有数据接口方法都是被必须覆写定义的，即从这个类衍生的任何数据provider都会执行那些方法。这就 定义了抽象类来联系BLL和DAL。
' links module
Public MustOverride Function GetLinks(ByVal ModuleId As Integer) As IDataReader
Public MustOverride Function GetLink(ByVal ItemID As Integer, ByVal ModuleId As Integer) As IDataReader
Public MustOverride Sub DeleteLink(ByVal ItemID As Integer)
Public MustOverride 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)
Public MustOverride 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)
The Data Access Layer must implement the methods contained in the DataProvider abstract class. However, each DAL provider may be very different in its actual implementation of these methods. This approach allows the provider the flexibility to choose its own database access protocol ( ie. managed .NET, OleDB, ODBC, etc… ). It also allows the provider to deal with proprietary differences between database platforms ( ie. stored procedures, SQL language syntax, @@IDENTITY ).
Each data provider must specify an implementation for its custom attributes defined in the web.config file.
数据接口层DAL
DAL必须定义了DataProvider定义的抽象类的所有方法。然而，每个DALprovider在实现那些方法上是完全不同的(如.net,OleDB,ODBC..等等)。这就定义了provider使用自己数据接口的灵活性。这样也 允许了不同数据库平台应用自己的特性（如存储过程，SQL语句...等等）。
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
Data access methods must be designed as simple queries ( ie. single SELECT, INSERT, UPDATE, DELETE ) so that they can be implemented on all database platforms. Business logic such as conditional branches, calculations, or local variables should be implemented in the Business Logic Layer so that it is abstracted from the database and centralized within the application. This simplistic approach to data access may take some getting used to if you frequently work with rich SQL languages variants that allow you to implement programming logic at the database level ( ie. Transact-SQL stored procedures which perform either an INSERT or UPDATE based on the specification of an identifier ).
The SQL Server / MSDE DataProvider included with DotNetNuke uses Stored Procedures as a best practice data access technique.
数据接口方法必须被设计为单一查询（如单一SELECT,INSERT,UPDATE,DELETE）,用来适用于所有的数据库平台。一些商务逻辑例如条件语句，计算，局部变量应该都包含在BLL内，以便从数据库和程序集中抽象出来。如果你经常使用不同的SQL语言，这样简化的方式允许你 在数据库层编程。
' 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 Function 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) As Integer
Return CType(SqlHelper.ExecuteScalar(ConnectionString, DatabaseOwner & ObjectQualifier & "AddLink", ModuleId, UserName, Title, Url, MobileUrl, GetNull(ViewOrder), Description, NewWindow), Integer)
End Function
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, GetNull(ViewOrder), Description, NewWindow)
End Sub
The Microsoft Access data provider included with DotNetNuke also uses stored procedures ( stored queries ) but does not have the Auto Parameter Discovery feature ( CommandBuilder.DeriveParameters ) therefore the parameters must be explicitly declared. Also notice that Access does not support @@IDENTITY to return the unique auto number generated; therefore, it must be retrieved in a subsequent database call ( GetLinkIdentity ).
DNN包含的Access数据库provide也使用存储过程，但没有自动参数返回功能。因此参数必须被显式声明。要 注意Access不 支持@@IDENTIFY这样来返回生成的唯一自动数值，因此，必须要在下次数据库调用中得到。
' 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 Function 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) As Integer
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", GetNull(ViewOrder)), _
New OleDbParameter("@Description", Description), _
New OleDbParameter("@NewWindow", NewWindow))
Return CType(OleDBHelper.ExecuteScalar(ConnectionString, CommandType.StoredProcedure, ObjectQualifier & "GetLinkIdentity", _
New OleDbParameter("@ModuleId", ModuleId), _
New OleDbParameter("@Title", Title)), Integer)
End Function
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", GetNull(ViewOrder)), _
New OleDbParameter("@Description", Description), _
New OleDbParameter("@NewWindow", NewWindow))
End Sub
DotNetNuke contains an Auto Upgrade feature which allows the application to upgrade the database automatically when a new application version is deployed. Scripts must be named according to version number and dataprovider ( ie. 02.00.00.SqlDataProvider ) and must be located in the directory specified in the providerPath attribute in the web.config file. Dynamic substitution can be implemented in scripts by overriding the ExecuteScript method in the provider implementation. This can be useful for object naming and security specifications.
数据库脚本
DNN包含了自动升级的功能，当程序发布新版本的时候允许程序自动升级数据库。脚本必须按照版本号和DataProvider来命名（例如02.00.00.SqlDataProvider）,并且必须位于web.config中providerPath属性指定的路径。在provider执行的时候会重载执行脚本，动态的更新数据库。
create procedure {databaseOwner}{objectQualifier}GetLinks
@ModuleId int
as
select ItemId,
CreatedByUser,
CreatedDate,
Title,
Url,
ViewOrder,
Description,
NewWindow
from {objectQualifier}Links
where ModuleId = @ModuleId
order by ViewOrder, Title
GO
Since many databases do not have a rich scripting language, each provider also has the ability to implement the UpgradeDatabaseSchema method which can be used to programmatically alter the database structure.
由于许多数据库没有富脚本语言，每个provider也可以执行UpgradeDatabaseSchema方法，由程序修改数据库结构。
Public Overrides Sub UpgradeDatabaseSchema(ByVal Major As Integer, ByVal Minor As Integer, ByVal Build As Integer)
' add your database schema upgrade logic related to a specific version. This is used for data stores which do not have a rich scripting language.
Dim strVersion As String = Major.ToString & "." & Minor.ToString & "." & Build.ToString
Select Case strVersion
Case "02.00.00"
End Select
End Sub
SQL Language Syntax
SQL Server and MSDE have a rich scripting language called Transact-SQL which supports local variables, conditional branches ( if …. then … else ), and looping. Script can be nicely formatted using tabs and spaces for improved readability.
SQL语言 语句
SQL Server和 MSDE有T-Sql脚本语言，支持局部变量，条件语句，循环。脚本语言通过缩进和空格控制格式提高易读性。
drop procedure {databaseOwner}{objectQualifier}GetPortalTabModules
go
create procedure {databaseOwner}{objectQualifier}GetPortalTabModules
@PortalId int,
@TabId int
as
select {objectQualifier}Modules.*,
{objectQualifier}Tabs.AuthorizedRoles,
{objectQualifier}ModuleControls.ControlSrc,
{objectQualifier}ModuleControls.ControlType,
{objectQualifier}ModuleControls.ControlTitle,
{objectQualifier}DesktopModules.*
from {objectQualifier}Modules
inner join {objectQualifier}Tabs on {objectQualifier}Modules.TabId = {objectQualifier}Tabs.TabId
inner join {objectQualifier}ModuleDefinitions on {objectQualifier}Modules.ModuleDefId = {objectQualifier}ModuleDefinitions.ModuleDefId
inner join {objectQualifier}ModuleControls on {objectQualifier}ModuleDefinitions.ModuleDefId = {objectQualifier}ModuleControls.ModuleDefId
inner join {objectQualifier}DesktopModules on {objectQualifier}ModuleDefinitions.DesktopModuleId = {objectQualifier}DesktopModules.DesktopModuleId
where ({objectQualifier}Modules.TabId = @TabId or ({objectQualifier}Modules.AllTabs = 1 and {objectQualifier}Tabs.PortalId = @PortalId))
and ControlKey is null
order by ModuleOrder
GO
MS Access has its own proprietary SQL language called JET SQL. In contrast to Transact-SQL, JET SQL is an extremely limited scripting language as it has no support for local variables, conditional branches, or looping. This limits the queries to very simple CRUD operations. A few syntactical items to note are JET does not accept script formatting using tabs, stored queries need () around the parameter list and parameter names must be wrapped in [@], NULL must be handled using the isnull() function, the word “outer” is dropped from the join clause, the Now() function returns the current date, bit fields use boolean True and False specification, and the query must be completed with a semi-colon (“;”).
Accesss数据库有自己的 脚本语言称为JET SQL。和T-SQL相比，JET SQL是非常有限的脚本语言，不 支持变量，条件语句，循环。这就 限制了查询只能是CRUD操作。需要 注意JET不 支持使用缩减控制格式，存储查询需要在参数加（），参数名称必须包括[@],控制必须被isnull函数处理，“outer“从联合语句取消了，Now（）返回现在日期，位值使用布儿的TRUE和False定义，并且查询必须由(“;”)结束。
drop procedure {objectQualifier}GetPortalTabModules
go
create procedure {objectQualifier}GetPortalTabModules ( [@PortalId] int, [@TabId] int )
as
select {objectQualifier}Modules.*,
{objectQualifier}Tabs.AuthorizedRoles,
{objectQualifier}ModuleControls.ControlSrc,
{objectQualifier}ModuleControls.ControlType,
{objectQualifier}ModuleControls.ControlTitle,
{objectQualifier}DesktopModules.*
from {objectQualifier}Modules, {objectQualifier}Tabs, {objectQualifier}DesktopModules, {objectQualifier}ModuleDefinitions, {objectQualifier}ModuleControls
where {objectQualifier}Modules.TabId = {objectQualifier}Tabs.TabId
and {objectQualifier}Modules.ModuleDefId = {objectQualifier}ModuleDefinitions.ModuleDefId
and {objectQualifier}ModuleDefinitions.ModuleDefId = {objectQualifier}ModuleControls.ModuleDefId
and {objectQualifier}DesktopModules.DesktopModuleId = {objectQualifier}ModuleDefinitions.DesktopModuleId
and ({objectQualifier}Modules.TabId = [@TabId] or ({objectQualifier}Modules.AllTabs = True and {objectQualifier}Tabs.PortalId = [@PortalId]))
and isnull(ControlKey) = True
order by ModuleOrder;
GO
Another item to note is that MS Access is a file-based database and as a result, requires write access at the file system level. In addition, the *.mdb file extension is not secured by default; therefore, you need to take precautions to ensure your data can not be compromised. As a result, the default web.config file specifies the database filename as DotNetNuke.resources. The *.resources file extension is protected by ASP.NET using the HttpForbiddenHandler in the machine.config and prevents unauthorized download. The Template.resources file distributed with the AccessDataProvider is a template database which contains a baseline schema and data for creating new Access databases.
另外需要注意的 是Access是基于文件的 数据库，因此需要文件写属性的要求。特别的,缺省*.mdb扩展名的 文件是 不 安全的。因此需要采取措施，防止数据流失。*.resource扩展名的 文件是被.Net保护的，建议修改。
- 作者：evan2046 2005年06月1日, 星期三 14:36加入博采
DNN研究之数据接口白皮书(4) - -
Database Object Naming
The web.config file includes an attribute named objectQualifer which allows you to specify a prefix for your database obects ( ie. DNN_ ). Web hosting plans usually offer only one SQL Server database that you have to share among all web applications in your account. If you do not specify an object prefix you may have a naming conflict with an application that already uses a specific object name ( ie. Users table ). Another benefit of prefixing object names is they will be displayed grouped together in tools such as Enterprise Manager for SQL Server when listed alphabetically which simplifies management.
If you are upgrading a pre-2.0 DotNetNuke database, you will want to set the objectQualifier to “”. This is because you may have third party modules which do not use the new DAL architecture and are dependent on specific object names. Setting the objectQualifier on an upgrade will rename all of your core database objects which may result in errors in your custom modules.
数据库对象命名
web.config文件包含了一个objectQualifer属性允许你 指定数据库对象前缀（例如DNN）.web主机通常只提供一个SQL Server数据库，不得不用你的账号的所有web程序 中共享。如果不指定对象前缀很容易混淆
The Microsoft Data Access Application Block ( MSDAAB ) is a .NET component that contains optimized data access code that helps you call stored procedures and issue SQL text commands against a SQL Server database. We use it as a building block in DotNetNuke to reduce the amount of custom code needed to create, test, and maintain data access functions in the application. We also created an OleDB.ApplicationBlocks.Data assembly for the Microsoft Access data provider based on the MSDAAB code.
In terms of implementation we chose to use the MSDAAB as a black box component rather than include the actual source code in our DAL implementation. This decision helps prevent modification of the MSDAAB code which enables us to upgrade the component seamlessly as new features/fixes become available.
程序模块
MSDAAB是.net组件，包含了优化过的数据接口代码帮助你 调用存储过程和sql命令。我们把他作为内置模块是减少用户写代码的工作。不提供原码是为了以后升级方便。
DotNetNuke uses the DataReader for passing collections of data from the Data Access Layer ( DAL ) to the Business Logic layer ( BLL ) layer. The DataReader was chosen because it is the fastest performing data transport mechanism available in ADO.NET ( a forward only, read only, stream of data ). The IDataReader is the base interface for all .NET compatible DataReaders. The abstract IDataReader allows us to pass data between layers without worrying about the data access protocol being used in the actual data provider implementation ( ie. SqlClient, OleDB, ODBC, etc… ).
DNN使用dr从DAL到BLL传输数据集合。Dr的性能在ADO.NET中是最快的。IDataReader是所有dr的基本接口。抽象的dr使你传送数据而不用担心实际应用中的数据协议。
Good object oriented design recommends the abstraction of the data store from the rest of the application. Abstraction allows the application to build upon an independent set of logical interfaces; thereby reducing the dependency on the physical implementation of the underlying database.
好的对象定向设计需要从程序中抽象出数据存储。抽象使程序基于相对独立的逻辑接口，因此减少对物理执行的基础数据库的依赖。
- 作者：evan2046 2005年06月1日, 星期三 14:43加入博采
The ultimate goal of DotNetNuke is to create a portal framework which provides a solid foundation of building block services that developers can leverage to create a robust application. One of the key functions in any application is data access. The .NET Framework provides a variety of methods for performing data access and from an architecture perspective it feels a bit like navigating a complex maze when it comes to selecting the optimal solution to meet your needs. This whitepaper will attempt to reduce the complexity and provide justification for the data access strategy implemented in the DotNetNuke application.
介绍
DotNetNuke的 最终目标是创造一个门户框架，为开发者开发健壮的应用程序，提供一个坚实的基础。在任何应用程序中数据接口都是关键部分。。.net框架提供了多种数据接口方式，当你从结构上考虑为满足需求，选择最佳解决方案的时候，也许会感到一些困惑。在这里我们尝试减少复杂性，并为DNN选择最好的数据接口策略。
Although a variety of literature exists on the various data access methods in the .NET Framework, the majority of it is too high level to actually apply in real world situations. The various methods are usually discussed in terms of their specific strengths and weaknesses but at the end of the day, the developer is still left with lingering questions as to the best overall choice for their application. The ultimate dilemma is centered around the fact that each data access method is best suited to different application use cases. In theory this is great; however in practice, each developer is looking for a consistent data access strategy to apply across the entire enterprise.
策略
虽然在.net框架中已经存在多种数据接口方式的说明文档,但大多数对于实际应用来说层次是有些高了.我们通常在过去讨论这些接口方式的优缺点,但最终开发者仍旧会在他们的实际方案选择的接口方式留下问题.最终的困境是由于.net的每种数据接口方式都适合不同的应用程序用例.在理论上来说这是非常棒的,而实际上,每个开发者都在寻找通用的数据接口策略,来应用到自己的所有项目.
A consistent data access strategy has some key benefits to the organization. The fact that the data access pattern is consistently defined results in higher productivity as the developer is not required to waste time selecting a data access method for each task. This results in improved code maintainability as the pattern is implemented consistently in all application areas. The risk of poor data access choices are minimized and the integrity of the code is increased through the centralization of data access components.
通用的数据借口策略对团队来说有几个关键的好处.事实上当开发者不需要为每个工作都去选择数据接口模式的时候，极大的提高了效率。当借口模式被通用定义后，这样就提高了代码的可维护性。单一数据模式使用的风险被最小化了，并通过统一数据接口组件提高了代码的忠实性。
The concept of a consistent data access strategy certainly opposes the notion that each business requirement needs to be matched to an optimal data access method. The philosophy of choosing a specific data access method for each task should theoretically result in the best overall performance for your application ( assuming you made the appropriate selection in all cases ). However, this perceived benefit is far overshadowed by the liability in terms of inconsistent development practices. 通用数据借口策略的概念必然反对每个商业需求都需要最佳的数据接口模式这种观念。从理论上说应该为应用程序选择最佳的方式（假定你选择了最适合的方式）。但是，这样做对今后不同的开发是不利的
Falling back on traditional concepts known as the 80/20 rule, DotNetNuke has focused on providing a consistent data access strategy which achieves the optimal goals in 80 percent of application use cases. In the other 20 percent, it is up to the organization to decide whether the performance requirements necessitate the implementation of a different data access method for the task; while at the same time accepting the consequences outlined above.
退回到传统的80/20原则，DNN的通用数据借口策略已经满足了80%的用户需求。另外的20%，决定于团队是否根据需求来强制执行不同的数据接口方式，并愿意承担上面所提到的后果。
One of the key requirements of DotNetNuke is to provide an implementation of the application that supports multiple data stores.
需求
DNN的 关键需求就是一贯应用程序能满足不同的数据存储。
Due to the fact we require the ultimate in flexibility and performance in terms of communicating with external data stores, we chose to discard the generic data access approach and build the application to take advantage of the database-native feature set ( ie. .NET managed providers, proprietary SQL dialects, stored procedures, etc… ). The tradeoff that we made when choosing to go with a database-specific access class was that we would need to write a separate data access layer for each database platform we wanted to support and hence the application would contain more code. While the data access layers share much common code, each is clearly targeted for use with a specific database.
由于我门需要最终的柔韧性和外部存储数据执行数据交换，我们放弃了通常数据接口，并基于数据库本身特性(ie.,SQL语言，存储过程…)建立程序。我们需要为不同数据平台写自己的类。当数据层共享了通用代码，每个都可以轻易使用特定数据库。
In order to simplify the use of the database access classes we elected to use the Provider Pattern ( also known as the Factory Design Pattern as outlined by the Gang of Four – GOF ), with reflection being used to dynamically load the correct data access objects at runtime. The factory is implemented as follows: an abstract class is created with a method declared for each method that must be exposed by the database access classes. For each database that we want to support, we create a concrete class that implements the database specific code to perform each of the operations in the abstract class or "contract." To support the runtime determination of which concrete class to load, we also include an Instance() method which is the factory itself and relies on our generic Provider class to read a value from configuration file to determine which assembly to load using reflection. Due to the fact that reflection is very expensive in terms of application performance, we store the constructor of the data provider class in the cache.
为了简化数据类我们选择了Provider模式（Factory模式中已经提到），在运行时动态装载数据对象。Factory模式按照下面执行：声明一个抽象类，每个方法针对不同的数据层。对我们想支持的每个数据库，我们建立一个混合类来执行抽象类定义的方法或contact。为了支持运行时复合类装载，我门包含了一个Factory自己的Include()方法，并依赖于我们的 Provider类来读取配置文件决定装载哪个程序集。由于映射对程序性能来说是很重要的，我们缓存了数据provider类。
Why abstract classes instead of interfaces? This is due to the fact that interfaces are immutable ( static ) and as a result do not lend themselves to versioning. Because interfaces do not support implementation inheritance, the pattern that applies to classes does not apply to interfaces. Adding a method to an interface is equivalent to adding an abstract method to a base class; any class that implements the interface will break because the class does not implement the new method.
为什么抽象类代替了接口？这是由于接口是静态的，并不允许自身的改写。由于接口不能支持继承，用于类的模式不能用于接口。对于一个类来说添加接口和添加抽象类是一样的；执行接口的时候类会中断，因为类不能执行新的方法。
The following diagram shows how the business logic, factory, and databases access classes interact with each other. The key advantage of the solution built is that the database access class can be compiled after the business logic classes as long as the data access class implements the DataProvider abstract class methods. This means that should we want to create an implementation for another database, we do not need to change the business logic tier (or UI tier). The steps to create another implementation are:
下面的图显示了商务逻辑，Factoey模式，数据接口之间的关系。建立这样的解决方案的关键优点是只要数据类执行了DataProvider的抽象类的方法，数据接口类可以在上午逻辑层之后编译。这意味着 我们更换数据库的时候不用更换BLL层。
执行步骤如下：
1. 为新数据库建立数据接口类执行Dataprovider的抽象类；
2. 编译类为一个程序集；
3. 测试并部署新的数据程序集在一个 服务器上；
4. 修改配置文件指向新的数据接口类；
5. 在BLL层无需任何改变和重编译。
Create the database access classes for the new database which implement the DataProvider abstract class. Compile the class into an assembly. Test and deploy the new data assembly to a running server. Change the configuration file to point to the new database access class. No changes or recompiles need to be performed on the business logic components.
- 作者：evan2046 2005年06月1日, 星期三 11:34加入博采
DNN研究之数据接口白皮书(2)- -
The web.config file contains a number of critical sections to enable the DataProvider pattern. The first section registers the Providers and their corresponding ConfigurationSectionHandlers. Although in this instance we only have a single section specified for the DotNetNuke group, we could use the exact same method to configure other providers ( ie. abstract authentication providers, etc… ). The only catch is that the section name value must be implemented elsewhere in the web.config file.
配置
web.config文件包含几个关键部分设置DataProvide。首先是注册Provider和 相关的ConfigurationSectionHandlers。我们可以按照这个设置来自己 进行配置。重要的 是section name在其他地方应该是统一的。
sectionGroup>
configSections>
The following section is retained for legacy modules which rely on the old method of data access:
下面的设置保持了旧的数据接口方式：
appSettings>
And finally the meat of the Provider model. The section name within the group ( as described in the configSections above ) should contain a defaultProvider attribute which relates to a specific instance in thecollection below. The defaultProvider is used as the single switch for changing from one provider to another. If a default provider is not specified, the first item in the collection is considered the default.
主要的，包含在里的区应包含一个 默认的 Provider，即下面 的。默认的Proider用来 在不同的Provider之间互相切换。如果没有 指定缺省的Provider，第一个 将被 设为默认的。
The section also includes acollection specification where all of the implementations for are identified. At a bare minimum, each provider must include a name, type, and providerPath attribute ( name is generic but usually refers to the classname, type refers to the strong classname of the provider, and providerPath refers to the location where provider specific resources such as scripts can be found ). Each provider can also have any number of custom attributes as well.
区包含了集合，最少应该包括名称，类型，路径（名称通常用类名，类型指向强类名，路径指定脚本资源访问）
connectionString = "Server=localhost;Database=DotNetNuke;uid=sa;pwd=;"
providerPath = "~\Providers\DataProvider\SqlDataProvider\"
objectQualifier = "DotNetNuke"
databaseOwner = "dbo"
/>
connectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
providerPath = "~\ Providers\DataProvider\AccessDataProvider\"
objectQualifier = "DotNetNuke"
databaseFilename = "DotNetNuke.resources"
/>
providers>
data>
dotnetnuke>
The following specification rules are in effect for defining nodes within the “providers” collection.
The configuration section contains one or more , , or elements. The following rules apply when processing these elements:
It is not an error to declare an empty element. Providers inherit items from parent configuration statements. It is an error to redefine an item using if the item already exists or is inherited. It is an error to a non-existing item. It is not an error to , , and then the same item again. It is not an error to , >, and then the same item again. removes all inherited items and items previously defined, e.g. an declared before a is removed while an declared after a is not removed.
下面的 规则说明了provider节点的 设置。
配置区包括了一个或多个元素。下面是设置规则：
1. 对于空元素没有错误声明；
2. Providers继承父级配置中的声明；
3. 如果已经存在或者已经继承了将会有 错误发生；
4. 用于一个 不存在的元素将会出错；
5. 对于元素然后又不会出错；
6. 对于元素后又不会出错；
7. 移除了所以以前声明和继承的元素，例如在之前声明的将被移除，而在之后则不受影响。
Description
Adds a data provider.
Attributes
name – Friendly name of the provider.
type – A class that implements the required provider interface. The value is a fully qualified reference to an assembly.
providerPath - the location where provider specific resources such as scripts can be found
Other name/value pairs – Additional name value pairs may be present. All name/value pairs are the responsibility of the provider to understand.
Description
Removes a named data provider.
Attributes
name – Friendly name of the provider to remove.
Description
Removes all inherited providers.
Provider.vb类提供了所有从web.config载入Provider，并且执行的设置。这是一个 通用类，不止包含数据接入。
The Provider.vb class provides all of the implementation details for loading the provider information from the web.config file and applying the , , processing rules. It is a generic class which is not only applicable to data access.
Dataprovider.Vb是一个 抽象类，包含了DotNetNuke所有的数据接口方法。它包含了一个factory自身的Instance（）方法，根据web.config设置来装载相应的程序集。
The DataProvider.vb is the abstract class containing all data access methods for DotNetNuke. It contains an Instance() method which is the factory itself and loads the appropriate assembly at runtime based on the web.config specification.
' provider constants - eliminates need for Reflection later
Private Const [ProviderType] As String = "data" ' maps to in web.config
' create a variable to store the reference to the instantiated object
Private Shared objProvider As DataProvider
Public Shared Function Instance() As DataProvider
' does the provider reference already exist?
If objProvider Is Nothing Then
Dim strCacheKey As String = [ProviderType] & "provider"
' use the cache because the reflection used later is expensive
Dim objType As Type = CType(DataCache.GetCache(strCacheKey), Type)
If objType Is Nothing Then
' Get the provider configuration based on the type
Dim objProviderConfiguration As ProviderConfiguration = ProviderConfiguration.GetProviderConfiguration([ProviderType])
' The assembly should be in \bin or GAC, so we simply need to get an instance of the type
Try
' Get the typename of the Core DataProvider from web.config
Dim strTypeName As String = CType(objProviderConfiguration.Providers(objProviderConfiguration.DefaultProvider), Provider).Type
' use reflection to get the type of the class that implements the provider
objType = Type.GetType(strTypeName, True)
' insert the type into the cache
DataCache.SetCache(strCacheKey, objType)
Catch e As Exception
' Could not load the provider - this is likely due to binary compatibility issues
End Try
End If
' save the reference
objProvider = CType(Activator.CreateInstance(objType), DataProvider)
End If
Return objProvider
End Function
All data access methods are defined as MustOverride which means that any data provider derived from this class must provide implementations for these methods. This defines the abstract class contract between the Business Logic Layer and the Data Access Layer.
所有数据接口方法都是被必须覆写定义的，即从这个类衍生的任何数据provider都会执行那些方法。这就 定义了抽象类来联系BLL和DAL。
' links module
Public MustOverride Function GetLinks(ByVal ModuleId As Integer) As IDataReader
Public MustOverride Function GetLink(ByVal ItemID As Integer, ByVal ModuleId As Integer) As IDataReader
Public MustOverride Sub DeleteLink(ByVal ItemID As Integer)
Public MustOverride 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)
Public MustOverride 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)
The Data Access Layer must implement the methods contained in the DataProvider abstract class. However, each DAL provider may be very different in its actual implementation of these methods. This approach allows the provider the flexibility to choose its own database access protocol ( ie. managed .NET, OleDB, ODBC, etc… ). It also allows the provider to deal with proprietary differences between database platforms ( ie. stored procedures, SQL language syntax, @@IDENTITY ).
Each data provider must specify an implementation for its custom attributes defined in the web.config file.
数据接口层DAL
DAL必须定义了DataProvider定义的抽象类的所有方法。然而，每个DALprovider在实现那些方法上是完全不同的(如.net,OleDB,ODBC..等等)。这就定义了provider使用自己数据接口的灵活性。这样也 允许了不同数据库平台应用自己的特性（如存储过程，SQL语句...等等）。
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
Data access methods must be designed as simple queries ( ie. single SELECT, INSERT, UPDATE, DELETE ) so that they can be implemented on all database platforms. Business logic such as conditional branches, calculations, or local variables should be implemented in the Business Logic Layer so that it is abstracted from the database and centralized within the application. This simplistic approach to data access may take some getting used to if you frequently work with rich SQL languages variants that allow you to implement programming logic at the database level ( ie. Transact-SQL stored procedures which perform either an INSERT or UPDATE based on the specification of an identifier ).
The SQL Server / MSDE DataProvider included with DotNetNuke uses Stored Procedures as a best practice data access technique.
数据接口方法必须被设计为单一查询（如单一SELECT,INSERT,UPDATE,DELETE）,用来适用于所有的数据库平台。一些商务逻辑例如条件语句，计算，局部变量应该都包含在BLL内，以便从数据库和程序集中抽象出来。如果你经常使用不同的SQL语言，这样简化的方式允许你 在数据库层编程。
' 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 Function 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) As Integer
Return CType(SqlHelper.ExecuteScalar(ConnectionString, DatabaseOwner & ObjectQualifier & "AddLink", ModuleId, UserName, Title, Url, MobileUrl, GetNull(ViewOrder), Description, NewWindow), Integer)
End Function
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, GetNull(ViewOrder), Description, NewWindow)
End Sub
The Microsoft Access data provider included with DotNetNuke also uses stored procedures ( stored queries ) but does not have the Auto Parameter Discovery feature ( CommandBuilder.DeriveParameters ) therefore the parameters must be explicitly declared. Also notice that Access does not support @@IDENTITY to return the unique auto number generated; therefore, it must be retrieved in a subsequent database call ( GetLinkIdentity ).
DNN包含的Access数据库provide也使用存储过程，但没有自动参数返回功能。因此参数必须被显式声明。要 注意Access不 支持@@IDENTIFY这样来返回生成的唯一自动数值，因此，必须要在下次数据库调用中得到。
' 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 Function 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) As Integer
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", GetNull(ViewOrder)), _
New OleDbParameter("@Description", Description), _
New OleDbParameter("@NewWindow", NewWindow))
Return CType(OleDBHelper.ExecuteScalar(ConnectionString, CommandType.StoredProcedure, ObjectQualifier & "GetLinkIdentity", _
New OleDbParameter("@ModuleId", ModuleId), _
New OleDbParameter("@Title", Title)), Integer)
End Function
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", GetNull(ViewOrder)), _
New OleDbParameter("@Description", Description), _
New OleDbParameter("@NewWindow", NewWindow))
End Sub
DotNetNuke contains an Auto Upgrade feature which allows the application to upgrade the database automatically when a new application version is deployed. Scripts must be named according to version number and dataprovider ( ie. 02.00.00.SqlDataProvider ) and must be located in the directory specified in the providerPath attribute in the web.config file. Dynamic substitution can be implemented in scripts by overriding the ExecuteScript method in the provider implementation. This can be useful for object naming and security specifications.
数据库脚本
DNN包含了自动升级的功能，当程序发布新版本的时候允许程序自动升级数据库。脚本必须按照版本号和DataProvider来命名（例如02.00.00.SqlDataProvider）,并且必须位于web.config中providerPath属性指定的路径。在provider执行的时候会重载执行脚本，动态的更新数据库。
create procedure {databaseOwner}{objectQualifier}GetLinks
@ModuleId int
as
select ItemId,
CreatedByUser,
CreatedDate,
Title,
Url,
ViewOrder,
Description,
NewWindow
from {objectQualifier}Links
where ModuleId = @ModuleId
order by ViewOrder, Title
GO
Since many databases do not have a rich scripting language, each provider also has the ability to implement the UpgradeDatabaseSchema method which can be used to programmatically alter the database structure.
由于许多数据库没有富脚本语言，每个provider也可以执行UpgradeDatabaseSchema方法，由程序修改数据库结构。
Public Overrides Sub UpgradeDatabaseSchema(ByVal Major As Integer, ByVal Minor As Integer, ByVal Build As Integer)
' add your database schema upgrade logic related to a specific version. This is used for data stores which do not have a rich scripting language.
Dim strVersion As String = Major.ToString & "." & Minor.ToString & "." & Build.ToString
Select Case strVersion
Case "02.00.00"
End Select
End Sub
SQL Language Syntax
SQL Server and MSDE have a rich scripting language called Transact-SQL which supports local variables, conditional branches ( if …. then … else ), and looping. Script can be nicely formatted using tabs and spaces for improved readability.
SQL语言 语句
SQL Server和 MSDE有T-Sql脚本语言，支持局部变量，条件语句，循环。脚本语言通过缩进和空格控制格式提高易读性。
drop procedure {databaseOwner}{objectQualifier}GetPortalTabModules
go
create procedure {databaseOwner}{objectQualifier}GetPortalTabModules
@PortalId int,
@TabId int
as
select {objectQualifier}Modules.*,
{objectQualifier}Tabs.AuthorizedRoles,
{objectQualifier}ModuleControls.ControlSrc,
{objectQualifier}ModuleControls.ControlType,
{objectQualifier}ModuleControls.ControlTitle,
{objectQualifier}DesktopModules.*
from {objectQualifier}Modules
inner join {objectQualifier}Tabs on {objectQualifier}Modules.TabId = {objectQualifier}Tabs.TabId
inner join {objectQualifier}ModuleDefinitions on {objectQualifier}Modules.ModuleDefId = {objectQualifier}ModuleDefinitions.ModuleDefId
inner join {objectQualifier}ModuleControls on {objectQualifier}ModuleDefinitions.ModuleDefId = {objectQualifier}ModuleControls.ModuleDefId
inner join {objectQualifier}DesktopModules on {objectQualifier}ModuleDefinitions.DesktopModuleId = {objectQualifier}DesktopModules.DesktopModuleId
where ({objectQualifier}Modules.TabId = @TabId or ({objectQualifier}Modules.AllTabs = 1 and {objectQualifier}Tabs.PortalId = @PortalId))
and ControlKey is null
order by ModuleOrder
GO
MS Access has its own proprietary SQL language called JET SQL. In contrast to Transact-SQL, JET SQL is an extremely limited scripting language as it has no support for local variables, conditional branches, or looping. This limits the queries to very simple CRUD operations. A few syntactical items to note are JET does not accept script formatting using tabs, stored queries need () around the parameter list and parameter names must be wrapped in [@], NULL must be handled using the isnull() function, the word “outer” is dropped from the join clause, the Now() function returns the current date, bit fields use boolean True and False specification, and the query must be completed with a semi-colon (“;”).
Accesss数据库有自己的 脚本语言称为JET SQL。和T-SQL相比，JET SQL是非常有限的脚本语言，不 支持变量，条件语句，循环。这就 限制了查询只能是CRUD操作。需要 注意JET不 支持使用缩减控制格式，存储查询需要在参数加（），参数名称必须包括[@],控制必须被isnull函数处理，“outer“从联合语句取消了，Now（）返回现在日期，位值使用布儿的TRUE和False定义，并且查询必须由(“;”)结束。
drop procedure {objectQualifier}GetPortalTabModules
go
create procedure {objectQualifier}GetPortalTabModules ( [@PortalId] int, [@TabId] int )
as
select {objectQualifier}Modules.*,
{objectQualifier}Tabs.AuthorizedRoles,
{objectQualifier}ModuleControls.ControlSrc,
{objectQualifier}ModuleControls.ControlType,
{objectQualifier}ModuleControls.ControlTitle,
{objectQualifier}DesktopModules.*
from {objectQualifier}Modules, {objectQualifier}Tabs, {objectQualifier}DesktopModules, {objectQualifier}ModuleDefinitions, {objectQualifier}ModuleControls
where {objectQualifier}Modules.TabId = {objectQualifier}Tabs.TabId
and {objectQualifier}Modules.ModuleDefId = {objectQualifier}ModuleDefinitions.ModuleDefId
and {objectQualifier}ModuleDefinitions.ModuleDefId = {objectQualifier}ModuleControls.ModuleDefId
and {objectQualifier}DesktopModules.DesktopModuleId = {objectQualifier}ModuleDefinitions.DesktopModuleId
and ({objectQualifier}Modules.TabId = [@TabId] or ({objectQualifier}Modules.AllTabs = True and {objectQualifier}Tabs.PortalId = [@PortalId]))
and isnull(ControlKey) = True
order by ModuleOrder;
GO
Another item to note is that MS Access is a file-based database and as a result, requires write access at the file system level. In addition, the *.mdb file extension is not secured by default; therefore, you need to take precautions to ensure your data can not be compromised. As a result, the default web.config file specifies the database filename as DotNetNuke.resources. The *.resources file extension is protected by ASP.NET using the HttpForbiddenHandler in the machine.config and prevents unauthorized download. The Template.resources file distributed with the AccessDataProvider is a template database which contains a baseline schema and data for creating new Access databases.
另外需要注意的 是Access是基于文件的 数据库，因此需要文件写属性的要求。特别的,缺省*.mdb扩展名的 文件是 不 安全的。因此需要采取措施，防止数据流失。*.resource扩展名的 文件是被.Net保护的，建议修改。
- 作者：evan2046 2005年06月1日, 星期三 14:36加入博采
DNN研究之数据接口白皮书(4) - -
Database Object Naming
The web.config file includes an attribute named objectQualifer which allows you to specify a prefix for your database obects ( ie. DNN_ ). Web hosting plans usually offer only one SQL Server database that you have to share among all web applications in your account. If you do not specify an object prefix you may have a naming conflict with an application that already uses a specific object name ( ie. Users table ). Another benefit of prefixing object names is they will be displayed grouped together in tools such as Enterprise Manager for SQL Server when listed alphabetically which simplifies management.
If you are upgrading a pre-2.0 DotNetNuke database, you will want to set the objectQualifier to “”. This is because you may have third party modules which do not use the new DAL architecture and are dependent on specific object names. Setting the objectQualifier on an upgrade will rename all of your core database objects which may result in errors in your custom modules.
数据库对象命名
web.config文件包含了一个objectQualifer属性允许你 指定数据库对象前缀（例如DNN）.web主机通常只提供一个SQL Server数据库，不得不用你的账号的所有web程序 中共享。如果不指定对象前缀很容易混淆
The Microsoft Data Access Application Block ( MSDAAB ) is a .NET component that contains optimized data access code that helps you call stored procedures and issue SQL text commands against a SQL Server database. We use it as a building block in DotNetNuke to reduce the amount of custom code needed to create, test, and maintain data access functions in the application. We also created an OleDB.ApplicationBlocks.Data assembly for the Microsoft Access data provider based on the MSDAAB code.
In terms of implementation we chose to use the MSDAAB as a black box component rather than include the actual source code in our DAL implementation. This decision helps prevent modification of the MSDAAB code which enables us to upgrade the component seamlessly as new features/fixes become available.
程序模块
MSDAAB是.net组件，包含了优化过的数据接口代码帮助你 调用存储过程和sql命令。我们把他作为内置模块是减少用户写代码的工作。不提供原码是为了以后升级方便。
DotNetNuke uses the DataReader for passing collections of data from the Data Access Layer ( DAL ) to the Business Logic layer ( BLL ) layer. The DataReader was chosen because it is the fastest performing data transport mechanism available in ADO.NET ( a forward only, read only, stream of data ). The IDataReader is the base interface for all .NET compatible DataReaders. The abstract IDataReader allows us to pass data between layers without worrying about the data access protocol being used in the actual data provider implementation ( ie. SqlClient, OleDB, ODBC, etc… ).
DNN使用dr从DAL到BLL传输数据集合。Dr的性能在ADO.NET中是最快的。IDataReader是所有dr的基本接口。抽象的dr使你传送数据而不用担心实际应用中的数据协议。
Good object oriented design recommends the abstraction of the data store from the rest of the application. Abstraction allows the application to build upon an independent set of logical interfaces; thereby reducing the dependency on the physical implementation of the underlying database.
好的对象定向设计需要从程序中抽象出数据存储。抽象使程序基于相对独立的逻辑接口，因此减少对物理执行的基础数据库的依赖。
- 作者：evan2046 2005年06月1日, 星期三 14:43加入博采