Click here to Skip to main content
15,868,099 members
Articles / Programming Languages / C#

SocialClub: A Sample application using C#.NET, Entity Framework 5 and LocalDb

Rate me:
Please Sign up or sign in to vote.
4.77/5 (48 votes)
4 Apr 2014CPOL6 min read 173K   13.5K   81   20
An introduction to basic CRUD operation using Entity framework code-first and LocalDB

Contents

Image 1

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

Image 2

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.

Image 3

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.

C#
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.

C#
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

C#
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.

XML
<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.

C#
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

C#
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.

C#
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;

C#
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.

C#
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().

C#
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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Ireland Ireland
Many years of experience in software design, development and architecture. Skilled in Microsoft .Net technology, Cloud computing, Solution Design, Software Architecture, Enterprise integration, Service Oriented and Microservices based Application Development. Currently, focusing on .Net Core, Web API, Microservices, Azure

Comments and Discussions

 
QuestionInterface for service Pin
doridd8-Mar-18 11:10
doridd8-Mar-18 11:10 
QuestionDatabase Connection string Pin
Dan Masaki6-Dec-16 3:46
Dan Masaki6-Dec-16 3:46 
QuestionHow to implement generic method for CRUD and separate Service layer and DAL Pin
Member 1091329028-Jul-16 23:09
Member 1091329028-Jul-16 23:09 
GeneralMy vote of 5 Pin
frikrishna8-Apr-16 10:52
frikrishna8-Apr-16 10:52 
QuestionMaking service generic Pin
Fidell22-Mar-16 0:18
Fidell22-Mar-16 0:18 
PraiseReally good kick start Pin
Adil S5-Dec-15 21:53
Adil S5-Dec-15 21:53 
GeneralMy vote of 5 Pin
mayank.saxena883-Mar-15 19:22
mayank.saxena883-Mar-15 19:22 
QuestionHow did you do to paint your pairs and impair rows with different colors Pin
Member 931612131-Jan-15 9:21
Member 931612131-Jan-15 9:21 
Questiondatabase is not created. Pin
kgpatel11-Sep-14 19:43
kgpatel11-Sep-14 19:43 
AnswerRe: database is not created. Pin
John-ph11-Sep-14 22:45
John-ph11-Sep-14 22:45 
GeneralRe: database is not created. Pin
kgpatel15-Sep-14 0:11
kgpatel15-Sep-14 0:11 
GeneralRe: database is not created. Pin
kgpatel14-Oct-14 18:15
kgpatel14-Oct-14 18:15 
QuestionBad connection to DB Pin
Member 1100403527-Aug-14 19:45
Member 1100403527-Aug-14 19:45 
AnswerRe: Bad connection to DB Pin
Member 1100403527-Aug-14 19:58
Member 1100403527-Aug-14 19:58 
GeneralRe: Bad connection to DB Pin
John-ph27-Aug-14 22:22
John-ph27-Aug-14 22:22 
QuestionThanks a lot. Pin
Anand Vashishtha30-Jun-14 23:24
Anand Vashishtha30-Jun-14 23:24 
GeneralRe: Thanks a lot. Pin
John-ph1-Jul-14 0:08
John-ph1-Jul-14 0:08 
QuestionGreat tutorial Pin
DuAell9-Apr-14 22:08
DuAell9-Apr-14 22:08 
GeneralMy vote of 5 Pin
cocis484-Apr-14 11:44
cocis484-Apr-14 11:44 
GeneralRe: My vote of 5 Pin
John-ph9-Apr-14 5:27
John-ph9-Apr-14 5:27 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.