|
|
|
The Guest Book Module
We will walk through the construction of the Guest book module in
these steps:
|
 |
Data Access Layer (DAL)
To build the Data Access Layer we will:
And that's it! It's actually not a lot of code to add to the "SqlDataProvider.vb"
and "DataProvider.vb" files. Creating the tables and stored
procedures is standard database programming that you are probably
already used to. |
From: "DotNetNuke
Module Developers Guide"
Copyright © 2003-2005 Perpetual Motion Interactive Systems, Inc. All
Rights Reserved. |
Connect To The Database
If you have created your DotNetNuke website using SQL Server 2005
Express, your Database connection should already be set up for you. If
you set it up using SQL 2000 or SQL 2005 then you may have to set it up.
To set up a connection to the SQL database, from the toolbar select VIEW
then SERVER EXPLORER. |
 |
|
In the SERVER EXPLORER right-click on DATA CONNECTIONS and select "Add
Connection" |
 |
|
Fill in the connection information and click OK |
 |
Delete the Sample Database Objects
You now need to delete the Table and Stored Procedures that the
template created so that you can make new ones (that happen to use the
same names) that the Guest Book will use.
The connection will now show up in the SERVER EXPLORER. click the
"plus icon" next to the connection to expand it. Next click the "plus
icon" next to TABLES to expand it.
Right-click on the table "YourCompany_GuestBook" and select
"Delete" and delete the table.
NOTE: At this
point the module will no longer work in your DotNetNuke web site. it
will not work again until your have completed all the steps in this
tutorial. |
 |
Click the "plus icon" next to STORED PROCEDURES to expand it. One at a
time, right-click on the following stored procedures and select "Delete"
to delete them. Remember, you have to delete them one at a time.
- YourCompany_AddGuestBook
- YourCompany_DeleteGuestBook
- YourCompany_GetGuestBook
- YourCompany_GetGuestBooks
- YourCompany_UpdateGuestBook
|
 |
Create The Table
Log into the DotNetNuke website as HOST (if you are not already
logged in as HOST) and from the HOST menu select SQL. |
 |
Paste the following script in the box:
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
object_id(N'{databaseOwner}[{objectQualifier}YourCompany_GuestBook]')
and OBJECTPROPERTY(id, N'IsTable') = 1)
DROP TABLE {databaseOwner}[{objectQualifier}YourCompany_GuestBook]
GO
CREATE TABLE {databaseOwner}[{objectQualifier}YourCompany_GuestBook]
(
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[ModuleID] [int] NULL ,
[Name] [nvarchar] (50) NULL ,
[Email] [nvarchar] (50) NULL ,
[Message] [nvarchar] (250) NULL ,
[DateEntered] [datetime] NULL
) ON [PRIMARY]
ALTER TABLE {databaseOwner}[YourCompany_GuestBook] ADD
CONSTRAINT [PK_{objectQualifier}YourCompany_GuestBook] PRIMARY KEY
CLUSTERED
(
[ID]
) ON [PRIMARY]
Select the "Run as Script" box and click "Execute".
|
 |
Optionally,
you can
create the table in the table designer in Visual Web Developer.
in the
SERVER EXPLORER, right-click on TABLES and select "Add New Table".
|
 |
|
Design the table in the table designer using the graphic on the right.
Make the "ID" Colum an "identity" column and also set it as the primary
key. save it as "YourCompany_GuestBook". Ensure
that the table has the same "ownership" (for example "dbo") that the
other DotNetNuke tables have. |
 |
Create The Stored Procedures
Log into the DotNetNuke website as HOST (if you are not already
logged in as HOST) and from the HOST menu select SQL. |
|
Paste the following script in the box:
CREATE
PROCEDURE
{databaseOwner}[{objectQualifier}YourCompany_GuestBook_Delete]
(
@ID int
)
AS
DELETE
FROM {objectQualifier}YourCompany_GuestBook
WHERE
(ID
= @ID)
RETURN
GO
CREATE
PROCEDURE
{databaseOwner}[{objectQualifier}YourCompany_GuestBook_GetAll]
(
@ModuleID int
)
AS
SELECT ID,
ModuleID,
Name,
Email,
Message,
DateEntered
FROM
{objectQualifier}YourCompany_GuestBook
WHERE
(ModuleID
= @ModuleID)
order
by
DateEntered DESC
RETURN
GO
CREATE
PROCEDURE
{databaseOwner}[{objectQualifier}YourCompany_GuestBook_Insert]
(
@ModuleID int,
@Name nvarchar(50),
@Email nvarchar(50),
@Message nvarchar(250)
)
AS
INSERT
INTO {objectQualifier}YourCompany_GuestBook
(ModuleID,
Name,
Email,
Message,
DateEntered)
VALUES
(@ModuleID,@Name,@Email,@Message,getdate())
RETURN
GO
CREATE
PROCEDURE
{databaseOwner}[{objectQualifier}YourCompany_GuestBook_Update]
(
@ID int,
@Name nvarchar(50),
@Email nvarchar(50),
@Message nvarchar(250),
@DateEntered datetime
)
AS
UPDATE {objectQualifier}YourCompany_GuestBook
SET
Name
= @Name,
Email =
@Email,
Message
=
@Message,
DateEntered =
@DateEntered
WHERE
(ID
= @ID)
RETURN
GO
Select the "Run as Script" box and click "Execute".
|
 |
Verify that the stored procedures have been created
Switch back to Visual Studio and select VIEW from the toolbar
and "Server Explorer"
|
 |
|
On the "Server Explorer" window, right-click on "Stored
Procedures" and select 'Refresh". |
 |
Scroll down the list of stored procedures and verify that these
stored procedures have been created:
- YourCompany_GuestBook_Delete
- YourCompany_GuestBook_GetAll
- YourCompany_GuestBook_Insert
- YourCompany_GuestBook_Update
|
 |
Alter the "SqlDataProvider.vb"
file
In Visual Studio, select VIEW from the toolbar and "Solution
Explorer" |
 |
|
In the Solution Explorer, expand the "GuestBook" directory
under the "App_code" folder and double-click on the "SqlDataprovider.vb"
file. |
 |
| Replace all the code with: |
|
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.ApplicationBlocks.Data
Imports DotNetNuke.Common.Utilities
Imports DotNetNuke.Framework.Providers
Namespace YourCompany.Modules.GuestBook
Public Class SqlDataProvider
Inherits DataProvider
Private Const ProviderType As String = "data"
Private Const ModuleQualifier As String = ""
Private _providerConfiguration As ProviderConfiguration =
ProviderConfiguration.GetProviderConfiguration(ProviderType)
Private _connectionString As String
Private _providerPath As String
Private _objectQualifier As String
Private _databaseOwner As String
' <summary>
' Constructs new SqlDataProvider instance
' </summary>
Public Sub New()
MyBase.New()
'Read the configuration specific information for this provider
Dim objProvider As Provider =
CType(_providerConfiguration.Providers(_providerConfiguration.DefaultProvider),
Provider)
' This code handles getting the connection string from either the
connectionString / appsetting section and uses the connectionstring
section by default if it exists.
' Get Connection string from web.config
_connectionString = Config.GetConnectionString()
' If above funtion does not return anything then connectionString
must be set in the dataprovider section.
If _connectionString = "" Then
' Use connection string specified in provider
_connectionString = objProvider.Attributes("connectionString")
End If
_objectQualifier = objProvider.Attributes("objectQualifier")
If ((_objectQualifier <> "") _
AndAlso (_objectQualifier.EndsWith("_") = False)) Then
_objectQualifier = (_objectQualifier + "_")
End If
_databaseOwner = objProvider.Attributes("databaseOwner")
If ((_databaseOwner <> "") _
AndAlso (_databaseOwner.EndsWith(".") = False)) Then
_databaseOwner = (_databaseOwner + ".")
End If
End Sub
' <summary>
' Gets and sets the connection string
' </summary>
Public ReadOnly Property ConnectionString() As String
Get
Return _connectionString
End Get
End Property
' <summary>
' Gets and sets the Provider path
' </summary>
Public ReadOnly Property ProviderPath() As String
Get
Return _providerPath
End Get
End Property
' <summary>
' Gets and sets the Object qualifier
' </summary>
Public ReadOnly Property ObjectQualifier() As String
Get
Return _objectQualifier
End Get
End Property
' <summary>
' Gets and sets the database ownere
' </summary>
Public ReadOnly Property DatabaseOwner() As String
Get
Return _databaseOwner
End Get
End Property
'
-----------------------------------------------------------------------------
' <summary>
' Gets the fully qualified name of the stored procedure
' </summary>
' <param name="name">The name of the stored procedure</param>
' <returns>The fully qualified name</returns>
'
-----------------------------------------------------------------------------
Private Function GetFullyQualifiedName(ByVal name As String) As
String
Return (DatabaseOwner _
+ (ObjectQualifier _
+ (ModuleQualifier + name)))
End Function
'
-----------------------------------------------------------------------------
' <summary>
' Gets the value for the field or DbNull if field has "null" value
' </summary>
' <param name="Field">The field to evaluate</param>
' <returns></returns>
'
-----------------------------------------------------------------------------
Private Function GetNull(ByVal Field As Object) As Object
Return Null.GetNull(Field, DBNull.Value)
End Function
Public Overrides Sub YourCompany_GuestBook_Insert(ByVal ModuleId As
Integer, ByVal Name As String, ByVal Email As String, ByVal Message
As String)
SqlHelper.ExecuteNonQuery(ConnectionString,
GetFullyQualifiedName("YourCompany_GuestBook_Insert"), ModuleId,
Name, Email, Message)
End Sub
Public Overrides Sub YourCompany_GuestBook_Delete(ByVal ID As
Integer)
SqlHelper.ExecuteNonQuery(ConnectionString,
GetFullyQualifiedName("YourCompany_GuestBook_Delete"), ID)
End Sub
Public Overrides Function YourCompany_GuestBook_GetAll(ByVal
ModuleId As Integer) As IDataReader
Return CType(SqlHelper.ExecuteReader(ConnectionString,
GetFullyQualifiedName("YourCompany_GuestBook_GetAll"), ModuleId),
IDataReader)
End Function
Public Overrides Sub YourCompany_GuestBook_Update(ByVal ID As
Integer, ByVal Name As String, ByVal Email As String, ByVal Message
As String, ByVal DateEntered As DateTime)
SqlHelper.ExecuteNonQuery(ConnectionString,
GetFullyQualifiedName("YourCompany_GuestBook_Update"), ID, Name,
Email, Message, DateEntered)
End Sub
End Class
End Namespace
|
You will notice that you will see errors such as
"'YourCompany_GuestBook_Insert' cannot be declared 'Overrides'
because it does not override a sub in a base class"
This is because the "DataProvider.vb" must indicate the
methods that the "SqlDataProvider.vb" overrides. When we place
those methods in the "DataProvider.vb" file the errors will go
away. |
 |
| |
|
| |
|
Alter the "DataProvider.vb"
file
|
|
|
In the Solution Explorer, in the "GuestBook" directory
under the "App_code" folder, double-click on the "DataProvider.vb"
file. |
 |
| Replace all the code with: |
|
Imports
System
Imports DotNetNuke
Imports System.Data
Imports
DotNetNuke.Framework
Namespace
YourCompany.Modules.GuestBook
Public
MustInherit
Class
DataProvider
'
singleton reference to the instantiated object
Private
Shared objProvider
As DataProvider
=
Nothing
'
constructor
Shared
Sub New()
CreateProvider()
End
Sub
'
dynamically create provider
Private
Shared
Sub CreateProvider()
objProvider =
CType(Reflection.CreateObject("data",
"YourCompany.Modules.GuestBook",
""),
DataProvider)
End
Sub
'
return the provider
Public
Shared
Function Instance()
As
DataProvider
Return
objProvider
End
Function
Public
MustOverride
Sub
YourCompany_GuestBook_Insert(ByVal
ModuleId As
Integer,
ByVal
Name As
String,
ByVal
Email As
String,
ByVal
Message As
String)
Public
MustOverride
Function
YourCompany_GuestBook_GetAll(ByVal
ModuleId As
Integer)
As
IDataReader
Public
MustOverride
Sub
YourCompany_GuestBook_Update(ByVal
ID As
Integer,
ByVal
Name As
String,
ByVal
Email As
String,
ByVal
Message As
String,
ByVal
DateEntered As
DateTime)
Public
MustOverride
Sub
YourCompany_GuestBook_Delete(ByVal
ID As
Integer)
End
Class
End
Namespace
|
| Switch back to the "SqlDataprovider.vb" file and notice all
the error messages are gone.
Now would be a good time to save (Ctrl+Shift+S). |
 |
Review
-
Data Access Layer (DAL)
(Done)
- Business Logic Layer (BLL)
- Presentation Layer (UI)
We are done with the Data Access Layer. We will now program the
Business Logic Layer. The Business Logic layer will go fast because
it is only 2 files, a simple class file to hold our data and a
"controller" file that will fill the class file with data as well as
handling inserting and deleting data. |
|
| |
|
| |
|
|
|
|
|
|
|
| |
|
| |
|
| |
|
