如何在ASP.NET查询生成器的SELECT语句中使用C#变量并在DropDownList显示结果

Let's walk through how to get this working correctly, step by step:

1. Fix the Windows Username Retrieval Code #

First, your original code has a syntax error—C# requires double backslashes for literal backslashes. Update it to:

protected void Page_Load(object sender, EventArgs e)
{
    string userName = System.Security.Principal.WindowsIdentity.GetCurrent().Name.Split('\\').Last();
    // We'll use this userName variable later to filter our query
}

This correctly pulls the username portion (without the domain prefix) from the Windows identity.

2. Configure the SqlDataSource's Select Query #

You'll need to join the managerTable and employee tables to link the logged-in manager to their department's employees. Here are two ways to set this up:

Option 1: Configure in ASPX Markup #

Add a parameter to your existing SqlDataSource that uses the userName variable we created. Update your markup like this:

<asp:SqlDataSource ID="EmployeeDataSource" runat="server" 
    ConnectionString="<%$ ConnectionStrings:YourDatabaseConnection %>"
    SelectCommand="SELECT e.EmployeeName 
                   FROM employee e
                   INNER JOIN managerTable m ON e.DepartmentId = m.DepartmentId
                   WHERE m.ManagerName = @CurrentManager">
    <SelectParameters>
        <asp:Parameter Name="CurrentManager" Type="String" />
    </SelectParameters>
</asp:SqlDataSource>

Note: Replace YourDatabaseConnection, DepartmentId, EmployeeName, and ManagerName with your actual database connection string and column names.

Option 2: Configure in Code-Behind #

If you prefer setting this up programmatically in Page_Load:

protected void Page_Load(object sender, EventArgs e)
{
    string userName = System.Security.Principal.WindowsIdentity.GetCurrent().Name.Split('\\').Last();

    EmployeeDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["YourDatabaseConnection"].ConnectionString;
    EmployeeDataSource.SelectCommand = @"SELECT e.EmployeeName 
                                         FROM employee e
                                         INNER JOIN managerTable m ON e.DepartmentId = m.DepartmentId
                                         WHERE m.ManagerName = @CurrentManager";
    EmployeeDataSource.SelectParameters.Add("CurrentManager", userName);
}

3. Bind the DropDownList #

Link your DropDownList to the SqlDataSource, either in markup:

<asp:DropDownList ID="DepartmentEmployeesDropDown" runat="server" 
    DataSourceID="EmployeeDataSource" 
    DataTextField="EmployeeName"
    DataValueField="EmployeeName"> <!-- Use EmployeeId here if you have a unique ID column -->
</asp:DropDownList>

Or programmatically in code-behind (add this after configuring the SqlDataSource):

DepartmentEmployeesDropDown.DataSource = EmployeeDataSource;
DepartmentEmployeesDropDown.DataTextField = "EmployeeName";
DepartmentEmployeesDropDown.DataValueField = "EmployeeName";
DepartmentEmployeesDropDown.DataBind();

Key Things to Verify #

• Make sure Windows Authentication is enabled in your web app (check your web.config for <authentication mode="Windows" />).
• Confirm that the ManagerName column in managerTable exactly matches the Windows usernames (case sensitivity depends on your database's collation settings).
• If your department is tracked by name instead of an ID, adjust the JOIN condition to use e.DepartmentName = m.DepartmentName instead.

内容的提问来源于stack exchange，提问作者Kevin Farrell