C＃如何编写标题和详细查询 [英] C# how to write header and detail query

问题描述

我有一个表单和2个sql表。表1包含标题信息和表2详细信息

一个按钮，用于启动将数据从表格写入应用程序的过程。

I have a form and 2 sql tables. Table 1 contains header info and table 2 details info
A button that starts the process of writing data from the tables to an application.

private void button1_Click(object sender, EventArgs e)
{
    using (SqlCommand cmd =  new SqlCommand(@SELECT col1,col2,col3,col4 FROM tbl1,conn))
    using (var rdr = cmd.ExecuteReader())
    {
    while (rdr.Read())
    {
    writeHeadData(rdr["col1"], rdr["col2"], rdr["col3"], rdr["col3"],rdr["col4"]);
    }
}


private void writeHeadData(string col1,string col2,string col3,string col4)
{
	//do something here (writeHeadData)
	writeDetailData(col1, col2, col3, col4)
}

private void writeDetailData(string col1,string col2,string col3,string col4)
{
	//do something here with data from the details table
}

我在哪里放置详细信息表中的查询（见下文）和使用WHERE子句确保详细信息与标题信息相关

Where do I put the query from the detail table (see below) and ensure that the detail info relates to the header info using the WHERE clause

using (SqlCommand cmd =  new SqlCommand(@SELECT * FROM tbl2,conn))
using (var rdr = cmd.ExecuteReader())
while (rdr.Read())
{
writeHeadData(rdr["col1"], rdr["col2"], rdr["col3"], rdr["col3"],rdr["col4"]);
}

每个标题行有3个详细信息行



我希望无论谁读这个了解我想要实现的目标，如果不清楚，请告诉我，我会尝试进一步阐明



我尝试过：



创建嵌套while。在两个部分中重复相同的查询。尝试使用单个表

There are 3 details rows for every header row

I hope whoever reads this understands what I’am trying to achieve and if not clear please tell me and I shall try and elucidate further

What I have tried:

Creating a nested while. Repeat the same query in both sections. Tried using a single table

推荐答案

我不是肯定的我理解你的问题，但我会开枪。据我了解，你有两个相关的表：一个包含头信息，另一个包含详细信息。您希望每个标题行大约有三个详细信息行。



加入这两个表中信息的最有效方法是使用INNER JOIN语句。这将导致结果集中的一些重复，您可以通过代码轻松过滤掉客户端（例如，查找commonColumn值的更改）。复制将来自标题表中的列值，重复三个细节行中的每一行。



为了可靠地执行此操作，您至少需要一列两个表共有的（或列集）。无论采用何种方法，多表情况都是如此。



要通过SELECT命令加入这两个表，只需执行以下操作即可：



SELECT header。*，details。* FROM header INNER JOIN详情ON details.commonColumn = header.commonColumn



在上面的例子中，假设你有一个名为header的表，一个名为details的表，以及一个名为commonColumn的公共列。 注意：只要列的值相同，公共列的名称在两个表中可以不同。



快捷方式header。*可用于引用表header中的所有列。同样，快捷方式details。*用于引用表details中的所有列。



实际上最好列出你真正想要的每一列。查询。因此，例如，代替header。*，列出header.commonColumn，header.anotherColumn。



您的结果集如下所示：



commonColumn someDetailColumn

id1 detail1

id1 detail2

id1 detail3

id2 detailA

id2 detailB

id3 detailC



替代方案，涉及多个查询，每个使用WHERE子句（类似于连接）的效率要低得多，因为它需要多次往返数据库服务器。



我希望这有帮助。

I am not positive I understand your question, but I'll take a shot. As I understand it, you have two related tables: one contains header information and the other contains detail information. You expect approximately three detail rows for every header row.

The most efficient way to join the information from those two tables is to use an INNER JOIN statement. This will result in some duplication in the result set, which you can easily filter out on the client side, via code (for example looking for a change in the value of a "commonColumn"). The duplication would come from the column values, from the header table, repeating for each of the three detail rows.

To do this reliably, you need at least one column (or set of columns) that are common to both tables. No matter what approach you take, this would be the case in a multi-table scenario.

To join the two tables, via your SELECT command, simply do the following:

SELECT header.*, details.* FROM header INNER JOIN details ON details.commonColumn=header.commonColumn

In the above example, the assumption is that you have a table named "header", a table named "details", and a common column named "commonColumn". Note: The name of the common column can be different in the two tables, as long as the value of the column is the same.

The short cut "header.*" can be used to reference ALL columns in the table "header". Likewise, the shortcut "details.*" is used to reference ALL columns in table "details".

It is actually preferable to list each column you actually want in the query. So, for example, instead of "header.*", list "header.commonColumn, header.anotherColumn".

Your result set would look something like the following:

commonColumn someDetailColumn
id1 detail1
id1 detail2
id1 detail3
id2 detailA
id2 detailB
id3 detailC

The alternative, which would involve multiple queries, each using a WHERE clause (similar to the join) would be far less efficient, since it would require multiple round trips to your database server.

I hope this helps.

这篇关于C＃如何编写标题和详细查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！