The DAL vs. The DAL+
Using the normal DotNetNuke Data Access Layer (DAL) design, we would create a database provider class (Concrete Provider) that communicated with the database and overrode methods in an abstract class (Abstract Data Provider). The abstract class sits between the concrete provider class and the Business Logic Layer (Controller Class). This would have allowed us to substitute an alternate database provider class to communicate with other databases.
The DAL+ does not require an Abstract Data Provider and it does not require that you code a custom Concrete Provider. The DAL+ is a subset of the DAL. It is comprised of 4 methods:
- ExecuteNonQuery - Used to execute a stored procedure that will not return a value.
- ExecuteReader - Used to execute a stored procedure that will return multiple records.
- ExecuteScalar - Used to execute a stored procedure that will return a single value.
- ExecuteSQL - Used to execute a sql statement.
However, unlike the DAL, the DAL+ is not 100% portable to other data sources. For example, if a module is developed using the DAL+ that retrieves data using one stored procedure, an alternate database must be able to perform the exact same functionality using only one stored procedure. In some cases this is not possible due to differences in databases.
Converting the Survey Module to use the DAL+
As a demonstration of the DAL+, the Survey module (that uses the traditional DAL) will be converted to use the DAL+ (you can download the original C# Version of the Survey Module and the VB Version of the Survey Module).
To replace the DAL layer, we only need to alter the files that reside in the App_Code directory.
Delete the highlighted files above so that only the following files remain:
Next, open the SurveyController.vb and SurveyOptionController.vb (or SurveyController.cs and SurveyOptionController.cs if you're using the C# version) and replace the GetSurveys, GetSurvey, DeleteSurvey, AddSurvey, UpdateSurvey, GetSurveyOptions, DeleteSurveyOption, AddSurveyOption, UpdateSurveyOption, and AddSurveyResult methods with the following code:
VB.NET
SurveyController.vb:
Public
Shared Function GetSurveys(ByVal ModuleId As Integer) As List(Of SurveyInfo)
Dim SurveyInfolist As List(Of SurveyInfo) = New List(Of SurveyInfo)
Using dr As IDataReader = DotNetNuke.Data.DataProvider.Instance().ExecuteReader("GetSurveys", ModuleId)
While dr.Read
Dim SurveyInfo As SurveyInfo = New SurveyInfo
SurveyInfo.SurveyId = Convert.ToInt32(dr("SurveyId"))
SurveyInfo.Question = Convert.ToString(dr("Question"))
SurveyInfo.OptionType = Convert.ToString(dr("OptionType"))
SurveyInfo.ViewOrder = Convert.ToInt32(ConvertNullInteger(dr("ViewOrder")))
SurveyInfo.CreatedByUser = Convert.ToInt32(dr("CreatedByUser"))
SurveyInfo.CreatedDate = Convert.ToDateTime(dr("CreatedDate"))
SurveyInfolist.Add(SurveyInfo)
End While
End Using
Return SurveyInfolist
End Function
Public Shared Function GetSurvey(ByVal SurveyID As Integer, ByVal ModuleId As Integer) As SurveyInfo
Dim SurveyInfo As SurveyInfo = New SurveyInfo
Using dr As IDataReader = DotNetNuke.Data.DataProvider.Instance().ExecuteReader("GetSurvey", SurveyID, ModuleId)
While dr.Read
SurveyInfo.SurveyId = Convert.ToInt32(dr("SurveyId"))
SurveyInfo.ModuleId = Convert.ToInt32(dr("ModuleID"))
SurveyInfo.Question = Convert.ToString(dr("Question"))
SurveyInfo.OptionType = Convert.ToString(dr("OptionType"))
SurveyInfo.ViewOrder = Convert.ToInt32(ConvertNullInteger(dr("ViewOrder")))
SurveyInfo.Votes = Convert.ToInt32(ConvertNullInteger(dr("Votes")))
SurveyInfo.CreatedByUser = Convert.ToInt32(dr("CreatedByUser"))
SurveyInfo.CreatedDate = Convert.ToDateTime(dr("CreatedDate"))
End While
End Using
Return SurveyInfo
End Function
Public Shared Sub DeleteSurvey(ByVal objSurvey As SurveyInfo)
DotNetNuke.Data.DataProvider.Instance().ExecuteNonQuery("DeleteSurvey", objSurvey.SurveyId, objSurvey.ModuleId)
End Sub
Public Shared Function AddSurvey(ByVal objSurvey As SurveyInfo) As Integer
Return CType(DotNetNuke.Data.DataProvider.Instance().ExecuteScalar("AddSurvey", objSurvey.ModuleId, objSurvey.Question, GetNull(objSurvey.ViewOrder), objSurvey.OptionType, objSurvey.CreatedByUser), Integer)
End Function
Public Shared Sub UpdateSurvey(ByVal objSurvey As SurveyInfo)
DotNetNuke.Data.DataProvider.Instance().ExecuteNonQuery("UpdateSurvey", objSurvey.SurveyId, objSurvey.Question, GetNull(objSurvey.ViewOrder), objSurvey.OptionType, objSurvey.CreatedByUser, objSurvey.ModuleId)
End Sub
Public Shared Function GetNull(ByVal Field As Object) As Object
Return Null.GetNull(Field, DBNull.Value)
End Function
SurveyOptionController.vb:
Public Shared Function GetSurveyOptions(ByVal SurveyId As Integer) As List(Of SurveyOptionInfo)
Dim SurveyOptionInfolist As List(Of SurveyOptionInfo) = New List(Of SurveyOptionInfo)
Using dr As IDataReader = DotNetNuke.Data.DataProvider.Instance().ExecuteReader("GetSurveyOptions", SurveyId)
While dr.Read
Dim SurveyOptionInfo As SurveyOptionInfo = New SurveyOptionInfo
SurveyOptionInfo.SurveyOptionId = Convert.ToInt32(dr("SurveyOptionID"))
SurveyOptionInfo.OptionName = Convert.ToString(dr("OptionName"))
SurveyOptionInfo.IsCorrect = Convert.ToString(dr("IsCorrect"))
SurveyOptionInfo.Votes = Convert.ToInt32(SurveyController.ConvertNullInteger(dr("Votes")))
SurveyOptionInfo.ViewOrder = Convert.ToInt32(SurveyController.ConvertNullInteger(dr("ViewOrder")))
SurveyOptionInfolist.Add(SurveyOptionInfo)
End While
End Using
Return SurveyOptionInfolist
End Function
Public Shared Sub DeleteSurveyOption(ByVal objSurveyOption As SurveyOptionInfo)
DotNetNuke.Data.DataProvider.Instance().ExecuteNonQuery("DeleteSurveyOption", objSurveyOption.SurveyOptionId)
End Sub
Public Shared Function AddSurveyOption(ByVal objSurveyOption As SurveyOptionInfo) As Integer
Return CType(DotNetNuke.Data.DataProvider.Instance().ExecuteScalar("AddSurveyOption", objSurveyOption.SurveyId, objSurveyOption.OptionName, SurveyController.GetNull(objSurveyOption.ViewOrder), objSurveyOption.IsCorrect), Integer)
End Function
Public Shared Sub UpdateSurveyOption(ByVal objSurveyOption As SurveyOptionInfo)
DotNetNuke.Data.DataProvider.Instance().ExecuteNonQuery("UpdateSurveyOption", objSurveyOption.SurveyOptionId, objSurveyOption.OptionName, SurveyController.GetNull(objSurveyOption.ViewOrder), objSurveyOption.IsCorrect)
End Sub
Public Shared Sub AddSurveyResult(ByVal objSurveyOption As SurveyOptionInfo, ByVal UserID As Integer)
DotNetNuke.Data.DataProvider.Instance().ExecuteNonQuery("AddSurveyResult", objSurveyOption.SurveyOptionId, UserID)
End Sub
C#
SurveyController.cs:
static public List<SurveyInfo> GetSurveys(int ModuleId)
{
List<SurveyInfo> SurveyInfolist = new List<SurveyInfo>();
using (IDataReader dr = DotNetNuke.Data.DataProvider.Instance().ExecuteReader("GetSurveys", ModuleId))
{
while (dr.Read())
{
SurveyInfo colSurveyInfo = new SurveyInfo();
colSurveyInfo.SurveyId = Convert.ToInt32(dr["SurveyId"]);
colSurveyInfo.Question = Convert.ToString(dr["Question"]);
colSurveyInfo.OptionType = Convert.ToString(dr["OptionType"]);
colSurveyInfo.ViewOrder = Convert.ToInt32(ConvertNullInteger(dr["ViewOrder"]));
colSurveyInfo.CreatedByUser = Convert.ToInt32(dr["CreatedByUser"]);
colSurveyInfo.CreatedDate = Convert.ToDateTime(dr["CreatedDate"]);
SurveyInfolist.Add(colSurveyInfo);
}
}
return SurveyInfolist;
}
static public SurveyInfo GetSurvey(int SurveyID, int ModuleId)
{
SurveyInfo colSurveyInfo = new SurveyInfo();
using (IDataReader dr = DotNetNuke.Data.DataProvider.Instance().ExecuteReader("GetSurvey",SurveyID, ModuleId))
{
while (dr.Read())
{
colSurveyInfo.SurveyId = Convert.ToInt32(dr["SurveyId"]);
colSurveyInfo.ModuleId = Convert.ToInt32(dr["ModuleID"]);
colSurveyInfo.Question = Convert.ToString(dr["Question"]);
colSurveyInfo.OptionType = Convert.ToString(dr["OptionType"]);
colSurveyInfo.ViewOrder = Convert.ToInt32(ConvertNullInteger(dr["ViewOrder"]));
colSurveyInfo.Votes = Convert.ToInt32(ConvertNullInteger(dr["Votes"]));
colSurveyInfo.CreatedByUser = Convert.ToInt32(dr["CreatedByUser"]);
colSurveyInfo.CreatedDate = Convert.ToDateTime(dr["CreatedDate"]);
}
}
return colSurveyInfo;
}
public static void DeleteSurvey(SurveyInfo objSurvey)
{
DotNetNuke.Data.DataProvider.Instance().ExecuteNonQuery("DeleteSurvey", objSurvey.SurveyId, objSurvey.ModuleId);
}
public static int AddSurvey(SurveyInfo objSurvey)
{
return (Convert.ToInt32(DotNetNuke.Data.DataProvider.Instance().ExecuteScalar("AddSurvey", objSurvey.ModuleId, objSurvey.Question, GetNull(objSurvey.ViewOrder), objSurvey.OptionType, objSurvey.CreatedByUser)));
}
public static void UpdateSurvey(SurveyInfo objSurvey)
{
DotNetNuke.Data.DataProvider.Instance().ExecuteNonQuery("UpdateSurvey", objSurvey.SurveyId, objSurvey.Question, GetNull(objSurvey.ViewOrder), objSurvey.OptionType, objSurvey.CreatedByUser, objSurvey.ModuleId);
}
public static object GetNull(object Field)
{
return DotNetNuke.Common.Utilities.Null.GetNull(Field, DBNull.Value);
}
SurveyOptionController.cs:
public
class SurveyOptionController
{
static public List<SurveyOptionInfo> GetSurveyOptions(int SurveyId)
{
List<SurveyOptionInfo> SurveyOptionInfolist = new List<SurveyOptionInfo>();
using (IDataReader dr = DotNetNuke.Data.DataProvider.Instance().ExecuteReader("GetSurveyOptions",SurveyId))
{
while (dr.Read())
{
SurveyOptionInfo colSurveyOptionInfo = new SurveyOptionInfo();
colSurveyOptionInfo.SurveyOptionId = Convert.ToInt32(dr["SurveyOptionID"]);
colSurveyOptionInfo.OptionName = Convert.ToString(dr["OptionName"]);
colSurveyOptionInfo.IsCorrect = Convert.ToBoolean(dr["IsCorrect"]);
colSurveyOptionInfo.Votes = Convert.ToInt32(SurveyController.ConvertNullInteger(dr["Votes"]));
colSurveyOptionInfo.ViewOrder = Convert.ToInt32(SurveyController.ConvertNullInteger(dr["ViewOrder"]));
SurveyOptionInfolist.Add(colSurveyOptionInfo);
}
}
return SurveyOptionInfolist;
}
public static void DeleteSurveyOption(SurveyOptionInfo objSurveyOption)
{
DotNetNuke.Data.DataProvider.Instance().ExecuteNonQuery("DeleteSurveyOption",objSurveyOption.SurveyOptionId);
}
public static int AddSurveyOption(SurveyOptionInfo objSurveyOption)
{
return (Convert.ToInt32(DotNetNuke.Data.DataProvider.Instance().ExecuteScalar("AddSurveyOption", objSurveyOption.SurveyId, objSurveyOption.OptionName, SurveyController.GetNull(objSurveyOption.ViewOrder), objSurveyOption.IsCorrect)));
}
public static void UpdateSurveyOption(SurveyOptionInfo objSurveyOption)
{
DotNetNuke.Data.DataProvider.Instance().ExecuteNonQuery("UpdateSurveyOption", objSurveyOption.SurveyOptionId, objSurveyOption.OptionName, SurveyController.GetNull(objSurveyOption.ViewOrder), objSurveyOption.IsCorrect);
}
public static void AddSurveyResult(SurveyOptionInfo objSurveyOption, int UserID)
{
DotNetNuke.Data.DataProvider.Instance().ExecuteNonQuery("AddSurveyResult", objSurveyOption.SurveyOptionId, UserID);
}
Essentially we are replacing code such as this:
DataProvider.Instance().GetSurveys(ModuleId)
with this:
DotNetNuke.Data.DataProvider.Instance().ExecuteReader("GetSurveys", ModuleId)
In doing so we are able to eliminate the need to create an abstract provider and a concrete provider. This saves hundreds of lines of code and potentially hours of development.
In addition, the DAL+ allows you to connect to the database with a single line of code. You do not have to set the database connection parameters yourself.
You can download the complete DAL+ C# Version of the Survey Module and the VB Version of the Survey Module