Here is the entire code -
ApplicationClass projectApp = new ApplicationClass();
projectApp.FileNew(Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
Project project = projectApp.ActiveProject;
//QUERY THE RESOURCES AND ADD RESOURCES TO THE MPP FILES
sql_string = "select * from virtualresources union select
user_id,user_full_name from users";
cmd = new SqlCommand(sql_string, MyConnection);
read = cmd.ExecuteReader();
while (read.Read())
{
project.Resources.Add(read.GetString(1),
read.GetInt32(0));
}
read.Close();
//QUERY ALL THE PROJECTS FROM THE PROJECTS TABLE
sql_string = "select
pjid,pjname,pjcode,[R&DProjectPriority],user_full_name from projects_master";
SqlDataAdapter dbadap = new SqlDataAdapter(sql_string,
MyConnection);
DataSet dsPrj = new DataSet();
dbadap.Fill(dsPrj);
//ASSIGN START DATE TO PROJECTS AND % USAGE OF RESOURCES
float nperusage = 100;
sql_string = "select startdate,percentage_usage from
rdtrainstartdate where train_id=" + ntrain + " and
product_id=" + nProductId;
cmd = new SqlCommand(sql_string, MyConnection);
SqlDataReader dbread_per_usage = cmd.ExecuteReader();
while (dbread_per_usage.Read())
{
project.ProjectStart = dbread_per_usage.GetDateTime(0);
float fperusage = dbread_per_usage.GetInt32(1);
nperusage = fperusage / 100;
}
dbread_per_usage.Close();
projectApp.TableEdit("Entry", true, false, false,
Missing.Value, Missing.Value, "Priority", "Priority", 12,
PjAlignment.pjLeft, false, false, PjDateFormat.pjDate_mm_dd_yy, 1, 6,
PjAlignment.pjCenter, Missing.Value, Missing.Value);
projectApp.TableEdit("Entry", true, false, false,
Missing.Value, Missing.Value, "Text1", "Task Description",
24, PjAlignment.pjLeft, false, false, PjDateFormat.pjDate_mm_dd_yy, 1, 3,
PjAlignment.pjCenter, Missing.Value,
Missing.Value);
projectApp.TableEdit("Entry", true, false, false,
Missing.Value, Missing.Value, "Text2", "Project Owner", 24,
PjAlignment.pjLeft, false, false, PjDateFormat.pjDate_mm_dd_yy, 1, 8,
PjAlignment.pjCenter, Missing.Value, Missing.Value);
int counter = 2;
foreach (DataRow drPrj in dsPrj.Tables[0].Rows)
{
//HERE WE START CREATING THE TASKS (THIS WILL ESENTIALLY BE A PROJECT
AND NO INDENT APPLIED
Task task = project.Tasks.Add(drPrj[2].ToString(),
Missing.Value);
writeLog(drPrj[2].ToString());
task.OutlineLevel = 1;
if (drPrj[3].ToString() != "")
{
project.Tasks[task.ID].Priority =
Convert.ToInt32(drPrj[3]);
}
if (drPrj[4].ToString() != "")
{
project.Tasks[task.ID].Text2 =
Convert.ToString(drPrj[4]);
}
int nPjId = Convert.ToInt32(drPrj[0]);
sql_string = "select
taskid,taskname,effort,pjid,taskdesc from tasks where pjid=" + nPjId;
dbadap = new SqlDataAdapter(sql_string, MyConnection);
DataSet dsTsk = new DataSet();
dbadap.Fill(dsTsk);
//There are 2 for loop for tasks (back-to-back)
//1-Renders all the tasks for a project
foreach (DataRow drTsk in dsTsk.Tables[0].Rows)
{
//TASK CREATION FOR THE CORRESPONDING PROJECT
Task taskC =
project.OutlineChildren.Add(drTsk[1].ToString(), Missing.Value);
taskC.OutlineLevel = 2;
if (drPrj[3].ToString() != "")
{
taskC.Priority = Convert.ToInt32(drPrj[3]);
}
sql_string = "select taskid,resourceid from
taskresources where taskid=" + drTsk[0];
dbadap = new SqlDataAdapter(sql_string, MyConnection);
DataSet dsRes = new DataSet();
dbadap.Fill(dsRes);
//ERROR******** - COMMENTING THIS FOR LOOP HELP'S EXECUTE THE ENTIRE
PROCESS FASTER
foreach (DataRow drRes in dsRes.Tables[0].Rows)
{
if (Convert.ToInt32(drRes[0]) ==
Convert.ToInt32(drTsk[0]))
{
project.Tasks[taskC.ID].Assignments.Add(taskC.ID, Convert.ToInt32(drRes[1]),
nperusage);
}
}
//ERROR************ - COMMENTING THIS FOR LOOP HELP'S EXECUTE THE ENTIRE
PROCESS FASTER
project.Tasks[taskC.ID].Duration = drTsk[2];
project.Tasks[taskC.ID].ConstraintType =
PjConstraint.pjSNET;
project.Tasks[taskC.ID].Text1 = drTsk[4].ToString();
}
//2-Set the relationship among the tasks
//To set the relationship we should 1st render all the
tasks - Only then the
//relationship can be crated
foreach (DataRow drTsk in dsTsk.Tables[0].Rows)
{
sql_string = "select taskname,reltypeid,taskid_2
from tasks inner join relationship on
taskid=taskid_1 where taskid_2=" + drTsk[0];
dbadap = new SqlDataAdapter(sql_string, MyConnection);
DataSet dsRel = new DataSet();
dbadap.Fill(dsRel);
Task taskC = project.Tasks[counter];
foreach (DataRow drRel in dsRel.Tables[0].Rows)
{
if (Convert.ToInt32(drRel[1]) == 1)
{
project.Tasks[taskC.ID].LinkPredecessors(project.Tasks[drRel[0].ToString()],
PjTaskLinkType.pjFinishToStart, Missing.Value);
}
else if (Convert.ToInt32(drRel[1]) == 2)
{
project.Tasks[taskC.ID].LinkPredecessors(project.Tasks[drRel[0].ToString()],
PjTaskLinkType.pjFinishToFinish, Missing.Value);
}
else if (Convert.ToInt32(drRel[1]) == 3)
{
project.Tasks[taskC.ID].LinkPredecessors(project.Tasks[drRel[0].ToString()],
PjTaskLinkType.pjStartToStart, Missing.Value);
}
}
counter++;
}
project.Tasks[task.ID].Text1 = drPrj[1].ToString();
counter++;
}
projectApp.LevelingOptions(true, Missing.Value, true,
PjLevelOrder.pjLevelPriority, true, Missing.Value,
Missing.Value, Missing.Value, false, false, false);
projectApp.LevelNow(true);
string sLocation = "c:/Test.mpp";
string sMsfile_name = "Test.mpp";
if (File.Exists(sLocation))
{
File.Delete(sLocation);
}
projectApp.FileSaveAs(sLocation, PjFileFormat.pjMPP,
Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value);
projectApp.Quit(PjSaveType.pjSave);
Regards,
Safal
Andrew Jacks said:
Safal,
Is there any posibility of you posting the full code?
I think the code needs to be streamlined a little, but can only tell if I
see the full code
Thanks
Safal said:
Background : Onclick of a button on an ASPX web page we start building a MPP
file. Here is the entire process
1) Query all projects (SQL) and create on MPP (using MS Project API)
2) Create a resource list of all user's (Again querying the user's table)
3) Query all tasks for the corresponding project and render them
4) Assign individual or multiple resources per task
5) Set the task Priority etc.
5) Once again loop through all tasks to set up the relationship among tasks
- 3rd loop
6) Level Options
Step 4) seems to be very time consuming.Commenting the same out the MPP
generation takes 10/15 mins (We are talking creation of 90 projects and lets
say min. 10 tasks per project). The script takes 5 hrs to build the MPP with
the resource assignment feature.
The code on step 2 -
while (read.Read())
{
project.Resources.Add(read.GetString(1),
read.GetInt32(0));
}
The code on step 4 looks like below -
sql_string = "select taskid,resourceid from resources where taskid=xxx"
dbadap = new SqlDataAdapter(sql_string,
MyConnection);
DataSet dsRes = new DataSet();
dbadap.Fill(dsRes);
foreach (DataRow drRes in dsRes.Tables[0].Rows)
{
if (Convert.ToInt32(drRes[0]) ==
Convert.ToInt32(drTsk[0]))
{
project.Tasks[taskC.ID].Assignments.Add(taskC.ID, Convert.ToInt32(drRes[1]),
Missing.Value);
}
}
Is there a better way to assign resources ?
Regards,
Safal
