The idea is: we have in database a table with a list of tasks, very similar fields as SharePoint Project Tasks with some custom ones of course.
The table looks something like this:
As you can see this would generate a hierarchy something like this:
First Task
--> Sub task 133
--> Task 79
--> Subtask 79.1
Second Task
-->Sub Task 2
The idea, of course, is to create this hierarchy in a SharePoint list by using Summary Tasks (folders) and tasks.
So... here we go.
Let's start by creating a query that will help us identify which task is used as parent task
SELECT [Task Name], [Task ID], [Task_Parent_Task_ID], [Description], [Task_Per_Complete], [Task_Start_Date], [Task_Finish_Date], [Task Order],
Case When (Parents.TID) is not null then 'Yes'
else 'No'
End as 'Is Parent'
FROM [Database].[Test_Table] CT
LEFT OUTER JOIN
(Select [Task_Parent_Task_ID] TID, count([Task ID]) 'Total Child'
From [Database].[Test_Table]
Where [Task_Parent_Task_ID] is not null
Group by [Task_Parent_Task_ID]) Parents ON CT.[Task ID] = Parents.TID
Now let's go to our Visual WebPart code and create the objects needed to connect to your site and list.
string currentURL = SPContext.Current.Site.UpgradeRedirectUri.ToString();
using (SPSite mySite = new SPSite(currentURL))
{
using (SPWeb myWeb = mySite.OpenWeb())
{
// The first thing is to create the tasklist object and content type object
SPList taskList = myWeb.Lists[TaskListName];
SPContentType SummaryTaskContentType = taskList.ContentTypes["Summary Task"];
// The summary task content type of course will be applied to parent items only
// Time to create a connection to your database to get the results from the query above.
using (SqlConnection myConnection = new SqlConnection(ConnectionString))
{
using (SqlCommand myCommand = new SqlCommand("dbo.GetCoreTasks", myConnection))
{
myCommand.CommandType = CommandType.StoredProcedure;
if (myConnection.State != ConnectionState.Open)
myConnection.Open(); SqlDataReader reader = myCommand.ExecuteReader();
// taskRelations is a global DataTable to store all the info
taskRelations.Load(reader);
// And now is the time to loop this table and create the items
// Call Read before accessing data.
foreach (DataRow record in taskRelations.Rows)
{
// New item's object
SPListItem item = null;
// to save the Parent folder
string parentFolder = string.Empty;
// used to construct the server relative url for the parent folder
string folderUrl = string.Empty;
// Check if this item is parent
if (record["Is Parent"].ToString().Equals("Yes"))
{
// if parent and also child
if (record["Task_Parent_Task_ID"] != DBNull.Value)
{
// find out parent's folder
parentFolder = GetFolderNameByTaskID(Convert.ToInt32(record["Task_Parent_Task_ID"]));
folderUrl = string.Format("{0}/Lists/{1}/{2}", myWeb.ServerRelativeUrl, TaskListName, parentFolder);
// Create the new item
item = taskList.AddItem(folderUrl, SPFileSystemObjectType.Folder);
}
else
{
// Create item at root level
item = taskList.AddItem();
}
// specify the Summary task content type
item["ContentTypeId"] = SummaryTaskContentType.Id;
item.FileSystemObjectType = SPFileSystemObjectType.Folder;
}
// this item is not being used as parent
else
{
// find out parent's folder this is just a function that will return a string indicating the parent's task name by searching in the DataTable.
parentFolder = GetFolderNameByTaskID(Convert.ToInt32(record["Task_Parent_Task_ID"]));
folderUrl = string.Format("{0}/Lists/{1}/{2}", myWeb.ServerRelativeUrl, TaskListName, parentFolder);
// Create the new item
item = taskList.AddItem(folderUrl, SPFileSystemObjectType.File);
}
// Set item fields
item["Title"] = record["Task Name"];
item["Task ID"] = record["Task ID"];
item["Parent ID"] = record["Task_Parent_Task_ID"];
item["Description"] = record["Description"];
item["% Complete"] = record["Task_Per_Complete"];
item["Start Date"] = record["Task_Start_Date"];
item["Due Date"] = record["Task_Finish_Date"];
item["Order"] = record["Task Order"];
// Update item
item.Update();
}
}
}
}
}
And there you go... this will create the tasks in SharePoint list as follows:
1. Two main summary tasks
2. One task and another Summary Task within "First Task"
3. And just one single task within "Second Task"
