Free Support Forum - aspose.com

Ms project from SQL Server

Hi
I have a similar request to “webster” on your forums I would like to output from a web page to Ms Project using data coming from a stored procedure on an SQL Server. I thought I would be able to iterate thro a dataset table to produce the Tasks. I see you refer webster to the demos for examples, but I can’t find any in the download I have
Thanks
Richard

Dear Richard,

After installing Aspose.Project.msi you have sources of 5 C# and 5 VB demos
in the C:\Program Files\Aspose\Aspose.Project\Demos (or something similar) folder.

Please check MpxCreate demo. It shows how to create project from scratch and
add new custom calendar, tasks, resources and assignments.

Hi alcrus
Many thanks for the prompt reply
I have looked at the demo but it is not what I need

what I need to be able to do is pull the data from a dataset table to create the MPX file looking in the API I asume I must use the Tasks Collection

I do not want to dim a separate Task for each activity which is the example you are showing, surely I can read a dataset table in the same way that you demonstrate reading an existing MPP file i.e.

While iter.MoveNext() = True

task = CType((iter.Current), task)

looking forward to your reply My company have already purchased the License and are very keen for me to start producing some results

Regards
Richard

Dear Richard,

I’m affraid there is no other ways to create tasks except reading data from database
and creating it step by step like this:

Dim task1 As Task = file.AddTask()
task1.Name = “Summary Task”

Dim task2 As Task = task1.AddTask()
task2.Name = "First Sub Task"
task2.Duration = New Duration(10, TimeUnit.Days)
task2.Start = New System.DateTime(2003, 1, 1)

Why you don’t like it? I think it’s clear and suitable for using data from database.

Hi alcrus

Thanks for the mail

The problem that I have is that we have developed an interactive Project database control system and what I wanted to do was display the activities graphically in ms project but the selection would be variable selected by the user so the number of Tasks could be anything from 1 - 400 So I need someway of Programmatically creating the tasks.

I did wonder if I created an XML file from the dataset could I use this to covert to the mpx file ?

Regards

Richard

Dear Richard,

I think each company who works with own project databases has proprietary format of database and different datasets.
And it’s not a good idea to import all these formats.
It’s not so important how many tasks you have in the project.
Time of importing all these tasks through API will be comparable with importing from XML.

hi alcrus
I managed to find a way to work with a dataset populated with data from my sql server, snippet of code as follows:


Dim totalRecords As Integer
Dim count As Integer = 1
totalRecords = DataSet11.Tables(0).Rows.Count
Dim myrow As DataRow
Dim mymindate As DateTime
mymindate = Now()
For Each myrow In DataSet11.Tables(0).Rows
If IsDate(myrow.Item("start")) = True Then
If myrow.Item("start") < mymindate Then
mymindate = myrow.Item("start")
End If
End If
Next
prj.StartDate = DateTime.Parse(mymindate)

Dim alltasks(totalRecords) As Task
Dim duration(totalRecords) As Duration
Dim mydate As System.DateTime
Do While count < totalRecords
If IsDate(Me.DataSet11.Tables(0).Rows(count - 1).Item("start")) = True And IsDate(Me.DataSet11.Tables(0).Rows(count - 1).Item("finish")) = True Then
alltasks(count) = file.AddTask()
alltasks(count).Name = Me.DataSet11.Tables(0).Rows(count - 1).Item("name")
alltasks(count).ID = Me.DataSet11.Tables(0).Rows(count - 1).Item("ID")
alltasks(count).OutlineLevel = Me.DataSet11.Tables(0).Rows(count - 1).Item("outline level")
alltasks(count).OutlineNumber = Me.DataSet11.Tables(0).Rows(count - 1).Item("outline number")
'alltasks(count).Duration = New Duration("10d")


alltasks(count).Start = (Me.DataSet11.Tables(0).Rows(count - 1).Item("start"))
alltasks(count).Finish = (Me.DataSet11.Tables(0).Rows(count - 1).Item("finish"))
If Me.DataSet11.Tables(0).Rows(count - 1).Item("outline number") Like "*.1*" Then
alltasks(count).ConstraintType = Constraint.SNET
End If

End If
count = count + 1
Loop

which works fine, exactly as I was hoping I would be able to do
but I still have one remaining problem

If I created a duration from the start and finish dates in my dataset this would be in calendar days not working days

I thhought I would be able to supply Start and Finish dates to MS Project and it would create the duration for me but when I do that Ms Project creates a duration of 1 day and ignores the supplied finish date.


do you have any suggestions ?


Yes, in most cases MS Project ignores finish date and do all calculations based on duration.
So to make everything correct you should define duration of a task.
The right solution is calculate number of working days between start and finish dates.
You can use task’s or default base calendar for that.

Hi many thanks for the prompt reply
could you give me an example of how I could use the task calendar to calculate the working days between two dates in vb please
Great Product by the way
Richard

Dear Richard,

I’m sorry for delay.
You can use Calendar.GetDuration function.
It returns duration in days (doesn’t check time).

Hi alcrus
Many Thanks for that, it works fine now

Just another nice to have, is it possible to control the Timescale
could I change the middle tier units from weeks to years etc

Richard

Dear Richard,

We have internal function for that but it’s not really correct because
we calculate all days as 8 hours, week as 40 hours and etc.
For correct converting it’s necessary to check calendars of course.