CODEWITHSUNDEEP
c#sqlwinforms
muhan
muhan

Reputation: 2557

C# datatable from sql join 2 columns same name

I have a sql select like so which returns a datatable:

select * from table1 a join table2 b on a.id=b.id

Both table1 and table2 have a column named itemno .

How do i reference the 2 separate columns? Normally I would use something like:

datarow["itemno"].ToString(); <=first column named itemno only
datarow["b.itemno"].ToString(); <= fail

However this only seems to get the first column named itemno.
Is there a way to reference the second column named itemno without changing my sql statement? (I know i can change my sql statement, take out the * and put in column aliases).

Upvotes: 1

Views: 2302

Answers (2)

Steffen Winkler
Steffen Winkler

Reputation: 2864

Given an SQL query like this

select a.id, b.id, a.columnA,a.columnB,a.itemno,b.itemno 
from table1 a
join table2 b on a.id=b.id

Your C# code would/could look like this to read all rows and all columns:

using (SqlCommand getAllColumns = new SqlCommand("select a.id, b.id,a.columnA,a.columnB,a.itemno,b.itemno from table1 a join table2 b on a.id=b.id", conn))
                    {
                        using (var drreader = getAllColumns.ExecuteReader())
                        {
                            DataTable tb = new DataTable();
                            tb.BeginLoadData();
                            tb.Load(drreader);
                            tb.EndLoadData();
                            foreach(DataRow row in tb.Rows.Cast<DataRow>().ToList())
                            {
                                 // assuming these are all varchar columns
                                 string idA = (string)row["id"];
                                 string idB = (string)row["id1"];
                                 string columnA = (string)row["columnA"];
                                 string columnB = (string)row["columnB"];
                                 string columnAItemNo = (string)row["itemno"]; //fetches the first itemno column, a.itemno in this case
                                 string columnBItemNo = (string)row["itemno1"]; //fetches the second itemno column, b.itemno in this case
                                 
                            }
                        }
                    }

I use this on .NET Framework 4.5. If you want to verify or debug this, put a breakpoint on the foreach line and inspect the DataTable object. The second itemno column should be titled differently compared to the first one.

Upvotes: 0

AdaTheDev
AdaTheDev

Reputation: 147224

You can reference the columns by index instead:

datarow[0].ToString();

I'd much prefer aliasing them though to be honest.

Upvotes: 3

Related Questions