Project Scheduling using ASP.NET GridView

• Download source - 292.5 KB

Introduction

This article will help you to create your own Web based project Scheduling using ASP.NET Gridview. In one of my projects, I have created a Web based project scheduling, but in that project, I have used Telerik pivot grid. But I thought of creating the same functionality without using any Third party controls. As a result, I developed the same functionality using 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, etc. In Project, there might be two dates available, one is the Schedule Start and End Date (This is the initial plan date or target date for our project) and another one Actual Start and End Date (This is when actually the project is Started and Completed). For all projects, we need to compare both Schedule and Actual date, if there are more differences in both the dates, then need to check for the project is completed within scheduled time or delay in project development.

Here, you can see "View Type" Column for each Project and project type, I will display the first row for the Schedule Date and next row for the Actual date. User can compare with both the results.

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

I have created two kinds of Scheduling Programs:

1. Production Plan Schedule with Actual Date comparison (the top image is first type).
2. Production Plan Schedule with Actual date with End Date Status display (see the below image).

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

Using the Code

The main aim of this article is to create a simple project scheduling and compare the result with Schedule date and with 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 backend that is in stored procedure, I did apply 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.

1. First, We Start with the Back End

Create Table: I have created and inserted sample data which will be used in this project. Here is the script to create and insert query.

CREATE TABLE [dbo].[SCHED_Master](
	[ID] [int] NOT NULL,
	[ProjectName] [varchar](100) NULL,
	[ProjectType] int NULL,
	[ProjectTypeName] [varchar](100) NULL,
	[SCHED_ST_DT] [datetime] NULL,
	[SCHED_ED_DT] [datetime] NULL,	
	[ACT_ST_DT] [datetime] NULL,
	[ACT_ED_DT] [datetime] NULL,
	[status] int null
PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, _
 IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

-- Insert Query

INSERT INTO [dbo].SCHED_Master
           ([ID],[ProjectName],[ProjectType],[ProjectTypeName],_
           [SCHED_ST_DT],[SCHED_ED_DT],[ACT_ST_DT],[ACT_ED_DT],[status])
     VALUES
           (1001,'Project1',1,'Urgent','2014-06-01 00:00:00.000','2014-09-02 00:00:00.000'
            ,'2014-06-22 00:00:00.000','2014-08-26 00:00:00.000',1)

INSERT INTO [dbo].SCHED_Master
           ([ID],[ProjectName],[ProjectType],[ProjectTypeName],_
           [SCHED_ST_DT],[SCHED_ED_DT],[ACT_ST_DT],[ACT_ED_DT],[status])
     VALUES
           (1002,'Project1',2,'Important','2014-09-22 00:00:00.000','2014-12-22 00:00:00.000'
            ,'2014-09-19 00:00:00.000','2014-12-29 00:00:00.000',1)

INSERT INTO [dbo].SCHED_Master
           ([ID],[ProjectName],[ProjectType],[ProjectTypeName],_
           [SCHED_ST_DT],[SCHED_ED_DT],[ACT_ST_DT],[ACT_ED_DT],[status])
     VALUES
           (1003,'Project1',3,'Normal','2015-01-01 00:00:00.000','2015-03-24 00:00:00.000'
            ,'2015-01-01 00:00:00.000','2015-03-14 00:00:00.000',1)

INSERT INTO [dbo].SCHED_Master
           ([ID],[ProjectName],[ProjectType],[ProjectTypeName],_
           [SCHED_ST_DT],[SCHED_ED_DT],[ACT_ST_DT],[ACT_ED_DT],[status])
     VALUES
           (1004,'Project2',1,'Urgent','2014-07-01 00:00:00.000','2014-09-02 00:00:00.000'
            ,'2014-07-22 00:00:00.000','2014-08-26 00:00:00.000',1)

INSERT INTO [dbo].SCHED_Master
           ([ID],[ProjectName],[ProjectType],[ProjectTypeName],_
           [SCHED_ST_DT],[SCHED_ED_DT],[ACT_ST_DT],[ACT_ED_DT],[status])
     VALUES
           (1005,'Project2',2,'Important','2014-09-29 00:00:00.000','2014-12-22 00:00:00.000'
            ,'2014-09-08 00:00:00.000','2014-12-14 00:00:00.000',1)

INSERT INTO [dbo].SCHED_Master
           ([ID],[ProjectName],[ProjectType],[ProjectTypeName],_
           [SCHED_ST_DT],[SCHED_ED_DT],[ACT_ST_DT],[ACT_ED_DT],[status])
     VALUES
           (1006,'Project2',3,'Normal','2015-01-01 00:00:00.000','2015-03-04 00:00:00.000'
            ,'2015-01-01 00:00:00.000','2015-02-24 00:00:00.000',1)

-- Select Query

select ID,ProjectName,ProjectType,ProjectTypeName,SCHED_ST_DT,_
       SCHED_ED_DT,ACT_ST_DT,ACT_ED_DT,status from SCHED_Master

Here, I have created a table with ProjectName, projectType, ScheduleStartDate, ScheduleEndDate, ActualStartdate and ActualEndDate. I have inserted sample data to the above table with schedule and actual start and End dates. We will use this sample data to produce our Scheduling details.<o:p>

Stored Procedure: In this stored procedure, I have applied all the logic to produce our schedule output using Pivot query. In my procedure, I have added a comment in each section for user's better understanding.

-- =============================================                                                                      
-- Author      : Shanu                                                                      
-- Create date : 2014-11-24                                                                      
-- Description : To get all prject Schedule details                                                                     
-- Latest                                                                      
-- Modifier    : Shanu                                                                      
-- Modify date : 2014-11-24                                                                      
-- =============================================                                                                      
--  usp_ProjectSchedule_FNStatus 'Project1'               
--  usp_ProjectSchedule_FNStatus ''                                                                
-- =============================================                                                                 
Alter PROCEDURE [dbo].[usp_ProjectSchedule_FNStatus]                                                    
@projectId           VARCHAR(10)  = ''                                                                 
                                                         
AS                                                                      
BEGIN                                                       
     
 -- 1. Declared for setting the Schedule Start and End date
 --1.Start /////////////
  Declare   @FromDate          VARCHAR(20)  = '2014-05-29'--DATEADD(mm,-12,getdate())                                                           
  Declare   @ToDate            VARCHAR(20)  = '2015-05-01'--DATEADD(mm, 1, getdate())  
  -- used for the pivot table result
  DECLARE @MyColumns AS NVARCHAR(MAX),
    @SQLquery  AS NVARCHAR(MAX)     
  --// End of 1.
  
  -- 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                                                      
 --2.Start /////////////                                                                
 IF OBJECT_ID('tempdb..#TEMP_EveryWk_Sndays') IS NOT NULL                                                                          
    DROP TABLE #TEMP_EveryWk_Sndays                                                                       
                                                                          
 DECLARE @TOTALCount INT                                          
    Select  @TOTALCount= DATEDIFF(dd,@FromDate,@ToDate);           
   WITH d AS                                                                       
            (                                                                      
              SELECT top (@TOTALCount) AllDays = DATEADD(DAY, ROW_NUMBER()                                                                       
                OVER (ORDER BY object_id), REPLACE(@FromDate,'-',''))                                                                      
              FROM sys.all_objects                                             
            )                                                                      
                                                                            
         SELECT  distinct DATEADD(DAY, 1 - DATEPART(WEEKDAY, AllDays), _
                 CAST(AllDays AS DATE))WkStartSundays  ,1 as status                                                              
                                                                           
 into #TEMP_EveryWk_Sndays                                                                    
    FROM d                             
   where                          
        AllDays  <= @ToDate                                      
   AND AllDays  >= @FromDate        
   
   -- test the sample temptable with select query
  -- select * from #TEMP_EveryWk_Sndays
   --///////////// End of 2.
   
   -- 3. This temp table is created toScedule details with result here I have used the Union,
   --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
 --3.Start /////////////
 IF OBJECT_ID('tempdb..#TEMP_results') IS NOT NULL                                                                          
    DROP TABLE #TEMP_results   
 
	   SELECT ProjectName,viewtype,ProjectType,resultnew,YMWK
	   INTO #TEMP_results
	   FROM(
				SELECT                                                                
						 A.ProjectName ProjectName   -- Our Project Name                                       
						,'1-Scd' viewtype            -- Our View type first we display 
                                                     -- Schedule Data and then Actual                                                 
						, A. ProjectType ProjectType -- Our Project type here you can 
                                                     -- use your own status as Urgent,
                                                     -- normal, etc. 
						,  Case when   cast(DATEPART( wk, max(A.SCHED_ED_DT)) _
                           as varchar(2)) =  cast(DATEPART( wk, WkStartSundays) _
                           as varchar(2))  then 2 else
						   case when min(A.SCHED_ST_DT)<= F.WkStartSundays _
                           AND max(A.SCHED_ED_DT) >= F.WkStartSundays                                                        
						   then 1 else 0  end end resultnew  -- perfectResult as I expect   
					    ,  RIGHT(YEAR(WkStartSundays), 2)+'-'+'W'+convert(varchar(2),_
                           Case when len(DATEPART( wk, WkStartSundays))='1' then '0'+                                
						   cast(DATEPART( wk, WkStartSundays) as varchar(2)) _
                           else cast(DATEPART( wk, WkStartSundays) as varchar(2)) END                             
						  ) as 'YMWK'  -- Here we display Year/month and Week of our 
                                       -- Schedule which will be displayed as the Column                 

			  FROM   -- here you can youe your own table                                                          
						 SCHED_Master A (NOLOCK)       
								 LEFT OUTER JOIN 
						 #TEMP_EveryWk_Sndays F (NOLOCK)  ON A.status= F.status                                                            
			                                          
				WHERE  -- Here you can check your own where conditions     
						A.ProjectName like '%' + @projectId                                                      
					AND	A.status=1                                                                          
					AND A.ProjectType in (1,2,3) 
					AND A.SCHED_ST_DT  <= @ToDate                                          
				    AND A.SCHED_ED_DT  >= @FromDate  
				GROUP BY                                                             
					   A.ProjectName                                                         
					 , A. ProjectType  
					 ,A.SCHED_ED_DT                   
					,F.WkStartSundays

	UNION  -- This query is to result the Actual result
			SELECT                                                                
						 A.ProjectName ProjectName   -- Our Project Name                                       
						,'2-Act' viewtype            -- Our View type first 
						-- we display Schedule Data and then Actual                                                 
						, A. ProjectType ProjectType -- Our Project type here you can 
						        -- use your own status as Urgent,normal and etc 
						,  Case when   cast(DATEPART( wk, max(A.ACT_ED_DT)) _
						   as varchar(2)) =  cast(DATEPART( wk, WkStartSundays) _
                            as varchar(2))  then 2 else
							case when min(A.ACT_ST_DT)<= F.WkStartSundays _
							     AND max(A.ACT_ED_DT) >= F.WkStartSundays                                                        
						   then 1 else 0  end end resultnew  -- perfectResult as i expect 
						
					    , RIGHT(YEAR(WkStartSundays), 2)+'-'+'W'+_
					    convert(varchar(2),Case when len(DATEPART( wk, WkStartSundays))='1' _
                              then '0'+                                
							  cast(DATEPART( wk, WkStartSundays) as varchar(2)) _
							  else cast(DATEPART( wk, WkStartSundays) as varchar(2)) END                             
							  ) as 'YMWK'  -- Here we display Year/month and Week of 
							               -- our Schedule which will be displayed 
                                           -- as the Column                 

			  FROM   -- here, you can use your own table                                                          
						 SCHED_Master A (NOLOCK)       
								 LEFT OUTER JOIN 
						 #TEMP_EveryWk_Sndays F (NOLOCK)  ON A.status= F.status                                                            
			                                          
				WHERE  -- Here, you can check your own where conditions      
						A.ProjectName like '%' + @projectId                                                     
					AND	A.status=1                                                                          
					AND A.ProjectType in (1,2,3) 
					AND A.ACT_ST_DT  <= @ToDate                                          
				    AND A.ACT_ED_DT  >= @FromDate  
				GROUP BY                                                             
					   A.ProjectName                                                         
					 , A. ProjectType  
					 ,A.SCHED_ED_DT                   
					,F.WkStartSundays
	 )  q                 

 --3.End /////////////

 --4.Start /////////////
 
 --here, first we get all the YMWK which should be display in Columns we use this 
 --in our next pivot query
select @MyColumns = STUFF((SELECT ',' + QUOTENAME(YMWK) 
                    FROM #TEMP_results
                    GROUP BY YMWK
                    ORDER BY YMWK
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
 --here we use the above all YMWK  to display its result as column and row display
set @SQLquery = N'SELECT ProjectName,viewtype,ProjectType,' + @MyColumns + N' from 
             (
                 SELECT 
       ProjectName, 
       viewtype,
       ProjectType,
       YMWK,
        resultnew as resultnew 
    FROM #TEMP_results
            ) x
            pivot 
            (
                 sum(resultnew)
                for YMWK in (' + @MyColumns + N')
            ) p  order by ProjectName, ProjectType,viewtype'

exec sp_executesql @SQLquery;                                             
                                                  
END

2. ASP.NET Gridview Code Part

I have created gridview dynamically using my helper class. In my helper class, I have the following function to make the design and bind simple. User can extend my helper class and use it in their project.

• Layout
• DataBind
• Template Column

Gridview layout "ShanuGDVHelper" is my Helper class to create gridview at runtime. In zip file, you can find my helper class. Here, first I set gridview width, height and create all bound and Template Columns. In Template Column, I have used a place holder. I will add an Image to placeholder and I have changed the Image URL by the status of the project type.

protected void InitializeGridControl()
   {
       ShanuGDVHelper.Layouts(Grid1, 600, 99, true, false, false, true, true);

       ShanuGDVHelper.BoundColumnFormat(Grid1, "Project Name",
       "ProjectName", HorizontalAlign.Left, 0,          "", "", false, true,
       VerticalAlign.Middle, HorizontalAlign.Left);
       ShanuGDVHelper.BoundColumnFormat(Grid1, "view Type", "viewtype",
       HorizontalAlign.Left, 0, "", "", false, true,
       VerticalAlign.Middle, HorizontalAlign.Left);
       ShanuGDVHelper.BoundColumnFormat(Grid1, "Project Type",
       "ProjectType", HorizontalAlign.Left, 0,          "", "", false, true,
       VerticalAlign.Middle, HorizontalAlign.Left);

       SortedDictionary<string, string=""> sd = new SortedDictionary<string, string="">() { };
       sd.Add("@projectId", txtProjectID.Text.Trim());

       DataSet ds = new DataSet();
       ds = new ShanuProjectScheduleBizClass().SelectList(sd);

       for (int i = 3; i < ds.Tables[0].Columns.Count; i++)
       {
           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);
       }

       //grid events
       ////////Grid1.RowCommand+=new GridViewCommandEventHandler(Grid1_RowCommand);
       //////// Grid1.RowCreated+=new GridViewRowEventHandler(Grid1_RowCreated);
   }</string,></string,>

Image Bind: Using Item template class, I bind the image to place holder and display the images depending on the status.

void plcHolder_DataBinding(object sender, EventArgs e)
   {
       PlaceHolder txtdata = (PlaceHolder)sender;
       GridViewRow container = (GridViewRow)txtdata.NamingContainer;
       object dataValue = DataBinder.Eval(container.DataItem, DataFieldName);
       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

       Image img = new Image();
       if (Convert.ToInt32(dataValue) == 1)
       {
           img.ImageUrl = GetImage(Convert.ToInt32(dataValue1.ToString()));
       }
       else if (Convert.ToInt32(dataValue) == 2)
       {
           img.ImageUrl = GetImage_ScdEnd(Convert.ToInt32(dataValue1.ToString()));
       }
       else
       {
           img.ImageUrl = "~/Images/blanks.jpg";
       }

       img.Style["float"] = "center";
       txtdata.Controls.Add(img);
   }

   private string GetImage(int value)
   {
       if (value == 1)
       {
           return "~/Images/red_new1.jpg";
       }
       else if (value == 2)
       {
           return "~/Images/blue_new1.jpg";
       }
       else
       {
           return "~/Images/green_new1.jpg";
       }
   }

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

public void SelectList()
   {
       SortedDictionary<string, string=""> sd = new SortedDictionary<string, string="">() { };
       sd.Add("@projectId", txtProjectID.Text.Trim());

       DataSet ds = new DataSet();
       ds = new ShanuProjectScheduleBizClass().SelectList(sd);

       if (ds.Tables.Count > 0)
       {
           if (ds.Tables[0].Rows.Count > 0)
           {
               ShanuGDVHelper.DataBinds(Grid1, ds, false);
           }
       }
   }

// Button Click event
 protected void btnSearch_Click(object sender, ImageClickEventArgs e)
   {
       SelectList();
   }</string,>

You can see that here I have 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.

//to return the dataset
   public DataSet SelectList(SortedDictionary<string, string=""> sd)
   {
       try
       {
           return SqlHelper.ExecuteDataset(ConnectionString,
           CommandType.StoredProcedure, "usp_ProjectSchedule", GetSdParameter(sd));
       }
       catch (Exception ex)
       {
           throw ex;
       }
   }

Conclusion

The main aim of this article is to create a simple Web Based project Scheduling monitoring program. I hope you like my article and if you have any questions, kindly leave me a comment.

History

• 24^th November, 2014: Initial release