如何在ASP.NET中实现日历布局逻辑，并使用MS SQL数据库实现C＃

发布于 2025-01-21 04:01:44 字数 308 浏览 0 评论 0原文

我在学校有这个项目，并且我创建了一个带有主页，会员注册，会员登录，会员登录，会员资料和管理员登录页面的项目，我想在我的主页上实现此布局用户已登录并能够在员工在家工作时可以选择日期，应该选择一个日期，然后插入数据库，供雇主查看谁在此特定日期或一天中在家中工作。

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

烧了回忆取暖 2025-01-28 04:01:44

continue

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:

        <div style="float:left">
            <asp:ListBox ID="lstEmployee" runat="server" 
                DataValueField="ID"
                DataTextField="EmpName" Height="270px" Width="125px" 
                AutoPostBack="True" 
                OnSelectedIndexChanged="lstEmployee_SelectedIndexChanged" >
            </asp:ListBox>
        </div>

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:

        <div style="float:left;margin-left:25px">
        <asp:ListView ID="ListView1" runat="server" OnItemDataBound="ListView1_ItemDataBound1"  >
            <ItemTemplate>
              <tr id="OneRow" runat="server"> 
                 <td>
                    <asp:Linkbutton ID="L1" runat="server" Text='<%# Eval("Sun", "{0:dd}") %>'
                    bDate = '<%# Eval("Sun") %>' OnClick="L1_Click"  />
                </td>
                <td>
                    <asp:Linkbutton ID="L2" runat="server" Text='<%# Eval("Mon", "{0:dd}") %>' 
                     bDate = '<%# Eval("Mon") %>' OnClick="L1_Click" />
                </td>
                <td>
                    <asp:Linkbutton ID="L3" runat="server" Text='<%# Eval("Tue", "{0:dd}") %>' 
                    bDate = '<%# Eval("Tue") %>' OnClick="L1_Click" />
                </td>
                <td>
                    <asp:Linkbutton ID="L4" runat="server" Text='<%# Eval("Wed", "{0:dd}") %>'
                    bDate = '<%# Eval("Wed") %>' OnClick="L1_Click" />
                </td>
                <td>
                    <asp:Linkbutton ID="L5" runat="server" Text='<%# Eval("Thu", "{0:dd}") %>'
                    bDate = '<%# Eval("Thu") %>' OnClick="L1_Click" />
                </td>
                <td>
                    <asp:Linkbutton ID="L6" runat="server" Text='<%# Eval("Fri", "{0:dd}") %>'
                    bDate = '<%# Eval("Fri") %>' OnClick="L1_Click" />
                </td>
                <td>
                    <asp:Linkbutton ID="L7" runat="server" Text='<%# Eval("Sat", "{0:dd}") %>'
                    bDate = '<%# Eval("Sat") %>' OnClick="L1_Click" />
                </td>
              </tr>
            </ItemTemplate>
            <LayoutTemplate>
                <table id="itemPlaceholderContainer" runat="server" border="0" style="">
                <tr runat="server" style="">
                    <th runat="server" style="text-align:center" >Sun</th>
                    <th runat="server" style="text-align:center">Mon</th>
                    <th runat="server" style="text-align:center">Tue</th>
                    <th runat="server" style="text-align:center">Wed</th>
                    <th runat="server" style="text-align:center">Thr</th>
                    <th runat="server" style="text-align:center">Fri</th>
                    <th runat="server" style="text-align:center">Sat</th>
                </tr>
                <tr id="itemPlaceholder" runat="server"></tr>
                </table>
            </LayoutTemplate>
        </asp:ListView>

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.

回复收藏 0 原文

~没有更多了~