Working with Lists

Suppose you have a List that you want to make another List from? This could be useful for an API when serializing Json from an object prior to sending it to a mobile app.

public class ListExample
{
    public static List GetAuthorSelect()
    {
        List authorSelect = new List();
        List author = new Data.AccessLayer.GetAuthors();
        {
            //create and copy the items we want to into a light list
            authorSelect = author.ConvertAll(x => new AuthorSelect
            {
                Id = x.Id,
                Name = x.Name
            });
        }
        return authorSelect;
    }

    //this method shows the equivalent using the traditional for loop
    public static List GetAuthorSelectLoop()
    {
        List authorSelect = new List();
        List author = new Data.AccessLayer.GetAuthors();
        if (author != null)
        {
            for (int i = 0; i < author.Count; i++)
            {
                authorSelect.Add(new AuthorSelect()
                {
                    Id = author[i].Id,
                    Name = author[i].Name
                });
            }
        }
        return authorSelect;
    }
}

public class AuthorSelect
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class Author
{
    public int Id { get; set; }
    public string Slug { get; set; }
    public string Name { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Nickname { get; set; }
    public string Url { get; set; }
    public string Description { get; set; }
}

List<T>.ConvertAll<TOutput> Method

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);
        }

    }
}

C# Strip HTML and Attributes in MVC

If you are receiving this error message when posting content that contains HTML:

A potentially dangerous Request.Form value was detected from the client

Add ValidateInput(false) to your controller’s HttpPost method:

[HttpPost, ValidateInput(false)]

Take a look at this blog article for the class that does the HTML strip work, http://www.dijksterhuis.org/safely-cleaning-html-with-strip_tags-in-csharp/.

In my implementation, the list of allowed tags is a string in the web.config:

<configuration>
	<appSettings>
		<add key="HtmlAllowed" value="a,b,br,em,i,p,strong"/>
	</appSettings>
</configuration>

The settings class contains this line to set the HtmlAllowed web.config appSetting to a char array:

public static string[] HtmlAllowed { get { return ConfigurationManager.AppSettings["HtmlAllowed"].Split(",".ToCharArray()); } }

Here is an example of a call to the method to strip HTML posted from a form input named “MyTextarea” while passing in the allowed tags array parameter in the second argument:

StripHtml.StripTagsAndAttributes(collection["MyTextarea"].Trim(), Settings.HtmlAllowed );

Source code

using System;
using System.Text.RegularExpressions;

namespace StripHTML
{
    class MainClass
    {
        
        private static string ReplaceFirst(string haystack, string needle, string replacement)
        {
            int pos = haystack.IndexOf(needle);
            if (pos < 0) return haystack;
            return haystack.Substring(0,pos) + replacement + haystack.Substring(pos+needle.Length);
        }

        private static string ReplaceAll(string haystack, string needle, string replacement)
        {
             int pos;
             // Avoid a possible infinite loop
             if (needle == replacement) return haystack;
              while((pos = haystack.IndexOf(needle))>0)
                       haystack = haystack.Substring(0,pos) + replacement + haystack.Substring(pos+needle.Length);
                        return haystack;
        }       

        public static string StripTags(string Input, string[] AllowedTags)
        {
            Regex StripHTMLExp = new Regex(@"(<\/?[^>]+>)");
            string Output = Input;

            foreach(Match Tag in StripHTMLExp.Matches(Input))
            {
                string HTMLTag = Tag.Value.ToLower();
                bool IsAllowed = false;
                
                foreach(string AllowedTag in AllowedTags)
                {
                    int offset = -1;

                    // Determine if it is an allowed tag 
                    // "" , "');
                    if (offset!=0) offset = HTMLTag.IndexOf('<'+AllowedTag+' ');
                    if (offset!=0) offset = HTMLTag.IndexOf(" m.Groups[1].Value + "href..;,;.." + m.Groups[2].Value;
            MatchEvaluator ClassMatch = m => m.Groups[1].Value + "class..;,;.." + m.Groups[2].Value;
            MatchEvaluator UnsafeMatch = m => m.Groups[1].Value + m.Groups[4].Value;
            
            /* Allow the "href" attribute */
            Output = new Regex("()").Replace(Output,HrefMatch);

            /* Allow the "class" attribute */
            Output = new Regex("()").Replace(Output,ClassMatch);

            /* Remove unsafe attributes in any of the remaining tags */
            Output = new Regex(@"(<.*) .*=(\'|\""|\w)[\w|.|(|)]*(\'|\""|\w)(.*>)").Replace(Output,UnsafeMatch);

            /* Return the allowed tags to their proper form */
            Output = ReplaceAll(Output,"..;,;..", "=");
            
            return Output;
        }
    }
}

MVC 3 RSS Reader

Model

public class Feed
{
	public string Title { get; set; }
	public string Link { get; set; }
	public string Description { get; set; }
}

Controller

public ActionResult Index()
{

	XDocument feedXML = XDocument.Load("http://feeds.haacked.com/haacked");

	//select out a collection of anonymous types from RSS feed
	var feedCollection = from rss in feedXML.Descendants("item")
				select new Feed
				{
					Title = rss.Element("title").Value,
					Link = rss.Element("link").Value,
					Description = rss.Element("description").Value
				};

    return View(feedCollection);
}

View

@model IEnumerable<RssReader.Models.Feed> 
@{
	ViewBag.Title = "Index";
}

<h2>Index</h2>

<ul>
@foreach (var item in Model)
{
	<li>@item.Title</li>

}
</ul>

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