Chinook Web API Project

For this project, you will need to have access to the Chinook SQL Server Database.

1. Create a New Project

New ASP.NET MVC 4 Web Application - ChinookWebApi
New ASP.NET MVC 4 Web Application – ChinookWebApi

2. Select the Web API Project Template

New ASP.NET MVC 4 Project - Web API Project Template
New ASP.NET MVC 4 Project – Web API Project Template

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
Add New Item – Tracks.cs

namespace ChinookWebApi.Models
{
    public class Tracks
    {
        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 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.

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

namespace ChinookWebApi
{
    using Models;

    public class Chinook
    {
        public static void DeleteTrack(int trackId)
        {
            var conn = new SqlConnection(Config.ChinookConnection);
            conn.Open();
            var cmd = new SqlCommand("DeleteTrack", conn)
            {
                CommandType = CommandType.StoredProcedure
            };
            cmd.Parameters.AddWithValue("@Id", trackId);
            cmd.ExecuteNonQuery();
            conn.Close();
        }
        
        public static List Tracks(int trackId)
        {
            var trackList = new List();
            var conn = new SqlConnection(Config.ChinookConnection);
            conn.Open();
            var cmd = new SqlCommand("GetTracks", conn)
            {
                CommandType = CommandType.StoredProcedure
            };
            cmd.Parameters.AddWithValue("@Id", trackId);
            var dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                trackList.Add(new Tracks
                {
                    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"]),
                });
            }
            dr.Close();
            conn.Close();

            return trackList;
        }

        public static List TracksByAlbum(int albumId)
        {
            var trackList = new List();
            var conn = new SqlConnection(Config.ChinookConnection);
            conn.Open();
            var cmd = new SqlCommand("GetTracks", conn)
            {
                CommandType = CommandType.StoredProcedure
            };
            cmd.Parameters.AddWithValue("@Id", albumId);
            var dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                trackList.Add(new Tracks
                {
                    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"]),
                });
            }
            dr.Close();
            conn.Close();

            return trackList;
        }

        public static Tracks UpsertTrack(Tracks tracks)
        {
            var conn = new SqlConnection(Config.ChinookConnection);
            conn.Open();
            var cmd = new SqlCommand("UpsertTrack", conn)
            {
                CommandType = CommandType.StoredProcedure
            };
            // Return value as parameter
            SqlParameter returnValue = new SqlParameter("returnVal", SqlDbType.Int);
            returnValue.Direction = ParameterDirection.ReturnValue;
            cmd.Parameters.Add(returnValue);

            cmd.Parameters.AddWithValue("@AlbumId", tracks.AlbumId);
            cmd.Parameters.AddWithValue("@Bytes", tracks.Bytes);
            cmd.Parameters.AddWithValue("@Composer", tracks.Composer);
            cmd.Parameters.AddWithValue("@GenreId", tracks.GenreId);
            cmd.Parameters.AddWithValue("@MediaTypeId", tracks.MediaTypeId);
            cmd.Parameters.AddWithValue("@Milliseconds", tracks.Milliseconds);
            cmd.Parameters.AddWithValue("@Name", tracks.Name);
            cmd.Parameters.AddWithValue("@TrackId", tracks.TrackId);
            cmd.Parameters.AddWithValue("@UnitPrice", tracks.UnitPrice);
            cmd.ExecuteNonQuery();

            int id = Convert.ToInt32(returnValue.Value);
            tracks.TrackId = id;

            conn.Close();

            return tracks;
        }

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

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.

RouteConfig.cs
public static void RegisterRoutes(RouteCollection routes)
{
    routes.IgnoreRoute("{resource}.axd/{*pathInfo}");

    routes.MapRoute(
        name: "Custom1",
        url: "{controller}/{id}",
        defaults: new { id = UrlParameter.Optional }
    );

    routes.MapRoute(
        name: "Default",
        url: "{controller}/{action}/{id}",
        defaults: new { controller = "Home", action = "Index", id = UrlParameter.Optional }
    );
}

Tracks Controller

In Solution Explorer, right-click on the Controllers folder select Add New. Under Scaffolding options Select the API controller with empty read/write actions Template. Name it TracksController.

Add Controller - TracksController API controller with empty read/write actions
Add Controller – TracksController API controller with empty read/write actions

TracksController.cs
using System;
using System.Net;
using System.Net.Http;
using System.Web.Http;

namespace ChinookWebApi.Controllers
{
    using ChinookWebApi.Models;

    public class TracksController : ApiController
    {
        // GET api/tracks
        public HttpResponseMessage Get()
        {
            var value = Chinook.Tracks(0);
            return Request.CreateResponse(HttpStatusCode.OK, value);
        }

        public HttpResponseMessage Get(int id)
        {
            var value = Chinook.Tracks(id);
            return Request.CreateResponse(HttpStatusCode.OK, value);
        }

        public HttpResponseMessage Get(string albumId)
        {
            int id = Convert.ToInt32(albumId);
            var value = Chinook.TracksByAlbum(id);
            return Request.CreateResponse(HttpStatusCode.OK, value);
        }

        // POST api/tracks
        public HttpResponseMessage Post([FromBody]Tracks tracks)
        {
            var value = Chinook.UpsertTrack(tracks);
            return Request.CreateResponse(HttpStatusCode.OK, tracks);
        }

        // PUT api/tracks/5
        public HttpResponseMessage Put(int id, [FromBody]Tracks tracks)
        {
            tracks.TrackId = id;
            var value = Chinook.UpsertTrack(tracks);
            return Request.CreateResponse(HttpStatusCode.OK, tracks);
        }

        // DELETE api/tracks
        public HttpResponseMessage Delete([FromBody]int[] value)
        {
            foreach (int id in value)
            {
                Chinook.DeleteTrack(id);
            }
            return Request.CreateResponse(HttpStatusCode.OK);
        }

        // DELETE api/tracks/5
        public HttpResponseMessage Delete(int id)
        {
            Chinook.DeleteTrack(id);
            return Request.CreateResponse(HttpStatusCode.OK);
        }
    }
}

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.

Advanced Rest Client - PUT Tracks Json Test
Advanced Rest Client – PUT Tracks Json Test
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/insert
R – GET  read
U – PUT  update
D – DELETE

{
    "TrackId": 2918,
    "Name": "\"?\"",
    "AlbumId": 231,
    "MediaTypeId": 3,
    "GenreId": 19,
    "Composer": "",
    "Milliseconds": 2782333,
    "Bytes": 528227089,
    "UnitPrice": 1.99
}

You could also use a GET request: ~/api/tracks/2918 to retrieve Json to use.

Copy this example json payload into the Advanced Rest Client and set the request type to POST to create a new Track record. The new tracks object will be returned including the new TrackId.

POST Json Payload Example
{
    "Name": "\"_New Track\"",
    "AlbumId": 231,
    "MediaTypeId": 3,
    "GenreId": 19,
    "Composer": "",
    "Milliseconds": 3003222,
    "Bytes": 540772000,
    "UnitPrice": 0.99
}

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:

CorsHandler.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Net.Http;
using System.Threading.Tasks;
using System.Threading;
using System.Net;

namespace ChinookWebApi.Handlers
{
    public class CorsHandler : DelegatingHandler
    {
        const string Origin = "Origin";
        const string AccessControlRequestMethod = "Access-Control-Request-Method";
        const string AccessControlRequestHeaders = "Access-Control-Request-Headers";
        const string AccessControlAllowOrigin = "Access-Control-Allow-Origin";
        const string AccessControlAllowMethods = "Access-Control-Allow-Methods";
        const string AccessControlAllowHeaders = "Access-Control-Allow-Headers";

        protected override Task SendAsync(HttpRequestMessage request, CancellationToken cancellationToken)
        {
            bool isCorsRequest = request.Headers.Contains(Origin);
            bool isPreflightRequest = request.Method == HttpMethod.Options;
            if (isCorsRequest)
            {
                if (isPreflightRequest)
                {
                    HttpResponseMessage response = new HttpResponseMessage(HttpStatusCode.OK);
                    response.Headers.Add(AccessControlAllowOrigin, request.Headers.GetValues(Origin).First());

                    string accessControlRequestMethod = request.Headers.GetValues(AccessControlRequestMethod).FirstOrDefault();
                    if (accessControlRequestMethod != null)
                    {
                        response.Headers.Add(AccessControlAllowMethods, accessControlRequestMethod);
                    }

                    string requestedHeaders = string.Join(", ", request.Headers.GetValues(AccessControlRequestHeaders));
                    if (!string.IsNullOrEmpty(requestedHeaders))
                    {
                        response.Headers.Add(AccessControlAllowHeaders, requestedHeaders);
                    }

                    TaskCompletionSource tcs = new TaskCompletionSource();
                    tcs.SetResult(response);
                    return tcs.Task;
                }
                else
                {
                    return base.SendAsync(request, cancellationToken).ContinueWith(t =>
                    {
                        HttpResponseMessage resp = t.Result;
                        resp.Headers.Add(AccessControlAllowOrigin, request.Headers.GetValues(Origin).First());
                        return resp;
                    });
                }
            }
            else
            {
                return base.SendAsync(request, cancellationToken);
            }
        }
    }
}

Register the CorsHandler in the Application_Start() method.

Global.asax.cs
using System;
using System.Web.Http;
using System.Web.Mvc;
using System.Web.Optimization;
using System.Web.Routing;

namespace ChinookWebApi
{
    using Handlers;

    public class WebApiApplication : System.Web.HttpApplication
    {
        protected void Application_Start()
        {
            AreaRegistration.RegisterAllAreas();

            WebApiConfig.Register(GlobalConfiguration.Configuration);
            FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
            RouteConfig.RegisterRoutes(RouteTable.Routes);
            BundleConfig.RegisterBundles(BundleTable.Bundles);

            GlobalConfiguration.Configuration.MessageHandlers.Add(new CorsHandler()); 
        }
    }
}

To be continued … Validation and Error Handling

This project is available for browsing and download at GitHub:
https://github.com/jimfrenette/ChinookWebApi

Resources

Chinook ASP.NET MVC 4 Web Application

For this tutorial, you will need:

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

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

New Scalar-value 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

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

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

  1. GetArtists() returns an json array of artists
  2. GetAlbums() returns an json array of albums by artist id
  3. 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);
        }

    }
}

DotNetNuke Modal PopUps

Task 1, create a jQuery UI dialog to confirm a postback action invoked by a ASP.NET button control.

In your module’s ascx file, add a button control and a div to contain you dialog.

<asp:Button ID="btnDelete" runat="server"
	AutoPostBack="false"
	ButtonType="LinkButton"
	CssClass="main_button"
	OnClick="btnDelete_Click"
	Text="Delete"
	UseSubmitBehavior="false">
</asp:Button>

<div id="dlgDeleteConfirm" title="Confirm">
	<div class="confirmDialog">Are you sure you want to delete this?</div>
</div>

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

private void Page_Load(object sender, System.EventArgs e)
{
	ClientScriptBlock();
}

private void ClientScriptBlock()
{
	Type csType = this.GetType();
	ClientScriptManager cs = Page.ClientScript;
	if (!cs.IsStartupScriptRegistered(csType, "Vars"))
	{
		StringBuilder sb = new StringBuilder();
		sb.Append("");
		cs.RegisterClientScriptBlock(csType, "Vars", sb.ToString());
	}
}

protected void btnDelete_Click(object sender, EventArgs e)
{
 //delete method followed by redirect or whatever on success
}
.confirmDialog {
	padding: 15px 15px 0 15px;
	font-weight: bold;
}

jQuery – I had trouble getting .dnnConfirm to handle the postback with it’s isButton option, so I used a standard jQuery UI Dialog instead.

jQuery(document).ready(function ($) {
	$("#dlgDeleteConfirm").dialog({
		autoOpen: false,
		modal: true,
		width: 300,
		height: 140,
		resizable: false,
		dialogClass: "dnnFormPopup",
		buttons: {
			"Yes": function() {<%=this.Page.ClientScript.GetPostBackEventReference(new PostBackOptions(this.btnDelete))%>;},
			"No": function() {$(this).dialog('close');}
		}
	});
	$("#" + btnDelete_ClientID).click(function () {
		$("#dlgDeleteConfirm").dialog('open'); return false;
	});
});

NOTE: You need to make sure that DNN loads the jQuery UI in either OnInit or OnLoad in your Module Controls code behind.

protected override void OnLoad(EventArgs e)
{
    base.OnLoad(e);

    //register jQuery-UI - no need to register jQuery
    //since jQuery-UI registration method will handle this 
    DotNetNuke.Framework.jQuery.RequestUIRegistration();
}

Task 2, create a jQuery UI dialog to contain an Edit form

Resources

DotNetNuke Module Control Options

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.

You could also add a new parent ascx file all together:

Add a New Web User Control

References:
Copy Edit.ascx code behind using ... to overwrite the new code behind using ...

Replace class inheritence
 : System.Web.UI.UserControl
With
 : [MyModuleName]ModuleBase

EXAMPLE
public partial class EditMyAsset : System.Web.UI.UserControl

public partial class EditMyAsset : MyModuleNameModuleBase

In App_LocalResources
Copy Edit.ascx.resx To EditMyAsset.ascx.resx

Add Module Definition:
- Open Edit Extension Form
  Host | Extensions | MyModuleName > Edit Extension
- Navigate to
  Module Definitions | Add Module Control

  Add a Key "EditMyAsset"
  Select the Source path "DesktopModules/MyModuleName/EditMyAsset.ascx"
  Select Type: "Edit"

- Open MyModuleName.dnn
  Copy the node where controlKey = "Edit" //moduleDefinitions/moduleControls/moduleControl
  Change the controlKey in the new node to "EditMyAsset"
  Change the controlSrc in the new node to "DesktopModules/MyModuleName/EditMyAsset.ascx"

Dynamically Loading Web User Controls

If this is View Control that will not have form postbacks, then it is okay to dynamically load the Web User Controls.

aspx code
<%@ Control language="C#" Inherits="DotNetNuke.Modules.MyModuleName.View" AutoEventWireup="false"  Codebehind="View.ascx.cs" %>

<asp:PlaceHolder id="phControls" runat="server"></asp:PlaceHolder>
aspx.cs code
using System;
using DotNetNuke.Common;
using DotNetNuke.Services.Exceptions;
using DotNetNuke.Entities.Modules;
using DotNetNuke.Entities.Modules.Actions;
using DotNetNuke.Services.Localization;
using DotNetNuke.Security;


namespace DotNetNuke.Modules.MyModuleName
{

    /// -----------------------------------------------------------------------------
    /// 
    /// The ViewMyModuleName class displays the content
    /// 
    /// -----------------------------------------------------------------------------
    public partial class View : MyModuleNameModuleBase, IActionable
    {

        #region Event Handlers

        override protected void OnInit(EventArgs e)
        {
            InitializeComponent();
            base.OnInit(e);
            DotNetNuke.Framework.jQuery.RequestDnnPluginsRegistration();
        }

        private void InitializeComponent()
        {
            this.Load += new System.EventHandler(this.Page_Load);
        }


        /// -----------------------------------------------------------------------------
        /// 
        /// Page_Load runs when the control is loaded
        /// 
        /// -----------------------------------------------------------------------------
        private void Page_Load(object sender, System.EventArgs e)
        {
            try
            {
                //load the User Controls based on QueryString params
                string query = Request.QueryString["q"];

                switch (query)
                {
                    case "album-grid":
                        SetControl("Controls/AlbumGrid.ascx");
                        break;
                    case "artist-grid":
                        SetControl("Controls/ArtistGrid.ascx");
                        break;
                    default:
                        SetControl("Controls/Default.ascx");
                        break;
                }
            }
            catch (Exception exc) //Module failed to load
            {
                Exceptions.ProcessModuleLoadException(this, exc);
            }

        }

        private void SetControl(string ControlPath)
        {
            PortalModuleBase b = (PortalModuleBase)LoadControl(ControlPath);
            b.ModuleConfiguration = this.ModuleConfiguration;
            b.ID = System.IO.Path.GetFileNameWithoutExtension(ControlPath);
            phControls.Controls.Add(b);
        }

        #endregion

        #region Optional Interfaces

        public ModuleActionCollection ModuleActions
        {
            get
            {
                ModuleActionCollection Actions = new ModuleActionCollection();
                Actions.Add(GetNextActionID(), Localization.GetString("EditModule", this.LocalResourceFile), "", "", "", EditUrl(), false, SecurityAccessLevel.Edit, true, false);
                return Actions;
            }
        }

        #endregion

    }

}

Additional Resources

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