9/14/14

Recover a SQL Server management studio query

We often come across the case when SSMS crashes and perhaps your query was not saved. If you have the AutoSave feature enabled, you can find a temporary file at this location:

c:\users\{username}\SQL Server Management Studio\Backup Files\Solution1

If you do not have this feature, you can still recover the script to the state when it was last executed by reading the information from the dynamic management views and functions. This can allow us to capture the SQL statements that were executed on the system for a particular period of time. Please note that this only works while the query statistic cache is not reset. 

To recover your query, use the following query and enter a search value and time frame.

USE MASTER 
GO

SELECT query.last_execution_time AS [Date Time], execsql.text AS [Script]
FROM sys.dm_exec_query_stats AS query
CROSS APPLY sys.dm_exec_sql_text(query.sql_handle) AS execsql
WHERE execsql.text like '%TAG-TO-SEARCH%' and last_execution_time > getdate()-1
ORDER BY query.last_execution_time DESC


In this query, I am only looking for queries for the last day. You can remove that date constraint to expand the search.  The TAG-TO-SEARCH value should be replaced by a word that can help you get a closer match.


You can find detail information of dynamic views here

9/13/14

Client Side HTML Semantic Templates with Handlebars.js

When working with ASP.NET MVC Web applications, a common approach is to create the user interface by defining server side views and binding the model/data to these views. Basically the HTML gets generated on the server. What if we would just like to download the data and templates to reduce the HTML download and leverage the client processing resources?

This is where handlebars.js can help us. With this JavaScript framework, we can define HTML semantics templates into views which can be used to render the user interface.  With this approach, we can use AJAX to call a web service and get JSON data (model) into the application, reference views as JavaScript libraries and bind the JSON data to render the content.

For Visual Studio projects

To start using Handlebars on your web project, we fist need to add the NuGet package to our project. This is done by entering this command on the Visual Studio Package Manager Console:

PM> Install-Package handlebars.js

This should add the following JavaScript files:

File Name
Description
Handlebars.js
This is the framework file with no compression. You can use this during the development process.
Handlebars.min.js
This is the minimize file for the production environment
Handlebars.runtime.js
This is a smaller file which does not contain any of the compilation support for the framework. Handlebars compiles views as JavaScript objects. The result is not machine code as you would expect. It is just a way to optimize the templates. This file is only needed if you precompile the templates.

Note*: If you get an error on this call Handlebars.compile(view), this is because you are including the Handlebars.runtime.js file. To fix this, remove the reference to the runtime.js file.

HTML Content

Now that we have a reference to Handlebars we can start building our JavaScript views.  We are going to start with this simple HTML page:

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <title>ozkary - semantic template</title>
<script src="http://code.jquery.com/jquery-1.7.1.min.js"></script>
<script src="/Scripts/handlebars.js"></script>
</head>
<body>
    <div id="content">content here</div>
</body>   
</html> 



Defining the Model

There is currently nothing on the page. We would like to show a list of vehicles right inside the #content element. The model that we are using has the following definition:

 
The JSON string for this model looks like this:

{Makes: [
{"Name": "Acura", "Models": [{"Name": "ILX"}, {"Name": "MDX" }, {"Name": "NSX Coupe" }] },
{"Name": "Ford", "Models": [{"Name": "Escape"}, {"Name": "Edge" }, {"Name": "Focus" }] },
{"Name": "Nissan", "Models": [{"Name": "Altima"}, {"Name": "Sentra" },{"Name": "XTerra" }] }
]};


The model is a simple parent - child JSON structure. It displays vehicle makes a few models for each make.

Defining the View

We now need to define a handlebars template which can be used to render the JSON model.  A template is defined in a Script block. The requirement for a handlebars template is that we need to add a Script block of type text/x-handlebars-template. The HTML that is used for binding the model should be enclosed within the script tags. We are using a simple layout with headers and un-order list to show each make and associated models. We can now add this block to our HTML page:

<script id="makes-template" type="text/x-handlebars-template">
{{#Makes}}
<h1>{{Name}}</h1>
<ul>
{{#Models}}
<li>{{Name}}</li>
{{/Models}}
</ul>
{{/Makes}} 
</script>

This is where it gets really interested. When using handlebars, we must use the double curly brackets for each expression.  In this example, the template takes the model and iterates each Make displaying the name in the h1 tags. It then iterates thru all the children of each make and displays the Model name in between the li tags. We should notice that each iteration block has this format:

{{#Tag}}
{{/Tag}} 

The hash (#) is used to start the block and the forward slash (/) is used to terminate it.

Process the template with the model context

Now that we have defined the model and template, we now need to process this information and add it to the document.  This is done by adding these tasks:

  • Load the model (JSON)
  • Load the template
  • Compile the template
  • Bind the model to the template
  • Render the html


The JavaScript code that does this looks like this:

  var makesModel = {
        Makes: [
    { "Name": "Acura", "Models": [{ "Name": "ILX" }, { "Name": "MDX" }, { "Name": "NSX Coupe" }] },
    { "Name": "Ford", "Models": [{ "Name": "Escape" }, { "Name": "Edge" }, { "Name": "Focus" }] },
    { "Name": "Nissan", "Models": [{ "Name": "Altima" }, { "Name": "Sentra" }, { "Name": "XTerra" }] }
        ]
    };

    function init() {
        var source = $("#makes-template").html();
        var template = Handlebars.compile(source);
        $("#content").html(template(makesModel));
    }

    init();

The HTML Content

If we run this script in JSFiddle, we will get the following content:



With this approach, we can see how to render web content using a MVVM design pattern. We eliminate the need to write code with model information, and we use semantics templates to generate the user interface on the client side.


Thanks for reading.