Create date dimension or calendar table using C# and VB.Net in ASP.Net

Hi david.ee,

I have created a sample which full fill your requirement you need to modify the according to your need.

SQL

CREATE PROCEDURE GetCalendar
	@StartDate DATE
   ,@EndDate DATE
AS
	BEGIN   
		CREATE TABLE #dim
		(
 		 [year]       AS DATEPART(YEAR,[date]),
 		 [date]       DATE PRIMARY KEY, 
 		 [day]        AS DATEPART(DAY,[date]),
 		 [month]      AS DATEPART(MONTH,[date]),
 		 FirstOfMonth AS CONVERT(DATE,DATEADD(MONTH,DATEDIFF(MONTH,0,[date]),0)),
 		 [MonthName]  AS DATENAME(MONTH,[date]),
 		 [week]       AS DATEPART(WEEK,[date]),
 		 [ISOweek]    AS DATEPART(ISO_WEEK,[date]),
 		 [DayOfWeek]  AS DATEPART(WEEKDAY,[date]),
 		 [quarter]    AS DATEPART(QUARTER,[date]), 
 		 FirstOfYear  AS CONVERT(DATE, DATEADD(YEAR,  DATEDIFF(YEAR,0,[date]), 0)),
 		 Style112     AS CONVERT(CHAR(8),[date], 112),
 		 Style101     AS CONVERT(CHAR(10),  
 		 [date], 101)
		 );

		INSERT #dim ([date])
		SELECT d = DATEADD(DAY, RowNumber - 1, @StartDate)
		FROM (
			  SELECT TOP (DATEDIFF(DAY, @StartDate, DATEADD(DAY,1,@EndDate))) 
			  RowNumber = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
			  FROM sys.all_objects AS s1
		      CROSS JOIN sys.all_objects AS s2    
			  ORDER BY s1.[object_id]
			 ) AS x;

		SELECT [year],[day],[week] FROM #dim

		DROP TABLE #dim
END

HTML

<div>
    <table>
        <tr>
            <td>
                StartDate:
            </td>
            <td>
                <asp:TextBox ID="txtStartDate" class="datepicker" runat="server" />
            </td>
        </tr>
        <tr>
            <td>
                EndDate:
            </td>
            <td>
                <asp:TextBox ID="txtEndDate" class="datepicker" runat="server" />
            </td>
        </tr>
        <tr>
            <td>
                <asp:Button Text="Generate Calendar" class="btn btn-primary" OnClick="GenerateCalendar"
                    runat="server" />
            </td>
            <td>
            </td>
        </tr>
    </table>
    <br />
    <asp:GridView ID="gvCalendarData" runat="server" />
    <br />
</div>
<div>
    <script type="text/javascript" src="https://code.jquery.com/jquery-1.12.4.js"></script>
    <script type="text/javascript" src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
    <link rel="stylesheet" href="//code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css" />
    <script type="text/javascript" src="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
    <link rel="stylesheet" href="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" />
    <script type="text/javascript">
        $(function () {
            $('.datepicker').datepicker();
        });
    </script>
</div>

C#

protected void GenerateCalendar(object sender, EventArgs e)
{
    DataTable dt = BindGrid(txtStartDate.Text.Trim(), txtEndDate.Text.Trim());
    gvCalendarData.DataSource = dt;
    gvCalendarData.DataBind();
}

private DataTable BindGrid(string startDate, string endDate)
{
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    SqlConnection con = new SqlConnection(constr);
    SqlCommand cmd = new SqlCommand("GetCalendar");
    cmd.Parameters.AddWithValue("@StartDate", Convert.ToDateTime(txtStartDate.Text.Trim()));
    cmd.Parameters.AddWithValue("@EndDate", Convert.ToDateTime(txtEndDate.Text.Trim()));
    SqlDataAdapter sda = new SqlDataAdapter();
    cmd.Connection = con;
    cmd.CommandType = CommandType.StoredProcedure;
    sda.SelectCommand = cmd;
    DataTable ds = new DataTable();
    sda.Fill(ds);
    return ds;
}

VB.Net

Protected Sub GenerateCalendar(sender As Object, e As EventArgs)
    Dim dt As DataTable = BindGrid(txtStartDate.Text.Trim(), txtEndDate.Text.Trim())
    gvCalendarData.DataSource = dt
    gvCalendarData.DataBind()
End Sub

Private Function BindGrid(startDate As String, endDate As String) As DataTable
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Dim con As New SqlConnection(constr)
    Dim cmd As New SqlCommand("GetCalendar")
    cmd.Parameters.AddWithValue("@StartDate", Convert.ToDateTime(txtStartDate.Text.Trim()))
    cmd.Parameters.AddWithValue("@EndDate", Convert.ToDateTime(txtEndDate.Text.Trim()))
    Dim sda As New SqlDataAdapter()
    cmd.Connection = con
    cmd.CommandType = CommandType.StoredProcedure
    sda.SelectCommand = cmd
    Dim ds As New DataTable()
    sda.Fill(ds)
    Return ds
End Function

ScreenShot