Data Abstraction

Database Providers

Module development closely mirrors DotNetNuke architecture. Each module should provide its own abstraction to the underlying database. This enables you to change physical databases without having to change or recompile the underlying code of DotNetNuke and your module. Remember, if you want to support multiple databases with your module, you need to create a physical provider for each database you want to support. So even if your DotNetNuke implementation is using a provider other than the one included in SQL Support, such as Oracle, for example, you need to create a provider for your module to support Oracle as well.

The only direct interaction with the previous stored procedures contained in your database will be done in the provider project. In the modules solution of DotNetNuke, all modules have a corresponding project for a SQL Data Provider. For example, the main module project called DotNetNuke.Events is contained in the DesktopModules solution. In addition to this project, you have the DotNetNuke.Events .SQLDataProvider project. This project contains the class and methods necessary to interact with the stored procedures covered earlier in this chapter. The following sections cover this class and the methods it contains to create a provider for this module.

The SQLDataProvider class for the Events module and all modules within the DotNetNuke .DesktopModules solution closely mirrors the structure of the DotNetNuke core architecture. Therefore, you will see the same methods contained with this project as you would see in the main DotNetNuke .SQLDataProvider project included in the Solutions directory within the DotNetNuke distribution.

This section breaks down the structure of the database provider class for the Events module beginning with Listing 13-7.

Listing 13-7: Importing Namespaces for the Events Module Data Provider Class

 Imports System Imports System.Data Imports System.Data.SqlClient Imports Microsoft.ApplicationBlocks.Data 

You import various namespaces into your class for dealing with the database. System.Data.SqlClient is used to connect to the SQL Server database. Because this assembly connects to the physical database, you need to use specific classes for connecting and manipulating the database. Microsoft.ApplicationBlocks .Data provides your assembly, which helps to reduce the code required for calling stored procedures and commands.

Note 

You can find more information on Microsoft Application Blocks in the "Patterns and Practices" section within the MSDN site at http://msdn.microsoft.com/library/en-us/dnanchor/html/Anch_EntDevAppArchPatPrac.asp.

After importing the namespaces, the namespace DotNetNuke is added: Namespace DotNetNuke.Modules.Events. One thing to note here is that you're using a DotNetNuke core module as an example. If you develop your own modules for DotNetNuke, you should create your own unique namespace in the form of CompanyName.ModuleName and CompanyName.ModuleName.SQLDataProvider. This ensures that your namespace is unique and should not conflict with other third-party modules with a single DotNetNuke portal framework.

From here you have your standard class name, and because you're creating a physical data provider class, you'll also inherit the DataProvider class of DotNetNuke (see Listing 13-8). Each Data AccessLayer must implement the methods contained in its DataProvider class, which are overridden for each physical database type (as you'll see later in this chapter).

Listing 13-8: Inheriting the DataProvider Class for the Module

 Public Class SqlDataProvider         Inherits DataProvider 

Each of the following sections of code for the Data Access Layer is broken down by regions. Regions are used in DotNetNuke development to organize code and make the code more readable.

The first region in the class is the Private Members region (see Listing 13-9). In this region, you define the variables for your provider, which is defined within web.config.

Listing 13-9: The Private Members of the Data Access Layer

 #Region "Private Members"     Private Const ProviderType As String = "data"     Private _providerConfiguration As Framework.Providers. ProviderConfiguration = Framework.Providers.ProviderConfiguration. GetProviderConfiguration(ProviderType)     Private _connectionString As String     Private _providerPath As String     Private _objectQualifier As String     Private _databaseOwner As String #End Region 

As in the overall DotNetNuke architecture, the code refers to the provider configuration within the data section of web.config (see Listing 13-10). In this section, you define the values for properties in the SqlDataProvider class.

Listing 13-10: Defining the Default Data Provider in the web.config

 <data defaultProvider="SqlDataProvider">       <providers>         <clear />         <add name="SqlDataProvider"           type="DotNetNuke.Data.SqlDataProvider, DotNetNuke.SqlDataProvider"           connectionStringName="SiteSqlServer"           upgradeConnectionString=""           providerPath="~\Providers\DataProviders\SqlDataProvider\"           objectQualifier="" templateFile="DotNetNuke_template.mdf"           databaseOwner="dbo" />       </providers> </data> 

Next is the Constructors region, where you read web.config and then populate the values from the data section to your private members within your class (see Listing 13-11).

Listing 13-11: Constructors Regions in the SQLDataProvider Class of the Events Module

 #Region "Constructors"     Public Sub New()         ‘ Read the configuration specific information for this provider         Dim objProvider As Framework.Providers.Provider = _CType(_provider Configuration.Providers(_providerConfiguration.DefaultProvider), _ Framework .Providers.Provider)         ‘ Read the attributes for this provider         If objProvider.Attributes("connectionStringName") <> "" AndAlso _ System.Configuration.ConfigurationSettings.AppSettings(objProvider.Attributes  ("connectionStringName")) <> "" Then             _connectionString = System.Configuration.  ConfigurationSettings.AppSettings(objProvider.Attributes("connectionStringName"))         Else             _connectionString = objProvider.Attributes("connectionString")         End If         _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 #End Region 

After populating your private members with values from web.config, you then expose some public properties for your class (see Listing 13-12). These properties are read-only and contain the values from web.config.

Listing 13-12: Public Properties — Exposing the Database Connection Information in the SQLDataProvider Class

 #Region "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 #End Region 

The database operations of your class are contained within the Public Methods region (see Listing 13-13). Remember the stored procedures discussed earlier? Now you're going to expose those procedures to your module so you can do your add, update, and delete operations, as well as obtain the data so it can be displayed in your module.

Listing 13-13: Public Methods within the SQLDataProvider Class

 #Region "Public Methods"     Private Function GetNull(ByVal Field As Object) As Object         Return Common.Utilities.Null.GetNull(Field, DBNull.Value)     End Function       Public Overrides Function AddEvent(ByVal ModuleId As Integer, _            ByVal Description As String, ByVal DateTime As Date, _            ByVal Title As String, ByVal ExpireDate As Date, _            ByVal UserName As String, ByVal Every As Integer, _            ByVal Period As String, ByVal IconFile As String, _            ByVal AltText As String) As Integer         Return CType(SqlHelper.ExecuteScalar(ConnectionString, _            DatabaseOwner & ObjectQualifier & "AddEvent", ModuleId, _            Description, DateTime, Title, GetNull(ExpireDate), _            UserName, GetNull(Every), GetNull(Period), _            GetNull(IconFile), GetNull(AltText)), Integer)       End Function       Public Overrides Sub DeleteEvent(ByVal ItemId As Integer)         SqlHelper.ExecuteNonQuery(ConnectionString, DatabaseOwner & _ ObjectQualifier & "DeleteEvent", ItemId)       End Sub       Public Overrides Function GetEvent(ByVal ItemId As Integer, _ ByVal ModuleId As Integer) As IDataReader         Return CType(SqlHelper.ExecuteReader(ConnectionString, _ DatabaseOwner & ObjectQualifier & "GetEvent", ItemId, _ ModuleId), IDataReader)       End Function       Public Overrides Function GetEvents(ByVal ModuleId As Integer) As _ IDataReader         Return CType(SqlHelper.ExecuteReader(ConnectionString, _ DatabaseOwner & ObjectQualifier & "GetEvents", ModuleId), _ IDataReader)       End Function       Public Overrides Function GetEventsByDate(ByVal ModuleId As Integer, _          ByVal StartDate As Date, ByVal EndDate As Date) As IDataReader         Return CType(SqlHelper.ExecuteReader(ConnectionString, _ DatabaseOwner & ObjectQualifier & "GetEventsByDate", _ ModuleId, StartDate, EndDate), IDataReader)       End Function       Public Overrides Sub UpdateEvent(ByVal ItemId As Integer, _          ByVal Description As String, ByVal DateTime As Date, _          ByVal Title As String, ByVal ExpireDate As Date, _          ByVal UserName As String, ByVal Every As Integer, _          ByVal Period As String, ByVal IconFile As String, _          ByVal AltText As String)         SqlHelper.ExecuteNonQuery(ConnectionString, DatabaseOwner & _            ObjectQualifier & "UpdateEvent", ItemId, Description, _            DateTime, Title, GetNull(ExpireDate), UserName, _            GetNull(Every), GetNull(Period), GetNull(IconFile), _            GetNull(AltText))         End Sub #End Region 

You can see that there is a one-to-one relationship within this class, so each method has a corresponding stored procedure within your SQL database. Here's a break down of the GetEvents method to explain what is happening.

Each event is a public method that overrides a corresponding method within the base class (DataProvider), which you inherited in the beginning of the class. So not only do you have a corresponding method in this class for each stored procedure, but you also have a corresponding method in the base DataProvider class, which is located in the main module project. The method within the base class is an abstracted method that your module implements, which enables you to separate the physical database interactions from your module assembly.

Next you'll notice that all parameters the stored procedure accepts are passed to your methods as well. In addition, you then execute the command and pass the database connection information such as the connection string, database owner account, object qualifier, name of the stored procedure, and the parameters it accepts.

The method then returns an IDataReader containing the result set from the database using the SQLHelper.ExecuteReader provided by the Microsoft Data Access Application Block you imported at the beginning of the class.

Finally, to handle null values returned from the database, DotNetNuke provides the GetNull method. When you create a method for your database, as was done in AddEvent and UpdateEvent in Listing 13-13, you should wrap the parameters with the GetNull method. This prevents errors from being raised in your Data Provider due to the null values.

That's it for the Data Access Layer. Remember this layer is compiled into its own assembly binary separate from the module's main assembly. By maintaining this separation, you can easily plug in providers for other databases. In addition, you don't need to recompile your base class when changing database operations or when replacing physical providers.