Build A New MusicStore Project Using NancyFX And PostgreSQL

Introduction

In this article, you will begin to build a new MusicStore project, using NancyFX and PostgreSQL.

NancyFX

Nancy is a lightweight, low-ceremony framework for building HTTP based Services on .NET Framework/Core and Mono.

For more information about NancyFx, you can visit GitHub and its official site.

• https://github.com/NancyFx/Nancy
• http://nancyfx.org/

Preparatory Work

As we all know, there are two parts of MVC MusicStore- one is related to the store and another is related to MemberShip, which is given below.


Before building the project, we should migrate the database from SQL Server Compact Edition database file to PostgreSQL and  MvcMusicStore is the only one thing which we require. We can easily generate SQL file from SQL Server Compact. Subsequently, edit and execute this file in pg Admin.

Now, we have to migrate the musicstore's data to the PostgreSQL and we need a new table to store the users. Hence, we create a new table in the database.The script is given below.

CREATE TABLE public.sysuser(
    sysuserid character varying(100) COLLATE pg_catalog."default" NOT NULL,
    sysusername character varying(100) COLLATE pg_catalog."default" NOT NULL,
    sysuserpassword character varying(100) COLLATE pg_catalog."default" NOT NULL,
    sysuseremail character varying(100) COLLATE pg_catalog."default",
    CONSTRAINT sysuser_pkey PRIMARY KEY (sysuserid))WITH (
    OIDS = FALSE)TABLESPACE pg_default;
ALTER TABLE public.sysuser
    OWNER to dev;

The next step which we should do is to configure PostgreSQL, so that we can visit it via the remote way. We edit the file pg_hba.conf, update the line host all all 127.0.0.1/32 md5 to host all all 0.0.0.0/0 md5.

Create Project And Install Packages

Now, we can create a new empty ASP.NET project and install some package from Package Manager console.

Install-Package Nancy -Version 1.4.3
Install-Package Nancy.Hosting.Aspnet -Version 1.4.1
Install-Package Nancy.Viewengines.Razor -Version 1.4.3
Install-Package Nancy.Authentication.Forms -Version 1.4.1
Install-Package Dapper
Install-Package Npgsql -Version 3.1.9

The packages given above are all we need to install in this project. I remove the useless DLL's from the project as well. After this step ,the references may look, as shown below.

Add and Configure Static Content

Add two folders in the project, where one is Content, the other is Scripts and copy the resource from mvcmusicstore and what we need are given below.

For using those static contents, we need to add some configuration in the bootstrapper class.

Here, the content of bootstrapper class is given below.

using Nancy;
using Nancy.Authentication.Forms;
using Nancy.Bootstrapper;
using Nancy.Conventions;
using Nancy.Session;
using Nancy.TinyIoc;
using NancyMusicStore.Common;
namespace NancyMusicStore
{
    public class CustomBootstrapper : DefaultNancyBootstrapper
    {
        protected override void ApplicationStartup(TinyIoCContainer container,IPipelines pipelines)
        {
            //Prevent errors on Linux
            StaticConfiguration.DisableErrorTraces = false;
        }

        protected override void ConfigureApplicationContainer(TinyIoCContainer container)
        {
            base.ConfigureApplicationContainer(container);
        }

        protected override void ConfigureConventions(NancyConventions conventions)
        {
            base.ConfigureConventions(conventions);
            conventions.StaticContentsConventions.Add(StaticContentConventionBuilder.AddDirectory("Scripts"));
            conventions.StaticContentsConventions.Add(StaticContentConventionBuilder.AddDirectory("Content"));
        }
}
}

Conect to The PostgreSQL

Add a connectionStrings section to the web.config

<connectionStrings>
   <add name="pgsqlConn" connectionString="Host=127.0.0.1;Username=dev;Password=123456;Database=nancymusicstore;" />
</connectionStrings>

Setup a DBHelper, which helps us to operate the database easily.

using Dapper;
using Npgsql;
using System.Collections.Generic;
using System.Linq;
using System.Data;
namespace NancyMusicStore.Common
{
    public class DBHelper
    {
        //open connection
        private static IDbConnection OpenConnection()
        {
            var conn = new NpgsqlConnection(ConfigHelper.GetConneectionStr());
            conn.Open();
            return conn;
        }

        //execute
        public static int Execute(string sql, object param = null, IDbTransaction transaction = null,
            int? commandTimeout = null, CommandType? commandType = null)
        {
            using (var conn = OpenConnection())
            {
                return conn.Execute(sql, param, transaction, commandTimeout, commandType);
            }
        }

        //execute
        public static object ExecuteScalar(string cmd, object param = null, IDbTransaction transaction = null,
            int? commandTimeout = null, CommandType? commandType = null)
        {
            using (var conn = OpenConnection())
            {
                return conn.ExecuteScalar(cmd, param, transaction, commandTimeout, commandType);
            }
        }

        //do query and return a list
        public static IList<T> Query<T>(string sql, object param = null, IDbTransaction transaction = null,
            bool buffered = true, int? commandTimeout = null, CommandType? commandType = null) where T : class
        {
            using (var conn = OpenConnection())
            {
                return conn.Query<T>(sql, param, transaction, buffered, commandTimeout, commandType).ToList();
            }
        }

        //do query and return the first entity
        public static T QueryFirstOrDefault<T>(string sql, object param = null, IDbTransaction transaction = null,
            int? commandTimeout = null, CommandType? commandType = null) where T : class
        {
            using (var conn = OpenConnection())
            {
                return conn.QueryFirstOrDefault<T>(sql, param, transaction, commandTimeout, commandType);
            }
        }
    }}

Create Models to map the tables

Create a Models folder and add some classes, which maps the tables .

Create a Layout Page of NancyMusicStore

Create a layout page in the path /Views/Shared, which is similar to what we do in ASP.NET MVC.

@inherits Nancy.ViewEngines.Razor.NancyRazorViewBase<dynamic>
<!DOCTYPE html>
<html>
<head>
    <title>@ViewBag.Title</title>
    <meta charset="UTF-8" />
    <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1" />
    <meta name="viewport" content="width=device-width, initial-scale=1" />
<link href="~/Content/Site.css" rel="stylesheet" type="text/css" />    </head>
<body>
    <div id="header">
        <h1><a href="/">NancyFx MUSIC STORE</a></h1>
        <ul id="navlist">
            <li class="first"><a href="/" id="current">Home</a></li>
            <li><a href="/store">Store</a></li>
            <li><a id="cart-status" href="/shoppingcart/index"></a></li>
            <li><a href="/storemanager">Admin</a></li>
        </ul>
    </div>

    <ul id="categories">
    </ul>

    <div id="main">
        @RenderBody()
    </div>

    <div id="footer">
        built with <a href="http://nancyfx.org">Nancy 1.4.3</a>
    </div>

    <script src="~/Scripts/jquery-1.5.1.min.js" type="text/javascript"></script>
@RenderSection("scripts", required: false)
</body>
</html>

In this layout page, we should pay attention to the first line, as it indicates that the ViewEngine which is used is Razor.

We can't use the Html.RenderAction in NancyFX's base Razor ViewEngine, so I will use AJAX to complete the categories and the number of shoppingcart in the layout page.

Now, we need to make this layout page as a default layout page, when we forget to appoint the layout page in a view.

Add a _ViewStart.cshtml file in the Views folder and here is the content of this file.

@{ Layout = "Views/Shared/_Layout.cshtml";}

There is a little difference in the file between NancyFX and ASP.NET MVC.

Create Modules

NancyFX's module is similar with MVC's controller. We create a new folder named Modules to store our modules.

To be the first, adding a HomeModule class in the Modules folder is to show the index page, the index page shows us the top five selling albums and the categories of albums.

using Nancy;
using NancyMusicStore.Common;
using NancyMusicStore.Models;
using System.Collections.Generic;
using System.Data;
using System.Linq;
namespace NancyMusicStore.Modules
{
    public class HomeModule : NancyModule
    {
        public HomeModule() : base("/")
        {
            Get["/"] = _ =>
            {
                var albums = GetTopSellingAlbums(5);
                return View["Index", albums];
            };
        }

        /// <summary>
        /// get top count selling albums
        /// </summary>
        /// <param name="count"></param>
        /// <returns></returns>
        private List<Album> GetTopSellingAlbums(int count)
        {
            string sql = "public.get_top_selling_albums";
            var list = DBHelper.Query<Album>(sql, new
            {
                num = count
            }, null, true, null, CommandType.StoredProcedure).ToList();
            return list;
        }
}
}

In the private method GetTopSellingAlbums, it uses a stored procedure to find out the top selling albums :

-- FUNCTION: public.get_top_selling_albums(integer)
-- DROP FUNCTION public.get_top_selling_albums(integer);
CREATE OR REPLACE FUNCTION public.get_top_selling_albums(
    num integer)
    RETURNS SETOF "TABLE(albumid integer, genreid integer, artistid integer, title character varying, price numeric, albumarturl character varying)"
    LANGUAGE 'plpgsql'
    COST 100.0
    VOLATILE NOT LEAKPROOF
    ROWS 1000.0AS $function$
begin
    RETURN QUERY SELECT
    A.albumid,
    A.genreid,
    A.artistid,
    A.title,
    A.price,
    A.albumarturl
    FROM albums A
    LEFT JOIN orderdetails O ON A.albumid = O.albumid
    GROUP BY A.albumid,A.genreid,A.artistid,A.title,A.price,A.albumarturl
    ORDER BY count(O.albumid) desc LIMIT num;          end;

$function$;
ALTER FUNCTION public.get_top_selling_albums(integer)
    OWNER TO dev;

Now, we need to add a corresponding view for the index page like ASP.NET MVC.

Create a Index.cshtml in the path Views/Home.

@inherits Nancy.ViewEngines.Razor.NancyRazorViewBase<List<NancyMusicStore.Models.Album>>
@{
    ViewBag.Title = "NancyFx Music Store";
}
<div id="promotion"></div>
<h3><em>Fresh</em> off the grill</h3>
<ul id="album-list">
    @foreach (var album in Model)
    {
        <li>
            <a href="javascript:;">
                <img alt="@album.Title" src="@album.AlbumArtUrl" />
                <span>@album.Title</span>
            </a>
        </li>
}
</ul>

At this time, we run the project and you may get the screenshot, as shown below.

We mentioned that the categories of the albums should display in the layout page by AJAX. Let's start to complete it.

Go back to the _Layout.cshtml and add JavaScript code to make it. Load the categories asynchronously.

<script type="text/javascript">
    $(function () {
        $.ajax({
            url: "/store/genremenu",
            method: "get",
            dataType: "json",
            success: function (res) {
                for (var i = 0; i < res.length; i++) {
                    $("#categories").append('<li><a href="javascript:;">' + res[i].name + '</a></li>');
                }
            }
        });
});
</script>

The request URL is the same as MVC MusicStore, so we need to add a StoreModule class to deal with the request.

using Nancy;
using NancyMusicStore.Common;
using NancyMusicStore.Models;
using NancyMusicStore.ViewModels;
using System.Collections.Generic;
using System.Data;
using System.Linq;
namespace NancyMusicStore.Modules
{
    public class StoreModule : NancyModule
    {
        public StoreModule() : base("/store")
        {
            Get["/genremenu"] = _ =>
            {
                return Response.AsJson(GetGenreList());
            };
        }

        private IList<Genre> GetGenreList()
        {
            string cmd = "public.get_all_genres";
            return DBHelper.Query<Genre>(cmd, null, null, true, null, CommandType.StoredProcedure);
        }
}
}

Now, when we visit the index page, it will send a asynchronous request to the http://yourdomain.com/store/genremenu and load the categories.

The index page of NancyMusic changes and resembles what is shown below.

What Next?

In my next article, I will complete the albums' information and show you how to manage the albums.