C# how to write header and detail query

Question

0.00/5 (No votes)

See more:

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.

C#

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 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

C#

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"]);
}

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

Posted 12-Feb-17 10:30am

callasda

Updated 18-Feb-17 9:57am

Bryian Tan

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Eric Lynch · Answer 1 · 2017-02-18T09:57:00

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.