Chinook ASP.NET MVC 4 Web Application

PublishedOctober 5, 2012

For this tutorial, you will need:

Visual Studio Express 2012 for Windows 8 download details
SQL Server Express Database using Visual Studio 2012 with SQL Server Express in this tutorial. Other versions may work (untested). msdn.microsoft.com
Chinook database github.com/jimfrenette/chinookdatabase
SQL Server 2012 Express Management Studio download details

In Visual Studio, create a new C# ASP.NET MVC 4 Web Application:

Visual Studio 2012 New Project Dialog

Next, select the Mobile Application Project Template. This template includes jQuery, jQuery intellisense for Visual Studio, jQuery Mobile and Knockoutjs javascript files.

Visual Studio 2012 Project Template Dialog

If you haven’t done so yet, now would be a good time to install the Chinook database to your SQL Server.

Make sure you can Access your SQL Server Configure Windows Firewall

Open up the Web.config and replace

<connectionStrings>
    <add name="DefaultConnection" providerName="System.Data.SqlClient" connectionString="Data Source=(LocalDb)\v11.0;Initial Catalog=aspnet-MvcMobileApp-20120922134208;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\aspnet-MvcMobileApp-20120922134208.mdf" />
</connectionStrings>

With *

<connectionStrings>
    <add name="ChinookConnection" providerName="System.Data.SqlClient" connectionString="Data Source=VGN-FW290\SQLEXPRESS;Initial Catalog=Chinook;Integrated Security=True" />
</connectionStrings>

VGN-FW290 is the name of my computer. Replace this with your computer name

SQL Stored Procedures

Open up SQL Server Management Studio and connect to the server that is defined in your web.config connection string. In the Object Explorer F8 Right click on the Stored Procedures node of the expanded Databases.Chinook tree and Select “New Stored Procedure”.

new-sproc

The first stored procedure you will create, GetArtists uses CTE (Common Table Expression) to handle the paging of the records. Especially in web applications, it is a best practice to utilize paging when returning records from large tables.

CREATE PROCEDURE [dbo].[GetArtists]
(
	@Id INT = 0,
	@PageSize INT = 0,
	@PageNumber INT = 1
)
AS

DECLARE
	@FirstRow INT,
	@LastRow INT

IF (@Id = 0)
BEGIN

	IF (@PageSize = 0) SET @PageSize = (SELECT COUNT(*) FROM dbo.Artist)

	SELECT	@FirstRow = ( @PageNumber - 1) * @PageSize + 1,
			@LastRow = (@PageNumber - 1) * @PageSize + @PageSize ;

	WITH Artist  AS
	(
		SELECT	ROW_NUMBER() OVER (ORDER BY Name ASC) AS RowNumber,
				COUNT(*) OVER () AS TotalCount, *
		FROM	dbo.Artist
	)
	SELECT	*
	FROM	Artist
	WHERE	RowNumber BETWEEN @FirstRow AND @LastRow

END
ELSE
BEGIN
	SET NOCOUNT ON

	SELECT * FROM Artist
		WHERE ArtistId = @Id
END

The next stored procedure you will create, GetAlbums is for selecting Album(s).

CREATE PROCEDURE [dbo].[GetAlbums]
(
	@Id INT = 0,
	@ArtistId INT = 0,
	@PageSize INT = 0,
	@PageNumber INT = 1
)
AS

DECLARE
	@FirstRow INT,
	@LastRow INT

IF (@ArtistId <> 0)
BEGIN

	IF (@PageSize = 0) SET @PageSize = (SELECT COUNT(*) FROM dbo.Album)

	SELECT	@FirstRow = ( @PageNumber - 1) * @PageSize + 1,
			@LastRow = (@PageNumber - 1) * @PageSize + @PageSize ;

	WITH Album  AS
	(
		SELECT	ROW_NUMBER() OVER (ORDER BY Title ASC) AS RowNumber,
				COUNT(*) OVER () AS TotalCount, *
		FROM	dbo.Album
		WHERE	ArtistId = @ArtistId
	)
	SELECT	*
	FROM	Album
	WHERE	RowNumber BETWEEN @FirstRow AND @LastRow

END
ELSE
BEGIN
	IF (@Id <> 0)
	BEGIN
		SET NOCOUNT ON

		SELECT * FROM Album
			WHERE AlbumId = @Id

	END
	ELSE
	BEGIN
		SET NOCOUNT ON

		IF (@PageSize = 0) SET @PageSize = (SELECT COUNT(*) FROM dbo.Album)

		SELECT	@FirstRow = ( @PageNumber - 1) * @PageSize + 1,
				@LastRow = (@PageNumber - 1) * @PageSize + @PageSize ;

		WITH Album  AS
		(
			SELECT	ROW_NUMBER() OVER (ORDER BY Title ASC) AS RowNumber,
					COUNT(*) OVER () AS TotalCount, *
			FROM	dbo.Album
		)
		SELECT	*
		FROM	Album
		WHERE	RowNumber BETWEEN @FirstRow AND @LastRow

	END
END

The third stored procedure you will create, GetTracks is for selecting Track(s).

CREATE PROCEDURE [dbo].[GetTracks]
(
	@Id INT = 0,
	@AlbumId INT = 0,
	@PageSize INT = 0,
	@PageNumber INT = 1
)
AS

DECLARE
	@FirstRow INT,
	@LastRow INT

IF (@AlbumId <> 0)
BEGIN

	IF (@PageSize = 0) SET @PageSize = (SELECT COUNT(*) FROM dbo.Track WHERE AlbumId = @AlbumId)

	SELECT	@FirstRow = ( @PageNumber - 1) * @PageSize + 1,
			@LastRow = (@PageNumber - 1) * @PageSize + @PageSize;

	WITH Track AS
	(
		SELECT	ROW_NUMBER() OVER (ORDER BY Name ASC) AS RowNumber,
				COUNT(*) OVER () AS TotalCount, *
		FROM	dbo.Track
		WHERE AlbumId = @AlbumId
	)
	SELECT	*
	FROM	Track
	WHERE	RowNumber BETWEEN @FirstRow AND @LastRow

END
ELSE
BEGIN
	IF (@Id <> 0)
	BEGIN
		SET NOCOUNT ON

		SELECT * FROM Track
			WHERE TrackId = @Id

	END
	ELSE
	BEGIN
		SET NOCOUNT ON

		IF (@PageSize = 0) SET @PageSize = (SELECT COUNT(*) FROM dbo.Track)

		SELECT	@FirstRow = ( @PageNumber - 1) * @PageSize + 1,
				@LastRow = (@PageNumber - 1) * @PageSize + @PageSize;

		WITH Track AS
		(
			SELECT	ROW_NUMBER() OVER (ORDER BY Name ASC) AS RowNumber,
					COUNT(*) OVER () AS TotalCount, *
			FROM	dbo.Track
		)
		SELECT	*
		FROM	Track
		WHERE	RowNumber BETWEEN @FirstRow AND @LastRow

	END
END

SQL Function

New Scalar-value function

Create this user defined function to add a computed AlbumCount column to the Artist Table.

CREATE FUNCTION [dbo].[CountAlbums](@ArtistId INT)
RETURNS INT
WITH SCHEMABINDING
AS BEGIN
    DECLARE @ArtistAlbumCount INT

    SELECT @ArtistAlbumCount = COUNT(*)
    FROM dbo.Album
    WHERE ArtistId = @ArtistId

    RETURN @ArtistAlbumCount
END

Now we need to bind the SQL Function to the Artist table. In the Object Explorer F8, expand the Tables node and Artist table node. Right click on the Columns folder and select New Column from the context menu. Enter AlbumCount under Column Name and int under Data Type. Navigate to the Column Properties tab, expand the Computed Column Specification and enter ([dbo].[CountAlbums]([ArtistId])) for the (Formula) value. Save the Artist table Ctrl+S.

new-computed-column

Data Models

Create a data model class for the Chinook data objects. In the Solution Explorer, right click on the Models folder and add a new file named ChinookModels.cs

MvcMobileApp/Models/ChinookModels.cs

using System;

namespace MvcMobileApp.Models
{
    public class AlbumModel
    {
        public int AlbumId { get; set; }
        public string Title { get; set; }
        public int ArtistId { get; set; }
    }

    public class ArtistModel
    {
        public int ArtistId { get; set; }
        public string Name { get; set; }
        public int AlbumCount { get; set; }
    }

    public class TrackModel
    {
        public int TrackId { get; set; }
        public string Name { get; set; }
        public int AlbumId { get; set; }
        public int MediaTypeId { get; set; }
        public int GenreId { get; set; }
        public string Composer { get; set; }
        public int Milliseconds { get; set; }
        public int Bytes { get; set; }
        public double UnitPrice { get; set; }
    }
}

Data Access

Create a data access class that can be used by any controller in our application to interface with the Chinook database. In the Solution Explorer, right click on the MvcMobileApp project and add a new class called Chinook. The result is a file named Chinook.cs in the root of our application.

MvcMobileApp/Chinook.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;

namespace MvcMobileApp
{
    using Models;

    public class Chinook
    {
        public static List GetAlbums(int artistId)
        {
            List albumList = new List();
            SqlConnection conn = new SqlConnection(Config.ChinookConnection);
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("GetAlbums", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@ArtistId", artistId);
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    albumList.Add(new AlbumModel()
                    {
                        AlbumId = Convert.ToInt32(dr["AlbumId"]),
                        Title = dr["Title"].ToString(),
                        ArtistId = Convert.ToInt32(dr["ArtistId"]),
                    });
                }
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                conn.Close();
            }
            return albumList;
        }

        public static List GetArtists()
        {
            //get all artists
            List artistList = GetArtists(0,1);
            return artistList;
        }

        public static List GetArtists(int pageSize, int pageNumber)
        {
            List artistList = new List();
            SqlConnection conn = new SqlConnection(Config.ChinookConnection);
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("GetArtists", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@PageSize", pageSize);
                cmd.Parameters.AddWithValue("@PageNumber", pageNumber);
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    artistList.Add(new ArtistModel()
                    {
                        ArtistId = Convert.ToInt32(dr["ArtistId"]),
                        Name = dr["Name"].ToString(),
                        AlbumCount = Convert.ToInt32(dr["AlbumCount"])
                    });
                }
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                conn.Close();
            }
            return artistList;
        }

        public static List GetTracks(int albumId)
        {
            List trackList = new List();
            SqlConnection conn = new SqlConnection(Config.ChinookConnection);
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("GetTracks", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@AlbumId", albumId);
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    trackList.Add(new TrackModel()
                    {
                        TrackId = Convert.ToInt32(dr["TrackId"]),
                        Name = dr["Name"].ToString(),
                        AlbumId = Convert.ToInt32(dr["AlbumId"]),
                        MediaTypeId = Convert.ToInt32(dr["MediaTypeId"]),
                        GenreId = Convert.ToInt32(dr["GenreId"]),
                        Composer = dr["Composer"].ToString(),
                        Milliseconds = Convert.ToInt32(dr["Milliseconds"]),
                        Bytes = Convert.ToInt32(dr["Bytes"]),
                        UnitPrice = Convert.ToDouble(dr["UnitPrice"]),
                    });
                }
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                conn.Close();
            }
            return trackList;
        }

        private class Config
        {
            static public String ChinookConnection { get { return WebConfigurationManager.ConnectionStrings["ChinookConnection"].ConnectionString; } }
        }

    }

}

Controller

CREATE a controller class for the cascading selects. In the Solution Explorer, right click on the Controllers folder and add a new empty MVC controller named ChinookController

add-controller

Below the public ActionResult Index() method, add these methods as shown below:

GetArtists() returns an json array of artists
GetAlbums() returns an json array of albums by artist id
GetTracks() returns an json array of tracks by album id

MvcMobileApp/Controllers/ChinookController.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace MvcMobileApp.Controllers
{
    public class ChinookController : Controller
    {
        //
        // GET: /Chinook/

        public ActionResult Index()
        {
            return View();
        }

        public ActionResult GetArtists()
        {
            var artistList = Chinook.GetArtists();
            return this.Json(artistList, JsonRequestBehavior.AllowGet);
        }

        public ActionResult GetAlbums(string id)
        {
            var albumList = Chinook.GetAlbums(Convert.ToInt32(id));
            return this.Json(albumList, JsonRequestBehavior.AllowGet);
        }

        public ActionResult GetTracks(string id)
        {
            var trackList = Chinook.GetTracks(Convert.ToInt32(id));
            return this.Json(trackList, JsonRequestBehavior.AllowGet);
        }

    }
}