如何用ASP.NET和C#从SQL Server取数并实现指定任务调度器展示

Got it, let's build this career day scheduler using ASP.NET (Web Forms) and C# step by step. I'll cover everything from database setup to frontend display with the required coloring effect, just like you need.

1. Database Table Setup #

First, let's confirm the table structure (use this SQL to create it if you haven't already):

CREATE TABLE InstituteCareerDays (
    InstituteId INT PRIMARY KEY IDENTITY(1,1),
    InstituteName NVARCHAR(100) NOT NULL,
    Address NVARCHAR(255) NOT NULL,
    CareerDay2017 DATE,
    CareerDay2018 DATE,
    CareerDay2019 DATE
);

Populate it with sample career day dates to test the functionality later.

2. Frontend ASPX Page #

Create a new Web Forms page with the dropdown, trigger button, and a GridView to display results. Here's the markup:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="CareerDayScheduler.aspx.cs" Inherits="YourProjectNamespace.CareerDayScheduler" %>

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Career Day Scheduler</title>
    <style type="text/css">
        /* Highlight style for specific career days */
        .highlighted-career-day {
            background-color: #ffd700;
            font-weight: 600;
        }
        .control-bar {
            margin: 15px 0;
        }
        .grid-view {
            border-collapse: collapse;
            width: 100%;
        }
        .grid-view th, .grid-view td {
            border: 1px solid #ddd;
            padding: 8px;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
        <div class="control-bar">
            <asp:DropDownList ID="ddlYear" runat="server" Width="100px">
                <asp:ListItem Value="2017">2017</asp:ListItem>
                <asp:ListItem Value="2018">2018</asp:ListItem>
                <asp:ListItem Value="2019">2019</asp:ListItem>
            </asp:DropDownList>
            <asp:Button ID="btnFetchData" runat="server" Text="Load Career Days" OnClick="btnFetchData_Click" CssClass="btn" />
        </div>

        <asp:GridView ID="gvCareerDays" runat="server" AutoGenerateColumns="false" 
            CssClass="grid-view" OnRowDataBound="gvCareerDays_RowDataBound">
            <Columns>
                <asp:BoundField DataField="InstituteId" HeaderText="Institute ID" />
                <asp:BoundField DataField="InstituteName" HeaderText="Institute Name" />
                <asp:BoundField DataField="Address" HeaderText="Address" />
                <asp:BoundField DataField="CareerDayDate" HeaderText="Career Day" DataFormatString="{0:MMMM dd, yyyy}" />
            </Columns>
        </asp:GridView>
    </form>
</body>
</html>

3. C# Code-Behind Logic #

Now write the backend code to handle data retrieval and apply the highlight effect:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.UI.WebControls;

namespace YourProjectNamespace
{
    public partial class CareerDayScheduler : System.Web.UI.Page
    {
        // Replace with your actual SQL Server connection string
        private readonly string _connectionString = @"Data Source=YOUR_SERVER_NAME;Initial Catalog=YOUR_DATABASE_NAME;Integrated Security=True";

        protected void btnFetchData_Click(object sender, EventArgs e)
        {
            string selectedYear = ddlYear.SelectedValue;
            string targetColumn = $"CareerDay{selectedYear}";

            // Query to fetch only institutes with a valid career day for the selected year
            string query = $"SELECT InstituteId, InstituteName, Address, {targetColumn} AS CareerDayDate FROM InstituteCareerDays WHERE {targetColumn} IS NOT NULL";

            using (SqlConnection conn = new SqlConnection(_connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(query, conn))
                {
                    conn.Open();
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    DataTable dt = new DataTable();
                    adapter.Fill(dt);
                    gvCareerDays.DataSource = dt;
                    gvCareerDays.DataBind();
                }
            }
        }

        protected void gvCareerDays_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                DateTime careerDay = Convert.ToDateTime(e.Row.Cells[3].Text);
                
                // Adjust this condition to match your "specific career day" requirement
                // Example: Highlight upcoming career days (next 14 days)
                if (careerDay >= DateTime.Now && careerDay <= DateTime.Now.AddDays(14))
                {
                    e.Row.Cells[3].CssClass = "highlighted-career-day";
                    // Uncomment below to highlight the entire row instead of just the date cell
                    // e.Row.CssClass = "highlighted-career-day";
                }
            }
        }
    }
}

4. Quick Tips for Production #

• Connection String: Store your connection string in Web.config instead of hardcoding it for better security and maintainability.
• Error Handling: Add try-catch blocks around database operations to handle exceptions (like connection failures) and show user-friendly messages.
• Highlight Logic: Modify the condition in gvCareerDays_RowDataBound to match exactly what you consider a "specific career day" (e.g., past days, specific date ranges, etc.).

内容的提问来源于stack exchange，提问作者Nawoda Dharmabandu