DNN Community Blog

The Community Blog is a personal opinion of community members and by no means the official standpoint of DNN Corp or DNN Platform. This is a place to express personal thoughts about DNNPlatform, the community and its ecosystem. Do you have useful information that you would like to share with the DNN Community in a featured article or blog? If so, please contact .

The use of the Community Blog is covered by our Community Blog Guidelines - please read before commenting or posting.


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

DotNetNuke DAL+

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);
}

What Did We Just Do?

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

Comments

Comment Form

Only registered users may post comments.

NewsArchives


Aderson Oliveira (15)
Alec Whittington (11)
Alex Shirley (10)
Andrew Nurse (30)
Andy Tryba (1)
Anthony Glenwright (5)
Antonio Chagoury (28)
Ash Prasad (32)
Ben Schmidt (1)
Benjamin Hermann (25)
Benoit Sarton (9)
Beth Firebaugh (12)
Bill Walker (36)
Bob Kruger (5)
Bogdan Litescu (1)
Brian Dukes (2)
Brice Snow (1)
Bruce Chapman (20)
Bryan Andrews (1)
cathal connolly (55)
Charles Nurse (163)
Chris Hammond (209)
Chris Paterra (55)
Clinton Patterson (40)
Cuong Dang (21)
Daniel Bartholomew (2)
Daniel Mettler (154)
Dave Buckner (2)
David Poindexter (4)
David Rodriguez (3)
Doug Howell (11)
Erik van Ballegoij (30)
Ernst Peter Tamminga (74)
Geoff Barlow (10)
George Alatrash (6)
Gifford Watkins (3)
Gilles Le Pigocher (3)
Ian Robinson (7)
Israel Martinez (17)
Jan Blomquist (2)
Jan Jonas (3)
Jaspreet Bhatia (1)
Jenni Merrifield (6)
Joe Brinkman (274)
John Mitchell (1)
Jon Henning (14)
Jonathan Sheely (4)
Jordan Coopersmith (1)
Joseph Craig (2)
Kan Ma (1)
Keivan Beigi (3)
Ken Grierson (10)
Kevin Schreiner (6)
Leigh Pointer (31)
Lorraine Young (60)
Malik Khan (1)
Matthias Schlomann (15)
Mauricio Márquez (5)
Michael Doxsey (7)
Michael Tobisch (3)
Michael Washington (202)
Miguel Gatmaytan (3)
Mike Horton (19)
Mitchel Sellers (28)
Nathan Rover (3)
Navin V Nagiah (14)
Néstor Sánchez (31)
Nik Kalyani (14)
Peter Donker (54)
Philip Beadle (135)
Philipp Becker (4)
Richard Dumas (22)
Robert J Collins (5)
Roger Selwyn (8)
Ruben Lopez (1)
Ryan Martinez (1)
Salar Golestanian (4)
Sanjay Mehrotra (9)
Scott McCulloch (1)
Scott S (11)
Scott Wilkinson (3)
Scott Willhite (97)
Sebastian Leupold (80)
Shaun Walker (237)
Shawn Mehaffie (17)
Stefan Cullmann (12)
Stefan Kamphuis (12)
Steve Fabian (31)
Timo Breumelhof (24)
Tony Henrich (3)
Torsten Weggen (2)
Vicenç Masanas (27)
Vincent Nguyen (3)
Vitaly Kozadayev (6)
Will Morgenweck (40)
Will Strohl (165)
William Severance (5)

Content Layout

Subscribe to DNN Digest

DNN Digest is our monthly email newsletter. It highlights news and content from around the DNN ecosystem, such as new modules and themes, messages from leadership, blog posts and notable tweets. Keep your finger on the pulse of the ecosystem by subscribing.  


Copyright 2017 by DNN Corp Terms of Use Privacy
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out