To Visual Basic 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.cs file in the Components folder.
As you see there is just an empty constructor and some old-style public properties. Delete all class members and make the class look like this:
public class ProductsInfo
{
public int ModuleId { get; set; }
public int ItemId { get; set; }
public string Name { get; set; }
public string Description { get; set; }
}
Clear and concise. We love auto-implemented properties, don't we?
With the data objects prepaired we need to get our data provider in the data access layer in order.
Open the file DataProvider.cs 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 abstract IDataReader GetProducts(int ModuleId);
public abstract IDataReader GetProduct(int ModuleId, int ItemId);
public abstract void AddProduct(int ModuleId, string Name, string Description);
public abstract void UpdateProduct(int ModuleId, int ItemId, string Name, string Description);
public abstract void DeleteProduct(int ModuleId, int ItemId);
Now open
SqlDataProvider.cs 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 override IDataReader GetProducts(int ModuleId)
{
return (IDataReader)SqlHelper.ExecuteReader(ConnectionString, GetFullyQualifiedName("GetProducts"), ModuleId);
}
public override IDataReader GetProduct(int ModuleId, int ItemId)
{
return (IDataReader)SqlHelper.ExecuteReader(ConnectionString, GetFullyQualifiedName("GetProduct"), ModuleId, ItemId);
}
public override void AddProduct(int ModuleId, string Name, string Description)
{
SqlHelper.ExecuteNonQuery(ConnectionString, GetFullyQualifiedName("AddProduct"), ModuleId, Name, Description);
}
public override void UpdateProduct(int ModuleId, int ItemId, string Name, string Description)
{
SqlHelper.ExecuteNonQuery(ConnectionString, GetFullyQualifiedName("UpdateProduct"), ModuleId, ItemId, Name, Description);
}
public override void DeleteProduct(int ModuleId, int ItemId)
{
SqlHelper.ExecuteNonQuery(ConnectionString, GetFullyQualifiedName("DeleteProduct"), ModuleId, ItemId);
}
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.cs to look like this:
public List<ProductsInfo> GetProducts(int ModuleId)
{
return CBO.FillCollection<ProductsInfo>(DataProvider.Instance().GetProducts(ModuleId));
}
public ProductsInfo GetProduct(int ModuleId, int ItemId)
{
return (ProductsInfo)CBO.FillObject(DataProvider.Instance().GetProduct(ModuleId, ItemId), typeof(ProductsInfo));
}
public void AddProduct(ProductsInfo prod)
{
if (prod.Name.Trim() != "")
{
DataProvider.Instance().AddProduct(prod.ModuleId, prod.Name, prod.Description);
}
}
public void UpdateProduct(ProductsInfo prod)
{
if (prod.Name.Trim() != "")
{
DataProvider.Instance().UpdateProduct(prod.ModuleId, prod.ItemId, prod.Name, prod.Description);
}
}
public void DeleteProduct(int ModuleId, int ItemId)
{
DataProvider.Instance().DeleteProduct(ModuleId, ItemId);
}
Remove the inheritance from interfaces ISearchable and IPortable, changing the class declaration from
public class ProductsController : ISearchable, 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.
Subscribe