Dot Net Rules

Display Data using a Stored Procedure in an ASP.Net MVC 5.0 application with Entity Framework 6.0

2017-03-13T04:04:00Z

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...(read more)

Understanding Dependency Injection in ASP.Net MVC applications

2016-12-28T02:14:00Z

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...(read more)

Returning IQueryable from Repository Pattern

2016-10-16T06:16:00Z

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...(read more)

Entity Framework and Repository Pattern

2016-08-01T05:32:26Z

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...(read more)

Explaining the Repository Pattern

2016-07-31T08:47:00Z

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...(read more)

Using ViewModels in ASP.Net MVC 5.0 applications

2016-06-19T02:54:00Z

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...(read more)

Using Jquery Ajax method in ASP.Net MVC

2016-06-13T03:33:00Z

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...(read more)

Looking into the Generic Repository Pattern, Entity Framework and ASP.Net MVC

2016-05-16T03:19:00Z

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...(read more)

Building a simple ASP.NET MVC 6 application using Entity Framework 7 & ASP.NET 5 RC

2016-03-28T04:41:00Z

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...(read more)

Passing multiple models to a view using Tuples in an ASP.Net MVC 5.0 application

2016-03-07T07:52:00Z

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...(read more)

Passing multiple models to a view in an ASP.Net MVC 5.0 application

2016-03-07T00:23:10Z

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...(read more)

Handling concurrency in Code First ASP.Net 5.0 MVC applications

2016-02-29T10:10:00Z

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...(read more)

Caching an ASP.Net MVC 5.0 application

2016-02-29T04:46:00Z

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...(read more)

Using MongoDB with ASP.Net MVC

2015-11-08T05:41:00Z

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....(read more)

Using Asynchronous Operations in Entity Framework 6.0 and ASP.Net MVC

2015-10-17T08:31:00Z

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...(read more)

Entity Framework Performance optimization patterns-part II

2015-10-12T08:44:00Z

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....(read more)

Entity Framework Performance optimization patterns

2015-10-04T04:57:00Z

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...(read more)

Using Second Level Cache in Entity Framework 6.1 applications

2015-06-28T09:00:00Z

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...(read more)

Database Command Logging & Profiling in Entity Framework 6.0

2015-06-17T05:56:00Z

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...(read more)

Handling batch operations with Entity Framework and Entity Framework Extended library

2015-06-07T04:16:00Z

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...(read more)

Handling batch operations with Entity Framework and EF Utilities

2015-06-03T06:52:00Z

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...(read more)

Using Stored Procedures in an ASP.Net MVC 5.0 application using Entity Framework 6.0

2015-03-29T08:25:00Z

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...(read more)

What is New in ASP.NET 5 and MVC 6

2015-03-16T05:37:00Z

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...(read more)

Looking into the JQuery Roundabout Plugin

2014-06-03T08:53:00Z

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...(read more)

Looking into temporary tables in SQL Server

2013-12-05T03:11:00Z

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