Render Gantt with Entity framework
In ASP.NET MVC Gantt Chart, we can load data from SQL database using ADO.NET Entity Data Model and can update the changes to the SQL database on CRUD operations.
Please refer following link to create the ADO.NET Entity Data Model in Visual studio,
SQL database connection in Gantt can be established by using data model entity class. The Data Model entity class name is used as the connectionString name to establish the connection with the SQL database. The ‘connectionStrings’ will be automatically create in our web.config file once entity data model generated.
Please refer following code snippet for adding SQL connection in web.config
<connectionStrings> <add name="GanttDataSourceEntities" connectionString="metadata=res://*/Models.Model1.csdl|res://*/Models.Model1.ssdl|res://*/Models.Model1.msl;provider=System.Data.SqlClient;provider connection string="data source=(LocalDB)\MSSQLLocalDB;attachdbfilename=|DataDirectory|\GanttDataSource.mdf;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework"" providerName="System.Data.EntityClient" /> </connectionStrings>
CRUD actions are performed by entity classes to update Gantt data using the client side event ActionComplete and with AJAX post method. The event argument requestType of the ActionComplete client side event is used to identify the type of action performed in Gantt such as update, delete, add new task. Please refer the below code example for updating the changes to the SQL database.
@(Html.EJ().Gantt("Gantt")
.Datasource(ViewBag.dataSource)
.ClientSideEvents(eve => {
eve.ActionComplete("ActionComplete");
}).
//...
)@(Html.EJ().ScriptManager())
<script type = "text/javascript">
function ActionComplete(args) {
//To update on indent,outdent,taskbar editing and predecessor drawn action
if (args.requestType == "indent" || args.requestType == "outdent" || args.requestType == "recordUpdate" || (args.requestType === 'save' && args.modifiedRecord) || args.requestType == "drawConnectorLine") {
var ganttRec = [];
// Dialog Editing
if (args.requestType == "save")
ganttRec.push(args.modifiedRecord);
else if (args.requestType == "drawConnectorLine")
ganttRec.push(args.currentRecord);
else
ganttRec.push(args.data); // Cell Editing
if (args.updatedRecords && args.updatedRecords.length)
ganttRec = ganttRec.concat(args.updatedRecords);
updateModifiedGanttRecords(ganttRec);
}
//Newly Added Record is obtained here , which can be updated to database
else if (args.requestType == "save" && args.addedRecord) {
var data = args.addedRecord.item;
$.ajax({
type: "POST",
url: '/Gantt/Add',
contentType: "application/json; charset=utf-8",
data: JSON.stringify(data),
dataType: "json",
});
// args.updatedRecords will have the array of dependent hierarchy of modified
//parents record when add new record as child of another record.
if (args.updatedRecords && args.updatedRecords.length)
updateModifiedGanttRecords(args.updatedRecords);
}
else if (args.requestType == "dragAndDrop") {
var ganttRec = [];
ganttRec.push(args.draggedRow);
if (args.updatedRecords && args.updatedRecords.length)
ganttRec = ganttRec.concat(args.updatedRecords);
updateModifiedGanttRecords(ganttRec);
}
//To update the database on delete action
else if (args.requestType == "delete") {
var data = args.data.item;
$.ajax({
type: "POST",
url: '/Gantt/Delete',
contentType: "application/json; charset=utf-8",
data: JSON.stringify(data),
dataType: "json",
});
if (args.data.hasChildRecords) {
deleteChildRecords(args.data);
}
if (args.updatedRecords && args.updatedRecords.length)
updateModifiedGanttRecords(args.updatedRecords);
}
}
// To update the multiple modified Gantt records in single request.
function updateModifiedGanttRecords(records) {
var modifiedRecord = [];
if (records && records.length) {
var length = records.length;
for (var i = 0; i < length; i++)
modifiedRecord.push(records[i].item);
}
$.ajax({
type: "POST",
url: '/Gantt/Update', //Update is Server side method
contentType: "application/json; charset=utf-8",
data: JSON.stringify(modifiedRecord),
dataType: "json",
})
}
//Delete inner level child records
function deleteChildRecords(record) {
var childRecords = record.childRecords,
length = childRecords.length,
count, currentRecord;
for (count = 0; count < length; count++) {
currentRecord = childRecords[count];
var data = currentRecord.item;
$.ajax({
type: "POST",
url: '/Gantt/Delete',
contentType: "application/json; charset=utf-8",
data: JSON.stringify(data),
dataType: "json",
});
if (currentRecord.hasChildRecords) {
deleteChildRecords(currentRecord);
}
}
}
</script>
Controller Code [C#]:
Creating Gantt object
public class TaskData
{
public string TaskId { get; set; }
public string TaskName { get; set; }
public DateTime StartDate { get; set; }
public DateTime EndDate { get; set; }
public string ParentId { get; set; }
public string Duration { get; set; }
public string Progress { get; set; }
public string Predecessor { get; set; }
}
Connecting SQL database and fetching records from database:
public class GanttController : Controller
{
GanttDataSourceEntities db = new GanttDataSourceEntities();
public ActionResult Index() {
ViewBag.dataSource = db.GanttDatas.ToList();
return View();
}
Add method to add new tasks to SQL database:
[HttpPost()]
public ActionResult Add(TaskData Task)
{
GanttData data = new GanttData
{
TaskId = Task.TaskId,
TaskName = Task.TaskName,
StartDate = Task.StartDate,
EndDate = Task.EndDate,
Duration = Task.Duration,
ParentId = Task.ParentId,
Predecessor = Task.Predecessor,
Progress = Task.Progress
};
db.GanttDatas.Add(data);
db.SaveChanges();
return Json(Task, JsonRequestBehavior.AllowGet);
}
Update method to update the changes from Gantt to SQL database:
[HttpPost()]
public ActionResult Update(List<TaskData> Tasks)
{
// Update List of modified Gantt records in single database request.
foreach (TaskData Task in Tasks)
{
string taskId = Task.TaskId;
GanttData data = db.GanttDatas.First(sv => sv.TaskId == taskId);
data.TaskId = Task.TaskId;
data.TaskName = Task.TaskName;
data.StartDate = Task.StartDate;
data.EndDate = Task.EndDate;
data.Duration = Task.Duration;
data.ParentId = Task.ParentId;
data.Predecessor = Task.Predecessor;
data.Progress = Task.Progress;
}
db.SaveChanges();
return Json(true, JsonRequestBehavior.AllowGet);
}
Delete method to delete the tasks from SQL database
[HttpPost()]
public ActionResult Delete(TaskData Task)
{
string id = Task.TaskId;
GanttData GanttRow = db.GanttDatas.Find(id);
db.GanttDatas.Remove(GanttRow);
db.SaveChanges();
return Json(Task, JsonRequestBehavior.AllowGet);
}
A simple sample to add, edit, delete and update the Gantt data using ADO.NET Entity Data Model. Please find the sample from the following location.
