Sunday, January 18, 2015

Use Sql Server named instance as local to avoid changing the connection strings

When working on a team project, we often come across the issue that the database connection string in the configuration file uses the default instance name, but our development database is actually using a named instance (see below)
Default or Named Instance
Default Instance
Named Instance

Some developers just create a default instance and continue on their work. Others would just change the configuration file or have a local copy of the configuration file to match their environment and move on.
There is another approach that we can use which would use less resources (in the case of another instance), and it is a lot more convenient than having to manage multiple configuration files. We can use named pipes configuration to change the pipe name from the instance name to the default instance.
Named Pipes Configuration
When connecting to the default instance, SQL Server uses the default pipe name of "\.\pipe\sql\query". A named instance uses a different pipe name as listed below:
Pipe Name (Default)
Pipe Name (Instance)

We can use SQL Server Configuration Manager to first enable the named pipes setting and update the pipe name. This can be done as follows:
Select SQL Server Network Configuration (see pic below for details)
  • Click protocols for (Instance Name)
    • Double click on Named Pipes
      •             Set Enabled to Yes
      •             Update the pipe name
      •             Apply the changes.
      •             Restart the SQL Server service - SQL Server (SQLEXPRESS)

Use Sql Server named instance as local

After making those changes, we can try to connect to our database using SQL Server Management Studio. We can try to connect to the database with both (local) and (local)\express for server name, and both connections should be successful.

Thanks for reading.

Sunday, January 11, 2015

ASP.NET MVC 5 Passwords must have at least one non letter or digit

If you are trying to register a new user, and you are getting the error below:

MVC 5 Passwords must have at least one non letter or digit

The new ASP.NET MVC 5 web templates are using the Identity framework to do the validation and password policy. This message indicates that the password policy requires a special character. In the event that our password policy does not need such requirements, we can disable this constrain.

To disable this constrain, we can follow these steps:
  • Open the identity_config.cs files under the App_start folder
  • Search for the string manager.PasswordValidator
  • We should see this code snippet:

// Configure validation logic for passwords
manager.PasswordValidator = new PasswordValidator
RequiredLength = 6,
RequireNonLetterOrDigit = true,
RequireDigit = true,
RequireLowercase = true,
RequireUppercase = true,
  • Set the RequireNonLetterOrDigit = false
  • Save and compile the application

The password requirements should now be a bit more flexible, and the error should no longer be displayed.

I hope that helps.

Saturday, January 10, 2015

This project references NuGet package(s) that are missing on this computer

This project references NuGet package(s) that are missing on this computer. Enable NuGet Package Restore to download them.  

When getting this error, Visual Studio indicates that a NuGet package is missing from your project references.  If you take a look at the project->references node, you will probably see that there are some missing libraries that have been added to the project via a NuGet package installation, and your system does not have the package.  These files may even be the core .Net libraries like System, System.Xml etc.

To address this problem, we can just follow these steps:
  • Select Solution Explorer
  • Right click on the Solution Name
  • Click Enable NuGet Package Restore

This tells Visual Studio that whenever it finds a missing package, it needs to download it. After this has been enabled, we need to reload each project that has the missing references. This can be done by closing and opening the solution which will reload all the projects or just unloading and reloading each specific project (right click on the project name to see the unload project option).

Another area to make sure that the Package restore feature is enabled is to look for the .nuget folder in the solution’s directory. There should be a Nuget.targets file which manages the restore.

I hope this helps.

Tuesday, January 6, 2015

Add Data Annotations to Entity Framework Models with Metadata or Buddy Classes

When using code generation (ORM), the model classes are automatically created by the tools.  We usually try to edit the class and add annotations such as field requirements, formatting and messages. The problem with this approach is that when we need to add properties to the model class, we will need to re-generate the class and all the data annotations will be wiped out which create extra work for us.

A quick way to address this challenge is to create a Metadata or Buddy class that provides all the annotations that we need. We can then add an attribute to a partial class with the same name as the class that was generated to indicate that there is another class that provides the annotations on its behalf.   We can now take a look at an ASP.NET MVC project which can be loaded from the source code link at the end of this article.

Class Generated by Entity Framework (Database first approach)

We first create a simple table with the following definition:

CREATE TABLE [dbo].[Car] (
    [Id]    INT           IDENTITY (1, 1) NOT NULL,
    [Make]  NVARCHAR (50) NOT NULL,
    [Model] NVARCHAR (50) NOT NULL,
    [Trim]  NVARCHAR (50) NULL,

Note: This table is created using the .mdf files under the app_data folder.

We now can use EF by adding an ADO.Net Entity Data Model to create the model from our database which generates the following definition (see Models/car.edmx file). Do not forget to compile after the table has been imported.

namespace og.samples.aspnet.MetaDataClass.Models
    public partial class Car
        public int Id { get; set; }
        public string Make { get; set; }
        public string Model { get; set; }
        public string Trim { get; set; }        

As we can see, there are no annotations on this model. Since this is a generated class, we would like to add our annotation class instead of changing it. We need to do this before we generate the views.

New Partial and Metadata Classes

The steps to add our metadata class are the followings:

1)      Add a new class under the model folder
a.      Name it  car.metadata.cs
2)      Create another class that contains all the data annotations.
a.      This is not a partial class
b.      This is a sealed class as there is no need to instantiate it
3)      Add a new partial class with the same name as the ORM class
a.      Add the [MetadataType] attribute to the class that was just created
b.      Set the type to the class that has the annotations.
4)      Compile

The code should now look as follows:

namespace og.samples.aspnet.MetaDataClass.Models
    /// <summary>
    /// partial class definition to associate the ORM generated class
    /// NO NEED to add the properties here.
    /// </summary>
    public partial class Car

    /// <summary>
    /// Buddy Class or Data Annotation Class
    /// Add the properties here with the associated annotations
    /// </summary>
    internal sealed class CarAnnotation
        [Required(ErrorMessage="{0} is required")]
        [MinLength(3,ErrorMessage="{0} should have three or more letters")]   //kia
        public string Make { get; set; }

        [Required(ErrorMessage = "{0} is required")]
        public string Model { get; set; }
We are making Make and Model required. In addition, we are making the Make to have a minimum of three characters.

Create Controller and Views

We now just need to add our controller and views by adding a controller item under the controller folders and selecting the following properties:
  1. MVC 5 with controller with views, using Entity framework
  2. Enter the following settings and compile the project

 Cars Create View (see sample project)

Run the application and select Run Demo under the Metadata classes section.

This is how the view should look. Press Create for the validation to take place as shown below.

With the above view, we can show that our data annotation validations are shown when the user does not meet the input requirements.


With this approach we can show that we can still enable the use of ORM tools to generate the models and continue to support our application specific data annotation requirements without the concern of losing any information.

Code Sample

Available at:  GitHub  (see Dev Branch for latest changes)


Friday, October 31, 2014

SQL Server Parse HTML Content from Data

With this TSQL script, we can remove HTML content as long as there are valid start and ending HTML tags.


Create FUNCTION [dbo].[udf_StripHTMLContent]
(@content VARCHAR(MAX),@tagStart varchar(55),  @tagEnd varchar(55))

DECLARE @start INT=1
WHILE @start > 0 AND @end > 0 AND @len > 0
      SET @start = CHARINDEX(@tagStart,@content)
      SET @end = (len(@tagEnd)-1) + CHARINDEX(@tagEnd,@content,CHARINDEX(@tagStart,@content))        
      SET @len = (@end - @start) + 1     
      IF @start > 0 AND @end > 0 AND @len > 0 and (@len > 1 and @end > len(@tagEnd))
            SET @content = STUFF(@content,@start,@len,'')              
RETURN REPLACE(@content,' ',' ')

This script parses the content looking for the start and end tags. It replaces all the content that is contained by those tags. It continues to parse the string until no more matching tags are found or the end of the string is reached.


Use the following examples to see how this function can be used. The first couple of examples just parse a string. The last example does an inline SQL update to remove HTML tags from some inventory records.

declare @html varchar(max), @start varchar(55), @end varchar(55)
set @start = '<div><a href="">'
set @end = '</a></div>'
set @html = 'This item has many features<div><a href="">Unwanted content</a></div>'
select @html as before
set @html  =dbo.udf_StripHTMLContent(@html,@start, @end)
select @html as after

set @start = '<p style="display:none">'
set @end = '</p>'
set @html = 'This item has many features<p style="display:none"><a href="">Unwanted content</a></p>'
select @html as before
set @html  =dbo.udf_StripHTMLContent(@html,@start, @end)
select @html as after


update a set ItemDetail = dbo.udf_StripHTMLContent(ItemDetail,@start, @end)
from Inventory..Item a

This function can also be used to strip out content that matches a particular start and ending pattern.

I hope this is useful.

Sunday, September 14, 2014

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.


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

Saturday, September 13, 2014

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
This is the framework file with no compression. You can use this during the development process.
This is the minimize file for the production environment
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="">
    <title>ozkary - semantic template</title>
<script src=""></script>
<script src="/Scripts/handlebars.js"></script>
    <div id="content">content here</div>

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

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:


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


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.