Introduction

This article will help you to create your own web-based project scheduling using ASP.Net GridView. In one of my projects I created a web-based project scheduling, but in that project I used a Telerik pivot grid. But I thought to create the same functionality without using any third-party controls. As a result I developed the same functionality using an ASP.Net GridView, so that everyone can download and use it.

Project Scheduling is a very important part in project planning. The project might be any type, for example software project development planning, Production Planning and and so on. For a realistic example let's consider a Car Seat Manufacturing Company. Every week they will produce, for example, 100 set of seats for a model of car. In the factory everything will go as planned, for example from this week beginning Monday to this week ending Friday a total of 100 seats need to be manufactured and delivered to the customer. Here we can see this is the plan since we need to produce 100 seats and deliver them to the customer. But for some reason the production could only make 90 sets of seats or production has made 100 set of seats in time. To track the Production Plan with the Actual Plan we use the Production Schedule Chart. The Production Plan will have both a Start and End Date, when the production must be started and when the production needs to be finished. The Actual date is the real production start and end date. The actual Start and End dates will be set after the production is completed. If the Actual date is the same or below the Production End date then it's clear that the production is on time and it can be delivered to the customer. If the Actual End date is past the production plan date then the production line must be closely watched and again the next time the same delay should be avoided.

In the project there might be 2 dates available, one is the Scheduled Start and End Dates (this is the initial planned date or the target date for our project) and another one is the Actual Start and End Date (this is when the project is actually started and completed). For all the projects we need to compare both the Scheduled and Actual dates, if there are greater differences in both of the dates then we need to check whether the project is completed within the scheduled time or if there was a delay in project development.

Here you can see the "View Type" column for each project and project type, I will display the first row for the Schedule Date (Scd) and the next row for the Actual Date (Act). The user can compare both results.

In my program I will display both the Scheduled and Actual plan details. By this the manager can view all the projects or he can search for one project and view the details and produce the report.

I have created the following two kinds of scheduling program:
  1. Production Plan Schedule with Actual Date comparison (the top image is the first type); it is Default.aspx
  2. Production Plan Schedule with Actual date with End Date Status display (see the following image); it is Default2.aspx


We can see here I have displayed the *Star in each end row so the user can easily find each project Schedule and Actual End Date.

Code Part

The main purpose of this article is to create a simple project scheduling and compare the results with the Scheduled date and with the Actual date.

The code part can be divided as:
  1. Back end part (used Stored Procedure in DB)
  2. Front end part (ASP.NET GridView)
In the backend is the Stored Procedure in which I applied all the logic to produce our output. It will be easy and simple if we just modify our sp and find the result in our ASP.Net.

In ASP.Net I have created a GridView dynamically using the GridView helper class created by me. Let's see each part in detail.
  • First we start with the Back End
Create Table: I have created and inserted sample data that will be used in this project, here is the script to create the database and insert some data.
  1. CREATE TABLE [dbo].[SCHED_Master](
  2. [ID] [int] NOT NULL,
  3. [ProjectName] [varchar](100) NULL,
  4. [ProjectType] int NULL,
  5. [ProjectTypeName] [varchar](100) NULL,
  6. [SCHED_ST_DT] [datetime] NULL,
  7. [SCHED_ED_DT] [datetime] NULL,
  8. [ACT_ST_DT] [datetime] NULL,
  9. [ACT_ED_DT] [datetime] NULL,
  10. [status] int null
  11. PRIMARY KEY CLUSTERED
  12. (
  13. [ID] ASC
  14. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  15. ) ON [PRIMARY]
  17. Insert Query
  21. INSERT INTO [dbo].SCHED_Master
  22. ([ID],[ProjectName],[ProjectType],[ProjectTypeName],[SCHED_ST_DT],[SCHED_ED_DT],[ACT_ST_DT],[ACT_ED_DT],[status])
  23. VALUES
  24. (1001,'Project1',1,'Urgent','2014-06-01 00:00:00.000','2014-09-02 00:00:00.000'
  25. ,'2014-06-22 00:00:00.000','2014-08-26 00:00:00.000',1)
  28. INSERT INTO [dbo].SCHED_Master
  29. ([ID],[ProjectName],[ProjectType],[ProjectTypeName],[SCHED_ST_DT],[SCHED_ED_DT],[ACT_ST_DT],[ACT_ED_DT],[status])
  30. VALUES
  31. (1002,'Project1',2,'Important','2014-09-22 00:00:00.000','2014-12-22 00:00:00.000'
  32. ,'2014-09-19 00:00:00.000','2014-12-29 00:00:00.000',1)
  34. INSERT INTO [dbo].SCHED_Master
  35. ([ID],[ProjectName],[ProjectType],[ProjectTypeName],[SCHED_ST_DT],[SCHED_ED_DT],[ACT_ST_DT],[ACT_ED_DT],[status])
  36. VALUES
  37. (1003,'Project1',3,'Normal','2015-01-01 00:00:00.000','2015-03-24 00:00:00.000'
  38. ,'2015-01-01 00:00:00.000','2015-03-14 00:00:00.000',1)
  41. INSERT INTO [dbo].SCHED_Master
  42. ([ID],[ProjectName],[ProjectType],[ProjectTypeName],[SCHED_ST_DT],[SCHED_ED_DT],[ACT_ST_DT],[ACT_ED_DT],[status])
  43. VALUES
  44. (1004,'Project2',1,'Urgent','2014-07-01 00:00:00.000','2014-09-02 00:00:00.000'
  45. ,'2014-07-22 00:00:00.000','2014-08-26 00:00:00.000',1)
  48. INSERT INTO [dbo].SCHED_Master
  49. ([ID],[ProjectName],[ProjectType],[ProjectTypeName],[SCHED_ST_DT],[SCHED_ED_DT],[ACT_ST_DT],[ACT_ED_DT],[status])
  50. VALUES
  51. (1005,'Project2',2,'Important','2014-09-29 00:00:00.000','2014-12-22 00:00:00.000'
  52. ,'2014-09-08 00:00:00.000','2014-12-14 00:00:00.000',1)
  54. INSERT INTO [dbo].SCHED_Master
  55. ([ID],[ProjectName],[ProjectType],[ProjectTypeName],[SCHED_ST_DT],[SCHED_ED_DT],[ACT_ST_DT],[ACT_ED_DT],[status])
  56. VALUES
  57. (1006,'Project2',3,'Normal','2015-01-01 00:00:00.000','2015-03-04 00:00:00.000'
  58. ,'2015-01-01 00:00:00.000','2015-02-24 00:00:00.000',1)
  61. -- Select Query
  63. select ID,ProjectName,ProjectType,ProjectTypeName,SCHED_ST_DT,SCHED_ED_DT,ACT_ST_DT,ACT_ED_DT,status from SCHED_Master
Here I created a table with ProjectName, projectType, ScheduleStartDate, ScheduleEndDate, ActualStartdate and ActualEndDate. I have inserted sample data into the table with a schedule and actual start and end dates. We will use this sample data to produce our Scheduling details.

Stored Procedure: In this Stored Procedure I have applied all the logic to produce our schedule output using a Pivot query. In my procedure I have added a comment in each section for better understanding by the user.
  1. -- =============================================
  2. -- Author : Shanu
  3. -- Create date : 2014-11-24
  4. -- Description : To get all prject Schedule details
  5. -- Latest
  6. -- Modifier : Shanu
  7. -- Modify date : 2014-11-24
  8. -- =============================================
  9. -- usp_ProjectSchedule_FNStatus 'Project1'
  10. -- usp_ProjectSchedule_FNStatus ''
  11. -- =============================================
  12. Alter PROCEDURE [dbo].[usp_ProjectSchedule_FNStatus]
  13. @projectId VARCHAR(10) = ''
  15. AS
  16. BEGIN
  18. -- 1. Declared for setting the Schedule Start and End date
  19. --1.Start /////////////
  20. Declare @FromDate VARCHAR(20) = '2014-05-29'--DATEADD(mm,-12,getdate())
  21. Declare @ToDate VARCHAR(20) = '2015-05-01'--DATEADD(mm, 1, getdate())
  22. -- used for the pivot table result
  23. DECLARE @MyColumns AS NVARCHAR(MAX),
  24. @SQLquery AS NVARCHAR(MAX)
  25. --// End of 1.
  27. -- 2.This Temp table is to created for get all the days between the start date and end date to display as the Column Header
  28. --2.Start /////////////
  29. IF OBJECT_ID('tempdb..#TEMP_EveryWk_Sndays') IS NOT NULL
  30. DROP TABLE #TEMP_EveryWk_Sndays
  32. DECLARE @TOTALCount INT
  33. Select @TOTALCount= DATEDIFF(dd,@FromDate,@ToDate);
  34. WITH d AS
  35. (
  36. SELECT top (@TOTALCount) AllDays = DATEADD(DAY, ROW_NUMBER()
  37. OVER (ORDER BY object_id), REPLACE(@FromDate,'-',''))
  38. FROM sys.all_objects
  39. )
  41. SELECT distinct DATEADD(DAY, 1 - DATEPART(WEEKDAY, AllDays), CAST(AllDays AS DATE))WkStartSundays ,1 as status
  43. into #TEMP_EveryWk_Sndays
  44. FROM d
  45. where
  46. AllDays <= @ToDate
  47. AND AllDays >= @FromDate
  49. -- test the sample temptable with select query
  50. -- select * from #TEMP_EveryWk_Sndays
  51. --///////////// End of 2.
  53. -- 3. This temp table is created toScedule details with result here i have used the Union ,
  54. --the 1st query return the Schedule Project result and the 2nd query returns the Actual Project result both this query will be inserted to a Temp Table
  55. --3.Start /////////////
  56. IF OBJECT_ID('tempdb..#TEMP_results') IS NOT NULL
  57. DROP TABLE #TEMP_results
  59. SELECT ProjectName,viewtype,ProjectType,resultnew,YMWK
  60. INTO #TEMP_results
  61. FROM(
  62. SELECT
  63. A.ProjectName ProjectName -- Our Project Name
  64. ,'1-Scd' viewtype -- Our View type first we display Schedule Data and then Actual
  65. , A. ProjectType ProjectType -- Our Project type here you can use your own status as Urgent,normal and etc
  66. , Case when cast(DATEPART( wk, max(A.SCHED_ED_DT)) as varchar(2)) = cast(DATEPART( wk, WkStartSundays) as varchar(2)) then 2 else
  67. case when min(A.SCHED_ST_DT)<= F.WkStartSundays AND max(A.SCHED_ED_DT) >= F.WkStartSundays
  68. then 1 else 0 end end resultnew -- perfectResult as i expect
  69. , RIGHT(YEAR(WkStartSundays), 2)+'-'+'W'+convert(varchar(2),Case when len(DATEPART( wk, WkStartSundays))='1' then '0'+
  70. cast(DATEPART( wk, WkStartSundays) as varchar(2)) else cast(DATEPART( wk, WkStartSundays) as varchar(2)) END
  71. ) as 'YMWK' -- Here we display Year/month and Week of our Schedule which will be displayed as the Column
  73. FROM -- here you can youe your own table
  74. SCHED_Master A (NOLOCK)
  75. LEFT OUTER JOIN
  76. #TEMP_EveryWk_Sndays F (NOLOCK) ON A.status= F.status
  78. WHERE -- Here you can check your own where conditions
  79. A.ProjectName like '%' + @projectId
  80. AND A.status=1
  81. AND A.ProjectType in (1,2,3)
  82. AND A.SCHED_ST_DT <= @ToDate
  83. AND A.SCHED_ED_DT >= @FromDate
  84. GROUP BY
  85. A.ProjectName
  86. , A. ProjectType
  87. ,A.SCHED_ED_DT
  88. ,F.WkStartSundays
  90. UNION -- This query is to result the Actual result
  91. SELECT
  92. A.ProjectName ProjectName -- Our Project Name
  93. ,'2-Act' viewtype -- Our View type first we display Schedule Data and then Actual
  94. , A. ProjectType ProjectType -- Our Project type here you can use your own status as Urgent,normal and etc
  95. , Case when cast(DATEPART( wk, max(A.ACT_ED_DT)) as varchar(2)) = cast(DATEPART( wk, WkStartSundays) as varchar(2)) then 2 else
  96. case when min(A.ACT_ST_DT)<= F.WkStartSundays AND max(A.ACT_ED_DT) >= F.WkStartSundays
  97. then 1 else 0 end end resultnew -- perfectResult as i expect
  99. , RIGHT(YEAR(WkStartSundays), 2)+'-'+'W'+convert(varchar(2),Case when len(DATEPART( wk, WkStartSundays))='1' then '0'+
  100. cast(DATEPART( wk, WkStartSundays) as varchar(2)) else cast(DATEPART( wk, WkStartSundays) as varchar(2)) END
  101. ) as 'YMWK' -- Here we display Year/month and Week of our Schedule which will be displayed as the Column
  103. FROM -- here you can youe your own table
  104. SCHED_Master A (NOLOCK)
  105. LEFT OUTER JOIN
  106. #TEMP_EveryWk_Sndays F (NOLOCK) ON A.status= F.status
  108. WHERE -- Here you can check your own where conditions
  109. A.ProjectName like '%' + @projectId
  110. AND A.status=1
  111. AND A.ProjectType in (1,2,3)
  112. AND A.ACT_ST_DT <= @ToDate
  113. AND A.ACT_ED_DT >= @FromDate
  114. GROUP BY
  115. A.ProjectName
  116. , A. ProjectType
  117. ,A.SCHED_ED_DT
  118. ,F.WkStartSundays
  120. ) q
  122. --3.End /////////////
  124. --4.Start /////////////
  126. --here first we get all the YMWK which should be display in Columns we use this in our next pivot query
  127. select @MyColumns = STUFF((SELECT ',' + QUOTENAME(YMWK)
  128. FROM #TEMP_results
  129. GROUP BY YMWK
  130. ORDER BY YMWK
  131. FOR XML PATH(''), TYPE
  132. ).value('.', 'NVARCHAR(MAX)')
  133. ,1,1,'')
  134. --here we use the above all YMWK to disoplay its result as column and row display
  135. set @SQLquery = N'SELECT ProjectName,viewtype,ProjectType,' + @MyColumns + N' from
  136. (
  137. SELECT
  138. ProjectName,
  139. viewtype,
  140. ProjectType,
  141. YMWK,
  142. resultnew as resultnew
  143. FROM #TEMP_results
  144. ) x
  145. pivot
  146. (
  147. sum(resultnew)
  148. for YMWK in (' + @MyColumns + N')
  149. ) p order by ProjectName, ProjectType,viewtype'
  151. exec sp_executesql @SQLquery;
  154. END
  • ASP.Net GridView Code part
I have created a grid view dynamically using my helper class. In my helper class I have the following function to make the design and binding simple. The user can extend my helper class and use it in their project.

In my previous article http://www.c-sharpcorner.com/UploadFile/asmabegam/creating-dynamic-datagridview-using-helper-class/ I explained how to create a Helper class for a DataGridView C# Windows Forms application. The same logic has been used to create a helper class for an ASP.Net GridView. You can find my Helper Class in the attached source file.
  • Layout
  • DataBind
  • Template Column
The GridView layout "ShanuGDVHelper" is my Helper class to create a GridView at runtime. In the Zip file you can find my helper class. Here first I set the GridView width, height and created all the bound and Template columns. In the Template column I used a placeholder. I will add an image to the placeholder and I have changed the image URL by the status of the project type.
  1. protected void InitializeGridControl()
  2. {
  4. ShanuGDVHelper.Layouts(Grid1, 600, 99, true, false, false, true, true);
  6. ShanuGDVHelper.BoundColumnFormat(Grid1, "Project Name", "ProjectName", HorizontalAlign.Left, 0, "", "", false, true, VerticalAlign.Middle, HorizontalAlign.Left);
  7. ShanuGDVHelper.BoundColumnFormat(Grid1, "view Type", "viewtype", HorizontalAlign.Left, 0, "", "", false, true, VerticalAlign.Middle, HorizontalAlign.Left);
  8. ShanuGDVHelper.BoundColumnFormat(Grid1, "Project Type", "ProjectType", HorizontalAlign.Left, 0, "", "", false, true, VerticalAlign.Middle, HorizontalAlign.Left);
  10. SortedDictionary<string, string=""> sd = new SortedDictionary<string, string="">() { };
  11. sd.Add("@projectId", txtProjectID.Text.Trim());
  13. DataSet ds = new DataSet();
  14. ds = new ShanuProjectScheduleBizClass().SelectList(sd);
  16. for (int i = 3; i < ds.Tables[0].Columns.Count; i++)
  17. {
  19. ShanuGDVHelper.Templatecolumn(Grid1, ds.Tables[0].Columns[i].ColumnName, ds.Tables[0].Columns[i].ColumnName, HorizontalAlign.Left, 0, GDVControlType.placeholder, "", true, VerticalAlign.Middle, HorizontalAlign.Left);
  20. }
  23. //grid events
  24. ////////Grid1.RowCommand+=new GridViewCommandEventHandler(Grid1_RowCommand);
  25. //////// Grid1.RowCreated+=new GridViewRowEventHandler(Grid1_RowCreated);
  26. }
Image Bind: Using the Item template class I have bound the image to the placeholder and displayed the images depending on the status.
  1. void plcHolder_DataBinding(object sender, EventArgs e)
  2. {
  4. PlaceHolder txtdata = (PlaceHolder)sender;
  5. GridViewRow container = (GridViewRow)txtdata.NamingContainer;
  6. object dataValue = DataBinder.Eval(container.DataItem, DataFieldName);
  7. object dataValue1 = DataBinder.Eval(container.DataItem, "ProjectType"); // here I have used this column as static user can change this to work with your program .here i have used this to check for Project type status and load the images
  9. Image img = new Image();
  10. if (Convert.ToInt32(dataValue) == 1)
  11. {
  12. img.ImageUrl = GetImage(Convert.ToInt32(dataValue1.ToString()));
  13. }
  14. else if (Convert.ToInt32(dataValue) == 2)
  15. {
  16. img.ImageUrl = GetImage_ScdEnd(Convert.ToInt32(dataValue1.ToString()));
  17. }
  18. else
  19. {
  20. img.ImageUrl = "~/Images/blanks.jpg";
  21. }
  23. img.Style["float"] = "center";
  24. txtdata.Controls.Add(img);
  26. }
  28. private string GetImage(int value)
  29. {
  30. if (value == 1)
  31. {
  32. return "~/Images/red_new1.jpg";
  33. }
  34. else if (value == 2)
  35. {
  36. return "~/Images/blue_new1.jpg";
  37. }
  38. else
  39. {
  40. return "~/Images/green_new1.jpg";
  41. }
  42. }

Bind Dataset to GridView: In my project I used a Biz Class and a SQL helper class to connect to the database and return the dataset to bind to the GridView. You can find the Biz and SQL helper classes under the “App_code” folder in my Zip file. The user can search the project by projectName, for example in a button click event I called the SelectList Method to bind the result to the GridView.

  1. public void SelectList()
  2. {
  3. SortedDictionary<string, string=""> sd = new SortedDictionary<string, string="">() { };
  4. sd.Add("@projectId", txtProjectID.Text.Trim());
  6. DataSet ds = new DataSet();
  7. ds = new ShanuProjectScheduleBizClass().SelectList(sd);
  9. if (ds.Tables.Count > 0)
  10. {
  11. if (ds.Tables[0].Rows.Count > 0)
  12. {
  13. ShanuGDVHelper.DataBinds(Grid1, ds, false);
  14. }
  15. }
  16. }
  18. // Button Click event
  20. protected void btnSearch_Click(object sender, ImageClickEventArgs e)
  21. {
  22. SelectList();
  23. }
You can see that here I called my Biz class "SelectList" method to return the dataset. In my biz class method I will send my Sp name to get the data.
  1. //to return the dataset
  2. public DataSet SelectList(SortedDictionary<string, string=""> sd)
  3. {
  4. try
  5. {
  6. return SqlHelper.ExecuteDataset(ConnectionString, CommandType.StoredProcedure, "usp_ProjectSchedule", GetSdParameter(sd));
  7. }
  8. catch (Exception ex)
  9. {
  10. throw ex;
  11. }
  12. }
Note: This demo application has been developed using Visual Studio 2010. The following procedure must be followed to run the program.
  1. Create the table and Stored Procedure to your database.
  2. Change the WebConfig database connection string to your local DB Server, UID and PWD.