In this post I will be looking into EF 6.0 using the Code First Workflow and its support for stored procedures and more particularly how to display data using a stored procedure. I have posted something similar in this post https://weblogs.asp.net/dotnetstories/using-stored-procedures-in-an-asp-net-mvc-5-0-application-using-entity-framework-6-0 but this post described in detail how to insert, update and delete data in an ASP.Net MVC 6.0 application using stored procedures but not how to display data using a stored procedure.
I will be building a simple ASP.Net MVC 5.0 application that will be the client application that will consume the Entity Framework data access layer.
I will be using Visual Studio 2015,C# 5.0 and LocalDb in this demo.
I will be leveraging the scaffolding functionality as much as possible.
I will also show you how to log queries sent to the database by Entity Framework 6.0.
With EF 6.0 we do have a mechanism to trace/profile everything EF sents to the data store.
We will create the ASP.NET Web Application with the MVC 5 Project Template.
1) Launch Visual Studio 2015 and click on the "New Project". Select Web from the left pane and create the ASP.NET Web Application. Have a look at the picture below
2) Select the MVC Project Template as shown below and then click OK.
3) Visual Studio 2105 will automatically create the ASP.Net MVC 5.0 application.In the Models folder we will add a new class file, Footballer.cs
Right-click on the Models folder and Add a new Class, Footballer.cs
public class Footballer
{
public int FootballerID { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public double Weight { get; set; }
public double Height { get; set; }
}
4) We will add declarative code in this simple class in order to define that we need the properties (columns in the database) to be required - not null fields in the database.
We need to reference the System.ComponentModel.DataAnnotations assembly. We will use Data Annotations and not the Fluent API. Have a look at the code below.
using System.ComponentModel.DataAnnotations;
namespace EF6StoredProcMVC.Models
{
public class Footballer
{
public int FootballerID { get; set; }
[Required]
public string FirstName { get; set; }
[Required]
public string LastName { get; set; }
[Required]
public double Weight { get; set; }
[Required]
public double Height { get; set; }
}
}
5) Now I am going to build the Controller. I am going to the Controllers folder and click Add --> New Scaffolded Item.
Have a look at the picture below
6) In the Add Scaffold wizard, select the MVC 5 Controller with views,using Entity Framework and click Add.
Have a look at the picture below
7) In the next Add Controller wizard window, select the Model Class (Footballer) and add the Data Context class.Create a Data Context class with a suitable name and select a name for the controller.Finally click Add.
I will use the async controller actions option.
Have a look at the picture below
8) Have a look at the generated FootballerDBContext.cs
using System.Data.Entity;
namespace EF6StoredProcMVC.Models
{
public class FootballerDBContext : DbContext
{
// You can add custom code to this file. Changes will not be overwritten.
//
// If you want Entity Framework to drop and regenerate your database
// automatically whenever you change your model schema, please use data migrations.
// For more information refer to the documentation:
// http://msdn.microsoft.com/en-us/data/jj591621.aspx
public FootballerDBContext() : base("name=FootballerDBContext")
{
}
public System.Data.Entity.DbSet<EF6StoredProcMVC.Models.Footballer> Footballers { get; set; }
}
}
9) Have a look at the FootballersController.cs that was also generated. Υou will see methods for displaing, adding, editing and deleting data. All this code was generated through the magic of scaffolding. Take your time to study the code below.
using System.Data.Entity;
using System.Threading.Tasks;
using System.Net;
using System.Web.Mvc;
using EF6StoredProcMVC.Models;
namespace EF6StoredProcMVC.Controllers
{
public class FootballersController : Controller
{
private FootballerDBContext db = new FootballerDBContext();
// GET: Footballers
public async Task<ActionResult> Index()
{
return View(await db.Footballers.ToListAsync());
}
// GET: Footballers/Details/5
public async Task<ActionResult> Details(int? id)
{
if (id == null)
{
return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
}
Footballer footballer = await db.Footballers.FindAsync(id);
if (footballer == null)
{
return HttpNotFound();
}
return View(footballer);
}
// GET: Footballers/Create
public ActionResult Create()
{
return View();
}
// POST: Footballers/Create
// To protect from overposting attacks, please enable the specific properties you want to bind to, for
// more details see http://go.microsoft.com/fwlink/?LinkId=317598.
[HttpPost]
[ValidateAntiForgeryToken]
public async Task<ActionResult> Create([Bind(Include = "FootballerID,FirstName,LastName,Weight,Height")] Footballer footballer)
{
if (ModelState.IsValid)
{
db.Footballers.Add(footballer);
await db.SaveChangesAsync();
return RedirectToAction("Index");
}
return View(footballer);
}
// GET: Footballers/Edit/5
public async Task<ActionResult> Edit(int? id)
{
if (id == null)
{
return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
}
Footballer footballer = await db.Footballers.FindAsync(id);
if (footballer == null)
{
return HttpNotFound();
}
return View(footballer);
}
// POST: Footballers/Edit/5
// To protect from overposting attacks, please enable the specific properties you want to bind to, for
// more details see http://go.microsoft.com/fwlink/?LinkId=317598.
[HttpPost]
[ValidateAntiForgeryToken]
public async Task<ActionResult> Edit([Bind(Include = "FootballerID,FirstName,LastName,Weight,Height")] Footballer footballer)
{
if (ModelState.IsValid)
{
db.Entry(footballer).State = EntityState.Modified;
await db.SaveChangesAsync();
return RedirectToAction("Index");
}
return View(footballer);
}
// GET: Footballers/Delete/5
public async Task<ActionResult> Delete(int? id)
{
if (id == null)
{
return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
}
Footballer footballer = await db.Footballers.FindAsync(id);
if (footballer == null)
{
return HttpNotFound();
}
return View(footballer);
}
// POST: Footballers/Delete/5
[HttpPost, ActionName("Delete")]
[ValidateAntiForgeryToken]
public async Task<ActionResult> DeleteConfirmed(int id)
{
Footballer footballer = await db.Footballers.FindAsync(id);
db.Footballers.Remove(footballer);
await db.SaveChangesAsync();
return RedirectToAction("Index");
}
protected override void Dispose(bool disposing)
{
if (disposing)
{
db.Dispose();
}
base.Dispose(disposing);
}
}
}
10) You can also have a look at the generated views that were created by the Scaffold wizzard.
We choose to open the _Layout.cshtml view and add the following line of code in the navigation pane
<li>@Html.ActionLink("Footballers", "Index", "Footballers")</li>
the full code is
<ul class="nav navbar-nav">
<li>@Html.ActionLink("Home", "Index", "Home")</li>
<li>@Html.ActionLink("About", "About", "Home")</li>
<li>@Html.ActionLink("Footballers", "Index", "Footballers")</li>
<li>@Html.ActionLink("Contact", "Contact", "Home")</li>
</ul>
Now we can navigate to our page through the menu.
Build and run your application.It will take some time to appear in the first time since the database is created.The database is under the App_Data special folder.
Have a look at the picture below to see the created database.
When you open the database, you will see the table with the appropriate columns created. Have a look at the picture below.
When you will see you application, click on Footballers from the menu and then add some sample data. Have a look at the picture below.
11) After you insert those records in the database then you would wonder what is the T-SQL that EF DBContext sends to the SQL Server LocalDb to execute.
There are various ways to profile the T-SQL statements that EF sends to the data store. Have a look here, here and here for some ways of profiling the data in earlier versions of EF.
You can always use the SQL Server Profiler. In EF 6.0 we can trace the T-SQL statements using the the Log property of DbContext.
I am going to add some code in the FootballersController.cs class file in order to intercept the T-SQL statements.
I am going to add inside the FootballersController constructor the following code.
public FootballersController()
{
db.Database.Log = T => Debug.Write(T);
}
Make sure you add a reference to the System.Diagnostics assembly.
Have a look below to see where above statement fits with everything.
using System.Data.Entity;
using System.Threading.Tasks;
using System.Net;
using System.Web.Mvc;
using EF6StoredProcMVC.Models;
using System.Diagnostics;
namespace EF6StoredProcMVC.Controllers
{
public class FootballersController : Controller
{
private FootballerDBContext db = new FootballerDBContext();
public FootballersController()
{
db.Database.Log = T => Debug.Write();
}
// GET: Footballers
public async Task<ActionResult> Index()
{
return View(await db.Footballers.ToListAsync());
}
// More code follows
Now If I build and run my application again and try to insert more records and I have my Output window open i can see wha is sent to the database.
Have a look at the picture below. Αs you can see a transaction is opened and values are inserted using an Insert statement. Then the transaction is commited. So we can see the complete T-SQL code. Note that when we have an Insert statement EF engine starts a transaction.
12) Now we need to display the Footballers with a stored procedure.
First we need to add the stored procedure to the database. Open the SQLServer Object Explorer. Select and expand the database and in the Programmability node, select Stored Procedures and then add a new stored procedure.
The stored procedure is really easy to code.
Create Proc DisplayFootballers
AS
BEGIN
/*selecting all records from the table Footballers*/
Select * From dbo.Footballers
END
After you type it , then click Update. The stored procedure will be created and will be part of the database.
Have a look at the picture below.
13) Now we need to make changes to the FootballersController.cs and more particularly in the Index() method.
I have commented out the code that was created automatically by the scaffolding mechanism.
// GET: Footballers
//public async Task<ActionResult> Index()
//{
// return View(await db.Footballers.ToListAsync());
//}
and replaced it with the code below.
// GET: Footballers
public async Task<ActionResult> Index()
{
string commandText = "DisplayFootballers";
return View(await db.Database.SqlQuery<Footballer>(commandText).ToListAsync());
}
As you can see, I just create a string variable with the name of the stored procedure. Then I call the SqlQuery (https://msdn.microsoft.com/en-us/library/system.data.entity.database.sqlquery(v=vs.113).aspx ) method passing the name of the stored procedure. The SqlQuery method will return elements of the given type (Footballer) after the execution of the stored procedure.
Build and run your application. Now when you add data to the application and click Save, this data is displayed on the screen through the stored procedure.
Have a look at the Output window below
As you can see the data (the list of the Footballers) is displayed now through the stored procedure. More posts will follow regarding ASP.Net MVC 6 (ASP.Net MVC Core 1.0) applications and Entity Framework 7 (EF Core 1.0).
Hope it helps!!!
In this post I am going to discuss Dependency Injection pattern, explain why we need it and how to use it in our modern web applications based on ASP.Net MVC paradigm.
DI (Dependency Injection) is not a difficult concept to grasp but a lot of developers do not understand it or even worse they use it wrongly.
We use DI to decouple layers in our application. I assume that you use Layered Architecture principles and patterns when architecting your application.
Usually we want to decouple the Domain's Layer dependency on the Persistence Layer. Have a look at the picture below.
It is obvious why we would like something like that. At the moment I utilise Entity Framework and SQL Server in my application but later on a new specification comes in which dictates that we should switch NHibernate and Oracle.
So at the end of the day as Solution Architects we need to decouple those two layers.
To utilise DI, or better if we intend to use DI in our project we need first to add a layer of abstraction between those two layers. This level of abstraction comes in the form of an Interface (a C# Interface if you want).
The Domain layer can talk to any persistence layer as long as this Persistence layer implements the methods defined in the contract (interface).
By doing that it is quite obvious that both the Domain Layer and the Persistence Layer both rely / depend on an abstraction.
No longer the Domain Layer needs to create new instances of concrete classes in the Persistence Layer, the coupling between those two layers does not exist and the dependency is broken.
The Persistence Layer implements the abstraction, in our case the C# interface.
So at runtime, some third party, can inject an instance of the Persistence Layer in the Domain Layer dynamically.
Have a look at the picture below.
When talking, understanding the DI pattern we need to understand the Dependency Inversion Principle.
This principle merely states that High level modules (classes) should not depend on Low level modules (classes), both should depend on abstractions.
Abstractions should not depend on details, details should depend upon abstractions.
Another principle we should bear in mind when talking about DI is the Inversion of Control Principle.
IoC states that a third party injector , or a third party container controls the flow of the program and determines which objects should be created and where they should be passed.
In DI there is a consumer that depends on the other classes for some service. Then there is a declaration of that consumer's (consumer = a certain class) need in the form of a contract/interface.
Finally there is a third-party injector,provider,container that supplies instances of classes that adhere to the contract to the dependent consumer classes.
This third-party injector software is best known as a DI Container. There are many DI Containers, most of them open- source, that developers use nowadays.
Unity, Ninject, Autofac are the most widely used DI containers.
These DI containers know which concrete classes to pass to the dependent consumer classes. We need to configure this information in a configuration file e.g XML file or in a C# code class.
Basically we create mappings in this C# code class, whenever you see this interface pass me an instance of this particular class.
In our consumer classes we need to enable/allow for an injected implementation.
The way I do it, or most people I know do it, is by using Constructor Injection.
Have a look at the code below.
public class OrderService
{
private readonly IOrderRepository _orderRepository;
public OrderService(IOrderRepository orderRepository)
{
_orderRepository = orderRepository;
}
//more code goes here
}
In our class, in our constructor it takes an instance of the IOrderRepository and in the body of that constructor it saves an instance to a local pivate read only field.
I am going to create an ASP.Net Core application to demonstrate DI.
I have installed Visual Studio 2015 Enterprise in my machine and have downloaded .Net Core. You can download Visual Studio 2015 Community edition.
In ASP.Net Core DI is a first class citizen and there is native support for it.
1) Launch Visual Studio 2015 and start a new ΑSP.Net Core Web Application.Give it an appropriate name.
2) Add a folder Models in the project. Add a class that describes a department
public class Department
{
public string Name { get; set; }
public decimal Budget { get; set; }
public DateTime StartDate { get; set; }
}
3) Create a folder Interfaces in the project. Add this code below in it
public interface IDepartmentRepository
{
List<Department> GetAll();
}
This is a simple method that we need to implement in the concrete class.
4) Add a folder Repositories.Add the following code in it. We just implement the GetAll method which just populates with data the Deparment in memory object.
public class DepartmentRepository:IDepartmentRepository
{
public List<Department> GetAll()
{
return new List<Department>()
{
new Department()
{
Name = "Physics",
Budget = 250000,
StartDate=DateTime.Parse("12/12/1999")
},
new Department()
{
Name = "Maths",
Budget = 550000,
StartDate=DateTime.Parse("11/11/1981")
},
new Department()
{
Name = "Software",
Budget = 950000,
StartDate=DateTime.Parse("08/11/1987")
}
};
}
}
5) In the Controllers folder in the HomeController.cs comment out everything. The new code follows.
public class HomeController : Controller
{
private readonly IDepartmentRepository _depRepo;
public HomeController(DepartmentRepository depRepo)
{
_depRepo = depRepo;
}
public IActionResult Index()
{
return View(_depRepo.GetAll());
}
}
We have a pivate readonly instance of our interface IDepartmentRepository and the constructor takes an instance DepartmentRepository. This is constructor injection.
6) Τhe Startup.cs configures the environment which our app will run. The Startup.cs file also places services into ASP.Net Core's Services layer,which is what enables dependency injecction.
I add the following line in bold.
public void ConfigureServices(IServiceCollection services)
{
// Add framework services.
services.AddApplicationInsightsTelemetry(Configuration);
services.AddMvc();
services.AddTransient<DepartmentRepository>();
}
7) Build and run your application. You will see the list with the departments.
In DI we have the concept of "lifetimes". A lifetime specifies when a DI-injected object gets created or recreated. There are three options:
Transient: Created each time they are requested.
Scoped: Created once per request.
Singleton: Created the first time they are requested. Each subsequent request uses the instance that was created the first time.
To recap when talking about the DI pattern we need to understand the Dependency Inversion Principle and the Inversion of Control Principle.
DI is a very useful pattern when we need to decouple Layer's functionality, thus making our application more maintainable.
Hope it helps!!!
In this post I am going to make the case why returning IQueryable from a Repository Pattern is an anti-pattern and why you should not do it.
I assume that you have some experience on building applications using the Layered Architecture and are familiar with various design patterns like Unit of Work and Repository pattern.
You can have a look at this post if you want to find out more about the Repository Pattern.
In this post I am talking the Repository pattern and Entity Framework and explain why we still need to use the Repository Pattern even if we use EF in our persistence layer.
One thing we should keep in mind is that at some point our methods in the Persistence layer should return something that can be consumed by the client classes in upper layers.
Clean code architecture dictates that we should program against clean interfaces and the various layers that exist in our application should communicate with each other trough interfaces so there is separation of concerns and the one layer is decoupled from the other. In other words we need to have low coupling between the layers.
The layers in an enterprise ASP.Net Core (ASP.Net 5 MVC 6.0) application should be the following:
1) User Interface layer (View models, View Components, Views, partial Views)
2) The domain layer which models the problem at hand, a series of classes like Customer, Order, e.t.c.
3) The Application or Services Layer which basically orchestrates all the classes in the lower levels (Domain layer, Persistence layer) and passes the information to the UI layer.
4) The Persistence layer where we have our ORM entities and the repository pattern which is responsible to simplify data access and enforce separation of concerns.
In the simplest words the repository pattern provides a clear separation between the application and the database access.
There will be instances of the Persistence layer classes in the Application/Services layer and there will be instances of the Application/Services in the UI layer, usually in the constructor of the controller.
Usually these classes are injected through the Dependency Injection pattern in the upper layers. This is of course something I will write in another post.
So now let's look into why we should not return IQueryable from a Repository class method.
By visiting this link from MSDN we can have an excellent explanation of what IQueryable<T> is.
public interface IQueryable<out T> : IEnumerable<T>, IEnumerable,
IQueryable
First of all it is an interface that inherits from IEnumerble<T> and IEnumerable.
Basically the IQueryable<T> interface is to be implemented by query providers. The query provider is a low level mechanism.
LINQ to Entities queries are translated into expression trees and then in raw SQL queries by the query provider. So you have a base query provider and on top you have things like the LINQ query provider.
So that is the purpose of the query provider. A query provider is not to be used to build a query object to execute and retrieve results from Linq to Entities.
I a nutshell IQueryable<T> allow us to operate and build queries that can be sent to a query provider, e.g LINQ to Entities query provider or Linq2SQL provider which in their turn will translate this to SQL and send it to the connected database.
IEnumerable<T> allow us to execute queries in memory against the objects, models, entities that the repository methods return.
Let me demonstrate with some code because a lot of people are still confused.
I will begin with a simple demonstration of IQueryable<T>.
Repository Layer
public IQueryable<Doctor> GetDoctors()
{
return context.Doctors;
}
Services Layer
//we assume the repository class(myrepo) is injected in the Services Layer
public void GetDoctorswithMostAppointments()
{
var getDoctorswith50Appointments = myrepo.GetDoctors().Where(d=>d.Appointments.Count >50);
var onlyAfewDoctors = getDoctorswith50Appointments.Skip(5).Take(10);
var returnedDoctors = onlyAfewDoctors.ToList();
}
the database is called in the line below
var returnedDoctors = onlyAfewDoctors.ToList();
Only the data that we want comes back from the database.
Now let me rewrite the code above using the IEnumerable<T>.
Repository Layer
public IEnumerable<Doctor> GetDoctors()
{
return context.Doctors.Include("Appointments");
}
The database call took place in the line above. All doctors and their appointments have been already loaded to the memory.
Services Layer
//we assume the repository class(myrepo) is injected in the Services Layer
public void GetDoctorswithMostAppointments()
{
var getDoctorswith50Appointments = myrepo.GetDoctors().Where(d=>d.Appointments.Count >50);
var onlyAfewDoctors = getDoctorswith50Appointments.Skip(5).Take(10);
var returnedDoctors = onlyAfewDoctors.ToList();
}
All the operations above are done in the memory.
Let's say that you write something like that in your Services layer which sits on the top of the domain layer.
var BestSellerBooks = context.Books.Where(b=>b.isPublished && b.AuthorLastName="Hemingway");
Let's examine what we get back here. Without a shadow of a doubt we get back an IQueryable<Book> which is an entity from the entity framework, meaning we use the Entity Framework inside our Applications or Services layer.
That is a leaky abstraction and a violation of separation of concerns principle. We have the Services layer tightly coupled to the underlying ORM which is EF in our case.
This is clearly wrong since the Services layer is no more technology agnostic.
What we should pass to the other layers from the persistence and repository classes and methods is a business object, a model. By returning IQueryable you do not get back a business entity but you get back a query builder which specifies how to get the data back. Repository should be about what to get and not how to get it.
This is the job of the repository, persistence layer of how to get it. What we want back in order to pass it to the layers above is the model, the data, the business entity.
A very important stage of the development process is testing. In large ASP.Net applications we use Unit testing to test the domain and persistence logic of the application.
We cannot test data persistence or domain logic of repository classes and methods that return IQueryable.
In this post we argued why we should return IEnumerable<T> from our repository classes and methods and not IQueryable<T> because if we do we couple tightly the various layers of our application, break separation of concerns priniciple and make the application impossible to test.
Hope its helps!!!
In this post I will try to explain why the Repository Pattern is not redundant when we use an ORM like Entity Framework in our application.
There are many blog posts and many questions/answers in places like StackOverflow where developers argue about that issue.
I will not be writing code in this article but I will try to explain why Entiti Framework does not implement the Repository Pattern and what is the difference between them are.
Before reading this post by all means do have a look at this post of mine where I explain the Repository Pattern in detail.
Let me say a few words first about Entity Framework first. Entity Framework is an ORM.
The .Net framework provides support for Object Relational Mappingthrough EF.
So EF is a an ORM tool and it is now the main data access technology that microsoft works on.
I use it quite extensively in my projects.
Through EF we have many things out of the box provided for us. We have the automatic generation of SQL code.
It maps relational data to strongly typed objects.All the changes made to the objects in the memory are persisted in a transactional way back to the data store.
In a nutshell it bridges the relational with the object oriented world. A lot of developers like it because they do not want to learn SQL or deal with low level database specification details. As with all things there is a learning curve and a lot of developers use EF code in many layers in their application making the code non-testable an non-maintenable.
The Repository Pattern abstracts and encapsulates anything that has to do with persiestence.
We can use Linq to Entities or stored procedures to store data in a data store that can be a file, a document db, a cloud storage or a relational database. The rest of the application does not know that, as the Repository encapsulates everything storage related.
At this point I want to underline the fact that many developers confuse the domain objects with persistence objects like an EF entity.
The domain model models the behavior of the application. The persistence model models how and what data will be stored, so in essence it models storage structure.
Having all these in mind let's move to the argument that many developers put forward "that there is no need to use the Repository Pattern when we are using Entity Framework which implements the Repository Pattern, hence the Repository Pattern is redundant".
Entity Framework main objects are DbSet and DbContext. DbSet looks like a repository.
DbSets represent objects in memory. There are methods for adding, removing, getting objects.
The DbContext has methods for storing the data. So one could argue that DbContext implements UnitOfWork pattern.
One main benefits of the repository pattern is that there is no duplicate query logic and a separation of concerns.
All of your queries for data when using Entity Framework are written against DbSet.
For example, when we are querying against a Customer or a Doctor entity, those entities are exposed on our DbContext as a DbSet.
DbSet implements the IQueryable interface.
public class DbSet<TEntity> : DbQuery<TEntity>, IDbSet<TEntity>,
IQueryable<TEntity>, IEnumerable<TEntity>, IQueryable, IEnumerable
where TEntity : class
DbSet with the LINQ extension methods implemented on it, return IQueryable.
That means we will have queries like the one below(if we are implementing a book e-shop)
context.Books
.Where(b=>b.BookID==bookid)
.Select(b=>b.name)
.Include(a=>a.Author)
.ToList();
in all layers of the application. We will have queries like the one above in our controller or service layer.
That is a leaky abstraction. UI or service layer should not know about how to implement data store persistence logic.
So we violate both the seperation of concerns and the duplication of query logic.
Another benefit of the repository pattern is that it decouples your application or domain entities from persistence frameworks.
In our applications when using EF we use DbContext directly in our application, our application is tightly coupled to Entity Framework. So our application is tightly coupled with the persistence framework. In most cases we do not want that. Repository pattern definetely help us to achieve persistence abstraction.
Some people say that I wil use EF and I am not going to change to nHibernate or any other ORM. Well maybe EF in 4-5 years from now has a different implementation of DbContext and DbSet. What will happen if you want to update the code. Many changes should take place inside the code.
Bottom line is that we need to hide DbContext behind a repository and only use the repository interfaces in your application. The repository is a softwar design pattern that helps us abstract all storage related details from the rest of the application. Entity Framework abstracts access to an relational database e.g SQL Server. Basically EF is an implementation detail of the Repository.
Hope it helps!!!
In this post I will try to define the Repository Pattern, explain what it is and how we must use it in my ow words and according to my experience as a software engineer.
I will not be writing much code in this post but I will highlight the reasons why we need the Repository Pattern and what it helps us achieve.
Let me start with the official definition. Martin Fowle in his book Patterns of Enterprise Application Architecture (a must for every software engineer) gives the definition of the Repository Pattern: "Mediates between the domain and data mapping layers using a collection-like interface for accessing domain objects.". You can read more link in his post.
In simpler words, the Repository Pattern creates an abstraction layer between the data access layer and the business logic layer of an application.
Repository design pattern is a way to simplify data access and enforce separation of concerns.
In every medium to large business application the Repository Pattern is necessary to bring seperation of concerns in our application as long as it is used correctly.
In the simplest words the repository pattern provides a clear separation between the application and the database access.
I think that most developers inherit a database before they start developing the application. They do not think in terms of the Domain Driven Design. They let the database shape the application code.They should think in a different way. The application domain, the business objects should be the heart of the application and shape the schema of the database. So we should not start building the application thinking about persistence for example.
Let me highlight the benefits of the Repository Pattern.
It helps us minimizes duplicate query logic.
I am pretty sure you have seen queries (Linq to Entities queries) inside a Controller. Well, never do that. Your code needs refactoring in that case.If not, you have a non testable application and an application that does not honor the seperation of concerns principle. It does not also honor the DRY (Do Not Repeat) yourself principle. It also vioates the theory of Clean Code Architecture.
Think like this. What is the MVC pattern? It is a UI pattern. A UI pattern is only, or should only be responsible for displaying data, data that is fetched to it by another layer. Controllers should not be responsible for querying.
Querying is data access concern, it belongs to another class, to another layer, the repository layer or the data access layer.
By moving (refactoring your code) a method to a class called repository we can have many controlers use it, hence getting rid of duplicate query logic.
Let me give you an example. Let's say we need to design an asp.net (MVC 5.0) application, an e-shop, a book e-shop. Let's just imagine that we need to display in various parts of the application the 50 top available published books with most sales. We could have a query like this inside the controller
var BestSellerBooks = context.Books
.Where(b=>b.isPublished && b.isAvailable)
.OrderByDescending(b=>b.Sales)
.Take(50);
But what if we needed this data in many places inside the application? We should call the code above inside many controllers. That would be wrong and result in code duplication and in the end, in non maintenable code.
Instead we could move the code in the repository class and the code inside our controller(s) could be
var BestSellerBooks = BookRepositor.GetBestSellerBooks(bookcategory, numofBooks)
The next great benefit of the repository pattern is the seperation of concerns. Well in general separation of concerns is a great thing. Most problems in applications is that we have database access code in all the areas of the application. Why does this happen?
A a controller receives a request and generates a response. The controller orchestrates everything. Builds a model and passes it to a view. That is all.
It should not access the database directly. It talks to the repository layer and says "Well give me the top selling authors or books or whatever".
Then the repository knows how to do that. It could use ADO.Net or EF or stored procedures to fetch the data. The domain layer or UI layer does not care about that and should not know about the implementaion details. Every layer has a concern so when testing or debugging our code we know which layer to look into.
Another benefit of the repository pattern is that it decouples your application from persistence frameworks.
Let's say that we use Entity Framework as the persistence framework. Then we decide for some reason to drop EF and use nHibernate. If we use the repository pattern correctly we could swap to nHibernate with minimal impact.
Even if I switch to nHibernate I will still call the
var BestSellerBooks = BookRepositor.GetBestSellerBooks(bookcategory, numofBooks)
from the UI or Service layer. I do not care about the implementation details of how data is fetched.
Let's say that I have an ASP.Net MVC Core 1.0 application or an ASP.Net MVC 5.0 application that allows people to look for local doctors and book appointments on line.
The repository pattern in practice for the main domain/business object (Doctor)of this application should be something like this
public interface IDoctorRepository
{
Add(Doctor item)
Remove(Doctor item)
Doctor GetByID (int id)
IEnumerable<Doctor> GetAll()
IEnumerable<Doctor> Find(SomeCriteria, criteria)
}
This is the abstraction I need (the interface) to know only about business objects and not database objects.Τhese business objects are in memory collection objects.
There are no methods like Update or Save. Save or persisting an object to the underlying data store is the business of another great pattern, the UnitOfWork Pattern.
Well, a lot of people, business people or junior developers still do not get the repository pattern. I will use a real life example to explain the repository pattern.
Let's think of employees who work for a known german car vendor/brand e.g Mercedes inside a car factory.
They work in a production line and they need a set of tools to finish decoration of the inside of a car.
They sit in the production line and press buttons. These buttons give instructions to a technical automated system that can fetch tools from a storage of tools within the factory.
After pressing a particular button that automated system fetches a tool to the employee who requested it. The employee finishes the work with that particular tool, presses a button, the automated system moves to a position where the employee places the specific tool, and the automated system stores it in a place where it knows how to fetch it from.
Does the employee know the inner workings of the automated system? No.
Does he need to understand how that automated system knows how to store and get tools to him? He does not. The employee is the "Business Domain Layer" of our application.
How does he communicate with the automated system? Through some abstraction, interface (buttons on a panel). The automated system is my repository layer/pattern, it abstracts from me (the employee-business layer) the inner workings of how tools are fetched.Hope it makes sense now.
To recap, the Repository Pattern abstracts the persistence layer, it is a collection of business objects in memory.
These business objects are stored and retrieved. One developer could use the EF to do that , another could use stored procedures to persist and retrieve those business objects.
The domain layer or the rest of the application does not know how these objects are persisted and the quite rightly so.
This is the job of the repository pattern and data access layer. The rest of the application communicates with repository layer/data access layer through well defined abstractions/interfaces.
Hope it helps!!!
In this post I am going to create a hands on example, a sample ASP.Net MVC application where I will demonstrate the concept of ViewModels.
As we know MVC is a UI design pattern.
MVC is based on an architecture model that embraces the so called "seperation of concern pattern".
There are three main building blocks in the MVC pattern.The Controller orchestrates everything.When we have an HTTP request coming in, that request is routed to the Controller. The View talks to the Model. The Model has the data that the View needs to display.The View does not have much logic in them at all.
We design our business domain (after extracting our use cases from the specification document) by creating the appropriate classes in the Models folder.
The Controller handles all user interaction logic. The View contains all the user interface that the user will interact.
So if MVC is a separation of concerns pattern, why we need another concept like ViewModels.
In our ASP.Net MVC applications we need to pass data, or display data from more than one models.This is common task and we can use ViewModels to achieve that.
If you want find out more ways to pass multiple models to a view you can look at this post and this post.
With the ViewModels we can get data from multiple data models, get those entities and shape them as we need. Then the View will need to consume that single object which is our ViewModel.
Another example where we can use ViewModels is when we have presentation logic issues. In our model e.g Customer we can have a DateOfBirth property with the datatype DateTime.
Let's say in our View we need to display only the Age of the person. That would need some calculation, some logic. That logic needs to go to View. At this point we broke the separation of concerns principle. ViewModels can help us implement that logic, which is a presentation logic and does not belong to any other building blocks of MVC, Controller,View or Model.
As stated earlier I will create a sample ASP.Net MVC 5.0 application. I will use a database that I have already created and the EF database first paradigm to access the database.
I am going to need a database for this example. The database is called Projects. There are two tables, Project and Employee.
There are employees that belong to projects, one project has many employees. This is 1->N relationship between projects and employees.
You can download the script that will create the database and populate the tables here.
Make sure you run the script in an instance of SQL Server you have.
I will use Visual Studio 2015 Enterprise edition to create this sample application. You can use Visual Studio Express edition or Community edition.
1) I need to display data in my MVC application from both the Employee and Project tables.
Open your Visual Studio and create empty ASP.NET MVC application.
Click on File -> New Project -> Web -> ASP.NET web application.
From next window Select template Empty and from Add folders and core reference choose MVC.
Give it an appropriate name e.g ViewModels. Now you have in your Solution Explorer window the typical structure of an ASP.Net MVC application.
2) We need to install Entity Framework throgh Nuget Package Manager. Then we need to add an ADO.NET Entity Data Model.
Right click on Model folder and select Add inside that Select ADO.NET Entity Data Model. Follow the steps of the wizzard, connect to the database and include the two tables in your model.So the model will be generated from the database. You will see the two classes Project and Employee in your Model folder.
3) I am going to create a ViewModels folder. Inside that folder I will create a new class ProjectViewModel.
We will add properties from both out Models to ProjectViewModel class. I will take Name,Surname,Age,Role from Employee model and ProjectName from Project model.
The code for the ProjectViewModel class follows.
Snippet
public class ProjectViewModel
{
public string Name { get; set; }
public string Surname { get; set; }
public int Age { get; set; }
public string Role { get; set; }
public string ProjectName { get; set; }
}
4) Now I am going to create a controller. I will create an empty controller.
To add a controller right click on Controllers Folder, select Add and then select Controller. After selecting controller a new Add Controller Dialog will popup.Name the controller ProjectEmployeesController, select Empty MVC Controller and click on Add button.
The code for the ProjectEmployeesController code follows
public class ProjectEmployeesController : Controller
{
// GET: ProjectEmployees
public ActionResult Index()
{
ProjectsEntities ctx = new ProjectsEntities();
List<ProjectViewModel> ProjectEmployeeslist = new List<ProjectViewModel>();
var datalist = (from proj in ctx.Projects
join emp in ctx.Employees on proj.ProjectId equals emp.ProjectId
select new {proj.ProjectName , emp.Name, emp.Surname, emp.Age, emp.Role}).ToList();
foreach (var item in datalist)
{
ProjectViewModel pvm = new ProjectViewModel();
pvm.ProjectName = item.ProjectName;
pvm.Name = item.Name;
pvm.Surname = item.Surname;
pvm.Age = item.Age;
pvm.Role = item.Role;
ProjectEmployeeslist.Add(pvm);
}
return View(ProjectEmployeeslist);
}
}
I create an instance of the DbContext class.
I then create a list object of type ProjectViewModel to hold the data from both the Project and Employee entities.
Then I query my model to get the data in the datalist. I iterate through datalist and store the values from the entities to the properties of the ProjectViewModel object pvm.
Finally I add the pvm object with all the values to the ProjectEmployeeslist object which can hold objects of type ProjectViewModel and then pass the model to the view.
5) Now we need to create the View to display the data from the model.Right click inside Index ActionResult Method and Select "Add View" to create the view template for our Index method. We are going to create a strongly typed view by selecting the Model class ProjectViewModel and we want to create a List. The scaffold mechanism will create our view that you can see below. As you can see below in the first line of the view I pass the ProjectViewModel ViewModel.
@model IEnumerable<ViewModels.ViewModels.ProjectViewModel>
@{
ViewBag.Title = "Index";
}
<h2>Index</h2>
<p>
@Html.ActionLink("Create New", "Create")
</p>
<table class="table">
<tr>
<th>
@Html.DisplayNameFor(model => model.Name)
</th>
<th>
@Html.DisplayNameFor(model => model.Surname)
</th>
<th>
@Html.DisplayNameFor(model => model.Age)
</th>
<th>
@Html.DisplayNameFor(model => model.Role)
</th>
<th>
@Html.DisplayNameFor(model => model.ProjectName)
</th>
<th></th>
</tr>
@foreach (var item in Model) {
<tr>
<td>
@Html.DisplayFor(modelItem => item.Name)
</td>
<td>
@Html.DisplayFor(modelItem => item.Surname)
</td>
<td>
@Html.DisplayFor(modelItem => item.Age)
</td>
<td>
@Html.DisplayFor(modelItem => item.Role)
</td>
<td>
@Html.DisplayFor(modelItem => item.ProjectName)
</td>
<td>
@Html.ActionLink("Edit", "Edit", new { /* id=item.PrimaryKey */ }) |
@Html.ActionLink("Details", "Details", new { /* id=item.PrimaryKey */ }) |
@Html.ActionLink("Delete", "Delete", new { /* id=item.PrimaryKey */ })
</td>
</tr>
}
</table>
6) Build and run your application. You will will see data from both entities appear in the View.
To recap ViewModels gives us the flexibility to shape data according to our business needs. We can add inside our ViewModels data that come from different entities or write presentation logic that does not belong to the View.
Hope it helps!!!
In this post I will use a simple ASP.Net 5.0 MVC application to implement a jQuery Ajax call to an ASP.Net MVC controller.
JQuery is not only about DOM manipulation and handling events on the client. It is very powerful when we want to manipulate HTML elements but also can talk with server side and instantiate
In this example we will use the JQuery Ajax function to talk to an ASP.Net MVC controller and result JSONResult to the view.
You can have a look in this post of mine where I talk about JQuery Ajax functions.
In this post I provide a more extended example on how to use JQuery Ajax functions.
I am going to create a sample application where I will have a simple dropdown which I will populate with data.
Every time there is a change in the DropDown control, there will be a JQuery Ajax call to the controller.
There are employees that belong to projects, one project has many employees. This is 1->N relationship between projects and employees.
I am going to need a database for this example. The database is called Projects. There are two tables, Project and Employee.
The dropdown control will have the project values and when the user selects different values (projects) the respective employees of the current project are loaded into a table.
I will use Visual Studio 2015 Ultimate edition to create this sample application. You can use Visual Studio Express edition or Community edition.
You can download the script here.
1) Open your Visual Studio and create empty ASP.NET MVC application.
Click on File -> New Project -> Web -> ASP.NET web application.
From next window Select template Empty and from Add folders and core reference choose MVC.
Give it an appropriate name. Now you have in your Solution Explorer window the typical structure of an ASP.Net MVC application.
2) Now we need to create a model to represent the Employee entity
Right click on Models folder and select Add -> Class name it as Product -> click Ok.
Add below properties for Employee model.
public class Employee
{
public int EmployeeID { get; set; }
public string Name { get; set; }
public string Surname { get; set; }
public int Age { get; set; }
public string Role { get; set; }
}
3) Now we need to add a controller in the Controllers folder. Right click on Controllers folder and select Add -> Controller.
Choose an Empty Controller. and click Add.
Name it as HomeController.
I will add a ShowProjects method in the HomeController.
public ActionResult ShowProjects()
{
List<SelectListItem> items = new List<SelectListItem>();
items.Add(new SelectListItem
{
Text = "Select Category",
Value = "0",
Selected = true
});
items.Add(new SelectListItem { Text = "E-shop", Value = "1" });
items.Add(new SelectListItem { Text = "Web Portal", Value = "2" });
items.Add(new SelectListItem { Text = "Blog", Value = "3" });
ViewBag.CategoryType = items;
return View();
}
As you can see I do not get the projects from the database. I only have 3 projects so I load them into a list and then add those items in the Category property of the ViewBag.
4) In the next step we need to add an MVC View to our project.
In this step you will add a MVC View to display Project and Employee details.
Open HomeController from Controllers folder -> Go to ShowProjects action method -> right click and select Add View.
It adds a View under Views -> Home folder with name ShowProjects.cshmtl.
We nee to add a DropDownList to display Projects and empty table to show employees depending on selected project.
The code follows
@{
ViewBag.Title = "ShowProjects";
}
<h2>Show Projects and their respective Employees</h2>
@using (Html.BeginForm("CategoryChosen", "Home", FormMethod.Get))
{
<table cellspacing="2" cellpadding="2">
<tr>
<td>
Category Type :
</td>
<td>
@Html.DropDownList("CategoryType")
</td>
</tr>
</table>
<br />
<div>
<table id="tblEmployees" class="tblEmployees">
<thead>
<tr>
<th align="left" class="employeeth">Employee ID</th>
<th align="left" class="employeeth">Name</th>
<th align="left" class="employeeth">Surname</th>
<th align="left" class="employeeth">Age</th>
<th align="left" class="employeeth">Role</th>
</tr>
</thead>
<tbody></tbody>
</table>
</div>
}
5) Add a styles.css file to your project.
.tblEmployees {
font-family: verdana,arial,sans-serif;
font-size: 11px;
color: #fff;
border-width: 1px;
border-color: #666666;
border-collapse: collapse;
}
.employeeth {
border-width: 1px;
padding: 8px;
border-style: solid;
border-color: #666666;
background-color: #1aa33e;
}
.employeetd {
border-width: 1px;
padding: 8px;
border-style: solid;
border-color: #666666;
background-color: #ffffff;
color:#000;
}
In the _Layout.cshtml add a reference to the styles.css
<link href="~/Content/styles.css" rel="stylesheet" />
6) Now we need to add another action on the HomeController controller.
Open HomeController controller and add an action method which accepts Project ID as input parameter, create list of employess whose ProjectID is equal to input parameter and return it as JSON result.
The action method and code follows.
public JsonResult GetEmployees(string id)
{
List<Employee> employees = new List<Employee>();
string query = string.Format("SELECT [EmployeeID], [Name], [Surname],[Age],[Role] " +
" FROM [Projects].[dbo].[Employee] WHERE ProjectID = {0}", id);
using (SqlConnection con = new SqlConnection("Data Source=FOFO-PC;Initial Catalog=Projects;Integrated Security=True"))
{
using (SqlCommand cmd = new SqlCommand(query, con))
{
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
employees.Add(
new Employee
{
EmployeeID = reader.GetInt32(0),
Name = reader.GetString(1),
Surname = reader.GetString(2),
Age = reader.GetInt32(3),
Role= reader.GetValue(4).ToString()
}
);
}
}
}
return Json(employees, JsonRequestBehavior.AllowGet);
}
The code is very simple. I do not use EF in this sample application. I am using simple ADO.Net to retrieve data from the database.
I am creating a list of employees list.
I create a variable that holds the SQL query.
I am using the SQLConnection object to open a connection to the database and then execute the query with the SQLCommand object.
I use the SQLDataReader object to create a reader object and populate it with values.
Finally I return the data in JSON format.
7)
Νow we need to add code for CategoryType DropDownList change event. This will create AJAX call to MVC controller GetEmployees action method.
Below is the code which fires on change of DropDownList, create AJAX call with required details, make a call to GetEmployees controller action method, accepts JSON result and display as Table object.
There must be a reference to the JQuery library.
In the _Layout.cshtml add a reference to JQuery library.
<script src="~/Scripts/jquery-1.10.2.min.js"></script>
<script type="text/javascript">
$(document).ready(function () {
$("#CategoryType").change(function () {
$("#tblEmployees tbody tr").remove();
$.ajax({
type: 'POST',
url: '@Url.Action("GetEmployees")',
dataType: 'json',
data: { id: $("#CategoryType").val() },
success: function (data) {
var items = '';
$.each(data, function (i, item) {
var rows = "<tr>"
+ "<td class='employeetd'>" + item.EmployeeID + "</td>"
+ "<td class='employeetd'>" + item.Name + "</td>"
+ "<td class='employeetd'>" + item.Surname + "</td>"
+ "<td class='employeetd'>" + item.Age + "</td>"
+ "<td class='employeetd'>" + item.Role + "</td>"
+ "</tr>";
$('#tblEmployees tbody').append(rows);
});
},
error: function (ex) {
var r = jQuery.parseJSON(response.responseText);
alert("Message: " + r.Message);
alert("StackTrace: " + r.StackTrace);
alert("ExceptionType: " + r.ExceptionType);
}
});
return false;
})
});
</script>
There is a post action to the GetEmployees action controller method, passing the CategoryTyepe value.
On success it displays JSON string send by action method, manipulate it and display product details on View.
On failure we get information about the error (Message, StackTrace and ExceptionType)
Go to http://localhost:21131/Home/ShowProjects (you will have your own port number), select any value from CategoryType DropDownList. This will make an AJAX call and will the fill Employees table. Have a look at the picture below.
You can also see the JSON results returned by the controller after the Ajax call using the developer tools of any browser. I am using Chrome.
Have a look at the picture below.
Hope it helps!!!
When developing/architecting our custom ASP.Net MVC applications we do use the Repository Pattern/Generic Repository Pattern.
I want to share my experience and thoughts with the community of developers out there that use Generic Repositories in their code.
At first a definition is appropriate. Martin Fowler gives the definition of the Repository Pattern: "Mediates between the domain and data mapping layers using a collection-like interface for accessing domain objects.". You can read more link in his post.
In simpler words, the Repository Pattern creates an abstraction layer between the data access layer and the business logic layer of an application.
Repository design pattern is a way to simplify data access and enforce separation of concerns.
When I began learning about repositories and implementing them in my own software design, it had a huge impact on my application architecture.
It helped me to automate testing practices, forced me to consider separation of concerns with each method and behavior I added to my code. In a nutshell the pattern makes one to write good, clean, testable, low coupling, non-duplicate code.
There are many ways to implement the repository pattern and I will show some bad ways to use and finally I will talk about its right usage.
This is the first approach to implement the Generic Repository Pattern. I am just creating a new empty project and I am adding a class library project in it.
Let's say that I will implement the Generic Repository Pattern for two domain objects (Product and Customer) that need data access operations because these in memory domain objects at one point through Entity Framework will have to materialize into rows of data in the data store.
1) This is the code below for the Customer Repository(ICustomerRepository)
public interface ICustomerRepository {
IQueryable<Customer> GetAll();
Customer GetByID(int CustomerId);
IQueryable<Customer> FindBy(Expression<Func<Customer, bool>> predicate);
void Add(Customer entity);
void Remove(Customer entity);
void Edit(Customer entity);
void Save();
}
2) This is the code below for the Product Repository (IProductRepository)
public interface IProductRepository
{
IQueryable<Product> GetAll();
Product GetByID(int ProductId);
IQueryable<Product> FindBy(Expression<Func<Product, bool>> predicate);
void Add(Product entity);
void Remove(Product entity);
void Edit(Product entity);
void Save();
}
As you can understand I am already repeating almost identical code because the two repositories for the two domain objects have identical data access methods.
3) I am going to implement now the Customer Repository.
This is the code below.
public class CustomerRepository:ICustomerRepository
{
private readonly CustomerEntities context = new CustomerEntities();
public IQueryable<Customer> GetAll()
{
IQueryable<Customer> query = context.Customers;
return query;
}
public Customer GetByID(int CustomerId)
{
var query = this.GetAll().FirstOrDefault(x => x.CustomerId == CustomerId);
return query;
}
public void Add(Customer entity)
{
context.Customers.Add(entity);
}
public void Remove(Customer entity)
{
context.Customers.Remove(entity);
}
public void Edit(Customer entity)
{
context.Entry<Customer>(entity).State = System.Data.EntityState.Modified;
}
public void Save()
{
context.SaveChanges();
}
4) The next step would be to implement the Product Repository for the Product domain object. As you can understand the code would be almost identical. I have already violated the DRY (Do Not Repeat Yourself) principle.
The code above implements the Repository pattern but needs reengineering because we had to do the above implementation for all our classes in our domain.
5) The first step towards re-engineering the code is to create a generic interface. This is the new generic interface.
public interface IGenericRepository<T> :IDisposable where T : class
{
IQueryable<T> GetAll();
IQueryable<T> FindBy(Expression<Func<T, bool>> predicate);
void Add(T entity);
void Remove(T entity);
void Edit(T entity);
void Save();
}
6) Νow we need to re-engineer our code for the two interfaces IProductRepository and ICustomerRepository
public interface IProductRepository : IGenericRepository<Product>
{
Product GetByID(int ProductId);
}
public interface ICustomerRepository:IGenericRepository<Customer>
{
Customer GetByID(int CustomerId);
}
I only need to implement GetByID method and the other methods come with IGenericRepositoy<T> interface.
If I move to the next step and implement the concrete classes (CustomerRepository,ProductRepository), I need to create all the methods in them. So we still duplicate and repeat the code.
7) We must re-engineer the code and come up with a better solution. I will create an abstract class that will inherit from my generic interface.The code follows.
public abstract class GenericRepository<C,DBC> :
IGenericRepository<T> where T : class where DBC : DbContext, new()
{
private DBC _newentities = new DBC();
protected DBC Context
{
get { return _newentities; }
set { _newentities = value; }
}
public virtual IQueryable<T> GetAll()
{
IQueryable<T> query = _newentities.Set<T>();
return query;
}
public IQueryable<T> FindBy(System.Linq.Expressions.Expression<Func<T, bool>> predicate)
{
IQueryable<T> query = _newentities.Set<T>().Where(predicate);
return query;
}
public virtual void Add(T entity)
{
_newentities.Set<T>().Add(entity);
}
public virtual void Remove(T entity)
{
_newentities.Set<T>().Remove(entity);
}
public virtual void Edit(T entity)
{
_newentities.Entry(entity).State = System.Data.EntityState.Modified;
}
public virtual void Save()
{
_newentities.SaveChanges();
}
private bool disposed = false;
protected virtual void Dispose(bool disposing)
{
if (!this.disposed)
if (disposing)
_newentities.Dispose();
this.disposed = true;
}
public void Dispose()
{
Dispose(true);
}
}
I created an abstract class which implements IGenericReposity<T> interface but also accepts another type parameter a type of DbContext class.
The methods above which implement basic data access methods are marked as virtual so they can be overwritten.
8) The concrete implementation for the two domain classes follow
public class ProductReposiltory :
GenericRepository<DBEntities, Product>, IProductRepository
{
public Product GetByID(int ProductId)
{
var query = this.GetAll().FirstOrDefault(x => x.ProductId == ProductId);
return query;
}
}
public class CustomerRepository:GenericRepository<DBEntities, Customer>,ICustomerRepository
{
public Customer GetByID(int CustomerId)
{
var query = this.GetAll().FirstOrDefault(x => x.CustomerId == CustomerId);
return query;
}
}
My concrete classes inherit from the Generic Repository and their own specific interface which in their turn inherit from the the IGeneric Repository.
I managed to have a code that implements separation of concerns by having a separate layer that creates that abstraction. My code is more testable and I do not have to repeat it.
Ι will create in another post the MVC project that will use my repositories.
Hope that helps!!!
In this post I will article shows you how to build a simple web application in ASP.NET MVC 6 & Entity Framework 7 using ASP.NET 5-ASP.Net Core.
I will also demonstrate some new concepts in ASP.Net 5.0 and highlight important changes.
If you are new to ASP.Net 5.0 and MVC 6.0 then please have a look at this introductory post of mine, here.
If you want to learn ASP.Ne 5.0 by video there is a great introductory course by Scott Hanselman in this link.
You can developer ASP.Net 5.0 applications without Visual Studio and outside Windows. I will use Visual Studio 2015 in this post.Any edition will work.
To those people who do not have access to Visual Studio 2015, please note that Visual Studio 2015 has a community edition which is available for free.Please check the licensing of this edition though
You can download it here. You also need to download ASP.Net 5.0.
Please note that ASP.NET 5 is not included in the installation of Visual Studio 2015. This will probably change in the fucture.
Before diving into our hands-on example, I would like to highlight the fact that ASP.NET 5.0 has been renamed to ASP.Net Core 1.0.
ASP.NET 5 is significantly different than other versions of ASP.NET. It has been rebuilt from scratch. It is open source, lightweight and cross-platform.
Let's start with our own hands-on example. It will be a simple CRUD Application that creates projects and maps employees to them.
1) Launch Visual Studio 2015 and create a new ASP.NET Application, name it as ASP.NetMVC6. Have a look a the picture below.
2) Click on Ok and in the next window select Empty from ASP.NET 5 Templates as shown in the following image.
3) After creating the project, the project structure will be as shown in the following image
4) Before explaining the various files in the new Solution structure (very different from Solution structures in previous ASP.Net versions) DNX is the .Net execution environment ASP.NET 5 uses. This runtime environment has everything to build and run .NET Apps on Windows, Mac and Linux. The project shows necessary references for the project.
Under Solution Items, there is a file called global.json. Open this file and you will see a projects setting that mentions src, for source, and test.
These are instructions for DNX, which is responsible for executing our application.
This section in global.json instructs the DNX to go look for projects in the listed subfolders src and test.
DNX relies on the global.json configuration file. Also in the file is the SDK version which explicitly configures this solution to work with a particular version of the DNX, in my case is "1.0.0-rc1-update1". You can also see the Solution DNX SDK version if you right-click on the project and choose Properties.
5) Project.json is used to configure all dependent reference assemblies for the application. This file provides intellisense for assemblies and their versions so that we can select a necessary reference for developing application.
If you right-click on the project and view all files in the File Explorer then you can see the csproj file is not there anymore. Visual Studio now uses an xproj file together with project.json. Have a look at the picture below
Project.json is used as the configuration file, but this time for the project instead of the solution.
6) Another big change that catches the eye is the wwwroot folder.Folder wwwroot is the actual server root. Only the files that are in wwwroot are accessible by the browser and served by the server.
We keep all our configurations in files like global.json and project.json outside of wwwroot so they can never be accessed in the browser.
Folder wwwroot contains front-end resources like CSS, JavaScript libraries, images, etc.
Dependencies is about client-side development. It allows you to configure the dependencies for the project like loading external JavaScript library like jQuery, Knockout.js, Bootstrap, etc. and using Bower and npm tools.
We all use Nuget to manage various packages that we need for our various applications.
We use it to install Entity Framework and a client side library like JQuery.
In ASP.Net 5.0 we should use NuGet for packages that are related to ASP.NET framework.
In ASP.Net 5.0 as far client-side packages is concerned we should use Bower.
Bower is just a client-side package manager that is fully supported from within Visual Studio.
Through Grunt developers can minify javascript files,compile LESS and Sass files into CSS, do code validation, run javascript unit tests.
Project.json is used to configure all dependent reference assemblies for the application. This file provides intellisense for assemblies and their versions so that we can select a necessary reference for developing application.Open this file in VS. You will see something like this.
{
"version": "1.0.0-*",
"compilationOptions": {
"emitEntryPoint": true
},
"dependencies": {
"Microsoft.AspNet.IISPlatformHandler": "1.0.0-rc1-final",
"Microsoft.AspNet.Server.Kestrel": "1.0.0-rc1-final"
},
"commands": {
"web": "Microsoft.AspNet.Server.Kestrel"
},
"frameworks": {
"dnx451": { },
"dnxcore50": { }
},
"exclude": [
"wwwroot",
"node_modules"
],
"publishExclude": [
"**.user",
"**.vspscc"
]
}
Project.json along with the project's xproj file is the replacement for the csproj file.
You will notice that the xproj file is a specific Visual Studio config file. DNX doesn't need it to execute your project.
Project.json contains the metadata of your project. One can specify the version, the title of the application.There is intellisense in this file.
In versions of ASP.NET prior to 5, the system.web assembly took care of starting the app, and the global.asax file had methods that were called by system.web in which you could provide custom logic.
7) Startup.cs is an entry point for the application. This file is used to define the environment for the application, e.g. Using MVC, EntityFramework, etc.
The steps needed to start up an application are now determined by the Startup class. There is no global.asax file.
The Startup class contains a Main method and when DNX executes the application, it looks for this method and calls it.
The DNX hosting environment will by call two methods first ConfigureServices and then Configure.
The purpose of the ConfigureServices method is to configure dependency injection. Dependency injection is integral part of ASP.NET. The dependency injection uses an IoC container.
The concept of Dependency Injection is really simple. Types,classes when the applications starts up are registered in the container. You can think of these types as services. We usually want to create instances of classes from other classes. That is not so good when we develop large scale applications.
Now that our classes/services are registered with the containter, we ask the container for an instance of those registered classes/services.
During registration,the lifetime of that instance is determined. The lifetime of the object is managed by the container.
You are free to use other dependency injection frameworks like Ninject,Autofac but now ASP.NET 5 comes with a default dependency injector out of the box.
The container is invisibly created at application startup and filled with ASP.NET 5's services.
At application startup,we can register our own types in the container by calling the ConfigureServices method in the Startup class.
By supplying an IServiceCollection object as a parameter of ConfigureServices, you get the API to do the registering.
You can see below the part of the Startup.cs file where you can register types.
public void ConfigureServices(IServiceCollection services)
{
}
The Configure method configures the HTTP request pipeline of ASP.NET meaning it specifies how the application should respond to HTTP requests.
When the request is made, it first arrives at the web server which in most cases is IIS.
IIS then invokes the DNX which loads the CLR.
Then an entry point for the application is needed. Inside the Startup.cs file, the Main method, is executed.
Then the request is pushed to and processed by the middlewares (Authentication,ASP.Net MVC 6.0) which will produce the response.
8) Now that we explained the various items in a new ASP.Net Core application, let's get back to our actual application.We will add dependencies for our application in the dependencies section of the project.json file. I have added the following dependencies
"EntityFramework.Commands": "7.0.0-rc1-final",
"EntityFramework.MicrosoftSqlServer": "7.0.0-rc1-final",
"Microsoft.AspNet.Mvc": "6.0.0-rc1-final",
"Microsoft.AspNet.Mvc.TagHelpers": "6.0.0-rc1-final",
"Microsoft.AspNet.Razor": "4.0.0-rc1-final",
"Microsoft.Extensions.Configuration.Json": "1.0.0-rc1-final",
"Microsoft.Extensions.CodeGenerators.Mvc": "1.0.0-rc1-final"
The whole dependecies section is
"dependencies": {
"Microsoft.AspNet.IISPlatformHandler": "1.0.0-rc1-final",
"Microsoft.AspNet.Server.Kestrel": "1.0.0-rc1-final",
"EntityFramework.Commands": "7.0.0-rc1-final",
"EntityFramework.MicrosoftSqlServer": "7.0.0-rc1-final",
"Microsoft.AspNet.Mvc": "6.0.0-rc1-final",
"Microsoft.AspNet.Mvc.TagHelpers": "6.0.0-rc1-final",
"Microsoft.AspNet.Razor": "4.0.0-rc1-final",
"Microsoft.Extensions.Configuration.Json": "1.0.0-rc1-final",
"Microsoft.Extensions.CodeGenerators.Mvc": "1.0.0-rc1-final"
},
After we do that you will see all these referneces added under the DNX 4.5.1 and DNX Core 5.0
Let me go through those dependencies and what actually mean.
a) EntityFramework.Commands is used for DB Migrations so that POCO Classes can be migrated and mapped with database objects
b) EntityFramework.MicrosoftSqlServer is used to specify the Database Server used as Sql Server
c) Microsoft.AspNet.Mvc specifies that MVC 6 is used for the application
d) Microsoft.AspNet.Razor indicates that the Razor View Engine will be used
e) Microsoft.Extensions.Configuration.Json gives the ability to load and read configuration from JSON files in the application.
9) Let's move on with creating the main POCO classes. As I said in the beginning of this post I will create a simple ASP.Net MVC 6.0 application that allows the user to create Projects (create,update,delete projects) and map Employees(create,update,delete employees) to projects. One project can have many employees working on it. We add a new folder in the project of name Models.
The two classes follow
public class Project
{
public int ProjectId { get; set; }
public string ProjectName { get; set; }
public virtual ICollection<Employee> Employees { get; set; }
}
public class Employee
{
public int EmployeeId { get; set; }
public string EmployeeName { get; set; }
public int Age { get; set; }
public int ProjectId { get; set; }
public virtual Project Project { get; set; }
}
The Project class contains the Employees collection representing one-to-many relationship.
The Employee class contains the reference for the Project class and there willl be a relationship between Project and Employees and we will be able to navigate between those two types.
10) Now we will need to add the Controller in our MVC project.We will use the amazing help of the VS scaffolding mechanism.Add a Controllers folder to your project.Right-Click on the folder and select Add Controller, option. Have a look at the picture below.
On clicking the Add button the following window will be displayed.In this window select Model class name.We will generate controller for Project entity. Add the DbContext class by clicking on the + button.
Keep the Generate Views and Use Layout View checkbox checked.
After clicking the Add button, the project will show ProjectsController class added in the Controllers folder with all the CRUD methods.
A Views folder will be created along with the Projects subfolder in it with Views for performing CRUD operations.
Now we need to repeat the same steps for Employee Model to generate controller and views for the employees.
In the Models folder, the ASPNetMVC6Context.cs file will be added with the following code:
The contents of the file follow
public class ASPNetMVC6Context : DbContext
{
private static bool _created = false;
public ASPNetMVC6Context()
{
if (!_created)
{
_created = true;
Database.EnsureCreated();
}
}
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
}
protected override void OnModelCreating(ModelBuilder builder)
{
}
public DbSet<Project> Project { get; set; }
public DbSet<Employee> Employee { get; set; }
}
11) Now we need to instruct EF 7.0 to implement DB migrations and to generate tables in the database. In my local instance of SQL Server I create a database with the name "Projects". We need to define a connection string by adding a ASP.Net Configuration file.
We add a new ASP.NET Configuration file in our project with the default name appsettings.json
The contents of this file follow
{
"Data": {
"DefaultConnection": {
"ConnectionString": "Server=.;Database=Projects;Trusted_Connection=True;"
}
}
}
12) In the Startup.cs, we must add the following property for Configuration so that we can load the appSettings.json file.
public IConfigurationRoot Configuration { get; set; }
Add the constructor in the Startup class with the following code:
public Startup(IHostingEnvironment env)
{
var builder = new ConfigurationBuilder()
.AddJsonFile("appsettings.json")
.AddJsonFile($"appsettings.{env.EnvironmentName}.json", optional: true);
builder.AddEnvironmentVariables();
Configuration = builder.Build();
}
This code loads and reads the appSettings.json file.
13) We need to add and change some code in the ConfigureServices () method of the Startup class.
This configures the EntityFramework for the application with Sql Server and the connection string. The code follows:
public void ConfigureServices(IServiceCollection services)
{
services.AddEntityFramework()
.AddSqlServer()
.AddDbContext<ASPNetMVC6Context>(options =>
options.UseSqlServer(Configuration["Data:DefaultConnection:ConnectionString"]));
services.AddMvc();
}
We pass the right connection string and not the LocalD one.
The services.AddMvc() adds the MVC service in the IServiceCollection.So we register MVC with the built-in DI framework.
14) In the Configure() method, we must add the following code in order to use MVC and routing for request processing.
app.UseStaticFiles();
app.UseMvc(routes =>
{
routes.MapRoute(
name: "default",
template: "{controller=Home}/{action=Index}/{id?}");
});
The complete code inside the Configure() method follows:
public void Configure(IApplicationBuilder app)
{
app.UseIISPlatformHandler();
app.UseStaticFiles();
app.UseMvc(routes =>
{
routes.MapRoute(
name: "default",
template: "{controller=Home}/{action=Index}/{id?}");
});
}
The above code also uses the UseStaticFiles() method which helps us to read and use CSS and jQuery files from the project in the View.
15) Now we need to add the migration commands in order to map the classes to the database.
Open the project in file explorer. Go to the Project folder (in my case is D:\for blog\ASP.NetMVC6\src\ASP.NetMVC6). You need to use the Open Command Window here. In order to make that window apper,right-click on the project folder from the file explorer, with ctrl+shift combination.
In that window type the following commands
a) dnvm upgrade
b) dnu restore
c) dnvm install 1.0.0-rc1-final
d) dnvm use 1.0.0-rc1-final
e) dnx ef migrations add Initial
After executing these commands, we will see the Migrations folder with following files:
a) 20160327174152_Initial_initial.cs - Contains code for creating table based on model classes.
b) ASPNetMVC6ContextModelSnapshot.cs - This is a class which is used as model builder with model class properties and its types.
Build the application.
In your Projects database you will see the Project and Employee tables in it.
16) Tag Helpers is one brand new feature in ASP.Net 5 and ASP.Net MVC 6.
This is a direct alternative of the MVC helper methods that we are writing inside our Razor Views.
I am pretty sure HTML designers will love this new feature as it makes more sense to them.
Let's have a look at the View below
@using (Html.BeginForm())
{
<div>
@Html.LabelFor(m => p.Αge, "Age:")
@Html.TextBoxFor(m => p.Age)
</div>
<input type="submit" value="Submit" />
}
We have the Html.BeginForm(), Html.LabelFor(), and Html.TextBoxFor() helper methods that are part of the razor syntax in order to build a form.
Let's rewrite the snipper above using Tag Helpers:
@addtaghelper "Microsoft.AspNet.Mvc.TagHelpers"
<form asp-controller="Customers" asp-action="Submit" method="post">
<div>
<label asp-for="Age">Age:</label>
<input asp-for="Age" />
</div>
<input type="submit" value="Save" />
</form>
Open up any of the created views and you will see Tag Helpers in them.
The asp-validation-summary attribute is used to display the validation summary. The asp-for attribute is used for Model Binding.
To execute these attributes we need to add new item of type, MVC View Imports . Right-click on the Views folder and add such an item. The default name is _ViewImports.cshtml view.
In this view add the following code.
@addTagHelper "*, Microsoft.AspNet.Mvc.TagHelpers"
17) Build and run your application. Navigate to the http://localhost:33158/projects
Add new projects to the application. Those projects will be saved to the database.
Obviously our application lacks in stying.We need to add Bootstrap to it.
When we need to add client side libraries like Bootstrap or JQuery in our project we should use Bower.
We need to add to the project a Bower Configuration File (Bower.json). We should add the following dependencies:
"dependencies": {
"jquery": "2.1.4",
"bootstrap": "3.3.6"
}
All the files will be added by the manager in the wwwroot/lib folder.
You need to reference those libraries them in the _Layout.cshtml view if you want to use those libraries in your project.
Then you need to reference the _Layout.cshtml view in your Views.
In this post we talked a lot about ASP.NET 5.0 and we showed how to build a CRUD application. We also looked in the new project structure and explained the various files and folders.
Hope it helps!!!
This is the second post in a series of posts regarding passing multiple models in a view.You can find the first one here
We do know that in any MVC application we cannot pass multiple models from a controller to a single view.
In this post I will provide you with a workaround for passing multiple models in a single view in MVC.
In object-oriented and functional programming, an immutable object is an object whose state cannot be modified after it is created.
A Tuple object is an immutable, fixed-size and ordered sequence object. It is a data structure that has a specific number and sequence of elements. The .NET framework supports tuples up to seven elements.
Strings and other concrete objects are typically expressed as immutable objects to improve readability and runtime efficiency in object-oriented programming.
I am going to use EF as my data access layer. More specifically I will use the Database First approach in EF.
Entity Framework is an object-relational mapping (ORM) framework for the .NET Framework.
EF addresses the problem of Object-relational impedance mismatch. I will not be talking about that mismatch because it is well documented in many sites on the Internet.
Through that framework we can program against a conceptual application model instead of programming directly against a relational schema-model.
1) I will create an empty ASP.Net Application (Empty MVC applicatin) and I give it the name .
I am using Visual Studio 2015 Enterprise edition, C# 5.0 and EF 5.0 version.
2) I will use the AdventureWorksLT2012 database. You can download it by visiting this link.
I have installed SQL Server 2014 Enterprise edition in my machine. SQL Express edition will work fine.
4) I will add an ADO.Net Entity data model using Database First. Follow the wizzard steps, create the connection string and then import into the conceptual model the ProductCategory and Product tables which will become two new entities in the domain model.
5) Add a new controller class in the Controllers Folder. Name it ProductController.cs
The code for the Controller follows
public class ProductController : Controller
{
// GET: Product
AdventureWorksLT2012Entities ctx = new AdventureWorksLT2012Entities();
public ActionResult Index()
{
var model = new Tuple<List<ProductCategory>, List<Product>>(GetCategory(), GetProducts());
return View(model);
}
public List<Product> GetProducts()
{
List<Product> products = new List<Product>();
var query = from p in ctx.Products
select p;
return query.ToList();
}
public List<ProductCategory> GetCategory()
{
List<ProductCategory> categories = new List<ProductCategory>();
var query = from c in ctx.ProductCategories
select c;
return query.ToList();
}
}
We create an object of the AdventureWorksLT2012Entities class.
We create two methods GetCategory(), GetProducts() that return a list of categories and product objects.
Inside the Index method of the controller
var model = new Tuple<List<ProductCategory>, List<Product>>(GetCategory(), GetProducts());
we store in the model that we will pass in our view, inside the Tuple object a sequence of ProductCategory,Product objects.
6) Now we need to add a View in our application. Add a view, do not use scaffolding, and name it Index. Place the Index view in the Product folder inside the Views folder.
In the Index.cshtml we have the following code
@model Tuple<List<ProductCategory>, List<Product>>
@{
Layout = null;
}
<b>Categories List</b>
<ul>
@foreach (var item in Model.Item1)
{
<li>@item.Name</li>
}
</ul>
<hr />
<b>Product List</b>
<ul>
@foreach (var item in Model.Item2)
{
<li>@item.Name - @item.Color</li>
}
</ul>
The model we pass to the view is @model Tuple<List<ProductCategory>, List<Product>>
Then we just create two foreach statements and iterate through the list of items. We get the name of the each category and the product name-color from each product in the database.
7) Build and run your application. You will see data from ProductCategory and Product entities.
Hope it helps!!!
We do know that in any MVC applicatin we cannot pass multiple models from a controller to a single view.
In this post I will provide you with a workaround for passing multiple models in a single view in MVC.
There are several approaches to achieve this but I will use the List<object> and the object object.
I am going to use EF as my data access layer. More specifically I will use the Database First approach in EF.
Entity Framework is an object-relational mapping (ORM) framework for the .NET Framework.
EF addresses the problem of Object-relational impedance mismatch. I will not be talking about that mismatch because it is well documented in many sites on the Internet.
Through that framework we can program against a conceptual application model instead of programming directly against a relational schema-model.
1) I will create an empty ASP.Net Application (Empty MVC applicatin) and I give it the name .
I am using Visual Studio 2015 Enterprise edition, C# 5.0 and EF 5.0 version.
2) I will use the AdventureWorksLT2012 database. You can download it by visiting this link.
I have installed SQL Server 2014 Enterprise edition in my machine. SQL Express edition will work fine.
4) I will add an ADO.Net Entity data model using Database First. Follow the wizzard steps, create the connection string and then import into the conceptual model the ProductCategory and Product tables which will become two new entities in the domain model.
5) Add a new controller class in the Controllers Folder. Name it ProductController.cs
The code for the Controller (Index method) follows
public class ProductController : Controller
{
// GET: Product
AdventureWorksLT2012Entities ctx = new AdventureWorksLT2012Entities();
public ActionResult Index()
{
List<object> model = new List<object>();
model.Add(ctx.ProductCategories.ToList());
model.Add(ctx.Products.ToList());
return View(model);
}
}
We create an object of the AdventureWorksLT2012Entities class.
Then we create a List<object> object list. We add the Products collection and ProductCategories collection to that list.
6) Now we need to add a View in our application. Add a view, do not use scaffolding, and name it Index. Place the Index view in the Product folder inside the Views folder.
In the Index.cshtml we have the following code
@model IEnumerable<object>
@{
List<PassingMultipleModelsToAView.ProductCategory> lstPCategory = Model.ToList()[0] as List<PassingMultipleModelsToAView.ProductCategory>;
List<PassingMultipleModelsToAView.Product> lstProduct = Model.ToList()[1] as List<PassingMultipleModelsToAView.Product>;
}
<h3>Categories</h3>
<ul>
@foreach (var item in lstPCategory)
{
<li>@item.Name</li>
}
</ul>
<hr />
<h3>Products</h3>
<ul>
@foreach (var item in lstProduct)
{
<li>@[email protected]</li>
}
</ul>
The model we pass to the view is @model IEnumerable<object>.
Then we pass the first list of the model (ProductCategory) to a list of the same type and the second list of the model(Product) to a list of the same type.
Then we just create two foreach statements and iterate through the list of items.
7) Build and run your application. You will see data from ProductCategory and Product entities. So we still passed a model to the view (model IEnumerable<object>) but this model represented two entities/models so in reality we passed
PassingMultipleModelsToAView.ProductCategory, PassingMultipleModelsToAView.Product models to the view and achieved our goal.
Hope it helps!!!
In this post I am going to provide you with a hands on example on how to handle concurrency in an EF Code First ASP.Net MVC 5.0 application. Basically I want to show you how to handle conflicts when multiple users update the same entity at the same time.
You can find another post regarding concurrency with EF Database First and web forms here.
When we are talking about concurrency in an application where EF acts as the data access layer, our main focus is how to make sure that the integrity of the data in the database is ensured in a multi user connected environment.
Databases are very good in managing concurrent users. All the objects/entities are retrieved from the datastore into the memory. The changes we make are applied in the memory and then we flush the changes to the database.
But what happens if those entities have been changed from another user in the time it took us to update them in memory?Well someone will say, "Dude, you just have to use pessimistic concurrency and your problems are solved."
Well, I do not want to go down that road. Locks are applied to all related data when I use this type of concurrency management.
Remember we are in a multiuser enviroment. That means I want to have data integrity on the one hand but I also want performance to be at an acceptable level. With pessimistic concurrency you limit scalability and performance. The Entity Framework provides no built-in support for it.
So I will use optimistic concurrency which in plain words mean "Hm... I want to check if anyone has modified the data in the database while I did my own modifications in memory".
The very basic scenario in a multi-user environment where a concurency conflict takes place is when one user displays an entity's data in order to edit it, and then another user updates the same entity's data before the first user's change is written to the database.
If you don't enable the detection of such conflicts, whoever updates the database last overwrites the other user's changes.
In many applications, this risk is acceptable: if there are few users, or few updates, or if isn't really critical if some changes are overwritten, the cost of programming for concurrency might outweigh the benefit.
In that case, you don't have to configure the application to handle concurrency conflicts.
I will use Visual Studio 2015 Enterprise edition,C# and EF Code First 6.1.3 version to build an MVC 5.0 application that
- will list information about a database that holds information about footballers
- will enable the user to create a new footballer
- will enable the user to edit an existing footballer
- will enable the user to delete an existing footballer
1) I am creating an ASP.Net Web Application, namely ConcurrencyViolationEFCodeFirst. I will use the ASP.Net 4.5.2 Empty (MVC template).I have an application with no models,views or controllers.
2) Now I will add a model to the application.I will use this model to manage the footballer's data-entities.
I will use Entity Framework as the data access technology. More specifically I will use the Code First paradigm. You might have come across the term POCO classes.This is exactly what I am going to use.If you want to learn more about Code First have a look in this post .
I need to install Entity Framework. I can do that from the Nuget Package Manager.
Then from the model class we will create our database.In the Solution Explorer right-click on the Models folder,select Add and then select Class.
Name the class Footballer.cs and add some properties to the class.My complete class follows
public class Footballer
{
public int FootballerID { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public double Weight { get; set; }
public double Height { get; set; }
public DateTime JoinedClub { get; set; }
}
3) Let me explain what I am doing here. I will use the Footballer class to represent footballers in a database (which I am going to create). Each instance of a Footballer class will correspond to a row within a database table.Naturally each property of the Footballer class will map to a column in the table (which I am going to create).
We need to add some more code to a separate class the FootballerDBContext.cs
public class FootballerDBContext : DbContext
{
public DbSet<Footballer> Footballers { get; set; }
}
The FootballerDBContext is a database context class.This class is responsible for talking to the underlying database,storing and updating the data to the database.
We need to add this reference to the file
using System.Data.Entity;
4) Now we need to create the connection string.The only place we can do that is by opening the web.config file and adding the following lines of code (inside the <connectionStrings> section)
<add name="FootballerDBContext"
connectionString="Data Source=(LocalDb)\v11.0;AttachDbFilename=|DataDirectory|\Footballers.mdf;Integrated Security=True"
providerName="System.Data.SqlClient"
/>
As you can see from the connection string I am using LocalDB.
Have a look here in order to see what LocalDB is.
5) Now we need to access our model from a controller.This is going to be a simple class that retrieves the footballers data.
Right-click the Controllers folder and create a new FootballerController controller. We will use the scaffolding system of ASP.Net MVC to create our controllers class and its action methods for edit,list,create, delete and the views needed to dispay the data to the user. We will use the Scaffold MVC 5 Controller with views, using Entity Framework.
The model class would be:Footballer (ConcurrencyViolationEFCodeFirst.Models)
The Data Context class would be:FootballerDBContext (ConcurrencyViolationEFCodeFirst.Models)
6) The ASP.NET MVC 5 framework as you can see automatically creates the CRUD (create, read, update, and delete) action methods and views.We have a fully functional web application that lets you create, list, edit, and delete records.
7) Build and run your application.Navigate to the localhost/youport/footballer
8) You can create a new footballer object-record.This information will be saved in the LocalDB-Footballers.mdf database.It is created by EF when you first run the application.
9) Make sure you add more entries to the database through the view/application.
We have created a new record and stored it in the database.Click the Edit,Details and Delete links.We have all this functionality out of the box through the magic of scaffolding.
I urge you to have a look (place breakpoints as well) in the FootballerController.cs class file.
We pass a strongly typed object (Footballer) to the various views.
Have a look in the views inside the Views/Footballer folder.
In the Create.cshtml, Delete.cshtml, Details.cshtml, Edit.cshtml, and Index.cshtml Views , at the beginning of these files, you will see this line of code.
@model IEnumerable<ConcurrencyViolationEFCodeFirst.Models.Footballer>
By adding a @model statement at the top of the view file, we tell the view the type of object that the view should render.
10) When running the application and hitting the record (I have added a couple of rows in the database through the UI already) http://localhost:4809/footballer/Edit/1 you can edit the record.Make some changes in the record but before hitting "Save" open the same record in another browser - http://localhost:4809/footballer/Edit/1 and change some other value of the same record- entity and then hit the "Save" button in the first window/browser. The changes will be saved to the database. Hit "Save" in the other second browser window. You will then see that the changes you made initially have been lost and only the changes in the second browser window have been saved. You have lost data integrity. Basically your data is of no value. We will demonstrate below mechanisms where you will not lose your data and handle concurrency exceptions.
This is called a Client Wins or Last in Wins scenario. (All values from the client take precedence over what's in the data store.) If you don't do any coding for concurrency handling, this will happen automatically.
You can prevent changes from the second browser/windows/user from being updated in the database. Typically, you would display an error message, show the current state of the data, and allow him/her to reapply her changes if she/he still wants to make them. This is called a Store Wins scenario. (The data-store values take precedence over the values submitted by the client.)
This method ensures that no changes are overwritten without a user being alerted to what's happening.
11) We can resolve conflicts by handling OptimisticConcurrencyException exceptions that the Entity Framework throws.
In order to know when to throw these exceptions, the Entity Framework must be able to detect conflicts. Therefore, you must configure the database and the data model appropriately.
Entity framework code first offers two approaches to detect concurrency violations:
- [ConcurrencyCheck] attribute
- [Timestamp] attribute
The first approach is used in cases where your table doesn't have any timestamp or rowversion column.
You are required to decorate all the non-primary key properties of an entity with [ConcurrencyCheck] attribute. That usually requires a lot of work.
Entity framework then adds all these columns to the WHERE clause of the resultant SQL statement. This way you check whether all the column values that you fetched and the values in the database are same or not.
If they are same then noone else modified that record and your UPDATE / DELETE succeeds.
If the values don't match then someone else has modified the values and your UPDATE / DELETE statement fails.
In the other approach we add a timestamp or rowversion column in your table.
In this case you decorate only the timestamp / rowversion property with the [Timestamp] attribute.
Just like [ConcurrencyCheck] entity framework then adds WHERE clause for this property.The Timestamp attribute specifies that this column will be included in the Where clause of Update and Dlelete commands sent to the database.
The advantage here is that you have only one column in the WHERE clause (in addition to the primary key) instead of many as in the case of [ConcurrencyCheck] attribute. The SQL server timestamp / rowversion gets translated as a byte[] in .NET code.
12) We change the definition of the public class Footballer by adding the Timestamp attribute to the RowVersion column
public class Footballer
{
public int FootballerID { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public double Weight { get; set; }
public double Height { get; set; }
public DateTime JoinedClub { get; set; }
[Timestamp]
public byte[] RowVersion { get; set; }
}
13) If we build and run the application we will receive an error
"The model backing the 'FootballerDBContext' context has changed since the database was created. Consider using Code First Migrations to update the database"
By adding a property you changed the database model, we need to do a migration. If you want to find out more about Code First Migrations .In the Package Manager Console (PMC), enter the following commands:
Enable-Migrations
Add-Migration RowVersion
Update-Database
14) Now if we look back through Server Explorer in the table we will see a Rowversion column. Now if we build and run the application we will receive no error. Have a look at the picture below.
15) Now we need to change the code inside the HttpPost Edit method with the following code:
[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Edit([Bind(Include = "FootballerID,FirstName,LastName,Weight,Height,JoinedClub,Rowversion")] Footballer footballer)
{
if (ModelState.IsValid)
{
try
{
db.Entry(footballer).State = System.Data.Entity.EntityState.Modified;
db.SaveChanges();
return RedirectToAction("Index");
}
catch (DbUpdateConcurrencyException ex)
{
ModelState.AddModelError("", "Unable to save changes. The record was modified by another user after you got the original value");
}
}
return View(footballer);
}
If no rows are affected by the UPDATE command (no rows have the original RowVersion value), the Entity Framework throws a DbUpdateConcurrencyException exception.
16) Now we build and run the application again.
When running the application and hitting the record (I have added a couple of rows in the database through the UI already) http://localhost:4809/footballer/Edit/1 you can edit the record.Make some changes in the record but before hitting "Save" ,open the same record in another browser(e.g Opera) - http://localhost:4809/footballer/Edit/1 and change some other values of the same record- entity and then hit the "Save" button in the first browser. You will not have an problems, the data is persisted to the database.
Then hit save in the second browser window. You will then see that the changes you made initially have been lost and only the changes in the second browser window have been saved.
Instead of seeing your changes in the second browser window persisted to the db you will receive the error below
"Unable to save changes. The record was modified by another user after you got the original value"
Now you cannot save data back to the datastore. You can go back to the list of records, get record 1 again from the database and edit/save the new values.
Hope it helps!!!
In this post I am going to provide you with a hands on example on using caching in an ASP.Net MVC 5.0 application using Visual Studio 2015 and C#.
More particularly I'll show you how to use the underlying ASP.net caching engine to improve the performance of an application.
There is a number of different cache settings you can use.These settings are all available from the underlying ASP.net cache engine. It's the same engine that Web Forms uses. It's not something that's specific to just the MVC framework.
But first things first. What is caching why do we need caching in the first place?
Caching is of vital significance in any high-performance web application.
Caching provides a way of storing frequently accessed data and reusing that data.
It is an effective way for improving web application’s performance and user experience.
The main advantages of caching include:
- Reduce network traffic - Content is cached at the client side, thus reducing network traffic
- Reduce database server round trips -Content that is cached at the web server, reduces requests to the database
- Reduce hosting server round-trips - Requests to the server are reduced
- Not generating reusable content - Avoid time and resource consumption for regenerating reusable content
It is profound that caching benefits the performance and user experience for a web application.
The following points indicate the situation when caching should be considered
- Content that is frequently accessed should be a good candidate for caching
- Content thatis unique for a user/session should not be cached
- Content that is very infrequently accesses should not be cached
- For content that is dynamically produced and changes frquently do not disable caching. Do define a shorter cache–expiration instead.
- When you want to cache versions of a page based on request such as cookies, theme, browser ,you could use the VaryByCustom function
When it comes to Caching an ASP.Net MVC 5.0 application, we will use Output caching.
Output caching allows you to store the output of a particular controller action in memory.
Then the ASP.net engine can respond to future requests for the same action just by giving back the cached result.
By doing that no code inside the action is executed. Having said that even though ASP.Net MVC is an entirely new paradigm on building an ASP.Net web application, is based on ASP.Net infrastructure and on the caching functionality that ship with ASP.Net.
In the ASP.Net MVC world we are familiar with terms like Model, View, Controller, ActionResults, Action Selectors, Code Expressions, Razor expressions and Action Filters. Using Action Filters is the way to enable Output Caching in a controller action.
By default, this attribute filter caches the data for 60 seconds. After 60 seconds, you guessed it, a call is made again to this action and ASP.NET MVC will cache the output again.
You can cache controller actions that produce any king of result (Views, static contents, Json).
I pointed out before we should cache content that is frquently accessed so in our MVC application we will cache these actions in a controller that are called often and execute "expensive statements" like db queries.
Do not randomly cache any action method in a controller. You must devise a successful caching strategy and to really know where your traffic is going. You have to know what are the most expensive operations in your software.
You have to take some measurements and do some logging in order for caching to really work effectively.
Let's move on to the actual hands-on example.
If you are not familiar with ASP.Net MVC please have a look at this post of mine.
1) I have installed Visual Studio 2015 Enterprise edition in my machine which is Windows 8 by the way.I have also installed SQL Server 2014 Express Edition in my machine.
I am going to build an ASP.Net MVC 5.0 application using Entity Framework and Code First. We will target the 4.5.2 version of the framework. Firstly I will start with a simple example with no database access.
ASP.Net MVC does not dictate what kind of data access architecture we will use in our application. It does not also dictate how to build our business layer (domain classes and objects).
2) I am launching VS 2015 and I will Visual C# as the programming language. I will also select ASP.NET MVC 5 Web Application from the available templates (Empty Template using MVC).
3) I am adding a HomeController.cs class in the Controllers folder.
4) The code in the Controller action follows
public class HomeController : Controller
{
// GET: Home
[OutputCache(Duration =20, VaryByParam="none")]
public ActionResult Index()
{
ViewBag.Message = DateTime.Now.ToString();
return View();
}
}
5) Set a break point at the beginning of this action and then run with the debugger.
6) What you should see here is on this first request, we will hit that break point so we're inside of the index action and you'll notice the one parameter I have to specify with the output cache attribute is the duration.
7) How long do I cache the response? It's specified in seconds and this is 20 seconds. So now if I press F5 to continue with the debugger, I can now see the home page and the caching time and now I can refresh this as many times as I want and the caching logic inside of ASP.net is looking at the request, seeing that there's a cached response for this request.
8) The code inside the Index.cshtml view follows:
@{
ViewBag.Title = "Index";
}
<h2>Index</h2>
<div>
<b>Caching Time:</b>@ViewBag.Message
</div>
9) Remove the debugger from the Action and then run the application again. Refresh the page.For a period of 20 seconds you will see the same time. After 20 seconds the action method will run again and cache the output again for another 20 seconds.
Let's have a look now in an example where we cache results from a database.
10)I will create a new ASP.Net MVC 5 application using the Empty Template (MVC). Now I will add a model to the application.I will use this model to manage footballer's data.I will use Entity Framework as the data access technology. More specifically I will use the Code First paradigm. You might have come across the term POCO classes.This is exactly what I am going to use.If you want to learn more about Code First have a look in this post .
Then from the model class we will create our database.In the Solution Explorer right-click on the Models folder,select Add and then select Class.
Name the class Footballer.cs and add some properties to the class.My complete class follows
public class Footballer
{
public int FootballerID { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public double Weight { get; set; }
public double Height { get; set; }
public DateTime JoinedClub { get; set; }
}
Let me explain what I am doing here. I will use the Footballer class to represent footballers in a database (which I am going to create). Each instance of a Footballer class will correspond to a row within a database table.Naturally each property of the Footballer class will map to a column in the table (which I am going to create).
We need to add some more code in another calss to the FootballerDBContext.cs
public class FootballerDBContext : DbContext
{
public DbSet<Footballer> Footballers { get; set; }
}
The FootballerDBContext is a database context class.This class is responsible for talking to the underlying database,storing and updating the data to the database.
We need to add this reference to the file
using System.Data.Entity;
11) Now we need to create the connection string.The only place we can do that is by opening the web.config file and adding the following lines of code (inside the <connectionStrings> section). I am
<add name="FootballerDBContext"
connectionString="Data Source=(LocalDb)\v11.0;AttachDbFilename=|DataDirectory|\Footballers.mdf;Integrated Security=True"
providerName="System.Data.SqlClient"
/>
As you can see from the connection string I am using LocalDB.
Have a look here in order to see what LocalDB is.
12) Now we need to access our model from a controller.This is going to be a simple class that retrieves the footballers data.
In the Controllers folder add the FootballerController.cs file.
Right-click the Controllers folder and create a new FootballerController controller. Have a look at the picture below to set the appropriate settings
Inside the FootballerController controller, the Index action follows:
private FootballerDBContext db = new FootballerDBContext();
public ActionResult Index()
{
ViewBag.Message = DateTime.Now.ToString();
var model = from foot in db.Footballers
select foot;
return View(model.ToList());
}
In the above code, we have created an object of FootballerDBContext and we are querying the Footballers table using a LINQ Query under Index method. We are also passing the result of the Query to our view, so that we can display the data from the Footballers table in our Footballer’s Index view which is generated automatically with the MVC scaffolding system when the controller is created.
The Index.cshtml contents follow:
@model IEnumerable<DemoCachingData.Models.Footballer>
@{
ViewBag.Title = "Index";
}
<h2>Index</h2>
<b>Caching Time:</b>@ViewBag.Message
@*<p>
@Html.ActionLink("Create New", "Create")
</p>*@
<table class="table">
<tr>
<th>
@Html.DisplayNameFor(model => model.FirstName)
</th>
<th>
@Html.DisplayNameFor(model => model.LastName)
</th>
<th>
@Html.DisplayNameFor(model => model.Weight)
</th>
<th>
@Html.DisplayNameFor(model => model.Height)
</th>
<th>
@Html.DisplayNameFor(model => model.JoinedClub)
</th>
<th></th>
</tr>
@foreach (var item in Model) {
<tr>
<td>
@Html.DisplayFor(modelItem => item.FirstName)
</td>
<td>
@Html.DisplayFor(modelItem => item.LastName)
</td>
<td>
@Html.DisplayFor(modelItem => item.Weight)
</td>
<td>
@Html.DisplayFor(modelItem => item.Height)
</td>
<td>
@Html.DisplayFor(modelItem => item.JoinedClub)
</td>
@*<td>
@Html.ActionLink("Edit", "Edit", new { id=item.FootballerID }) |
@Html.ActionLink("Details", "Details", new { id=item.FootballerID }) |
@Html.ActionLink("Delete", "Delete", new { id=item.FootballerID })
</td>*@
</tr>
}
</table>
When we run for the first time the application the underlying database and table are created.Entity Framework Code First created the database for us. EF detected that the database connection string provided, pointing to a database didn’t exist, so Code First created the database automatically.
From VS you can show all files of the application. Under App_Data special folder you will see the FootBallersDBContext database.
Have a look at the picture below
13) Now let’s add some records in our Footballers table (I added two record straight from the Server Explorer UI) and then run the application. You will see the records displayed on our Index page.
For increasing the performance of our page, we will make use of OutputCache Action filter. As I mentioned earlier, this attribute can be applied either on an individual action method or on the entire controller.
Let’s apply this attribute on our Action Method Index which is available under FootballerController as shown below
[OutputCache(Duration = 60, VaryByParam = "none")]
public ActionResult Index()
{
ViewBag.Message = DateTime.Now.ToString();
var model = from foot in db.Footballers
select foot;
return View(model.ToList());
}
14) Now let’s run the page and check the output by refreshing it multiple times. The page should be cached for 1 minute.
In addition to duration, the output cache attribute supports a number of different settings like VaryByParam. The default setting for this is star or asterisk which means vary by every parameter possible and that is usually the setting that you want. That's the default because you normally do not want to return the same cached response for different parameters and by parameters think of things like query string parameters. A query string parameter can point to a record you wouldn't want to return the same cached response when someone is looking for record 1 and 2.
15) Now let’s test the VaryByParam attribute. Pass an ID parameter to the Index action methods and modify the code as shown below
[OutputCache(Duration = 60, VaryByParam = "ID")]
public ActionResult Index(int ID)
{
ViewBag.Message = DateTime.Now.ToString();
var model = from foot in db.Footballers
where foot.FootballerID == ID
select foot;
return View(model.ToList());
}
Now run the application again.This time pass an ID as a query string to the URL and see the output of your page. When you pass the different IDs with the URL, the caching will vary based on the IDs. For example –
http://localhost:61850/Footballer/Index/1 will be different than the http://localhost:61850/Footballer/Index/2
16) You can also set the cache location. The default value here is anywhere meaning it will cache on the server and the client can also cache the result.
You can be very specific and say that the result should only be cached on the server or on the client or on proxy servers in between.
If I want to cache the results on the server I need to modify the Action Filter of the Index method, like the following:
[OutputCache(Duration = 60, VaryByParam = "ID", Location=OutputCacheLocation.Server)]
The OutputCache attribute values can be: Any, Client,Downstream, Server, None, or ServerAndClient.
For configuring the cache location, you need to add the System.Web.UI namespace on your controller.
17) There's also a vary by header setting. That allows you to vary the cache based on a specific HTTP header like the language of the browser/client.
18) You can also make use of SqlDependency to invalidate the cache when the cached data gets changed on the Server side.
Finally I want to talk about Cache Profiles.
Cache Profiles are very helpful when it comes to pick the correct cache duration and other cache settings. You can create different cache profiles and use them accordingly to fit your caching strategy. ASP.net allows you to specify a cache profile in the output cache attribute.
The cache profile then is something that's stored in your web.config file and can specify a duration. It's underneath a section named caching and it's inside of here where you can put duration values.
You can have multiple cache profiles and you reference the cache profile by name in the output cache attribute.
It is recommended to use cache profiles instead of hard code the cache settings in the OutPutCache. We avoid repetition in the cache attributes.
If we want to double the duration inside of all of your cache attributes, we don't have to do a search and replace throughout the code. We just go to one location in your web.config file.
It's also easier to change the cache settings once the application is deployed because now all you do is edit the web.config file and then you avoid changing the settings in code, recompiling, and redeploying.
Inside the web.config file in the <system.web> section I can add my cache profile.
<system.web>
<caching>
<outputCacheSettings>
<outputCacheProfiles>
<add name="CacheMax" duration="3600" varyByParam="none"/>
<add name="CacheMin" duration="20" varyByParam="none"/>
</outputCacheProfiles>
</outputCacheSettings>
</caching>
....
</system.web>
Let’s apply the new CacheProfile (e.g CacheMax) on our Action Method Index which is available under FootballerController as shown below
[OutputCache(CacheProfile = "CacheMax", VaryByParam = "ID", Location = OutputCacheLocation.Server)]
Hope it helps.
In this post I am going to demonstrate with a hands-on example how to use the popular NoSQL database MongoDB to store and retrieve data using Visual Studio 2015and ASP.Net MVC.
When creating an application, any type of application (web, windows, distributed) all the data is stored and retrieved from a database. The most popular kind of databases that we are using to store and retrieve data are called Relational Databases and I have been using SQL Server as the RDBMS of choice for many years now. Data grows fast nowadays. We are having more applications than ever and databases grow rapidly.
That fact alone has some serious implications. There is more demand for scale and speed. When you design a web application, a commercial web application e.g an ecommerce store, you know that the speed and scale of the e-shop will mean more customer conversions for your client.
It is the demand for scale, speed, and fast application development that has brought on a new breed of databases, namely NoSQL databases. MongoDB is the fastest growing NoSQL databases out there.
If you need scalability and speed in an application, this NoSQL database really shines.
MongoDB is open source software. MongoDB is written in C++. It's available in 32 and 64-bit distributions. It is available in Linux 32-bit and the Windows 32-bit distributions. In a production environment you cannot use 32-bit distributions for obvious reasons. It is a document based database and has drivers for many programming languages.
You can find more (download it) in the website https://www.mongodb.com/.You can find whitepapers, webinars e.t.c. There are also online courses - https://university.mongodb.com/courses. The latest release is MongoDB 3.2.
Relational databases save data in tables and rows.Programmers develop applications using object oriented languages. The objects we use are simply not tables and rows. In order to overcome this impedance we write a mapping layer, or use an ORM (Entity Frameowork is the ORM I use) to translate between objects in memory and what is stored in the database.
So what is so different in MongoDB and in NoSQL dbs in general? Well, in MongoDB, there is no schema to define. There are no tables and no relationships. There are only documents. Every document you save in MongoDB can be as simple, or as complex as your application requires.
As a developer you do not have to explicitly create databases, tables and columns.
Documents can have embedded documents and collections. They can have any structure you can imagine, much like the objects you create when using object-oriented programming languages (https://docs.mongodb.org/ecosystem/drivers/ ). You do not need a separate table for Customer, their Orders and their Addresses. Everything is nested within the customer document itself. There are not foreign keys and you need not to worry about foreign keys and the integrity of relationships between tables.
You dot not model your code according to the data store requirements, design, limitations and nature. Instead of that the data store will fit your code and the application's requirements.
When developing modern web applications, you've likely used JSON. MongoDB uses BSON, very similar pattern to JSON, to store documents. So you will feel very familiar with MongoDD if you are familiar with JSON. More on BSON you can find in this site - http://bsonspec.org/ . You can start b y reading the FAQ - http://bsonspec.org/faq.html
MongoDB uses GridFS for storing, querying, and associating files with data in the application.
Looking into the DB-engine site we can see that MongoDB is the 4th most popular storage system, http://db-engines.com/en/ranking.
Let's move now to the hands-on example.
1) First, we need to install MongoDB. You can download it here (https://www.mongodb.org/downloads#production ) and you can see installation instructions for Windows here, https://docs.mongodb.org/master/tutorial/install-mongodb-on-windows/
2) I downloaded the mongodb-win32-x86_64-2008plus-ssl-3.0.7-signed.msi and installed MongoDB easily on my Windows 8.1 Enterprise edition. The D:\mongodb\bin folder contains all the .exe for the MongoDB to run properly. More specifically
Server - mongod.exe
Router - mongos.exe
Client - mongo.exe
3) I create a db folder in the path D:\data\db. MongoDb requires a data directory.
I start MongoDB server from the command prompt by changing to the installation directory and type D:\mongodb\bin\mongod.exe - The waiting for connections message in the console output indicates that the mongod.exe process is running successfully.
4) To connect to MongoDB through the mongo.exe shell, open another Command Prompt window, D:\mongodb\bin\mongo.exe.
I have started MongDB.
Have a look at the picture below
5) I am creating a database with the name "Football" by typing in the command prompt window
Use football
6) Now I need to populate, insert records in the database - have a look at this link - https://docs.mongodb.org/manual/tutorial/insert-documents/
As we said earlier MongoDB stores data in the form of documents, which are JSON-like field and value pairs.
In the Command Prompt window I type
var Name=["Messi","Ronaldo","Rooney","Torres","Ibrahimovic"];
for(i=0 ;i<5;i++){
db.Footballers.insert({Name : Name
});
}
Now we can see if the rows we inserted were in fact inserted in the MongoDB.
Type db.Footballers.find() in the command prompt window.
Have a look at the picture below (all the records have been inserted)
7) Now we need to create an ASP.Net MVC application and we will use this simple web application to connect to MongoDB and show these records in a web page.
8) I create a web application in VS (I choose an empty web application) and the name of the project is FootballMVCMongoDB.
9) After VS is creating the ASP.Net empty web application (with MVC in mind - Empty Controllers and Models folder) we need to install the C# MongoDB Driver. We can do that from Nuget. In the Package Manager Console I type "install-Package mongocsharpdriver". The driver is installed and the libraries are added to my project. Have a look at the picture below.
10) Ι am going to add my model class, Footballers.cs in the Models folder.
public class Footballers
{
public ObjectId Id { get; set; }
public string Name { get; set; }
}
11) I am going to create an empty HomeController.
I need to modify my controller to connect to the Football db to read the footballer names.
We need to create an instance of the MongoClient.
We add a constructor to this controller.
This is the code for the constructor
private MongoDatabase database;
public HomeController()
{
var connectionstring = "mongodb://localhost";
var client = new MongoClient(connectionstring);
var server = client.GetServer();
database = server.GetDatabase("Football");
}
Ι have the connection string, then I create an instance of a client to interact with the MongoDB server and then I access the MongoDB database.
12) I need to create Method which will return Json Type result.
public JsonResult GetAll()
{
var collections = database.GetCollection<Footballers>("Footballers");
IList<Footballers> footballers = new List<Footballers>();
var getFootballers = collections.FindAs(typeof(Footballers), Query.NE("Name", "null"));
foreach (Footballers footballer in getFootballers)
{
footballers.Add(footballer);
}
return Json(footballers, JsonRequestBehavior.AllowGet);
}
I create a collection (collections) and a List of footballers. Then I query the collection for documents that the name is not equal null. Then I populate my list of footballers and specify the return type as JSon.
Have a look here https://docs.mongodb.org/v3.0/reference/operator/query/ne/ .
13) Then I need to create a view to show the results. In the Views folder I add the folder Home. Then I add the Index.cshtml view inside the Home folder.
Inside the Index.cshtml view I type the following code
<script src="~/Script/jquery-2.1.1.min.js"></script>
<script src="~/Script/Football.js"></script>
<div>
<table id="fList">
<tr>
<th>Number </th>
<th>Name</th>
</tr>
</table>
</div>
Then I need to call my server side method that returns JSON from the client. I will use JQuery and I will insert the code in the Football.js in the Script folder in my solution. Inside the Football.js i type the following code.
$(document).ready(function () {
GetAll();
});
function GetAll()
{
$.ajax({
type: 'GET',
contentType: 'application/json; charset=utf-8',
url: 'Home/GetAll',
success: function (data) {
var newHtml = "";
$.each(data, function (index, value) {
newHtml += "<tr><td>"+(index+1)+"</td><td>" + value.Name +"</td></tr>";
});
$('#fList').append(newHtml);
},
error: function (data) {
alert('Error in getting result');
}
});
}
I just create a method and then call the GetAll method (server-side), get the JSon data back,parse it and append it in a table html element.
14) Build and run your application. The names of the footballers will show up in the screen. Have a look at the picture below.
We have created our first application where we read data from a MongoDB database using ASP.Net MVC. We also downloaded, installed and started MongoDB and inserted records in it. We also talked about the importance and use of NoSQL databases.
You can submit questions or search through answers in the StackOverflow section for MongoDB - http://stackoverflow.com/questions/tagged/mongodb
Hope it helps.
In this post I am going to provide you with a hands-on example on how to take advantage of asynchronous execution using Entity Framework. EF 6 has the ability to execute a query and command asynchronously using DbContext.
Entity Framework is an object-relational mapping (ORM) framework for the .NET Framework.EF addresses the problem of Object-relational impedance mismatch. I will not be talking about that mismatch because it is well documented in many sites on the Internet.
Through that framework we can program against a conceptual application model instead of programming directly against a relational schema-model. By doing so we can decrease the amount of code we do write to access a data storage and thus decrease maintenance time. You can find many posts regarding Entity Framework in this blog.
Before I go on with my hands-on example I would like to talk about asynchronous programming.
Asynchronous programing is everywhere these days. It doesn't matter if you're on the client or the server. On the client, you want asynchronous operations to take place because you want to keep the UI thread free. You don't want it to block on IO operations because then the UI freezes and it doesn't responds to the user's mouse clicks. This is why the A in Ajax stands for asynchronous and it's partially why Ajax makes browser applications more responsive. It keeps that UI thread free.
On the server, we also want async operations because that keeps the request processing thread free from blocking on IO operations that keeps them busy in executing and servicing network request.
There's such a demand for async programming these days that Microsoft introduced new keywords into the C# language to make async programming simple. The keywords are async and await.
I would like to continue on asynchronous programming and explain what is thread processing on the application server (IIS) and how the .Net Framework maintains threads to service requests.
If you need to build a highly scalable web application then one of your goals is to keep the threads in server process as busy as possible.
In an environment like ASP.NET, there are only a limited number of threads reserved for processing HTTP requests.
If these threads have to wait for a long input output operation to complete, you end up with those threads sitting idle and doing no work.
A classic example of this is a controller action (in ASP.Net MVC) that needs to call another web service, an IO operation to produce the result.
If that request that travels over the network, and we often measure that in milliseconds,let's say that it takes 250 milliseconds, then the processing thread has to sit idle for 250 milliseconds. If all the threads are tied up and idle,any new HTTP request that are arriving at the server, ASP.NET will queue them up and force them to wait for one of this request processing threads to free out.
We can free our threads out from running long input output operations and using asynchronous behavior on the server. This has nothing to do with Ajax or asynchronous calls on the client. People often confuse Ajax partial updates with asynchronous programing on the server.
On the web server, the .NET Framework maintains a pool of threads that are used to service ASP.NET requests. When a request arrives, a thread from the pool is dispatched to process that request.
If the request is processed synchronously, the thread that processes the request is busy while the request is being processed, thus the thread cannot service another request.
This might not be a problem, because the thread pool can be made large enough to accommodate many busy threads. However, the number of threads in the thread pool is limited (the default maximum for .NET 4.5 is 5,000). In large applications with high concurrency of long-running requests, all available threads might be busy. This condition is known as thread starvation. When this condition is reached, the web server queues requests. If the request queue becomes full, the web server rejects requests with an HTTP 503 status (Server Too Busy).
Another very important point that I want to make is that asynchrnous does not mean parallel, it simply means that the IIS web server can handle more concurrent requests. If you have a database that is the bottleneck asynchronous programming will not solve that problem. You should tackle the issues that make database performance to be the main problem in the application lifecycle (poor indexes, badly designed queries, low memory).Basically, the rule of thumb is to use an async task when your operation is accessing a slow medium like the network, disk, or database.
You may want to have a look in this article as well.
I will build a small ASP.Net application that will fetch data from a database using the EF 6.0 (database first) as my data access layer and writing LINQ to Entities queries against the entity data model.
Building a web application using non blocking calls to the data layer is a great way to increase the scalability of your system. When performing a task asynchronously we free up the worker thread to accept another request while work is being done in the background. Entity Framework 6.0 has support for Async/Await pattern (e.g ToListAsync, FirstAsync).
1) Create an empty ASP.Net Web Application (Empty MVC Application) and give it the name EF6AspNetAsynchronous. I am using Visual Studio 2015 Enterprise edition.
2) I will use the AdventureWorks2014 database (You can download it here) for this application and more specifically the Person.CountryRegion table. I have installed SQL Server 2014 Enterprise edition in my machine. SQL Express edition will work fine.
3) I will add an ADO.Net Entity data model (inside the Models folder) using Database First. Follow the wizzard steps, create the connection string and then import into the conceptual model the Person.CountryRegion table which will become an entity in the domain model. If you want to look at those detailed steps if you are new to EF and Database First have a look at this post.
4) I will query the database using synchronous methods first. Then I will change the code in my EF 6.0 data access layer and in the controller to leverage async support in ASP.Net MVC and EF 6.0.
5) Add a new item, a class file in the Models folder. The name of the class is DBHelper.cs
public static class DBHelper
{
public static List<CountryRegion> SelectAll()
{
AdventureWorks2014Entities ctx = new AdventureWorks2014Entities();
var query = from c in ctx.CountryRegions
orderby c.Name ascending
select c;
return query.ToList();
}
}
6) Add an empty Controller with the name CountryController.cs
The code for the CountryController controller follows
public class CountryController : Controller
{
// GET: Country
private AdventureWorks2014Entities db = new AdventureWorks2014Entities();
public ActionResult Index()
{
List<CountryRegion> model = DBHelper.SelectAll();
return View(model);
}
}
We also need to reference our model
using EF6AspNetAsynchronous.Models;
7) Add an empty View in the Views Folder. First create a new Country folder inside the Views folder.
The code for the View follows.
@model IEnumerable<EF6AspNetAsynchronous.Models.CountryRegion>
@{
ViewBag.Title = "CountryRegion";
}
<h2>Index</h2>
<table class="table">
<tr>
<th>
@Html.DisplayNameFor(model => model.CountryRegionCode)
</th>
<th>
@Html.DisplayNameFor(model => model.Name)
</th>
<th>
@Html.DisplayNameFor(model => model.ModifiedDate)
</th>
<th></th>
</tr>
@foreach (var item in Model)
{
<tr>
<td>
@Html.DisplayFor(modelItem => item.CountryRegionCode)
</td>
<td>
@Html.DisplayFor(modelItem => item.Name)
</td>
<td>
@Html.DisplayFor(modelItem => item.ModifiedDate)
</td>
</tr>
}
</table>
8) Build and run your application. All the information is displayed in the page. Have a look at the picture below.
9) Let me show you how to create the asynchronous version of this simple web application.
In the DBHelper.cs class change the code to the code below
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Threading.Tasks;
namespace EF6AspNetAsynchronous.Models
{
public static class DBHelper
{
public async static Task<List<CountryRegion>> SelectAllAsync()
{
AdventureWorks2014Entities ctx = new AdventureWorks2014Entities();
var query = from c in ctx.CountryRegions
orderby c.Name ascending
select c;
return await query.ToListAsync();
}
}
}
I changed the name of the method to SelectAllAsync.The method SelectAllAsync() method is denoted with async keyword.The method returns a Task of a List of CountryRegion entities. The return keyword is followed by the await keyword.
I do not call the ToList() method the return statement calls the ToListAsync() method.The ToListAsync() is the asynchronous method that EF 6.0 supports.
10) Now let's make the necessary changes to the Controller
public class CountryController : Controller
{
// GET: Country
private AdventureWorks2014Entities db = new AdventureWorks2014Entities();
public async Task<ActionResult> Index()
{
List<CountryRegion> model = await DBHelper.SelectAllAsync();
return View(model);
}
}
}
We also need to reference the using System.Threading.Tasks; namespace.
The Index() method itself is marked with an async keyword and it returns a Task object wrapping the ActionResult.
I make a call to the SelectAllAsync() method of the DBHelper class. This time SelectAllAsync() is called with an await keyword and returns a List of CountryRegion objects.Build and run your application again. You will get the same results.
We looked into a detailed example on how to select data from a data table in a datastore, SQL Server, through EF Database first leveraging the EF 6.0 support for asynchronous operations and the Async/Await pattern of ASP.Net 4.5.
Hope it helps!!!
This is the second post in a series of posts where I talk about good coding practices when it comes to using Entity Framework as our data access layer when building our applications.
You can read the first post of the series here. The main things to take away from that first post is to use projection whenever possible (do not query sql server for data that you do not want on the client) and to filter on the server (meaning SQL Server) which has a powerful engine to do that and not on the client.
In this post I am going to provide you with a hands-on example on how to avoid writing your LINQ to Entities queries in a way that will hinder performance of your application. The aim of this post (hopefully a series of posts on performance and Entity Framework) is to highlight bad coding practices when architecting an applications that uses EF as the ORM to fetch and manipulate data from the data store. I am going to point out some practises and patterns that very often developers use and cause EF to create poor-performing T-SQL statements.
First, a quick word on what Entity Framework is. Entity Framework is an object-relational mapping (ORM) framework for the .NET Framework.EF addresses the problem of Object-relational impedance mismatch. I will not be talking about that mismatch because it is well documented in many sites on the Internet.
Through that framework we can program against a conceptual application model instead of programming directly against a relational schema-model. By doing so we can decrease the amount of code we do write to access a data storage and thus decrease maintenance time. You can find many posts regarding Entity Framework in this blog.
A lot of people wonder why we should use Entity Framework in the first place. We could still keep using good old T-SQL in our applications.
The obvious answer is that EF addresses the Object Relation impedance mismatch and it bridges those two different worlds. Entity Framework creates an object oriented model for accessing the data tier. In an object oriented development environment, it makes working with the data tier much more seamless for the developer. It allows developers to spend time writing code for their application rather than dealing with the tedious tasks of opening connections to the database e.t.c. The abstraction that is offered by EF by generating the intermediate code, which in this case is T-SQL, it's much easier to migrate code to another platform, such as Oracle or Postgres or some other ODBC source. We get incredible flexibility by doing this.
Using EF does not mean we should forget about SQL Server, T-SQL, relationships, foreign keys and performance. We should keep in mind that SQL Server is based on set theory and relational algebra and it thrives when acting on sets of data, updating a set of rows rather than each row of data at the time.
1) Create an empty ASP.Net Application (Web Forms Application) and give it the name EFoptimisation2. I am using Visual Studio 2013 Ultimate edition.
2) Add a new web forms page in the application. Leave the default name. The application I am going to build is very simple web forms application. The user will enter a last name and will get back the first name(s) for that last name.
3) I will use the AdventureWorks2014 database (You can download it here) for this application and more specifically the Person.Person table. I have installed SQL Server 2014 Enterprise edition in my machine.
4) I will add an ADO.Net Entity data model using Database First paradigm. Follow the wizzard steps, create the connection string and then import into the conceptual model only the Person and EmailAddress tables which will become an entities in the domain model. If you want to look at those detailed steps if you are new to EF and Database First have a look at this post.
5) Add a textbox and a button to the page. The user will enter the first name in the textbox and will hit enter and then the results (the email addresses for that first name) will be printed on the page.We will navigate to the EmailAddress entity throug the navigation property EmailAddresses
This is the code inside the Page_Load event handling routine.
protected void Page_Load(object sender, EventArgs e)
{
using (var ctx = new AdventureWorks2014Entities())
{
string FirstName = TextBox1.Text;
var query = from p in ctx.People
where p.FirstName.Equals(FirstName)
select p;
foreach (var person in query)
{
foreach (var email in person.EmailAddresses)
{
Response.Write(email.EmailAddress1);
Response.Write("<br/>");
}
}
}
}
The code above is pretty straight forward.
6) Now we are ready to run the application. Before we do that I will launch SSMS and connect to my local instance of SQL Server. Then I will also launch the SQL Profiler and create a new trace. The trace will listen only for the RPC:Completed event. I activate te trace so the trace is running.
7) I build and run my web app. The results I get back when typing "Alex" as first name is 51 email addresses.
8) Let me see what the trace recorded in my SQL Profiler and the T-SQL that was generated.
We have an individual statement for every email address that we retrieved. This is a not a set based operation since we issue many transactions to the SQL Server.
9) Now we will rewrite our code above in order for EF to work better with the SQL Engine. I am going to use the "Include" method in my code.
We do inform Entity Framework that not only we want all the columns from the People object specified in the from clause, but also want all those columns in the path specified as a parameter of the include method-- EmailAddress in our scenario.
protected void Page_Load(object sender, EventArgs e)
{
using (var ctx = new AdventureWorks2014Entities())
{
string FirstName = TextBox1.Text;
var query = from p in ctx.People.Include("EmailAddresses")
where p.FirstName.Equals(FirstName)
select p;
foreach (var person in query)
{
foreach (var email in person.EmailAddresses)
{
Response.Write(email.EmailAddress1);
Response.Write("<br/>");
}
}
}
}
The Profiler is still running on the background.
I build and run my web app. The results I get back when typing "Alex" as first name is 51 email addresses.
This is what I get back from the Profiler.
Now as you notice it's a pretty extensive query but there is a problem with this approach.We are not allowed to use projection. We can only use the columns of the People object.
10) We need to rewrite our code again. I will use explicit joins this time.The code follows.
protected void Page_Load(object sender, EventArgs e)
{
using (var ctx = new AdventureWorks2014Entities())
{
string FirstName = TextBox1.Text;
var query = from p in ctx.People
join email in ctx.EmailAddresses
on p.BusinessEntityID equals email.BusinessEntityID
where p.FirstName.Equals(FirstName)
select new { email.EmailAddress1};
foreach (var item in query)
{
Response.Write(item.EmailAddress1);
Response.Write("<br/>");
}
}
}
The Profiler is still running on the background.
I build and run my web app. The results I get back when typing "Alex" as first name is 51 email addresses.
This is what I get back from the Profiler.
As we can see this is a T-SQL statement that we could type in an SSMS Query window. We have one query that results in one set based operation thus improving greatly the performance of our application by getting rid off the unecessary round trips.
11) We could rewrite the code above in a more object oriented way using lambda expressions.
protected void Page_Load(object sender, EventArgs e)
{
using (var ctx = new AdventureWorks2014Entities())
{
string FirstName = TextBox1.Text;
var query = ctx.People
.Where(p => p.FirstName.Equals(FirstName))
.SelectMany(email => email.EmailAddresses)
.Select(theemail => theemail.EmailAddress1);
foreach (var item in query)
{
Response.Write(item);
Response.Write("<br/>");
}
}
}
The Profiler is still running on the background.
I build and run my web app. The results I get back when typing "Alex" as first name is 51 email addresses.
This is the T-SQL statement I got back from the Profiler. This is what it was executed against the database.
EXEC sp_executesql N'SELECT
[Extent2].[EmailAddress] AS [EmailAddress]
FROM [Person].[Person] AS [Extent1]
INNER JOIN [Person].[EmailAddress] AS [Extent2] ON [Extent1].[BusinessEntityID] = [Extent2].[BusinessEntityID]
WHERE [Extent1].[FirstName] = @p__linq__0', N'@p__linq__0 nvarchar(4000)',
@p__linq__0 = N'Alex'
If we typed that query ourselves in an SSMS query window we would type something like this:
SELECT Person.EmailAddress.EmailAddress
FROM Person.EmailAddress
INNER JOIN Person.Person ON Person.EmailAddress.BusinessEntityID = Person.Person.BusinessEntityID
WHERE Person.Person.FirstName = 'Alex'
As you can see those two queries are pretty much the same.
Entity framework abstracts the T-SQL creation from us, the developers. Having said that we are still in charge of the overall performance of our application. Performance plays always a big role in any application. We do know that SQL Server thrives on set based operations.We should write our Linq to Entities queries in a way that set based T-SQL statements are generated.
Hope it helps!!!
In this post I am going to provide you with a hands-on example on how to avoid writing your LINQ to Entities queries in a way that will hinder performance of your application. The aim of this post (hopefully a series of posts on performance and Entity Framework) is to highlight bad coding practices when architecting an applications that uses EF as the ORM to fetch and manipulate data from the data store. I am going to point out some practises and patterns that very often developers use and cause EF to create poor-performing T-SQL statements.
Entity Framework will always create T-SQL, the thing to keep in mind is that we have to make sure that this T-SQL code (that we cannot write ourselves since it is abstracted by EF) if it is poor then when passed to the SQL Server engine (through the optimiser and the creation of the execution plan) will cause our applications to perform poorly especially under heavy load.
Let me talk a bit about query optimisation and T-SQL. T-SQL is declarative by nature. When we write T-SQL statements in a query window in SSMS and execute them,we just say to SQL Server “I want these results back”. We do not provide any details on how the results will be returned.If there was nothing else between our T-SQL code and the SQL Server Database engine, we simply would not get any results back.Luckily for us there is a very important component, the Query Optimizer that generates an imperative plan. By saying imperative I mean detailed. This plan that is called execution plan is what is actually executed by the relational engine.The query optimiser will not look for a perfect plan.It is a cost-based optimiser that must find an efficient plan.The optimiser when deciding upon the execution plan will take in to consideration the type of operations,statistics (must always be up to date),indexes,hardware resources (number of CPUs ,available memory),SQL Server edition,number of active concurrent connections and query hints. If the T-SQL that is generated by the EF is pooly written then the optimiser will not create an optimal plan hence the problems in performance.
Entity Framework is an object-relational mapping (ORM) framework for the .NET Framework.EF addresses the problem of Object-relational impedance mismatch. I will not be talking about that mismatch because it is well documented in many sites on the Internet.
Through that framework we can program against a conceptual application model instead of programming directly against a relational schema-model. By doing so we can decrease the amount of code we do write to access a data storage and thus decrease maintenance time. You can find many posts regarding Entity Framework in this blog.
1) Create an empty ASP.Net Application (Web Forms Application) and give it the name EFoptimisation. I am using Visual Studio 2013 Ultimate edition.
2) Add a new web forms page in the application. Leave the default name. The application I am going to build is very simple web forms application. The user will enter a last name and will get back the first name(s) for that last name.
3) I will use the AdventureWorks2014 database (You can download it here) for this application and more specifically the Person.Person table. I have installed SQL Server 2014 Enterprise edition in my machine.
4) I will add an ADO.Net Entity data model using Database First paradigm. Follow the wizzard steps, create the connection string and then import into the conceptual model only the Person.Person table which will become an entity in the domain model. If you want to look at those detailed steps if you are new to EF and Database First have a look at this post.
5) Add a textbox and a button to the page. The user will enter the last name in the textbox and will hit enter and then the results will be printed on the page.
This is the code inside the Page_Load event handling routine.
protected void Page_Load(object sender, EventArgs e)
{
using( var ctx = new AdventureWorks2014Entities())
{
string LastName = TextBox1.Text;
var query = from person in ctx.People
select person;
foreach (var p in query)
{
if (p.LastName==LastName)
{
Response.Write(p.FirstName);
Response.Write("<br/>");
}
}
}
}
The code above is pretty straight forward.
6) Now we are ready to run the application. Before we do that I will launch SSMS and connect to my local instance of SQL Server. Then I will also launch the SQL Profiler and create a new trace. The trace will listen only for the SQL:BatchStarting event. I activate te trace so the trace is running.
7) I build and run my web app. The results I get back when typing "Yuan" as last name is 92 first names.
8) Let me see what the trace recorded in my SQL Profiler and the T-SQL that was generated.
9) If I copy and paste the T-SQL in my SSSM and execute the query (Enable Actual Execution Plan and Client Statistics) I will get the following results - 19972 rows in total. Ηave a look at the piscture below
Now let's have a look at the execution plan created. Have a look at the picture below.
We have a Clustered Index Scan.The Clustered Index Scan means that SQL Server started at the very top of the table and scanned every row until it reached the bottom of the table. Not a very good scenario to have in terms of performance. Now lets have at the Client Statistics tab. Have a look at the picture below.
As you can see the bytes transfered from SQL Server is almost 26 mbytes.That is a huge amount of data to be transfered through the network back to the client to satisfy a single query.
10) We need to refactor our code in order to create more efficient T-SQL code.
protected void Page_Load(object sender, EventArgs e)
{
using( var ctx = new AdventureWorks2014Entities())
{
string LastName = TextBox1.Text;
var query = from person in ctx.People
where person.LastName.Equals(LastName)
select person;
foreach (var p in query)
{
Response.Write(p.FirstName);
Response.Write("<br/>");
}
}
}
As you can see from the code above I am doing now the filtering on the server.
Have a look below to see what the Profiler's trace output was.
The T-SQL now has a Where clause.It is a parametirised query.If I place this query in my SSMS and execute it I will get back 92 rows only and my execution plain will look like this
This is a by far more optimal executon plan(Index Seek & Lookup) that the Clustered Index Seek.
If I look at the Client Statistics tab (Bytes received from the server), I have only 145Kbytes of data compared with the 26Mbytes I had previously.
11) Now we can use projection to retrieve only the columns that we are interested in (FirstName) and get rid of the other ones.
I go back to the Page_Load routine and instead of
var query = from person in ctx.People
where person.LastName.Equals(LastName)
select person;
I rewrite my code to utilize projection
var query = from person in ctx.People
where person.LastName.Equals(LastName)
select new { person.FirstName };
Then I build and run the application with the Profiler running.
This is the T-SQL from the Profiler when I type "Yuan" in the textbox.
exec sp_executesql N'SELECT
1 AS [C1],
[Extent1].[FirstName] AS [FirstName]
FROM [Person].[Person] AS [Extent1]
WHERE [Extent1].[LastName] = @p__linq__0',N'@p__linq__0 nvarchar(4000)',@p__linq__0=N'Yuan'
As you can see from the T-SQL above I get only the FirstName column. If I place the T-SQL code in an SSMS query window (Enable Client Statistics & Actual Execution plan) I get back 92 rows of data and the following picture shows the actual exection plan. Now we see a Non Clustered Index Seek. We are seeking and not scanning which is a great thing.
Let's have a look at the client statistics.As you can see from the picture below the bytes received from the server is just 9183 bytes , 9 Kbytes. This is huge reduction compared to the 145 Kbytes and 26 Mbytes.
To recap, make sure that you do all the filtering on SQL Server and use projection when you do not want all the table columns in your application, when writing apps that use EF as the data access layer.It is always wrong to filter on the client when SQL Server has all the power to do that for us.
Hope it helps!!!
In this post I am going to provide you with a hands-on example on how to use second level caching with Entity Framework 6.0 version. Second Level Caching is one of the features that is not present out-of-the-box in Entity Framework.
Second level cache is a query cache. The results of SQL commands are stored in the cache, so that the same SQL commands retrieve their data from the Cache rather than executing the query again against the underlying provider. This can have a performance boost for your application and results in less activity against your database, at the cost of increased memory.
There is currently a project in codeplex, EntityFramework.Cache that enables us caching of query results for EF 6.1 applications.
Entity Framework is an object-relational mapping (ORM) framework for the .NET Framework.EF addresses the problem of Object-relational impedance mismatch. I will not be talking about that mismatch because it is well documented in many sites on the Internet.
Through that framework we can program against a conceptual application model instead of programming directly against a relational schema-model. By doing so we can decrease the amount of code we do write to access a data storage and thus decrease maintenance time. You can find many posts regarding Entity Framework in this blog.
1) I will create an empty ASP.Net Application (Web Forms Application) and I give it the name EFSecondLevelCaching. I am using Visual Studio 2013 Ultimate edition.
2) I add a new web forms page in the application. Leave the default name. Αdd a Gridview control to the web form.
3) I will use the AdventureWorks2014 database (You can download it here) for this application and more specifically the Person.Person table. I have installed SQL Server 2014 Enterprise edition in my machine. SQL Express edition will work fine.
4) I will add a class library project to the solution, namely DataAccess. I will add an ADO.Net Entity data model using Database First to the class library project. Follow the wizzard steps, create the connection string and then import into the conceptual model the Person.Person table which will become an entity in the domain model.
In the am going to install the “EntityFramework.Cache” from the Nuget Library in this project.
5) I will write a sql statement to acquire rows from the Person.Person table. Connect to your SQL Server instance. Type the following query.
SELECT *
FROM [AdventureWorks2014].[Person].[Person]
WHERE [FirstName] = 'Rob'
This query after execution will return 5 rows.
6) In the webform1.aspx.cs file ( public partial class WebForm1) I will create a new method that returns the persons with FirstName = Rob
private static List<Person> SelectAll()
{
AdventureWorks2014Entities ctx = new AdventureWorks2014Entities();
var query = from p in ctx.People
where p.FirstName == "Rob"
orderby p.BusinessEntityID ascending
select p;
return query.ToList();
}
Ι just return all the persons with the first name Rob from the database.
7) In our webform in the Page_Load event we write the following code.
protected void Page_Load(object sender, EventArgs e)
{
GridView1.DataSource = SelectAll();
GridView1.DataBind();
Response.Write("</hr>");
}
Make sure you build your application but do not run it just yet. Make sure that you copy the connection string from the App.config to the Web.config
<connectionStrings>
<add name="AdventureWorks2014Entities" connectionString="metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;provider=System.Data.SqlClient;provider connection string="data source=.;initial catalog=AdventureWorks2014;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework"" providerName="System.Data.EntityClient" />
</connectionStrings>
8) I will use the Sql Server Profiler to see what queries are hitting my database. I launch the SQL Server Profiler, I connect to the SQL Server Enterprise edition instance in my machine and then create a trace with the SQL:BatchCompleted, SQL:BatchStarting event and a filter in the DatabaseName column with the name AdventureWorks2014.
Build and run the application. You will see that the first time the page loads the application hits the database. If you refresh the web page the database is hit every time.
Have a look at the picture below.
9) In the Person.cs class file add the code below
public class Configuration : DbConfiguration
{
public Configuration()
{
var transactionHandler = new CacheTransactionHandler(new InMemoryCache());
AddInterceptor(transactionHandler);
var cachingPolicy = new CachingPolicy();
Loaded +=
(sender, args) => args.ReplaceService<DbProviderServices>(
(s, _) => new CachingProviderServices(s, transactionHandler,
cachingPolicy));
}
}
As I said earlier I have already installed the EF Second level cache package ,“EntityFramework.Cache” , from Nuget Library.
Now we need to tell EF to use caching by configuring the caching provider and the transaction handler.
That is why we have created the DbConfiguration class. In the constructor of your DbConfiguration derived class you need to set the transaction interceptor and the Loaded event handler which will be responsible for replacing the provider.
Have a look at the picture below
Build and run your application. The first time the page loads the database is it. When I reload the page the database is not hit again and the queries are served by the cache.
Have a look at the picture below
Hope it helps!!!
In this post I am going to provide you with a hands-on example on how to log commands & queries sent to the database by Entity Framework 6.0.
You can use the fantastic Entity Framework third party profiler by Hibernating Rhinos if you want. You can find a similar post here.
Entity Framework is an object-relational mapping (ORM) framework for the .NET Framework.EF addresses the problem of Object-relational impedance mismatch. I will not be talking about that mismatch because it is well documented in many sites on the Internet.
Through that framework we can program against a conceptual application model instead of programming directly against a relational schema-model. By doing so we can decrease the amount of code we do write to access a data storage and thus decrease maintenance time. You can find many posts regarding Entity Framework in this blog.
1) Create an empty ASP.Net Application (Web Forms Application) and give it the name EFLogging. I am using Visual Studio 2013 Ultimate edition.
2) Add a new web forms page in the application. Leave the default name.
3) I will use the AdventureWorks2014 database (You can download it here) for this application and more specifically the Person.Person table. I have installed SQL Server 2014 Enterprise edition in my machine. SQL Express edition will work fine.
4) I will add an ADO.Net Entity data model using Database First. Follow the wizzard steps, create the connection string and then import into the conceptual model the Person.Person table which will become an entity in the domain model.
5) I will write a sql statement to acquire rows from the Person.Person table. Connect to your SQL Server instance. Type the following query.
This query after execution will return 5 rows.
SELECT [FirstName]
FROM [AdventureWorks2014].[Person].[Person]
WHERE FirstName = 'Rob'
6) In our webform in the Page_Load event we write the following code. We change the name Rob to Robby.
protected void Page_Load(object sender, EventArgs e)
{
using (var ctx = new AdventureWorks2014Entities())
{
ctx.Database.Log = T => Debug.Write(T);
var query = from p in ctx.People
where p.FirstName=="Rob"
select p;
foreach (var item in query)
{
item.FirstName = "Robby";
}
ctx.SaveChanges();
}
}
7) You can see in the output that it logs all the activities performed by EF e.g. opening & closing connection, execution & completion time and database queries & commands.
Have a look at the picture below
Hope it helps!!!
In this post I am going to provide you with a hands-on example on how to handle efficiently batch operations with Entity Framework and the EF Extended library.
You can find another similar post where I demonstrate how to handle batch opration with Entity Framework and the EF Utilities library.
Entity Framework is an object-relational mapping (ORM) framework for the .NET Framework.EF addresses the problem of Object-relational impedance mismatch. I will not be talking about that mismatch because it is well documented in many sites on the Internet.
Through that framework we can program against a conceptual application model instead of programming directly against a relational schema-model. By doing so we can decrease the amount of code we do write to access a data storage and thus decrease maintenance time. You can find many posts regarding Entity Framework in this blog.
As stated earlier it is easy to implement basic CRUD (create, read, update, delete) functionality using EF. There are performances issues when performing batch operations on thousand of domain objects (records).
In this post I will do a batch update on a table with traditional EF techniques and then use another library called EF Extended. The performance can be significantly improved with EFExtended batch CUD (create, update, delete) operations.
1) I will create an empty ASP.Net Application (Web Forms Application) and I give it the name EFExtended. I am using Visual Studio 2013 Ultimate edition.
2) I add a new web forms page in the application. Leave the default name.
3) I will use the AdventureWorks2014 database (You can download it here) for this application and more specifically the Person.Person table. I have installed SQL Server 2014 Enterprise edition in my machine. SQL Express edition will work fine.
4) I will add an ADO.Net Entity data model using Database First. Follow the wizzard steps, create the connection string and then import into the conceptual model the Person.Person table which will become an entity in the domain model.
Have a look at the picture below to see what you must have in Visual Studio.
5) I will write a sql statement to acquire rows from the Person.Person table. Connect to your SQL Server instance. Type the following query.
SELECT [EmailPromotion]
FROM [AdventureWorks2014].[Person].[Person]
WHERE EmailPromotion = 0
This query after execution will return 11158 rows. We wil create a Batch update operation through our ASP.Net web forms application. We will also measure the performance of this batch operation. We will create a batch operation from our application that will affect all the rows (11158). If we did the batch update from T-SQL we would use the following statement.
UPDATE [AdventureWorks2014].[Person].[Person]
SET EmailPromotion = 10
WHERE EmailPromotion =0
6) In our webform in the Page_Load event we write the following code. We add 10 to every row in the Persons entity to the EmailPromotion column.
protected void Page_Load(object sender, EventArgs e)
{
using (AdventureWorks2014Entities ctx = new AdventureWorks2014Entities())
{
IQueryable<Person> persons = ctx.People.Where(x => x.EmailPromotion == 0);
foreach (Person person in persons)
{
person.EmailPromotion = person.EmailPromotion + 10;
}
ctx.SaveChanges();
}
}
Make sure you build your application but do not run it just yet.
7) I will use the Entity Framework Profiler to gain valuable insight in the batch operation and record metrics regarding the performance. You can download it here -http://www.hibernatingrhinos.com/products/efprof. You can acquire a trial license very easily. Make sure you download and install this fantastic EF profiler.
Inside the folder where you downloaded the Entity Framework Profiler locate the HibernatingRhinos.Profiler.Appender DLL.
We will add a reference to this DLL to our web application.Make sure you do that.
In the Global.asax file we need to add the following code to the Application_Start() event handling routine.
HibernatingRhinos.Profiler.Appender.EntityFramework.EntityFrameworkProfiler.Initialize();
Launch the EF Profiler and then build and run the application. The application will update the 11158 rows.
The results I got for my machine can be seen in the pictures below. You can see that the database has been hit 11158 times and the average duration for each one is 7ms.
You can also see the total duration in seconds that the whole batch operation lasted. we needed 94 seconds for updating 11158 rows. As you can understand this is not acceptible and we need to find a more efficient way to do the batch update.
8) Now we must install the EFExtended library. We will do that through the Nuget Package Manager.Launch Nuget from VS and install EFExtended library. Have a look at the picture below
Now you must add a reference to using EntityFramework.Extensions library to your application. Inside the WebForm1.aspx.cs add the following
using EntityFramework.Extensions;
9) Comment out everything in the Global.asax file in Application_Start() event handling routine.
Comment out everything in the webform Page_Load event handling routine. Type only the following inside the Page_Load event handling routine.
protected void Page_Load(object sender, EventArgs e)
{
using (AdventureWorks2014Entities ctx = new AdventureWorks2014Entities())
{
//we find the rows with EmailPromotion =10 (11158) rows and add 10 more to each row
ctx.People.Where(x => x.EmailPromotion == 10).Update(x => new Person() { EmailPromotion = 20 });
}
}
10) I will use the Sql Server Profiler to get the metrics regarding the batch operation using the EntityFramework Extended library. I launch the SQL Server Profiler, I connect to the SQL Server Enterprise edition instance in my machine and then create a trace with the RPC:Completed event and a filter in the Database column.
Have a look at the picture below
11) Build and run your application and see the SQL Server Profiler trace. The duration column indicates that the batch operation needed only 495 ms to update 11158 rows.
Have a look at picture below
The results are amazing and the reason for such vast performance increase is because in traditional EF a separate UPDATE SQL statement runs for each row in the table, while the batch update operation provided by EF Extended library runs one UPDATE BULK SQL statement. Inserts and deletes also work directly against the database.
My laptop is a Toshiba Qosmio Intel(R) Core(TM) i7-4700 MQ CPU @ 2.40GHz with 32Gbytes RAM with a 256 SSD drive.
Batch Update Operation with EF (11158 rows affected): 94 seconds
Batch Update Operation with EF Extended Library (11158 rows affected): 495 ms - that means it is 190 times faster
Hope it helps!!!
In this post I am going to provide you with a hands-on example on how to handle efficiently batch operations with Entity Framework and the EF Utilities library.
Entity Framework is an object-relational mapping (ORM) framework for the .NET Framework.EF addresses the problem of Object-relational impedance mismatch. I will not be talking about that mismatch because it is well documented in many sites on the Internet.
Through that framework we can program against a conceptual application model instead of programming directly against a relational schema-model. By doing so we can decrease the amount of code we do write to access a data storage and thus decrease maintenance time. You can find many posts regarding Entity Framework in this blog.
As stated earlier it is easy to implement basic CRUD (create, read, update, delete) functionality using EF. There are performances issues when performing batch operations on thousand of domain objects (records).
In this post I will do a batch update on a table with traditional EF techniques and then use another library called EF Utilities. The performance can be significantly improved with EF Utilities batch CUD (create, update, delete) operations.
EF Utilities(EntityFramework.Utilities) is created by Mikal Eliasson (http://mikee.se/ - @MikaelEliasson).
1) I will create an empty ASP.Net Application (Web Forms Application) and I give it the name EFUtilitiesUpdate. I am using Visual Studio 2013 Ultimate edition.
2) I add a new web forms page in the application. Leave the default name.
3) I will use the AdventureWorks2014 database (You can download it here) for this application and more specifically the Person.Person table. I have installed SQL Server 2014 Enterprise edition in my machine. SQL Express edition will work fine.
4) I will add an ADO.Net Entity data model using Database First. Follow the wizzard steps, create the connection string and then import into the conceptual model the Person.Person table which will become an entity in the domain model.
Have a look at the picture below to see what you must have in Visual Studio.
5) I will write a sql statement to acquire rows from the Person.Person table. Connect to your SQL Server instance. Type the following query.
SELECT [EmailPromotion]
FROM [AdventureWorks2014].[Person].[Person]
WHERE EmailPromotion = 0
This query after execution will return 11158 rows. We wil create a Batch update operation through our ASP.Net web forms application. We will also measure the performance of this batch operation. We will create a batch operation from our application that will affect all the rows (11158). If we did the batch update from T-SQL we would use the following statement.
UPDATE [AdventureWorks2014].[Person].[Person]
SET EmailPromotion = 10
WHERE EmailPromotion =0
6) In our webform in the Page_Load event we write the following code. We add 10 to every row in the Persons entity to the EmailPromotion column.
protected void Page_Load(object sender, EventArgs e)
{
using (AdventureWorks2014Entities ctx = new AdventureWorks2014Entities())
{
IQueryable<Person> persons = ctx.People.Where(x => x.EmailPromotion == 0);
foreach (Person person in persons)
{
person.EmailPromotion = person.EmailPromotion + 10;
}
ctx.SaveChanges();
}
}
Make sure you build your application but do not run it just yet.
7) I will use the Entity Framework Profiler to gain valuable insight in the batch operation and record metrics regarding the performance. You can download it here -http://www.hibernatingrhinos.com/products/efprof. You can acquire a trial license very easily. Make sure you download and install this fantastic EF profiler.
Inside the folder where you downloaded the Entity Framework Profiler locate the HibernatingRhinos.Profiler.Appender DLL.
We will add a reference to this DLL to our web application.Make sure you do that.
In the Global.asax file we need to add the following code to the Application_Start() event handling routine.
HibernatingRhinos.Profiler.Appender.EntityFramework.EntityFrameworkProfiler.Initialize();
Launch the EF Profiler and then build and run the application. The application will update the 11158 rows.
The results I got for my machine can be seen in the pictures below. You can see that the database has been hit 11158 times and the average duration for each one is 8.41ms.
You can also see the total duration in seconds that the whole batch operation lasted. we needed 111 seconds (almost 2 minutes) for updating 11158 rows. As you can understand this is not acceptible and we need to find a more efficient way to do the batch update.
8) Now we must install the EFUtilities library. We will do that through the Nuget Package Manager.Launch Nuget from VS and install EFUtilities. Have a look at the picture below
Now you must add a reference to using EntityFramework.Utilities library to your application. Inside the WebForm1.aspx.cs add the following
using EntityFramework.Utilities;
9) Comment out everything in the Global.asax file in Application_Start() event handling routine.
Comment out everything in the webform Page_Load event handling routine. Type only the following inside the Page_Load event handling routine.
protected void Page_Load(object sender, EventArgs e)
{
using (AdventureWorks2014Entities ctx = new AdventureWorks2014Entities())
{
//we find the rows with EmailPromotion =10 (11158) rows and add 10 more to each row
EFBatchOperation.For(ctx, ctx.People).Where(b => b.EmailPromotion == 10).Update(b => b.EmailPromotion, b => b.EmailPromotion + 10);
}
}
10) I will use the Sql Server Profiler to get the metrics regarding the batch operation using the EntityFramework.Utilities library. I launch the SQL Server Profiler, I connect to the SQL Server Enterprise edition instance in my machine and then create a trace with the SQL:BatchCompleted, SQL:BatchStarting events and a filter in the Database column.
Have a look at the picture below
11) Build and run your application and see the SQL Server Profiler trace. The duration column indicates that the batch operation needed only 66 ms to update 11158 rows.
Have a look at picture below
The results are amazing and the reason for such vast performance increase is because in traditional EF a separate UPDATE SQL statement runs for each row in the table, while the batch update operation provided by EF Utilities runs one UPDATE BULK SQL statement utilizing the SqlBulkCopy class. Inserts and deletes also work directly against the database.
My laptop is a Toshiba Qosmio Intel(R) Core(TM) i7-4700 MQ CPU @ 2.40GHz with 32Gbytes RAM with a 256 SSD drive.
Batch Update Operation with EF (11158 rows affected): 111 seconds
Batch Update Operation with EF Utilities (11158 rows affected): 56 ms - that means it is 1982 times faster
Hope it helps!!!
In this post I will be looking into EF 6.0 using the Code First Workflow and its support for stored procedures.
I will be building a simple ASP.Net MVC 5.0 application that will be the client application that will consume the Entity Framework data access layer.
I will be using Visual Studio 2013,C# 5.0 and LocalDb in this demo.
While I am building this simple ASP.Net application I will point out to various tools and option in VS 2013 that make the completion of an application faster.
I will be writing as less code as possible. I will be leveraging the scaffolding functionality as much as possible.
I will also show you how to log queries sent to the database by Entity Framework 6.0.
With EF 6.0 we do have a mechanism to trace/profile everything EF sents to the data store.
I will also demonstrate how you can install Entity Framework Power Tools that enables developers to visualise the POCO classes in an entity designer.
We will create the ASP.NET Web Application with the MVC 5 Project Template.
1) Launch Visual Studio 2013 and click on the "New Project". Select the Web from the left pane and create the ASP.NET Web Application. Have a look at the picture below
2) Select the MVC Project Template as shown below.
3) Visual Studio 2103 will automatically create the ASP.Net MVC 5.0 application.In the Models folder we will add a new class file, Footballer.cs
Right-click on the Models folder and Add a new Class, Footballer.cs.
public class Footballer
{
public int FootballerID { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public double Weight { get; set; }
public double Height { get; set; }
}
4) We will add declarative code in this simple class in order to define that we need the properties (columns in the database) to be required - not null fields in the database.
We need to reference the System.ComponentModel.DataAnnotations assembly. Have a look at the code below.
using System.ComponentModel.DataAnnotations;
namespace EF6StoreProcMVC5.Models
{
public class Footballer
{
public class Footballer
{
public int FootballerID { get; set; }
[Required]
public string FirstName { get; set; }
[Required]
public string LastName { get; set; }
[Required]
public double Weight { get; set; }
[Required]
public double Height { get; set; }
}
}
}
5) Entity Framework is already installed in our application. Looking into packages.config I see that i have the
<package id="EntityFramework" version="6.1.1" targetFramework="net451" />
I need to update to the latest version of the Entity Framework. YI update the package by entering the following command in the Package Manager Console:
Update-Package EntityFramework
Have a look at the picture
Now Entity Framework in my solution is updated to 6.1.3.
6) Now I am going to build the Controller. I am going to the Controllers folder and click Add --> New Scaffolded Item.
Have a look at the picture below
7) In the Add Scaffold wizard, select the MVC 5 Controller with views.
Have a look at the picture below
8) In the next Add Controller wizard, select the Model Class and add the Data Context class, select a suitable name for that and select a name for the controller and click Add
Have a look at the picture below
9) Have a look at the generated FootballerDBContext.cs
using System.Data.Entity;
namespace EF6StoreProcMVC5.Models
{
public class FootballerDBContext : DbContext
{
// You can add custom code to this file. Changes will not be overwritten.
//
// If you want Entity Framework to drop and regenerate your database
// automatically whenever you change your model schema, please use data migrations.
// For more information refer to the documentation:
// http://msdn.microsoft.com/en-us/data/jj591621.aspx
public FootballerDBContext() : base("name=FootballerDBContext")
{
}
public DbSet<Footballer> Footballers { get; set; }
}
}
At this point if you want to visualise the Footballer entity then you need to install the EF Power Tools. You must go to Tools and then Extensions and Updates.
Then do a search for Entity Framework Power Tools and then install them.
Have a look at the picture below
You can then select the FootballerDBContext.cs and then Entity Framework --> View Entity Data Model. Have a look at the picture below.
Have a look at the picture below to see the entity in the designer. This is very handy tool if you want to visualise your entities.
10) Have a look at the FootballersController.cs that was also generated.
using System.Data.Entity;
using System.Linq;
using System.Net;
using System.Web.Mvc;
using EF6StoreProcMVC5.Models;
namespace EF6StoreProcMVC5.Controllers
{
public class FootballersController : Controller
{
private FootballerDBContext db = new FootballerDBContext();
// GET: Footballers
public ActionResult Index()
{
return View(db.Footballers.ToList());
}
// GET: Footballers/Details/5
public ActionResult Details(int? id)
{
if (id == null)
{
return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
}
Footballer footballer = db.Footballers.Find(id);
if (footballer == null)
{
return HttpNotFound();
}
return View(footballer);
}
// GET: Footballers/Create
public ActionResult Create()
{
return View();
}
// POST: Footballers/Create
// To protect from overposting attacks, please enable the specific properties you want to bind to, for
// more details see http://go.microsoft.com/fwlink/?LinkId=317598.
[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Create([Bind(Include = "FootballerID,FirstName,LastName,Weight,Height")] Footballer footballer)
{
if (ModelState.IsValid)
{
db.Footballers.Add(footballer);
db.SaveChanges();
return RedirectToAction("Index");
}
return View(footballer);
}
// GET: Footballers/Edit/5
public ActionResult Edit(int? id)
{
if (id == null)
{
return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
}
Footballer footballer = db.Footballers.Find(id);
if (footballer == null)
{
return HttpNotFound();
}
return View(footballer);
}
// POST: Footballers/Edit/5
// To protect from overposting attacks, please enable the specific properties you want to bind to, for
// more details see http://go.microsoft.com/fwlink/?LinkId=317598.
[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Edit([Bind(Include = "FootballerID,FirstName,LastName,Weight,Height")] Footballer footballer)
{
if (ModelState.IsValid)
{
db.Entry(footballer).State = EntityState.Modified;
db.SaveChanges();
return RedirectToAction("Index");
}
return View(footballer);
}
// GET: Footballers/Delete/5
public ActionResult Delete(int? id)
{
if (id == null)
{
return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
}
Footballer footballer = db.Footballers.Find(id);
if (footballer == null)
{
return HttpNotFound();
}
return View(footballer);
}
// POST: Footballers/Delete/5
[HttpPost, ActionName("Delete")]
[ValidateAntiForgeryToken]
public ActionResult DeleteConfirmed(int id)
{
Footballer footballer = db.Footballers.Find(id);
db.Footballers.Remove(footballer);
db.SaveChanges();
return RedirectToAction("Index");
}
protected override void Dispose(bool disposing)
{
if (disposing)
{
db.Dispose();
}
base.Dispose(disposing);
}
}
}
11) You can also have a look at the generated views that were created by the Scaffold wizzard
We choose to open the _Layout.cshtml view and add the following line of code in the navigation pane
<li>@Html.ActionLink("Footballers", "Index", "Footballers")</li>
the full code is
<ul class="nav navbar-nav">
<li>@Html.ActionLink("Home", "Index", "Home")</li>
<li>@Html.ActionLink("About", "About", "Home")</li>
<li>@Html.ActionLink("Footballers", "Index", "Footballers")</li>
<li>@Html.ActionLink("Contact", "Contact", "Home")</li>
</ul>
Now we can navigate to our page through the menu.
Build and run your application.It will take some time to appear in the first time since the database is created.The database is under the App_Data special folder.
When you will see you application, click on Footballers from the menu and then add some sample data. Have a look at the picture below.
12) After you insert those records in the database then you would wonder what is the T-SQL that EF DBContext sent to the SQL Server LocalDb to execute.
There are various ways to profile the T-SQL statements that EF sends to the data store. Have a look here, here and here for some ways of profiling the data in earlier versions of EF.
You can always use the SQL Server Profiler. In EF 6.0 we can trace the T-SQL statements using the the Log property of DbContext.
I am going to add some code in the FootballersController.cs class file in order to intercept the T-SQL statements.
I am going to add inside the FootballersController constructor the following code.
public FootballersController()
{
db.Database.Log = T => Debug.Write(T);
}
Make sure you add a reference to the System.Diagnostics assembly.
Have a look below to see where above statement fits with everything.
using System.Data.Entity;
using System.Linq;
using System.Net;
using System.Web.Mvc;
using EF6StoreProcMVC5.Models;
using System.Diagnostics;
namespace EF6StoreProcMVC5.Controllers
{
public class FootballersController : Controller
{
private FootballerDBContext db = new FootballerDBContext();
public FootballersController()
{
db.Database.Log = T => Debug.Write(T);
}
// GET: Footballers
public ActionResult Index()
{
return View(db.Footballers.ToList());
}
Now If I build and run my application again and try to insert more records and I have my Output window open i can see wha is sent to the database.
Have a look at the picture below. Αs you can see a transaction is opened and values are inserted using an Insert statement. Then the transaction is commited. So we can see the complete T-SQL code. Note that when we have an Insert statement EF engine starts a transaction.
13) We want to use stored procedures instead of Insert,Update,Delete statements. We can have the EF engine generate them. We need to enable Code First Migrations
Go to Tools-> NuGet Package Manager->Package Manager Console and enter the following command and hit Enter:
Enable-Migrations -ContextTypeName EF6StoreProcMVC5.Models.FootballerDBContext
You have to put your own DBContext class where I put EF6StoreProcMVC5.Models.FootballerDBContext.
Have a look at the files generated under the Migrations folder.
Code first Migrations were enabled for the project.
Now we must tell the DbContext class that must use the Stored Procedures. Do open the FootballerDBContext class and update the code as shown below:
We just add this method and keep everything else.
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity<Footballer>().MapToStoredProcedures();
}
We do override the OnModelCreating method.
14) Now we need to generate the stored procedures
Go to Tools-> NuGet Package Manager->Package Manager Console and enter the following command and hit Enter:
Add-Migration MyfootballSPs
The 201503282306498_MyfootballSPs.cs is generated. Have a look at the code below to see the Stored Procedures.
namespace EF6StoreProcMVC5.Migrations
{
using System;
using System.Data.Entity.Migrations;
public partial class MyfootballSPs : DbMigration
{
public override void Up()
{
CreateStoredProcedure(
"dbo.Footballer_Insert",
p => new
{
FirstName = p.String(),
LastName = p.String(),
Weight = p.Double(),
Height = p.Double(),
},
body:
@"INSERT [dbo].[Footballers]([FirstName], [LastName], [Weight], [Height])
VALUES (@FirstName, @LastName, @Weight, @Height)
DECLARE @FootballerID int
SELECT @FootballerID = [FootballerID]
FROM [dbo].[Footballers]
WHERE @@ROWCOUNT > 0 AND [FootballerID] = scope_identity()
SELECT t0.[FootballerID]
FROM [dbo].[Footballers] AS t0
WHERE @@ROWCOUNT > 0 AND t0.[FootballerID] = @FootballerID"
);
CreateStoredProcedure(
"dbo.Footballer_Update",
p => new
{
FootballerID = p.Int(),
FirstName = p.String(),
LastName = p.String(),
Weight = p.Double(),
Height = p.Double(),
},
body:
@"UPDATE [dbo].[Footballers]
SET [FirstName] = @FirstName, [LastName] = @LastName, [Weight] = @Weight, [Height] = @Height
WHERE ([FootballerID] = @FootballerID)"
);
CreateStoredProcedure(
"dbo.Footballer_Delete",
p => new
{
FootballerID = p.Int(),
},
body:
@"DELETE [dbo].[Footballers]
WHERE ([FootballerID] = @FootballerID)"
);
}
public override void Down()
{
DropStoredProcedure("dbo.Footballer_Delete");
DropStoredProcedure("dbo.Footballer_Update");
DropStoredProcedure("dbo.Footballer_Insert");
}
}
}
15) We need to tell the database to create the MyfootballSPs. Enter the following command in the Package Manager Console:
Update-Database
and hit Enter. Go to Server Explorer and open the database. Have a look under Stored Procedures. The stored procedures are created.
Have a look at the picture below to see what I got when I did update the database.
16) We want to check if Entity Framework is now using the stored procedures. Do run the application again and add some footballers in the web page.
Have a look at the picture below to see what I got in the Output window:
I can see clearly that the Insert statements are not used and instead of that the dbo.Footballer_Insert is used.
In this post we did create a small ASP.Net MVC 5.0 application and we did go through on how to use stored proecedures with Code Firsr EF workflow.
Hope it helps!!!
In the past few weeks I was looking into the new changes that have been made in the current beta release of ASP.NET 5.0. In this post I will talk about those new changes and what impressed me most.
There have been many changes and in my opinion this release is the most important , significant release since the ASP.Net introduction.
If you want to install it and write some ASP.Net 5 code first you need to download the latest CTP of Visual Studio 2015 which was made available a few weeks back.
Let me start by telling you that ASP.NET is 15 years old since it has been released back in 2000, just to put things into perspective.
ASP.Net 5 is open source
ASP.NET 5 is developed as an open source project and is available on GitHub.
If you want you can download the code and make changes and then submit those changes. Obviously the ASP.Net team is responsible which of those changes will find their way to the official final release.
I really like the new approach that MS is taking on the whole open source subject. I think most developers love it and it helps us to understand more about how everything is built.
ASP.NET is supported by a cross-platform runtime
This simply means that ASP.Net 5 applications can run now on the MAc OS X and Linux operating systems.
This means that developers that do not come from a Windows background can build and run their apps in a MacBook. I think that will open new horizons for people that chose not to use machines that run microsoft operating systems
Some developers might argue that they have been running ASP.Net apps on Mono. As we know Mono is an open source implementation of Microsoft's .NET Framework. It is not the same code base.
When hosting your application the ASP.Net 5 gives you two choices and great flexibility since ASP.Net 5 works with two runtimes, .NET Core and .Net Framework.
Applications using the .NET Core (which is more limited than the full .Net version) will run on this version of the runtime even if the host machine runs a different version of the runtime.
If one updates the runtime of that particular application other applications that run on different versions of the runtime will not be affected. Another thing to bear in mind is that you will not be prompted for features that you do not need.
You can also have your application running on the full .Net Framework so you can have full access to all of the APIs available.
Web Forms won’t be part of ASP.NET 5
This is a pretty big decision and a major change. Web forms will still be supported in the sense that someone will be able to build web apps with web forms in VS 2015 but will not get all the goodies that ASP.Net 5 comes with e.g those web form apps will not run on Linux or OS X operating systems.
Web forms was a great programming paradigm that help MS to bring into the ASP.Net stack all those window VB developers that used to build window applications on a form environment.
Things have moved since. Web forms abstract the web and do not embrace it as ASP.Net MVC does. The choice that has been made by MS is clear, they want us to develop or rewrite new web applications on ASP.Net MVC which is what happens anyway in most cases. ASP.Net MVC 6 that ships with the ASP.Net 5 is the technology of choice for all new ASP.Net 5.0 applications
New Project Templates
There are new project templates in ASP.Net 6.0 and VS 2015. When starting a new ASP.NET 5 project, "File -> New Project -> Web -> ASP.NET Web Application" you can see the set of project templates displayed as below.
ASP.NET 4.6 templates are grouped together and the new ASP.NET 5 preview templates are grouped seperately.
ASP.Net 5 does not support VB.Net
This is another big decision made by Microsoft. I quote Scott Hunter here "ASP.NET 5 is C# only at this point and that will not change before we RTM. We plan to have extensibility points so other languages like VB, F#, etc can be added via the form of a support package or such."
Most developers (I would say 98% of the .Net projects in the last 5 years have been implemented in C#) were anticipating such a decision I suppose.
ASP.Net 5 & Tag Helpers
Tag Helpers is one brand new feature in ASP.Net 5 and ASP.Net MVC 6.
This is a direct alternative of the MVC helper methods that we are writing inside our Razor Views.
I am pretty sure HTML designers will love this new feature as it makes more sense to them.
Let's have a look at the View below
@using (Html.BeginForm())
{
<div>
@Html.LabelFor(m => p.Αge, "Age:")
@Html.TextBoxFor(m => p.Age)
</div>
<input type="submit" value="Submit" />
}
We have the Html.BeginForm(), Html.LabelFor(), and Html.TextBoxFor() helper methods that are part of the razor syntax in order to build a form.
Let's rewrite the snipper above using Tag Helpers:
@addtaghelper "Microsoft.AspNet.Mvc.TagHelpers"
<form asp-controller="Customers" asp-action="Submit" method="post">
<div>
<label asp-for="Age">Age:</label>
<input asp-for="Age" />
</div>
<input type="submit" value="Save" />
</form>
Have a look here for a complete example
Support and Integration with Bower, Grunt and Gulp
There is a built in support for Bower, Grunt and Gulp in VS 2015.
Developers can manage JavaScript and CSS libraries through Bower which is basically a package manager for client-side libraries
Through GruntJS developers can minify javascript files,compile LESS and Sass files into CSS, do code validation, run javascript unit tests.
A great addition for front-end development that will excite many client side developers.
Unified MVC 6 model
When building ASP.Net applications we often use MVC, Web API and Web Pages. In this new release of ASP.Net 5 these programming frameworks are merged into one.
For example in ASP.Net MVC 6 there is only one Controller class, I mean one base Controller class (Microsoft.AspNet.Mvc.Controller) class.
In previous versions of ASP.NET MVC, MVC controllers were different than Web API controllers.
Support for AngularJS
AngularJS is a JavaScript framework. It can be added to an HTML page with a <script> tag. AngularJS extends HTML attributes with Directives.
It is extremely popular amongst client-side developers and now there is great support in Visual Studio 2015 for AngularJs modules e.t.c.
ASP.NET 5 Dependency Injection Framework support
Most professional developers use dependency injection as a software design pattern that implements inversion of control. There is built-in support for DI in ASP.Net 5 so we do not need to rely on third party DI frameworks like AutoFac.
xUnit.net built in support in VS 2015
Mstest ([TestClass], [TestMethod]) was the default testing framework for Visual Studio so far.
ASP.NET 5 and VS 2015 uses xUnit.net as a unit test framework. This framework uses the [Fact] attribute. Support for Mstest still exists.
Have a look at this extensive and detailed example on how to use xUnit net and ASP.net
Hope it helps!!!
I have been using JQuery for a couple of years now and it has helped me to solve many problems on the client side of web development. You can find all my posts about JQuery in this link. In this post I will be providing you with a hands-on example on the JQuery Roundabout Plugin.
This is a plugin that supports circular rotating display for content. It supports autoplay.
In this hands-on example I will be using Expression Web 4.0.This application is not a free application. You can use any HTML editor you like.
You can use Visual Studio 2013 Express edition. You can download it here.
You can download this plugin from this link.
I launch Expression Web 4.0 and then I type the following HTML markup (I am using HTML 5)
<!DOCTYPE html>
<html>
<head>
<title>JQuery Roundabout Plugin </title>
<style>
ul {
margin: 1em auto;
width:700px;
height: 300px;
list-style: none;
}
li {
text-align: center;
height: 300px;
width: 700px;
background-color:#eee;
color:#565748;
}
</style>
<script src="jquery-2.0.2.min.js"></script>
<script src="jquery.roundabout.min.js"></script>
<script type="text/javascript">
$(function () {
$('ul').roundabout({ minOpacity: .1, minScale: .1 });
});
</script>
</head>
<body>
<div>
<ul>
<li>
<h2>Manchester Utd</h2>
<p>Manchester United Football Club is an English professional football club, based in Old Trafford, Greater Manchester that plays in the Premier League. Founded as Newton Heath LYR Football Club in 1878, the club changed its name to Manchester United in 1902 and moved to Old Trafford in 1910 and is one of the most successful clubs in English football.Manchester United have won the most League titles (20) of any English club,[3] a joint record 11 FA Cups, four League Cups, and a record twenty FA Community Shields. The club has also won three European Cups, one UEFA Cup Winners' Cup, one UEFA Super Cup, one Intercontinental Cup, and one FIFA Club World Cup. In 1998–99, the club won a continental treble of the Premier League, the FA Cup and the UEFA Champions League..</p>
<p>Sed urna ligula, tristique ut accumsan in, condimentum quis quam. Nam pharetra erat at lacus
sagittis sagittis. </p>
<a href="http://www.manutd.com/">Man Utd</a>
</li>
<li>
<h2>Liverpool FC</h2>
<p>Liverpool Football Club /ˈlɪvərpuːl/ is an English Premier League football club based in Liverpool. Liverpool F.C. is one of the most successful clubs in England and has won more European trophies than any other English team with five European Cups, three UEFA Cups and three UEFA Super Cups. The club has also won eighteen League titles, seven FA Cups and a record eight League Cups.
Liverpool was founded in 1892 and joined the Football League the following year. The club has played at Anfield since its formation. The most successful period in Liverpool's history was the 1970s and '80s when Bill Shankly and Bob Paisley led the club to eleven league titles and seven European trophies. Cras vitae nibh
ac augue pellentesque malesuada. Maecenas ultricies vitae dui quis tincidunt. Curabitur
ut sagittis lorem. Nam eros sapien, aliquet pharetra iaculis quis, ullamcorper ut mi.
Sed urna ligula, tristique ut accumsan in, condimentum quis quam. Nam pharetra erat at lacus
sagittis sagittis. </p>
<a href="http://www.liverpoolfc.com/">Liverpool FC</a>
</li>
<li>
<h2>Everton FC</h2>
<p>Everton Football Club /ˈɛvərtən/ are an English Premier League football club based in Liverpool. The club have competed in the top division for a record 110 seasons (missing only four seasons, all at level 2) and have won the League Championship nine times.[3]
Formed in 1878, Everton were founding members of The Football League in 1888 and won their first league championship two seasons later. Following four league titles and two FA Cup wins, Everton experienced a lull in the immediate post World War Two period until a revival in the 1960s which saw the club win two league championships and an FA Cup. The mid-1980s represented their most recent period of success, with two League Championship successes, an FA Cup, and the 1985 European Cup Winners' Cup. The club's most recent major trophy was the 1995 FA Cup. The club's supporters are known as Evertonians.</p>
<a href="http://www.evertonfc.com/">Everton FC</a>
</li>
</ul>
</div>
</body>
</html>
I have included the JQuery file and the JQuery Roundabout plugin file in the head section.
I have some content in an unordered list and some internal styles to give them some basic styling.The markup and the styles are pretty basic.
Have a look at the picture below to see the plugin in action.
The only javascript code I need to have this effect (roundabout) is the following
<script type="text/javascript">
$(function () {
$('ul').roundabout({ minOpacity: .1, minScale: .1 });
});
</script>
You can set options on Roundabout to change how it behaves.We do this by passing in an object of options into the main Roundabout call upon initialization.
For more options regarding this plugin visit this link
Hope it helps!!!
I have been delivering a certified course in MS SQL Server 2012 recently and I was asked several questions about temporary tables, how to create them, how to manage them, when to use them and what are the limitations of them.
In this post I will try to shed light on this particular issue with lots of hands-on demos.
Temporary tables and table variables make use of the system tempdb database.
I have installed SQL Server 2012 Enterprise edition in my machine but you can use the SQL Server 2012/2008 Express edition as well.
I am connecting to my local instance through Windows Authentication and in a new query window I type (you can copy paste)
First I am going to create a new temporary table and populate it. Execute the script below.
USE tempdb
GO
IF OBJECT_ID('tempdb..#footballer') IS NOT NULL
DROP TABLE #footballer;
GO
CREATE TABLE #footballer
(
[FootballerID] INT IDENTITY NOT NULL PRIMARY KEY,
[lastname] [varchar](15) NOT NULL,
[firstname] [varchar](15) NOT NULL,
[shirt_no] [tinyint] NOT NULL,
[position_played] [varchar](30) NOT NULL,
);
GO
SET IDENTITY_INSERT [dbo].[#footballer] ON
GO
INSERT [#footballer] ([FootballerID], [lastname],
[firstname], [shirt_no], [position_played])
VALUES (1,N'Oliver', N'Regina', 4, N'goalkeeper')
INSERT [#footballer] ([FootballerID], [lastname],
[firstname], [shirt_no], [position_played])
VALUES (2,N'Alexander', N'Roy', 8, N'goalkeeper')
INSERT [#footballer] ([FootballerID], [lastname],
[firstname], [shirt_no], [position_played])
VALUES (3,N'Mueller', N'Dewayne', 10, N'defender')
INSERT [#footballer] ([FootballerID], [lastname],
[firstname], [shirt_no], [position_played])
VALUES (4,N'Buckley', N'Beth', 3, N'midfielder')
INSERT [#footballer] ([FootballerID], [lastname],
[firstname], [shirt_no], [position_played])
VALUES (5,N'Koch', N'Jolene', 7, N'striker')
GO
SELECT * FROM #footballer
As you can see there is a # prefix in front of the table. This table will be saved in the tempdb.
Finally I select everything from the temporary table.
If I open a new query window and try to select everything (see the query below) from the #footballer table.
USE tempdb
GO
SELECT * FROM #footballer
You will not receive any results. You will receive an error - Invalid object name '#footballer'.
This is a local temporary table and it is in scope only in the current connection-session.
We can also create global temporary tables. In a new query window execute the following script.
USE tempdb
GO
IF OBJECT_ID('tempdb..##footballernew') IS NOT NULL
DROP TABLE ##footballernew;
GO
CREATE TABLE #footballernew
(
[FootballerID] INT IDENTITY NOT NULL PRIMARY KEY,
[lastname] [varchar](15) NOT NULL,
[firstname] [varchar](15) NOT NULL,
[shirt_no] [tinyint] NOT NULL,
[position_played] [varchar](30) NOT NULL,
);
GO
SET IDENTITY_INSERT [dbo].[##footballernew] ON
GO
INSERT [##footballernew] ([FootballerID], [lastname],
[firstname], [shirt_no], [position_played])
VALUES (1,N'Oliver', N'Regina', 4, N'goalkeeper')
INSERT [##footballernew] ([FootballerID], [lastname],
[firstname], [shirt_no], [position_played])
VALUES (2,N'Alexander', N'Roy', 8, N'goalkeeper')
INSERT [##footballernew] ([FootballerID], [lastname],
[firstname], [shirt_no], [position_played])
VALUES (3,N'Mueller', N'Dewayne', 10, N'defender')
INSERT [##footballernew] ([FootballerID], [lastname],
[firstname], [shirt_no], [position_played])
VALUES (4,N'Buckley', N'Beth', 3, N'midfielder')
INSERT [##footballernew] ([FootballerID], [lastname],
[firstname], [shirt_no], [position_played])
VALUES (5,N'Koch', N'Jolene', 7, N'striker')
GO
SELECT * FROM ##footballernew
We denote the global temporary table with ## - ##footballernew
The global temporary table is deleted when all users referencing the table disconnect.
Both global and local temporary tables should be deleted in code rather than depending on automatic drop.
A temporary table created in a stored procedure is visible to other stored procedures executed from within the first procedure.
In a new query window type the following.
USE tempdb
GO
SELECT * FROM ##footballernew
In this case there will be no error. Global temporary tables persist across sessions-connections.
You can also add columns to temporary tables and alter the definition of existing columns.
In this script I add another column and then alter the definition of an existing column.
USE tempdb
GO
IF OBJECT_ID('tempdb..#footballer') IS NOT NULL
DROP TABLE #footballer;
GO
CREATE TABLE #footballer
(
[FootballerID] INT IDENTITY NOT NULL PRIMARY KEY,
[lastname] [varchar](15) NOT NULL,
[firstname] [varchar](15) NOT NULL,
[shirt_no] [tinyint] NOT NULL,
[position_played] [varchar](30) NOT NULL,
);
GO
SET IDENTITY_INSERT [dbo].[#footballer] ON
GO
INSERT [#footballer] ([FootballerID], [lastname],
[firstname], [shirt_no], [position_played])
VALUES (1,N'Oliver', N'Regina', 4, N'goalkeeper')
INSERT [#footballer] ([FootballerID], [lastname],
[firstname], [shirt_no], [position_played])
VALUES (2,N'Alexander', N'Roy', 8, N'goalkeeper')
INSERT [#footballer] ([FootballerID], [lastname],
[firstname], [shirt_no], [position_played])
VALUES (3,N'Mueller', N'Dewayne', 10, N'defender')
INSERT [#footballer] ([FootballerID], [lastname],
[firstname], [shirt_no], [position_played])
VALUES (4,N'Buckley', N'Beth', 3, N'midfielder')
INSERT [#footballer] ([FootballerID], [lastname],
[firstname], [shirt_no], [position_played])
VALUES (5,N'Koch', N'Jolene', 7, N'striker')
GO
ALTER TABLE #footballer
ADD [is_retired] BIT NULL;
GO
ALTER TABLE #footballer
ALTER COLUMN [lastname] [nvarchar](50);
GO
You can use any data type for columns definition in a temporary table. You can also use user-defined data types.
You can also have constraints in temporary tables.If you execute the code below, it will work perfectly fine.
USE tempdb
GO
IF OBJECT_ID('tempdb..#Movies') IS NOT NULL
DROP TABLE #footballer;
GO
CREATE TABLE #Movies
(
MovieID INT PRIMARY KEY ,
MovieName NVARCHAR(50) ,
MovieRating TINYINT
)
GO
ALTER TABLE #Movies
WITH CHECK
ADD CONSTRAINT CK_Movie_Rating
CHECK (MovieRating >= 1 AND MovieRating <= 5)
But you have to be careful when creating-applying foreign keys. FOREIGN KEY constraints are not enforced on local or global temporary tables.
Execute the script below to see what I mean.The foreign key will not be created.
USE tempdb
go
CREATE TABLE #Persons
(
P_Id INT NOT NULL ,
LastName VARCHAR(255) NOT NULL ,
FirstName VARCHAR(255) ,
Address VARCHAR(255) ,
City VARCHAR(255) ,
PRIMARY KEY ( P_Id )
)
CREATE TABLE #Orders
(
O_Id int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
P_Id int FOREIGN KEY REFERENCES #Persons(P_Id)
)
Please bear in mind that you can create temporary tables with clustered and non-clustered indexes on them.
Let's investigate the behavior of temporary tables and IDENTITY columns.
If you execute the script below , it will fail. This is the same behavior when executing the same script to regular tables. You cannot specify values for the IDENTITY column.If you choose to do so you must set IDENTITY_INSERT ON.
USE tempdb
GO
IF OBJECT_ID('tempdb..#Persons') IS NOT NULL
DROP TABLE #Persons;
GO
CREATE TABLE #Persons
(
P_Id INT PRIMARY KEY CLUSTERED IDENTITY(1,1) ,
LastName VARCHAR(255) NOT NULL ,
FirstName VARCHAR(255) ,
Address VARCHAR(255) ,
City VARCHAR(255)
)
--this will not work
INSERT #Persons(P_Id,LastName,FirstName,Address,City)
VALUES (1,'Steven','Gerrard','123 liverpool street','liverpool')
SET IDENTITY_INSERT [#Persons] ON
GO
--this will work
INSERT #Persons(P_Id,LastName,FirstName,Address,City)
VALUES (1,'Steven','Gerrard','123 liverpool street','liverpool')
Αlso note that transactions are honored in temporary tables. If I begin an explicit transaction -an insert- without committing it will insert the row of data but then if a rollback is issued the whole operation will be rolled back
Execute the script below.
USE tempdb
GO
IF OBJECT_ID('tempdb..#Persons') IS NOT NULL
DROP TABLE #Persons;
GO
CREATE TABLE #Persons
(
P_Id INT PRIMARY KEY CLUSTERED IDENTITY(1,1) ,
LastName VARCHAR(255) NOT NULL ,
FirstName VARCHAR(255) ,
Address VARCHAR(255) ,
City VARCHAR(255)
)
SET IDENTITY_INSERT [#Persons] ON
GO
--this will insert the value
BEGIN TRAN
INSERT #Persons(P_Id,LastName,FirstName,Address,City)
VALUES (1,'Steven','Gerrard','123 liverpool street','liverpool')
GO
SELECT * FROM #Persons
--this will rollback the transaction
ROLLBACK TRAN
Hope it helps!!!