Dot Net Rules

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

nikolaosk — Mon, 13 Mar 2017 04:04:00 GMT

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

Understanding Dependency Injection in ASP.Net MVC applications

nikolaosk — Wed, 28 Dec 2016 02:14:00 GMT

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

Returning IQueryable from Repository Pattern

nikolaosk — Sun, 16 Oct 2016 06:16:00 GMT

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

Entity Framework and Repository Pattern

nikolaosk — Mon, 01 Aug 2016 05:32:26 GMT

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

Explaining the Repository Pattern

nikolaosk — Sun, 31 Jul 2016 08:47:00 GMT

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

Using ViewModels in ASP.Net MVC 5.0 applications

nikolaosk — Sun, 19 Jun 2016 02:54:00 GMT

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

Using Jquery Ajax method in ASP.Net MVC

nikolaosk — Mon, 13 Jun 2016 03:33:00 GMT

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

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

nikolaosk — Mon, 16 May 2016 03:19:00 GMT

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

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

nikolaosk — Mon, 28 Mar 2016 04:41:00 GMT

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

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

nikolaosk — Mon, 07 Mar 2016 07:52:00 GMT

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

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

nikolaosk — Mon, 07 Mar 2016 00:23:10 GMT

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

Handling concurrency in Code First ASP.Net 5.0 MVC applications

nikolaosk — Mon, 29 Feb 2016 10:10:00 GMT

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

Caching an ASP.Net MVC 5.0 application

nikolaosk — Mon, 29 Feb 2016 04:46:00 GMT

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

Using MongoDB with ASP.Net MVC

nikolaosk — Sun, 08 Nov 2015 05:41:00 GMT

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

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

nikolaosk — Sat, 17 Oct 2015 08:31:00 GMT

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

Entity Framework Performance optimization patterns-part II

nikolaosk — Mon, 12 Oct 2015 08:44:00 GMT

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

Entity Framework Performance optimization patterns

nikolaosk — Sun, 04 Oct 2015 04:57:00 GMT

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

Using Second Level Cache in Entity Framework 6.1 applications

nikolaosk — Sun, 28 Jun 2015 09:00:00 GMT

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

Database Command Logging & Profiling in Entity Framework 6.0

nikolaosk — Wed, 17 Jun 2015 05:56:00 GMT

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

Handling batch operations with Entity Framework and Entity Framework Extended library

nikolaosk — Sun, 07 Jun 2015 04:16:00 GMT

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

Handling batch operations with Entity Framework and EF Utilities

nikolaosk — Wed, 03 Jun 2015 06:52:00 GMT

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

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

nikolaosk — Sun, 29 Mar 2015 08:25:00 GMT

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

What is New in ASP.NET 5 and MVC 6

nikolaosk — Mon, 16 Mar 2015 05:37:00 GMT

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

Looking into the JQuery Roundabout Plugin

nikolaosk — Tue, 03 Jun 2014 08:53:00 GMT

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

Looking into temporary tables in SQL Server

nikolaosk — Thu, 05 Dec 2013 03:11:00 GMT

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