In the previous blog entry "Adding Tasks" we walked through the process for Adding Tasks. In this entry we will walk down a similar path, but this time we’ll be adding in the functionality to allow users to update their own tasks. In order to do this we’ll have to get a little deep in JavaScript and JQuery, but in the end it will be worth it.
Creating the Update Stored Procedure
As you may expect we need to first create our stored procedure that will handle the updating of our task data. In the same process as we previously did, go to your tasks table and right click and then “Script Table as” then “UPDATE To” then “New Query Editor Window”.
Once you let SQL create your script stub you see some code that looks like this
Update Statements: Code with Caution
This gives us a good start, but obviously we need to update this code. One thing that I need to be sure I communicate is that “UPDATE” statements can do a lot of damage to your data if not coded correctly. The crucial part of the update statement is the “WHERE” portion of it. The WHERE statement tells SQL exactly which rows/columns you want to update. If you do not specify a WHERE statement then SQL will go and instantly update every record in your database with data you didn’t intend for it to and the worst part is that there is no undo.
For example, take the stub code that SQL just created for us. If you remove the WHERE clause then whatever values you told SQL to update with… it would update every single record in the Tasks table with the same value. Once you update records, even if it’s a million records, they’re updated and there’s not much going back unless restore a database backup. So, if you find yourself coding UPDATE statements… be sure to have the WHERE portion in place before you ever click execute.
So what do we need to update in order to make this work for our tasks module? Well we need to account for the parameters that we’ll be passing in. Update the stubbed code to the following (inserting your own unique prefix):
CREATE PROCEDURE CBP_UpdateTask
@TaskID int,
@TaskName nvarchar(max),
@TaskDescription nvarchar(max),
@IsComplete bit
AS
UPDATE [dbo].[CBP_Tasks]
SET
TaskName = @TaskName,
TaskDescription = @TaskDescription,
IsComplete = @IsComplete
WHERE TaskID = @TaskID
SELECT @TaskID
GO
Looking at this code we again see the prefixed stored procedure name of “CBP_UpdateTask”. Again, you will put your own unique prefix and this helps avoiding clashes. After the name we define our parameters and you notice that we’re not accepting a UserID or ModuleID here in the update statement. A user can’t really change their UserID nor can they change the ModuleID so I purposely left those out. We’ll see if that comes back to haunt us later. Then we specify this as type “UPDATE” followed by the table name we want to update. In this case we want to update the CBP_Tasks table. Yours will be named whatever your table name is of course. Then we see the SET command and here is where we are updating values. We set the column data to the value of the parameter we are passing in. Finally you see the ever important WHERE statement. In the WHERE statement we instruct SQL to only update the row where the TaskID value is the same as the value of the TaskID parameter we’re passing in. For repetition… if we did not include the WHERE statement we would update every row with whatever data we pass in, which makes for not fun times.
Testing the Stored Procedure
As always, we’ll then go and execute this stored procedure to ensure that it works properly. First right click on the database and refresh it. Then expand the Tables node and find your table. Right click and “Select top 1000 Rows” because we want to be able to verify if this update worked here in a few seconds. Then expand the Programmability > Stored Procedures and see if you see your “UpdateTask” stored procedure. Right click on your update stored procedure and “Execute Stored Procedure”.
Here again we are presented with the pop-up that is looking for our sample parameters. Enter some parameters here… make sure that you enter a TaskID that is currently present in your table… and then click Ok. If all went well then you should have successfully updated a record in your table.
Updating the SQLDataProvider Files
Now with our stored procedure created we need to update it with the DNN specific syntax for inclusion in our SQLDataProvider files. Recall that the DNN specific syntax checks to see if the stored procedure exists and if it does then it drops it and instantly re-creates it. The script also replaces the .dbo with the DNN specific databaseOwner and objectQualifier values.
Update your SQLDataProvider script to reflect the following and save in your SQLDataProvider file:
IF EXISTS (select * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}CBP_UpdateTask]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}CBP_UpdateTask
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}CBP_UpdateTask
@TaskID int,
@TaskName nvarchar(max),
@TaskDescription nvarchar(max),
@IsComplete bit
AS
UPDATE {databaseOwner}[{objectQualifier}CBP_Tasks]
SET
TaskName = @TaskName,
TaskDescription = @TaskDescription,
IsComplete = @IsComplete
WHERE TaskID = @TaskID
SELECT @TaskID
GO
Update your Uninstall.SQLDataProvider script to reflect the following and save in your Uninstall.SQLDataProvider file:
IF EXISTS (select * FROM dbo.sysobjects
WHERE id =
object_id(N'{databaseOwner}[{objectQualifier}CBP_UpdateTask]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}CBP_UpdateTask
GO
Creating the Update TaskController
Now that we have a functioning update stored procedure we are ready to update our TaskController.cs file. In Visual Studio open the TaskController.cs file. Just below the AddTask controller method insert the following code:
public void UpdateTask(Task task)
{
task.TaskId = DataProvider.Instance().ExecuteScalar<int>("CBP_UpdateTask",
task.TaskId,
task.TaskName,
task.TaskDescription,
task.isComplete
);
}
Looking at this code you can again see the “Public Void” and the void is because we’re not returning any rows of data. Then we give the function a name “UpdateTask” and pass in a task object named “task”. Again, we set the taskId equal to an instance of our data provider and we utilize the ExecuteScalar sql method and pass in the string of the name of our stored procedure. In this case the “UpdateTask” followed by all the parameters that the Update stored procedure expects. And that’s all we have to do to update our TaskController class. On to the web service!
Creating the Update Web Service
In our web service we need to add a method for updating tasks. This route will be the URL that we will reach out to whenever we want to update tasks. Paste the following code into your web service file just below the “AddTask” method.
public class TaskToUpdateDTO
{
public string TTU_TaskName { get; set; }
public string TTU_TaskDescription { get; set; }
public bool TTU_isComplete { get; set; }
public int TTU_TaskID { get; set; }
}
[DnnModuleAuthorize(AccessLevel = SecurityAccessLevel.View)]
[ValidateAntiForgeryToken]
[HttpPost]
public HttpResponseMessage UpdateTask(TaskToUpdateDTO DTO)
{
try
{
var task = new Task()
{
TaskName = DTO.TTU_TaskName,
TaskDescription = DTO.TTU_TaskDescription,
isComplete = DTO.TTU_isComplete,
TaskId = DTO.TTU_TaskID
};
TaskController tc = new TaskController();
tc.UpdateTask(task);
return Request.CreateResponse(HttpStatusCode.OK);
}
catch (Exception exc)
{
return Request.CreateErrorResponse(HttpStatusCode.InternalServerError, exc);
}
}
Investigating this code it’s very similar to our previous code of our “AddTask” method. This time we create a class for the TaskToUpdateDTO (data transfer object). What is this object? It’s the object that will hold the values we get from our View.ascx file before we create a new task object and populate it with these values.
The Attributes
Then again we have the DNNModuleAuthorize attribute and we’ve set it to view permissions. Again this means that anyone who has access to see this module will be able to call this function. I’m leaving the permissions a little loose for this series, you can update them as you like. Next we have the ValidateAntiForgeryToken because this is a POST request and we should always have that attribute on post requests. This helps protect us from being hacked by CSRF attacks (as mentioned in the previous blog). Finally we see the type of request indicated and as we mentioned this is a POST request.
Then inside of our try block we create a new task object based on our task class definition and populate its properties with the values that are in our TaskToUpdate Data Transfer Object. Once populated we create a new TaskController named “tc” and then we call the UpdateTask method that we just created in our controller class. Into that method we pass in the task object that we’ve just populated. And we end with the catch block and log any exceptions that may have occurred.
Now with our stored proc, task controller, & web service created we’re ready to move on to the View.ascx updates
Note to Angular.js & Knockout.js Fans
If you are a front-end developer with strong Angular or Knockout skills then the following section will probably drive you crazy as some of the functionality could be more easily achieved using Angular or Knockout. However, in hopes of not adding another layer of complexity (or something else to learn) I’m doing it all in jQuery. In the future I may return back and update the series with an Angular update.
Updating the View.ascx
While I was working on the updates needed in order to make the update functionality work I changed some of the structure. It was nothing drastic, but in order for us to be on the same page I think it’s best if you copy the code below and paste it in to your view.ascx file. You can definitely update it manually if you wish, but it can be frustrating as one small comma or apostrophe can break everything in JavaScript. So update your code as you wish or paste in the following:
<%@ Control Language="C#" AutoEventWireup="true" CodeBehind="View.ascx.cs" Inherits="Christoc.Modules.MyFirstModule.View" %>
<div class="AddTaskDiv">
<h2>Add Task</h2>
<hr />
<div class="lblTaskName">Task Name</div>
<input id="TaskName" type="text" />
<div class="lblTaskDescription">Task Description</div>
<input id="TaskDescription" type="text" />
<div class="isCompleteGroup">
<input id="cbxIsComplete" type="checkbox" />
<div class="lblTaskIsComplete">Is Complete</div>
</div>
<input class="dnnClear dnnRight dnnPrimaryAction" id="btnAddTask" type="button" value="Add Task" />
</div>
<div class="TaskListDiv">
<div class="Headings">
<h2>Complete</h2>
<h2>Task Name</h2>
<h2>Task Description</h2>
<hr/>
</div>
<div class="TaskList"></div>
</div>
<script type="text/javascript">
var moduleId = <%= ModuleId %>;
var CurrentUserID = <%= UserId %>;
function loadTasks() {
$.getJSON(
"/DesktopModules/MyFirstModule/API/ModuleTask/GetTasks?moduleId=" + moduleId,
function (result) {
$('.TaskList').html("");
var parsedTaskJSONObject = jQuery.parseJSON(result);
$.each(parsedTaskJSONObject, function () {
//Defines object's UserId & TaskId as variableS so they're accessble inside of function
var objectUID = this.UserId;
var objectTaskID = this.TaskId;
//Defines function that checks to see if the current user's ID matches the userID who created the task... if so add the Edit button
function AddEditCheck() {
if (objectUID == CurrentUserID) {
$('.TaskList').append('<div class="EditIcon" id="' + objectTaskID +'">' + 'EDIT' + '</div>');
}
else {
$('.TaskList').append('<div class="EditIcon">' + '</div>');
}
}
if (this.isComplete == true) {
$('.TaskList').append(
'<div class="ListItems ListItems' + this.TaskId +'" id="ListItem' + this.TaskId +'">' +
'<div class="checkbox checkbox' + this.TaskId + '"id="checkbox"><input class="cbxListIsComplete" id="cbox' + this.TaskId + '"type="checkbox" checked/></div>' +
'<div class="ListTaskName" id="ListTaskName' + this.TaskId +'">' + this.TaskName + '</div>' +
'<div class="ListTaskDescription" id=ListTaskDescription' + this.TaskId +'>' + this.TaskDescription + '</div>' +
'</div>');
AddEditCheck();
}
else {
$('.TaskList').append(
'<div class="ListItems ListItems' + this.TaskId +'" id="ListItem' + this.TaskId +'">' +
'<div class="checkbox checkbox' + this.TaskId + '"id="checkbox"><input class="cbxListIsComplete" id ="cbox' + this.TaskId + '"type="checkbox"/></div>' +
'<div class="ListTaskName" id="ListTaskName' + this.TaskId +'">' + this.TaskName + '</div>' +
'<div class="ListTaskDescription" id=ListTaskDescription' + this.TaskId +'>' + this.TaskDescription + '</div>' +
'</div>');
AddEditCheck();
}
});
//When any EDIT link is clicked we grab the EDIT DIV's ID so that we know the taskID, TaskNameDIV, & TaskDescriptionDIV
$('.EditIcon').click(function() {
var EditClickedID = $(this).attr('id');
var EditListItemDiv = '.ListItems' + EditClickedID;
var EditTaskCheckBox = '#cbox' + EditClickedID;
var EditTaskNameDiv = '#ListTaskName' + EditClickedID;
var EditTaskDescriptionDiv = '#ListTaskDescription' + EditClickedID;
//Find the current task's isComplete value and store it as a variable. If it's checked it will return "true" if not then it will return "false"
var CurrentTaskIsComplete = $('.TaskList').find('' + EditTaskCheckBox + '').prop('checked');
//Find the current task's name and store it as a variable
var CurrentTaskName = $('.TaskList').find('' + EditTaskNameDiv + '').text();
//Grabe the current task name HTML and replace it with an input box and populate the contents with the current task name variable
$('.TaskList').find('' + EditTaskNameDiv + '').html('<input class="UpdatedTaskName" id="UpdatedTaskName' + EditClickedID +'" type="text" value="' + CurrentTaskName +'"/>');
//Find the current task's description and store it as a variable
var CurrentTaskDescription = $('.TaskList').find('' + EditTaskDescriptionDiv + '').text();
//Grab the current task description text and replace it with an input box and populate the contents with the current task description variable
$('.TaskList').find('' + EditTaskDescriptionDiv + '').html('<input class="UpdatedTaskDescription" id="UpdatedTaskDescription' + EditClickedID +'" type="text" value="' + CurrentTaskDescription +'"/>');
//Append cancel and save options
$('.TaskList').find('' + EditListItemDiv + '').append('<div class="Cancel Cancel' + EditClickedID +'">Cancel</div>');
$('.TaskList').find('' + EditListItemDiv + '').append('<div class="Save" id="' + EditClickedID +'">Save</div>');
//When the cancel button is clicked then set the fields back to the original values then remove the save & cancel buttons
$('.Cancel').click(function() {
$('.TaskList').find('' + EditTaskCheckBox + '').prop('checked', CurrentTaskIsComplete);
$('.TaskList').find('' + EditTaskNameDiv + '').html(CurrentTaskName);
$('.TaskList').find('' + EditTaskDescriptionDiv + '').html(CurrentTaskDescription);
$(this).remove();
$('.Save').remove();
});
//When the Save button is clicked then send the updated values to the database and set the text to the updated values then remove the save & cancel buttons
$('.Save').click(function() {
var SaveClickedID = $(this).attr('id');
var SaveTaskNameDiv = '#UpdatedTaskName' + SaveClickedID;
var SaveTaskDescriptionDiv = '#UpdatedTaskDescription' + SaveClickedID;
var SaveTaskCheckBox = '#cbox' + SaveClickedID;
var taskId = SaveClickedID;
var taskName = $('.TaskList').find('' + SaveTaskNameDiv + '').val();
var taskDescription = $('.TaskList').find('' + SaveTaskDescriptionDiv + '').val();
var isComplete = $('.TaskList').find('' + SaveTaskCheckBox + '').prop('checked');
var taskToUpdate = {
TTU_TaskName: taskName,
TTU_TaskDescription: taskDescription,
TTU_isComplete: isComplete,
TTU_TaskID: taskId,
};
console.log(taskToUpdate);
var sf = $.ServicesFramework(<%:ModuleContext.ModuleId%>);
$.ajax({
url: '/DesktopModules/MyFirstModule/API/ModuleTask/UpdateTask',
type: "POST",
contentType: "application/json",
beforeSend: sf.setModuleHeaders,
data: JSON.stringify(taskToUpdate),
success: function(data) {
loadTasks();
}
});
$(this).remove();
$('.Cancel').remove();
});
});
});
}
loadTasks();
$('#btnAddTask').click(function() {
var taskName = $('#TaskName').val();
var taskDescription = $('#TaskDescription').val();
var isComplete = $('#cbxIsComplete').prop('checked');
var taskToCreate = {
TTC_TaskName: taskName,
TTC_TaskDescription: taskDescription,
TTC_isComplete: isComplete,
TTC_ModuleID: moduleId,
TTC_UserID: CurrentUserID
};
var sf = $.ServicesFramework(<%:ModuleContext.ModuleId%>);
$.ajax({
url: '/DesktopModules/MyFirstModule/API/ModuleTask/AddTask',
type: "POST",
contentType: "application/json",
beforeSend: sf.setModuleHeaders,
data: JSON.stringify(taskToCreate),
success: function(data) {
loadTasks();
}
});
});
</script>
Now that is a lot of code to paste in right… instead of trying to explain it all here in text, I’m going to explain it all in the video below. So check out the video to get the details and hopefully it will save me some time typing and you some time reading.
Updating the Module.css
Since I updated the structure of our HTML and changed some names I needed to update my module.css file so the elements would still be styled correctly. So update your module.css file as needed or paste in the following code:
.AddTaskDiv { float: left; width: 25%; margin-right: 1%; margin-bottom: 50px; background: #ccc; padding: 10px; }
.lblTaskIsComplete { float: left; }
.TaskListDiv { float: right; width: 70%; min-height: 263px; margin-bottom: 50px; background: #ccc; padding: 10px; }
.Headings h2 { margin-right: 50px; float: left; }
.EditIcon{float: left; color: #0000ff; font-size: 8px; cursor: pointer;clear: left;}
.checkbox { float: left; width: 12%;margin-left: 5px;}
#cbxIsComplete { float: left; margin-top: 3px; margin-right: 0px; }
.ListTaskName { float: left; width: 18%; }
.ListTaskDescription { float: left; width: 63%; }
.ListItems{ float:right;width: 97%;}
.UpdatedTaskName{ width: 117px;}
.UpdatedTaskDescription{ width: 400px;}
.Cancel{ cursor: pointer;}
.Save{ cursor: pointer;}
The video below walks through the concepts covered in this blog entry
Wrapping Up
Hopefully you were able to update tasks in your task list and if so congrats! As you can see the majority of the work was in the view.ascx file and it was all JavaScript & JQuery. Once everything is completed it’s evident that this method provides a really nice user experience. Of course my JavaScript skills are not perfect and things could be a lot prettier, but hopefully you understand the principles needed to update and of course you can style and add all the eye-candy effects that you wish.
Helpful Resources
We just did a lot with jQuery selectors and JavaScript concatenation. If you want more info on that subject then the below resources may be helpful:
Summary
So we’re almost done with the series, but we have 2 more items to cover… what if you want to delete a task from the task list? We’re going to cover that in the next blog. Hope to see you there.
Go to the Next Blog Entry: Deleting Tasks