Creating a Simple ASP.NET CRUD Application Using SQL CE 4 and Entity Framework Code First.

Introduction

The following shows how easy it is to create simple web sites using the new Code First Entity Framework, SQL Compact Edition and Web Forms. It must be said that a number of parts of this solution are still beta, so expect the unexpected.

I won't go into either Code First Entity Framework or SQL Compact Edition but check out Scott Guthrie's (see references) articles as they are fabulous on both topics.

I am using Webforms initially because I find it a very quick building simple sites using ASP.NET webform controls.

The site I am starting to build in this article is a sporting website for social teams. We are only going to see the very simple aspects of the Person management, but I will add more features in future articles as I switch to MVC.

You'll find more articles like this at http://www.domscode.com

What you will get out of this

1. An understanding of how easy it is to use Code First Entity Framework;
2. How easy it is to use the integrated SQL Server
3. How easy CRUD facility can be built with existing Webform controls

You will need:

1. Visual Studio 2010 Professional or higher (C#)
2. Microsoft ADO.NET Entity Framework Feature CTP4http://www.microsoft.com/downloads/en/details.aspx?FamilyID=4e094902-aeff-4ee2-a12d-5881d4b0dd3e&displaylang=en and
3. Microsoft SQL Server CE 4.0 CTPhttp://www.microsoft.com/downloads/en/details.aspx?FamilyID=0d2357ea-324f-46fd-88fc-7364c80e4fdb&displaylang=en

Building the Application:

• Start Visual Studio and create a new ASP.NET Web Application project called TeamWebSite
  
• After the following line:
  
  <asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
  
• Add the following:
  
  </h2>
  <p>
  <asp:ObjectDataSourceID="dsPlayer"runat="server"SelectMethod="GetAllPlayers"
  TypeName="TeamWebSite.Data.Player"DeleteMethod="DeletePlayer"
  UpdateMethod="EditPlayer">
  <DeleteParameters>
  <asp:ParameterName="PlayerID"Type="Int32"/>
  </DeleteParameters>
  <UpdateParameters>
  <asp:ParameterName="PlayerID"Type="Int32"/>
  <asp:ParameterName="PlayerName"Type="String"/>
  <asp:ParameterName="PlayerDescription"Type="String"/>
  <asp:ParameterName="PlayerAge"Type="Int32"/>
  </UpdateParameters>
  </asp:ObjectDataSource>
  <asp:GridViewID="gvPlayer" runat="server"AllowPaging="True"
  DataSourceID="dsPlayer"AutoGenerateColumns="False"DataKeyNames="PlayerID">
  <Columns>
  <asp:CommandFieldShowDeleteButton="True"ShowEditButton="True"/>
  <asp:BoundFieldDataField="PlayerID"HeaderText="PlayerID"
  SortExpression="PlayerID"/>
  <asp:BoundFieldDataField="PlayerName"HeaderText="PlayerName"
  SortExpression="PlayerName"/>
  <asp:BoundFieldDataField="PlayerDescription"HeaderText="PlayerDescription"
  SortExpression="PlayerDescription"/>
  <asp:BoundFieldDataField="PlayerAge"HeaderText="PlayerAge"
  SortExpression="PlayerAge"/>
  </Columns>
  </asp:GridView>
  <asp:ButtonID="AddPerson" runat="server"Text="Add"
  onclick="AddPerson_Click"/>
  </p>
  
  Ok so this is pretty simple, just two main controls. First of all the datasource. This is the object datasource for the page and all we specify is the type (or class) it is using for this datasource and then specifies the Type, the methods (edit and delete) and the parameters. The other control is the good old Gridview and as you can see we just ensure it specifies AutoPaging, AutogenerateColumns and the datakey (we need this so that the delete command can use the PlayerID to delete).
  
• Replace the following code behind of Default.aspx.cs with:
  
  using System;
  
  namespace TeamWebSite
  {
  public partialclass _Default : System.Web.UI.Page
  {
  protected void AddPerson_Click(object sender, EventArgs e)
  {
  Response.Redirect("AddPerson.aspx");
  }
  }
  }
  
  Pretty simple. Here we are just adding a click event handler to move to a new page and now let's add the only other page we need.
  
• Add to the project a new item: web form using master page called AddPerson.aspx and select the master page: Site.master
• Add the following between the <content> </content> tags:
  
  <%@Page Language="C#"AutoEventWireup="true"CodeBehind="AddPerson.aspx.cs"Inherits="TeamWebSite.AddPerson"MasterPageFile="~/Site.Master"%>
  <asp:ContentID="HeaderContent" runat="server"ContentPlaceHolderID="HeadContent">
  </asp:Content>
  <asp:ContentID="BodyContent" runat="server"ContentPlaceHolderID="MainContent">
  <div>
  <asp:ObjectDataSourceID="dsPerson"runat="server"InsertMethod="CreatePlayer"
  SelectMethod="GetAllPlayers"TypeName="TeamWebSite.Data.Player">
  </asp:ObjectDataSource>
  <asp:FormViewID="fvPerson" runat="server"DataSourceID="dsPerson"
  DefaultMode="Insert"oniteminserted="fvPerson_ItemInserted">
  <InsertItemTemplate>
  Name:
  <br/>
  <asp:TextBoxID="PlayerNameTextBox" runat="server"
  Text='<%# Bind("PlayerName") %>'/>
  <asp:RequiredFieldValidatorID="rfPlayerName"runat="server"ControlToValidate="PlayerNameTextBox"ErrorMessage="Enter Name"></asp:RequiredFieldValidator>
  <br/>
  Description:
  <br/>
  <asp:TextBoxID="PlayerDescriptionTextBox" runat="server"
  Text='<%# Bind("PlayerDescription") %>'/>
  <br/>
  Age:
  <br/>
  <asp:TextBoxID="PlayerAgeTextBox" runat="server"
  Text='<%# Bind("PlayerAge") %>'/>
  <br/>
  <asp:RequiredFieldValidatorID="rfPlayerAge"runat="server"ErrorMessage="Enter Age" ControlToValidate="PlayerAgeTextBox"></asp:RequiredFieldValidator>
  <asp:LinkButtonID="InsertButton"runat="server"CausesValidation="True"
  CommandName="Insert"Text="Insert"/>
  <asp:LinkButtonID="InsertCancelButton"runat="server"
  CausesValidation="False"CommandName="Cancel"Text="Cancel"/>
  </InsertItemTemplate>
  </asp:FormView>
  <asp:ValidationSummaryID="vsPerson"runat="server"/>
  </div>
  </asp:Content>
  
  You should see that again, there are two main controls on the page:
  
  
  1. Object Datasource - again we are just pointing to the type and specifying the insert command.
     
     
  2. FormView - Here we specify the datasource above and then create the markup with in the InsertItem template that allows us to create the container for all the controls we will use on the AddPerson.aspx page (see figure 2)
• Now just add the following as code behind
  
  using System.Web.UI.WebControls;
  
  namespace TeamWebSite
  {
  public partialclass AddPerson : System.Web.UI.Page
  {
  protected void fvPerson_ItemInserted(object sender, FormViewInsertedEventArgs e)
  {
  Response.Redirect("Default.aspx");
  }
  }
  }
  
  So nothing major here just a redirect back to the default when we have inserted the row.
  
  Now we can add the class holding our crud goodliness:
  
• Add a class to the Project and call it TeamSite.cs
  
• Add the following references to the project: C:Program Files (x86)Microsoft ADO.NET Entity Framework Feature CTP4BinariesMicrosoft.Data.Entity.CTP.dll and C:Program FilesMicrosoft SQL Server Compact Editionv4.0DesktopSystem.Data.SqlServerCe.dll
  
• Replace the code with:
  
  using System;
  using System.Collections.Generic;
  using System.Data.Entity;
  using System.Linq;
  using System.Data.Entity.Infrastructure;

  namespace TeamWebSite.Data
  {
  public classTeamWebSite : DbContext
  {
  //EF Container
  public DbSet<Player> players
  {
  get;
  set;
  }
  }

  //Standard old POCO class
  public class Player
  {
  public Player() { }
  
  public int PlayerID { get; set; }

  public string PlayerName { get; set; }
  public string PlayerDescription { get;set; }
  public int PlayerAge { get; set; }

  //Standard Select
  publicList<Player> GetAllPlayers()
  {
  TeamWebSite db =new TeamWebSite();
  return db.players.ToList<Player>();
  }

  //Insert
  publicvoid CreatePlayer(string PlayerName, string PlayerDescription,int PlayerAge)
  {
  TeamWebSite db =new TeamWebSite();
  db.players.Add(newPlayer { PlayerName = PlayerName, PlayerDescription = PlayerDescription, PlayerAge = PlayerAge });
  db.SaveChanges();
  }

  //Edit
  publicvoid EditPlayer(int PlayerID, string PlayerName,string PlayerDescription,int PlayerAge)
  {
  TeamWebSite db =new TeamWebSite();
  Player p = db.players.SingleOrDefault(player => player.PlayerID == PlayerID);
  if (p !=null)
  {
  p.PlayerName = PlayerName;
  p.PlayerAge = PlayerAge;
  p.PlayerID = PlayerID;
  db.SaveChanges();
  }
  else
  {
  thrownew ApplicationException("Can not find the player");
  }
  }

  //Delete
  publicvoid DeletePlayer(int PlayerID)
  {
  TeamWebSite db =new TeamWebSite();
  Player p = db.players.SingleOrDefault(player => player.PlayerID == PlayerID);
  if (p !=null)
  {
  db.players.Remove(p);
  db.SaveChanges();
  }
  else
  {
  thrownew ApplicationException("Can not find the player");
  }
  }
  }

  //Called by Global.asax to reload the database in a situation when the model changes from
  //the physical DB
  public class WebSiteInitializer : RecreateDatabaseIfModelChanges<TeamWebSite>
  {
  protected override void Seed(TeamWebSite context)
  {
  var Players =new List<Player>
  {
  newPlayer { PlayerID= 1, PlayerName="John", PlayerAge=31, PlayerDescription="older player" },
  newPlayer { PlayerID=2, PlayerName="sTEVE" , PlayerAge=23, PlayerDescription="asdasd" }
  };
  Players.ForEach(d => context.players.Add(d));
  }
  }
  }
  
  OK so it might look like a lot but break it down and it's not very complicated at all. There are 3 classes (Yes we should have them in separate files but to keep things all in one page for brevity I am being very very bad). The three classes:
  
  TeamWebSite - inherits from DBContext (part of the EF framework), publishes our main property: public DbSet<Player> players.
  Person - standard basic old POCO class. Nothing really special.
  
  WebSiteInitializer - class that we hook to from global.asax to make sure we keep the EF datamodel synced with the database.
  

• Add the connection string to web.config by adding the following within the <connectionstrings> tags:
  
  <add name="TeamWebSite" connectionString="Data Source=C:tempTeamWebSite.sdf" providerName="System.Data.SqlServerCe.4.0"/>
  
  Just remember, make sure the directory exists for the datasource.
  
  One last thing - one of the realities of code first is that you need to be able to maintain the link between the datamodel and the database at all times. When this synchronization is broken you'll get errors. Our workaround for this is to go back to a base set of data - for prototyping scenarios this should be fine.
  
• Edit the global.asax and add the following using:
  
  using System.Data.Entity.Infrastructure;
  
• replace Application_Start with:
  
  void Application_Start(object sender, EventArgs e)
  {
  // Code that runs on application startup
  Database.SetInitializer<TeamWebSite.Data.TeamWebSite>(new TeamWebSite.Data.WebSiteInitializer());
  }
  
  Having a look at the application :
  
• Now start up the application (F5) and you should see:
  
  
  
• Click Add you'll see:
  
  
  
• Saving a record and you'll see:
  
  
  

In Conclusion:

So this was pretty simple but I think you get the idea that you have quickly built, a simple, self contained CRUD web application, using a range of new tools.

References:

http://weblogs.asp.net/scottgu/archive/2010/07/16/code-first-development-with-entity-framework-4.aspx
http://weblogs.asp.net/scottgu/archive/2010/06/30/new-embedded-database-support-with-asp-net.aspx