Contents
Introduction
This article provides an introduction to basic CRUD operation using Entity framework (EF) Code-Frist development with LocalDB (SQL Express). The SocialClub sample application provided here is based on my previous article[^]. The presentation layer (UI) remains the same but the Data layer (Data Access and Service logic) changed to demonstrate Entity framework code first.
Software environment
- Development environment : Visual Studio.NET 2012
- Framework : .NET Framework 4.5
- User Interface : WinForms
- Programming language : C#.NET
- Data Access : Entity Framework 5
- Database : LocalDB (Sql Server)
Pre-Requisite
Visual Studio.NET 2012 is required to run the sample application and basic knowledge of .NET Framework, C#.NET and Sql Server is required to get started.
Application Structure
- Presentation Layer: The presentation layer contains components needed to interact with the user of the application.
- Data Access Layer: The data access layer provides a simple API for accessing and manipulating data. The component typically provides methods to perform Create, Read, Update, and Delete (CRUD) operations for a specific business entity. Logical separation of data access class component is shown in the below diagram (middle layer) and is discussed in the further section
- Database: Database layer contains Database with table structure & data
LocalDB
LocalDB is a new version of SQL Express that comes with .NET Frameowrk 4.5 which is specifically created for developers. If you have installed Visual Studio 2012 then you already have LocalDB. In the visual studio 2012, Go to View menu -> select Sql Server Object Explorer. Right click on SQL Server and select Add SQL Server, this will prompt for a Sql Server name. Provide the server name as (localdb)\v11.0 and click on connect. You are connected to LocalDb. It provides a programming surface for running T-SQL similar to SQL Express. You will find more information about LocalDb in this MSDN blog[^].
Below shown is a sample screen shot of LocalDb from visual studio 2012.
Data Access Layer
Code First
Code-First is an approach to data Modelling. First step in this approach is to create the POCO (Plain-Old-CLR-Object) classes (Business Entities) that represents the domain model and then create the database from these POCO classes.
Business Entity (POCO)
Social Club is a small application that manages the club membership details. We just need one POCO class with the necessary properties that represents the membership details that will allow to create and store "Club Members" information.
public class ClubMember
{
public int Id { get; set; }
public string Name { get; set; }
public DateTime DateOfBirth { get; set; }
public int Occupation { get; set; }
public Nullable<decimal> Salary { get; set; }
public int MaritalStatus { get; set; }
public int HealthStatus { get; set; }
public Nullable<int> NumberOfChildren { get; set; }
}
DbContext
The second step is to define a class derived from DbContext class. This Context class exposes collection of entities. DbContext and DbSet are the two primary types that are part of entity framework library used for interacting with the data. The DbContext class manages the connection with the database. It opens and closes the connections as needed.
public class SocialClubDbContext : DbContext
{
public SocialClubDbContext()
: base("SocialClub.DbConnection")
{
Database.SetInitializer<socialclubdbcontext>(new SocialClubInitializer());
Configuration.ProxyCreationEnabled = false;
}
public DbSet<clubmember> ClubMembers { get; set; }
}
Database Initialization
You can see in the above code, a database initializer is called in the constructor to initialize the database. Entity framework provides different options to initialize the database when using code-first approach. Following are the available options
- CreateDatabaseIfNotExists: This initializer creates the database if database doesn't exist
- DropCreateDatabaseIfModelChanges: This initializer drops/deletes the existing database and creates new database if the model classes has been changed
- DropCreateDatabaseAlways: This initializer drops/deletes an existing database every time you run the application irrespective of whether the model classes have changed or not
- Custom DB initializer: We can create our own initializer class derived from one of the above option and override the seed method to perform custom initialization.
Below shown code is a custom DB Initializer class for the sample application that is derived from CreateDatabaseIfNotExists and the class overrides the seed method to initialize the DB
public class SocialClubInitializer : CreateDatabaseIfNotExists<socialclubdbcontext>
{
protected override void Seed(SocialClubDbContext context)
{
var clubMembers = new List<clubmember>{
new ClubMember {
Name = "Pete Darson",
Occupation = (int)Occupation.Doctor,
HealthStatus = (int)HealthStatus.Good,
MaritalStatus = (int)MaritalStatus.Married,
NumberOfChildren = 2,
DateOfBirth = new DateTime(1982,07,12),
Salary = 5500
},
new ClubMember {
Name = "Mat Pearson",
Occupation = (int)Occupation.Engineer,
HealthStatus = (int)HealthStatus.Excellent,
MaritalStatus = (int)MaritalStatus.Single,
NumberOfChildren = 0,
DateOfBirth = new DateTime(1980,05,21),
Salary = 3500
}
};
clubMembers.ForEach(category => context.ClubMembers.Add(category));
}
}
Connection String
An App.config file is added to the project, this file contains the database Connection string. DbContext class when interacting with database will try to find the connection string from the configuration file.When deriving the context class from DbContext, we can either pass the connection name as a parameter to base constructor or we can use parameterless constructor. when parameterless constructor is used, the name of the connection string should matche the name of the context class which DbContext takes it as default connection. If the name is different, then we need to pass the connection name in the constructor to tell the DbContext to use the given connection.
Below provided is the connection string added to app.config file which specify the LocalDb as data source. If you prefer to use SQL Server, then the connection string in the app.config file can be modified as required.
<connectionStrings>
<add name="SocialClub.DbConnection"
providerName="System.Data.SqlClient"
connectionString="Data Source=(LocalDb)\v11.0;Initial Catalog=SocialClub;Integrated Security=true" />
</connectionStrings>
Refer the derived DbContext class above, You will notice that the default constructor calls a base constructor using the "name={connectionstringname}" syntax. This indicates that the connection string with the specified name should be loaded from the configuration file to use for this context.
Extension Method
An extension method is added to convert the "IList" collection to DataTable and a record to DataRow. This is to bridge the gap between the Data layer and presentation layer as we are using same sample application provided in the previous article. The UI is bound to DataTable and DataRow so, the below extension methods helps converting the data to required type.
public static DataTable ToDataTable<t>(this IList<t> data)
{
PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));
DataTable table = new DataTable();
foreach (PropertyDescriptor prop in properties)
{
table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType)
?? prop.PropertyType);
}
foreach (T item in data)
{
DataRow row = table.NewRow();
foreach (PropertyDescriptor prop in properties)
{
row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
}
table.Rows.Add(row);
}
return table;
}
public static DataRow ToDataRow<t>(this T data)
{
PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));
DataTable table = new DataTable();
foreach (PropertyDescriptor prop in properties)
{
table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType)
?? prop.PropertyType);
}
DataRow row = table.NewRow();
foreach (PropertyDescriptor prop in properties)
{
row[prop.Name] = prop.GetValue(data) ?? DBNull.Value;
}
table.Rows.Add(row);
return table.Rows[0];
}
Accessing and manipulating data using LINQ
The service class in the data layer implements the interface to query the Dbcontext for accessing and updating the data. Once the context class is ready, we can query the Dbcontext to create, retrieve, update and delete the records. To access the data, we need to access the DbSet property of context object. Accessing a DbSet property on a context object returns all entities of the specified type. Let us create an interface for the club member service and start implementing some of the interface to see how to access and manipulate data using LINQ
public interface IClubMemberService
{
DataRow GetById(int Id);
DataTable GetAll();
DataTable Search(int occupation, int maritalStatus, string operand);
bool Create(ClubMember clubMember);
bool Update(ClubMember clubMember);
bool Delete(int id);
}
Creating a new record
The code below shows how to create and add a new ClubMember to socialClub database. Create a new ClubMember model and set its properties, and then add it to the ClubMembers property of the SocialClubDbContext context object and then call the SaveChanges method to save the changes to database.
public bool Create(ClubMember clubMember)
{
using (var context = new SocialClubDbContext())
{
context.ClubMembers.Add(clubMember);
return context.SaveChanges() > 0;
}
}
Retrieving a record
To retrive a single record, we need to query the ClubMember DbSet property with a "where" clause that filters the sequence of values based on the condition. In the below sample, the "where" clause will return only one element, so, we call SingleOrDefault() method to get the only element of the sequence or the Default value if the sequence is empty;
public DataRow GetById(int id)
{
using (var context = new SocialClubDbContext())
{
ClubMember membership = context.ClubMembers
.Where(i => i.Id == id)
.SingleOrDefault();
return membership.ToDataRow<clubmember>();
}
}
Updating a record
To update a record, attach the updated record to the corresponding DBSet property and set the entity state to EntityState.Modified and finally, call the SaveChanges() method to save the changes to the database.
public bool Update(ClubMember clubMember)
{
using (var context = new SocialClubDbContext())
{
context.ClubMembers.Attach(clubMember);
context.Entry(clubMember).State = EntityState.Modified;
return context.SaveChanges() > 0;
}
}
Deleting a record
There are different ways to delete a record. Say, you can attach the record and set the entity state to EnityState.Deleted and save the changes to the database which is similar to what we did in the above "Updating a record". Another way (see sample code below) is to find the record and pass the record to the "Remove" method of the DbSet property and call SaveChanges().
public bool Delete(int id)
{
using (var context = new SocialClubDbContext())
{
var clubMember = context.ClubMembers.Find(id);
context.ClubMembers.Remove(clubMember);
return context.SaveChanges() > 0;
}
}
Reference
Entity Framework Tutorial
