Data Models

In Solution Explorer, right-click the Models folder and select Add > New Item (Ctrl+Shift+A) > Select Class and save it as Tracks.cs. Then add the data type objects to the Tracks class.
Add New Item – Tracks.cs

Data Access

Create a Chinook class for database access. In the Solution Explorer, right click on the ChinookWebApi project and add a new class named Chinook.cs. To see the respective stored procedure SQL code, refer to Chinook SQL Server Database.

Register Routes

In order to follow Web API design best practices, we need to edit the routes so action names are not required. Open ~\App_Start\RouteConfig.cs and add a route mapping above the existing Default mapping so your RegisterRoutes method looks like this.

Now would be a good time to select F5 and debug the app. Load the url to request a track and see what the method we added returns, for example http://localhost:65374/api/tracks/1. If you want to return json, you could use the Advanced Rest Client Chrome App with the Content-type header set to application/json.

PUT Json Payload Example

Copy this example json payload into the Advanced Rest Client and set the request type to PUT to update a Track record. The key is making sure the HTTP request type is set for the type of CRUD operation you wish to perform.C – POST create/insertR – GET readU – PUT updateD – DELETE

POST Json Payload Example

With the HTTP request type set to DELETE, you can remove a single Track record with a request like:~/api/tracks/2918
or to Delete multiple records, a request like this will work:~/api/tracks
with a payload of TrackId’s to remove:

[ 2918, 2919, 2920 ]

Cross-Origin Resource Sharing (CORS)

To enable CORS in the Web API and allow JavaScript XMLHttpRequests from a browser in another domain, Carlos Figueira’s MSDN blog shows us how to create a global message handler for all controllers and actions in the application. Right-click the ChinookWebApi project and add a new folder named Handlers with a new CorsHandler class in it:

* 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”.

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.

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

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

SQL Function

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

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

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.

I am looking forward to the latest release of Visual Studio (version 2012) in a few days. My favorite web app library, ASP.NET MVC, will now render HTML 5 by default. And now, Microsoft is including jQuery Mobile in the MVC 4 project templates.

In you ascx code behind, create a method to create a client script block, you call can call on page load. This modular approach of passing server vars to the client is one I like the best. With this method you can modify the code to pass in arguments and also include them when writing your script block, etc..

Resources

You could use a Multi-View inside of your Edit.ascx to create a dynamic way to toggle visible form/content objects and their code within a single Web User Control ascx and it’s ascx.cs code file. This is a popular approach when working inside the Edit control since dynamically loaded sub-controls will not have persisted postback data.

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.

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.

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.

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: