On Wednesday I wrote about how the current DotNetNuke documentation does a poor job of bridging the gap between writing very basic modules and writing very functional modules: The Basics Are Just Not Enough When Implementing A DotNetNuke Module.
Today I am starting a series of articles aimed at helping you begin writing more functional modules. This is Part I of the Making Your DotNetNuke Module Do More For You series.
In this article I will demonstrate how you can begin to use your DotNetNuke module’s Data Access Layer (DAL) to achieve certain functionality when you need it. After reading this you should be able to add code to your DataProvider class that will allow you to retrieve output parameter values from a stored procedure and then pass those values up into your business logic layer.
Before you begin, make certain that you have your DotNetNuke basics down. At a bare minimum you need to understand how the presentation, business logic, and data layers are divided within the DotNetNuke architecture. It is essential that you be able to identify which files in your code represent each layer of the architecture. This information is covered in the third section of Michael Washington’s module development guide. You can download that document from the DotNetNuke website.
Let’s begin.
The Basic DAL / BLL Functionality
Assuming you follow the prescribed methodologies (and not using DAL+, an article for another day) your module likely executes almost identically to this:
Your presentation layer code-behind gets data by calling one of your controller’s methods:
1 string criteria = SearcTextBox.Text.Trim();
2 JimmyHaHaController jhhController = new JimmyHaHaController();
3 List<JimmyHaHaInfo> searchResults =
4 jhhController.SearchJimmyHaHas(criteria);
5 SearchResultsDataList.DataSource = searchResults;
6 SearchResultsDataList.DataBind();
Inside your controller’s method you use the CBO.FillCollection method to convert your database query result records into a List of objects that represent your data in a meaningful way. As a parameter to the FillCollection method, you pass the results of a query you call on your DataProvider.
1 public List<JimmyHaHaInfo> SearchJimmyHaHas(string Criteria)
2 {
3 return CBO.FillCollection<JimmyHaHaInfo>(
4 DataProvider.Instance().SearchJimmyHaHas(Criteria));
5 }
Finally, inside the method you called on your DataProvider, you execute some code that performs the database query and returns a DataReader.
1 public override IDataReader SearchJimmyHaHas(string Criteria)
2 {
3 return (IDataReader)SqlHelper.ExecuteReader(
4 ConnectionString,
5 GetFullyQualifiedName("SearchJimmyHaHas"),
6 Criteria);
7 }
This architecture is tried and proven, and so I recommend you stick with it unless you have a good reason not to.
But now let’s consider a real-life module requirement that you might run into.
You need your module to query a database and search for records in a table that match certain criteria. You will present the search results on a Data List control. However, for efficiency reasons, you only want to pull 10 records from the database at once to show on a “page” of results. In the same query you will also need to retrieve a scalar value, the total number of search results.
In the presentation layer you will use this combination to efficiently implement paging on your Data List control:
I won’t get into the minutiae of stored procedures, but here is an example that does what we described above:
1 ALTER PROCEDURE dbo.SearchJimmyHaHas
2 (
3 @Criteria varchar(200),
4 @PageIndex int,
5 @NumRows int,
6 @ResultCount int output
7 )
8
9 AS
10
11 BEGIN
12
13 /* GET TOTAL NUMBER OF SEARCH RESULTS */
14 SELECT @ResultCount=(SELECT COUNT(*)
15 FROM haha JOIN FREETEXTTABLE(haha, *, @Criteria)ftt
16 on haha.id=ftt.[KEY] )
17
18 Declare @StartRowIndex INT;
19 set @StartRowIndex = (@PageIndex * @NumRows) + 1;
20
21 With ItemMatches as (
22
23 SELECT
24 ROW_NUMBER() OVER (ORDER BY ftt.rank DESC) as Row,
25 ftt.rank,
26 haha.id,
27 haha.title
28 from haha
29 join freetexttable(haha, *, @Criteria) ftt
30 on haha.id=ftt.[KEY]
31 )
32
33 SELECT
34 rank,
35 id,
36 title
37 FROM ItemMatches
38 WHERE Row between
39 @StartRowIndex and @StartRowIndex+@NumRows-1
40
41 END
In a nutshell, the stored procedure takes four parameters, the first three as input parameters, and the final one as an output parameter. This final parameter gets set to the total number of search results. The stored procedure itself only returns at most the quantity of rows specified in the @NumRows parameter of the stored procedure.
Once you start inquiring about how to get the value of the output parameter, it becomes apparent that your current code doesn’t contain the facilities to accomplish this, meaning you’re going to have to start thinking about changing some code inside your DAL and BLL.
There are several approaches you can take here to acquire the value of the output parameter from the stored procedure. One appropriate solution might involve removing the CBO hydrator from your BLL, and implementing your own. This would give you full control over the traversal of data using the DataReader from your DataProvider.
Fortunately, you won’t need to take this route unless you are wanting to achieve other goals such as improving the performance of the hydrating process, which again is a topic for another day.
The approach we will take in this tutorial is to leave the existing data architecture in place. So let’s take a look at the two parts of code that we have to work with.
First we have the DataProvider method that calls the stored procedure and returns a SqlDataReader back to the controller object that called the method.
1 public override IDataReader SearchJimmyHaHas(string Criteria)
2 {
3 return (IDataReader)SqlHelper.ExecuteReader(
4 ConnectionString,
5 GetFullyQualifiedName("SearchJimmyHaHas"),
6 Criteria);
7 }
The results of this method get passed on to the second region of code that concerns us, the CBO.FillCollection() hydration method that is called inside our controller’s code. The FillCollection() method requires the List<> data type as a parameter. Thus we want to keep the same return type on our DataProvider’s method. All other code in our DataProvider method is fair game for changing. And nothing is stopping us from doing so.
Allow me to present you with one implementation of this method that solves the problem:
1 public override IDataReader SearchJimmyHaHas(
2 string SearchCriteria,
3 int PageIndex,
4 int NumRows,
5 out SqlParameter ResultParam)
6 {
7 SqlConnection connection =
8 new SqlConnection(ConnectionString);
9 SqlCommand cmd =
10 new SqlCommand("SearchJimmyHaHas", connection);
11 cmd.CommandType = CommandType.StoredProcedure;
12 cmd.Parameters.Add(
13 new SqlParameter("@Criteria",SearchCriteria));
14 cmd.Parameters.Add(
15 new SqlParameter("@PageIndex", PageIndex));
16 cmd.Parameters.Add(new SqlParameter("@NumRows", NumRows));
17 ResultParam = new SqlParameter();
18 ResultParam.ParameterName = "@ResultCount";
19 cmd.Parameters.Add(ResultParam);
20 cmd.Parameters["@ResultCount"].SqlDbType = SqlDbType.Int;
21 cmd.Parameters["@ResultCount"].Direction
22 = ParameterDirection.Output;
23 connection.Open();
24 return cmd.ExecuteReader();
25 }
Starting at the method signature, you see that I added a new parameter that uses the out keyword to make it a reference parameter. This will allow the value of the output parameter from the stored procedure to be accessed back inside the controller object.
Next you see that the call to the SqlHelper.ExecuteReader() method has been replaced by a few lines of code that allow us to explicitly configure the parameters that go into the SqlCommand object. Here we’ve taken control of the query details by implementing them ourself rather than letting the SqlHelper object do it for us.
Let me warn you that the steps above were difficult to get working correctly. There appears to be a bug in the way SqlCommand and SqlParameter interact with stored procedure output. In my experience you need to add the output parameter to the command first. After that you can configure the direction and db type. The other parameters can be added normally using one of the many convenient Parameter.Add overloads. So if you have problems, try mimicking the code I provided here.
Since we changed the parameters to the Data Provider method, this changes the way our controller code must call the method.
The method requires a SqlParameter object to be passed in by reference using the out keyword. So we create an instance of SqlParameter and do just that. Then we call the CBO.FillCollection object like usual. Instead of directly returning it, we save a reference to the results so that we can retrieve the stored procedure output parameter we care so much about.
A second caveat that I must mention here is that the stored procedure output parameter value must be accessed AFTER the FillCollection() method is executed. This is due to the way Readers function. When using a Reader object, the output parameter values from a stored procedure are not available until after every record in the query results has been traversed, and the Reader is closed. That is precicely what takes place inside the FillCollection() call. If you take a minute to think about it, it doesn’t even make sense to try to access the output value before at least calling your DataProvider method, because no data whatsoever is available until you do so. Just keep this in mind when you are modifying your own Data Access Layer.
The final thing you will notice is that I added and extra parameter to the BLL’s method to allow the returned value to be passed up into the presentation layer.
1 public List<JimmyHaHaInfo> SearchJimmyHaHas(
2 string SearchCriteria,
3 int PageIndex,
4 int NumRows,
5 out int ResultCount)
6 {
7 SqlParameter param = new SqlParameter();
8 List<JimmyHaHaInfo> list =
9 CBO.FillCollection<JimmyHaHaInfo>(
10 DataProvider.Instance().SearchJimmyHaHas(
11 SearchCriteria,
12 PageIndex,
13 NumRows,
14 out param));
15 ResultCount = (int)param.Value;
16 return list;
17 }
There are numerous ways to skin this cat, but I simply am keeping with the theme of using the reference parameter in this tutorial.
To Summarize
This is only one example of how you can start customizing parts of your modules’ BLL and DAL. With any luck the take-away points that you get from today’s article are these:
- Common-day module solutions have special requirements that necessitate that you modify the code that accesses your database. If you haven’t done so already, you should start becoming familiar with customizing your DataProvider code and your BLL code.
- There is always more than one way to accomplish what you need within your code. The more familiar you are with the functionality of DotNetNuke, the more likely it will be that you can accomplish your goals without having to re-write much of the functionality that the DotNetNuke core provides.
- For example, you can retrieve the values of stored procedure output parameters by making the small changes described above.
- Moreover, reference parameters can be used to pass data between the layers in your module.
You have been reading the first installment in my series on making your DotNetNuke module do more for you.
In the next installment I will be discussing how the DotNetNuke page can impact the functionality of your module, and how your module can modify parts of the page it lives on. I will see you then!
Update: Click here to read Part II: Common DotNetNuke Module Page Tasks