Tuesday, November 12, 2013

Easiest Repository Pattern Tutorial

I would like to explain how to implement repository pattern before we proceed any further with our single page application discussion.

What is repository pattern?
Repository pattern separates the logic that retrieves the data from the database and logic that uses the data by your application.  Thus it makes your data access layer independent of your presentation layer.

As shown in above figure:
  1. Create database table
  2. Create POCO class (Model class) with getters and setters mapping to all the properties of database table.
  3. Create Interface which list down all the operations we are going to perform on that table.  Most of the time we are doing CRUD operation (Create, Read, Update and Delete operation on table).
  4. Implementation of Interface.
  5. Presentation layer consuming interface to perform database operation.
In summary, accessing database through interface is repository pattern.  (Disclaimers: Please note I am using few sentence which are very lame in nature just to make explanation as simple as possible for anyone to understand, ones user have good understanding he can judge the things better himself).

Advantages of making use of Repository Pattern
  • Since we are accessing database through interface, presentation layer is independent of database layer.  That means you can have same data access logic reusable for multiple presentation layer (eg: console application, asp.net mvc, asp.net web form or windows form can use same data access logic.)  Similarly whenever you change the way you access data from database doesn't affect how it is rendered on presentation layer.  That means if you are using ado.net to access database, later you decide to make use of entity framework or micro-orm or web service or web api, will not require you to make any change on the presentation side.
  • Code will be more maintainable and readable.
  • Testable code.
  • Flexibility of architecture and much more (Running out of time, so google it please).
Repository Pattern Implementation Step by Step
Step 1: Create database table
For this example:  Please create 
  • "Departments" table with 2 columns
    • DeptId  int
    • DeptName varchar(35)
Department table creation script




CREATE TABLE [dbo].[Departments](
[DeptId] [int] IDENTITY(1,1) NOT NULL,
[DeptName] [varchar](35) NULL,
[DeptId] ASC



Insert records in table script

Insert into Departments values ('Human Resource');
Insert into Departments values ('Finance');
Insert into Departments values ('Payroll');
Insert into Departments values ('Transportation');
Insert into Departments values ('Logistic');
Insert into Departments values ('Information Technology');
Insert into Departments values ('Administration');
Insert into Departments values ('Customer Care');

Ones you are done your departments table will be as shown in figure:

Step 2: Create POCO class (Model class for departments table)
Create a VS.Net Class library project for creating POCO (Plain old CLR Object) class.

Create a class called "Departments.cs" and add getters and setters for all table property.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace MyAppDemo.Model
    public class Departments
        public int DeptId { getset; }
        public string DeptName { getset; }

    Step 3: Create Interface and list all CRUD methods
    Create a separate VS.Net Class library project for Interface.  To do this right click solution file and add new project to existing solution.

    Ones you create Interface project add project reference for Model project into interface project.  Create a
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using MyAppDemo.Model;
    namespace MyAppDemo.Interface
        public interface IDepartments
            void Insert(Departments model);
            void Update(Departments model);
            void Delete(long Id);
            Departments SelectOne(long Id);
            IEnumerable<Departments> SelectAll();

    Step 4: Create Interface Implementation project
    Create a separate VS.Net Class library project for Implementation.  To do this right click solution file and add new project to existing solution.

    Add reference of both model project and interface project into Implementation project.

    Since for this project I will be accessing data using entity framework.  

    Lets add Entity Framework nuget package for this project.

    In order to make use of entity framework we will need database context file.  So lets first create DB Context file and then Implementation file.

    Create a "MyAppDemoContext.cs" file.
    using System;
    using System.Collections.Generic;
    using System.Data.Entity;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using MyAppDemo.Model;
    namespace MyAppDemo.Implementation
        public class MyAppDemoContext : DbContext
            public MyAppDemoContext()
                : base("DefaultConnection")
            public DbSet<Departments> Department { getset; }

    Now lets create implementation file.  "DepartmentsImpl.cs"
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using MyAppDemo.Model;
    using MyAppDemo.Interface;
    namespace MyAppDemo.Implementation
        public class DepartmentsImpl : IDepartments
            // Create a Instance of DB Context
            private MyAppDemoContext db = new MyAppDemoContext();
            public void Insert(Departments model)
            public void Update(Departments model)
                Departments foundModel = 
                    .Where(a => a.DeptId.Equals(model.DeptId))
                if (foundModel == null)
                    throw new Exception("Model not found");
                foundModel.DeptName = model.DeptName;
            public void Delete(long Id)
                Departments foundModel = 
                    .Where(a => a.DeptId.Equals(Id))
                if (foundModel == null)
                    throw new Exception("Model not found");
            public Departments SelectOne(long Id)
                return db.Department
                        .Where(a => a.DeptId.Equals(Id))
            public IEnumerable<Departments> SelectAll()
                return db.Department.AsEnumerable();
    Ones you are done with these steps your solution will look as under:

    Step 5: Presentation Layer which will be making use of data access layer through interface.
    Create a separate console project.  To do this right click solution file and add new project.
    Add connection string in App.Config file and following code for making DB listing call to your "Program.cs"
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using MyAppDemo.Model;
    using MyAppDemo.Interface;
    using MyAppDemo.Implementation;
    namespace MyAppDemo.PresentationConsole
        class Program
            static void Main(string[] args)
                IDepartments repository = new DepartmentsImpl();
                //List All Departments
                List<Departments> departmentsList = repository.SelectAll().ToList();
                foreach (var department in departmentsList)
                Console.WriteLine("Press any key to exit...");

    Ones you are done with all the steps your solution will look as shown in figure.
    Similarly you can add one more project for Asp.net MVC and use same DB layer.

    Download Complete Sourcecode for demo discussed in this tutorial for repository pattern.

    Monday, November 11, 2013

    Understanding Flow of control for Hot Towel SPA Template

    Like any other application HTS is not following default route of having Global.asax as starting point.  "I believe" this is not good and there are few other things which "I believe" you won't do it for your production code which we will be going to customize in upcoming blog post.

    Flow of process for Hot Towel SPA template

    • App_Start/BreezeWebApiConfig.cs/RegisterBreezePreStart()
    • App_Start/HotTowelRouteConfig.cs/RegisterHotTowelPreStart()
    • Root/Global.asax/Application_Start()
    • App_Start/FilterConfig.cs/RegisterGlobalFilters()
    • App_Start/RouteConfig.cs/RegisterRoutes()
    • App_Start/HotTowelConfig.cs/PreStart()
    • Controllers/HotTowelController.cs/Index()
    • Views/HotTowel/Index.cshtml
    • App/Main.js/boot()
    • App/Shell.js/boot()
    • App/Home.js/activate()
    Understanding of above process will be very helpful in customizing Hot Towel SPA template to suits your needs.

    Situation where above process flow is very helpful
    • Customizing Application specific settings
    • Remove breeze and adding support for web api
    • Adding any more JS support.
    In next post we will understand how to customize hot towel spa to suits your custom needs.

    First Single Page App (SPA) using Hot Towel SPA Template

    In this post, I will be creating our first SPA application using Hot towel SPA template.

    If you haven't installed VS.Net 2012.2 Update, then please do it now before reading further.

    Step 1: Open VS.Net and start creating asp.net mvc application

    Step 2: There are 2 default template available for creating SPA application, choose "Hot Towel SPA" template

    Step 3: Ones your solution is ready, you will notice all SPA related files in App folder.  You will also notice that Hot Towel SPA (HTS) has taken care of initial plumbing of installing durandal.js, require.js, bootstrap, breeze, etc.

    App Folder Stucture

    • Views Folder - Contains HTML files, it will have plain html code which is used for presentation.
    • ViewModels Folder - Contains Javascript files, it will have logic regarding how you are going to access model, jquery web api calls and databind related logic (Knockout)
    • Services Folder - Can be used for storing global SPA code which can be shared among all SPA pages.

    Special SPA Files

    • Main.js File - It will contain logic for applying SPA application related settings and starting SPA application.
    • Shell.js File - It will contain navigation related logic

    SPA Convention

    • In order to create new page in HTS, you will have to create html file in view and javascript file with same name in viewmodel.
      • Example: details.html and details.js files 
    • You can then write navigation logic in shell.js file.

    Now lets run the application to view how it looks :)

    In next post we will understand, how to perform basic customization to default HTS template.

    Single Page Application (SPA) - Overview

    For those of you haven't got chance to work on Single Page Application (SPA), I will be discussing few of my learning in series of post, including how to perform CRUD operation using both Breeze and Web Api.

    What is Single Page Application (SPA)?
    (From Asp.net Site) ASP.NET Single Page Application (SPA) helps you build applications that include significant client-side interactions using HTML 5, CSS 3 and JavaScript. It’s now easier than ever before to getting started writing highly interactive web applications.

    In my own words:

    • SPA application doesn't requires page postback.
    • SPA application works very well with web api and it can be used for good user experience (UX).
    • SPA application doesn't mean that, their will be only single page in your application.  It will be multipage application which doesn't requires postback 
    • From developers point of view, SPA is javascript way of doing MVC, when working with VS.Net and asp.net mvc.
    • SPA technology is not new but with KnockoutJS and other javascript framework now making SPA application will be much more easier than few years back.

    Example of SPA 

    Before you start any further reading, I would highly recommend to go over John Papa's introductory training video about SPA on Pluralsight.

    VS.Net comes with default SPA template when you install VS.Net 2012.2 update.  However I will highly recommend using John papa's Hot Towel SPA template since it comes up with initial plumbing work for you so that you are get, set, go to develop SPA application.

    Since John is stressing more on using breeze to make DB call, which "I believe" is not good, since it is very hard to implement "Repository Pattern" and I don't like javascript files spreading across multiple project is good.  Instead "I believe" the best way to go with SPA is make use of Hot towel SPA and take out breeze related stuff from project and hook up web api related plumbing.  Since Web api calls are not javascript dependent we can easily spread our code across multiple solution without including javascript file and can do whatever we like.  (Specially it is very easy to implement repository pattern in SPA using Web api rather than using Breeze.)

    My views on Breeze Vs Web Api
    • I personally find it is very easy and fast to develop SPA application using Breeze and it is easy to do client side caching of data.
    • Web api is best way to work with SPA since you are not dependent on javascript plumbing to do DB call.
    • If you are not sure about which route to go, I would recommend you to try implementing Repository Pattern with both Breeze and Web Api and it will make your decision simple.

    In my whole series I will be making use of Hot Towel SPA Template to create all application.

    Hot Towel SPA Template is combination of 


  • ASP.NET Web API  

  • Breeze.js - rich data management   

  • ASP.NET Web Optimization - bundling and minification 

  • Durandal.js - navigation and view composition 

  • Knockout.js - data bindings 

  • Require.js - Modularity with AMD and optimization 

  • Toastr.js - pop-up messages 

  • Twitter Bootstrap - robust CSS styling

  • Teched Video Tutorial for SPA

    Few dev tips while working on SPA
    • Most of the time when you are working on SPA, if you run into problem.  Search appropriately to get quicker solution:  For example: If you are facing problem in data binding then search for "Knockout" rather than "Hot towel spa" or "SPA".  Similarly if you run into problem for navigation then search for "Durandal", If you are running into problem for CSS search for "Bootstrap" and so on.
    • Have F12 (Developers tool open while working on SPA)
    • If something is not displaying on screen as expected, check "Console Log" for browser - Shortcut - Cntrl + Shift + J
    • Always "Disable cache" 
      • In chrome go to settings at the right end corner and select Disable cache (while Dev Tools is open)
    • Make use of "Internet Explorer (IE) while working with VS.Net" if you are facing trouble while debugging javascript file. Javascript debugging works very well when we are working with IE and VS.Net
    • Master concept of Knockout.Js and Web Api.  On High level SPA is all about using Knockout.Js and Web api (Please note: I am assuming here that user will be using Hot Towel SPA template and using all combination as describe above)

    In next post we will create our first SPA application using Hot towel spa template.

    Saturday, November 09, 2013

    NotMapped attribute for PetaPoco or Ignore Column while Insert/Update in Petapoco

    NotMapped Property for PetaPoco or Ignore Column while Insert/Update in Petapoco

    [NotMapped]   ==>  [ResultColumn]

    [NotMapped] equivalent of entity framework in Petapoco is [ResultColumn]

    For more information

    Example:  If you have contact form model where you want user to enter captcha, but you don't want this captcha to be store in db, so In order to ignore captcha column while inserting values in database you can mark the column as [NotMapped] in entity framework and if you are using Petapoco you can use attribute [ResultColumn]

    public class contact
        public string FullName { get; set; }
        public string Feedback { get; set; }
        public string Captcha { get; set; }

    Friday, November 01, 2013

    Delete all the rows from all the tables in SQL Server

    If you are in situation where you just want empty database structure, without having data in it.

    Run following select statement which will generate set of delete statement to delete all the records for all the tables in your database.

    'Delete from ' + Table_Catalog + '.' + Table_Schema + '.' + Table_Name + ';' 
    WHERE Table_Type = 'BASE TABLE'

    In case your database is large and you want to know status of which table is currently getting deleted you can use following:

    'Print(''Delete started for ' + Table_Catalog + '.' + Table_Schema + '.' + Table_Name + ''');' +
    'Delete from ' + Table_Catalog + '.' + Table_Schema + '.' + Table_Name + ';' +
    'Print(''Delete done for ' + Table_Catalog + '.' + Table_Schema + '.' + Table_Name + ''');'  +
    WHERE Table_Type = 'BASE TABLE'

    Wednesday, October 30, 2013

    Entity Framework errors: An error occurred while updating the entries.

    Multiple situation where Entity Framework was giving me problem while adding new record, updating record or deleting.  Then here are few things which may go wrong:

    Solution 1. Check whether Database Table has Proper primary key setup.

    Solution 2. Check your DB table and POCO Model class is in sync.  If you have some extra properties which you have to use in POCO class but doesn't need in DB Table, then you may create [Not Mapped] Property.

     public bool IsEditMode { getset; }

    Solution 3. Are you assigning date column a default value of DateTime.MinValue before add or update operation?  If yes, then this may also result into this problem.

    Solution 4. Check if you have any decimal field in your table?  If yes then you will need to make proper configuration changes OnModelCreating.  In order to do so, add following code inside: OnModelCreating method inside your DBContext file.

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    //Precision and scale should match sql server table decimal field.
    modelBuilder.Entity().Property(x => x.YOUR_DECIMAL_FIELDNAME).HasPrecision(26, 16);

    This error could be sometime tricky since this may occur due to many reasons, so if you run into any other situation and found the solution, Please add your comment here.

    Hope this will help to narrow down your problem.

    Sunday, August 25, 2013

    SQL Server Performance Tuning and Query Optimization Videos

    If you are like me, who don't get much chance to get your hands dirty in fine tuning sql server queries, then you must watch this videos.

    I am really thankful to this guy, who has posted such a useful videos.


    Frankly speaking their is lot of materials out their on this topic and I always avoid learning because of that.  This videos helped me to quickly get started to attack problem I was facing.

    If you landed here searching how to improve performance of your website then along with database sql indexing you should also look for this checklist.

    Generate C# Class from JSON File or URL

    I have came across a useful site which will be helpful in generating C# Class from JSON File or URL.

    This will be very useful when you are using JsonConvert.DeserializeObject Method.


    Generate JSON File from database data using c#

    Following code will help you to generate JSON file from database table.

    //Get records from database
    var products = db.Products.ToList();
    //Generate JSON from database data
    using (StringWriter writer = new StringWriter())
        // Json.Write sends a Json-encoded string to the writer.
        System.Web.Helpers.Json.Write(products, writer);
        // When ready, you can send the writer
        // output to the browser, a file, etc.
        /*Uncomment above line to view JSON
           output in browser*/
        using (StreamWriter outfile =
                   new StreamWriter(@"c:\Temp\Products.json"))

    Please note: In order to have this code work, you will need to have "System.web.Helpers" dll added to your solution.

    Saturday, August 17, 2013

    Adding Column to SQL Server using Database Defensive Programming Technique

    Recently I have learned good way to add column to sql server using database defensive programming technique from my co-worker.  All the credit for this blog post goes to him.  Thank you sir incase you are reading this blog post. (I have purposefully avoid mentioning name of co-worker due to privacy reason.)

    Following example is very simple and self explanatory, Incase if you didn't get anything then pass your comment in comment section.

    IF EXISTS(SELECT 1 from information_schema.tables 
              where table_name = 'MyTableName')
        Print('Table Exist');
        --Add Column MyColumn
        IF NOT EXISTS(SELECT 1 from information_schema.columns 
                      where table_name = 'MyTableName' 
                      and Column_Name='MyColumn')
     ALTER TABLE MyTableName ADD MyColumn varchar(345) NULL;
     Print('MyColumn Column Added');
     Print('MyColumn Column Already Exist');
      Print('Table does not Exist');
    IF @@ERROR <> 0
            PRINT('Problem in running script, Rolling back');
      PRINT('Script Run Successfully');

    Saturday, June 29, 2013

    What's new in VS.Net 2013 Preview and Asp.net

    I personally feel that upcoming VS.Net version will going to be really cool.  You will find lot of features out of box to quickly get you started for development.  This release will going to be for those, who wants to write less code and do rapid development with less code maintenance.

    VS.Net 2013 Preview feature list:

    • Responsive Design using Twitter Bootstrap
    • Scaffolding available for Asp.net web forms, mvc and web api projects.
    • Improvement to existing way of external authentication (Google, Twitter, FB, etc...)
    • Mobile switcher for asp.net application
    • One Asp.net - You can mix asp.net web form, mvc, web api other technologies to serve your needs.
    • And many more... Please note I didn't get chance to explore all.

    Check out following links to explore more on this topic:

    Scaffolding for Asp.net Webforms

    Those who knows asp.net mvc, might already knows the power of scaffolding...

    With VS.Net 2013 we will have scaffold template included for asp.net web forms.  By scaffold template you can generate a boilerplate code for asp.net web forms in a min.

    The Web Forms scaffold generator can automatically build Create-Read-Update-Delete (CRUD) views based on a model.

    Step by Step example of using scaffold template inside asp.net web forms to generate CRUD operations.

    Step 1: 
    Download VS.Net 2013 Express Preview

    Step 2:
    Create Asp.net WebForm Project

    Step 3:
    Add Model Class and named it as "Product.cs"  (Right click Model class and Add new class)

    Step 4:
    Create POCO Class with following content in it.  Yes you can take advantage of DataAnnotations of Entity framework.

    using System;
    using System.Collections.Generic;
    using System.ComponentModel.DataAnnotations;
    using System.Linq;
    using System.Web;

    namespace WebApplication3.Models
        public class Product
            public int ID { get; set; }

            public string ProductName { get; set; }
            public string Description { get; set; }
            public int Price { get; set; }        

    In above code 
    • [ScaffoldColumn(false)] will not generate any presentation code for that field
    • [StringLenght(25)] will limit product name upto 25 characters only.
    • [DataType(DataType.Currency)] will limit to enter only proper currency value.

    Step 5:
    Build your application.  (Cntrl + Shift + B)

    Step 6: 
    Now its time to use scaffold template to generate code for CRUD operations in asp.net web forms.
    Right click on model class and add scaffold for Product class.

    Step 7:
    Click on Add button as shown in figure to "Generates Asp.net Web Forms pages for read/write operations based on a given data model."

    Step 8:
    Select Model class as "Product.cs", Since we haven't created or have any data context class, select "Add new data context" and Click on "Add" button.

    Step 9:
    If you wish you can change name of your data context class.  For this demo purpose I am keeping everything to default generated by VS.Net.  Press "OK" button.

    Step 10:
    You will noticed that VS.Net 2013 has generated new data context file inside model folder and has also created CRUD operations for Product class.

    Step 11:
    Run the application and enjoy the boilerplate code...  You will also noticed that web forms project has extension-less url and also has responsive design using Twitter bootstrap out of the box...

    Type the url: /Product  notice it is extension-less url and

    Click on "Create new" link and create new product

    Data Entry few product.  Notice fancy validation on entering wrong values.  "Everything is out of box, you don't need to write a single line of code to make this working... Isn't that cool"

    Product Listing

    Similarly you can edit record, delete record...

    Hope you enjoyed this post...

    Wednesday, June 26, 2013

    OCIEnvCreate failed with return code -1 but error message text was not available. - Solution

    Recently I was facing an issue with connecting to oracle.  I was keep on getting below error when trying to connect oracle through .Net Code.

    Error: OCIEnvCreate failed with return code -1 but error message text was not available.

    Cause of Error:
    I am using Win 7 machine with Oracle Installed on it and using Oracle SQL Developer.  Few days back IT team at my company push updates and for some reason this updates image has uninstalled Oracle client on my machine.  Oracle Client being not installed on my system was the cause of this error.

    Check for Start > All Programs > Oracle - OraClient11g_home_64bit > Application Development > and Click on SQLPlus Icon.

    If that Icon brings up following error, then you need to reinstall Oracle Client in order to pass through this error.

    The drive or network connection that the shortcut 'SQL Plus.lnk' refers to is unavailable.  Make sure that the disk is properly inserted or the network resource is available, and then try again.

    If you are able to see "SQL Command Prompt" on your machine, then you do have good copy of Oracle Client installed on your machine, so you need to verify your .Net code to see whether proper connection string is assigned and so on...

    Following link might also help you...

    Hope this helps you in narrow down your problem.

    Most Recent Post

    Subscribe Blog via Email

    Enter your email address:

    Disclaimers:We have tried hard to provide accurate information, as a user, you agree that you bear sole responsibility for your own decisions to use any programs, documents, source code, tips, articles or any other information provided on this Blog.
    Page copy protected against web site content infringement by Copyscape