The underlying provider failed on Open Error in Entity Framework Transactions

Error:
The underlying provider failed on Open
MSDTC on server 'XXXXX\\SQLEXPRESS' is unavailable."   at System.Data.EntityClient.EntityConnection.OpenStoreConnectionIf(Boolean openCondition, DbConnection storeConnectionToOpen, DbConnection originalConnection, String exceptionCode, String attemptedOperation, Boolean& closeStoreConnectionOnFailure)\r\n   at System.Data.EntityClient.EntityConnection.Open()\r\n   at System.Data.Objects.ObjectContext.EnsureConnection()\r\n   at System.Data.Objects.ObjectContext.SaveChanges(Boolean acceptChangesDuringSave)\r\n   at System.Data.Objects.ObjectContext.SaveChanges()\r\n  

Cause:
The error The underlying provider failed on Open is thrown while performing a transactional update using Entity Framework, if the MSDTC - Distributed Transaction Coordinator service is not started.

Resolution:

Transactions in Entity Framework with DbTransaction.

In many real time scenarios we will have to use transactions to perform Atomic operation, i.e a set so statements should get executed completely or get rollback completely. A typical example of a transactions in the Bank account example, debit X amount from account A and credit the X amount to account B, now if there is an issue between debiting from account A and crediting to account B the whole operation should be cancelled.

In this post we shall see on how to perform transactions using Entity Framework and DbTransaction. Unlike the TransactionScope which needs the MSDTC - Distributed Transaction Coordinator service to be running, DbTransaction will work even if the service is not running.

In this post we will do 2 operations insert a row in the Bugs table and insert a row in the Comments table in a single transaction.

Transactions in Entity Framework with TranscationScope.

In many real time scenarios we will have to use transactions to perform Atomic operation, i.e a set so statements should get executed completely or get rollback completely. A typical example of a transactions in the Bank account example, debit X amount from account A and credit the X amount to account B, now if there is an issue between debiting from account A and crediting to account B the whole operation should be cancelled.

In this post we shall see on how to perform transactions using Entity Framework and TransactionScope. To make use of the TransactionScope we need to make sure that the MSDTC - Distributed Transaction Coordinator service is running. If the service is not running then the code will throw the following error.
The underlying provider failed on Open

In this post we will do 2 operations update the Bugs table and insert a row in the Comments table in a single transaction.

TranscationScope Vs DbTransaction

A transaction is an atomic operation, i.e a set so statements should get executed completely or get rollback completely. A typical example of a transactions in the Bank account example, debit X amount from account A and credit the X amount to account B, now if there is an issue between debiting from account A and crediting to account B the whole operation should be cancelled.

Transactions in the Entity Framework can be performed using 2 ways, using TranscationScope or by using DbTransaction. In this post we shall compare the 2 approaches.

Transactions in Entity Framework

A transaction is an atomic operation, i.e a set so statements should get executed completely or get rollback completely. A typical example of a transactions in the Bank account example, debit X amount from account A and credit the X amount to account B, now if there is an issue between debiting from account A and crediting to account B the whole operation should be cancelled.

Transactions in the Entity Framework can be performed using 2 ways, using TranscationScope or by using DbTransaction. In this post we shall compare the 2 approaches.

What's New in ASP.NET MVC4

The feature additions in Asp.Net MVC4 are relatively less when compared to the MVC2 – MVC3 upgrade. Asp.Net MVC4 mainly focuses on mobile web development, in addition it adds a few features to the existing Web application projects.

1. Includes support for Asp.Net Web API

What's New in ASP.NET MVC3

Asp.Net MVC3 has brought in a whole lot of features, which help in improved productivity, performance, scalability and security. 

Asp.Net MVC3 is the successor of Asp.Net MVC2, it requires .Net Framework 4.0 or higher and is compatible with Visual Studio 2010 or higher. We shall see some of the key feature additions to Asp.Net MVC3 in this post.

1. Introduction of Razor view engine

Asp.Net MVC Application Project Templates

Visual Studio 2008/2010, provides 2 templates for creating Asp.Net MVC Applications.

          Asp.Net MVC Web Application
          Asp.Net MVC Empty Web Application

Asp.Net MVC Web Application
The Asp.Net MVC Web Application template creates a project with a set of pre-defined files which are ready to run, this template will give a sample set of files on top of which the users can create additional files.

Asp.net MVC Vs Web Farms Application

Asp.Net MVC is not a replacement for the Web Forms model of Asp.net application, each has its own advantages and disadvantages, it is up to the user to decide on which model to user based on the requirements and other factors.

In this post we shall compare the features of Asp.Net MVC Applications and Web Farms Application

ASP.NET MVC - The Controller

As the name suggests the Controller is the one which controls the execution of the entire application, all requests to the applications are handled by the controller, once the controller receives the request it decided the model to be called, and the View to be invoked to render the output to the Browser.

A Model is a .vb or .cs class, Controller classes should end with the word “Controller” example EmployeeController, DepartmentController etc. The controller contains a set of Action methods, each of which is mapped to a specific request from the user.

ASP.NET MVC - The View

The View is the UI part of the MVC Architecture, the View is fully responsible for what the user sees in the browser, all UI aspects like controls, color, style, formatting etc are taken care of by the View.
The View defines the style and formatting aspects of the display and embeds the data provided by the model to produce a page which has meaningful information.

A View is an .aspx file, The Name of the View should match the name of the function in the controller which handles the View. For example a view with the name LogOn should have a matching method in the controller which handles requests for this View.

The Controller receives the request from the user and invokes the corresponding view, the view in turn makes use of the data from the Model and renders the page to the user in the browser.

ASP.NET MVC - The Model

The Model represents the data engine of the MVC architecture, the Model holds the data to be rendered to the UI, the View makes use of the data in the model and binds it appropriately in the Page to display data in the browser. The Model is also responsible for retrieving the data from and saving the data back to the underlying database.

A Model is a .vb or .cs class, Model classes should end with the word “Model” example EmployeeModel, DepartmentModel etc.

The Model class is accessible by both the View and the Controller; the data provided by the Model is used by the view to render information to the user in the browser. The View defines the style and formatting aspects of the display and embeds the data provided by the model to produce a page which has meaningful information.

ASP.NET MVC Architecture

The Asp.Net MVC architecture focuses on separating the UI design and the implementation logic, and thereby improves testability of the application.

The Architecture of Asp.Net MVC makes it possible to make use of advanced client side scripting concepts like JavaScript and jQuery. The Web Forms Architecture also supports JavaScript and jQuery but they don’t mingle well together, the Asp.Net MVC Architecture overcomes this issue and co-ordinates fully with JavaScript, jQuery and other client side scripting frameworks.

MVC stands for

ASP.NET MVC3 Prerequisite

An Asp.Net MVC3 Application need the following minimum requirements.

.NET Framework 4
 ASP.NET 4
 Visual Studio 2010 or Visual Web Developer 2010

Supported Operating Systems: 

ASP.NET MVC2 Prerequisite

An Asp.Net MVC2 Application need the following minimum requirements.

.Net Framework 3.5
Visual Studio 2008
Visual Studio 2008 SP1 or Visual Web Developer 2008 SP1

Supported Operating Systems: 

Asp.Net MVC Overview

Asp.Net MVC is one of the models provide by Asp.Net to build web applications, this model is based on the MVC (Model View Controller) design pattern. This model focuses in separating the design from the implementation logic, and is aptly applicable where intense testing is required in each of the blocks (design, logic and data access). Since this model separates design and implementation logic, it makes it possible to test each of these blocks individually.

How to find the entity framework version in your Machine

The ADO.Net Entity Framework comes in various versions starting from 3.5, the latest version at the time of writing this post is 6. Each version has its own set of features, so naturally you will need to know the version which you are working with to make use of the features in that version.

To know the version of the Entity Framework in your machine follow the steps below.

ADO.Net Entity Framework call Stored Procedures with Parameters

To work with Model using the ADO.Net Entity Data Model, we should first create an ADO.Net Entity Data Model class, refer to the post Creating your First ADO.Net Entity Data Model to see on how to create an ADO.Net Entity Data Model, that maps to the physical database.

Once you have created the ADO.Net Entity Data Model class, we can proceed with querying the model objects. In this post we shall see on how to use the ADO.Net Entity Data Model to call a Stored Procedure and pass parameters to the Procedure.

In this post, we will use an existing Stored procedure and call it using ADO.Net Entity Framework.

ALTER PROCEDURE [dbo].[InsertDepartment]

      @DepartmentName varchar(50)

AS

BEGIN

      INSERT INTO Department (Name) Values (@DepartmentName);

      SELECT ID, Name FROM DEPARTMENT WHERE ID = @@IDENTITY;

END

If you do not have a Stored procedure, create one. Once you have the Procedure in place, follow the below steps to map the Procedure to the ADO.Net Entity Framework.

1. Open the Model class (EmployeeModel.edmx)
2. Right click on the Designer and select Update Model from Database
3. This opens up the Object Browse, Expand the StoredProcedure node in the Object Tree and select the Stored Procedures to be used.

4. The Selected Procedures get added to the Stored procedure Node, in the Model Browser
5. Right click on the Designer and select Add -> Function Import
6. In the Popup which appears, select the Stored procedure Name, and give a Mapping Function Name.
7. Set the Return Type to one of the Objects in the Model, here I will set it as Department.

8. The new Function gets added to the Model under the Function Imports node in the Model Browser.
9. We have now created the mapping, call the mapping function from the code as follows.

EmployeesEntities dbContext = new EmployeesEntities();

var dep = dbContext.InsertDepartment("Accounts");

10. Once the code executes, a new row gets inserted into the Departments table.

One limitation of this approach is that the Entity Framework expects the Stored Procedure to return results which are compatible with Entity Framework. In this case, the procedure was returning results which were compatible with the Department type, hence it works fine, but if I want to create a custom statement by joining other tables and want to return the result then this approach will not work.

If the procedure returns a custom type which is not compatible with the Entities defined then this approach then we will have to define a Complex Type and map the return value of the procedure to the Complex Type.

The Complex Type definition is available only in Entity Framework 4 and above versions which ships with .Net Framework 4 and Visual Studio 2010. .Net Framework 3.5 SP1 with Visual Studio 2008 SP1 uses Entity Framework 3.5, which does not have the Complex Type definition option.

ADO.Net Entity Framework call Stored Procedures

In order to select data from a Model using the ADO.Net Entity Data Model, we should first create an ADO.Net Entity Data Model class, refer to the post Creating your First ADO.Net Entity Data Model to see on how to create an ADO.Net Entity Data Model, that maps to the physical database.

Once you have created the ADO.Net Entity Data Model class, we can proceed with querying the model objects. In this post we shall see on how to use the ADO.Net Entity Data Model to call a Stored Procedure and bind the results of the procedure to a GridView.

In this post, we will use an existing Stored procedure and call it using ADO.Net Entity Framework.

ALTER PROCEDURE [dbo].[GetEmployees]

AS

BEGIN

      SET NOCOUNT ON;

      SELECT E.ID, E.Name,E.DOB, E.DOJ,E.Phone,E.Email, D.Name as Department, E.Salery

       From Employee E INNER JOIN DEPARTMENT D

      On E.DepartmentID = D.ID

END

If you do not have a Stored procedure, create one. Once you have the Procedure in place, follow the below steps to map the Procedure to the ADO.Net Entity Framework.

1. Open the Model class (EmployeeModel.edmx)
2. Right click on the Designer and select Update Model from Database
3. This opens up the Object Browse, Expand the Stored Procedure node in the Object Tree and select the Stored Procedures to be used.

4. The Selected Procedures get added to the Stored procedure Node, in the Model Browser
5. Right click on the Designer and select Add -> Function Import
6. In the Popup which appears, select the Stored procedure Name, and give a Mapping Function Name.
7. Set the Return Type to one of the Objects in the Model, here I will set it as Employee.

8. The new Function gets added to the Model under the Function Imports node in the Model Browser.
9. We have now created the mapping, call the mapping function from the code as follows.

EmployeesEntities dbContext = new EmployeesEntities();

var emp = dbContext.GetEmployees();

//

grdEmployees.DataSource = emp.ToList();

grdEmployees.DataBind();

10. That’s it we have called a Stored Procedure using ADO.Net Entity Framework.

One limitation of this approach is that the Entity Framework expects the Stored Procedure to return results which are compatible with Entity Framework. In this case, the procedure was returning results which were compatible with the Employee type, hence it works fine, but if I want to create a custom statement by joining other tables and want to return the result then this approach will not work.

If the procedure returns a custom type which is not compatible with the Entities defined then this approach then we will have to define a Complex Type and map the return value of the procedure to the Complex Type.

The Complex Type definition is available only in Entity Framework 4 and above versions which ships with .Net Framework 4 and Visual Studio 2010. .Net Framework 3.5 SP1 with Visual Studio 2008 SP1 uses Entity Framework 3.5, which does not have the Complex Type definition option.

C# Win Form Validation

C# Win Form Validation

C# Windows Application Form validation using ErrorProvider Number validation using ErrorProvider in C# Windows Application Integer validation using ErrorProvider in C# Windows Application Combobox validation using ErrorProvider in C# Windows Application RegularExpression Validation using Regex class in C# Windows Application Email validation using ErrorProvider in C# Windows Application

C# Win Form Design

C# Win Form Design

Rounder Corner C# Windows Form Adding Image Buttons to C# Windows Form Adding a Custom Icon to C# Windows Application and Forms

C# Win Form Controls

C# Win Form Controls

Binding data to ComboBox in C# Windows Form Adding TabControl to a C# Windows Form C# Windows Forms ListView with multiple columns Binding data to a ListView with Multiple Columns in C# Windows Form

C# Win Form DataGridView

C# Win Form DataGridView

Get new rows in a DataTable / DataGridView in C# Windows Forms Get Modified rows in a DataTable / DataGridView in C# Windows Forms Get Deleted rows in a DataTable / DataGridView in C# Windows Forms

C# Win Form MDI Form

C# Win Form MDI Form

MDI Applications (Multiple-Document Interface) Creating MDI Parent Forms Add Menu to MDI Parent Form Add Toolbar to a MDI Parent Form Creating MDI Child Forms

C# Win Form App Config

C# Win Form App Config

C# Windows Application Reading app.config file C# Windows Application Reading app.config file

Deleting Data using ADO.Net Entity Framework

In order to select data from a Model using the ADO.Net Entity Data Model, we should first create an ADO.Net Entity Data Model class, refer to the post Creating your First ADO.Net Entity Data Model to see on how to create an ADO.Net Entity Data Model, that maps to the physical database.

Once you have created the ADO.Net Entity Data Model class, we can proceed with querying the model to select data. In this post we shall see on how to use the ADO.Net Entity Data Model to DELETE a record in the Employee table.

1. Create an Object of the ADO.Net Entity Data Model class
2. Get the Object of the Employee whose details needs to be Deleted
3. Delete the Object
4. Commit changes to the Model

Here is the code to DELETE rows using the ADO.Net Entity Data Model

EmployeesEntities dbContext = new EmployeesEntities();

//

// Get The Object of the Selected Employee

Employee objEmployee = (from emp in dbContext.Employee

                        where emp.ID == 235

                        select emp).First();

//

// Delete the Employee Object and Save the Detils.

dbContext.DeleteObject(objEmployee);

dbContext.SaveChanges();

5. Once we execute the code, a record for the Employee with ID 235 will get deleted from the Employee Table.

ht: normal;mso-layout-grid-align:none;text-autospace:none'>objEmployee.Salery = 7500;

//

// Save the Employee Details

dbContext.SaveChanges();

5. Notice that we are referring to the Departments object to get the DepartmentID value for the Employee table.

6. Once we execute the code, a new record for the Employee Tom will get updated in the Employee Table.

Related Post
Concepts
ADO.NET Entity Framework
ADO.NET Entity Framework Architecture
Entity Data Model
LINQ To SQL Vs ADO.Net Entity Framework

Programming
ADO.Net Entity Data Model Prerequisite
Creating your First ADO.Net Entity Data Model
Selecting Data using ADO.Net Entity Framework
Selecting Data by Joining Tables using ADO.Net Entity Framework
Inserting Data using ADO.Net Entity Framework
Inserting Data with Foreign Key Reference using ADO.Net Entity Framework
Updating Data using ADO.Net Entity Framework
Updating Data with Foreign Key Reference using ADO.Net Entity Framework
Deleting Data using ADO.Net Entity Framework

Updating Data using ADO.Net Entity Framework

In order to select data from a Model using the ADO.Net Entity Data Model, we should first create an ADO.Net Entity Data Model class, refer to the post Creating your First ADO.Net Entity Data Model to see on how to create an ADO.Net Entity Data Model, that maps to the physical database.

Once you have created the ADO.Net Entity Data Model class, we can proceed with querying the model to select data. In this post we shall see on how to use the ADO.Net Entity Data Model to UPDATE a record in the Employee table.

1. Create an Object of the ADO.Net Entity Data Model class
2. Get the Object of the Employee whose details needs to be Updated
3. Update the details in the Employee Object
4. Commit changes to the Model

Here is the code to UPDATE rows using the ADO.Net Entity Data Model

EmployeesEntities dbContext = new EmployeesEntities();

//

// Get the Existing details of the Employee

Employee objEmployee = (from emp in dbContext.Employee

                        where emp.Name == "Tom"

                        select emp).First();

//

// Update the Employee Details

objEmployee.Name = "Joseph";

objEmployee.DOB = "12/12/1962";

objEmployee.DOJ = "02/02/2002";

//

// Save the Employee Details

dbContext.SaveChanges();

5. Once we execute the code, a new record for the Employee Tom will get updated in the Employee Table.

Related Post
Concepts
ADO.NET Entity Framework
ADO.NET Entity Framework Architecture
Entity Data Model
LINQ To SQL Vs ADO.Net Entity Framework

Programming
ADO.Net Entity Data Model Prerequisite
Creating your First ADO.Net Entity Data Model
Selecting Data using ADO.Net Entity Framework
Selecting Data by Joining Tables using ADO.Net Entity Framework
Inserting Data using ADO.Net Entity Framework
Inserting Data with Foreign Key Reference using ADO.Net Entity Framework
Updating Data using ADO.Net Entity Framework
Updating Data with Foreign Key Reference using ADO.Net Entity Framework
Deleting Data using ADO.Net Entity Framework

Updating Data with Foreign Key Reference using ADO.Net Entity Framework

In order to select data from a Model using the ADO.Net Entity Data Model, we should first create an ADO.Net Entity Data Model class, refer to the post Creating your First ADO.Net Entity Data Model to see on how to create an ADO.Net Entity Data Model, that maps to the physical database.

Once you have created the ADO.Net Entity Data Model class, we can proceed with querying the model to select data. In this post we shall see on how to use the ADO.Net Entity Data Model to UPDATE a record in the Employee table, which references the Department Table through the Foreign Key DepartmentID 

1. Create an Object of the ADO.Net Entity Data Model class
2. Get the Object of the Employee whose details needs to be Updated
3. Update the details in the Employee Object
4. Commit changes to the Model

Here is the code to UPDATE rows using the ADO.Net Entity Data Model

EmployeesEntities dbContext = new EmployeesEntities();

//

// Get the Existing details of the Employee

Employee objEmployee = (from emp in dbContext.Employee

                        where emp.Name == "Tom"

                        select emp).First();

//

// Update the Employee Details

objEmployee.Name = "Joseph";

objEmployee.DOB = "12/12/1962";

objEmployee.DOJ = "02/02/2002";

objEmployee.Phone = "222-222-2222";

objEmployee.Email = "joseph@abcsoftware.com";

objEmployee.Department = (from dep in dbContext.Department

                          where dep.Name == "Sales"

                          select dep).First();

objEmployee.Salery = 7500;

//

// Save the Employee Details

dbContext.SaveChanges();

5. Notice that we are referring to the Departments object to get the DepartmentID value for the Employee table.

6. Once we execute the code, a new record for the Employee Tom will get updated in the Employee Table.

Related Post
Concepts
ADO.NET Entity Framework
ADO.NET Entity Framework Architecture
Entity Data Model
LINQ To SQL Vs ADO.Net Entity Framework

Programming
ADO.Net Entity Data Model Prerequisite
Creating your First ADO.Net Entity Data Model
Selecting Data using ADO.Net Entity Framework
Selecting Data by Joining Tables using ADO.Net Entity Framework
Inserting Data using ADO.Net Entity Framework
Inserting Data with Foreign Key Reference using ADO.Net Entity Framework
Updating Data using ADO.Net Entity Framework
Updating Data with Foreign Key Reference using ADO.Net Entity Framework
Deleting Data using ADO.Net Entity Framework

Inserting Data using ADO.Net Entity Data Model

In order to select data from a Model using the ADO.Net Entity Data Model, we should first create an ADO.Net Entity Data Model class, refer to the post Creating your First ADO.Net Entity Data Model to see on how to create an ADO.Net Entity Data Model, that maps to the physical database.

Once you have created the ADO.Net Entity Data Model class, we can proceed with querying the model to select data. In this post we shall see on how to use the ADO.Net Entity Data Model to INSERT a record into the Employee table.

1. Create an Object of the ADO.Net Entity Data Model class
2. Create an Object of the Employee Class
3. Add the details to be inserted to the Employee Object
4. Add the Employee Object to the ADO.Net Entity Data Model

5. Commit changes to the Model

Here is the code to INSERT rows using the ADO.Net Entity Data Model

EmployeesEntities dbContext = new EmployeesEntities();

//

Employee objEmployee = new Employee();

objEmployee.Name = "Chris";

objEmployee.DOB = "11/11/1967";

objEmployee.DOJ = "05/01/2009";

//

dbContext.AddToEmployee(objEmployee);

dbContext.SaveChanges();

6. Once we execute the code, a new record for Chris will get Inserted into the Employee Table.
Related Post
Concepts
ADO.NET Entity Framework
ADO.NET Entity Framework Architecture
Entity Data Model
LINQ To SQL Vs ADO.Net Entity Framework

Programming
ADO.Net Entity Data Model Prerequisite
Creating your First ADO.Net Entity Data Model
Selecting Data using ADO.Net Entity Framework
Selecting Data by Joining Tables using ADO.Net Entity Framework
Inserting Data using ADO.Net Entity Framework
Inserting Data with Foreign Key Reference using ADO.Net Entity Framework
Updating Data using ADO.Net Entity Framework
Updating Data with Foreign Key Reference using ADO.Net Entity Framework
Deleting Data using ADO.Net Entity Framework