This article explains how to create a simple Gantt Style chart inside DataGridView in order to display the Project Schedule results.
Introduction
In this article, we will see how to create a simple Gantt Style chart inside DataGridView to display the Project Schedule results. In my previous article, I explained the same for ASP.NET GridView and also for MVC using AngularJS.
Few members requested me to do the same for Winform using DataGridView. I have made a simple program with the same functionality as my Webform example. Kindly refer to both articles to find more details about project Scheduling.
All business logic to display result are in my SQL Server Stored Procedure.
As I have explained in both articles, all the business logic to display the project management result has been done in my Stored Procedure. I have used Pivot query in my Stored Procedure to display the final result. We can see the details about how I have written my procedure to display the result in code part.
Using the Code
We will create a SCHED_Master table under the database 'projectDB'. The following is the script to create a database, table and sample insert query. Run this script in your SQL Server. I have used SQL Server 2012.
USE MASTER
GO
IF EXISTS (SELECT [name] FROM sys.databases WHERE [name] = 'projectDB' )
DROP DATABASE projectDB
GO
CREATE DATABASE projectDB
GO
USE projectDB
GO
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 INTO [dbo].SCHED_Master
([ID],[ProjectName],[ProjectType],[ProjectTypeName],_
[SCHED_ST_DT],[SCHED_ED_DT],[ACT_ST_DT],[ACT_ED_DT],[status])
VALUES
(1001,'Project-1',1,'Urgent','2015-06-01 00:00:00.000','2015-09-02 00:00:00.000'
,'2015-06-22 00:00:00.000','2015-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,'Project-1',2,'Important','2015-06-12 00:00:00.000','2015-8-02 00:00:00.000'
,'2015-06-19 00:00:00.000','2015-8-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,'Project-1',3,'Normal','2015-06-28 00:00:00.000','2015-09-03 00:00:00.000'
,'2015-07-02 00:00:00.000','2015-08-24 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,'Project-2',1,'Urgent','2015-06-28 00:00:00.000','2015-08-02 00:00:00.000'
,'2015-07-02 00:00:00.000','2015-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,'Project-2',2,'Important','2015-07-09 00:00:00.000','2015-12-22 00:00:00.000'
,'2015-06-28 00:00:00.000','2015-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,'Project-2',3,'Normal','2015-06-01 00:00:00.000','2015-08-04 00:00:00.000'
,'2015-06-22 00:00:00.000','2015-08-24 00:00:00.000',1)
select ID,ProjectName,ProjectType,ProjectTypeName,SCHED_ST_DT,_
SCHED_ED_DT,ACT_ST_DT,ACT_ED_DT,status from SCHED_Master
After creating our table, we will create a stored procedure to display the project schedule result using Pivot query.
I will explain each step of my procedure so that you can understand it clearly to make your own with your table formats.
Step 1
Create the procedure with parameter and declare the variable inside procedure to be used in the SP.
Note here I have set the FromDate and ToDate static. You can change this as a parameter from SP to get dynamic results as per your date range.
CREATE PROCEDURE [dbo].[usp_ProjectSchedule_Select]
@projectId VARCHAR(10) = ''
AS
BEGIN
Declare @FromDate VARCHAR(20) = '2015-06-08'
Declare @ToDate VARCHAR(20) = '2016-05-06'
DECLARE @MyColumns AS NVARCHAR(MAX),
@SQLquery AS NVARCHAR(MAX)
Step 2
We have defined our Project FromDate and EndDate. Now we need to search the project schedule result from the given date. The main aim of the Project Schedule chart is to display the data range as Weeks, Month, Year or Day of any one format with continuous result within the range. To get the continuous result, I will get the Days if Sundays from the Start and End date. I will display the result as Week display, so here I have used every week Sunday date and stored all the dates to temptable for displaying the result.
This Temp table is created for getting all the days between the start date and end date to display as the Column Header.
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
Step 3
I will join the above temp table to actual Schedule table to compare the dates and produce the result. Firstly, I will check for the Schedule result and using the union, I will combine the result to the Actual result and insert the final result to another temp table to generate our pivot result.
Note: For actual data in pivot list, I will display the result as:
“-1” – For End Date of both Schedule and Actual result. In my program, I will check for the resultant value, if its “-1”, then I will display the text as “END” with red back color which is to notify the user for End date of each project.“0” – If the result value is “0”, then it means the days are not in any Schedule or Actual days, so it should be left as blank.“1” – If the result is “1”, it is to intimate as the Schedule Start and End days. I will be using Blue color to display the Schedule Days.“2” - If the result is “1”, it is to intimate as the Actual Start and End days. I will be using Green color to display the Schedule Days.
This is only a sample procedure that explains a sample program for Project schedule, you can customize this table, procedure and program as per your requirement and set your own rule and status to display the result.
This temp table is created to schedule details with the result, here I have used the Union.
The first query returns the Schedule Project result and the second query returns the Actual Project result. Both these queries will be inserted to a Temp Table.
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
,'1-Scd' viewtype
, A. ProjectType ProjectType
, Case when cast(DATEPART( wk, max(A.SCHED_ED_DT)) _
as varchar(2)) = _
cast(DATEPART( wk, WkStartSundays) as varchar(2)) _
then -1 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
, 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'
FROM
SCHED_Master A (NOLOCK)
LEFT OUTER JOIN
#TEMP_EveryWk_Sndays F (NOLOCK) ON A.status= F.status
WHERE
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
SELECT
A.ProjectName ProjectName
,'2-Act' viewtype
, A. ProjectType ProjectType
, Case when cast(DATEPART( wk, max(A.ACT_ED_DT)) as varchar(2)) = _
cast(DATEPART( wk, WkStartSundays) _
as varchar(2)) then -1 else
case when min(A.ACT_ST_DT)<= F.WkStartSundays _
AND max(A.ACT_ED_DT) >= F.WkStartSundays
then 2 else 0 end end resultnew
, 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'
FROM
SCHED_Master A (NOLOCK)
LEFT OUTER JOIN
#TEMP_EveryWk_Sndays F (NOLOCK) ON A.status= F.status
WHERE
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
Step 4
Here, I will display the final result using the pivot query from the final result of temp table result.
select @MyColumns = STUFF((SELECT ',' + QUOTENAME(YMWK)
FROM #TEMP_results
GROUP BY YMWK
ORDER BY YMWK
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @SQLquery = N'SELECT ProjectName as Project,viewtype as ViewType,_
ProjectType as PrpjectType,' + @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;
Here is the complete code for the Stored Procedure.
CREATE PROCEDURE [dbo].[usp_ProjectSchedule_Select]
@projectId VARCHAR(10) = ''
AS
BEGIN
Declare @FromDate VARCHAR(20) = '2015-06-08'
Declare @ToDate VARCHAR(20) = '2016-05-06'
DECLARE @MyColumns AS NVARCHAR(MAX),
@SQLquery AS NVARCHAR(MAX)
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
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
,'1-Scd' viewtype
, A. ProjectType ProjectType
, Case when cast(DATEPART( wk, max(A.SCHED_ED_DT)) _
as varchar(2)) = _
cast(DATEPART( wk, WkStartSundays) as varchar(2)) then -1 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
, 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'
FROM
SCHED_Master A (NOLOCK)
LEFT OUTER JOIN
#TEMP_EveryWk_Sndays F (NOLOCK) ON A.status= F.status
WHERE
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
SELECT
A.ProjectName ProjectName
,'2-Act' viewtype
, A. ProjectType ProjectType
, Case when cast(DATEPART( wk, max(A.ACT_ED_DT)) as _
varchar(2)) = cast(DATEPART( wk, WkStartSundays) _
as varchar(2)) then -1 else
case when min(A.ACT_ST_DT)<= F.WkStartSundays AND _
max(A.ACT_ED_DT) >= F.WkStartSundays
then 2 else 0 end end resultnew
, 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'
FROM
SCHED_Master A (NOLOCK)
LEFT OUTER JOIN
#TEMP_EveryWk_Sndays F (NOLOCK) ON A.status= F.status
WHERE
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
select @MyColumns = STUFF((SELECT ',' + QUOTENAME(YMWK)
FROM #TEMP_results
GROUP BY YMWK
ORDER BY YMWK
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @SQLquery = N'SELECT ProjectName as Project,viewtype as ViewType,_
ProjectType as PrpjectType,' + @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
If we run the procedure, the final output will be like the following. Here, we can see I will display the result of every week using the pivot query.
Create our Windows Form Application in Visual Studio 2015
Prerequisites
Visual Studio 2015. You can download it from here.
After installing Visual Studio 2015, click Start, Programs and select Visual Studio 2015.
Click New, Project, then select Visual C# and go to Windows, then Windows Forms Application. Select your project location and enter your application name.
Design your form. In my form, I have added a Textbox for searching the details by Project Name and a button to bind the result.
Note: I have used my DataGridView helper class to create the DataGridView at runtime instead of design time. Kindly refer to my article related to create a DatagridView helper class.
Form Load
In Form Load, initialize the DataGridView and add the DataGridView to Panel Control using the Helper Class. After DateGridview Initialized, bind the data to Grid.
private void shanuDatagridViewPaint_Load(object sender, EventArgs e)
{
MasterGrid_Initialize();
bindData();
}
Initialize Grid
Using my helper class, I will create the DataGridView at runtime. Pass all the parameters such as Grid Back Color, Height, Width and all properties to create at runtime like the following:
public void MasterGrid_Initialize()
{
Helper.ShanuDGVHelper.Layouts(Master_shanuDGV, Color.White,
Color.WhiteSmoke, Color.WhiteSmoke, false, Color.WhiteSmoke,
true, Color.FromArgb(112, 128, 144), false, false, false,
Color.White, 40, 20, "small");
Helper.ShanuDGVHelper.Generategrid(Master_shanuDGV, pnlGrid, 1000, 600, 10, 10);
Master_shanuDGV.CellFormatting += new DataGridViewCellFormattingEventHandler
(MasterDGVs_CellFormatting);
}
As we can see after Initializing the DataGridView, I have used the CellFormatting DatagridView Event.
CellFormatting DataGridView Event
In Cellformatting DataGridView, I will check for each cell result and set the back color of each cell to display our Gantt style chart inside DataGridView. I will check for the project type and give each project actual and schedule result with unique color to see the result in more graphical output.
void MasterDGVs_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
{
try
{
ProjectType = ds.Tables[0].Rows[e.RowIndex]
["PrpjectType"].ToString();
if (e.ColumnIndex > 2)
{
Color color1= Color.FromArgb(116, 176, 30);
Color color2 = Color.FromArgb(0, 76, 153);
if (e.Value.ToString() == "0")
{
e.Value = "";
}
if(ProjectType=="1")
{
color1 = Color.FromArgb(116, 176, 30);
color2 = Color.FromArgb(0, 76, 153);
}
else if (ProjectType == "2")
{
color1 = Color.FromArgb(218, 165, 32);
color2 = Color.FromArgb(255, 215, 0);
}
else if (ProjectType == "3")
{
color1 = Color.FromArgb(147, 112, 219);
color2 = Color.FromArgb(255, 105, 180);
}
switch (e.Value.ToString())
{
case "-1":
e.CellStyle.BackColor = Color.FromArgb(255, 69, 0);
e.CellStyle.SelectionBackColor =
Color.FromArgb(255, 69, 0);
e.CellStyle.ForeColor = Color.White;
e.CellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
e.Value = "END";
break;
case "2":
e.CellStyle.BackColor = color1;
e.CellStyle.SelectionBackColor = color1;
e.Value = "";
break;
case "1":
e.CellStyle.BackColor = color2;
e.CellStyle.SelectionBackColor = color2;
e.Value = "";
break;
}
}
}
catch (Exception ex)
{ }
}
Search Button Click
In button click and on Form Load, I will call the bindData() to bind the data to the DataGridView.
private void btnSearch_Click(object sender, EventArgs e)
{
bindData();
}
bindData() Method
In this method, I will pass the stored procedure name and parameters to the Business Logic class. From Business logic Class, I will pass the parameter and SP name to DAL Class where it will connect to the database, get the result and return as DataSet. The final DataSet result from BL will get in Form and bind the result in DatagridView.
private void bindData()
{
try
{
SortedDictionary<string, string> sd =
new SortedDictionary<string, string>() { };
sd.Add("@projectId", txtProjectID.Text.Trim());
ds = new ShanuProjectScheduleBizClass().SelectList(sd);
Master_shanuDGV.DataSource = null;
if (ds.Tables[0].Rows.Count > 0)
{
Master_shanuDGV.DataSource = ds.Tables[0];
}
}
catch (Exception ex)
{
}
}
Points of Interest
Note: You can also extend this program to display the output in a more graphical way by using the DatagridviewCell painting event. You can draw your own custom format chart types inside the DatagridviewCell Painting event.
Note: Connection String
You can find "DBConnection.txt" inside the bin folder, change the connection string to your SQL Server DB Setting.
Change Connection String in code:
You can find a "BizBase.cs" inside Helper/Biz folder. Change the connection string to your local SQL Server Connection String.
History
- 9th September, 2015: Initial version
