Open Source

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.

  1. CREATE TABLE public.sysuser(  
  2.     sysuserid character varying(100) COLLATE pg_catalog."default" NOT NULL,  
  3.     sysusername character varying(100) COLLATE pg_catalog."default" NOT NULL,  
  4.     sysuserpassword character varying(100) COLLATE pg_catalog."default" NOT NULL,  
  5.     sysuseremail character varying(100) COLLATE pg_catalog."default",  
  6.     CONSTRAINT sysuser_pkey PRIMARY KEY (sysuserid))WITH (  
  7.     OIDS = FALSE)TABLESPACE pg_default;  
  8. ALTER TABLE public.sysuser  
  9.     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.

  1. Install-Package Nancy -Version 1.4.3  
  2. Install-Package Nancy.Hosting.Aspnet -Version 1.4.1  
  3. Install-Package Nancy.Viewengines.Razor -Version 1.4.3  
  4. Install-Package Nancy.Authentication.Forms -Version 1.4.1  
  5. Install-Package Dapper  
  6. 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.

  1. using Nancy;  
  2. using Nancy.Authentication.Forms;  
  3. using Nancy.Bootstrapper;  
  4. using Nancy.Conventions;  
  5. using Nancy.Session;  
  6. using Nancy.TinyIoc;  
  7. using NancyMusicStore.Common;  
  8. namespace NancyMusicStore  
  9. {  
  10.     public class CustomBootstrapper : DefaultNancyBootstrapper  
  11.     {  
  12.         protected override void ApplicationStartup(TinyIoCContainer container,IPipelines pipelines)  
  13.         {  
  14.             //Prevent errors on Linux  
  15.             StaticConfiguration.DisableErrorTraces = false;  
  16.         }  
  17.           
  18.         protected override void ConfigureApplicationContainer(TinyIoCContainer container)  
  19.         {  
  20.             base.ConfigureApplicationContainer(container);                   
  21.         }  
  22.   
  23.         protected override void ConfigureConventions(NancyConventions conventions)  
  24.         {  
  25.             base.ConfigureConventions(conventions);  
  26.             conventions.StaticContentsConventions.Add(StaticContentConventionBuilder.AddDirectory("Scripts"));  
  27.             conventions.StaticContentsConventions.Add(StaticContentConventionBuilder.AddDirectory("Content"));  
  28.         }  
  29. }  
  30. }   

Conect to The PostgreSQL

Add a connectionStrings section to the web.config

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

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

  1. using Dapper;  
  2. using Npgsql;  
  3. using System.Collections.Generic;  
  4. using System.Linq;  
  5. using System.Data;  
  6. namespace NancyMusicStore.Common  
  7. {  
  8.     public class DBHelper  
  9.     {  
  10.         //open connection         
  11.         private static IDbConnection OpenConnection()  
  12.         {  
  13.             var conn = new NpgsqlConnection(ConfigHelper.GetConneectionStr());  
  14.             conn.Open();  
  15.             return conn;  
  16.         }  
  17.   
  18.         //execute   
  19.         public static int Execute(string sql, object param = null, IDbTransaction transaction = null,  
  20.             int? commandTimeout = null, CommandType? commandType = null)  
  21.         {  
  22.             using (var conn = OpenConnection())  
  23.             {  
  24.                 return conn.Execute(sql, param, transaction, commandTimeout, commandType);  
  25.             }  
  26.         }  
  27.   
  28.         //execute   
  29.         public static object ExecuteScalar(string cmd, object param = null, IDbTransaction transaction = null,  
  30.             int? commandTimeout = null, CommandType? commandType = null)  
  31.         {  
  32.             using (var conn = OpenConnection())  
  33.             {  
  34.                 return conn.ExecuteScalar(cmd, param, transaction, commandTimeout, commandType);  
  35.             }  
  36.         }  
  37.   
  38.         //do query and return a list  
  39.         public static IList<T> Query<T>(string sql, object param = null, IDbTransaction transaction = null,  
  40.             bool buffered = true, int? commandTimeout = null, CommandType? commandType = null) where T : class  
  41.         {  
  42.             using (var conn = OpenConnection())  
  43.             {  
  44.                 return conn.Query<T>(sql, param, transaction, buffered, commandTimeout, commandType).ToList();  
  45.             }  
  46.         }  
  47.   
  48.         //do query and return the first entity  
  49.         public static T QueryFirstOrDefault<T>(string sql, object param = null, IDbTransaction transaction = null,  
  50.             int? commandTimeout = null, CommandType? commandType = null) where T : class  
  51.         {  
  52.             using (var conn = OpenConnection())  
  53.             {  
  54.                 return conn.QueryFirstOrDefault<T>(sql, param, transaction, commandTimeout, commandType);  
  55.             }  
  56.         }  
  57.     }}   

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.

  1. @inherits Nancy.ViewEngines.Razor.NancyRazorViewBase<dynamic>  
  2. <!DOCTYPE html>  
  3. <html>  
  4. <head>  
  5.     <title>@ViewBag.Title</title>    
  6.     <meta charset="UTF-8" />  
  7.     <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1" />  
  8.     <meta name="viewport" content="width=device-width, initial-scale=1" />      
  9. <link href="~/Content/Site.css" rel="stylesheet" type="text/css" />    </head>  
  10. <body>  
  11.     <div id="header">  
  12.         <h1><a href="/">NancyFx MUSIC STORE</a></h1>  
  13.         <ul id="navlist">  
  14.             <li class="first"><a href="/" id="current">Home</a></li>  
  15.             <li><a href="/store">Store</a></li>              
  16.             <li><a id="cart-status" href="/shoppingcart/index"></a></li>  
  17.             <li><a href="/storemanager">Admin</a></li>  
  18.         </ul>  
  19.     </div>  
  20.       
  21.     <ul id="categories">         
  22.     </ul>  
  23.      
  24.     <div id="main">  
  25.         @RenderBody()  
  26.     </div>  
  27.   
  28.     <div id="footer">  
  29.         built with <a href="http://nancyfx.org">Nancy 1.4.3</a>  
  30.     </div>  
  31.   
  32.     <script src="~/Scripts/jquery-1.5.1.min.js" type="text/javascript"></script>  
  33. @RenderSection("scripts", required: false)  
  34. </body>  
  35. </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.

  1. @{ 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.

  1. using Nancy;  
  2. using NancyMusicStore.Common;  
  3. using NancyMusicStore.Models;  
  4. using System.Collections.Generic;  
  5. using System.Data;  
  6. using System.Linq;  
  7. namespace NancyMusicStore.Modules  
  8. {  
  9.     public class HomeModule : NancyModule  
  10.     {  
  11.         public HomeModule() : base("/")  
  12.         {  
  13.             Get["/"] = _ =>  
  14.             {  
  15.                 var albums = GetTopSellingAlbums(5);  
  16.                 return View["Index", albums];  
  17.             };  
  18.         }  
  19.   
  20.         /// <summary>  
  21.         /// get top count selling albums   
  22.         /// </summary>  
  23.         /// <param name="count"></param>  
  24.         /// <returns></returns>  
  25.         private List<Album> GetTopSellingAlbums(int count)  
  26.         {  
  27.             string sql = "public.get_top_selling_albums";  
  28.             var list = DBHelper.Query<Album>(sql, new  
  29.             {  
  30.                 num = count  
  31.             }, null, true, null, CommandType.StoredProcedure).ToList();  
  32.             return list;  
  33.         }  
  34. }  
  35. }   

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

  1. -- FUNCTION: public.get_top_selling_albums(integer)  
  2. -- DROP FUNCTION public.get_top_selling_albums(integer);  
  3. CREATE OR REPLACE FUNCTION public.get_top_selling_albums(  
  4.     num integer)  
  5.     RETURNS SETOF "TABLE(albumid integer, genreid integer, artistid integer, title character varying, price numeric, albumarturl character varying)"  
  6.     LANGUAGE 'plpgsql'  
  7.     COST 100.0  
  8.     VOLATILE NOT LEAKPROOF   
  9.     ROWS 1000.0AS $function$  
  10. begin          
  11.     RETURN QUERY SELECT   
  12.     A.albumid,  
  13.     A.genreid,  
  14.     A.artistid,  
  15.     A.title,  
  16.     A.price,  
  17.     A.albumarturl   
  18.     FROM albums A  
  19.     LEFT JOIN orderdetails O ON A.albumid = O.albumid  
  20.     GROUP BY A.albumid,A.genreid,A.artistid,A.title,A.price,A.albumarturl  
  21.     ORDER BY count(O.albumid) desc LIMIT num;          end;  
  22.   
  23. $function$;  
  24. ALTER FUNCTION public.get_top_selling_albums(integer)  
  25.     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.

  1. @inherits Nancy.ViewEngines.Razor.NancyRazorViewBase<List<NancyMusicStore.Models.Album>>  
  2. @{  
  3.     ViewBag.Title = "NancyFx Music Store";      
  4. }  
  5. <div id="promotion"></div>  
  6. <h3><em>Fresh</em> off the grill</h3>  
  7. <ul id="album-list">  
  8.     @foreach (var album in Model)  
  9.     {  
  10.         <li>  
  11.             <a href="javascript:;">  
  12.                 <img alt="@album.Title" src="@album.AlbumArtUrl" />  
  13.                 <span>@album.Title</span>  
  14.             </a>  
  15.         </li>  
  16. }  
  17. </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.

  1. <script type="text/javascript">  
  2.     $(function () {  
  3.         $.ajax({  
  4.             url: "/store/genremenu",  
  5.             method: "get",  
  6.             dataType: "json",  
  7.             success: function (res) {  
  8.                 for (var i = 0; i < res.length; i++) {  
  9.                     $("#categories").append('<li><a href="javascript:;">' + res[i].name + '</a></li>');  
  10.                 }  
  11.             }  
  12.         });  
  13. });  
  14. </script>   

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

  1. using Nancy;  
  2. using NancyMusicStore.Common;  
  3. using NancyMusicStore.Models;  
  4. using NancyMusicStore.ViewModels;  
  5. using System.Collections.Generic;  
  6. using System.Data;  
  7. using System.Linq;  
  8. namespace NancyMusicStore.Modules  
  9. {  
  10.     public class StoreModule : NancyModule  
  11.     {  
  12.         public StoreModule() : base("/store")  
  13.         {  
  14.             Get["/genremenu"] = _ =>  
  15.             {  
  16.                 return Response.AsJson(GetGenreList());  
  17.             };  
  18.         }  
  19.   
  20.         private IList<Genre> GetGenreList()  
  21.         {  
  22.             string cmd = "public.get_all_genres";  
  23.             return DBHelper.Query<Genre>(cmd, null, null, true, null, CommandType.StoredProcedure);  
  24.         }  
  25. }  
  26. }   

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.

Up Next
    Ebook Download
    View all
    Learn
    View all
    Get Started

    You need to be a premium member to use this feature. To access it, you'll have to upgrade your membership.

    Become a sharper developer and jumpstart your career.

    Basic

    $0

    $

    . 00

    monthly

    For Basic members:

    • Standard Profile
    • Access learning courses
    • Access free e-books
    • 1x point
    Premium

    $20

    $

    . 00

    monthly

    For Premium members:

    • Earn 2x reward points
    • Premium profile with Verified check
    • Premium Avatar with status update
    • Learning courses
    • Unlimited Certifications and badges on profile
    • Unlimited Book downloads
    • Access to Premium content
    • Online Training
    • Unlimited PDF conversions and downloads
    • Professional Resume Writing
    • Interview Preparations Guide
    • 25 messages per month
    • 1 "Kodzu" avatar, basic
    Elite

    $45

    $

    . 00

    monthly

    For Elite members:

    • Bundle E-books
    • Access to all Learn Series
    • Unlimited Apply Jobs