Tutorial: Building a simple CRUD application using ASP.NET Webforms, Code First EF 4 and SQL CE 4


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.

I’ll add the source code link up soon.

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 CTP4  http://www.microsoft.com/downloads/en/details.aspx?FamilyID=4e094902-aeff-4ee2-a12d-5881d4b0dd3e&displaylang=en and
  3. Microsoft SQL Server CE 4.0 CTP http://www.microsoft.com/downloads/en/details.aspx?FamilyID=0d2357ea-324f-46fd-88fc-7364c80e4fdb&displaylang=en

I will hopefully host this solution soon and will post its link when it is up. So here we go.

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>
       Demo Team Site
    </h2>
    <p>
        <asp:ObjectDataSource ID="dsPlayer" runat="server" SelectMethod="GetAllPlayers"
        TypeName="TeamWebSite.Data.Player" DeleteMethod="DeletePlayer"
            UpdateMethod="EditPlayer">
            <DeleteParameters>
                <asp:Parameter Name="PlayerID" Type="Int32" />
            </DeleteParameters>
            <UpdateParameters>
                <asp:Parameter Name="PlayerID" Type="Int32" />
                <asp:Parameter Name="PlayerName" Type="String" />
                <asp:Parameter Name="PlayerDescription" Type="String" />
                <asp:Parameter Name="PlayerAge" Type="Int32" />
            </UpdateParameters>
        </asp:ObjectDataSource>
        <asp:GridView ID="gvPlayer" runat="server" AllowPaging="True"
        DataSourceID="dsPlayer" AutoGenerateColumns="False" DataKeyNames="PlayerID">
            <Columns>
                <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
                <asp:BoundField DataField="PlayerID" HeaderText="PlayerID"
                    SortExpression="PlayerID" />
                <asp:BoundField DataField="PlayerName" HeaderText="PlayerName"
                    SortExpression="PlayerName" />
                <asp:BoundField DataField="PlayerDescription" HeaderText="PlayerDescription"
                    SortExpression="PlayerDescription" />
                <asp:BoundField DataField="PlayerAge" HeaderText="PlayerAge"
                    SortExpression="PlayerAge" />
            </Columns>
        </asp:GridView>
        <asp:Button ID="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 partial class _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:Content ID="HeaderContent" runat="server" ContentPlaceHolderID="HeadContent">
</asp:Content>
<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
    <div>
        <asp:ObjectDataSource ID="dsPerson" runat="server" InsertMethod="CreatePlayer"
            SelectMethod="GetAllPlayers" TypeName="TeamWebSite.Data.Player">
        </asp:ObjectDataSource>
        <asp:FormView ID="fvPerson" runat="server" DataSourceID="dsPerson"
            DefaultMode="Insert" oniteminserted="fvPerson_ItemInserted" >
            <InsertItemTemplate>
                Name:
                <br />
                <asp:TextBox ID="PlayerNameTextBox" runat="server"
                    Text='<%# Bind("PlayerName") %>' />
                <asp:RequiredFieldValidator ID="rfPlayerName" runat="server" ControlToValidate="PlayerNameTextBox" ErrorMessage="Enter Name"></asp:RequiredFieldValidator>
                <br />
                Description:
                <br />
                <asp:TextBox ID="PlayerDescriptionTextBox" runat="server"
                    Text='<%# Bind("PlayerDescription") %>' />
                <br />
                Age:
                <br />
                <asp:TextBox ID="PlayerAgeTextBox" runat="server"
                    Text='<%# Bind("PlayerAge") %>' />
                <br />
                <asp:RequiredFieldValidator ID="rfPlayerAge" runat="server" ErrorMessage="Enter Age" ControlToValidate="PlayerAgeTextBox" ></asp:RequiredFieldValidator>
                <asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True"
                    CommandName="Insert" Text="Insert" />
                &nbsp;<asp:LinkButton ID="InsertCancelButton" runat="server"
                    CausesValidation="False" CommandName="Cancel" Text="Cancel" />
            </InsertItemTemplate>
        </asp:FormView>
        <asp:ValidationSummary ID="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 partial class 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 class TeamWebSite : DbContext
    {
        //EF Container 
        public DbSet<Player> players
        {
            get;
            set;
        }
    }

    //Standard old POCO class
    public class Player
    {
        public Player() { }

        public int PlayerID { getset; }

        public string PlayerName { getset; }
        public string PlayerDescription { getset; }
        public int PlayerAge { getset; }

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

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

        //Edit
        public void 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
            {
                throw new ApplicationException("Can not find the player");
            }
        }

        //Delete
        public void 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
            {
                throw new 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>
            {
                new Player { PlayerID= 1, PlayerName="John", PlayerAge=31, PlayerDescription="older player" },
                new Player { 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"/>

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 synchronisation is broken you’ll get errors. Our workaround for this is to go back to a base set of data – for prototypying 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:

Figure 3. The list of players after we have added a player

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.

I’ll continue in this series with an improved version of the tool built in MVC 3.0

Stay tuned

Dom

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

Advertisements

10 thoughts on “Tutorial: Building a simple CRUD application using ASP.NET Webforms, Code First EF 4 and SQL CE 4

  1. Pingback: Blog J.Schweiss | Getting started with SQLServer Compact 4.0

  2. Thanks for putting together this tutorial – it’s been very helpful in getting me started.

    I’m getting the following error when I try to start the application on line “using System.Data.Entity.Infrastructure;”

    Error:
    “The type or namespace name ‘Entity’ does not exist in the namespace ‘System.Data’ (are you missing an assembly reference?)”

    I’m assuming I’ve missed something very basic, but I can’t find it.

    • Mike – thanks for that – It looks like a bug unless you had an old EF4 CTP install on your machine (in the GAC). I have upgraded the app to EF4.1 and the new dll is now included with the source.

      Get the latest version from codeplex if you get a chance:

      http://teamwebsite.codeplex.com/

      I have noted in the comments for the latest version

      “Fix for the bug that Mike picked up – thanks for that Mike!!!! – this also includes an upgrade to EF4.1”. Let me know if you have any problems

      Cheers
      Dom

  3. Thank you for putting up this tutorial. I was checking out the codes, but I have a problem.

    I’m getting this error when I made the addition to global.asax:

    Error—1—The type name ‘Data’ does not exist in the type ‘TeamWebSite.TeamWebSite’

    Did I miss something?

  4. i can’t found the language c# when i will add new items -web form using master page- there just visual basic installed Templates, what should i do ? .. thank you

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s