.NET SQL Execution Plan

15 October 2011 Leave a comment

Today at work we ran into a bit of a problem.  The page on our website kept timing out although the SQL that was causing the timeout executed in under a second.  After some Googling it turned out that SQL Server sometimes selects a different execution plan for .NET than for SQL Server Management Studio.  I found a SQL command that would return the execution plan that was chosen for the query and return it as a separate result set.

SET STATISTICS PROFILE ON;
-- Your Query
SET STATISTICS PROFILE OFF;

Since it was all text based and not that straight forward to understand I could not wait to get home and make it a bit more UI using Google Charts.  After hacking away for a couple of hours I ended up with an app that does just that.  The screenshot below is how the results now get displayed.

 

The source is uploaded to Github should anyone be interested in having a look at the source or possibly have a use for it somewhere.

MVC 3 and Entity Framework Code First

12 October 2011 Leave a comment

In this post I will cover the basics of getting started with Entity Framework using code first.  I will assume familiarity with Visual Studio and MVC projects.

To get started I:

  1. created a new MVC 3 application in Visual Studio
  2. chose internet application – either will do
  3. selected the Razor View Engine
  4. selected Html 5 markup

So now we have the bog standard default MVC website.  Since Entity Framework now comes standard with MVC there is no need to install any additional packages and we can start playing straight away.

Just to do something a bit different I will create an application that allows you to add/edit/delete/list solar systems which can contain planets.  For the sake of simplicity I will not create interfaces.

Now, let the coding being…

Add two new areas called SolarSystem and Planet.  In SolarSystem’s Model folder we add a class called SolarSystem and in Planet’s Model folder we add one called Planet.  The model called the same as the containing namespace is a dumb idea, I know, but for this post I will just keep it that way.  Below is the code for the models.  You can see in the planet model we have SolarSystem as well as SelectedSolarSystemId.  This will be more obvious later on but it is used for the UI to automagically create a list of solar systems for selection when creating or updating a planet.

public class Planet
{
    [Key]
    public int Id { get; set; }

    [Required]
    [StringLength(150)]
    public string Name { get; set; }

    [Required]
    public int SolarSystemId { get; set; }

    public SolarSystem.Models.SolarSystem SolarSystem { get; set; }
}

public class SolarSystem
{
    [Key]
    public int Id { get; set; }

    [Required]
    [StringLength(150)]
    public string Name { get; set; }
}

For Entity Framework to pick up the new models we have to rebuild the application first.

Now the magic begins.  Right-click on the Controllers folder for the Planet area and select the option to add a new controller.  In the popup (below) form enter the name as PlanetController then in the scaffolding options select the template “Controller with read/write actions and views, using Entity Framework”.  Also select our model, Planet in this case, and select “<New data context…>” from the Data context class option and enter a name, DataContext in my case.  Double check that you entered a controller name and then click add.  Forgetting the controller name is a very easy mistake to make.
If you get an error that the metadata could not be read then you most probably do not have accessible properties or you have selected a class from another namespace.  Then, once you select the model it will create a whole lot of good stuff…just about enough to replace developers…but this will now contain errors mixing up the namespace and the model so you will have to manually qualify your models in the controller actions to contain the namespace (or just name them right from the start).
Now do the same for SolarSystem but select the existing DataContext class that was created instead of a new one…and remember to double check that a controller name was entered. 

New controller popup

We will add buttons to the default layout to access the areas easily.  Do this by adding the following code to the _layout.cshtml in the shared views folder in the main application (myapp\Views\Shared\) just after the existing default buttons.

<li>@Html.ActionLink("Planets", "Index", "Planet", new { area = "Planet" }, null)</li>
<li>@Html.ActionLink("Solar Systems", "Index", "SolarSystem", new { area = "SolarSystem" }, null)</li>

If you run the application now you will be able to navigate to the areas and create new planets and solar systems.  In a few minutes we have created a fully fletched, albeit ugly, CRUD website.  A few tweaks and the UI can look fairly decent.

Note that if you are running into problems with database connection then make sure you have SQL Server Express running, it uses that by default.  Or sort out a connection string that works.  I ran into this and it took me a while to figure out what the problem actually was.

One thing I do not like, at all, is the ViewBag that is used in the planet controller.  This should be done using an editor template with a viewmodel instead…but that would take significantly longer to accomplish.

So, now we have an application running, what’s next I hear you say?  Data…default test data populated each time the database is recreated.  And a custom database name!  Also, once the database has been created it will not by default recreate the database when a model is changed which will cause the application to crash.  This is also covered below.

We will start by adding a default constructor to the DataContext class that pass a string containing the database name of your choice to it’s base class (DbContext).

public DataContext()
: base("SomeCustomDatabaseName")
{}

In order to create some test data each time the database is created we will override the OnModelCreating method in the DataContext class.  We will then have the following:

public class DataContext : DbContext
{
    public DataContext()
        : base("SomeCustomDatabaseName")
    {}

    public DbSet<Planet> Planets { get; set; }
    public DbSet<SolarSystem> SolarSystems { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        Database.SetInitializer(new CustomDataContextInitializer());
        base.OnModelCreating(modelBuilder);
    }
}

CustomDataContextInitializer needs to be created and needs to inherit from different classes depending on whether you want to drop and recreate the database each time you run the app or only when a model changes.  In this example I will just let it recreate it every time the application is run and thus inherit from DropCreateDatabaseAlways<DataContext> instead of DropCreateDatabaseIfModelChanges<DataContext>.  Then for inserting data into the database we need to override the Seed method as follows.

internal class CustomDataContextInitializer : DropCreateDatabaseAlways<DataContext>
{
    protected override void Seed(DataContext dbContext)
    {
        base.Seed(dbContext);

#region Add Solar System
        SolarSystem solarSystem = new SolarSystem
        {
            Id = 0,
            Name = "The Sol System"
        };

        dbContext.SolarSystems.Add(solarSystem);
#endregion

#region Add Planets
        IList<Planet> planets = new List<Planet>
        {
            new Planet
            {
                Id = 0,
                Name = "Earth",
                SolarSystem = solarSystem
            },
            new Planet
            {
                Id = 1,
                Name = "Mars",
                SolarSystem = solarSystem
            },
            new Planet
            {
                Id = 2,
                Name = "Saturn",
                SolarSystem = solarSystem
            }
        };

        planets.ToList<Planet>().ForEach(planet => dbContext.Planets.Add(planet));
#endregion
        // The important bit
        dbContext.SaveChanges();
    }
}

Now when you run your application it will populate the database with your test data which you can go mad on…and it will all be reset when you restart your application.  Amazing stuff!

Now all you need to do is decorate your models with the badly needed data annotations and some CSS.

Insert multiple oData records with a single post

6 February 2011 Leave a comment

A few months ago I was playing around with some user interface ideas. I used the jQuery sortable which will save to my oData service after the user had made a change. This caused multiple posts to the database, depending on the amount of items added, removed or reordered should the user navigate away before it was all completed.
After some searching I found a way around this which I decided to play around with again today for a project I will be doing soon. The idea is to combine all the updates/inserts into a sequence and then submit it in a single post. This will make it a lot quicker and also have a lot less code to display a single message when all the updates are complete.

This post will not go into much detail about getting the whole lot set up, so here is what you need already set up:

  • ASP.NET website building and running
  • WCF Data Service set up to return values when queried through the browser
  • MS AJAX Toolkit (Had to download the source to get all the .js libraries I needed)

For the sake of not struggling I copied all the files from the .\MS Ajax\SampleWebSites\AjaxClientWebSite\Scripts\MicrosoftAjax folder into my website’s Scripts folder and referenced only Start.debug.js (or Start.js for release). This takes care of loading all the rest of the required libraries.

My data service was called TestDataService.svc with a single table containing two columns since this was just the bare minimum to get my head around it again.

In my html page I have the following code to load, display and insert data.

    
    
        // We require this library
        Sys.require([Sys.components.dataView, Sys.components.openDataContext, Sys.components.openDataServiceProxy]);
        
        // A reference to our service
        var exampleService;

        // Page loaded and DOM ready
        Sys.onReady(function () {
            // Create the proxy to our data service
            exampleService = new Sys.Data.OpenDataServiceProxy("/TestDataService.svc");
            // Load the data from the service
            loadData();
        });

        function loadData() {
            // Query the service
            exampleService.query("/testTables", cbSuccess, cbFailure);
        }

        // Success callback for the loadData query
        function cbSuccess(result, context, operation, userContext) {
            // Clear the list
            $("#dbData").children().remove();
            // Add all the items from the database/service to the list
            $.each(result, function (index, row) {
                $("#dbData").append("<li>" + row.name + ": " + row.value + "</li>");
            });
        }

        // Failure callback for the loadData query
        function cbFailure() {
            alert("Error contacting service");
        }

        // Insert multiple recors into the database using a single post
        function insertMultiple() {
            // Sequence action for inserting data
            var actionSequence = exampleService.createActionSequence();
            // Creating 3 records
            for (var i = 0; i &lt; 3; i++) {
                var tmpData = {
                    name: "Sequence" + i, // Column name for the table
                    value: "" + i // Column name for the table
                };
                // Add the freshly create item to the sequence for testTable
                actionSequence.addInsertAction(tmpData, "/testTables");
            }
            // Execute the sequence
            actionSequence.execute(cbInsertComplete, "Inserted all three records");
        }

        // Success callback for the bulk insert
        function cbInsertComplete(results) {
            // Results returned after successful insert as _result. 
            $.each(results, function (index, row) {
                $("#dbData").append("<li>" + row._result.name + ": " + row._result.value + "</li>");
            });
        }
    

There is not much to the HTML

        <p>
            <ul id="dbData">
                        <!-- Data gets loaded here -->
            </ul>
        </p>
        <p>            
            <input id="Button2" type="button" value="Reload Data" onclick="loadData();" />
            <input id="Button1" type="button" value="Insert Data" onclick="insertMultiple();" />
        </p>

As you can see in the image below, when running this through FireBug you can see the first request to load the data which is currently empty, then there is a single post which posts the data and get the results back. We use the results to add the new data to the list.

This code is a very rough guide to executing a sequence of actions using oData services and should not be used as is in a live environment. No error checking is done and no best practices are followed in this post, it is purely the very basics to getting started.

If you would like more detailed information then please do contact me, I will be more than happy to help where I can.

jEditable Dropdown Text

9 June 2010 3 comments

I was stuck for a few minutes trying to figure out how to display the text of the selected item in the dropdown when using jEditable.

My script was querying an odata service (WCF Data Service) which returned a list of values with their ids.  I wanted this to be represented in a dropdown with the text displayed instead of the default “selected value”.  After struggling for a bit I came up with an idea, since someone else was struggling with the same problem I decided to create a post about it.

Here is my JavaScript code:

var settings = new Array(); // holds settings
function loadSettings(selected) {
    // loads the settings from the database via oData service
    $.ajax({
        type: "GET",
        url: "dataService.svc/Settings?$orderby=Code%20asc",
        data: {}, // required for Chrome
        contentType: "application/json; charset=utf-8",
        dataType: "json",
        async: false, // wait until it's done before setting up jEditable
        success: function (data) {
            /* Settings
             *   int SettingId (PK)
             *   string Title
             */
            // step through all the results
            $.each(data.d, function () {
                // add them to a hashtable
                settings[this.SettingId] = this.Title;
            });
            // check if a selected one was passed in
            if (selected != undefined)
                settings["selected"] = selected; // set the selected one
        },
        error: function (msg, a) {
            // call error method
            // TODO: Display correct error here by checking resulting code
            showError("ERROR: Could not load the settings from the database");
        }
    });

    // get the selected one's TEXT from the hashtable
    var set = settings[val];
    // TODO: Make this code better, use .append("<span />");
    $('#settings').html('<span id="ddSettings">' + set + '</span>');

    // setup field for inline editing
    $('#ddSettings').editable(function (value, settings) {
        saveSettings(value, docId, settings); // function to save settings
        return suffixes[value]; // return the text from the hastable
    }, 
    {
        data: settings, // my hashtable
        cancel: 'Cancel',
        placeholder: '---',
        submit: 'Save',
        tooltip: 'Click to edit',
        type: 'select',
        style: 'display: inline;'
    });
}

And my html:

<html>
<head>
    <script src="/Scripts/jquery-1.4.2.min.js" type="text/javascript"></script>
    <script src="../Scripts/jquery.jeditable.mini.js" type="text/javascript"></script>
    <script type="text/javascript">
        $(document).ready(function() {
            loadSettings();  // load the settings dropdown
        });

        // put the JavaScript source from above in here
    </script>
</head>
    <body>
        Settings:&nbsp;<div id="settings"></div>
    </body>
</html>

I hope this will help someone get around this problem.

ETag value in header different from object ETag

1 June 2010 4 comments

This is more a note to self which will hopefully help someone.

The last couple of weeks I have been working on a web application that uses WCF Data Services (oData) and jQuery.  I started receiving the following error: "The etag value in the request header does not match with the current etag value of the object.".  After hours of struggling I found out that it was the oninsert trigger in my database causing the problem.  It seems that when you insert the data the trigger changes that and then it is different from what you inserted and it then throws an error instead of returning the record you have just inserted.

Invalid JSON primitive in Chrome

24 April 2010 2 comments

I ran into the strangest problem today.  My Ajax call to my web service failed with the error “Invalid JSON primitive”, but only in Chrome…it worked in all my other browsers.

After reading up a bit it seemed to be a problem with the data being sent to the service.  Strange enough I was not sending any…and that turned out to be the problem. 

So for whoever runs into this problem, try setting the data parameter in jQuery to nothing, like in the code below.

$.ajax({
    type: "POST",
    contentType: "application/json; charset=utf-8",
    url: "DataService.asmx/ProjectList",
    dataType: "json", //response type
    data: "{}", // IMPORTANT
    success: function(msg, success) {
        if (success == 'success') {
            //good response
            data = eval(msg.d);
            // attach the template
            $("#slideshow").setTemplateElement('projectFaderTemplate', null, { runnable_functions: true });
            // process the template
            $("#slideshow").processTemplate(msg);
        } else {
            $('#msg').html('Error: ' + msg.d); //service returned an error
        }
    },
    error: function(msg, e) {
        $('#msg').html('Error.  Please contact Technical.'); //error with ajax call
    }
});

Hope this helps someone.

Linq to XML

23 April 2010 Leave a comment

Here is a very short post giving an example on how to use Linq to XML. 

Let’s say we have the following XML

<?xml version="1.0" encoding="utf-8" ?>
<projects>
    <project title="Kraankuil">
        <screenshot>kraankuil.jpg</screenshot>
        <uri>http://www.kraankuil.co.za</uri>
        <technologies>Gimp, ASP.NET, CMS</technologies>
        <launched>12 March 2010</launched>
        <description>Website for a small family owned B&amp;B in South Africa.</description>
    </project>
</projects>

We will do a class to easily handle the data like this

public class Project
{
    public string Title { get; set; }
    public string Screenshot { get; set; }
    public string Uri { get; set; }
    public string Technologies { get; set; }
    public string Launched { get; set; }
    public string Description { get; set; }
}

I will be using this is a web service where I use this code

[WebMethod]
[ScriptMethod(ResponseFormat = ResponseFormat.Json)]
public List<Project> ProjectList()
{ 
    XDocument dataDoc;

    // load the file - from cache if possible
    if (HttpContext.Current.Cache["dataDoc"] == null)
    {
        dataDoc = XDocument.Load(Server.MapPath("~/App_Data/Data.xml"));
        HttpContext.Current.Cache["dataDoc"] = dataDoc; // store it in the cache
    } else {
        dataDoc = (XDocument)HttpContext.Current.Cache["dataDoc"];
    }

    //get the projects from the file
    var projectList = from project in dataDoc.Descendants("project")
                      select new Project
                      {
                          Title = project.Attribute("title").Value,
                          Screenshot = project.Element("screenshot").Value,
                          Technologies = project.Element("technologies").Value,
                          Launched = project.Element("launched").Value,
                          Description = project.Element("description").Value,
                          Uri = project.Element("uri").Value
                      };

    return projectList.ToList();
}

Note: This is NOT a best practices guide so do use common sense, it is only meant to get you started