How to implement the calendar layout logic in ASP.NET and C# with MS SQL Database

Question

I have this project at school and I have created a project with homePage, member signUp, member Login, member profile, and Admin Login pages I want to implement this layout on my homepage when the user is logged in and be able to select dates when an employee is working from home should choose a date and insert into a database for the employer to see who is working from home on this specific date or day.

Albert D. Kallal · Accepted Answer

Well, if we break this down?

We need a listbox on the left side to display the Employee's

So, drop in a listbox, say like this:

And our code to load is thus this:

    DateTime dtStart, dtEnd;    // start and end of this month
    DateTime CalStart, CalEnd;  // start and end of the whole cal display

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            LoadEmployee();
            LoadCal();
        }
    }

    void LoadEmployee()
    {
        // load up employee listbox
        SqlCommand cmdSQL = new SqlCommand(
          "SELECT ID,(FirstName + ' ' + LastName) as EmpName " +
          "FROM Employee ORDER BY FirstName");

        lstEmployee.DataSource = MyRstP(cmdSQL);
        lstEmployee.DataBind();
    }

And now we have this:

Ok, now we need a calendar. Hum, ok, looking at outlook, we see that we need 6 rows of a "thing".

I think a simple Listview, and for each row (6 of them), we need a data table, with 1-7 days of data.

So, a simple list view, one row - but we spit out 6 of them.

and we want the calendar to be clickable, so each day will be a button - a LinkButton should work fine.

So, a list view could be like this:

Not a lot of markup.

Now, we need to load up the data, so we have this:

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            LoadEmployee();
            LoadCal();
        }
    }

And the code to load up the calendar? A data table of 6 rows, and 7 columns, say like this code:

    void LoadCal()
    {
        SetupDates();

        DataTable OneMonth = new DataTable();
        // add heading colums to table (Sun->Sat)
        for (int i = 0; i <= 6; i++)
        {
            DateTime d = CalStart.AddDays(i);
            string strCol = d.ToString("ddd"); // gets day of week as text (sun->sat)
            OneMonth.Columns.Add(new DataColumn(strCol, typeof(DateTime)));
        }

        DateTime dtPtr = CalStart;   // start a simple date counter - upper left
        // add 6 rows for calendar
        for (int i = 1; i <= 6; i++)
        {
            DataRow OneRow = OneMonth.Rows.Add();
            for (int wDay = 0; wDay <= 6; wDay++) // put date in each colum 1 - 7
            {
                OneRow[wDay] = dtPtr;   // shove date into cal square
                dtPtr = DateAndTime.DateAdd(DateInterval.Day, 1, dtPtr);
            }
            ListView1.DataSource = OneMonth;
            ListView1.DataBind();
        }
    }

And we now have this:

Now, I did want to "gray" out the dates off this month, so in the Listview databound event (a great event for formatting grids or listview), then I put in this code

If the date ptr is outside of this month, we grey out the linkbutton.

so, this:

    protected void ListView1_ItemDataBound1(object sender, ListViewItemEventArgs e)
    {
        if (e.Item.ItemType == ListViewItemType.DataItem)
        {
            ListViewDataItem lvRow = e.Item as ListViewDataItem;
            for (int MyDay = 1; MyDay <= 7;MyDay++)
            {
                LinkButton LDate = lvRow.FindControl("L" + MyDay) as LinkButton;
                DateTime BoxDate =  Convert.ToDateTime(LDate.Attributes["bDate"]);
                if ( (BoxDate < dtStart) | (BoxDate > dtEnd)) 
                    LDate.BackColor = System.Drawing.Color.LightGray;

                // this sets the size for ALL squares 
                LDate.Attributes.Add("style", "float:right;height:70px;width:60px;text-align:right");
            }
        }
    }

Ok, so now we have to add some code to highlight a square if this is a work at home date.

We have this data table:

So, after we load the Grid, then we click on a list view item.

That code is this:

    protected void lstEmployee_SelectedIndexChanged(object sender, EventArgs e)
    {
        int EmployeeID = 0;
        EmployeeID = Convert.ToInt32(lstEmployee.SelectedValue);
        ShowOffDays(EmployeeID);
    }

So, we need a routine to now display (highlight) any record in the table based on employee id and the given date.

So this:

    void ShowOffDays(int EmpID)
    {
        SetupDates();

        var cmdSQL = new SqlCommand(
            "SELECT * FROM AtHome WHERE EmployeeID = @EmpID " +
            "AND AtHomeDate BETWEEN @dtSTart AND @dtEnd");

        cmdSQL.Parameters.Add("@EmpID", SqlDbType.Int).Value = EmpID;
        cmdSQL.Parameters.Add("@dtStart", SqlDbType.Date).Value = dtStart;
        cmdSQL.Parameters.Add("@dtEnd", SqlDbType.Date).Value = dtEnd;

        DataTable rstAtHome = new DataTable();
        rstAtHome = MyRstP(cmdSQL);

        DateTime dtPtr = CalStart;
        for(int i = 0;i<=5;i++)
        {
            ListViewItem lvRow = ListView1.Items[i];
            for (int wDay = 1;wDay <= 7;wDay++)
            {
                if ((dtPtr >= dtStart) & (dtPtr <= dtEnd))   // only highlit this month
                {
                    LinkButton LDate = lvRow.FindControl("L" + wDay) as LinkButton;
                    DataRow[] tView = rstAtHome.Select("AtHomeDate = #" + dtPtr + "#");
                    if (tView.Length == 0)
                        // no at home date
                        LDate.BackColor = System.Drawing.Color.White;
                    else
                        // found a date - blue the square0
                        LDate.BackColor = System.Drawing.Color.LightBlue;
                }
                dtPtr = DateAndTime.DateAdd(DateInterval.Day, 1, dtPtr);
            }
        }
    }

Now, that was a bit of code - but not too bad.

So, now we get this:

Ok, so now we need a click event for the square. If you click a empty square, we add a single row to the table, and if already highliged, we delete it.

So, that click event can be this:

    protected void L1_Click(object sender, EventArgs e)
    {
        LinkButton btn = sender as LinkButton;
        DateTime dtDateV = Convert.ToDateTime(btn.Attributes["bDate"]);
        int EmpID = Convert.ToInt32(lstEmployee.SelectedItem.Value);

        DataTable rstDayOff = new DataTable();
        using (var conn = new SqlConnection(Properties.Settings.Default.TEST4))
        {
            using (var cmdSQL = new SqlCommand(
                "SELECT * FROM AtHome WHERE EmployeeID = @EmpID " +
                "AND AtHomeDate = @Date", conn))
            {
                cmdSQL.Parameters.Add("@EmpID", SqlDbType.Int).Value = EmpID;
                cmdSQL.Parameters.Add("@Date", SqlDbType.Date).Value = dtDateV;
                conn.Open();
                rstDayOff.Load(cmdSQL.ExecuteReader());
                if (rstDayOff.Rows.Count == 0 )
                {
                    // add this day off to table
                    DataRow OneDay = rstDayOff.Rows.Add();
                    OneDay["EmployeeID"] = EmpID;
                    OneDay["AtHomeDate"] = dtDateV;
                    btn.BackColor = System.Drawing.Color.LightBlue;
                }
                else
                {
                    // delete the at home day
                    rstDayOff.Rows[0].Delete();
                    btn.BackColor = System.Drawing.Color.White;
                }
                SqlDataAdapter da = new SqlDataAdapter(cmdSQL);
                SqlCommandBuilder daU = new SqlCommandBuilder(da);
                da.Update(rstDayOff);
            }
        }
    }

Not bad! So, now you can click to highlight, or un-highlight a day. (it toggles the given square).

So, above should give you some ideas.

And the two helper routines I used was to get a data table. And the other to setup the dates.

These:

    void SetupDates()
    {
        DateTime dt = DateTime.Today;

        dtStart = DateAndTime.DateSerial(DateAndTime.Year(dt), DateAndTime.Month(dt), 1);
        int DaysInMonth = DateTime.DaysInMonth(DateAndTime.Year(dt),DateAndTime.Month(dt));

        // end of month = DaysInmonth less one
        dtEnd = DateAndTime.DateAdd(DateInterval.Day, DaysInMonth - 1, dtStart);

        // now with date end/start - get display start/end (subtract day of week)

        CalStart = DateAndTime.DateAdd(DateInterval.Day, 1 - (DateAndTime.Weekday(dtStart)), dtStart);
        CalEnd = DateAndTime.DateAdd(DateInterval.Day, 41, CalStart);

    }

and

    DataTable MyRstP(SqlCommand cmdSQL)
    {
        DataTable rstData = new DataTable();
        using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.TEST4))
        {
            using (cmdSQL)
            {
            cmdSQL.Connection = conn;
            conn.Open();
            rstData.Load(cmdSQL.ExecuteReader());
            }
        }
        return rstData;
    }

So, it was a bit of code, but breaking down each part - into smaller bits and sizes? It was not really that hard.

Edit: the connection string

Well, the connection string is generally placed in the web.config file. However you can get visual studio to do that for you and it tends to be a whole lot easier.

So, from Visual studio, go project->"my project settings".

Say like this:

So, for things like maybe the welcome message, company name, perhaps to use some tax rate, or a bunch of typical settings that just about any and every applcation has?

Well, you can use the settings tab from above. so, say like this:

So, in above, I have My Cool Company name, and in code I can thus reference that setting. And note how I have multiple conneciton strings.

Using the above is nice, since if you choose to add a connection string, it not only means ONE PLACE in your whole application, but you can use the connection string builder to help you out.

Say, like this:

when you click on the [...], then you get this:

so, you get the connection wizard, and that makes setting up a connection a whole lot less hassile.

So, now in code, to get that string, you can use this:

using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.TEST4))

Now, at the end of the day, if you go look at web.config, you see the settings in web.config, but that can be extra work, so using the above project settings is a just a great handy place to use and place all of the many settings you will have in any typical applcation.

How to implement the calendar layout logic in ASP.NET and C# with MS SQL Database

Answers (1)

Edit: the connection string

Related Questions