Scheduler .NET with Entity Framework Database First

Scheduler .NET with Entity Framework Database First

There are three ways you can work with data in the Entity Framework: Model First, Code First and Database First. This tutorial is for Database First. This topic shows how to start with an existing database and quickly create a simple event calendar in ASP.NET with basic views and functionality like on the picture below:

 entity framework database first calendar

It uses the Entity Framework 6 and MVC 5 to build the web application. This topic addresses the situation where you have a database and want to generate code for a web application based on the fields of that database. This approach is called Database First development. If you do not already have an existing database, you can instead use an approach called Code First development which involves defining data classes and generating the database from the class properties.

Step 1. Create an MVC Web Application

Open Visual Studio and create a new C# Web project named “EventCalendarEFDatabaseFirst”.

ef database mvc calendar

ef database tutorial

Step 2. Install packages via NuGet

Right click on your project in the solution explorer and select Manage NuGet Packages. This will open Manage NuGet Packages dialogue box. Now, select Online in the left bar and search for DHTMLX Scheduler .NET for ASP.NET and EntityFramework as shown below.

 install scheduler from nuget

 install ef from Nuget

Step 3. Create the Controller and View

At this step, create a controller and name it CalendarController.cs. It includes the default Index() method. Change the controller code to the following:

    public class CalendarController : Controller
    {
        public ActionResult Index()
        {
            var sched = new DHXScheduler(this);
            sched.Skin = DHXScheduler.Skins.Terrace;
            sched.LoadData = true;
            sched.EnableDataprocessor = true;
            sched.InitialDate = new DateTime(2016, 5, 5);
            return View(sched);
        }
    }

Right click the Views\Calendar folder and click Add, then click MVC 5 View Page with (Layout Razor). Add the following markup.

@{
    Layout = null;
}
 
 
<!DOCTYPE html>
<html>
<head>
    <title>DHXScheduler initialization sample</title>
    <style>
        body {
            background-color: #eee;
        }
    </style>
</head>
<body>
    <div style="height:700px;width:900px;margin:0 auto">
        @Html.Raw(Model.Render())
    </div>
</body>
</html>

Step 4. Set up a database

At this step, create a database to store the calendar events. In this database create the table 'Events' with the following fields (all fields are mandatory):

  • id - (int; primary key, identity) the ID of the event;
  • text - (nvarchar(256)) the description of the event;
  • start_date - (datetime) the date and the time the event starts;
  • end_date - (datetime) the date and the time the event ends.
CREATE TABLE [Events](
  [id] int IDENTITY(1,1) NOT NULL,
  [text] nvarchar(256) NULL,
  [start_date] datetime NOT NULL,
  [end_date] datetime NOT NULL,
  PRIMARY KEY (id)
)

While the DB tool is open, you can add some data to the table.

Step 5. Generate the models

You will now create Entity Framework models from the database tables. These models are classes that you will use to work with the data. Each model mirrors a table in the database and contains properties that correspond to the columns in the table.

Right-click the Models folder, and select Add and New Item. In the Add New Item window, select Data in the left pane and ADO.NET Entity Data Model from the options in the center pane. Name the new model file Events.

 ado.net entity data model

Click Add. In the Entity Data Model Wizard, select Generate from database.

 entity data model wizard

Click Next. If you have database connections defined within your development environment, you may see one of these connections pre-selected. However, you want to create a new connection to the database you created in the first part of this tutorial. Click the New Connection button.

 new connection button

In the Connection Properties select the CalendarDb.mdf from the available databases.

 calendar db connection properties

Click OK. The correct connection properties are now displayed. You can use the default name for connection to the Web.Config file

 default name for connection

Click Next. Select Tables to generate models for all three tables.

 tables to generate models

Click Finish. If you receive a security warning, select OK to continue running the template. The models are generated from the database tables, and a diagram is displayed that shows the properties.

database tables

Step 6. Manipulation with scheduler

At this stage we need to update the data load and save in the controller CalendarController.cs. Update data load and processing:

public ContentResult Data()
        {
            return (new SchedulerAjaxData(
                new CalendarContext().Events
                .Select(e => new { e.id, e.text, e.start_date, e.end_date })
                )
                );
        }

Add the required entities to enable data save:

public ContentResult Save(int? id, FormCollection actionValues)
        {
            var action = new DataAction(actionValues);
            var changedEvent = DHXEventsHelper.Bind<Event>(actionValues);
            var entities = new CalendarContext();
            try
            {
                switch (action.Type)
                {
                    case DataActionTypes.Insert:
                        entities.Events.Add(changedEvent);
                        break;
                    case DataActionTypes.Delete:
                        changedEvent = entities.Events.FirstOrDefault(ev => ev.id == action.SourceId);
                        entities.Events.Remove(changedEvent);
                        break;
                    default:// "update"
                        var target = entities.Events.Single(e => e.id == changedEvent.id);
                        DHXEventsHelper.Update(target, changedEvent, new List<string> { "id" });
                        break;
                }
                entities.SaveChanges();
                action.TargetId = changedEvent.id;
            }
            catch (Exception a)
            {
                action.Type = DataActionTypes.Error;
            }
 
 
            return (new AjaxSaveResponse(action));
        }

Here is the full code:

public class CalendarController : Controller
    {
        public ActionResult Index()
        {
            var sched = new DHXScheduler(this);
            sched.Skin = DHXScheduler.Skins.Terrace;
            sched.LoadData = true;
            sched.EnableDataprocessor = true;
            sched.InitialDate = new DateTime(2016, 5, 5);
            return View(sched);
        }
 
 
        public ContentResult Data()
        {
            return (new SchedulerAjaxData(
                new CalendarDbEntities().Events
                    .Select(e => new { e.id, e.text, e.start_date, e.end_date })
                )
            );
        }
 
 
        public ContentResult Save(int? id, FormCollection actionValues)
        {
            var action = new DataAction(actionValues);
            var changedEvent = DHXEventsHelper.Bind<Events>(actionValues);
            var entities = new CalendarDbEntities();
            try
            {
                switch (action.Type)
                {
                    case DataActionTypes.Insert:
                        entities.Events.Add(changedEvent);
                        break;
                    case DataActionTypes.Delete:
                        changedEvent = entities.Events.FirstOrDefault(ev => ev.id == action.SourceId);
                        entities.Events.Remove(changedEvent);
                        break;
                    default:// "update"
                        var target = entities.Events.Single(e => e.id == changedEvent.id);
                        DHXEventsHelper.Update(target, changedEvent, new List<string> { "id" });
                        break;
                }
                entities.SaveChanges();
                action.TargetId = changedEvent.id;
            }
            catch (Exception a)
            {
                action.Type = DataActionTypes.Error;
            }
 
 
            return (new AjaxSaveResponse(action));
        }
    }

Finally, open RouteConfig.cs in the App_Start folder and update the controller route from 'Home' to 'Calendar' as it shown below:

public static void RegisterRoutes(RouteCollection routes)
        {
            routes.IgnoreRoute("{resource}.axd/{*pathInfo}");
 
 
            routes.MapRoute(
                name: "Default",
                url: "{controller}/{action}/{id}",
                defaults: new { controller = "Calendar", action = "Index", id = UrlParameter.Optional }
            );
        }

An event calendar with Entity Framework is ready. Check it in the browser and try to create events.


comments powered by Disqus