We’ve come a long ways in our quest to develop a module and hopefully the journey thus far has been beneficial, but our module is still not complete! We’ve got to add in a few more bits of functionality and then we’ll be finished with this series. Regarding the subject of this blog… Adding Tasks… the good news is that we have already created all the files and tables we need to modify in order to add tasks. We just need to simply add in some additional functionality/code to make this feature come alive.
Before we moved forward if you mentally pause can you guess what files and code we need to update in order to add a task? Give it some thought and hopefully you’ve been learning and already know or are close to knowing.
In order to add tasks we need to create 1 new Stored Procedure and update 3 files:
- Create the “Add Task” Stored Procedure in SQL Server
- Update TaskController.cs
- Update Webservices.cs
- Update View.ascx
Creating the “AddTask” SQL Stored Procedure
Since we now want to give our users the ability to add tasks we need to add another script in SQL Server to handle the adding of tasks. Adding tasks essentially equals adding more data to our Tasks table. Since the table is already there we simply need to create a new stored procedure that will add tasks to it.
In SQL Server right-click and open up a new query window on the ModDev2 database (or whatever you named your database) and paste in the following script (note that you should update your unique Stored Procedure and Table name prefixes where mine are “CBP”):
CREATE PROCEDURE [dbo].[CBP_AddTask]
@TaskName nvarchar(max),
@TaskDescription nvarchar(max),
@IsComplete bit,
@ModuleID int,
@UserID int
AS
INSERT INTO dbo.CBP_Tasks(
TaskName,
TaskDescription,
IsComplete,
ModuleID,
UserID
)
VALUES (
@TaskName,
@TaskDescription,
@IsComplete,
@ModuleID,
@UserID
)
SELECT @@IDENTITY
GO
Looking at this code you can see a similar script to when we created the “GetTasks” stored procedure. Notice the name of the procedure is “ CBP_AddTask “ – here again we are prefacing the stored procedure with a name unique to us to avoid any clashes with other stored procedures. Then we create several parameters which this stored procedure will accept and then insert into the “CBP_Tasks” table.
Execute this script by clicking “Execute” or the F5 button. Once your stored procedure has been created let’s test out the procedure to see if it works as imagined. First refresh your database by right-clicking on the database name and selecting refresh. Then expand the “Programmability” section, then “Stored Procedures” and you should see the “CBP_AddTask” stored procedure. Right-click on the stored procedure and click “Execute Stored Procedure”. In the pop-up window enter the logical parameters that we want to insert into the Tasks table. Enter in some data and click OK.
After entering the data in the stored procedure we now need to verify that the data got stored in the Tasks table. Go up to the Tables node in your database and expand the tables. Find the “Tasks” table and right-click and “Select the top 1000 rows” to confirm that your task was successfully entered. If your newly created task was there in the list of records then we’re ready to move on.
Updating the SQLDataProvider Files
We just created a new stored procedure and you may already be thinking that we need to update our SQLDataProvider file to account for this new stored procedure. If you thought about that then you’re right on track! Again, the SQLDataProvider files need to be updated so that whenever our module is installed or uninstalled from a DNN instance the necessary tables and stored procedures will either be created or deleted (and we will illustrate this concept at the end of the series).
To update the SQLDataProvider files go into Visual Studio and open the 00.00.01.SqlDataProvider file. In the stored procedure section copy and paste the “Add Task” stored procedure that we just created into this file. Then copy and paste the “IF EXISTS… “ code snippet the above “GetTasks” stored procedure and paste it in front of the AddTasks stored procedure. Now update that “IF EXISTS..” text to check for the existence of the “AddTask” stored procedure.
If you want the code snippet just copy and paste the below:
IF EXISTS (select * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}CBP_AddTask]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}CBP_AddTask
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}CBP_AddTask
@TaskName nvarchar(max),
@TaskDescription nvarchar(max),
@IsComplete bit,
@ModuleID int,
@UserID int
AS
INSERT INTO {databaseOwner}[{objectQualifier}CBP_Tasks](
TaskName,
TaskDescription,
IsComplete,
ModuleID,
UserID
)
VALUES (
@TaskName,
@TaskDescription,
@IsComplete,
@ModuleID,
@UserID
)
SELECT @@IDENTITY
GO
Now open the Uninstall.SqlDataProvider file and paste that same “IF EXISTS…” code snippet into the uninstall file. That should be it for updating the SQLDataProvider files.
If you want the code snippet just copy and paste in the below:
IF EXISTS (select * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}CBP_AddTask]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}CBP_AddTask
GO
TaskController.cs Updates
As a reminder, our TaskController class, is the traffic director between the database and our objects. In our first controller method (the “GetTasks” method) we selected data from the database and populated objects in memory then bound them to the view via our web service and some jQuery.
For the “AddTasks” method we will do the exact opposite. That is we will take an already populated object in memory and then add it to the Tasks table in our database. Just as the traffic director switches from giving traffic a stop sign to giving them the go ahead sign, you can imagine our task controller allowing objects and data to go the opposite direction for this “AddTask” method to work.
Open up the TaskController.cs file. Just below the GetTasks function create a new AddTask function by placing this code into file:
public void AddTask(Task task)
{
task.TaskID = DataProvider.Instance().ExecuteScalar<int>("CBP_AddTask",
task.TaskName,
task.TaskDescription,
task.isComplete,
task.ModuleId,
task.UserId
);
}
Looking at this code we see the “public void” and that void just means that we are not returning any rows of data in this function. We are simply adding data so there is no return type. “AddTask” is the name of the function and it accepts a task object that we pass in as being named “task”.
Then we reference an instance of the DataProvider class and call an ExecuteScalar method. The “ExecuteScalar” method executes the query and returns the first column of the first row in the result set. Into the method we pass in the parameters of our new task because our stored procedure and our Tasks table expect these parameters and data fields. Now that we’ve got our stored procedure created and our TaskController updated we need to move on to update the web service class.
WebServices.cs Updates
In the Webservices.cs file we need to create a function to add the task. There will be one main difference in this method which is that the “AddTask” method is an HttpPost method. Our GetTasks method was an HttpGet which we specified as an attribute.
In your webservices.cs file just below the GetTasks function we want to create the new method for adding tasks. Copy and paste this into your file:
public class TaskToCreateDTO
{
public string TTC_TaskName { get; set; }
public string TTC_TaskDescription { get; set; }
public bool TTC_isComplete { get; set; }
public int TTC_ModuleID { get; set; }
public int TTC_UserId { get; set; }
}
[DnnModuleAuthorize(AccessLevel = SecurityAccessLevel.View)]
[ValidateAntiForgeryToken]
[HttpPost]
public HttpResponseMessage AddTask(TaskToCreateDTO DTO)
{
try{
var task = new Task()
{
TaskName = DTO.TTC_TaskName,
TaskDescription = DTO.TTC_TaskDescription,
isComplete = DTO.TTC_isComplete,
ModuleId = DTO.TTC_ModuleID,
UserId = DTO.TTC_UserId
};
TaskController tc = new TaskController();
tc.AddTask(task);
return Request.CreateResponse(HttpStatusCode.OK);
}
catch(Exception exc)
{
return Request.CreateErrorReponse(HttpStatusCode.InternalServerError, exc);
}
}
Let’s look at the code for our AddTasks function. You’ll notice that first we define a new class named “TasktoCreateDTO” which represents the task that we’re fixing to create with the suffix of DTO attached to it. DTO stands for “Data Transfer Object” and the suffix just visually communicates that this object is simply an object that we’re using to transfer the data via our service. This class is very similar to the Task class, but is defined here within our service as it contains the data we are fixing to set as the properties of our real task object.
Then the service starts with our 2 attributes… the access/security level required and the type of request. Remember that we can use the DNN Access level to dictate the permissions needed to be able to perform this HttpPost. The access level of [DnnModuleAuthorize(AccessLevel = SecurityAccessLevel.View)] means that in order to execute this request a user must have “view” rights within our module. Since we’re allowing this level of security here anybody who can see the “Add Task” button will be able to add tasks. Keep this in mind as you configure the page and module permissions… and we’ll discuss this a little more in a few paragraphs.
Next we see the [ValidateAntiForgeryToken] which is an attribute that should be associated with HttpPost actions to protect against CSRF attacks. Learn more about CSRF Attacks.
Next we have the request type attribute of [HttpPost]. HttpPost signals that this service responds to Post requests (rather than Get requests).
Then we create the method by creating a public HttpResponseMessage AddTask and we pass in the parameter of type TaskToCreateDTO named DTO. The first thing we do inside of the method is to create a new variable named “task” of type “Task”. This process of creating a new object sometimes referred to as “newing up”. This new task object is using our Task class definition and we set the properties of our new object to the matching properties of the DTO task that we just passed in as a parameter.
Then we create a new instance of the TaskController class and name it “tc” for task controller. As you know we just added a new method to the Task Controller class which was the “AddTask” method. So we type the characters “tc.” … and when you click the dot the intellisense suggests the AddTask method and we select it. Into the AddTask method of the TaskController tc we pass in the new task object in which we just created and set the properties. And that’s all we have to do to add a task. Then to finish the web service we create a return request and pass in the HttpStatusCode of “Ok”.
Did you think we would have to update our route (in the RouteMapper.cs file)? We didn’t have to because we’re still using the same route even for these new services.
View.ascx Updates
In the previous section you may have wondered about the TaskToCreateDTO object. Where does this object come from? Well, in our view.ascx we need to get funky with some jQuery in order to post our new task data to the web service and this “DTO” object is part of that funkiness. So let’s update our view.ascx code in order to make all this happen.
The first thing we will start with is the button in the AddTaskDiv. We know that a user is most likely going to input data and click the “Add Task” button whenever they’re ready to send data to the server or essentially add a task to the list. Consequently we’ll start our custom code on the click function of this button.
Below the $.getJSON function paste in the following code:
var userID = <%= UserId %>;
$('#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: userID
};
var sf = $.ServicesFramework(<%:ModuleContext.ModuleId%>);
$.ajax({
url: "/DesktopModules/MyFirstModule/API/ModuleTask/AddTask",
type: "POST",
contentType: "application/json;charset=utf-8",
data: JSON.stringify(taskToCreate),
beforeSend: sf.setModuleHeaders
});
});
Looking at this code you can see the in the very first line that we create a variable called “userID” and we set it equal to the UserId value that we get from the server. Just as we did with the module’s ID we are setting the value of a JavaScript variable via evaluating some server side code. Why did we do that… because in order to properly insert the task object we need to know the user ID of the user who created the task.
Just below that variable you see the $(‘#btnAddTask’).click function and that is jQuery saying that whenever the button is clicked we want to run a function. Inside of the function you see the value of the taskName, taskDescription, and the isComplete fields being set to the values and properties of their respective input fields in the form. Then we create a variable called “taskToCreate” and set the properties of this object to these values we just got from the form. You may also notice that the property names of this variable directly match up with the class we defined in our service by using the “TTC_” prefix.
Then you see another variable we create called “sf”. This variable is necessary as it plays a role in the reviewing of permissions and security within our module and the DNN Services Framework. As you can see the Services Framework is getting the ModuleContext and ModuleId and we are setting this variable to those values so that we’ll be able to effectively implement our module’s permissions.
Finally we see the jQuery AJAX function and the values we’re passing in as parameters. The first one is the URL and that’s the specific URL of our route. You can see where we’re accessing the “AddTask” route. Then we indicate that this is of type POST followed by specifying the content type which is JSON. In the data parameter we pass our taskToCreate object and we “stringify” it so that it will be passed as a string. Finally we add the “beforeSend” where we set the module headers based on our “sf” variable’s value so that we can ensure our permissions are functioning correctly.
With all that done let’s save & build our solution to see if it works. After building refresh your site and see if you can add a task. If everything went as planned then we just added a task to our list… but the task didn’t instantly show up. What happened? Refresh the page and see if your task shows up now. Did it show up in the list? It should have. The good news is that we were just able to successfully add a task to our task table. The, perhaps unexpected, result was that our task list didn’t instantly update, which is what we want to happen.
The module is actually doing exactly what we’ve told it to do… or rather what we’ve coded it to do. Now we need to figure out a way to update the task list whenever a new task is added. It’s actually not that difficult to make happen. If you think about it… we were able to execute our “Get” request to get the tasks whenever the page loaded. To make the tasks list update we just need to execute another “Get” request whenever we have successfully added a task. And to do that we’ll make use of some JavaScript functions.
Update your entire script section of code at the bottom to the below:
<script type="text/javascript">
var moduleId = <%= ModuleId %>;
var userID = <%= UserId %>;
function loadTasks() {
$.getJSON(
"/DesktopModules/MyFirstModule/API/ModuleTask/GetTasks?moduleId=" + moduleId,
function (result) {
$('.ListItems').html("");
var parsedTaskJSONObject = jQuery.parseJSON(result);
$.each(parsedTaskJSONObject, function () {
if (this.isComplete == true) {
$('.ListItems').append(
'<div class="checkbox"><input class="cbxListIsComplete" type="checkbox" checked/></div>' +
'<div class="ListTaskName">' + this.TaskName + '</div>' +
'<div class="ListTaskDescription">' + this.TaskDescription + '</div>');
}
else {
$('.ListItems').append(
'<div class="checkbox"><input class="cbxListIsComplete" type="checkbox"/></div>' +
'<div class="ListTaskName">' + this.TaskName + '</div>' +
'<div class="ListTaskDescription">' + this.TaskDescription + '</div>');
}
});
});
}
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: userID
};
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>
Looking at this code what is different? Well I moved our global variables of moduleId and userID to the top and then created a function called “LoadTasks”. In that function one of the first things I did was to clear out the list of tasks. I did this via jQuery by using the $(‘.ListItems’).html(“”); command. This just grabs the ListItems DIV and sets it contents to nothing. Then we go right back behind it and populate it again. Then we go through the same loop of adding our data and content to the list.
Initially I’m just defining the loadTasks() function. Just beneath the function definition you see where we actually call the function via the loadTasks(); call. The code beneath that is essentially the same except for one addition. At the bottom of our jQuery AJAX function there is a new parameter of “success:” and what this signals is that whenever the POST successfully executes then I want you to run a function. We pass in “data” as the parameter and then run the loadTasks() function. So as soon as we add a task successfully that loadTasks() function will run again and this is what we need to make our list instantly update.
Save the code and refresh the page. Now add a task and check out the action that you get… a lot smoother and almost instantaneous huh? Pretty slick right… that’s why people love using the Web API services framework because it’s fast and there are no postbacks.
Were you just able to add your first task? If so congrats! Tweet to me and let me know you made it this far!
The video below walks through the concepts covered in this blog entry
This was a long blog entry, but updating the functionality didn’t take too long. I’m purposely being overly verbose and I hope that is helpful to you as you learn. I’m learning more simply by writing this series! Now we’ve got our tasks listed out and we’re able to add tasks. We are really getting our shine on here now. In the next blog entry we’ll figure out a way to allow users to update their own tasks. Hope to see you there!
Go to the Next Blog Entry: "Editing Tasks"