Hello Jeni,

jeni said:

This sample is not working for my version

I`m not sure for which version are you talking, but this sample is independant of Infragistics controls version and can be used everywhere.

Please take a look at the SQL script. There are both tables that I`m using in my database

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[dbProjects](

      [ProjectID] [uniqueidentifier] NOT NULL,

      [ProjectKey] [nvarchar](50) NOT NULL,

      [ProjectName] [nvarchar](50) NULL,

      [ProjectStartTime] [datetime] NULL,

 CONSTRAINT [PK_dbProjects] PRIMARY KEY CLUSTERED

(

      [ProjectKey] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[dbTasks](

      [TaskID] [uniqueidentifier] NOT NULL,

      [ProjectKey] [nvarchar](50) NULL,

      [TaskName] [nvarchar](50) NULL,

      [TaskStartTime] [datetime] NULL,

      [TaskDuration] [time](7) NOT NULL,

      [ParentTaskID] [uniqueidentifier] NULL,

      [TaskPercentComplete] [int] NULL,

      [AllProperties] [varbinary](max) NULL,

 CONSTRAINT [PK_dbTasks] PRIMARY KEY CLUSTERED

(

      [TaskID] ASC,

      [TaskDuration] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

ALTER TABLE [dbo].[dbTasks]  WITH NOCHECK ADD  CONSTRAINT [FK_dbTasks_dbProjects] FOREIGN KEY([ProjectKey])

REFERENCES [dbo].[dbProjects] ([ProjectKey])

GO

ALTER TABLE [dbo].[dbTasks] CHECK CONSTRAINT [FK_dbTasks_dbProjects]

GO

Let me know if you have any further questions.

Regards

This sample is not working for my version. Please give me the schema of the database. So I can try in my version and see whether this works.

Thanks in advance

Hello Jeni,

I`m not sure what  are the reasons to choose approach with SQL commands, but my personal oppinion is that this approach is more complicate than others (for example EntityDataModel or TableAdapters). Nevertheless you could solve your task by this way. Lest`s splite your question to few different tasks:

jeni said:

how to add a new task and save the data to the database

Generaly we have two possible approaches to do that. The first option could be if we are using StoredProcedures from database. Of course we should create it at our server. We could call this StoredProcedures from our application using SqlCommand. By this way we will provide only required parameters from our new Task to stored procedure. I think that this is better approach than my next suggestion, because I could include additinal validation, checks and rules in the StoredProcedure, before to decide to store the Data / chages in the database. Right now we are talking how to add a new Task, so I`ll handle ultraGanttView1_TaskAdded() event and will include the code below:

private void ultraGanttView1_TaskAdded(object sender, Infragistics.Win.UltraWinGanttView.TaskAddedEventArgs e)

        {

            ErrorCode = 0;

            ErrorMessage = "";

            SqlCommand cmd = sqlConnection.CreateCommand();

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.CommandText = "sp_GanttTasks_InsertTask";

            cmd.Parameters.Add("@RC", SqlDbType.Int);

            cmd.Parameters.Add("@TaskID", SqlDbType.UniqueIdentifier);

            cmd.Parameters.Add("@ProjectKey", SqlDbType.VarChar, 50);

            cmd.Parameters.Add("@TaskName", SqlDbType.VarChar, 50);

            cmd.Parameters.Add("@TaskStartTime", SqlDbType.DateTime);

            cmd.Parameters.Add("@TaskDuration", SqlDbType.DateTimeOffset);

            cmd.Parameters.Add("@ParentTaskID", SqlDbType.UniqueIdentifier);

            cmd.Parameters.Add("@TaskPercentComplete", SqlDbType.Int);

            cmd.Parameters.Add("@AllProperties", SqlDbType.Binary);

            cmd.Parameters.Add("@iErrorCode", SqlDbType.Int);

            cmd.Parameters.Add("@sMessage", SqlDbType.VarChar, 1024);

            cmd.Parameters["@RC"].Direction = ParameterDirection.ReturnValue;

            cmd.Parameters["@TaskID"].Direction = ParameterDirection.Input;

            cmd.Parameters["@ProjectKey"].Direction = ParameterDirection.Input;

            cmd.Parameters["@TaskName"].Direction = ParameterDirection.Input;

            cmd.Parameters["@TaskStartTime"].Direction = ParameterDirection.Input;

            cmd.Parameters["@TaskDuration"].Direction = ParameterDirection.Input;

            cmd.Parameters["@ParentTaskID"].Direction = ParameterDirection.Input;

            cmd.Parameters["@TaskPercentComplete"].Direction = ParameterDirection.Input;

            cmd.Parameters["@AllProperties"].Direction = ParameterDirection.Input;

            cmd.Parameters["@iErrorCode"].Direction = ParameterDirection.Output;

            cmd.Parameters["@sMessage"].Direction = ParameterDirection.Output;

            cmd.Parameters["@TaskID"].Value = e.Task.Id;

            cmd.Parameters["@ProjectKey"].Value = e.Task.ProjectKey;

            cmd.Parameters["@TaskName"].Value = e.Task.Name;

            cmd.Parameters["@TaskStartTime"].Value = e.Task.StartDateTime;

            cmd.Parameters["@TaskDuration"].Value = e.Task.Duration; ;

            //..... and so on for all other properties

            cmd.ExecuteNonQuery();

            // Check for errors from StoredProcedure

            if (Convert.ToInt32(cmd.Parameters["@iErrorCode"].Value) > 0)

            {

                ErrorCode = Convert.ToInt32(cmd.Parameters["@iErrorCode"].Value);

                ErrorMessage = Convert.ToString(cmd.Parameters["@sMessage"].Value);

            }

        }

Our second option could be without StoreProcedure, but using this approach we should include our validation, checksm rules and so into our application`s code. By this way each time when we need to change our validation, checks rules, we should build a new version. If you are using my first suggestion, than you could modify your StoreProcedure and everything will start to wrok with the new rules. But this is developer`s decision. Here is sample with my second approach:

SqlCommand myCMD = new SqlCommand("INSERT INTO [Test].[dbo].[dbTasks]([TaskID],[ProjectKey],[TaskName],[TaskStartTime],[TaskDuration],[ParentTaskID],[TaskPercentComplete],[AllProperties])VALUES(" + e.Task.Id + ", " + e.Task.ProjectKey + ", " + " and so on for all properties", sqlConnection);

SqlDataReader myReader = myCMD.ExecuteReader();

myReader.Dispose();

sqlConnection.Close();

jeni said:

how do we edit and save the data back to the database

For this scenario you could use both approaches that I explain above, the difference will be:

1. You should handel appropriate event which will fired after changes into your existing Task

2. If you are using StoreProcedure - you should use another procedure which will make updates into your database

3. If you are using direct SQL Command, just use Update instead of Insert script

Please let me knwo if you have any further questions.

Regards

Hello

As if you have explained in the third option I have managed to display data in the ganntview tasks and sub-tasks  My issue is how do we edit and save the data back to the database, also adding new tasks and sub-tasks and save it into database.

I followed an approach almost similar to the third option but Projects and Tasks are different tables,

The basic functionality adding new task, sub-task,edit delete task etc is to be included.

Thanks in advance.

Regards

jeni

Hello Jeni,

Please take a look at the few different approaches :

Option 1:

DataTable dt = new DataTable();

System.Data.OleDb.OleDbConnection DataBaseConnection = new System.Data.OleDb.OleDbConnection("Provider=MSDataShape;Data Provider=SQLOLEDB;Data Source=MyServer;Integrated Security=SSPI;Initial Catalog=Test");

System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM [dbTasks] WITH(NOLOCK)", DataBaseConnection);

adapter.Fill(dt);

ultraCalendarInfo1.DataBindingsForTasks.DataSource = dt;

Option 2:

  SqlConnection sqlConnection;

  SqlConnectionStringBuilder sqlBuilder = new SqlConnectionStringBuilder();

  sqlBuilder.PersistSecurityInfo = true;

  sqlBuilder.DataSource = "MyServer";

  sqlBuilder.InitialCatalog = "Test";

  sqlBuilder.IntegratedSecurity = true;

  sqlBuilder.ConnectTimeout = 30;

sqlConnection = new SqlConnection(sqlBuilder.ConnectionString);

sqlConnection.Open();

SqlCommand myCMD = new SqlCommand("SELECT * FROM [dbTasks]", sqlConnection);

SqlDataReader myReader = myCMD.ExecuteReader();

while (myReader.Read())

            {

                ultraCalendarInfo1.DataBindingsForTasks.DataSource = myReader.AsQueryable();

                this.ultraCalendarInfo1.DataBindingsForTasks.NameMember = "TaskName";

                this.ultraCalendarInfo1.DataBindingsForTasks.DurationMember = "TaskDuration";

                this.ultraCalendarInfo1.DataBindingsForTasks.StartDateTimeMember = "TaskStartTime";

                this.ultraCalendarInfo1.DataBindingsForTasks.IdMember = "TaskID";

                this.ultraCalendarInfo1.DataBindingsForTasks.ProjectKeyMember = "ProjectKey";

                this.ultraCalendarInfo1.DataBindingsForTasks.ParentTaskIdMember = "ParentTaskID";

                this.ultraCalendarInfo1.DataBindingsForTasks.PercentCompleteMember = "TaskPercentComplete";

                this.ultraCalendarInfo1.DataBindingsForTasks.AllPropertiesMember = "AllProperties";

            }

Option 3:

SqlConnection conn = new SqlConnection();

conn.ConnectionString = "Data Source=MyServer;Initial Catalog=Test;Integrated Security=True";

SqlCommand cmd = new SqlCommand("select * from [dbTasks] inner join [dbRows] ON [dbItems].ID =[dbRows].ID", conn);

conn.Open();

SqlDataReader dr = cmd.ExecuteReader();

while (dr.Read())

            {

               dtProjects.Rows.Add(dr[0], dr[1]);

               dtTasks.Rows.Add(dr[2], dr[3], dr[4]);

            }

Please let me know if you have any further questions.

Regards