C# datatable from sql join 2 columns same name

Question

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

AdaTheDev · Accepted Answer · 2009-08-07 07:46:18Z

3

You can reference the columns by index instead:

datarow[0].ToString();

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

answered Aug 7, 2009 at 7:46

AdaTheDev

148k28 gold badges215 silver badges204 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user151323 Over a year ago

Here is the danger hidden. If you change you query later a little and it will start to return the columns in a different order, you code will stop working.

Max Over a year ago

Good point NewInTown. The aliasing is by far the best way to do it.

muhan Over a year ago

Alias them in the SQL statement? It's a very long sql statment with lots of columns. I guess that's my only choice as I'd rather not use a numerical index. I can't believe there is no way to reference columns with the same name in ADO/C#.

AdaTheDev Over a year ago

Yes. It's generally a good idea to list the specific columns to return anyway to prevent returning unneccessary data and can result in better query performance. So would be a good idea to think about whether you need all columns returned anyway.

Steffen Winkler · Accepted Answer · 2020-10-20 13:09:44Z

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.

Collectives™ on Stack Overflow

C# datatable from sql join 2 columns same name

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related