时间戳

首页 > 解决方案 > 从 27 个表中选择数据到单个数据网格中

c# - 从 27 个表中选择数据到单个数据网格中

问题描述

我有一个应用程序，我将学生的成绩存储在 27 个表中。从1-9年级。每个年级有 3 个不同的表来存储第一、第二和第三学期的成绩，因此有 27 个表。所以我的问题是我将如何从 1-9 年级的学生中选择所有年级，并使用学生 ID 作为查询的唯一标识符进行显示。

private void btnsearch_Click(object sender, EventArgs e)
{
    cn.Open();
    cmd = new SqlCommand("select * from grade1FT_results INNER JOIN grade1ST_results,grade1TT_results,grade2FT_results,grade2ST_results,grade2TT_results,grade3FT_results,grade3ST_results,grade3TT_results,grade4FT_results,grade4ST_results,grade4TT_results,grade5FT_results,grade5ST_results,grade5TT_results,grade6FT_results,grade6ST_results,grade6TT_results,grade7FT_results,grade7ST_results,grade7TT_results,grade8FT_results,grade8ST_results,grade8TT_results,grade9FT_results,grade9ST_results,grade9TT_results ON StudentId=@Id ", cn);
    cmd.Parameters.AddWithValue("@Id", txtid.Text);
    cmd.ExecuteNonQuery();

    DataTable dtable;
    SqlDataAdapter da = new SqlDataAdapter();
    da.SelectCommand = cmd;
    dtable = new DataTable();
    da.Fill(dtable);
    BindingSource dsource = new BindingSource();

    dsource.DataSource = dtable;
    datagrid.DataSource = dsource;
    da.Update(dtable);

    cn.Close();
}

我收到错误语法错误：“',' 附近的语法不正确。” 在这条线上

cmd.ExecuteNonQuery();

任何帮助是极大的赞赏。

标签： c#sql-server

解决方案

就您遇到的问题而言，您的 SQL 不正确。这就是您收到该错误的原因。使用 SQL Server Management Studio 测试您的 SQL，而不是从应用程序中进行；这将为您节省很多时间。

我强烈建议在 SQL Server 中创建一个视图来执行此操作。我使用了 LEFT JOIN，因为您希望在结果中出现的学生可能直到 10,11 或 12 年级才上学，所以我假设没有记录。我认为这非常接近：

CREATE VIEW dbo.StudentG1toG9
AS
select * --You need to list out each column here (StudentID only once)
from Student
LEFT JOIN grade1FT_results AS [G1FT] ON [G1FT].[StudentID]=[Student].[StudentID]
LEFT JOIN grade1ST_results AS [G1ST] ON [G1ST].[StudentID]=[Student].[StudentID]
LEFT JOIN grade1TT_results AS [G1TT] ON [G1TT].[StudentID]=[Student].[StudentID]
LEFT JOIN grade2FT_results AS [G2FT] ON [G2FT].[StudentID]=[Student].[StudentID]
LEFT JOIN grade2ST_results AS [G2ST] ON [G2ST].[StudentID]=[Student].[StudentID]
LEFT JOIN grade2TT_results AS [G2TT] ON [G2TT].[StudentID]=[Student].[StudentID]
LEFT JOIN grade3FT_results AS [G3FT] ON [G3FT].[StudentID]=[Student].[StudentID]
LEFT JOIN grade3ST_results AS [G3ST] ON [G3ST].[StudentID]=[Student].[StudentID]
LEFT JOIN grade3TT_results AS [G3TT] ON [G3TT].[StudentID]=[Student].[StudentID]
LEFT JOIN grade4FT_results AS [G4FT] ON [G4FT].[StudentID]=[Student].[StudentID]
LEFT JOIN grade4ST_results AS [G4ST] ON [G4ST].[StudentID]=[Student].[StudentID]
LEFT JOIN grade4TT_results AS [G4TT] ON [G4TT].[StudentID]=[Student].[StudentID]
LEFT JOIN grade5FT_results AS [G5FT] ON [G5FT].[StudentID]=[Student].[StudentID]
LEFT JOIN grade5ST_results AS [G5ST] ON [G5ST].[StudentID]=[Student].[StudentID]
LEFT JOIN grade5TT_results AS [G5TT] ON [G5TT].[StudentID]=[Student].[StudentID]
LEFT JOIN grade6FT_results AS [G6FT] ON [G6FT].[StudentID]=[Student].[StudentID]
LEFT JOIN grade6ST_results AS [G6ST] ON [G6ST].[StudentID]=[Student].[StudentID]
LEFT JOIN grade6TT_results AS [G6TT] ON [G6TT].[StudentID]=[Student].[StudentID]
LEFT JOIN grade7FT_results AS [G7FT] ON [G7FT].[StudentID]=[Student].[StudentID]
LEFT JOIN grade7ST_results AS [G7ST] ON [G7ST].[StudentID]=[Student].[StudentID]
LEFT JOIN grade7TT_results AS [G7TT] ON [G7TT].[StudentID]=[Student].[StudentID]
LEFT JOIN grade8FT_results AS [G8FT] ON [G8FT].[StudentID]=[Student].[StudentID]
LEFT JOIN grade8ST_results AS [G8ST] ON [G8ST].[StudentID]=[Student].[StudentID]
LEFT JOIN grade8TT_results AS [G8TT] ON [G8TT].[StudentID]=[Student].[StudentID]
LEFT JOIN grade9FT_results AS [G9FT] ON [G9FT].[StudentID]=[Student].[StudentID]
LEFT JOIN grade9ST_results AS [G9ST] ON [G9ST].[StudentID]=[Student].[StudentID]
LEFT JOIN grade9TT_results AS [G9TT] ON [G9TT].[StudentID]=[Student].[StudentID]
GO

然后你可以简单地从代码中得到它：

SELECT *
FROM dbo.StudentG1toG9
WHERE [StudentID]=@Id

推荐阅读