Customer Order Monthly Report Using Pivot In SQL Server

I have written something like this earlier but I got a request from one of my follower to write it again by extending functionality like how we can show this report in ASP.NET and change color of Grid View by putting some condition. I will explain this in the following.

I have 2 tables in my SQL Server.

1. Customer
   
   1. CREATE TABLE [dbo].[Customer](
   2. [Customer_ID] [int] IDENTITY(1,1) NOT NULL,
   3. [Customer_Name] [varchar](50) NULL,
   4. CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
   5. (
   6. [Customer_ID] ASC
   7. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
   8. ) ON [PRIMARY]
   10. GO
   11. SET ANSI_PADDING OFF
   12. GO
   
   Figure 1
   
   Data in my Customer Table:
   
   
   Figure 2
   
   Now my Second Table,
   
   
2. Customer_Orders
   
   1. CREATE TABLE [dbo].[Customer_Orders](
   2. [OrderID] [int] IDENTITY(1,1) NOT NULL,
   3. [Customer_ID] [int] NULL,
   4. [Unit_Order] [int] NULL,
   5. [Month] [varchar](50) NULL,
   6. [Year] [int] NULL,
   7. CONSTRAINT [PK_Customer_Orders] PRIMARY KEY CLUSTERED
   8. (
   9. [OrderID] ASC
   10. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
   11. IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
   12. ) ON [PRIMARY]
   13. GO
   14. SET ANSI_PADDING OFF
   15. GO
   
   Figure 3
   
   Data in my Customer_Orders table:
   
   
   Figure 4

Now my Stored Procedure to generate report:

Figure 5

Now execute this Stored Procedure:

EXEC GenerateReport


Figure 6

Now we will show this report in GridView using ASP.NET.

Open Visual Studio -> New Web Site:

Add a GridView on your aspx and do coding in aspx.cs page to call stored procedure:

The following is my aspx page:
Here's my aspx.cs code:
My Connection String in web.config file:
Now run you application:


Figure 7