如何使用临时表执行T-SQL脚本并在C# MVC应用程序上显示该表

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Department NVARCHAR(50)
);

-- 创建局部临时表
CREATE TABLE #TempEmployees (
    EmployeeID INT,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Department NVARCHAR(50)
);

-- 将数据从Employees表插入到临时表
INSERT INTO #TempEmployees (EmployeeID, FirstName, LastName, Department)
SELECT EmployeeID, FirstName, LastName, Department FROM Employees;

-- 查询临时表
SELECT * FROM #TempEmployees;

using System.Data;
using System.Data.SqlClient;
using System.Web.Mvc;

public class HomeController : Controller
{
    private string connectionString = "your_connection_string_here";

    public ActionResult Index()
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();

            // 执行T-SQL脚本
            string sql = @"
                CREATE TABLE #TempEmployees (
                    EmployeeID INT,
                    FirstName NVARCHAR(50),
                    LastName NVARCHAR(50),
                    Department NVARCHAR(50)
                );

                INSERT INTO #TempEmployees (EmployeeID, FirstName, LastName, Department)
                SELECT EmployeeID, FirstName, LastName, Department FROM Employees;

                SELECT * FROM #TempEmployees;
            ";

            using (SqlCommand command = new SqlCommand(sql, connection))
            {
                using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                {
                    DataTable tempEmployees = new DataTable();
                    adapter.Fill(tempEmployees);

                    // 将数据传递给视图
                    return View(tempEmployees);
                }
            }
        }
    }
}

@model System.Data.DataTable

<table>
    <thead>
        <tr>
            <th>Employee ID</th>
            <th>First Name</th>
            <th>Last Name</th>
            <th>Department</th>
        </tr>
    </thead>
    <tbody>
        @foreach (System.Data.DataRow row in Model.Rows)
        {
            <tr>
                <td>@row["EmployeeID"]</td>
                <td>@row["FirstName"]</td>
                <td>@row["LastName"]</td>
                <td>@row["Department"]</td>
            </4>
        }
    </tbody>
</table>