Click here to Skip to main content
14,636,351 members
Articles » Web Development » ASP.NET » General
Article
Posted 27 Jan 2015

Stats

45K views
1.7K downloads
54 bookmarked

Project Scheduling Using ASP.Net GridView

Rate this:
4.82 (27 votes)
Please Sign up or sign in to vote.
4.82 (27 votes)
27 Jan 2015CPOL
Project Scheduling using GridView ASP.NET.

Image 1

Introduction

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

Project Scheduling is very important part in project planning. The project might be any type for example Software project development planning, Production Planning and etc. In Project there might be 2 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 is 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" Comlum for each Project and project type i will display first row for the Schedule Date and next row for the Actual date.user can compare with both the results.

Image 2

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 Kind of Scheduling Program.

  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).

Image 3

We can see here 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 have 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 in detail of each part.

1. First we start with 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 better understanding.<o:p>

-- =============================================                                                                      
-- 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 and 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 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.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 disoplay 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 grid view dynamically using my helper class. In my helper class I have the fallowing 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 have bind the image to place holder and display the images depend 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 data base 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.user can search the project by projectName for exampple in button click event i have called the SelectList Method to bidn 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,></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;
        }
    }
</string,>

Conclusion

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

History

  • Initial release on 2014/11/24.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

syed shanu
Team Leader
India India
Microsoft MVP | Code Project MVP | CSharp Corner MVP | Author | Blogger and always happy to Share what he knows to others. MyBlog

My Interview on Microsoft TechNet Wiki Ninja Link

Comments and Discussions

 
QuestionGood and practical example Pin
Imran Abdul Ghani26-Feb-17 6:04
MemberImran Abdul Ghani26-Feb-17 6:04 
GeneralVery interesting Pin
Oscar R. Onorato28-Nov-14 4:26
MemberOscar R. Onorato28-Nov-14 4:26 
GeneralRe: Very interesting Pin
syed shanu28-Nov-14 4:53
mvasyed shanu28-Nov-14 4:53 
QuestionNICE ! Pin
Member 1111981625-Nov-14 15:22
MemberMember 1111981625-Nov-14 15:22 
AnswerRe: NICE ! Pin
syed shanu25-Nov-14 18:49
mvasyed shanu25-Nov-14 18:49 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.