(VB) DNN 5 module development tutorial - part 3: The data layer

3. June 2010 22:41

To C# version

In this part we are going to take a look at how the data layer is organized, set up the database objects and create the functionality needed to access the data.

Of course you are free to access your data any way you want. You might not even have the need to access the database. Anyway, the project template provides us with a data layer structure that I found quite okay to work with.
Your module controls call methods in the ProductsController class (our BLL), which calls methods in the SqlDataProvider class (part of our DAL, which inherits from the abstract DataProvider class). SqlDataProvider then access the database by calling stored procedures (also part of our DAL). The stored procedure calls are made using the Microsoft Enterprise Library.

So, where do we start? Let's build from the ground up and start with the SQL scripts that set up your database objects.
Open the file 01.00.00.SqlDataProvider. As you can see the file is really just an SQL batch script with placeholders for database owner and object prefix ({databaseOwner} and {objectQualifier}). On module installation the placeholders are replaced with the current DotNetNuke installation's values for database owner and object prefix.
I suggest we begin with a clean slate, so delete everything in the file. Insert the following instead:

/** Create Table **/
if not exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}SipidCode_Products]') and OBJECTPROPERTY(id, N'IsTable') = 1)
    BEGIN
        CREATE TABLE {databaseOwner}[{objectQualifier}SipidCode_Products]
        (
            [ModuleID] [int] NOT NULL,
            [ItemID] [int] NOT NULL IDENTITY(1, 1),
            [Name] [nvarchar](200) NOT NULL,
            [Description] [nvarchar](2000) NOT NULL
        )

        ALTER TABLE {databaseOwner}[{objectQualifier}SipidCode_Products] ADD CONSTRAINT [PK_{objectQualifier}SipidCode_Products] PRIMARY KEY NONCLUSTERED  ([ItemID])
        CREATE CLUSTERED INDEX [IX_{objectQualifier}SipidCode_Products] ON {databaseOwner}[{objectQualifier}SipidCode_Products] ([ModuleID])

        ALTER TABLE {databaseOwner}[{objectQualifier}SipidCode_Products] WITH NOCHECK ADD CONSTRAINT [FK_{objectQualifier}SipidCode_Products_{objectQualifier}Modules] FOREIGN KEY ([ModuleID]) REFERENCES {databaseOwner}[{objectQualifier}Modules] ([ModuleID]) ON DELETE CASCADE NOT FOR REPLICATION
    END
GO

To not loose focus we keep the data structure fairly simple... nothing too weird here. The ModuleID column is used to associate the product items to a specific module instance. The rest of the columns belong to the actual product item.
Now that we have a table we need some stored procedures to access its data. But before we create the stored procedures it's a good idea to drop them if the already exist. So add the following:

/** Drop Existing Stored Procedures **/

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}SipidCode_GetProducts]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    drop procedure {databaseOwner}{objectQualifier}SipidCode_GetProducts
GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}SipidCode_GetProduct]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    drop procedure {databaseOwner}{objectQualifier}SipidCode_GetProduct
GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}SipidCode_AddProduct]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    drop procedure {databaseOwner}{objectQualifier}SipidCode_AddProduct
GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}SipidCode_UpdateProduct]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    drop procedure {databaseOwner}{objectQualifier}SipidCode_UpdateProduct
GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}SipidCode_DeleteProduct]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    drop procedure {databaseOwner}{objectQualifier}SipidCode_DeleteProduct
GO

And lastly, creation of the stored procedures:

/** Create Stored Procedures **/

create procedure {databaseOwner}{objectQualifier}SipidCode_GetProducts
    @ModuleId int
as

select ModuleId,
       ItemId,
       [Name],
       Description
from {objectQualifier}SipidCode_Products
where  ModuleId = @ModuleId
GO

create procedure {databaseOwner}{objectQualifier}SipidCode_GetProduct
     @ModuleId int,
     @ItemId    int
as

select ModuleId,
       ItemId,
       [Name],
       Description
from {objectQualifier}SipidCode_Products
where  ModuleId = @ModuleId
and ItemId = @ItemId
GO

create procedure {databaseOwner}{objectQualifier}SipidCode_AddProduct
    @ModuleId    int,
    @Name         nvarchar(200),
    @Description  nvarchar(2000)
as

insert into {objectQualifier}SipidCode_Products (
       ModuleId,
       [Name],
       Description
)
values (
    @ModuleId,
    @Name,
    @Description
)
GO

create procedure {databaseOwner}{objectQualifier}SipidCode_UpdateProduct
    @ModuleId     int,
    @ItemId        int,
    @Name          nvarchar(200),
    @Description   nvarchar(2000)
as

update {objectQualifier}SipidCode_Products
set    Name       = @Name,
        Description = @Description
where ModuleId = @ModuleId
and ItemId = @ItemId
GO

create procedure {databaseOwner}{objectQualifier}SipidCode_DeleteProduct
    @ModuleId       int,
    @ItemId          int
as

delete
from   {objectQualifier}SipidCode_Products
where  ModuleId = @ModuleId
and    ItemId = @ItemId
GO

To store the data from the database and use it the application code, we use a class as data holder. This class only members are public properties corresponding to the database table columns we are interested in. Open the ProductsInfo.vb file in the Components folder.
As you see there is just an empty constructor and some public properties. Delete all class members and make the class look like this:

Public Class ProductsInfo

 Private _ModuleId As Integer
 Private _ItemId As Integer
 Private _Name As String
 Private _Description As String

 Public Property ModuleId() As Integer
  Get
   Return _ModuleId
  End Get
  Set(ByVal Value As Integer)
   _ModuleId = Value
  End Set
 End Property

 Public Property ItemId() As Integer
  Get
   Return _ItemId
  End Get
  Set(ByVal Value As Integer)
   _ItemId = Value
  End Set
 End Property

 Public Property Name() As String
  Get
   Return _Name
  End Get
  Set(ByVal Value As String)
   _Name = Value
  End Set
 End Property

 Public Property Description() As String
  Get
   Return _Description
  End Get
  Set(ByVal Value As String)
   _Description = Value
  End Set
 End Property

End Class

Personally, I would have used the shorthand auto properties introduced in VB10. But since I'd like this tutorial to work for VB9 users without too much hassle we stay old school for now :)

With the data objects prepaired we need to get our data provider in the data access layer in order.
Open the file DataProvider.vb in the Components folder. The interesting stuff is found in the "Abstract methods" region. We want to remove the last "s" from the method names and adjust the parameters of AddProduct and UpdateProduct so they look like this:

Public MustOverride Function GetProducts(ByVal ModuleId As Integer) As IDataReader
Public MustOverride Function GetProduct(ByVal ModuleId As Integer, ByVal ItemId As Integer) As IDataReader
Public MustOverride Sub AddProduct(ByVal ModuleId As Integer, ByVal Name As String, ByVal Description As String)
Public MustOverride Sub UpdateProduct(ByVal ModuleId As Integer, ByVal ItemId As Integer, ByVal Name As String, ByVal Description As String)
Public MustOverride Sub DeleteProduct(ByVal ModuleId As Integer, ByVal ItemId As Integer)

Now open SqlDataProvider.vb so we can make matching changes there. Expand the "Public Methods" region. We need to change these methods so they match the abstract methods in DataProvider. Besides changing the method signatures we also have to adjust the implementations. The string parameter in the calls to GetFullyQualifiedName() should match the name of the stored procedure we want to call. We also have to change the parameters sent to the stored procedures. The methods in the "Public Methods" region should look like this:

Public Overrides Function GetProducts(ByVal ModuleId As Integer) As IDataReader
 Return CType(SqlHelper.ExecuteReader(ConnectionString, GetFullyQualifiedName("GetProducts"), ModuleId), IDataReader)
End Function

Public Overrides Function GetProduct(ByVal ModuleId As Integer, ByVal ItemId As Integer) As IDataReader
 Return CType(SqlHelper.ExecuteReader(ConnectionString, GetFullyQualifiedName("GetProduct"), ModuleId, ItemId), IDataReader)
End Function

Public Overrides Sub AddProduct(ByVal ModuleId As Integer, ByVal Name As String, ByVal Description As String)
 SqlHelper.ExecuteNonQuery(ConnectionString, GetFullyQualifiedName("AddProduct"), ModuleId, Name, Description)
End Sub

Public Overrides Sub UpdateProduct(ByVal ModuleId As Integer, ByVal ItemId As Integer, ByVal Name As String, ByVal Description As String)
 SqlHelper.ExecuteNonQuery(ConnectionString, GetFullyQualifiedName("UpdateProduct"), ModuleId, ItemId, Name, Description)
End Sub

Public Overrides Sub DeleteProduct(ByVal ModuleId As Integer, ByVal ItemId As Integer)
 SqlHelper.ExecuteNonQuery(ConnectionString, GetFullyQualifiedName("DeleteProduct"), ModuleId, ItemId)
End Sub

Then we need to make matching changes in the ProductsController class. As I said before ProductsController calls the methods in SqlDataProvider to get the data and returns it to the controls. In our simple implementation there is not very much interesting going on in ProductsController. In a more advanced scenario there could be business specific logic in there, thereby earning ProductsController the name "business logic layer".
We want the methods in the "Public methods" region in ProductsController.vb to look like this:

Public Function GetProducts(ByVal ModuleId As Integer) As List(Of ProductsInfo)
 Return CBO.FillCollection(Of ProductsInfo)(DataProvider.Instance().GetProducts(ModuleId))
End Function

Public Function GetProduct(ByVal ModuleId As Integer, ByVal ItemId As Integer) As ProductsInfo
 Return CType(CBO.FillObject(DataProvider.Instance().GetProduct(ModuleId, ItemId), GetType(ProductsInfo)), ProductsInfo)
End Function

Public Sub AddProduct(ByVal prod As ProductsInfo)
 If prod.Name.Trim <> "" Then
  DataProvider.Instance().AddProduct(prod.ModuleId, prod.Name, prod.Description)
 End If
End Sub

Public Sub UpdateProduct(ByVal prod As ProductsInfo)
 If prod.Name.Trim <> "" Then
  DataProvider.Instance().UpdateProduct(prod.ModuleId, prod.ItemId, prod.Name, prod.Description)
 End If
End Sub

Public Sub DeleteProduct(ByVal ModuleId As Integer, ByVal ItemId As Integer)
 DataProvider.Instance().DeleteProduct(ModuleId, ItemId)
End Sub

 

Remove the inheritance from interfaces ISearchable and IPortable, changing the class declaration from

Public Class ProductsController
 Implements Entities.Modules.ISearchable, Entities.Modules.IPortable

to

Public Class ProductsController

Delete the methods GetSearchItems, ExportModule and ImportModule. We will revisit these in later posts.

With that done we now have the functionality we need to access the database and get/set/update our data. Next time we will put it to use in the control for the module's edit view.

Tags: , , ,

DotNetNuke | Modules | Tutorials

Comments are closed

About the addict

Johan Seppäläinen lives in Uppsala, Sweden. He spends most of his days working as a systems architect/developer, specialized in solutions built on Microsoft platforms.
Occasionally there is time for some recreational coding, when he pursues optimal solutions and code zen, mainly in C#. When he is not writing in this blog, that is.