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
Make sure you can Access your SQL Server [Configure Windows Firewall]
<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
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.
Create stored procedure GetAlbums for selecting Album(s).
Create stored procedure GetTracks for selecting Track(s).
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.
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.
Create stored procedure DeleteTrack for removing track records.