ASP.NET MVC Custom Membership Password Hashing based on SALT key using SHA-3 Algorithm

Mots-clefs

, , , , , , , , , , , , ,

ASP.NET membership is designed to enable you to easily use a number of different membership providers for your ASP.NET applications. You can use the supplied membership providers that are included with the .NET Framework, or you can implement your own providers.

There are two primary reasons for creating a custom membership provider.

  • You need to store membership information in a data source that is not supported by the membership providers included with the .NET Framework, such as a MysQL database, an Oracle database, or other data sources.
  • You need to manage membership information using a database schema that is different from the database schema used by the providers that ship with the .NET Framework. A common example of this would be membership data that already exists in a SQL Server database for a company or Web site.

This tutorial  is a continuation of our tutorial  How to configure Custom Membership and Role Provider using ASP.NET MVC4

Let’s talk about password encoding and decoding and take the opportunity to introduce the Unit Of Work Pattern used to insert and retrieve data.

There exists a sequence of hash functions SHA-0, SHA-1, SHA-2 and the most recent SHA-3.

SHA-3, is a new cryptographic hash function that has been selected by NIST in October 2012 following a public competition launched in 2007, this because the weaknesses discovered on MD-5 and SHA-1 let fear fragility SHA-2 is built on the same schéma. It has variations that can produce hashes 224, 256, 384 and 512 bits. It is built on a different principle from that of MD5, SHA-1 and SHA-2 functions.

So in our tutorial, we will use the SHA-3  512 bits,

For this, it is not intended to replace SHA-2, which is at present not been compromised by a significant attack, but to provide an alternative response to attacks against MD5 possibilities standards SHA-0 and SHA-1.

A. Unit Of Work Pattern

The purpose of this article is not an introduction to Unit Of Work Pattern but a particular use of it. So for more information Unit Of Work Pattern, please read articles that deal with this subject such as http://msdn.microsoft.com/en-us/magazine/dd882510.aspx

  • Create a class library Project and reference EntityFramwork.dll
  • Generate the model and classes from membeship database (Model.Context.tt, Model.Entities.tt and Model.mapping.tt ) . If you are newly in Entity Framwork, please read our tutorial  Introduction to entity framework Code first
  • Create IRepository interface

public interface IRepository<T> where T : class
{
/// <summary>
/// Get the total objects count.
/// </summary>
int Count { get; }

/// <summary>
/// Gets all objects from database
/// </summary>
IQueryable<T> All();

/// <summary>
/// Gets object by primary key.
/// </summary>
/// <param name= »id »> primary key </param>
/// <returns> </returns>
T GetById(object id);

/// <summary>
/// Gets objects via optional filter, sort order, and includes
/// </summary>
/// <param name= »filter »> </param>
/// <param name= »orderBy »> </param>
/// <param name= »includeProperties »> </param>
/// <returns> </returns>
IQueryable<T> Get(Expression<Func<T, bool>> filter = null, Func<IQueryable<T>, IOrderedQueryable<T>> orderBy = null, string includeProperties = «  »);

/// <summary>
/// Gets objects from database by filter.
/// </summary>
/// <param name= »predicate »> Specified a filter </param>
IQueryable<T> Filter(Expression<Func<T, bool>> predicate);

/// <summary>
/// Gets objects from database with filting and paging.
/// </summary>
/// <param name= »filter »> Specified a filter </param>
/// <param name= »total »> Returns the total records count of the filter. </param>
/// <param name= »index »> Specified the page index. </param>
/// <param name= »size »> Specified the page size </param>
IQueryable<T> Filter(Expression<Func<T, bool>> filter, out int total, int index = 0, int size = 50);

/// <summary>
/// Gets the object(s) is exists in database by specified filter.
/// </summary>
/// <param name= »predicate »> Specified the filter expression </param>
bool Contains(Expression<Func<T, bool>> predicate);

/// <summary>
/// Find object by keys.
/// </summary>
/// <param name= »keys »> Specified the search keys. </param>
T Find(params object[] keys);

/// <summary>
/// Find object by specified expression.
/// </summary>
/// <param name= »predicate »> </param>
T Find(Expression<Func<T, bool>> predicate);

/// <summary>
/// Create a new object to database.
/// </summary>
/// <param name= »entity »> Specified a new object to create. </param>
T Create(T entity);

/// <summary>
/// Deletes the object by primary key
/// </summary>
/// <param name= »id »> </param>
void Delete(object id);

/// <summary>
/// Delete the object from database.
/// </summary>
/// <param name= »entity »> Specified a existing object to delete. </param>
void Delete(T entity);

/// <summary>
/// Delete objects from database by specified filter expression.
/// </summary>
/// <param name= »predicate »> </param>
void Delete(Expression<Func<T, bool>> predicate);

/// <summary>
/// Update object changes and save to database.
/// </summary>
/// <param name= »entity »> Specified the object to save. </param>
void Update(T entity);
/// <summary>
///
/// </summary>
/// <param name= »query »></param>
/// <param name= »parameters »></param>
/// <returns></returns>
IEnumerable<T> GetWithRawSql(string query, params object[] parameters);
}

  • Next, we are going to implement the IRepository interface

public class Repository<T> : IRepository<T> where T : class
{
protected readonly DbContext _dbContext;
protected readonly DbSet<T> _dbSet;

public Repository(DbContext dbContext)
{
_dbContext = dbContext;
_dbSet = _dbContext.Set<T>();
}

public virtual int Count
{
get { return _dbSet.Count(); }
}

public virtual IQueryable<T> All()
{
return _dbSet.AsQueryable();
}

public virtual T GetById(object id)
{
return _dbSet.Find(id);
}

public virtual IQueryable<T> Get(Expression<Func<T, bool>> filter = null, Func<IQueryable<T>, IOrderedQueryable<T>> orderBy = null, string includeProperties = «  »)
{
IQueryable<T> query = _dbSet;

if (filter != null)
{
query = query.Where(filter);
}

if (!String.IsNullOrWhiteSpace(includeProperties))
{
foreach (var includeProperty in includeProperties.Split(new[] { ‘,’ }, StringSplitOptions.RemoveEmptyEntries))
{
query = query.Include(includeProperty);
}
}

if (orderBy != null)
{
return orderBy(query).AsQueryable();
}
else
{
return query.AsQueryable();
}
}

public virtual IQueryable<T> Filter(Expression<Func<T, bool>> predicate)
{
return _dbSet.Where(predicate).AsQueryable();
}

public virtual IQueryable<T> Filter(Expression<Func<T, bool>> filter, out int total, int index = 0, int size = 50)
{
int skipCount = index * size;
var resetSet = filter != null ? _dbSet.Where(filter).AsQueryable() : _dbSet.AsQueryable();
resetSet = skipCount == 0 ? resetSet.Take(size) : resetSet.Skip(skipCount).Take(size);
total = resetSet.Count();
return resetSet.AsQueryable();
}

public bool Contains(Expression<Func<T, bool>> predicate)
{
return _dbSet.Count(predicate) > 0;
}

public virtual T Find(params object[] keys)
{
return _dbSet.Find(keys);
}

public virtual T Find(Expression<Func<T, bool>> predicate)
{
return _dbSet.FirstOrDefault(predicate);
}

public virtual T Create(T entity)
{
var newEntry = _dbSet.Add(entity);
return newEntry;
}

public virtual void Delete(object id)
{
var entityToDelete = _dbSet.Find(id);
Delete(entityToDelete);
}

public virtual void Delete(T entity)
{
if (_dbContext.Entry(entity).State == EntityState.Detached)
{
_dbSet.Attach(entity);
}
_dbSet.Remove(entity);
}

public virtual void Delete(Expression<Func<T, bool>> predicate)
{
var entitiesToDelete = Filter(predicate);
foreach (var entity in entitiesToDelete)
{
if (_dbContext.Entry(entity).State == EntityState.Detached)
{
_dbSet.Attach(entity);
}
_dbSet.Remove(entity);
}
}

public virtual void Update(T entity)
{
var entry = _dbContext.Entry(entity);
_dbSet.Attach(entity);
entry.State = EntityState.Modified;
}

public virtual IEnumerable<T> GetWithRawSql(string query, params object[] parameters)
{
return _dbSet.SqlQuery(query, parameters).ToList();
}
}

  • Lets create a IDbContextFactory interface  interface and implement it

Hash1png

We will use the DBContextFactory interface to handle multiple database or schemas, for example if a database contains multiple schemas, we will have the opportunity to work with multiple schemas within a single context.

  • Lets create a IUnitOfWork interfaceand implement it

Hash2

IUnitOfWork  interface will be used only to get Repositories, save context and finally  dispose objectsHash3

B. Implementation of MembershipProvider

Our CustomMembershipProvider derives from  MembershipProvider

The first thing we are doing is to override Initialize(string name, NameValueCollection config) so as to get config parameters and also get ApplicationId or create it if does not exist

Hash5

Try it :

public override void Initialize(string name, NameValueCollection config)
{
// Initialize values from web.config.
if (config == null)
throw new ArgumentNullException(« config »);

// Initialize the abstract base class.
base.Initialize(name, config);

// Get application name
if (config[« applicationName »] == null || config[« applicationName »].Trim() == «  »)
{
ApplicationName = System.Web.Hosting.HostingEnvironment.ApplicationVirtualPath;
}
else
{
ApplicationName = config[« applicationName »];
}

// Verify a record exists in the application table.
if ((_applicationId == Guid.Empty) || String.IsNullOrEmpty(_applicationName))
{
// Insert record for application.
if (_applicationId == Guid.Empty)
{
_applicationId = new MemberShipService().GetApplicationId(Guid.NewGuid(), _applicationName, String.Empty);
}
}
}

B.1  Encoding Password

Now lets create a random salt using SHA 512 string format CreateSalt512

In cryptography, a salt is random data that are used as an additional input to a one-way function that hashes a password or passphrase. The primary function of salts is to defend against dictionary attacks and pre-computed rainbow table attacks.

A new salt is randomly generated for each password. In a typical setting, the salt and the password are concatenated and processed with a cryptographic hash function, and the resulting output (but not the original password) is stored with the salt in a database. Hashing allows for later authentication while defending against compromise of the plaintext password in the event that the database is somehow compromised.

Hash6

Try it :

public static string RandomString(int size, bool lowerCase)
{
var builder = new StringBuilder();
var random = new Random();
for (int i = 0; i < size; i++)
{
char ch = Convert.ToChar(Convert.ToInt32(Math.Floor(26 * random.NextDouble() + 65)));
builder.Append(ch);
}
return lowerCase ? builder.ToString().ToLower() : builder.ToString();
}
private static string CreateSalt512()
{
var message = RandomString(512,false);
return BitConverter.ToString((new SHA512Managed()).ComputeHash(Encoding.ASCII.GetBytes(message))).Replace(« -« , «  »);
}

  • Now we will generate a hashed Password using our clear password and our resultes salt (secret key)

Hash7

Try it :

private string GenerateHMAC(string clearMessage, string secretKeyString)
{
var encoder = new ASCIIEncoding();
var messageBytes = encoder.GetBytes(clearMessage);
var secretKeyBytes = new byte[secretKeyString.Length / 2];
for (int index = 0; index < secretKeyBytes.Length; index++)
{
string byteValue = secretKeyString.Substring(index * 2, 2);
secretKeyBytes[index] = byte.Parse(byteValue, NumberStyles.HexNumber, CultureInfo.InvariantCulture);
}
var hmacsha512 = new HMACSHA512(secretKeyBytes);

byte[] hashValue = hmacsha512.ComputeHash(messageBytes);

string hmac = «  »;
foreach (byte x in hashValue)
{
hmac += String.Format(« {0:x2} », x);
}

return hmac.ToUpper();
}

  • Now we ‘ll use the GenerateHMAC function to Hash password and put in on database table

Hash8

Try it :

public override MembershipUser GetUser(string username, bool userIsOnline)
{
// Please implement it : check if user exist or not on database
return null;
}

public override MembershipUser CreateUser(string username,string password,string email,string passwordQuestion,string passwordAnswer,
bool isApproved,
object providerUserKey,
out MembershipCreateStatus status)
{
status = MembershipCreateStatus.Success;

MembershipUser u = GetUser(username, false);

if (u == null)
{
DateTime createDate = DateTime.UtcNow;
string salt = CreateSalt512();

var user = new aspnet_Users
{
UserId = new Guid(providerUserKey.ToString()),UserName = username,LoweredUserName = username.ToLowerInvariant(),ApplicationId = _applicationId,
IsAnonymous = false,
LastActivityDate = createDate,
MobileAlias = null,

aspnet_Membership = new aspnet_Membership
{
ApplicationId = _applicationId,Comment = null,CreateDate = createDate,Email = email,IsApproved = isApproved,IsLockedOut = false,
LastLoginDate = createDate,FailedPasswordAnswerAttemptCount = 0,LastLockoutDate = _minDate,
LoweredEmail = (email != null ? email.ToLowerInvariant() : null),
MobilePIN = null,Password = GenerateHMAC(password, salt),FailedPasswordAnswerAttemptWindowStart = _minDate,
FailedPasswordAttemptCount = 0,FailedPasswordAttemptWindowStart = _minDate,LastPasswordChangedDate = createDate,
PasswordSalt = salt,PasswordFormat = (int)MembershipPasswordFormat.Hashed,PasswordQuestion = passwordQuestion
}

};

new MemberShipService().CreateUser(user);
return GetUser(username, false);
}
status = MembershipCreateStatus.DuplicateUserName;
return null;
}

  • Now lets implement  our operations  of  MemberShipService class :

Hash9

Try it :

public Guid GetApplicationId(Guid id, string applicationName, string description)
{
using (var unitOfWork = new UnitOfWork(new DbContextFactory<LogCornerSecurityContext>()))
{
// Returns Application id exist
string loweredApplicationName = applicationName.ToLowerInvariant();
var result = unitOfWork.GetRepository<aspnet_Applications>()
.Get(a => a.LoweredApplicationName == loweredApplicationName).FirstOrDefault();

// Create it if does not exist
if (result == null)
{

result = unitOfWork.GetRepository<aspnet_Applications>().Create(new aspnet_Applications
{
ApplicationId = id,
ApplicationName = applicationName,
Description = description,
LoweredApplicationName = applicationName.ToLowerInvariant()
});
unitOfWork.Save();
}
return result.ApplicationId;
}
}

public void CreateUser(aspnet_Users user)
{
using (var unitOfWork = new UnitOfWork(new DbContextFactory<LogCornerSecurityContext>()))
{
unitOfWork.GetRepository<aspnet_Users>().Create(user);
unitOfWork.Save();
}
}

  •  Run our sample application and enter user informations, so password is hashed and stored as follows :

Login informations

Hash10

Runtime values

Hash11

Database table [dbo].[aspnet_Membership] valuesHash12

So we can see that password is hashed according to salt and stored in database.

To better understand and right implement salt, please seek for more information about How Hashes are Cracked ?  what happen if hacker try to decode password ?

Before using Salt Password Hashing, we must understand what is Dictionary and Brute Force Attacks, Reverse Lookup Tables, Lookup Tables and Rainbow Tables.

B.2  << Matching >> Password

Now, to << decode >> the password (if one can speak of decoding) we can simply hash the password provided by the user with the basic salt stored on database and compare the resulting hash with the hash stored in the database.

So one and only the user knows the password. If any one else can discover the password then we are the victim of a successful attack.

The validate function can look like this

Untitled pictureTry it :

public override bool ValidateUser(string username, string password)
{
// Get the user so as to find Salt and Hashed Password
aspnet_Users user = new MemberShipService().GetUser(username, _applicationId);

if (user != null)
{
// hash the password provided by the user with the basic salt stored on database and compare
// the resulting hash with the hash stored in the database

bool isAuthenticated = (user.aspnet_Membership.Password == GenerateHMAC(password, user.aspnet_Membership.PasswordSalt));
if (isAuthenticated)
{
// If success, then update datetime login
user.LastActivityDate = DateTime.Now;
user.aspnet_Membership.LastLoginDate = DateTime.Now;
UpdateUser(user);
}
else
{
// If not logged then update failure account so as to lock user
UpdateFailureCount(username, « password », isAuthenticated);
}
return isAuthenticated;
}
return false;
}

Now, what can happen if clear password travels on networks before being encoded ?

We will see this topic at next tutorial

Regards

Publicités

Using Advices and Poincuts in SPRING.NET and ASP.NET MVC

Mots-clefs

, , ,

In this tutorial we learn how to do dependency Injection using SPRING.NET,  Dependency injection (DI) is a process whereby objects define their dependencies, that is, the other objects they work with, only through constructor arguments and properties that are set on the object instance after it is constructed. (Factory methods may be considered a special case of providing constructor arguments for the purposes of this description). The container injects these dependencies when it creates the object. This process is fundamentally the inverse to the case when the object itself is controlling the instantiation or location of its dependencies by using direct construction of classes, or the Service Locator pattern. The inverting of this responsibility is why the name Inversion of Control (IoC) is used to describe the container’s actions.

This tutorial is a continuation of Aspect Oriented Programming using spring.NET, ASP.NET MVC

The principles covered in this tutorial will apply to creating ASP.NET MVC Web Site and inject services object in controller using SPRING.NET. We also use advices and pointcuts to intercept object behaviours

We are going to implement a before advice , after advice, around advice and throw advice

  • So open our project and reference Spring.AOP

advice1

  • The next step is to create our advices

advice2 advice3 advice4 advice5

  • Now lets create xml file aop.xml and define it Build Action as Embedded Ressource

advice6

  • Open spring-config-services.xml file and update it as follow :

advice7

Open our web.config file and add aop.xml file as follow :

advice8

  • Ok, we are at the end of this tutorial. So lets begin tests.

Make breakPoint at our advices and press F5 , the excetion workflow look like this : The method GetProducts of IProductService is intercepted Before, Around and after execution.

advice9

advice10

advice11

  • Lets now test our ThrowAdvice by a division by Zero and press F5 :

advice12

advice13

We can see that the exception is intercepted. So we log here all exceptions occured during execution of our service.

Thank you for feedbacks

Testing Tools for Visual Studio Application Lifecycle Management

Mots-clefs

, , , ,

Visual Studio Application Lifecycle Management (ALM) solution enables businesses to manage the entire life of the application development, reduce cycle times and eliminate waste in order to provide a continuous stream of business value.

When effectively implemented, ALM practices break down barriers between teams, enabling organizations to meet the challenges and to provide high quality software at a faster pace Companies using ALM also benefit from a greater reduction of waste, faster cycle times and greater agility.

During application development , it essential to answer the following questions:

  1. What is the probability of my application to crach ?
  2. Will my Infrastructure  be able to stand in front of a large amount of users?
  3. When  integrating a new software component , is it possible to have  negative impact on the existing one? .

In this tutorial, we are going to walk through 3 steps:

  • Validate and maintain the quality of our application through the unit tests. Next, we switch to using a test driven development (TDD) approach in which we write unit tests before writing the production code.
  • Simulate user load on a web application
  • Validate and maintain the quality of our application through the unit tests

We have different types of tests :

  1. C. Web Performance Testing
  2. D. Load Testing
  3. E. Code Coverage
  4. F. Ordered tests
  5. G. Testing and Team Foundation Server

A. Getting Started

There are actually many profiles of potential testers : all project developers will not have to perform load tests or web tests, this task is delegated to a small group of people.

We are using visual studio 2012 so as to be able to cover a large number of test case.

In contrast, people who perform unit tests will be more numerous..

It is therefore imperative to conduct a battery of tests to different aspects of the application. To identify gaps in earlier and be able to work their corrections cost, this exercise should take place throughout the development process.

B. Unit tests

Unit tests verify that every part of our application responds correctly to the functional requirements that have been expressed and it has no bug. Throughout the development of the application, re-running the tests automatically (when generating build as seen in the previous chapter) or when editing the corresponding module, we can ensure that we non-regression system.
Since Visual Studio 2005, Microsoft proposes using MSTest, its own testing framework, fully integrated into the IDE. The principle is simple: the attributes are used to decorate classes and methods and have written assertions. This is the conclusion of the latter indicate that if our test is passed or not.
In the following example, we will use the Authenticate class below. We will add it in a Project Class Library in the solution:

  • Create a project class Library and add our class Authenticate as a sample for testing.

public class Authenticate
{
public Authenticate(string email, string password)
{
this.Email = email;
this.Password = password;
}

private string _email;

public string Email
{
get { return _email; }
private set
{
string errorMessage;
if (!IsValidEmail(value, out errorMessage))
{
throw new ArgumentException(errorMessage);
}

_email = value;
}
}

private string _password;
public string Password
{
get { return _password; }
private set
{
string errorMessage;
if (!IsValidPassword(value, out errorMessage))
{
throw new ArgumentException(errorMessage);
}
_password = value;
}
}

public static bool IsValidPassword(string value,out string errorMessage)
{
errorMessage = string.Empty;
if(string.IsNullOrWhiteSpace(value))
{
errorMessage = « Password may not be null or empty. »;
return false;
}
return true;
}
public static bool IsValidEmail(string email, out string errorMessage)
{
if (string.IsNullOrWhiteSpace(email))
{
throw new ArgumentException(« Parameter email may not be null or empty. »);
}

errorMessage = « Your adress email is incorrect »;
Regex regex = new Regex(@ »^([\w\.\-]+)@([\w\-]+)((\.(\w){2,3})+)$ »);
Match match = regex.Match(email);
if (match.Success)
{
return true;
}
return false;
}

public void ChangePassword(string oldPassword, string newPassword)
{
if (oldPassword == Password)
{
Password = newPassword;
}
else
{
throw new ArgumentException( « The old password was not correct. »);
}
}
}

  • Next Add Project Unit Test as follow

UnitTest1

  • Now Create a UnitTest

UnitTest2

The first thing we will do is to test our constructor

UnitTestind1

It is very simple to write the test  : we pass parameters such as emai  and password and verfy that the values affected by the constructor and parameters are same.

Generally, we pass arguments to our methods, we get the result and check that it is consistent with what was expected

UnitTestind2

  • To run test, right click method name and choose appropriate test

UnitTestind3

  • We can also, open test explorer as follow

UnitTestind4

  • In test explorer Window, we have the result of our test but also many options. We let you discover them

UnitTestind5

We can also analyse Code Coverage so as to determine if all our code path is covered by our test case

UnitTestind6

We let you discover the streaming video ( surrounding red ) wich present briefly unit testing

UnitTestind7

C. Web Performance Testing

D. Load Testing

E. Code Coverage

F. Ordered tests

G. Testing and Team Foundation Server

The completed  article is coming soon . Please fill the form below to be get notifed

Introduction to entity framework Code first

Mots-clefs

, , , ,

Microsoft’s ADO.NET Entity Framework (EF) simplifies data access by allowing you to avoid working directly with the database in your code. Instead you can retrieve data by writing queries against strongly typed classes letting the Entity Framework handle the database interaction on your behalf. EF can also persist changes back to the database for you. In addition to this benefit, you will also benefit from the EF’s comprehension of relationships. This means you will not be required to write extra code to specify joins between entities when expressing queries or simply working with your objects in memory.

EF provides you with three ways to define the model of your entities. Using the database first workflow, you can begin with a legacy database to create a model. With the model first workflow, you  can design a model in a designer. Or you can simply define classes and let EF work with those—referred to as code first.

In tis tutorial, we are going to introduce Entity Framework using the Database Code First approach:

Then we start from an existing database, which lets us create the model by a simple drag and drop.

A : Entity Framwork  Code first  Using an existing Database

We are going to use aspnetdb (the default membeship database)

Here is database script :

– ————————————————–
– Creating all tables
– ————————————————–
– Creating table ‘aspnet_Applications’
CREATE TABLE [dbo].[aspnet_Applications] (
[ApplicationName] nvarchar(256) NOT NULL,
[LoweredApplicationName] nvarchar(256) NOT NULL,
[ApplicationId] uniqueidentifier NOT NULL,
[Description] nvarchar(256) NULL
);
GO
– Creating table ‘aspnet_Membership’
CREATE TABLE [dbo].[aspnet_Membership] (
[ApplicationId] uniqueidentifier NOT NULL,
[UserId] uniqueidentifier NOT NULL,
[Password] nvarchar(128) NOT NULL,
[PasswordFormat] int NOT NULL,
[PasswordSalt] nvarchar(128) NOT NULL,
[MobilePIN] nvarchar(16) NULL,
[Email] nvarchar(256) NULL,
[LoweredEmail] nvarchar(256) NULL,
[PasswordQuestion] nvarchar(256) NULL,
[PasswordAnswer] nvarchar(128) NULL,
[IsApproved] bit NOT NULL,
[IsLockedOut] bit NOT NULL,
[CreateDate] datetime NOT NULL,
[LastLoginDate] datetime NOT NULL,
[LastPasswordChangedDate] datetime NOT NULL,
[LastLockoutDate] datetime NOT NULL,
[FailedPasswordAttemptCount] int NOT NULL,
[FailedPasswordAttemptWindowStart] datetime NOT NULL,
[FailedPasswordAnswerAttemptCount] int NOT NULL,
[FailedPasswordAnswerAttemptWindowStart] datetime NOT NULL,
[Comment] nvarchar(max) NULL
);
GO
– Creating table ‘aspnet_Paths’
CREATE TABLE [dbo].[aspnet_Paths] (
[ApplicationId] uniqueidentifier NOT NULL,
[PathId] uniqueidentifier NOT NULL,
[Path] nvarchar(256) NOT NULL,
[LoweredPath] nvarchar(256) NOT NULL
);
GO
– Creating table ‘aspnet_PersonalizationAllUsers’
CREATE TABLE [dbo].[aspnet_PersonalizationAllUsers] (
[PathId] uniqueidentifier NOT NULL,
[PageSettings] varbinary(max) NOT NULL,
[LastUpdatedDate] datetime NOT NULL
);
GO
– Creating table ‘aspnet_PersonalizationPerUser’
CREATE TABLE [dbo].[aspnet_PersonalizationPerUser] (
[Id] uniqueidentifier NOT NULL,
[PathId] uniqueidentifier NULL,
[UserId] uniqueidentifier NULL,
[PageSettings] varbinary(max) NOT NULL,
[LastUpdatedDate] datetime NOT NULL
);
GO
– Creating table ‘aspnet_Profile’
CREATE TABLE [dbo].[aspnet_Profile] (
[UserId] uniqueidentifier NOT NULL,
[PropertyNames] nvarchar(max) NOT NULL,
[PropertyValuesString] nvarchar(max) NOT NULL,
[PropertyValuesBinary] varbinary(max) NOT NULL,
[LastUpdatedDate] datetime NOT NULL
);
GO
– Creating table ‘aspnet_Roles’
CREATE TABLE [dbo].[aspnet_Roles] (
[ApplicationId] uniqueidentifier NOT NULL,
[RoleId] uniqueidentifier NOT NULL,
[RoleName] nvarchar(256) NOT NULL,
[LoweredRoleName] nvarchar(256) NOT NULL,
[Description] nvarchar(256) NULL
);
GO
– Creating table ‘aspnet_SchemaVersions’
CREATE TABLE [dbo].[aspnet_SchemaVersions] (
[Feature] nvarchar(128) NOT NULL,
[CompatibleSchemaVersion] nvarchar(128) NOT NULL,
[IsCurrentVersion] bit NOT NULL
);
GO
– Creating table ‘aspnet_Users’
CREATE TABLE [dbo].[aspnet_Users] (
[ApplicationId] uniqueidentifier NOT NULL,
[UserId] uniqueidentifier NOT NULL,
[UserName] nvarchar(256) NOT NULL,
[LoweredUserName] nvarchar(256) NOT NULL,
[MobileAlias] nvarchar(16) NULL,
[IsAnonymous] bit NOT NULL,
[LastActivityDate] datetime NOT NULL
);
GO
– Creating table ‘aspnet_WebEvent_Events’
CREATE TABLE [dbo].[aspnet_WebEvent_Events] (
[EventId] char(32) NOT NULL,
[EventTimeUtc] datetime NOT NULL,
[EventTime] datetime NOT NULL,
[EventType] nvarchar(256) NOT NULL,
[EventSequence] decimal(19,0) NOT NULL,
[EventOccurrence] decimal(19,0) NOT NULL,
[EventCode] int NOT NULL,
[EventDetailCode] int NOT NULL,
[Message] nvarchar(1024) NULL,
[ApplicationPath] nvarchar(256) NULL,
[ApplicationVirtualPath] nvarchar(256) NULL,
[MachineName] nvarchar(256) NOT NULL,
[RequestUrl] nvarchar(1024) NULL,
[ExceptionType] nvarchar(256) NULL,
[Details] nvarchar(max) NULL
);
GO
– Creating table ‘aspnet_UsersInRoles’
CREATE TABLE [dbo].[aspnet_UsersInRoles] (
[aspnet_Roles_RoleId] uniqueidentifier NOT NULL,
[aspnet_Users_UserId] uniqueidentifier NOT NULL
);
GO
– ————————————————–
– Creating all PRIMARY KEY constraints
– ————————————————–
– Creating primary key on [ApplicationId] in table ‘aspnet_Applications’
ALTER TABLE [dbo].[aspnet_Applications]
ADD CONSTRAINT [PK_aspnet_Applications]
PRIMARY KEY CLUSTERED ([ApplicationId] ASC);
GO
– Creating primary key on [UserId] in table ‘aspnet_Membership’
ALTER TABLE [dbo].[aspnet_Membership]
ADD CONSTRAINT [PK_aspnet_Membership]
PRIMARY KEY CLUSTERED ([UserId] ASC);
GO
– Creating primary key on [PathId] in table ‘aspnet_Paths’
ALTER TABLE [dbo].[aspnet_Paths]
ADD CONSTRAINT [PK_aspnet_Paths]
PRIMARY KEY CLUSTERED ([PathId] ASC);
GO
– Creating primary key on [PathId] in table ‘aspnet_PersonalizationAllUsers’
ALTER TABLE [dbo].[aspnet_PersonalizationAllUsers]
ADD CONSTRAINT [PK_aspnet_PersonalizationAllUsers]
PRIMARY KEY CLUSTERED ([PathId] ASC);
GO
– Creating primary key on [Id] in table ‘aspnet_PersonalizationPerUser’
ALTER TABLE [dbo].[aspnet_PersonalizationPerUser]
ADD CONSTRAINT [PK_aspnet_PersonalizationPerUser]
PRIMARY KEY CLUSTERED ([Id] ASC);
GO
– Creating primary key on [UserId] in table ‘aspnet_Profile’
ALTER TABLE [dbo].[aspnet_Profile]
ADD CONSTRAINT [PK_aspnet_Profile]
PRIMARY KEY CLUSTERED ([UserId] ASC);
GO
– Creating primary key on [RoleId] in table ‘aspnet_Roles’
ALTER TABLE [dbo].[aspnet_Roles]
ADD CONSTRAINT [PK_aspnet_Roles]
PRIMARY KEY CLUSTERED ([RoleId] ASC);
GO
– Creating primary key on [Feature], [CompatibleSchemaVersion] in table ‘aspnet_SchemaVersions’
ALTER TABLE [dbo].[aspnet_SchemaVersions]
ADD CONSTRAINT [PK_aspnet_SchemaVersions]
PRIMARY KEY CLUSTERED ([Feature], [CompatibleSchemaVersion] ASC);
GO
– Creating primary key on [UserId] in table ‘aspnet_Users’
ALTER TABLE [dbo].[aspnet_Users]
ADD CONSTRAINT [PK_aspnet_Users]
PRIMARY KEY CLUSTERED ([UserId] ASC);
GO
– Creating primary key on [EventId] in table ‘aspnet_WebEvent_Events’
ALTER TABLE [dbo].[aspnet_WebEvent_Events]
ADD CONSTRAINT [PK_aspnet_WebEvent_Events]
PRIMARY KEY CLUSTERED ([EventId] ASC);
GO
– Creating primary key on [aspnet_Roles_RoleId], [aspnet_Users_UserId] in table ‘aspnet_UsersInRoles’
ALTER TABLE [dbo].[aspnet_UsersInRoles]
ADD CONSTRAINT [PK_aspnet_UsersInRoles]
PRIMARY KEY NONCLUSTERED ([aspnet_Roles_RoleId], [aspnet_Users_UserId] ASC);
GO
– ————————————————–
– Creating all FOREIGN KEY constraints
– ————————————————–
– Creating foreign key on [ApplicationId] in table ‘aspnet_Membership’
ALTER TABLE [dbo].[aspnet_Membership]
ADD CONSTRAINT [FK__aspnet_Me__Appli__145C0A3F]
FOREIGN KEY ([ApplicationId])
REFERENCES [dbo].[aspnet_Applications]
([ApplicationId])
ON DELETE NO ACTION ON UPDATE NO ACTION;
– Creating non-clustered index for FOREIGN KEY ‘FK__aspnet_Me__Appli__145C0A3F’
CREATE INDEX [IX_FK__aspnet_Me__Appli__145C0A3F]
ON [dbo].[aspnet_Membership]
([ApplicationId]);
GO
– Creating foreign key on [ApplicationId] in table ‘aspnet_Paths’
ALTER TABLE [dbo].[aspnet_Paths]
ADD CONSTRAINT [FK__aspnet_Pa__Appli__45F365D3]
FOREIGN KEY ([ApplicationId])
REFERENCES [dbo].[aspnet_Applications]
([ApplicationId])
ON DELETE NO ACTION ON UPDATE NO ACTION;
– Creating non-clustered index for FOREIGN KEY ‘FK__aspnet_Pa__Appli__45F365D3′
CREATE INDEX [IX_FK__aspnet_Pa__Appli__45F365D3]
ON [dbo].[aspnet_Paths]
([ApplicationId]);
GO
– Creating foreign key on [ApplicationId] in table ‘aspnet_Roles’
ALTER TABLE [dbo].[aspnet_Roles]
ADD CONSTRAINT [FK__aspnet_Ro__Appli__32E0915F]
FOREIGN KEY ([ApplicationId])
REFERENCES [dbo].[aspnet_Applications]
([ApplicationId])
ON DELETE NO ACTION ON UPDATE NO ACTION;
– Creating non-clustered index for FOREIGN KEY ‘FK__aspnet_Ro__Appli__32E0915F’
CREATE INDEX [IX_FK__aspnet_Ro__Appli__32E0915F]
ON [dbo].[aspnet_Roles]
([ApplicationId]);
GO
– Creating foreign key on [ApplicationId] in table ‘aspnet_Users’
ALTER TABLE [dbo].[aspnet_Users]
ADD CONSTRAINT [FK__aspnet_Us__Appli__0425A276]
FOREIGN KEY ([ApplicationId])
REFERENCES [dbo].[aspnet_Applications]
([ApplicationId])
ON DELETE NO ACTION ON UPDATE NO ACTION;
– Creating non-clustered index for FOREIGN KEY ‘FK__aspnet_Us__Appli__0425A276′
CREATE INDEX [IX_FK__aspnet_Us__Appli__0425A276]
ON [dbo].[aspnet_Users]
([ApplicationId]);
GO
– Creating foreign key on [UserId] in table ‘aspnet_Membership’
ALTER TABLE [dbo].[aspnet_Membership]
ADD CONSTRAINT [FK__aspnet_Me__UserI__15502E78]
FOREIGN KEY ([UserId])
REFERENCES [dbo].[aspnet_Users]
([UserId])
ON DELETE NO ACTION ON UPDATE NO ACTION;
GO
– Creating foreign key on [PathId] in table ‘aspnet_PersonalizationAllUsers’
ALTER TABLE [dbo].[aspnet_PersonalizationAllUsers]
ADD CONSTRAINT [FK__aspnet_Pe__PathI__4BAC3F29]
FOREIGN KEY ([PathId])
REFERENCES [dbo].[aspnet_Paths]
([PathId])
ON DELETE NO ACTION ON UPDATE NO ACTION;
GO
– Creating foreign key on [PathId] in table ‘aspnet_PersonalizationPerUser’
ALTER TABLE [dbo].[aspnet_PersonalizationPerUser]
ADD CONSTRAINT [FK__aspnet_Pe__PathI__4F7CD00D]
FOREIGN KEY ([PathId])
REFERENCES [dbo].[aspnet_Paths]
([PathId])
ON DELETE NO ACTION ON UPDATE NO ACTION;
– Creating non-clustered index for FOREIGN KEY ‘FK__aspnet_Pe__PathI__4F7CD00D’
CREATE INDEX [IX_FK__aspnet_Pe__PathI__4F7CD00D]
ON [dbo].[aspnet_PersonalizationPerUser]
([PathId]);
GO
– Creating foreign key on [UserId] in table ‘aspnet_PersonalizationPerUser’
ALTER TABLE [dbo].[aspnet_PersonalizationPerUser]
ADD CONSTRAINT [FK__aspnet_Pe__UserI__5070F446]
FOREIGN KEY ([UserId])
REFERENCES [dbo].[aspnet_Users]
([UserId])
ON DELETE NO ACTION ON UPDATE NO ACTION;
– Creating non-clustered index for FOREIGN KEY ‘FK__aspnet_Pe__UserI__5070F446′
CREATE INDEX [IX_FK__aspnet_Pe__UserI__5070F446]
ON [dbo].[aspnet_PersonalizationPerUser]
([UserId]);
GO
– Creating foreign key on [UserId] in table ‘aspnet_Profile’
ALTER TABLE [dbo].[aspnet_Profile]
ADD CONSTRAINT [FK__aspnet_Pr__UserI__29572725]
FOREIGN KEY ([UserId])
REFERENCES [dbo].[aspnet_Users]
([UserId])
ON DELETE NO ACTION ON UPDATE NO ACTION;
GO
– Creating foreign key on [aspnet_Roles_RoleId] in table ‘aspnet_UsersInRoles’
ALTER TABLE [dbo].[aspnet_UsersInRoles]
ADD CONSTRAINT [FK_aspnet_UsersInRoles_aspnet_Roles]
FOREIGN KEY ([aspnet_Roles_RoleId])
REFERENCES [dbo].[aspnet_Roles]
([RoleId])
ON DELETE NO ACTION ON UPDATE NO ACTION;
GO
– Creating foreign key on [aspnet_Users_UserId] in table ‘aspnet_UsersInRoles’
ALTER TABLE [dbo].[aspnet_UsersInRoles]
ADD CONSTRAINT [FK_aspnet_UsersInRoles_aspnet_Users]
FOREIGN KEY ([aspnet_Users_UserId])
REFERENCES [dbo].[aspnet_Users]
([UserId])
ON DELETE NO ACTION ON UPDATE NO ACTION;
– Creating non-clustered index for FOREIGN KEY ‘FK_aspnet_UsersInRoles_aspnet_Users’
CREATE INDEX [IX_FK_aspnet_UsersInRoles_aspnet_Users]
ON [dbo].[aspnet_UsersInRoles]
([aspnet_Users_UserId]);
GO
– ————————————————–
– Script has ended
– ————————————————–

  • Now , create a Class Library project
  • Add an ADO.NET Entity Data Model (.edmx  file )  Item

1

  • Choose Generate from database option because will generate our model from an existing database

2

  • Here, Will connect to SQL Server or another database Server, So Microsoft SQL SERVER as Data Source, if we use another database Server click change.
  • Type or select our Server Name (SQLEXPRESS) in our case
  • Select our authentication Mode
  • Select ASPNETDB Database (in our case, we are going to use this database for test),  If you do not have the ASPNETDB Database you can download it at the end tutorial

3

  • Check Table Objet, we no need Stored Procedures and views

4

  • Click finish to create our model.  The generated model will look like

1

  • Expand our model.edmx file, and  remove Model.Context.tt, Model.Designer.cs, Model.edmx.diagram and Model.tt

CodeFirst_1

  • Right Click Model.ebmx file and click  Add Code Generation Item

CodeFirst_4

  • Choose EF 5.x DbContext Fluent Generator ( or install it by nuget Packages if does not exists)

CodeFirst_5

  • Now expand your Model. edmx file, then you ll see that Model.Entiites.tt (contains our Database object generated as class files) and Model.Mapping.tt (contains mappings of our objects)

CodeFirst_6

  • Here, we are going to move our generated items to new folders or project so as to remove the model.edmx file and become independent of it)
  1. So Create a folder CodeFirstModel and move the content of Model.Entities.tt on it
  2. Create a folder CodeFirstMapping and move the content of Model.Mappings.tt on it
  3. Create a folder CodeFirstContext and move the file of Model.Context.cs on it

CodeFirst_7 CodeFirst_8 CodeFirst_9

  • now, we can delete our model.edmx file

CodeFirst_10

  • Finally, we can clean our connectionString by removing all metadata

CodeFirst_11

Now, we can start testing our work

CodeFirst_12

B : Entity Framwork  Code first  Without  an existing Database

In this case, we don’t  have an existing database (the goals is to enable a more code-centric option which we call “code first development”).

  • So, lets  create a class named Product (the sample is based on Nothwind Sample database)

CodeFirtsB_1

  • Create a Vendor Class as follow

CodeFirtsB_2

  • Create a ProductVendor class as follow. Note that a Product can have many Vendors and a vendor can sell many Products.

CodeFirtsB_3

  • Our next step is to define Mapping between classes so as to define our database objects.

Note that it is possible to do Code First without mapping relationships between classes . In this case our databases objects will be created as the names of our classes and database fields as the name of our class properties.

  • So lets mapp relationships between classes as follow :

for more information about Configuring Relationships between classes, please take a look at Configuring Relationships with the Fluent API

CodeFirtsB_4 CodeFirtsB_5png CodeFirtsB_6

  • Now define our DbContext like this :

CodeFirtsB_7

  • The line  below create the database if does not exist:

Database.SetInitializer<OurContext>(new CreateDatabaseIfNotExists<OurContext> ());

  • Now update our web.config file so as to set our Database name (in our case , we are setting the name as CommerceDatabase).
  • To test our sample, we can only execute the code below.CodeFirtsB_8
  • The result is that a databse whose name is CommerceDatabase  is created on our server .\SQLEXPRESS.
  • CodeFirtsB_10

C : Entity Framwork  Code first  Using UnitOfWork pattern

Plese see  Section A of our totorial ASP.NET MVC Custom Membership Password Hashing based on SALT key using SHA-3 Algorithm

Introduction to entity framework database first

Mots-clefs

, , , ,

Microsoft’s ADO.NET Entity Framework (EF) simplifies data access by allowing you to avoid working directly with the database in your code. Instead you can retrieve data by writing queries against strongly typed classes letting the Entity Framework handle the database interaction on your behalf. EF can also persist changes back to the database for you. In addition to this benefit, you will also benefit from the EF’s comprehension of relationships. This means you will not be required to write extra code to specify joins between entities when expressing queries or simply working with your objects in memory.

EF provides you with three ways to define the model of your entities. Using the database first workflow, you can begin with a legacy database to create a model. With the model first workflow, you  can design a model in a designer. Or you can simply define classes and let EF work with those—referred to as code first.

In tis tutorial, we are going to introduce Entity Framework using the Database First approach.

Then we start from an existing database, which lets us create the model by a simple drag and drop.

A : Entity Framwork database first Model Creation

  • So, lets create a Class Library project
  • Add an ADO.NET Entity Data Model (.edmx  file )  Item

1

  • Choose Generate from database option because will generate our model from an existing database

2

  • Here, Will connect to SQL Server or another database Server, So Microsoft SQL SERVER as Data Source, if we use another database Server click change.
  • Type or select our Server Name (SQLEXPRESS) in our case
  • Select our authentication Mode
  • Select ASPNETDB Database (in our case, we are going to use this database for test),  If you do not have the ASPNETDB Database you can download it at the end tutorial

3

  • Check Table Objet, we no need Stored Procedures and views

4

  • Click finish to create our model.  The generated model will look like

1

  • By right clicking our Model , we have possibilities to explore our database

2

  • Expand our model.edmx file, and Model.tt so we’ill see lot of generated classes that represent our database tables.

3

  • Expand Model.Context.tt , we have a generated class that represent our context.

We will use this context to manage database objects

4

  • reference EntityFramwork.dll (or get it by nuget Package)
  • Compile our project

The code below shows how to use our Model,

2

1

Database script

— ————————————————–
— Creating all tables
— ————————————————–

— Creating table ‘aspnet_Applications’
CREATE TABLE [dbo].[aspnet_Applications] (
[ApplicationName] nvarchar(256) NOT NULL,
[LoweredApplicationName] nvarchar(256) NOT NULL,
[ApplicationId] uniqueidentifier NOT NULL,
[Description] nvarchar(256) NULL
);
GO

— Creating table ‘aspnet_Membership’
CREATE TABLE [dbo].[aspnet_Membership] (
[ApplicationId] uniqueidentifier NOT NULL,
[UserId] uniqueidentifier NOT NULL,
[Password] nvarchar(128) NOT NULL,
[PasswordFormat] int NOT NULL,
[PasswordSalt] nvarchar(128) NOT NULL,
[MobilePIN] nvarchar(16) NULL,
[Email] nvarchar(256) NULL,
[LoweredEmail] nvarchar(256) NULL,
[PasswordQuestion] nvarchar(256) NULL,
[PasswordAnswer] nvarchar(128) NULL,
[IsApproved] bit NOT NULL,
[IsLockedOut] bit NOT NULL,
[CreateDate] datetime NOT NULL,
[LastLoginDate] datetime NOT NULL,
[LastPasswordChangedDate] datetime NOT NULL,
[LastLockoutDate] datetime NOT NULL,
[FailedPasswordAttemptCount] int NOT NULL,
[FailedPasswordAttemptWindowStart] datetime NOT NULL,
[FailedPasswordAnswerAttemptCount] int NOT NULL,
[FailedPasswordAnswerAttemptWindowStart] datetime NOT NULL,
[Comment] nvarchar(max) NULL
);
GO

— Creating table ‘aspnet_Paths’
CREATE TABLE [dbo].[aspnet_Paths] (
[ApplicationId] uniqueidentifier NOT NULL,
[PathId] uniqueidentifier NOT NULL,
[Path] nvarchar(256) NOT NULL,
[LoweredPath] nvarchar(256) NOT NULL
);
GO

— Creating table ‘aspnet_PersonalizationAllUsers’
CREATE TABLE [dbo].[aspnet_PersonalizationAllUsers] (
[PathId] uniqueidentifier NOT NULL,
[PageSettings] varbinary(max) NOT NULL,
[LastUpdatedDate] datetime NOT NULL
);
GO

— Creating table ‘aspnet_PersonalizationPerUser’
CREATE TABLE [dbo].[aspnet_PersonalizationPerUser] (
[Id] uniqueidentifier NOT NULL,
[PathId] uniqueidentifier NULL,
[UserId] uniqueidentifier NULL,
[PageSettings] varbinary(max) NOT NULL,
[LastUpdatedDate] datetime NOT NULL
);
GO

— Creating table ‘aspnet_Profile’
CREATE TABLE [dbo].[aspnet_Profile] (
[UserId] uniqueidentifier NOT NULL,
[PropertyNames] nvarchar(max) NOT NULL,
[PropertyValuesString] nvarchar(max) NOT NULL,
[PropertyValuesBinary] varbinary(max) NOT NULL,
[LastUpdatedDate] datetime NOT NULL
);
GO

— Creating table ‘aspnet_Roles’
CREATE TABLE [dbo].[aspnet_Roles] (
[ApplicationId] uniqueidentifier NOT NULL,
[RoleId] uniqueidentifier NOT NULL,
[RoleName] nvarchar(256) NOT NULL,
[LoweredRoleName] nvarchar(256) NOT NULL,
[Description] nvarchar(256) NULL
);
GO

— Creating table ‘aspnet_SchemaVersions’
CREATE TABLE [dbo].[aspnet_SchemaVersions] (
[Feature] nvarchar(128) NOT NULL,
[CompatibleSchemaVersion] nvarchar(128) NOT NULL,
[IsCurrentVersion] bit NOT NULL
);
GO

— Creating table ‘aspnet_Users’
CREATE TABLE [dbo].[aspnet_Users] (
[ApplicationId] uniqueidentifier NOT NULL,
[UserId] uniqueidentifier NOT NULL,
[UserName] nvarchar(256) NOT NULL,
[LoweredUserName] nvarchar(256) NOT NULL,
[MobileAlias] nvarchar(16) NULL,
[IsAnonymous] bit NOT NULL,
[LastActivityDate] datetime NOT NULL
);
GO

— Creating table ‘aspnet_WebEvent_Events’
CREATE TABLE [dbo].[aspnet_WebEvent_Events] (
[EventId] char(32) NOT NULL,
[EventTimeUtc] datetime NOT NULL,
[EventTime] datetime NOT NULL,
[EventType] nvarchar(256) NOT NULL,
[EventSequence] decimal(19,0) NOT NULL,
[EventOccurrence] decimal(19,0) NOT NULL,
[EventCode] int NOT NULL,
[EventDetailCode] int NOT NULL,
[Message] nvarchar(1024) NULL,
[ApplicationPath] nvarchar(256) NULL,
[ApplicationVirtualPath] nvarchar(256) NULL,
[MachineName] nvarchar(256) NOT NULL,
[RequestUrl] nvarchar(1024) NULL,
[ExceptionType] nvarchar(256) NULL,
[Details] nvarchar(max) NULL
);
GO

— Creating table ‘aspnet_UsersInRoles’
CREATE TABLE [dbo].[aspnet_UsersInRoles] (
[aspnet_Roles_RoleId] uniqueidentifier NOT NULL,
[aspnet_Users_UserId] uniqueidentifier NOT NULL
);
GO

— ————————————————–
— Creating all PRIMARY KEY constraints
— ————————————————–

— Creating primary key on [ApplicationId] in table ‘aspnet_Applications’
ALTER TABLE [dbo].[aspnet_Applications]
ADD CONSTRAINT [PK_aspnet_Applications]
PRIMARY KEY CLUSTERED ([ApplicationId] ASC);
GO

— Creating primary key on [UserId] in table ‘aspnet_Membership’
ALTER TABLE [dbo].[aspnet_Membership]
ADD CONSTRAINT [PK_aspnet_Membership]
PRIMARY KEY CLUSTERED ([UserId] ASC);
GO

— Creating primary key on [PathId] in table ‘aspnet_Paths’
ALTER TABLE [dbo].[aspnet_Paths]
ADD CONSTRAINT [PK_aspnet_Paths]
PRIMARY KEY CLUSTERED ([PathId] ASC);
GO

— Creating primary key on [PathId] in table ‘aspnet_PersonalizationAllUsers’
ALTER TABLE [dbo].[aspnet_PersonalizationAllUsers]
ADD CONSTRAINT [PK_aspnet_PersonalizationAllUsers]
PRIMARY KEY CLUSTERED ([PathId] ASC);
GO

— Creating primary key on [Id] in table ‘aspnet_PersonalizationPerUser’
ALTER TABLE [dbo].[aspnet_PersonalizationPerUser]
ADD CONSTRAINT [PK_aspnet_PersonalizationPerUser]
PRIMARY KEY CLUSTERED ([Id] ASC);
GO

— Creating primary key on [UserId] in table ‘aspnet_Profile’
ALTER TABLE [dbo].[aspnet_Profile]
ADD CONSTRAINT [PK_aspnet_Profile]
PRIMARY KEY CLUSTERED ([UserId] ASC);
GO

— Creating primary key on [RoleId] in table ‘aspnet_Roles’
ALTER TABLE [dbo].[aspnet_Roles]
ADD CONSTRAINT [PK_aspnet_Roles]
PRIMARY KEY CLUSTERED ([RoleId] ASC);
GO

— Creating primary key on [Feature], [CompatibleSchemaVersion] in table ‘aspnet_SchemaVersions’
ALTER TABLE [dbo].[aspnet_SchemaVersions]
ADD CONSTRAINT [PK_aspnet_SchemaVersions]
PRIMARY KEY CLUSTERED ([Feature], [CompatibleSchemaVersion] ASC);
GO

— Creating primary key on [UserId] in table ‘aspnet_Users’
ALTER TABLE [dbo].[aspnet_Users]
ADD CONSTRAINT [PK_aspnet_Users]
PRIMARY KEY CLUSTERED ([UserId] ASC);
GO

— Creating primary key on [EventId] in table ‘aspnet_WebEvent_Events’
ALTER TABLE [dbo].[aspnet_WebEvent_Events]
ADD CONSTRAINT [PK_aspnet_WebEvent_Events]
PRIMARY KEY CLUSTERED ([EventId] ASC);
GO

— Creating primary key on [aspnet_Roles_RoleId], [aspnet_Users_UserId] in table ‘aspnet_UsersInRoles’
ALTER TABLE [dbo].[aspnet_UsersInRoles]
ADD CONSTRAINT [PK_aspnet_UsersInRoles]
PRIMARY KEY NONCLUSTERED ([aspnet_Roles_RoleId], [aspnet_Users_UserId] ASC);
GO

— ————————————————–
— Creating all FOREIGN KEY constraints
— ————————————————–

— Creating foreign key on [ApplicationId] in table ‘aspnet_Membership’
ALTER TABLE [dbo].[aspnet_Membership]
ADD CONSTRAINT [FK__aspnet_Me__Appli__145C0A3F]
FOREIGN KEY ([ApplicationId])
REFERENCES [dbo].[aspnet_Applications]
([ApplicationId])
ON DELETE NO ACTION ON UPDATE NO ACTION;

— Creating non-clustered index for FOREIGN KEY ‘FK__aspnet_Me__Appli__145C0A3F’
CREATE INDEX [IX_FK__aspnet_Me__Appli__145C0A3F]
ON [dbo].[aspnet_Membership]
([ApplicationId]);
GO

— Creating foreign key on [ApplicationId] in table ‘aspnet_Paths’
ALTER TABLE [dbo].[aspnet_Paths]
ADD CONSTRAINT [FK__aspnet_Pa__Appli__45F365D3]
FOREIGN KEY ([ApplicationId])
REFERENCES [dbo].[aspnet_Applications]
([ApplicationId])
ON DELETE NO ACTION ON UPDATE NO ACTION;

— Creating non-clustered index for FOREIGN KEY ‘FK__aspnet_Pa__Appli__45F365D3’
CREATE INDEX [IX_FK__aspnet_Pa__Appli__45F365D3]
ON [dbo].[aspnet_Paths]
([ApplicationId]);
GO

— Creating foreign key on [ApplicationId] in table ‘aspnet_Roles’
ALTER TABLE [dbo].[aspnet_Roles]
ADD CONSTRAINT [FK__aspnet_Ro__Appli__32E0915F]
FOREIGN KEY ([ApplicationId])
REFERENCES [dbo].[aspnet_Applications]
([ApplicationId])
ON DELETE NO ACTION ON UPDATE NO ACTION;

— Creating non-clustered index for FOREIGN KEY ‘FK__aspnet_Ro__Appli__32E0915F’
CREATE INDEX [IX_FK__aspnet_Ro__Appli__32E0915F]
ON [dbo].[aspnet_Roles]
([ApplicationId]);
GO

— Creating foreign key on [ApplicationId] in table ‘aspnet_Users’
ALTER TABLE [dbo].[aspnet_Users]
ADD CONSTRAINT [FK__aspnet_Us__Appli__0425A276]
FOREIGN KEY ([ApplicationId])
REFERENCES [dbo].[aspnet_Applications]
([ApplicationId])
ON DELETE NO ACTION ON UPDATE NO ACTION;

— Creating non-clustered index for FOREIGN KEY ‘FK__aspnet_Us__Appli__0425A276’
CREATE INDEX [IX_FK__aspnet_Us__Appli__0425A276]
ON [dbo].[aspnet_Users]
([ApplicationId]);
GO

— Creating foreign key on [UserId] in table ‘aspnet_Membership’
ALTER TABLE [dbo].[aspnet_Membership]
ADD CONSTRAINT [FK__aspnet_Me__UserI__15502E78]
FOREIGN KEY ([UserId])
REFERENCES [dbo].[aspnet_Users]
([UserId])
ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

— Creating foreign key on [PathId] in table ‘aspnet_PersonalizationAllUsers’
ALTER TABLE [dbo].[aspnet_PersonalizationAllUsers]
ADD CONSTRAINT [FK__aspnet_Pe__PathI__4BAC3F29]
FOREIGN KEY ([PathId])
REFERENCES [dbo].[aspnet_Paths]
([PathId])
ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

— Creating foreign key on [PathId] in table ‘aspnet_PersonalizationPerUser’
ALTER TABLE [dbo].[aspnet_PersonalizationPerUser]
ADD CONSTRAINT [FK__aspnet_Pe__PathI__4F7CD00D]
FOREIGN KEY ([PathId])
REFERENCES [dbo].[aspnet_Paths]
([PathId])
ON DELETE NO ACTION ON UPDATE NO ACTION;

— Creating non-clustered index for FOREIGN KEY ‘FK__aspnet_Pe__PathI__4F7CD00D’
CREATE INDEX [IX_FK__aspnet_Pe__PathI__4F7CD00D]
ON [dbo].[aspnet_PersonalizationPerUser]
([PathId]);
GO

— Creating foreign key on [UserId] in table ‘aspnet_PersonalizationPerUser’
ALTER TABLE [dbo].[aspnet_PersonalizationPerUser]
ADD CONSTRAINT [FK__aspnet_Pe__UserI__5070F446]
FOREIGN KEY ([UserId])
REFERENCES [dbo].[aspnet_Users]
([UserId])
ON DELETE NO ACTION ON UPDATE NO ACTION;

— Creating non-clustered index for FOREIGN KEY ‘FK__aspnet_Pe__UserI__5070F446’
CREATE INDEX [IX_FK__aspnet_Pe__UserI__5070F446]
ON [dbo].[aspnet_PersonalizationPerUser]
([UserId]);
GO

— Creating foreign key on [UserId] in table ‘aspnet_Profile’
ALTER TABLE [dbo].[aspnet_Profile]
ADD CONSTRAINT [FK__aspnet_Pr__UserI__29572725]
FOREIGN KEY ([UserId])
REFERENCES [dbo].[aspnet_Users]
([UserId])
ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

— Creating foreign key on [aspnet_Roles_RoleId] in table ‘aspnet_UsersInRoles’
ALTER TABLE [dbo].[aspnet_UsersInRoles]
ADD CONSTRAINT [FK_aspnet_UsersInRoles_aspnet_Roles]
FOREIGN KEY ([aspnet_Roles_RoleId])
REFERENCES [dbo].[aspnet_Roles]
([RoleId])
ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

— Creating foreign key on [aspnet_Users_UserId] in table ‘aspnet_UsersInRoles’
ALTER TABLE [dbo].[aspnet_UsersInRoles]
ADD CONSTRAINT [FK_aspnet_UsersInRoles_aspnet_Users]
FOREIGN KEY ([aspnet_Users_UserId])
REFERENCES [dbo].[aspnet_Users]
([UserId])
ON DELETE NO ACTION ON UPDATE NO ACTION;

— Creating non-clustered index for FOREIGN KEY ‘FK_aspnet_UsersInRoles_aspnet_Users’
CREATE INDEX [IX_FK_aspnet_UsersInRoles_aspnet_Users]
ON [dbo].[aspnet_UsersInRoles]
([aspnet_Users_UserId]);
GO

— ————————————————–
— Script has ended
— ————————————————–

Thank you,

Related articles 

 

ASP.NET MVC Tracking User Activity

Mots-clefs

, , , , , , , ,

We can use filter attributes to mark any action method or controller. If the attribute marks a controller, the filter applies to all action methods in that controller.

Typically, we create an action or response filter by creating an attribute class that inherits from the abstract ActionFilterAttribute class.

Some built-in action filters, such as AuthorizeAttribute and HandleErrorAttribute, inherit from the FilterAttribute class.

Other action filters, such as OutputCacheAttribute, inherit from the abstract ActionFilterAttribute class, which enables the action filter to run either before or after the action method runs.

The following tutorial shows how to create a simple action filter that logs trace messages before and after an action method is called.

So Lets Create an ASP.NET MVC internet application project

  1. Add a class TraceFilterAttribute that inherit from ActionFilterAttribute
  2. Override OnActionExecuting and OnActionExecuted methods as follow

1

3. Finally apply  TraceFilterAttribute to your controller action on wich we want to enable trace

2

Run application, so you will see that each request is tracking during action and after action. And we can retrieve action name, parameters, and entire route description.

The only thing to do now is log user activity on file or database Table

4

3

4. Now Lets log runtime errors :

Our TraceFilterAttribute must implement OnException of IExceptionFilter

5

Each exception can now be logged.

Thank you for feedbacks

Related tutorials :