DotNetNuke® DAL+ (Dal Plus) - Survey Module in C# and VB.NET

Converting the DotNetNuke® Survey module to use the DAL+ (in VB and C#)

DotNetNuke DAL+

Also see:

DAL & DAL+
- Use DAL+ ExecuteSQL for truly rapid DotNetNuke® Module development
- Connecting to another database
IHydratable: Use the CBO again for your DotNetNuke® Module

The DAL vs. The DAL+


The DAL	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