New Community Website

Ordinarily, you'd be at the right spot, but we've recently launched a brand new community website... For the community, by the community.

Yay... Take Me to the Community!

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:



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"))
    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


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")))
    End While
End Function

Shared Sub DeleteSurveyOption(ByVal objSurveyOption As SurveyOptionInfo)
    DotNetNuke.Data.DataProvider.Instance().ExecuteNonQuery("DeleteSurveyOption", objSurveyOption.SurveyOptionId)
End Sub

Shared Function AddSurveyOption(ByVal objSurveyOption As SurveyOptionInfo) As Integer
CType(DotNetNuke.Data.DataProvider.Instance().ExecuteScalar("AddSurveyOption", objSurveyOption.SurveyId, objSurveyOption.OptionName, SurveyController.GetNull(objSurveyOption.ViewOrder), objSurveyOption.IsCorrect), Integer)
End Function

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

Shared Sub AddSurveyResult(ByVal objSurveyOption As SurveyOptionInfo, ByVal UserID As Integer)
    DotNetNuke.Data.DataProvider.Instance().ExecuteNonQuery("AddSurveyResult", objSurveyOption.SurveyOptionId, UserID)
End Sub



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"]);
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)
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)
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);


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"]));
    return SurveyOptionInfolist;

public static void DeleteSurveyOption(SurveyOptionInfo objSurveyOption)

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:


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


Comment Form

Only registered users may post comments.


Aderson Oliveira (22)
Alec Whittington (11)
Alessandra Davies (3)
Alex Shirley (10)
Andrew Hoefling (3)
Andrew Nurse (30)
Andy Tryba (1)
Anthony Glenwright (5)
Antonio Chagoury (28)
Ash Prasad (37)
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 (213)
Chris Paterra (55)
Clint Patterson (108)
Cuong Dang (21)
Daniel Bartholomew (2)
Daniel Mettler (181)
Daniel Valadas (48)
Dave Buckner (2)
David Poindexter (12)
David Rodriguez (3)
Dennis Shiao (1)
Doug Howell (11)
Erik van Ballegoij (30)
Ernst Peter Tamminga (80)
Francisco Perez Andres (17)
Geoff Barlow (12)
George Alatrash (12)
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)
Kelly Ford (4)
Ken Grierson (10)
Kevin Schreiner (6)
Leigh Pointer (31)
Lorraine Young (60)
Malik Khan (1)
Matt Rutledge (2)
Matthias Schlomann (16)
Mauricio Márquez (5)
Michael Doxsey (7)
Michael Tobisch (3)
Michael Washington (202)
Miguel Gatmaytan (3)
Mike Horton (19)
Mitchel Sellers (40)
Nathan Rover (3)
Navin V Nagiah (14)
Néstor Sánchez (31)
Nik Kalyani (14)
Oliver Hine (1)
Patricio F. Salinas (1)
Patrick Ryan (1)
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)
Sacha Trauwaen (1)
Salar Golestanian (4)
Sanjay Mehrotra (9)
Scott McCulloch (1)
Scott Schlesier (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)
Steven Fisher (1)
Timo Breumelhof (24)
Tony Henrich (3)
Torsten Weggen (3)
Tycho de Waard (4)
Vicenç Masanas (27)
Vincent Nguyen (3)
Vitaly Kozadayev (6)
Will Morgenweck (40)
Will Strohl (180)
William Severance (5)
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out