Post
Ask Question

Project Scheduling Using ASP.Net GridView

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]    
  16.     
  17.  Insert Query    
  18.     
  19.     
  20.     
  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)    
  26.     
  27.     
  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)    
  33.     
  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)    
  39.     
  40.     
  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)    
  46.     
  47.     
  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)    
  53.     
  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)    
  59.     
  60.     
  61. -- Select Query    
  62.     
  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)  = ''                                                                   
  14.                                                            
  15. AS                                                                        
  16. BEGIN                                                         
  17.        
  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.  
  26.     
  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                                                                         
  31.                                                                             
  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.             )                                                                        
  40.                                                                               
  41.          SELECT  distinct DATEADD(DAY, 1 - DATEPART(WEEKDAY, AllDays), CAST(AllDays AS DATE))WkStartSundays  ,1 as status                                                                
  42.                                                                              
  43.  into #TEMP_EveryWk_Sndays                                                                      
  44.     FROM d                               
  45.    where                            
  46.         AllDays  <= @ToDate                                        
  47.    AND AllDays  >= @FromDate          
  48.      
  49.    -- test the sample temptable with select query  
  50.   -- select * from #TEMP_EveryWk_Sndays  
  51.    --///////////// End of 2.  
  52.      
  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     
  58.    
  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                   
  72.   
  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                                                              
  77.                                                         
  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  
  89.   
  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   
  98.                           
  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                   
  102.   
  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                                                              
  107.                                                         
  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  
  119.   
  120.      )  q                   
  121.   
  122.  --3.End /////////////  
  123.   
  124.  --4.Start /////////////  
  125.    
  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'  
  150.   
  151. exec sp_executesql @SQLquery;  
  152.                                                  
  153.                                                     
  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.     {  
  3.   
  4.         ShanuGDVHelper.Layouts(Grid1, 600, 99, true, false, false, true, true);  
  5.   
  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);  
  9.         
  10.         SortedDictionary<string, string=""> sd = new SortedDictionary<string, string="">() { };  
  11.         sd.Add("@projectId", txtProjectID.Text.Trim());  
  12.   
  13.         DataSet ds = new DataSet();  
  14.         ds = new ShanuProjectScheduleBizClass().SelectList(sd);  
  15.   
  16.         for (int i = 3; i < ds.Tables[0].Columns.Count; i++)  
  17.         {  
  18.   
  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.         }  
  21.   
  22.   
  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.     {  
  3.   
  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  
  8.   
  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.         }  
  22.           
  23.         img.Style["float"] = "center";  
  24.         txtdata.Controls.Add(img);  
  25.   
  26.     }  
  27.   
  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());  
  5.   
  6.         DataSet ds = new DataSet();  
  7.         ds = new ShanuProjectScheduleBizClass().SelectList(sd);  
  8.   
  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.     }  
  17.   
  18.  // Button Click event  
  19.   
  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.


Similar Articles