DotNetNuke Modules – Data Access Layer

tobj-077 On Tuesday, we started working on the code for our Data Access Layer by having CodeSmith generate some classes for us. Today, we are going to generate the DataSets we need and modify the controller classes to use them. At the end, you will have a DataLayer and Business Logic Layer that will allow you to start coding your presentation layer using the familiar ASP.NET 2.0 model.

So the next step in our creation of the Data Access Layer (DAL) is to create our DataSet(s). This should be rather trivial. If you only have a few tables that you will be using, you can place all of your DataTables in one DataSet. But for large projects, I strongly recommend breaking things up so that you have as few tables in a DataSet as possible. It just makes things easier to maintain. I find that one DataTable per DataSet is the most practical. But if you need the entity relationships between tables, you might need to put more than one DataTable in a DataSet.

So add your DataSet into the module directory under app_code. If you have not already established a connection to your DotNetNuke database in server explorer, do that now. Next, select the table or tables you want in your DataSet (you can multi-select the tables from the server explorer) and drag and drop them into the DataSet design surface. By default, this will generate the DataTable and the associated TableAdapter. Since we will not be using the TableAdapter, you can delete it by clicking on the TableAdapter name and pressing the Delete key. Make sure you only delete the TableAdapter and not the DataTable.

Now we are almost done. All we need to do is to fix up the controllers that we generated at the beginning of the week.

You will notice that your controllers have several methods in them. Everything that returns an ArrayList we are going to modify to return the DataTable. Everything that returns an Info object we are going to change to return the typed DataRow. Similarly, anything that takes an Info object as a parameter will be replaced to accept the DataRow.

There are two advantages to going through all this trouble. First, the DataTable is much more strongly typed than the ArrayList. So we are much less likely to make a mistake casting the object in the ArrayList to something it is not. Second, many of the cool new databinding features that showed up in ASP.NET 2.0 ONLY work with DataSets. Automatic sorting of GridView columns is one such feature. So if you want your application to take advantage of these you’ll want to make these changes.

So using the HtmlText table as an example, the CodeSmith wizard would have created a Controller class that looks like:

public HtmlTextInfo Get(int moduleID)
{
    return (HtmlTextInfo)DotNetNuke.Common.Utilities.CBO.FillObject(
        DataProvider.Instance().GetHtmlText(moduleID),
        typeof(HtmlTextInfo));
}

public ArrayList List()
{
    return DotNetNuke.Common.Utilities.CBO.FillCollection(
        DataProvider.Instance().ListHtmlText(),
        typeof(HtmlTextInfo));
}

public ArrayList GetByModules(int moduleID)
{
    return DotNetNuke.Common.Utilities.CBO.FillCollection(
        DataProvider.Instance().GetHtmlTextByModules(moduleID),
        typeof(HtmlTextInfo));
}

public int Add(HtmlTextInfo objHtmlText)
{
    return (int)DataProvider.Instance().AddHtmlText(
        objHtmlText.DesktopHtml, objHtmlText.DesktopSummary,
        objHtmlText.CreatedByUser, objHtmlText.CreatedDate);
}

public void Update(HtmlTextInfo objHtmlText)
{
    DataProvider.Instance().UpdateHtmlText(
        objHtmlText.ModuleID, objHtmlText.DesktopHtml,
        objHtmlText.DesktopSummary, objHtmlText.CreatedByUser,
        objHtmlText.CreatedDate);
}

public void Delete(int moduleID)
{
    DataProvider.Instance().DeleteHtmlText(moduleID);
}

We want to change these to look like:

public DataSetDMBSample.HtmlTextRow Get(int moduleID)
{
    DataSetDMBSample.HtmlTextDataTable table =
        new DataSetDMBSample.HtmlTextDataTable();
    table.Load(DataProvider.Instance().GetHtmlText(moduleID));
    if (table.Count > 0)
        return table[0];
    return null;
}

public DataSetDMBSample.HtmlTextDataTable List()
{
    DataSetDMBSample.HtmlTextDataTable table =
        new DataSetDMBSample.HtmlTextDataTable();
    table.Load(DataProvider.Instance().ListHtmlText());
    return table;
}

public DataSetDMBSample.HtmlTextDataTable GetByModules(int moduleID)
{
    DataSetDMBSample.HtmlTextDataTable table =
        new DataSetDMBSample.HtmlTextDataTable();
    table.Load(DataProvider.Instance().GetHtmlTextByModules(moduleID));
    return table;
}

public int Add(DataSetDMBSample.HtmlTextRow objHtmlText)
{
    return (int)DataProvider.Instance().AddHtmlText(
        objHtmlText.DesktopHtml, objHtmlText.DesktopSummary,
        objHtmlText.CreatedByUser, objHtmlText.CreatedDate);
}

public void Update(DataSetDMBSample.HtmlTextRow objHtmlText)
{
    DataProvider.Instance().UpdateHtmlText(
        objHtmlText.ModuleID, objHtmlText.DesktopHtml,
        objHtmlText.DesktopSummary, objHtmlText.CreatedByUser,
        objHtmlText.CreatedDate);
}

public void Delete(int moduleID)
{
    DataProvider.Instance().DeleteHtmlText(moduleID);
}

Here’s what we’ve done:

  • Replace all ArrayList with DataSetDMBSample.HtmlTextDataTable
  • Replace all HtmlTextInfo with DataSetDMBSample.HtmlTextRow
  • Create a table object and load it with the IDataReader that is returned by the DataProvider.Instance().Get… call for things that return Tables or a Row
  • For our Get method (the only thing in this example that returns a row in this example) check to make sure we have a row to return and return it, otherwise, return null.

Here’s what hasn’t changed:

  • The Delete() method stays the same.
  • The Add and Update methods only have the parameter type changed. Nothing else changes in these two functions.
  • The DataProvider.Instance().Get… functions are the same as in the original code.

I’m sure some really smart person could open up the CST files that CodeSmith uses and change them to generate the above code directly. But since this is something I only use once a year (maybe), it hasn’t been worth the effort to me to figure that part out. It only takes about 3 minutes to make these changes once you get the hang of it.

 

Other post in DotNetNuke - Module Development

Related Post

  • DotNetNuke Modules – Creating Base ModulesDotNetNuke Modules – Creating Base Modules Now that we have DotNetNuke installed into Visual Studio we can go ahead and create our first modules. Actually, creating the modules is pretty simple. But it is even easier to do it wrong, which...
  • DotNetNuke Modules – Foundational ConceptsDotNetNuke Modules – Foundational Concepts There are two design patterns that DotNetNuke relies on heavily, not just in the core code, but in any module you might develop. The first is a three tiered architecture, or more appropriately a...
  • DotNetNuke – The Data Layer – Installing CodeSmithDotNetNuke – The Data Layer – Installing CodeSmith There are other controls, and I'm sure I could go on talking about them for weeks yet, but we need to move on. Since most modules you build are going to need at least one table behind them, and mos...
  • DotNetNuke – Modules – Creating The TablesDotNetNuke – Modules – Creating The Tables On Tuesday we installed the free version of CodeSmith to help us create the database access code. Before we can create the access code, we need some tables in our system. Even if you think you know...
  • DotNetNuke – Data Access Layer AlternativeDotNetNuke – Data Access Layer Alternative Now that I've explained the standard way of creating a Data Access Layer (DAL) for DotNetNuke, we can address the alternative method of providing this same functionality.You see, the only reason f...