Skip to content

Using SQL Management Studio Execution Plan

Its been a long while since my last programming post, but I have been doing a lot of SQL work on the backend over the last few months.

I had been told on Monday by a colleague I had rarely spoken to about using the SQL Profiler a lot more as it involves timing individual SQL queries/executions in the SQKL database, it is soemthing I always oversee. However trying to use this relatively unknown tool that I keep forgetting on a Monday morning didn’t work, it was perhaps I did a SQL database upgrade to 2014 but the dlls were missing.

Instead he told me to use the Execution plan, what is this? It is a detailed overview of the components of an SQL statement you wrote which analyses the cost % ratio of the total execution query, which I find equally as good as the SQL Profiler. See the screenshot below.

Execution Plan

As you can see in the screenshot diagram, the last query key lookup clustered index is suggested! Yes it makes suggestions to adding the query, in this situation it is to add a clustered index on several of the fields for a particular table to potentially improve the speed of the lookup of this complex query! Thanks!!!


Scale mobile screen to size without zoom

Heres a useful bit of code taken from David Walsh ever so useful website that is a metag to to prevent any web pages on the mobile from zooming in and out from within its defined scale.


<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1, user-scalable=0"/> <!--320-->

That’s as easy as it gets. According to Mozilla, mobile browsers are generally set to a size of 320 or 480. The maximum scale keeps the scale settings when the user switches from portrait to landscape view

My Super Generic Webservice DataTable to serialisable XML string method call Part 2

Further upon the super generic webservice method I wrote, on the web service side end, you can obtain the Stream fairly easily and convert to a datatable, here is how: –

public string getXMLStream(Stream data)
    // Convert Stream Data to StreamReader
    StreamReader reader = new StreamReader(data);

    // Read StreamReader data as String
    string xmlString = reader.ReadToEnd();
    // Convert to DataTable from XML String
    DataTable dataTable = ConvertXMLToDataTable(xmlString);

    string returnValue = xmlString;
The rather useful converter is also shown here.

public DataTable ConvertXMLTODataTable(string xmlData)
    StringReader reader = new StringReader(xmlData);
    DataSet dataSet = new DataSet();
    schemaName = dataSet.DataSetName;
    return dataSet.Tables[0];

My Super Generic Webservice DataTable to serialisable XML string method call

OK I couldn’t hold back as to how super this C# method I wrote yesterday and would like you guys to know about.

Essentially what it does is that it reads in an SQL database tables, converts ii into a DataTable using SQL Data Adapter then the very clever bit is that it convert it to a MemorySteam that is XML serialisable over the HTTP to WCF Data Services for it to be received on the other end as a Stream and serialised back to DataTable again! Its fantastic, and its so generic, you can use this time and time again. Hope its useful for you!

public string Add(string webServiceCall, string sourceTable, string destinationTable)
 // Restful service URL
 string url = readServiceUri().ToString() + webServiceCall;
// declare ascii encoding
 ASCIIEncoding encoding = new ASCIIEncoding();
 string strResult = string.Empty;
 string xmlResult = "";
DataSet dataSet = new DataSet();
 DataTable dataTable = new DataTable();
 dataTable.TableName = destinationTable;
 using (SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString))
 SqlCommand cmd = new SqlCommand("SELECT * FROM " + sourceTable, conn);
cmd.CommandType = CommandType.Text;
 SqlDataAdapter adapter = new SqlDataAdapter();
 adapter.SelectCommand = cmd;
 catch (Exception ex)
 throw new Exception("ERROR " + ex);
// Important logical conversion to XML string
 using (MemoryStream memoryStream = new MemoryStream())
 using (TextWriter streamWriter = new StreamWriter(memoryStream))
 XmlSerializer xmlSerializer = new XmlSerializer(typeof(DataSet));
 xmlSerializer.Serialize(streamWriter, dataSet);
 xmlResult = Encoding.UTF8.GetString(memoryStream.ToArray());
string postData = xmlResult.ToString();
 // Convert xmlstring to byte using ascii encoding
 byte[] data = encoding.GetBytes(postData);
 // Declare httpwebrequet wrt url defined above
 HttpWebRequest webRequest = (HttpWebRequest)WebRequest.Create(url);
 // Set method as post
 webRequest.Method = "POST";
 // Set content type
 webRequest.ContentType = "application/x-www-form-urlencoded";
 // Set content length
 webRequest.ContentLength = data.Length;
 // Set timeout
 webRequest.Timeout = 60000;
 // Get stream data out of webrequest object
 Stream newStream = webRequest.GetRequestStream();
 newStream.Write(data, 0, data.Length);
 // Declare & read response from service
 HttpWebResponse webResponse = (HttpWebResponse)webRequest.GetResponse();
 if (webResponse.StatusCode == HttpStatusCode.OK)
 // set utf8 encoding
 Encoding enc = System.Text.Encoding.GetEncoding("utf-8");
 // read response stream from response object
 StreamReader loResponseStream =
 new StreamReader(webResponse.GetResponseStream(), enc);
 // read string from stream data
 strResult = loResponseStream.ReadToEnd();
 // close the stream object
 // close the response object
 // below steps remove unwanted data from response string
 strResult = strResult.Replace("</string>", "");
if (strResult.Contains("OK"))
 return "OK";
 return "FAIL";
 return "FAIL";
 catch (Exception ex)
 throw new Exception("ERROR " + ex);

JSON to HTML JQuery Converter

After fumbling to find a JSON convert to HTML using JQuery, I have finally found useful piece of code for reading a list of JSON objects and sets it to a HTML table for output. It is useful for calling AJAX in JQuery for MVC C# controllers which returns a JSON object and in return the AJAX can translate the JSON object to HTML. Here's the code.

var myList = [{"name" : "abc", "age" : 50}, {"age" : "25", "hobby" : "swimming"}, {"name" : "xyz", "hobby" : "programming"}]; // Builds the HTML Table out of myList. function buildHtmlTable(selector) { var columns = addAllColumnHeaders(myList, selector); for (var i = 0 ; i < myList.length ; i++) { var row$ = $('<tr/>'); for (var colIndex = 0 ; colIndex < columns.length ; colIndex++) { var cellValue = myList[i][columns[colIndex]]; if (cellValue == null) { cellValue = ""; } row$.append($('<td/>').html(cellValue)); } $(selector).append(row$); } } // Adds a header row to the table and returns the set of columns. // Need to do union of keys from all records as some records may not contain // all records function addAllColumnHeaders(myList) { var columnSet = []; var headerTr$ = $('<tr/>'); for (var i = 0 ; i < myList.length ; i++) { var rowHash = myList[i]; for (var key in rowHash) { if ($.inArray(key, columnSet) == -1){ columnSet.push(key); headerTr$.append($('<th/>').html(key)); } } } $(selector).append(headerTr$); return columnSet; }​


<body onLoad="buildHtmlTable('#excelDataTable')">
<table id="excelDataTable" border="1">

Use AppSettingsReader!

This is something that is useful and is a very general tip,m you can use the AppSettingsReader from System.Configuration to read a key value set in the web.config.

 System.Configuration.AppSettingsReader settingsReader =
 new AppSettingsReader();

 //Get your key from config file!

 string key = (string)settingsReader.GetValue("AValue",

//This is saved here in the web.config

 <add key="AValue" value="hereismyvalue" />

This is very useful for using the same string on many Controller classes for example!

Ambiguous Error in ViewModel

If you ever receive the common MVC error when converting a returning object to the correct type which occurs during runtime say for a page called OrderSummary.

The current request for action ‘OrderSummary’ on controller type ‘HomeController’ is ambiguous between the following action methods:
System.Web.Mvc.ActionResult OrderSummary() on type WebApplication.Controllers.HomeController
System.Web.Mvc.ActionResult OrderSummary(System.Collections.Generic.List`1[WebApplication.Models.OrderViewModel]) on type WebApplication.Controllers.HomeController

The problem here is that the object OrderViewModel is not being passed correctly as a list of enumerable type that the View Model can read in, you can set this like so: –

var convertedList = (from db in onlineOrders.AsEnumerable()
 select new Order()
 Id = Convert.ToInt32(db["Id"]),
 ItemID = Convert.ToInt32(db["ItemID"]),
 CustomerID = Convert.ToInt32(db["CustomerID"]),
 DateOrdered = (emp["DateOrdered"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(db["DateOrdered"])),
 Quantity = Convert.ToInt32(db["Quantity"])
List<OrderList> convertedList = new List<OrderList>();
var emptyList = convertedList;
return View(convertedList);

You notice here that the convertlist is using the enumerable type of ToList () which convert the the database read items from the table onlineOrders as enumerable, rather than casting it manually which doesn’t work, and then I add this converted enumerable list to be placed in var that is read to the ViewMoel called OrderSummary.

Hope that helps!!!!!!