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.

Download and Installation

Chinook Database

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

Published by

Jim Frenette

Web Developer – views here are my own except those taken from people more clever than me.

Loading Disqus Comments ...
Loading Facebook Comments ...