Click here to Skip to main content
15,063,596 members
Please Sign up or sign in to vote.
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
Updated 18-Feb-17 9:57am
v2

1 solution

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.
   
v2
Comments
callasda 19-Feb-17 14:38pm
   
Thank you Eric
I'll explain in another way. I have 2 tables. Order and OrderDetails. Master and child. So the order header info will go inside the method "writeHeadData" and the order details info will go inside the method "writeDetailData" based on the order header info
Can you please tell me what is the query that should go inside the "writeHeadData" and what the is the query is that should go inside "writeDetailData" allowing for the the common key/field between the 2 tables
This is the difficulty I am having.

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900