How to read data using SqlDataReader when two columns in different tables have same name?

Question

I have two columns named 'Name' in two different tables. One is in the Store table the other is in the Product table. How do I differentiate which one is read?

The data is taken from 4 different tables using left joins as shown in the query below.

var conn = new SqlConnection("serverinfo");
SqlCommand query = new SqlCommand("SELECT Store.StoreID, Store.Name, 
Product.Name, " +
" StockRequest.Quantity, StoreInventory.StockLevel from Store" +
" LEFT JOIN StoreInventory ON StoreInventory.StoreID = Store.StoreID" 
+ " LEFT JOIN Product ON StoreInventory.ProductID = 
Product.ProductID" + " LEFT JOIN StockRequest ON StockRequest.StoreID 
= Store.StoreID", conn);

SqlDataReader read;

        try {
            conn.Open();
            read = query.ExecuteReader();
            while (read.Read()) {
                Console.WriteLine("{0} {1} {2} {3} {4}",
                                  read["StoreID"],
                                  read["Name"],
                                  read["Name"],
                                  read["Quantity"],
                                  read["StockLevel"]);

            }
            conn.Close();
            } catch (Exception e) {}

The table names are Store, Product, StockRequest, StoreInventory.

Right now the data for both of the 'Name' columns are taken from the Store table. I am unable to take the 'Name' data from the Product table.

and what are the table names ??ur SqlCommand doesn't include any table name ! — Aousaf Rashid
– Aousaf Rashid, Commented Mar 24, 2018 at 23:50
Why not use another SqlCommand and read the tables that contain Name separately ? — Aousaf Rashid
– Aousaf Rashid, Commented Mar 24, 2018 at 23:54
Give a alias to duplicate column using 'as'; one simple way to solve if everything else is working for you. — haku
– haku, Commented Mar 24, 2018 at 23:57
Rename the columns like "SELECT Store.StoreID, Store.Name AS StoreName, Product.Name AS ProductName, …". — ckuri
– ckuri, Commented Mar 24, 2018 at 23:58

haku · Accepted Answer · 2018-03-25 00:03:41Z

3

You could use alias to give a different name to the duplicate column.

SqlCommand query = new SqlCommand("SELECT Store.StoreID, 
"Store.Name as StoreName," + 
"Product.Name as ProductName," +
" StockRequest.Quantity, StoreInventory.StockLevel from Store" +
" LEFT JOIN StoreInventory ON StoreInventory.StoreID = Store.StoreID" 
+ " LEFT JOIN Product ON StoreInventory.ProductID = 
Product.ProductID" + " LEFT JOIN StockRequest ON StockRequest.StoreID 
= Store.StoreID", conn);

Then just use the right name when reading it, like:

read["StoreID"],
read["StoreName"], read["ProductName"],
read["Quantity"], read["StockLevel"]

answered Mar 25, 2018 at 0:03

haku

4,5649 gold badges47 silver badges71 bronze badges

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

Collectives™ on Stack Overflow

How to read data using SqlDataReader when two columns in different tables have same name?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related