Here is the sample

Hi,

Have you been able to resolve your issue ? If you still have any concerns or questions I will be glad to help. If you need any additional assistance don’t hesitate to ask.

Regards

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

Hello

Thanks for your reply.

I shall be much obliged if you can send me a sample using sqlcommand.

Regards

jeni