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.