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

Last Reply on Sep 14, 2017 09:11 AM By david.ee

Posted on Sep 12, 2017 11:21 PM

Hello All,

I am having difficulties creating a gridview with one column which lists days of one calendar year.

Please Azim or Mudassar kindly help with this either javascript or codebehind.

Thank you

Posted on Sep 14, 2017 05:11 AM

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