How to use migration with Entity Framework Core

Entity framework core is  the lightweight, extensible and cross-platform version of Entity Framework. Before some time, Microsoft has Released a new version of Entity Framework RC2. I have written a couple of blog post about Entity framework code first migration earlier for Entity framework 6.0. So there was a couple of request coming for writing a blog post about Entity Framework Core RC2 migration. So I thought it will be a good idea to give an overview how database migration works in Entity Framework Core RC2. This post will cover a basic scenario where we are going to create the database with existing ASP.NET Identity migration and then we are going to create a new model and have that migration applied in the database.

How to use Entity Framework Migrations:

Let’s get started, To demonstrate entity framework core migrations, I am going to create a sample asp.net core web application like following.



Once we select asp.net core application it will appear the following dialog.



Now when you create a sample application. It will basically create a boilerplate code for the asp.net identity and as a part of that it is going to create entity framework migration files under Data –> Migrations folder.



Here you can find that sample code in GitHub repository given at the bottom.  Now we already asp.net identity migration code ready. So Let’s have those migrations applied with the following command from NuGet package manager console.

update-database

Now let’s add a new model “Employee” like following.

namespace CoreMigration.Models
{
    public class Employee
    {
        public int EmployeeId { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
    }
}

As we have employee class, We need to add migration for that. I’m going to create a migration for employee class via the following command.

add-migration AddEmployee

It will create “AddEmployee” migration class in Data->Migrations folder.



And here is the code for the migration for the same.

using Microsoft.EntityFrameworkCore.Migrations;
using Microsoft.EntityFrameworkCore.Metadata;

namespace CoreMigration.Data.Migrations
{
    public partial class AddEmployee : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.CreateTable(
                name: "Employees",
                columns: table => new
                {
                    EmployeeId = table.Column<int>(nullable: false)
                        .Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn),
                    FirstName = table.Column<string>(nullable: true),
                    LastName = table.Column<string>(nullable: true)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_Employees", x => x.EmployeeId);
                });
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropTable(
                name: "Employees");
        }
    }
}

Now we have our migration class ready so I am going to run the update-database command as following.



Now let’s check the database again and You can see employee table is there.



That’s it. It’s very easy. Hope you like it. Stay tuned for more!!.

You can find complete source code of this application at following location on Github - https://github.com/dotnetjalps/EntityFrameworkCoreMigration

Continue Reading

My Entity framework blog series

I have already written lots of blog post for entity framework. I thought it will be a good idea to make a blog post where I can list all the blog post about Entity framework. Here are is the list of all blog post I have written about Entity Framework.

Working with transaction in entity framework 6
How to use stored procedure with Entity framework code first
Entity framework code first, my sql and SQL Azure
Entity Framework Internals: Connection Resiliency
Entity Framework Internals: Private setters and private constructors
Entity Framework Internals: Enum Support
Entity Framework internals :IEnumerable and IQueryable
Complex type in EFCodeFirst
Different types of creating keys in EFCodeFirst
Different way of mapping with EFCodeFirst
EFCodeFirst Migrations
Entity Framework code first and Inheritance–Table per hierarchy
Entity framework code first and inheritance- Table Per Type
Entity Framework : There is already an open DataReader associated with this Command
Where I can find SQL Generated by Entity framework?
Execute TSQL statement with ExecuteStoreQuery in entity framework 4.0
Entity framework and Stored procedure output parameter
Some important attributes in EFCodeFirst
CRUD Operation with ASP.NET MVC and EFCodeFirst Part-1
CRUD Operation with ASP.NET MVC and EFCodeFirst Part-2
Invalid Object Name with Entity framework -EFCodeFirstCTP5
Entity Framework 4.0- Bind Stored Procedure with Result Entity class

Continue Reading

Working with transaction in Entity Framework 6

In  any Relation database, maintaining the integrity of the database is very important and Transaction is one way of maintaining database integrity. When you have the situation, where you need to insert data into multiple tables and if inserting a data in one of the table fails you should always rollback other inserts transaction becomes very useful. The Same scenario can occur for update or delete operations. If transactions are not there you will end up with lots of junk data in tables. Entity framework is one of most popular ORM in Microsoft.NET World. So in this post, we are going to learn how we can use transactions with Entity Framework 6.

Transaction and Entity Framework 6:

Entity framework internally maintains a transaction when you call SaveChanges() method. So all Inserts, update operation under single save changes method call will be in a single transaction. But when you want to wrap multiple SaveChanges() method under single transaction there was not inbuilt functionality in the earlier version of Entity framework. We have used to use TransactionScope class for the same.

But now with Entity Framework 6.0, We have two inbuilt APIs for Transaction.

DbContext.Database.BeginTransaction:

It allows us to Begin transaction for multiple save changes, You can combine as many operations as you want under the single transaction and hence either all will be performed successfully then the transaction will be committed  and if any exception occurred than transaction will be rollback.

DbContext.Database.UseTransaction :

Sometimes we need to use a transaction which is started outside of the entity framework. In this case, this option allows us to use that transaction with entity framework also.

In this blog post, We are going to use Begin Transaction. I will write a separate blog post about how to use existing transaction with entity framework.

So enough theory, let's create a console application understand it better.



We need entity framework. So I have added it via NuGet package.



In this application, we are going to use two model classes category and product. We will save them both in the single transaction and try to understand how the transaction works with Entity Framework.

namespace EFWithTransactions
{
    public class Category
    {
        public int CategoryId { get; set; }
        public string CategoryName { get; set; }
    }
}

And here is product model.

using System.ComponentModel.DataAnnotations.Schema;

namespace EFWithTransactions
{
    public class Product
    {
        public int ProductId { get; set; }
        public string ProductName { get; set; }
        [ForeignKey("Category")]
        public int CategoryId { get; set; }
        public virtual Category Category { get; set; }
    }
}

Here you can see I have category id there in product and a product is belong to category and I have created my DB context class like following.

using System.Data.Entity;

namespace EFWithTransactions
{
    public class ProductDbContext : DbContext
    {
        public ProductDbContext()
            : base("ProductConnectionString")
        {

        }
        public DbSet<Category> Categories { get; set; }
        public DbSet<Product> Products { get; set; }
    }
}

And following code is for Main method of console application, Which illustrate real times scenario where exception might occurs during multiple save changes().

using System;

namespace EFWithTransactions
{
    class Program
    {
        static void Main(string[] args)
        {
            using (ProductDbContext productDbContext = new ProductDbContext())
            {
                using (var transaction = productDbContext.Database.BeginTransaction())
                {
                    try
                    {
                        //saving category
                        Category category = new Category
                        {
                            CategoryName = "Clothes"
                        };
                        productDbContext.Categories.Add(category);
                        productDbContext.SaveChanges();

                        // Throw some error to check transaction
                        // Comment this to make transactions sucessfull
                        // throw new Exception("Custom Exception");

                        //saving product
                        Product product = new Product
                        {
                            ProductName = "Blue Denim Shirt",
                            CategoryId = category.CategoryId
                        };
                        productDbContext.Products.Add(product);
                        productDbContext.SaveChanges();
                        Console.Write("Cateogry and Product both saved");
                        transaction.Commit();
                    }
                    catch (Exception exception)
                    {
                        transaction.Rollback();
                        Console.WriteLine("Transaction Roll backed due to some exception");
                    }
                }

            }
            Console.ReadKey();
        }
    }
}

If you see above code carefully, you can see I have two save changes method one for category and another for the product. Also, I have used BeginTransaction method to initiate a new transaction. I have put one custom exception to illustrate something is wrong with that transaction. Also, try catch block is there so if any exception occurs catch block will rollback the transaction. If all went well it will commit the transaction.

Now let's run this application and following is an output as expected. As we have thrown an exception.



And there is no data inserted in database also.



Now let's comment the throw new exception part.

//throw new Exception("Custom Exception");

And now let's run our application again and here is the output as expected.



And now we have data in the database there.



So now we have a really good way to use transaction in entity framework. Hope you like it. Stay tuned for more!.

You can find complete source code of above blog post on github at - https://github.com/dotnetjalps/EF6WithTransaction

Continue Reading

How to use stored procedure with Entity Framework Code First

I'm getting lots of request from readers of my blog about writing blog post about how to use stored procedure with entity framework code first. So in this blog post we're going to learn about how we can use stored procedure with Entity framework code first.

To demonstrate that we are going to use create a table called Employee like following.


And here is the create table script for the same.

CREATE TABLE [dbo].[Employee](
[EmployeeId] [int] NOT NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[Designation] [nvarchar](50) NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 
(
[EmployeeId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

And following is the data I have inserted there.



And here is the store procedure we are using for that.

CREATE PROCEDURE usp_GetAllEmployees
AS
SELECT EmployeeId,FirstName,LastName,Designation FROM Employee

Now we have already done with our database side, it's time to write some C# code. I'm going to use simple console application for the same.



Now It's time to add entity framework via nuget package .



Here is the model class I have created.

namespace CodeFirstStoredProcedure
{
public class Employee
{
public int EmployeeId { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string Designation { get; set; }
}
}

And then I have created entity framework dbcontext like following.

using System.Data.Entity;

namespace CodeFirstStoredProcedure
{
public class EmployeeDbContext : DbContext
{
public EmployeeDbContext()
: base("EmployeeConnectionString")
{

}
public DbSet<Employee> Employees { get; set; }
}
}

And following is a code for using stored procedure to get data from database via entity framework code first.

using System;
using System.Linq;

namespace CodeFirstStoredProcedure
{
class Program
{
static void Main(string[] args)
{
using (EmployeeDbContext dbContext = new EmployeeDbContext())
{
string commandText = "[dbo].[usp_GetAllEmployees]";
var employees = dbContext.Database.SqlQuery<Employee>(commandText).ToList();

Console.WriteLine("Printing Employee");
foreach (var employee in employees)
{
Console.WriteLine(employee.EmployeeId);
Console.WriteLine(employee.FirstName);
Console.WriteLine(employee.LastName);
Console.WriteLine(employee.Designation);
Console.WriteLine("-----------------");
}
}
Console.ReadKey();
}
}
}

Here in the above code, If you see care fully. I have used SQL Query function to execute stored procedure and that will return a list of employee. Then I have printed this list with console.writeline.  Now when you run this application, output will be as expected.



That's it. It's very easy to use stored procedure with entity framework code first. Hope you like it. Stay tuned for more!!.

You can find complete source code of this sample application at - following location - https://github.com/dotnetjalps/EFCodeFirstStoredProcedure

Continue Reading

Entity Framework Code First and MySql on Azure

We have used Entity framework with SQL Servers mostly. In this blog post we are going to see how we can use Entity framework with MySql on Azure.

How to create MySql database on Azure:

On azure MySql is not maintained by Microsoft itself. It is provided their partner cleardb. So You need to search at Azure Market Place and then add it like below.

Continue Reading

Entity Framework Internals: Connection Resiliency

When you use cloud services to deploy your application and database it might be possible that transient connection will be a problem between database server and web servers. When you use on premise servers that uses the database server and web server on same data centre. When you use cloud there will be huge infrastructures and you never know where it is deployed even if it deployed on same data centre there will more connection like network load balancers etc. When you use cloud services that will be shared by lots of users which means its responsiveness can be affected by them. And your access to the database might be subject to throttling. Throttling means the database service throws exceptions when you try to access it more frequently than is allowed in your Service Level Agreement (SLA).

So in cloud service there will be transient problems which will be resolved in short period of time. So when you got such kind of errors then you can wait for some time and then you have retry. For that kind of operation Entity Framework provides connection resiliency feature.

The connection resiliency features must be configured for proper database services. It has to know which exceptions are likely to be transient problem and which exceptions are caused by our code. It has to wait for an appropriate amount of time between retries of failed operation. Also it has to try number of times before giving up.

Continue Reading

Entity Framework Internals: Private setters and private constructors

I have been heavily learning Domain Driven Design recently and In that there is a concept called “Anemic Domain Model” which we used to use in our project. In simple terms Anemic Domain Models are those classes that do not have any behaviour with them and most of created with properties. You can use them as container for your data from database. While Domain driven design is all about behaviours. So we need to make our models incorporate behaviours also which is called  “Rich Domain models”.

One of step to convert your Anemic Domain Models to Rich Domain Models is to create parameterised constructors. For example an Employee must have FirstName and LastName. So instead of doing validation at the insertion time on UI we should not allow Employee to be created without FirstName and Lastname. The only way to do this to make this properties setters private and assign value of those properties via parameters constructors like below.

public class Employee
{
        public Employee(string firstName, string lastName)
        {
            FirstName = firstName;
            LastName = lastName;
        }
            
        public int EmployeeId { get; set; }
        public string FirstName { get; private set; }
        public string LastName { get; private set; }
        public string Designation { get; set; }
}

Now, If you want to do DDD with any Object Relational Mappers there will be a problem as most of Object Relational Mappers create properties with public setters. Entity Framework is such a Object Relational mapper and I love to work with Entity Framework.

Continue Reading

Fluent Nhibernate : Create Table from Code(Class- CodeFirst)

Now days,  Convention over configured is more preferred and that’s why Fluent Nhibernate got more popularity Because with Fluent Nhibernate we don’t have to write and maintain mapping in xml. You can write classes to map your domain objects to your database tables. If you want to learn how we can do this with existing database and Fluent Nhibernate, I have also written a blog post about CRUD operation with Fluent Nhibernate and ASP.NET MVC . After writing this blog post I was getting a lots of emails about how we can create database from the Fluent Nhibernate as we can do same with Entity Framework Code First. So I thought it’s a good idea to write a blog post about it instead of writing individual emails.

How to create tables from class via Fluent Nhibernate:

To Demonstrate how we can create tables based mapping classes create we’re going to create a console application via adding new project like below.

Continue Reading

Entity Framework Internals: Enum Support

In .NET world, We all are using Enums in our code. It’s makes our code more readable then using hardcoded numbers of strings. Entity Framework 5.0 or higher version has support for Enums. In this blog post we are going to see how we can use Enums with entity framework.

We are going to create a console application to see how it’s works. I’m going to create a console application for the same.



After creating a application I’ve added Entity framework via Nuget package.

Continue Reading

Entity Framework internals :IEnumerable and IQueryable

Now days, Entity framework is one the most used ORM in .NET world. Still I can see lots of people confused with IQueryable and IEnumerable. If they dont' know what is difference between them then they are going to make mistakes .When you first look at it both looks same. But there are quite differences and If you don’t understands internals of it then it will impact your query performance also.

IEnumerable vs IQueryable:

Here is the basic difference between IEnumerable and IQueryable. When you write queries with IEnumerable it executes queries on Server and load data in memory and then filter data on client side. This will be a problem when you are fetching large amount of data. While IQueryable executes queries on server side with all filters and then load all data into memory.

Example:

Sound complex!. Let’s write a simple example to understand it better. I am going to create a simple table in database called student table.

Continue Reading

Complex type in EFCodeFirst

I have already written a few blog post about EF Code first and it’s features following is a complete list of blog posts.

Entity framework code first and inheritance- Table Per Type
Entity Framework code first and Inheritance–Table per hierarchy
Entity Framework Code First migrations
Different way of mapping with EFCodeFirst
Different way of creating keys in EFCodeFirst

This post will also be part of this EF code first series. In this blog post we are going to learn how we can handle complex type in EF Code First. In our day to day operation we are having different kind of database tables where  there are multiple type of relationships  are defined like one-to-one, one-to-many and many-to-many. This all kind of relationship is handle by complex types.

Continue Reading

Different way of creating keys in EFCodeFirst

Recently I have been playing with Entity Framework Code First and I like it a lot. There are plenty of features given and you can configure all that stuff with code. I have already written couple of post for Entity Framework Code First.

Different way of mapping in EFCodeFirst
Entity Framework code first and Inheritance–Table per hierarchy
Entity framework code first and inheritance- Table Per Type

As you EFCodeFirst generate database table based on the configuration defined in classes. Today we are going to learn about how we can create key with different configurations.

Continue Reading

CRUD Operation with ASP.NET MVC and Fluent Nhibernate.

Before some time I have written a post about Getting Started with Nhibernate and ASP.NET MVC –CRUD operations. It’s one of the most popular post blog post on my blog. I get lots of questions via email and other medium why you are not writing a post about Fluent Nhibernate and ASP.NET MVC. So I thought it will be a good idea to write a blog post about it.

What is Fluent Nhibernate:

Convection over configuration that is mantra of Fluent Hibernate If you have see the my blog post about Nhibernate then you might have found that we need to create xml mapping to map table. Fluent Nhibernate uses POCO mapping instead of XML mapping and I firmly believe in Convection over configuration that is why I like Fluent Nhibernate a lot. But it’s a matter of Personal Test and there is no strong argument why you should use Fluent Nhibernate instead of Nhibernate.

Fluent Nhibernate is team Comprises of James Gregory, Paul Batum, Andrew Stewart and Hudson Akridge. There are lots of committers and it’s a open source project.

You can find all more information about at following site.

http://www.fluentnhibernate.org/

On this site you can find definition of Fluent Nhibernate like below.

Fluent, XML-less, compile safe, automated, convention-based mappings for NHibernate. Get your fluent on.

They have excellent getting started guide on following url. You can easily walk through it and learned it.
https://github.com/jagregory/fluent-nhibernate/wiki/Getting-started

ASP.NET MVC and Fluent Nhibernate:

So all set it’s time to write a sample application. So from visual studio 2013 go to file – New Project and add a new web application project with ASP.NET MVC.

Continue Reading

Different way of mapping with EFCodeFirst

Recently I have been working Entity Framework Code First 6.0 and it’s awesome. I am in love with it. It has got great fluent API and we can do anything with that. It’s also promotes Convention over configuration which I love so far.

I have seen people are more confused about how we can map table to class via Entity framework via code first. So this post is going to be in same series. In this post I’m going to write about different ways to mapping table to class. I have already written two post about in details.

Entity Framework code first and Inheritance–Table per hierarchy
Entity framework code first and inheritance- Table Per Type

In this blog post I will give you tips and tricks to map class(type) with database table.

Continue Reading

Entity Framework Code First migrations

In this blog post we are going to learn about entity code first migrations. Entity Framework code first approach will allow you to define model classes as per the domain requirements via POCOs. Hence you have complete control over classes are written. But as application grows and there are some new features to be added and  your classes will change. Entity Framework Code Migrations allows you to handle this migrations.

As we all now that Entity Framework Code First approach allows you to create database based on your classes created. It’s provide you three types of initializers.

CreateDatabaseIfNotExist: This is the default initializer which will create classes if database not exists.

DropCreateDatabasesWhenModelChanges: This initializer is only good when you don’t concern about your database records. If there is any change in class then it will match database will classes and if there is any difference then it will drop and create a new database.

DropCreateDatabaseAlways: This initializer will always create new database whether database exist or not. If database exist then it will drop that database and create it again.

Continue Reading

Entity Framework code first and Inheritance–Table per hierarchy

Before some day I have posted a blog about Entity Framework code first and Inheritance – Table per type and this post in next in series with that.

In previous post we have learned about how entity framework code first handles inheritance and in this part we are going to extend this and modify some of code of the code of data context to see how its creates a “Table per Hierarchy”.

We’re going to use same code as previous post just going to change EDataContext code like below.

public class EDataContext : DbContext
{
    public EDataContext() : base("MyConnectionString") 
    { 
    }
    public IDbSet<Customer> Customers { get; set; }
    public IDbSet<Employee> Employees { get; set; }
    public IDbSet<Person> Persons { get; set; }
}

If you see this code carefully and compare is with previous post. The only difference is persons property. That will force Entity Framework to create a single table for inheritance hierarchy. Now we are going to run this application again and see how it creates table. It will create only one table like this.

Continue Reading

Entity framework code first and inheritance- Table Per Type

Also see part-2 of this blog post- Entity Framework code first and Inheritance–Table per hierarchy

Recently I am using Entity Framework a lot. So I tried some of advance scenario and this post related to it only. In this post we will learn how entity framework handles inheritance.

In object oriented world, We all use Inheritance for reusability. We create some base classes and inherit those in child classes to facilitate code reuse and we don’t have to write again and again. In this post we will also have same kind of methodology. We are going to create a “Person” class and inherit this in to Employee and Customer class and we are going to see how entity framework code first handles this kind of inheritance by default. I’m going to use entity framework version 6.0 and Visual Studio 2013.

So what we are waiting for. Let’s get started. So create a console application from file menu new project.



Once you are done with creating a application. It’s time to add entity framework via nuget package.

Continue Reading

PetaPoco series–DotNetJalps

Recently one of the friend ask to create list of post that I have written about PetaPoco this post is a complete list of all my PetaPoco related post.

What is PetaPoco?

PetaPoco is a Microsoft ORM developed by Top Ten Software. It was inpired by Massive and Dapper Micro ORM. Following is a link where you can get all information about PetaPoco.

http://www.toptensoftware.com/petapoco/

PetaPoco related blog posts on DotNetJalps:

Following is a complete list of PetaPoco posts that I have written on my blog. I will keep updating this list also for future posts about PetaPoco.

Get started with ASP.NET MVC and PetaPoco
PetaPoco with stored procedures
PetaPoco with parameterised stored procedure and Asp.Net MVC
CRUD operations with PetaPoco and ASP.NET MVC

Hope you like it. Stay tuned for more.

Continue Reading

Getting Started with NHibernate and ASP.NET MVC- CRUD Operations

In this post we are going to learn how we can use NHibernate in ASP.NET MVC application.

What is NHibernate:

ORMs(Object Relational Mapper) are quite popular this days. ORM is a mechanism to map database entities to Class entity objects without writing a code for fetching data and write some SQL queries. It automatically generates SQL Query for us and fetch data behalf on us.

NHibernate is also a kind of Object Relational Mapper which is a port of popular Java ORM Hibernate. It provides a framework for mapping an domain model classes to a traditional relational databases. Its give us freedom of writing repetitive ADO.NET code as this will be act as our database layer. Let’s get started with NHibernate.

How to download:

There are two ways you can download this ORM. From nuget package and from the source forge site.

1. Nuget - http://www.nuget.org/packages/NHibernate/
2. Source Forge-http://sourceforge.net/projects/nhibernate/

Creating a table for CRUD:

I am going to use SQL Server 2012 express edition as a database. Following is a table with four fields Id, First Name, Last name, Designation.

Creating ASP.NET MVC project for NHibernate:

Let’s create a ASP.NET MVC project for NHibernate via click on File-> New Project –> ASP.NET MVC 4 web application.

Installing NuGet package for NHibernate:

I have installed nuget package from Package Manager console via following Command.

It will install like following.

NHibertnate configuration file:

Nhibernate needs one configuration file for setting database connection and other details. You need to create a file with ‘hibernate.cfg.xml’ in model Nhibernate folder of your application with following details.

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
  <session-factory>
    <property name="connection.provider">
      NHibernate.Connection.DriverConnectionProvider
    </property>
    <property name="connection.driver_class">
      NHibernate.Driver.SqlClientDriver
    </property>
    <property name="connection.connection_string">
      Server=(local);database=LocalDatabase;Integrated Security=SSPI;
    </property>
    <property name="dialect">
      NHibernate.Dialect.MsSql2012Dialect
    </property>
  </session-factory>
</hibernate-configuration>

Here you have got different settings for NHibernate. You need to selected driver class, connection provider as per your database. If you are using other databases like Orcle or MySQL you will have different configuration. ThisNHibernate ORM can work with any databases.

Creating a model class for NHibernate:

Now it’s time to create model class for our CRUD operations. Following is a code for that. Property name is identical to database table columns.

namespace NhibernateMVC.Models
{
    public class Employee
    {
        public virtual int Id { get; set; }
        public virtual string FirstName { get; set; }
        public virtual string LastName { get; set; }
        public virtual string Designation { get; set; }
    }
}

Creating a mapping file between class and table:

Now we need a xml mapping file between class and model with name “Employee.hbm.xml” like following in Nhibernate folder.

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" auto-import="true" assembly="NhibernateMVC" namespace="NhibernateMVC.Models">
  <class name="Employee" table="Employee" dynamic-update="true" >
    <cache usage="read-write"/>
    <id name="Id" column="Id" type="int">
      <generator class="native" />
    </id>
    <property name="FirstName" />
    <property name="LastName" />
    <property name="Designation" />
  </class>
</hibernate-mapping>

Creating a class to open session for NHibernate:

I have created a class in models folder called NHIbernateSession and a static function it to open a session for NHibertnate.

using System.Web;
using NHibernate;
using NHibernate.Cfg;

namespace NhibernateMVC.Models
{
    public class NHibertnateSession
    {
        public static ISession OpenSession()
        {
            var configuration = new Configuration();
            var configurationPath = HttpContext.Current.Server.MapPath(@"~\Models\Nhibernate\hibernate.cfg.xml");
            configuration.Configure(configurationPath);
            var employeeConfigurationFile = HttpContext.Current.Server.MapPath(@"~\Models\Nhibernate\Employee.hbm.xml");
            configuration.AddFile(employeeConfigurationFile);
            ISessionFactory sessionFactory = configuration.BuildSessionFactory();
            return sessionFactory.OpenSession();
        }
    }
}

Listing:

Now we have our open session method ready its time to write controller code to fetch data from the database. Following is a code for that.

using System;
using System.Web.Mvc;
using NHibernate;
using NHibernate.Linq;
using System.Linq;
using NhibernateMVC.Models;

namespace NhibernateMVC.Controllers
{
    public class EmployeeController : Controller
    {

        public ActionResult Index()
        {
            using (ISession session = NHibertnateSession.OpenSession())
            {
                var employees = session.Query<Employee>().ToList();
                return View(employees);
            }
           
        }
    }
}

Here you can see I have get a session via OpenSession method and then I have queried database for fetching employee database. Let’s create a new for this you can create this via right lick on view on above method.We are going to create a strongly typed view for this.



Our listing screen is ready once you run project it will fetch data as following.

Create/Add:

Now its time to write add employee code. Following is a code I have written for that. Here I have used session.save method to save new employee. First method is for returning a blank view and another method with HttpPost attribute will save the data into the database.

public ActionResult Create()
{
    return View();
}

      
[HttpPost]
public ActionResult Create(Employee emplolyee)
{
    try
    {
        using (ISession session = NHibertnateSession.OpenSession())
        {
            using (ITransaction transaction = session.BeginTransaction())
            {
                session.Save(emplolyee);
                transaction.Commit();
            }
        }

        return RedirectToAction("Index");
    }
    catch(Exception exception)
    {
        return View();
    }
}

Now let’s create a create view strongly typed view via right clicking on view and add view.



Once you run this application and click on create new it will load following screen.

Edit/Update:

Now let’s create a edit functionality with NHibernate and ASP.NET MVC. For that I have written two action result method once for loading edit view and another for save data. Following is a code for that.

public ActionResult Edit(int id)
{
    using (ISession session = NHibertnateSession.OpenSession())
    {
        var employee = session.Get<Employee>(id);
        return View(employee);
    }
   
}


[HttpPost]
public ActionResult Edit(int id, Employee employee)
{
    try
    {
        using (ISession session = NHibertnateSession.OpenSession())
        {
            var employeetoUpdate = session.Get<Employee>(id);

            employeetoUpdate.Designation = employee.Designation;
            employeetoUpdate.FirstName = employee.FirstName;
            employeetoUpdate.LastName = employee.LastName;

            using (ITransaction transaction = session.BeginTransaction())
            {
                session.Save(employeetoUpdate);
                transaction.Commit();
            }
        }
        return RedirectToAction("Index");
    }
    catch
    {
        return View();
    }
}

Here in first action result I have fetched existing employee via get method of NHibernate session and in second I have fetched and changed the current employee with update details. You can create view for this via right click –>add view like below. I have created a strongly typed view for edit.



Once you run code it will look like following.

Details:

Now it’s time to create a detail view where user can see the employee detail. I have written following logic for details view.

public ActionResult Details(int id)
      {
          using (ISession session = NHibertnateSession.OpenSession())
          {
              var employee = session.Get<Employee>(id);
              return View(employee);
          }
      }

You can add view like following via right click on actionresult view.



now once you run this in browser it will look like following.

Delete:

Now its time to write delete functionality code. Following code I have written for that.

public ActionResult Delete(int id)
{
    using (ISession session = NHibertnateSession.OpenSession())
    {
        var employee = session.Get<Employee>(id);
        return View(employee);
    }
}

       
[HttpPost]
public ActionResult Delete(int id, Employee employee)
{
    try
    {
        using (ISession session = NHibertnateSession.OpenSession())
        {
            using (ITransaction transaction = session.BeginTransaction())
            {
                session.Delete(employee);
                transaction.Commit();
            }
        }
        return RedirectToAction("Index");
    }
    catch(Exception exception)
    {
        return View();
    }
}

Here in the above first action result will have the delete confirmation view and another will perform actual delete operation with session delete method.



When you run into the browser it will look like following.



That’s it. It’s very easy to have crud operation with NHibernate. Stay tuned for more.CodeProject

Continue Reading

Entity Framework : There is already an open DataReader associated with this Command

Recently working on an application with entity framework 4.1 When I was retrieving data and updating data with same data context I was getting following error. It was working fine on another machine but on mine it was giving this error.

There is already an open DataReader associated with this Command.

After digging into the problem for some time I have found that I have not opened multiple active result set in my connection string. So what was happening when I retrieved data with entity framework there was an already opened data reader with that and after that I was updating data so we all know that an data reader is always have connected to particular connection. So it was not allowing me to update the data.
So I have added following attribute to my connection string.

“MultipleActiveResultSets=True”

Then I again test above scenario and it was working fine. It’s very easy. Hope you like it. Stay tuned for more..

Continue Reading