Chinook SQL Server Database

The Chinook Database is a sample database available for SQL Server, Oracle, MySQL, PostgreSQL, SQL Server Compact, SQLite and more. It is ideal for demos, application prototypes and perhaps even a digital media store application. It includes tables for artists, albums, media tracks, invoices and customers.

Chinook Database Download and Installation

Chinook Database
Chinook Database – Documentation

Make sure you can Access your SQL Server [Configure Windows Firewall]

Connection String

<connectionStrings>
 <add name="ChinookConnection" providerName="System.Data.SqlClient" connectionString="Data Source=VGN-FW290\SQLEXPRESS;Initial Catalog=Chinook;Integrated Security=True" />
 <!-- using account without SQL Express
 <add name="ChinookConnection" providerName="System.Data.SqlClient" connectionString="Data Source=VGN-FW290;Initial Catalog=Chinook;Persist Security Info=True;User ID=sa;Password=***********" />
 -->
</connectionStrings>

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

SQL Stored Procedures

SQL Server 2012 Express Management Studio

Open up SQL Server Management Studio and connect to the server that is defined in the 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”.

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.

dbo.GetArtists

Create stored procedure GetAlbums for selecting Album(s).

dbo.GetAlbums

Create stored procedure GetTracks for selecting Track(s).

dbo.GetTracks

SQL Function

New Scalar-value function

An efficient way to return an Album Count column in each row is by using a function. This will come in handy when paginating Album records in the UI. Create this user defined function to add a computed AlbumCount column to the Artist Table.

dbo.CountAlbums

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).

Create stored procedure UpsertTrack for creating and updating track records.

dbo.UpsertTrack

Create stored procedure DeleteTrack for removing track records.

dbo.DeleteTrack

jQuery – MSSQL Pagination and Data Templates

Here is a tutorial to show you how to paginate JSON data in the client using jQuery, jQuery Templates plugin, ASP.NET and SQL Server.

For this tutorial, you will need:

From the Chinook archive, copy
Chinook_SqlServer.sql
Chinook_SqlServer_AutoIncrementPKs.sql
CreateSqlServer.bat

to your MSSQL Data folder.

i.e., C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data

Run the CreateSqlServer.bat

Open up Visual Studio | Server Explorer (Ctrl+Alt+S)

Right Click on “Data Connections” and select “Add Connection…”

For the Data source, select “Microsoft SQL Server”

Select the SQL Server Name. i.e., [COMPUTER NAME]\SQLEXPRESS

Connect to a database, Select the Chinook database

Create the sp_ArtistsByPage.sql stored procedure:

In Visual Studio, Create a new project (Ctrl+Shift+N)

Select ASP.NET Empty Web Application (This template is available when .NET Framework4 is selected)

Add a database Connection String in the web.config similar to this one. Note: VGN-FW290 is the name of my computer. Replace this with yours obviously. Tip: in Server Explorer, Expand the Data Connections node and right-click on the Chinook connection. Select properties(F4) and copy the Connection String.


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

Copy the jQuery .js file to the Scripts folder of the application. Then right click on the Scripts folder and select add existing item (Alt+Shift+A). Browse to the folder you just copied jQuery to and select it so it is part of the project.

Repeat the process for the jQuery Pager and Templates plugins, (jquery.pager.js and jquery.tmpl.min.js). Use either the uncompressed or minified (*.min.js) versions.

Drag the jQuery library Template plugin files from the Solution Explorer into the Default.aspx source to the line just before the closing </form> tag.

Add the following css to \Styles\screen.css, add it to the project and drag into the source before the closing </head> tag.

body 
{
	margin:5px;
	background:#FFF;
	font-family: Arial, Sans-Serif;
	font-size:90%;
}
a {color:#369;}
a.active 
{
	color:#000;
	text-decoration:none;
}
a:hover {
	color:#FFF;
	background:#369;
	text-decoration:none;
}
a.disabled {color:#ADADAD;}
a.disabled:hover {
	color:#ADADAD;
	background:none;
}
h1, h2, h3 {
	margin:.8em 0 .2em 0;
	padding:0;
}
p {
	margin:.4em 0 .8em 0;
	padding:0;
}
img {
	margin:0;
	border:0;
}
#artists {position: relative; width:700px;}
#artistTable {border-collapse:collapse; margin-top:10px; clear:both; }
#artistList tr.highlight {background-color: #FDF5CE;} 
#artistList td {padding: 2px 5px 2px 5px;} 
#loading {
	width: 24px;
	height:24px;
	background-image: url('../Styles/ajax-loader.gif');
}
#pager ul.pages
{
	display: block;
	border: none;
	text-transform: uppercase;
	font-size: 10px;
	padding: 0;
}
#pager ul.pages li
{
	list-style: none;
	float: left;
	border: 1px solid #888;
	text-decoration: none;
	margin: 0 5px 0 0;
	padding: 5px;
	background-color: #FFF;
}
#pager ul.pages li:hover
{
	border: 1px solid #00376F;
}
#pager ul.pages li.pgEmpty
{
	border: 1px solid #CCC;
	color: #AAA;
	cursor: default;
}
#pager ul.pages li.pgCurrent
{
	border: 1px solid #003F7E;
	color: #042CCA;
	font-weight: 700;
	background-color: #dadada;
}
#pageSize
{
    position: absolute;
    top: -10px;
    right: 0;
	width:80px;
	text-align:right;
}
#pageSize label 
{
	font-size:10px;
	padding: 0 2px 0 0;
}
#pageSize select {width:50px;}

Default.aspx should now look something like this:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="PaginationSQL._Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
	<title></title>
	<link href="Styles/screen.css" rel="stylesheet" type="text/css" />
</head>
<body>
	<form id="form1" runat="server">
	<div> </div>
	<script src="Scripts/jquery-1.4.4.min.js" type="text/javascript"></script>
	<script src="Scripts/jquery.pager.js" type="text/javascript"></script>
	<script src="Scripts/jquery.tmpl.js" type="text/javascript"></script>
	</form>
</body>
</html>
Solution Explorer

Add a Generic Handler to the project and name it Json.ashx. Go to www.preloaders.net and download a preloader animation. Copy it to the Styles folder and add it to the project. Your Solution Explorer tree should look something like this one shown at left.

C# code for the handler

using System;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Web;
using System.Web.Configuration;
using System.Web.Script.Serialization;

namespace PaginationSQL
{
    /// 
    /// Summary description for Json
    /// 
    public class Json : IHttpHandler
    {
        private string ChinookConnection = WebConfigurationManager.ConnectionStrings["ChinookConnection"].ConnectionString;
        private string data = "";
        private int page_number = 1;
        private int page_size = 25;
        private int total_count = 0;

        public void ProcessRequest(HttpContext context)
        {
            page_size = Convert.ToInt32(context.Request.QueryString["page_size"]);
            page_number = Convert.ToInt32(context.Request.QueryString["page_number"]);
            
            StringBuilder json = new StringBuilder();

            string artistId = "";
            string name = "";

            DataSet artist = GetArtistsByPage(page_size, page_number);
            foreach (DataRow dr in artist.Tables[0].Rows)
            {
                artistId = dr["ArtistId"].ToString();
                name = JavaScriptStringEncode(dr["Name"].ToString());

                json.Append("{");
                json.Append("\"artistId\":\"" + artistId + "\",");
                json.Append("\"artistName\":\"" + name + "\"");
                json.Append("},");

                total_count = Convert.ToInt32(dr["TotalCount"]);
            }
            data = "{\"items\":[";
            if (json.Length != 0)
            {
                //remove trailing comma since we can't determine if last record during the append
                //data += "[" + json.ToString(0, json.Length - 1) + "]";
                data += json.ToString(0, json.Length - 1);
            }
            data += "],\"page_number\":" + page_number + ",\"page_size\":" + page_size + ",\"total_count\":" + total_count + "}";

            context.Response.Clear();
            context.Response.Cache.SetCacheability(HttpCacheability.NoCache);
            context.Response.ContentType = "application/json;charset=utf-8";
            context.Response.Flush();
            context.Response.Write(data);
        }

        public bool IsReusable
        {
            get
            {
                return false;
            }
        }

        //if your app has more data access methods, move this into a
        //data access layer class, such as Dal.cs
        public DataSet GetArtistsByPage(int pageSize, int pageNumber)
        {
            SqlConnection con = new SqlConnection(ChinookConnection);
            SqlCommand cmd = new SqlCommand("ch_ArtistsByPage", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@pageSize", pageSize);
            cmd.Parameters.AddWithValue("@pageNumber", pageNumber);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds, "Artists");
            return ds;
        }

        //if your app works with json elsewhere, move this into
        //another class - perhaps Utils.cs
        public string JavaScriptStringEncode(string message)
        {
            if (String.IsNullOrEmpty(message))
            {
                return message;
            }
            StringBuilder builder = new StringBuilder();
            JavaScriptSerializer serializer = new JavaScriptSerializer();
            serializer.Serialize(message, builder);
            return builder.ToString(1, builder.Length - 2); // remove first + last quote
        }

    }
}

Finally, add this template and jQuery page specific javascript at the bottom of the page above the closing </form> tag. Press F5 to test the web application.


<script id="artistTmpl" type="text/x-jquery-tmpl">
	<tr>
		<td align="right" valign="top">${artistId}</td>
		<td>${artistName}</td>
	</tr>
</script>
<script type="text/javascript">
	$(document).ready(function () {

		$("#loading").hide();

		var pageIndex = 1, pageSize = 10, pageCount = 0;
		getArtists(pageIndex);
		$("#pageSize select").change(function () {
			pageIndex = 1
			pageSize = $(this).val();
			getArtists(pageIndex);
		});

		function getArtists(index) {
			var query = "Json.ashx?page_number=" + index + "&page_size=" + pageSize;
			pageIndex = index;
			$("#artistList")
			.fadeOut("medium", function () {
				$("#loading").show()
				$.ajax({
					dataType: "json",
					url: query,
					jsonp: "$callback",
					success: showArtists
				});
			});
		}

		function showArtists(data) {
			pageCount = Math.ceil(data.total_count / pageSize),
			artists = data.items;
			$("#pager").pager({ pagenumber: pageIndex, pagecount: pageCount, buttonClickCallback: getArtists });
			$("#artistList").empty()
			$("#artistTmpl").tmpl(artists).appendTo("#artistList")
			$("#loading").hide().find("div").fadeIn(4000).end()
			$("#artistList").fadeIn("medium")
			$("#artistList tr").hover(
				function () {
					$(this).addClass("highlight");
				},
				function () {
					$(this).removeClass("highlight");
				});
		}

	});
</script>

This project is available for browsing and download at GitHub:
[PaginationSQL @GitHub]

Arrays and Lists in SQL Server 2008

I sure hope this article will help solve a problem for me: http://www.sommarskog.se/arrays-in-sql-2008.html” . It sure looks promising since the opening paragraph pretty much asks my question and the remainder of the article addresses it with solutions. I would like to pass an array of id’s as a single parameter to a stored procedure. Another solution to consider is passing the id’s as an XML string, and then parse the XML into a temp table to join against, or a query against the XML directly using SP_XML_PREPAREDOCUMENT and OPENXML.

Stay tuned and I will let you know how it turns out…

UPDATED Jan 31, 2011

Well, I decided to code it using the latter method described above. Here is some sample code to illustrate:

C#
//create a list of id's for the XmlSerializer
List ids = new List();
ids.Add(109);
ids.Add(120);
ids.Add(203);

//create Xml doc from list to pass id's into sproc as a single param
XmlSerializer xs = new XmlSerializer(typeof(List));
MemoryStream ms = new MemoryStream();
xs.Serialize(ms, ids);
string idXml = UTF8Encoding.UTF8.GetString(ms.ToArray());
idXml created from the above c# code:
<?xml version="1.0" ?> 
<ArrayOfInt xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
	<int>109</int> 
	<int>120</int> 
	<int>203</int> 
</ArrayOfInt>

Stored Procedure