如何使用C＃winforms连接多个表并在单个数据网格中显示 [英] How to join multiple tables and display in a single datagrid using C# winforms

问题描述

我有一个应用程序可以存储1 - 9年级的学生成绩。每个年级有3个学期，所以共有27个表来存储学生的成绩。学生可以从任何年级开始，因此可能是学生的成绩为4 - 9年级，因为他/她从4年级开始。所以我的表格名称如下，Grade1FT，Grade1ST，Grade1TT，Grade2FT，Grade2ST， Grade3TT等，即它遵循这样的模式，直到Grade9FT，Grade9ST，Grade9TT。现在每个成绩表包含10列，因此列名称包括ID，英语，数学，科学，社交，RME，ICT，绘图，历史和地理。

我有另一个名为register的表，其中所有学生注册他们的名字并获得他们唯一的ID。



我希望学生能够搜索1 - 9年级的所有成绩，并在单个数据网格中显示查看使用他/她输入文本框的唯一学生ID。我输入了一个试图运行的示例代码，但是我收到一个错误，说左边附近的错误



这是我试过的但是没有运气。



我尝试过：

  private   void  btnsearchall_Click（ object  sender，EventArgs e） 
 {
 cn.Open（）; 
 cmd =  new  SqlCommand（  SELECT寄存器。学生等LEFT JOIN grade1FT_results，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 WHERE 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（）; 
 
 
 
} 

解决方案

SELECT语句的工作方式如下;

  SELECT  
<列名> 
  FROM  
<表名> <别名> 
<加入类型>  JOIN <表名> <别名>  ON <加入条件> 
  WHERE  
<条件> 

此示例如下：

  SELECT  
 a.StudentName，b .Grade  AS  Yr1Term1，c .Grade  AS  Yr1Term2 
  FROM  
注册
  LEFT   JOIN  grade1FT_results b  ON  a.StudentId = b.StudentId 
  LEFT   JOIN  grade1ST_results c  ON  a.StudentId = c.StudentId 
  WHERE  
 a.StudentId =  123  

有关Select语句的完整说明，请参阅以下链接; 选择声明 [^]



你得到的错误是因为你没有正确指定你的FROM语句



种类问候

0）你的查询不会运行。



1）我会在数据库中创建一个执行所有这些表上的联合

  SELECT  *  FROM  mydatabase.dbo.grade1FT_results 
  UNION   ALL   SELECT  *  FROM  mydatabase.dbo.grade1ST_results 
 ... 
 ... 
  UNION   ALL   SELECT  *  FROM  mydatabase.dbo.grade9TT_results 

...你的代码中的查询最终会看起来像这样：



< pre lang =sql> SELECT * FROM mydatabase.dbo.myview WHERE StudentID = @ id

Keep请记住，我假设所有表都具有完全相同的模式，但如果它们没有，则必须按名称选择每个列，而不是使用 SELECT *



还有其他方法可以做到，所以如果您不喜欢这种方式，请随意搜索其他方式。

I have an application that stores students results from Grade 1 - 9. Each grade has 3 terms so there are 27 tables in total to store the grades of a student. A student can start from any grade, therefore it might be that a student has results for grade 4-9 because he/she started from grade 4. So my tables tables are names as follows, Grade1FT,Grade1ST,Grade1TT,Grade2FT,Grade2ST,Grade3TT etc. i.e it follows such a pattern right to Grade9FT,Grade9ST,Grade9TT. Now each grade table contains 10 columns, so the column names are ID,English,Maths,Science,Social,RME,ICT,Drawing,History and Geography.
I have another table called register where all students register their names and are given their unique ID's.

I want the ability where a student can search through all his results from grade 1 - 9 and display in a single datagrid view using his unique student ID which he/she inputs into a textbox. I put in a sample code trying to run but l got an error saying "error near LEFT"

Here is what l tried but with no luck.

What I have tried:

private void btnsearchall_Click(object sender, EventArgs e)
        {
            cn.Open();
            cmd = new SqlCommand(" SELECT register.StudentId LEFT JOIN grade1FT_results,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 WHERE 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();

           

        }

解决方案

The SELECT statement works as follows;

SELECT
<Column Names>
FROM
<Table Name> <alias>
<Join Type> JOIN <Table Name> <alias> ON <join condition>
WHERE
<Conditions>

An example of this is as follows;

SELECT
a.StudentName, b.Grade AS Yr1Term1, c.Grade AS Yr1Term2
FROM
Register a
LEFT JOIN grade1FT_results b ON a.StudentId = b.StudentId
LEFT JOIN grade1ST_results c ON a.StudentId = c.StudentId
WHERE
a.StudentId = 123

Refer the following link for a full description of the Select statement; Select Statement[^]

The error you are getting is because you have not specified your FROM statement correctly

Kind Regards

0) Your query won’t run.

1) I would create a view in the database that performs a union on all of those tables

SELECT * FROM mydatabase.dbo.grade1FT_results
UNION ALL SELECT * FROM mydatabase.dbo.grade1ST_results
...
...
UNION ALL SELECT * FROM mydatabase.dbo.grade9TT_results

...and the query in your code would end up looking something like this:

SELECT * FROM mydatabase.dbo.myview WHERE StudentID = @id

Keep in mind that I'm assuming all of the tables have exactly the same schemas, but if they don't, you'll have to select each individual column by name instead of using SELECT *

There are other ways to do it, so if you don't like this way, feel free to search out other ways.

这篇关于如何使用C＃winforms连接多个表并在单个数据网格中显示的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！