Mule 4 Secure Properties

Every development project needs a way to have certain values change when an application is moved from environment to environment. Many teams opt for environment variables on the server. I’m not a fan of these, because they are “hidden”. Only a server admin can see that they exist, and what their values are. MuleSoft, Mule 4 has a method for using Secure Properties in your apps, you may want to consider using these.

The main advantage of Secure Properties is that they are easily maintained in the Runtime Manager. This means that they are easy to administrate by whomever has rights to deploy applications. Hence, the values for each environment can be part of your roll-out documentation. If the property values are sensitive, i.e. passwords, then your documentation can state that the values are to be retrieved by other means, but still show where they should be set.

Alright then, enough with the back story, let’s get started!

First thing we’ll need to do is to setup a YAML file that has the properties we want to use in our app. We can easily add to this list over time as needs arise. For this example, I’m going to call the file secure-config.yaml, but feel free to name it as you see fit.

Let’s get started

  1. Right click on your Mule project
  2. Select “New” -> “File”
  3. Navigate to your project and into the src\main\resources folder
  4. Enter the file name you’d like with a .yaml extension
  5. Click “Finish”
Adding new file in MuleSoft Anypoint Studio

Open your new file and add your new properties, you can select to create them in a hierarchy, or not. As always, I recommend organizing your thoughts, as you don’t know how many properties you may eventually have. It could get difficult to find the one you want in the future if there are dozens.

Sample Secure Properties YAML file

Once this is saved, you need to declare the properties you’re using that you wish to set in the Runtime Manager. In order to do this, you’ll need to edit your mule-artifact.json file in the root of your project. We’ll be adding a new line to declare the properties:

mule-artifact.json file with new line added

Make sure to add a comma before your new line to keep the integrity of the properties in this file. Notice how the property that I had created in the config-secure.yaml file under the “defaultAppSettings” is depicted here with a period. This is the same way you’ll use it in the Runtime Manager and any documentation that you create.

After saving your changes to the mule-artifact.json file, there is one more step. We need to add this new config-secure.yaml file as a known configuration file for your project.

  1. With any of your flow files open from your “src/main/mule” folder, click on the “Global Elements” tab.
  2. Click on “Create”
  3. Now Click on “Global Configurations” -> “Configuration properties”
  4. Click “OK”
  5. In the “File” box, select your new config-secure.yaml file. You can use the ellipses to make sure you have the path correct.
  6. Click “OK”
  7. Save all your changes
Showing the value of the Configuration properties once you’re done

NOTE: Make sure and save all your changes in Anypoint Studio.

Now you can use these properties as you would any other properties in a .yaml file.

Example in a Set Variable showing a secure property as the Value

For this example, I have a simple application that pulls my two config settings and saves them to variables. I then display the variables as JSON.

Using my properties that are fed by my config-secure.yaml file

Deploying an application is beyond the scope of this post, so I’ll assume you know how to do that.

After Deploying Anypoint Manager

After deploying, I run my application and view the following response message in my browser:

Now, back in Anypoint Manager – Runtime Manager, I hover over the name of my application and click on the little leaping arrow:

How to manage your application in Runtime Manager

Select “Manage Application” on the right side pane.

On this screen, look for the properties tab and click it.

Properties tab in Anypoint Manager – Runtime Manager

Here is where we can change the values of the properties we created in the config.secure.yaml file. By also adding them to the mule-artifact.json file, we have exposed them to this tab. So I’ll change the two values for my two sample properties, like so:

There are three things I want to mention about this screen.

  1. Make sure you click the save button after adding each property. If you add several and click save, I’ve had it wipe out all but the one I clicked.
  2. Note the key is exactly the same as the way that I access them from within my Mule application. Make sure and double check the names of your properties.
  3. You MUST click “Redeploy Application” for any of these change to not only take effect, but to be saved at all.

Click on “Redeploy Application”. Once it is started. I’ll refresh the application in my browser and see what we have:

Mule service JSON display after changing values on Properties tab

So now I include the settings for these properties in my deployment document and every time the application is run on a server, it picks up the values that are appropriate for that environment.

Happy Muling, and remember, don’t make an ass of yourself.

Duplicate Rows in an SQL Database

There is often a need to find rows that have duplicate values. You can use this query to find duplicate rows in an SQL database when they have duplicate value in a single column or multiple columns, depending on your need.

Here’s an example of a simple query:

SELECT firstname, COUNT(*) occurrences
FROM [users]
GROUP BY
	firstname
HAVING 
	COUNT(*) > 1

This will give you a simple list that shows you any duplicate firstname values in the users table and the count of how many duplicates there are.

So now you know that there are duplicates, how can you make use of this data? In order to do something with the data, you’ll need some more information, such as the primary key for the rows.

You can’t add that to the query columns since that would then make every row unique and they’d no longer show up in your resultset as duplicate. What you need to do is wrap this query into a subquery that then pulls these records out as a predicate.

You can read more about how the GROUP BY works on Microsoft’s site here:

https://docs.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql?view=sql-server-ver16

  SELECT * FROM [users] WHERE firstname IN (
	SELECT firstname FROM (
		SELECT firstname, COUNT(*) occurrences
		FROM [users]
		GROUP BY
			firstname
		HAVING 
			COUNT(*) > 1) a
	)

Your original duplicate query is buried in there from lines 3 to 8. We have to alias that query in order to use it as a resultset. I simply called it ‘a’ for this example. The second inner SELECT is needed to knock off the count column in order for it to work in the WHERE IN clause of the outer SELECT statement. This now gives us a list of very actionable data.

SQL Table Structure Compare

We ran into a problem at work where we have a table, along with a history table, or some other form of almost identical table where the definitions need to be in sync.

Here’s the scenario:

We have a table, say “clients”. There is a requirement that we track any changes to this table in a “clients_history” table. This could be implemented using a trigger, or just a secondary save in your repository using EF. The implementation is irrelevant.

A few months later, a developer is working a user story where they need to add a field to clients to track if the company doesn’t want sales people to call on the client any longer.

The developer adds a field to the clients table:

ALTER TABLE dbo.clients ADD
     isdeadbeat bit NULL

Now there are several things that will happen:

  • The developer will not know or remember to add this field to the client_history table
  • Depending on how the trigger is coded, it could raise a difficult to troubleshoot error trying to insert into the clients_history table
  • The new field data will not be saved in the clients_history table

How can we resolve this issue. Well, there are dozens of ways, but what’s the easiest?

If you deploy your Database changes from a TFS project, you can add a post deployment script that runs after every deployment. You probably do this already for inserting values into lookup tables etc.

We’ll add a new script and insert the following code:

DECLARE @Count INT;
SELECT @Count = COUNT(*) FROM (
SELECT name FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.clients_history')
EXCEPT 
SELECT name FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.clients')
) AS A

IF (@Count > 0)
BEGIN
	RAISERROR('There is a mismatch between two tables',18,-1);
	RETURN
END

GO

When we run just the SELECT statements with the EXCEPT, we see the there are differences identified:

If we run the entire script, it will raise and exception to let the person doing the deployment know that there is an issue that needs to be addressed. You’ll just add a new statement for each table pair that you want to check, as they are identified.

Now call this new script with your Script.PostDeployment.sql file.

Now whenever someone publishes this script, if there are unwanted differences between the two files, they will be noted.

NOTES:

  • The table that is most likely to have changes that need to be sync’d, in this case, the clients table, should be first in the EXCEPT statement.
  • If there is a field that you want to ignore for some reason, you must provide a where clause.

Look-up Engine Basics

Lookup engine design can shave hours of off any enhancement and support issue your involved in!

Almost every database enabled project that I’ve written in my career had a requirement for lookup values. Enumerations are great when needed to pass parameters between methods, but when you need to display values to users, I wanted something more flexible. There are several things that usually come up as requirements:

  1. I want to be able to use a list of values in drop downs
  2. I want the values to be categorized
  3. I want the values to be ordered by more than alphabetical
  4. I want the values to have alternate values that can be used for decisions and code conversions
  5. I want the values to be dynamic, so I can add and remove them at will
  6. I want to be able to deactivate values as apposed to deleting them
  7. I want to do all this without a software release
  8. I don’t want to deploy values by using Identity Insert into different environments
  9. I want a user interface to maintain the values
  10. I want to be able to store different types of values, strings, dates, integers etc.

What typically happens, is a table of values is created, often with a few records that are for a specific purpose. These values are solely maintained by the DBA.

This usually forces a developer to do one of two things:

  1. Use magic numbers
if (EmployeeTypeId == 1) {  }

2. Use textual value

if (EmployeeType == "Supervisor") {  }

In example one (1), using magic numbers is problematic. If there is ever a day that users can add values to this table, how do you guarantee that the unique Ids are synchronized between DEV, QA, UAT and Production?

In the second example (2), the business users will inevitably want to change the text value of an entry, thereby breaking code.

As an afterthought, a column will be added later to allow for an active (bit) flag of some sort.

Why should I care?

Not having a centralized lookup system means that the following things will occur:

  1. Items will be added to this table in DEV and then not propagated uniformly, causing a failed deployment to production. I’ve spent many late nights troubleshooting deployment issues that turned out to be missing lookup values in a table.
  2. Someone will put in a request to change a value in production and cause exceptions to be raised in the application. The development team is rarely consulted for changes like this.
  3. The DBA will be strapped with babysitting these tables that are strewn about the database.
  4. Lack of Foreign Keys will cause developers that are troubleshooting or enhancing to spend lots of time tracking down tables that have lookup values.
  5. Developers may assume there is no lookup table and hard code the values they see elsewhere in their new pages. Then future developers that find the table, will see that there is a mismatch and have to spend extra time to rectify the issue.

Whenever I see pain points like this, I usually try to think of a solution that could resolve known issues, and as a plus, be generic enough to be re-usable. If there isn’t a coordinated effort, your project could wind up with two or more solutions for providing an engine to accomplish this.

The advantages of a unified method are many:

  1. Support becomes easier, as all lists of values are handled the same way in code
  2. All lists of values are stored in the database in the same format
  3. An admin web page can easily be used to maintain all the lookup lists
  4. The engine can have special functions built in
  5. The engine and it’s abilities can be easily versioned
  6. The engine can be used to convert values

I devised a simple table structure to be used for look-ups. It has been enhanced a little over the years, but has remained essentially unchanged for the past decade or so. That’s right, this simple solution has been able to solve all the lookup needs over the years without modification!

First, let me explain what really adds the power to this structure. There are two columns that are more important than any others:

  1. lu_categories.catshortname
  2. lu_items.lucode

When a new [lu_category] record is added, the [catshortname] is a value that is used in any code to pull back the category. So my typical implementation is that once a category is created, the [catshortname] should never change. The ONLY reason to use the [lu_category].[id] should be in the maintenance user interface, but never anywhere else. This solves the problem of synchronizing this table between environments.

Also,When a new [lu_item] record is added, it must Foreign Key (FK) back to the [lu_category].[id] column. The [lu_item].[lucode] is the way that this item is typically referred to in code. The [lu_item].[id] can, and should, be used as a foreign key in tables that use it. This can cause the dreaded issue of Primary Key coordination mentioned earlier if, for instance, other data is being moved from environment to environment that has an FK to this table, however, this issue would be there no matter what your solution, so this is not made better or worse by using this table.

You can use this to lookup values by creating an SQL function, and by using a Lookups business object in your C# application. I’ll be posting some of my Lookup object code in future posts.

SELECT dbo.GetLookupValue("EmployeeTypes", "SUPERVISOR");

string _EstimateTypeId = _lu.GetLuItem("EmployeeTypes", "SUPERVISOR").luvalue;

In the above examples, there are two simple calls to get the luvalue for a record in the lu_items table using the categoryshortname and the lucode. This is a typical use case.

There are many that argue against this type of structure. Since there are two PK’s involved in the relationship makes proper FK’s impossible, so they say. For a typical application, there is only one FK required from the lu_items table. If you’re concerned that this Id might be for a value that is in the wrong category, then I believe your data issues are much more vast than worrying about these two tables. If you live in a world where you often have data corruption issues through mismatched FKs, then don’t use this solution. My only concern is that arguing against it in general is improper. This is a case where it can make so many lives simpler, even if there are edge cases where it is not optimal.

I’ve written a demo application using a .NET Core MVC application that not only shows the lookup system, but demos home grown paging as well. Feel free to download it an give it a try:

YTGI/YTG-MVC-Lookups: Lookups / Paging Demo Site (github.com)

Thanks for reading, I hope this has given you some food for thought about designing engines, or micro services, that can be used throughout your organization to simplify development and support.

MVC Index Partial View for Paging

This post was revised and updated on 10/24/2020.

When paging, you usually want to have a way to allow your users to search and filter the data. When they click on search, or hit enter, or click on a column heding, you want to retain that search criteria after the page displays the results. The easiest way to do that is to keep the results in a partial view that is refreshed separately from the primary view. This way, you don’t have to fight the battle of keeping track of it.

One issue I’ve run into is that the user may search for a keyword, then they modify the keyword, think better of it, and just click a sort column. Your page posts back and the search is now for the modified keyword, which wasn’t their intention. Using the partial view method described here WILL NOT have this unwanted functionality.

When writing a partial view for the search results, I’ve added in the ability to utilize my business objects for paging, shown on post: Business Object for Paging, so that paging is implemented generically. I then use the AJAX JQuery call to refresh of the page on an column click for sorting.

Like the main view, this partial view is specifically written for the LuCategory model, there is no need to reference the model as an interface. You can specify the exact model implementation you’re expecting on the page.

Don’t make the mistake of adding the Script reference to
“jquery.unobtrusive-ajax” to this partial view as well, as that will cause some wonky results.

@using YTG.MVC.Lookups.Models
@using YTG.Models
@model SearchModel<LuCategory>

<script type="text/javascript">

    function GetCategories(sortColumn) {

        // Set the global values for sorting post back
        var searchModel = {};
        searchModel.SortColumn = sortColumn;
        searchModel.PrevSortColumn = '@Model.SortColumn';
        searchModel.CurrentPage = @Model.CurrentPage;
        searchModel.PageSize = @Model.PageSize;
        searchModel.SearchTerm = '@Model.SearchTerm';
        searchModel.SortDescending = '@Model.SortDescending';
        searchModel.ActiveOnly = '@Model.ActiveOnly';

        $.ajax({
            type: "POST",
            url: "/Lookups/CatDisplay",
            contentType: "application/json; charset=utf-8",
            data: JSON.stringify(searchModel),
            dataType: "html",
            async: true,
            success: function (result, status, xhr) {
                $("#gridPartial").html(result)
            },
            error: function (xhr, status, error) {
                alert("Result: " + status + " " + error + " " + xhr.status + " " + xhr.statusText)
            }
        });

    }

</script>

<div id="CatDisplay">
    <section id="main-content" class="animated fadeInUp">

        <div class="text-center">
            <!-- Pager -->
            @if (Model.EndPage > 1)
            {
                await Html.RenderPartialAsync("_PagerPartial", Model);
            }
        </div>

        <div class="panel-default">
            <table width="100%" class="table table-bordered table-hover">
                <thead>
                    <tr>
                        <th>Edit</th>
                        <th onclick="GetCategories('Name')" title="Click to Sort">Name</th>
                        <th onclick="GetCategories('ShortName')" title="Click to Sort">Short Name</th>
                        <th onclick="GetCategories('Description')" title="Click to Sort">Description</th>
                        <th class="text-center" title="Quantity of Items">Item Count</th>
                        <th class="text-center">@Html.DisplayName("Active")</th>
                    </tr>
                </thead>
                <tbody>
                    @foreach (var item in Model.SortedResults)
                    {
                    <tr>
                        <td class="text-center">
                            @Html.ActionLink(" ", "Edit", "Lookups", new { id = item.Id.ToString() }, new { @class = "fas fa-edit", @title = "Id: " + item.Id })
                        </td>
                        <td>@Html.DisplayFor(modelItem => item.Name)</td>
                        <td>@Html.DisplayFor(modelItem => item.ShortName)</td>
                        <td>@Html.DisplayFor(modelItem => item.Description)</td>
                        <td class="text-center">@item.Items.Count().ToString()</td>
                        <td class="text-center">@Html.DisplayFor(modelItem => item.IsActive)</td>
                    </tr>
                    }
                </tbody>
            </table>

            @if (Model.RecordCount == 0)
            {
                <div class="col-md-12">
                    <p class="text-center text-warning">
                        There are no Categories defined
                    </p>
                    <br /><br />
                </div>
            }

            <div class="text-center">
                <!-- Pager -->
                @if (Model.EndPage > 1)
                {
                    await Html.RenderPartialAsync("_PagerPartial", Model);
                    await Html.RenderPartialAsync("_PageCountPartial", Model);
                }
            </div>

        </div>
    </section>
</div>

I chose to not display the paging partial views when there is only one page of data using “@if (Model.EndPage > 1)”. You may prefer for them to remain, or at the very least, display the total count of records somewhere if paging is not enabled.

As I’m sure you now probably surmised, this post series took about 20 hours of effort. Please let me know if you’ve found it useful, or if you see any areas where improvements can be made (be nice). Thanks, and happy coding.

MVC Paging Index View

This post was revised and updated on 10/24/2020.

Below is a sample of an Index View utilizing the Generic paging partial views and another partial view that displays my data in a grid.

If you’re falling onto this page from a search link, then you may want to start at the beginning: Ultimate Guide to MVC Paging.

It contains a search box and an “Active Only” check box, if you have a requirement to knock out inactive records. Make special note of the required “jquery.unobtrusive-ajax”.

Since this view is specifically written for the LUCategory, there is no need to reference the model as an interface. You can specify the exact model implementation you’re expecting on the page.

The next important note is that you must store any values from the search model that you’re not displaying on the page in Hidden fields, or else they won’t be passed back on a click, say if the user clicks “Clear”.

@using YTG.MVC.Lookups.Models
@using YTG.Models
@model SearchModel<LuCategory>

@{
    ViewData["Title"] = "Index";
}

<h1>Lookups Category Index</h1>

<section id="main-content" class="animated fadeInUp">
    <div class="row">
        <div class="w-100">
            @using (Html.BeginForm("Index", "Lookups", FormMethod.Get, new { id = "frmCategorySearch" }))
            {
                @Html.HiddenFor(m => m.SortColumn, new { id = "SortColumn" })
                @Html.HiddenFor(m => m.SortDescending, new { id = "SortDescending" })
                @Html.HiddenFor(m => m.PageSize, new { id = "PageSize" })
                @Html.HiddenFor(m => m.CurrentPage, new { id = "CurrentPage" })

                <table class="table-responsive" cellspacing="0" width="100%" style="border-color: red !important;">
                    <tr>
                        <td>
                            @Html.TextBoxFor(m => m.SearchTerm, new
                               {
                                   id = "searchTerm",
                                   placeholder = "Category Name",
                                   CurrentPage = Model.CurrentPage,
                                   PageSize = Model.PageSize,
                                   SortExpression = Model.SortColumn,
                                   SortOrder = Model.SortDescending
                               })
                            <input type="submit" value="Search" class="btn btn-sm btn-primary" name="submitButton" />
                            <input type="submit" value="Clear" class="btn btn-sm btn-primary" name="submitButton" />
                            <a asp-action="Edit" asp-controller="Lookups" asp-route-id="-1" class="btn btn-sm btn-primary">Add New Category</a>
                            <div>
                                @Html.CheckBoxFor(m => m.ActiveOnly, new { id = "ActiveOnly", name = "ActiveOnly" })
                                @Html.LabelFor(m => m.ActiveOnly)
                            </div>
                        </td>
                    </tr>
                </table>

                <div id="gridPartial" style=" border-color: blue !important;">
                    @{ await Html.RenderPartialAsync("_CatsDisplay", Model); }
                </div>

            }
        </div>
    </div>
</section>

This view implements searching, filtering by active, paging, and length of page selection, yet it’s only 51 lines of code!

Before we can finish up, I want to explain the Paging and PageCount partial views.

MVC Paging and PageCount Partial Views

Page Count Partial View

In order to keep the paging generic in MVC, I’ve set them up as two partial views. A MVC Paging and PageCount Partial Views. In the partial view, there is a parameter called refid that I didn’t use in the sample code, but it’s there to service a parent/child relationship. Let’s say you display a list of projects. The user clicks on a project and are displayed a list of tasks for that project. When the user clicks to go to the next page of the tasks, the query will need to pass through the ProjectID in order to “Filter” the values to still only be for the project that was selected. That’s where you’ll use the refid.

Also notice the use of the ISearchModel as the model. That’s because the view won’t allow a model defined generically.

// This won't work
@model SearchModel<TEntity>

By using the Interface, I can get around this restriction and the view will allow a SearchModel to be passed in with whichever collection is needed.

@using YTG.MVC.Lookups.Models

@model ISearchModel

<div>
    <center>
        Change Page Size:<br />
        <ul class="pagination justify-content-center">
            <li class="@(Model.PageSize == 25 ? "active strong" : "") page-item"><a class="page-link" href="#" onclick="GetPageSize(25)">25</a></li>
            <li class="@(Model.PageSize == 50 ? "active strong" : "") page-item"><a class="page-link" href="#" onclick="GetPageSize(50)">50</a></li>
            <li class="@(Model.PageSize == 100 ? "active strong" : "") page-item"><a class="page-link" href="#" onclick="GetPageSize(100)">100</a></li>
        </ul><br />
        Records on this Page: @Model.RecordCount<br />
        Total records found: @Model.TotalItems<br />
    </center>

</div>


<script type="text/javascript">

    function GetPageSize(PageSize) {

        var _url = "/@Model.controllerName/@Model.actionName";

        // Set the global values for sorting post back
        var searchModel = {};
        searchModel.SortColumn = '@Model.SortColumn';
        searchModel.PrevSortColumn = '' // Leave blank so sorting doesn't kick;
        searchModel.CurrentPage = @Model.CurrentPage;
        searchModel.PageSize = PageSize;
        searchModel.SearchTerm = '@Model.SearchTerm';
        searchModel.SearchFilter = '@Model.SearchFilter';
        searchModel.SortDescending = '@Model.SortDescending';
        searchModel.ActiveOnly = '@Model.ActiveOnly';
        searchModel.RefId = @Model.RefId;
        searchModel.RefUniqueId = '@Model.RefUniqueId';

        $.ajax({
            type: "POST",
            url: _url,
            contentType: "application/json; charset=utf-8",
            data: JSON.stringify(searchModel),
            dataType: "html",
            success: function (result, status, xhr) {
                $("#gridPartial").html(result)
            },
            error: function (xhr, status, error) {
                alert("Result: " + status + " " + error + " " + xhr.status + " " + xhr.statusText)
            }
        });

    }

</script>

Paging Partial View

Just as above, this partial view is written to be generic, so it can be use all throughout your site without any special code for the particular page implementation.

@using YTG.MVC.Lookups.Models

@model ISearchModel
<!-- Pager -->
<nav>
    <ul class="pagination justify-content-center">
        @if (Model.CurrentPage > 1)
        {
            <li class="page-item"><a class="page-link" href="#" onclick="GetPaging(1)">First</a></li>
            <li class="page-item"><a class="page-link" href="#" onclick="GetPaging(@Model.CurrentPage - 1)">Previous</a></li>
        }

        @for (var displayPage = Model.StartPage; displayPage <= Model.EndPage; displayPage++)
        {
            <li class="@(displayPage == Model.CurrentPage ? "active" : "") page-item"><a class="page-link" href="#" onclick="GetPaging(@displayPage)">@displayPage</a></li>
        }

        @if (Model.CurrentPage < Model.TotalPages)
        {
            <li class="page-item"><a class="page-link" href="#" onclick="GetPaging(@Model.CurrentPage + 1)">Next</a></li>
            <li class="page-item"><a class="page-link" href="#" onclick="GetPaging(@Model.TotalPages)">Last</a></li>
        }
    </ul>
</nav>
<!-- /Pager -->

<script type="text/javascript">

    function GetPaging(ToPage) {

        var _url = "/@Model.controllerName/@Model.actionName";

        // Set the global values for sorting post back
        var searchModel = {};
        searchModel.SortColumn = '@Model.SortColumn';
        searchModel.PrevSortColumn = ''; // Leave blank so sorting doesn't kick;
        searchModel.CurrentPage = ToPage;
        searchModel.PageSize = @Model.PageSize;
        searchModel.SearchTerm = '@Model.SearchTerm';
        searchModel.SearchFilter = '@Model.SearchFilter';
        searchModel.SortDescending = '@Model.SortDescending';
        searchModel.ActiveOnly = '@Model.ActiveOnly';
        searchModel.RefId = @Model.RefId;
        searchModel.RefUniqueId = '@Model.RefUniqueId';

        $.ajax({
            type: "POST",
            url: _url,
            async: true,
            contentType: "application/json",
            data: JSON.stringify(searchModel),
            dataType: "html",
            success: function (result, status, xhr) {
                $("#gridPartial").html(result)
            },
            error: function (xhr, status, error) {
                alert("Result: " + status + " " + error + " " + xhr.status + " " + xhr.statusText)
            }
        });

    }

</script>

I recommend that these are placed in your Shared folder under the Views folder.

Heart of MVC Paging

This post was revised and updated on 10/24/2020.

If you fell on this post first, you’ll notice that the implementation of paging is a little more involved than the average example on the web. That’s because most of the other examples are either, limited, or outright “The wrong way to do it”. To start from the begining, click here: The Ultimate Guide to MVC Paging!.

Once you have a PagedResult object, or any object with the collection and total count of records that match the filters, back from your web service, you’ll need to convert it to a SearchModel that can be used throughout your MVC views and partial views to enable the paging.

In the implementation demo, we’re going to call a function to return the SearchModel, both from the original call to the Index controller, and then later from a Ajax when the user clicks on a page number or sort column. So I create a function that both controllers can share:

/// <summary>
/// Category search with paged results
/// </summary>
/// <param name="page"></param>
/// <param name="pageSize"></param>
/// <param name="searchTerm"></param>
/// <param name="sortFilter"></param>
/// <param name="sortColumn"></param>
/// <param name="sortDescending"></param>
/// <param name="ActiveOnly"></param>
/// <returns></returns>
public async Task<SearchModel<LuCategory>> GetCategoriesPagedAsync(int page = 1, 
            int pageSize = 25, 
            string searchTerm = "",
            string searchFilter = "", 
            string sortColumn = "ShortName",
            bool sortDescending = false, 
            bool ActiveOnly = true)
{
    SearchModel<LuCategory> _smlus = new SearchModel<LuCategory>();
    try
    {
        PagedResult<LuCategory> _cats = await LookupsSvc.GetCategoriesPagedAsync(page, pageSize, searchTerm, searchFilter, sortColumn, sortDescending, ActiveOnly);
        _smlus.TotalItems = _cats.TotalCount;
        _smlus.CurrentPage = page;
        _smlus.PageSize = pageSize;
        _smlus.RecordCount = _cats.Items.Count();
        _smlus.SortColumn = sortColumn;
        _smlus.SearchTerm = searchTerm;
        _smlus.SortDescending = sortDescending;
        _smlus.ActiveOnly = ActiveOnly;
        _smlus.SortedResults = _cats.Items.ToList();

        _smlus.actionName = "CatDisplay";
        _smlus.controllerName = "Lookups";

        return _smlus;

    }
    catch (Exception)
    {
        throw;
    }
}

While the code might be self explanatory, I’ll explain what’s happening here. Notice the two properties, actionName and contollerName. These are used by the partial views to know how to get to the controller method that’s going to serve up the partial view, so you can set it to go anywhere you like. The reason these properties are part of the object, is because this allows the paging partial views to be generic and reusable.

We would normally return the PagedResults object from our web service call. Then I transfer the results into the SearchModel while appending all the search, paging and filtering criteria passed into the method. The reason for this is that we want to retain this data in order to use the SearchModel in the Index view, as well as the partial views used for paging.

So lets go over the controllers first. The Index GET has all the parameters needed for the SearchModel with defaults, so that it can be called the first time with no arguments. 

/// <summary>
/// Index of categories paged
/// </summary>
/// <param name="page"></param>
/// <param name="pageSize"></param>
/// <param name="searchTerm"></param>
/// <param name="sortFilter"></param>
/// <param name="sortColumn"></param>
/// <param name="sortDescending"></param>
/// <param name="ActiveOnly"></param>
/// <returns></returns>
[HttpGet]
public async Task<IActionResult> Index(int page = 1, int pageSize = 25, string searchTerm = "", string sortFilter = "", string sortColumn = "ShortName", bool sortDescending = false, bool ActiveOnly = true)
{
    try
    {
        var _smlus = await GetCategoriesPagedAsync(page, pageSize, searchTerm, sortFilter, sortColumn, sortDescending, ActiveOnly);

         return View(_smlus);

    }
    catch (Exception)
    {
        throw;
    }
}

Notice how having all the code in a sub method keeps our controller method clean and uncluttered.

Next, we need to have the controller method that returns the PartialView that will service the Ajax calls:

/// <summary>
/// Edit Category, with display of items
/// </summary>
/// <param name="id"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
[HttpGet]
public async Task<ActionResult> Edit(long id, int pageSize = 25)
{
    LuCategoryModel model = new LuCategoryModel();
    try
    {
         if (id >= 0)
         {
             // Get the base LuCategory
             LuCategory _return = await LookupsSvc.GetLuCategoryByIdAsync(id);

            model = Helpers.MappingUtils.LuCatToLuCatModel(_return, false);

             // Get a paged result of some items because we want a paged list of child items as well
            model.Items = await GetItemsPagedAsync(id, 1, pageSize);
        }

        return View(model);

    }
    catch (Exception)
    {
        throw;
    }
}

This is all that’s needed to service the Index view, grid partial view, page count partial view and the pager partial view.

Business Objects for Paging

The .NET framework contains lots of ways to pass around data generically now. Tuples, Dynamic Type, Anonymous types, ExpandoObject, etc. But my favorite is a standard style DTO using Generic collections that I can use for business objects for paging.

For paging in an N-Tier, Repository Pattern environment, I use two different Data Transfer Objects (DTO). The first, is an object used to transfer the basic data from the data layer that contains the collection of return objects or DataRows. The second is a more detailed and flexible object that contains all the properties to satisfy 90% of my paging needs.

DataLayer PageResults

Since my service layer already knows the intimate details of my paging request, when I receive back the response from the data layer, I really only need two things:

  1. The collection I asked for
  2. The total number of items that fit my filters

I’ve always felt that if the API was made public, that less is more.

Yes, I could use the same object that I use in the service layer that has more properties, but I’ve always felt that if the API was made public, that less is more. The data layer return object is one that I call PagedResults, it’s structured like:

using System;
using System.Collections.Generic;

namespace YTG.Models
{

    /// <summary>
    /// Yasgar Technology Group, Inc.
    /// http://www.ytgi.com
    /// </summary>
    /// <typeparam name="TEntity"></typeparam>
    public class PagedResult<TEntity> : IPagedResult<TEntity>
    {

        /// <summary>
        /// Primary constructor
        /// </summary>
        /// <param name="items"></param>
        /// <param name="totalCount"></param>
        public PagedResult(List<TEntity> items, int totalCount)
        {
            Items = items;
            TotalCount = totalCount;
        }

        private Lazy<List<TEntity>> m_items = new Lazy<List<TEntity>>(() => new List<TEntity>(), true);

        /// <summary>
        /// IEnumerable list of items returned
        /// Yasgar Technology Group, Inc. - www.ytgi.com
        /// </summary>
        public List<TEntity> Items
        {
            get { return m_items.Value; }
            set
            {
                m_items = new Lazy<List<TEntity>>(() => value, true);
            }
    }

        /// <summary>
        /// Total count of records from query
        /// Yasgar Technology Group, Inc. - www.ytgi.com
        /// </summary>
        public int TotalCount { get; set; } = 0;

    }
}

You can read a little more about this object here:

Business Object used for Search and Paging

Your data layer will pull back a dataset or IQueryable resultset. You can stuff that into the PagedResult with the following code:

return new PagedResult<Models.LuCategory>(_entityrows.ToList(), _totalCount);

I usually do this in a service layer before my web service front end, so something like:

  1. Pull back a DataSet or Entity IQueryable collection
  2. Return that to the service layer
  3. Loop through and map to a DTO and stuff into the Paging DTO

A ha! I know what you’re thinking, I would just stuff the DataSet or Entity collection in there and send it back, save some time. Right? I don’t recommend that for one main reason; you’re tying your UI to the structure of your database or ORM. I don’t want to digress in this post as to why this is not desirable, but I promise to discuss it in another post soon.

Hate writing redundant mapping code? Read this post: Stuffing DataSets into your objects.

I return this object from my system REST API or WCF web service. In my service layer in the web site, I combine the results into my UI based DTO that has the rest of the properties needed for paging, called SearchModel. It is IMPERATIVE that this DTO be based on the Interface in the project. The SearchModel object looks like this:

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Text.Json.Serialization;

namespace YTG.MVC.Lookups.Models
{

    /// <summary>
    /// View Model for Searching and holding results
    /// </summary>
    public class SearchModel<TEntity> : ISearchModel
    {

        /// <summary>
        /// Default constructor
        /// </summary>
        public SearchModel() { }


        public SearchModel(int totalItems, int? page, int pageSize = 10)
        {
            TotalItems = totalItems;
            CurrentPage = page ?? 1;
            PageSize = pageSize;
        }

        private int m_TotalItems = 0;
        private int m_CurrentPage = 1;
        private int m_PageSize = 0;
        private Lazy<List<TEntity>> m_SortedResults = new Lazy<List<TEntity>>(() => new List<TEntity>(), true);

        /// <summary>
        /// Gets or set the total number of items to be paged
        /// Yasgar Technology Group, Inc. - www.ytgi.com
        /// </summary>
        public int TotalItems
        {
            get
            { return m_TotalItems; }
            set
            {
                m_TotalItems = value;
                if (m_PageSize > 0)
                { TotalPages = (m_TotalItems + (m_PageSize - 1)) / m_PageSize; }
            }
        }

        /// <summary>
        /// Gets or sets the current page being displayed
        /// Automatically populates the values of other properties
        /// Yasgar Technology Group, Inc. - www.ytgi.com
        /// </summary>
        public int CurrentPage
        {
            get
            { return m_CurrentPage; }
            set
            {
                m_CurrentPage = value < 1 ? 1 : value;
                StartPage = m_CurrentPage - 5;
                EndPage = m_CurrentPage + 4;

                if (StartPage <= 0)
                {
                    EndPage -= (StartPage - 1);
                    StartPage = 1;
                }

                if (EndPage > TotalPages)
                {
                    EndPage = TotalPages;
                    if (EndPage > 10)
                    {
                        StartPage = EndPage - 9;
                    }
                }
            }
        }

        /// <summary>
        /// Gets or sets the page size currently set
        /// Yasgar Technology Group, Inc. - www.ytgi.com
        /// </summary>
        public int PageSize
        {
            get
            { return m_PageSize; }
            set
            {
                m_PageSize = value;
                if (m_TotalItems > 0)
                { TotalPages = (m_TotalItems + (m_PageSize - 1)) / m_PageSize; }
                // To get EndPage set correctly if this property is set last
                CurrentPage = m_CurrentPage;
            }
        }

        /// <summary>
        /// Gets or set the total number of pages based on the TotalItems and PageSize
        /// Yasgar Technology Group, Inc. - www.ytgi.com
        /// </summary>
        public int TotalPages { get; set; } = 0;

        /// <summary>
        /// Gets or sets the start page for the pager display
        /// Yasgar Technology Group, Inc. - www.ytgi.com
        /// </summary>
        public int StartPage { get; set; } = 0;

        /// <summary>
        /// Gets or sets the end page for the pager display
        /// Yasgar Technology Group, Inc. - www.ytgi.com
        /// </summary>
        public int EndPage { get; set; } = 0;

        /// <summary>
        /// Gets or sets the search filter for the paged items
        /// Yasgar Technology Group, Inc. - www.ytgi.com
        /// </summary>
        public string SearchFilter { get; set; } = string.Empty;

        /// <summary>
        /// Gets or sets the search term for the paged items
        /// Yasgar Technology Group, Inc. - www.ytgi.com
        /// </summary>
        public string SearchTerm { get; set; } = string.Empty;

        /// <summary>
        /// Gets or sets the sort expression for the paged items
        /// Yasgar Technology Group, Inc. - www.ytgi.com
        /// </summary>
        public string SortColumn { get; set; } = string.Empty;

        /// <summary>
        /// Gets or sets the previous sort expression for the paged items when posting back to controller
        /// Yasgar Technology Group, Inc. - www.ytgi.com
        /// </summary>
        public string PrevSortColumn { get; set; } = string.Empty;

        /// <summary>
        /// Gets or sets the count of records in the current page
        /// Yasgar Technology Group, Inc. - www.ytgi.com
        /// </summary>
        public int RecordCount { get; set; } = 0;

        /// <summary>
        /// Gets or sets the sort order, default is asc
        /// Yasgar Technology Group, Inc. - www.ytgi.com
        /// </summary>
        public bool SortDescending { get; set; } = false;

        /// <summary>
        /// Gets or sets whether the paged items only contain active records
        /// Yasgar Technology Group, Inc. - www.ytgi.com
        /// </summary>
        [Display(Name = "Active Only")]
        public bool ActiveOnly { get; set; } = true;

        /// <summary>
        /// Gets or sets the Reference Id, to be used for a parent object
        /// Yasgar Technology Group, Inc. - www.ytgi.com
        /// </summary>
        public long RefId { get; set; } = 0;

        /// <summary>
        /// Gets or sets the Reference Unique Id, to be used for a parent object
        /// Yasgar Technology Group, Inc. - www.ytgi.com
        /// </summary>
        public Guid RefUniqueId { get; set; } = Guid.Empty;

        /// <summary>
        /// Gets or sets the List of pre-sorted results
        /// Yasgar Technology Group, Inc. - www.ytgi.com
        /// </summary>
        public List<TEntity> SortedResults
        {
            get { return m_SortedResults.Value; }
            set
            {
                m_SortedResults = new Lazy<List<TEntity>>(() => value, true);
            }
        }


        /// <summary>
        /// Gets or sets the controller name for this Model
        /// Needed for Pagination partial views
        /// Yasgar Technology Group, Inc. - www.ytgi.com
        /// </summary>
        public string controllerName { get; set; } = string.Empty;

        /// <summary>
        /// Gets or sets the action name for this model
        /// Needed for Pagination partial views
        /// Yasgar Technology Group, Inc. - www.ytgi.com
        /// </summary>
        public string actionName { get; set; } = string.Empty;

    }
}

I use the simpler PagedResults to return the data collection and the total count from my repository. Then push this data, combined with whatever filter options were sent in back to the View.

You can take a look at how these objects are used in my sample app on GitHub:

https://github.com/YTGI/YTG-MVC-Lookups

Stuffing DataSets into your objects

One thing that most developers loath is writing mapping classes. One common need is stuffing datasets into your objects from a return of your SQL Client code. Yes, I know that there are utilities that are out there like AutoMapper, but here’s something to consider.

The power of many of these utilities are their ability to map properties that have the same name, but different case without any code. This is definitely a time saver. I would highly recommend their use in the service layer of your UI if you’re mapping one object, or a small collection of objects like a paged response of 25 or 50 objects. But don’t use these tools if you are processing batch size collections. Why you ask?

Think about it, how would you find out if there was a property in an object with a particular name regardless of case? Reflection of course, but Reflection has a serious processing cost. So using it for a small collection of objects being displayed to a user is one thing, but when you’re processing thousands, or millions of objects, there is nothing that beats declarative code for speed.

I want to make it clear, this method has lots of advantages, as well as disadvantages, there’s never a panacea…

Advantages

  1. One standard place that maps ALL your return values to your DTO
  2. No maintenance of numerous areas mapping data. Think of the scenario where a PK or FK is changed from int to long…
  3. The entire team ALWAYS knows where code is mapped, they don’t have to hunt through multiple service layers and mapping classes
  4. Your object can consume DataSets from multiple stored procedures seamlessly and still populate, even if the field names are different.

Disadvantages

  1. This is only recommended if you’re getting back DataSets, as otherwise, you’d be putting a dependency for an EF entity in your DTO. (Don’t do this, as then you have to reference your Repository in your UI project, which is, how do doctors say it, contra-recommended)
  2. If you’re filling your object from multiple stored procedures, it will hide the fact that a particular column your expecting is absent, which is a code smell.

With the aforementioned caveats, let’s show the concept. The reason I only recommend this for DataSets is that it’s part of the .NET framework. I vehemently recommend that DTO’s don’t have any references to objects that don’t exist either 1) In the .NET framework, or 2) In your common utilities libraries.

Here’s an example of the constructors you would have in such an object:

        /// <summary>
        /// Default constructor
        /// </summary>
        public SalesProduct() { }

        /// <summary>
        /// Constructor with a DataSet to populate
        /// </summary>
        /// <param name="p_DataRow"></param>
        public SalesProduct(DataRow p_DataRow)
        {
            Populate(p_DataRow);
        }

Remember that when you code a constructor, like the second one, you stop the ability to instantiate your object without a parameter. So, in most cases, you want to declare an empty “Default” constructor as well. Remember, if you don’t have an empty constructor, you won’t be able to deserialize the object from a web service call…

Next step is to have the Populate(DataSet) method in your object like this example:

        /// <summary>
        /// Populate this object from a DataRow
        /// www.ytgi.com
        /// </summary>
        /// <param name="p_DataRow"></param>
        private void Populate(DataRow p_DataRow)
        {

            if (p_DataRow.Table.Columns.Contains("ProductID"))
            { this.ProductId = p_DataRow.IsNull("ProductID") ? 0 : p_DataRow.Field<int>("ProductID"); }

            if (p_DataRow.Table.Columns.Contains("ProductName"))
            { this.ProductName = p_DataRow.IsNull("ProductName") ? string.Empty : p_DataRow.Field<string>("ProductName"); }
            if (p_DataRow.Table.Columns.Contains("Description"))
            { this.ProductName = p_DataRow.IsNull("Description") ? string.Empty : p_DataRow.Field<string>("Description"); }


            if (p_DataRow.Table.Columns.Contains("UnitPrice"))
            { this.UnitPrice = p_DataRow.IsNull("UnitPrice") ? 0m : p_DataRow.Field<decimal>("UnitPrice"); }

            if (p_DataRow.Table.Columns.Contains("OnlineOrderFlag"))
            { this.OnlineOrderFlag = p_DataRow.IsNull("OnlineOrderFlag") ? false : p_DataRow.Field<bool>("OnlineOrderFlag"); }

            if (p_DataRow.Table.Columns.Contains("ShipDate"))
            { this.ShipDate = p_DataRow.IsNull("ShipDate") ? DateTime.MinValue : p_DataRow.Field<DateTime>("ShipDate"); }

        }

Notice one thing in the above code that’s my favorite feature of using this method, the ProductName property is being populated by either the “ProductName” column or the “Description” column, depending on the stored procedure you’re calling.

I typically use this implementation when I return a DataSet from a stored procedure and load the objects like this:

List<Models.SalesProduct> _sales = new List<Models.SalesProduct>();

// Would normally do this in the ServiceLayer
if (_returnData != null)
{
   foreach (DataRow _row in _returnData.Tables[0].Rows)
   {
       _sales.Add(new Models.SalesProduct(_row));
   }
}

I hope this helps folks to think of different ways to consolidate code that doesn’t violate the patterns they like to use.

If you’re going through the Paging series, visit the next post to continue: Business Objects for Paging.