Duplicate column name weirdness in ADO.NET

Monday, April 10, 2006

I came across the following issue the other day with a very large stored procedure that had *lots* of columns. Unfortunately I had introduced a duplicate column name by using "as" in my SQL but things definitely work as I expected. Take a look for yourself ...

  1 using (SqlConnection connection = new SqlConnection("user id=x;password=y;server=localhost;"))
  2 {
  3 	connection.Open();
  4 	SqlDataAdapter adapter = new SqlDataAdapter("SELECT 1 as A, 2 as a, 3 as a", connection);
  5 	DataSet dataSet = new DataSet();
  6 	adapter.Fill(dataSet);
  7 	DataTable table = dataSet.Tables[0];
  8 
  9 	Console.Out.WriteLine("table.Rows.Count = {0}", table.Rows.Count);
 10 	Console.Out.WriteLine("table.Columns.Count = {0}", table.Columns.Count);
 11 	Console.Out.WriteLine("table.Rows[0][\"A\"] = {0}", table.Rows[0]["A"]);
 12 	Console.Out.WriteLine("table.Rows[0][\"a\"] = {0}", table.Rows[0]["a"]);
 13 	Console.Out.WriteLine("table.Rows[0][0] = {0}", table.Rows[0][0]);
 14 	Console.Out.WriteLine("table.Rows[0][1] = {0}", table.Rows[0][1]);
 15 	Console.Out.WriteLine("table.Rows[0][2] = {0}", table.Rows[0][2]);
 16 	Console.Out.WriteLine("table.Columns[0].ColumnName = {0}", table.Columns[0].ColumnName);
 17 	Console.Out.WriteLine("table.Columns[1].ColumnName = {0}", table.Columns[1].ColumnName);
 18 	Console.Out.WriteLine("table.Columns[2].ColumnName = {0}", table.Columns[2].ColumnName);
 19 	Console.Out.WriteLine("table.Rows[0][\"a1\"] = {0}", table.Rows[0]["a1"]);
 20 	Console.Out.WriteLine("table.Rows[0][\"a2\"] = {0}", table.Rows[0]["a2"]);
 21 	Console.ReadLine();
 22 }
 23

Think you know what the output will be?

table.Rows.Count = 1
table.Columns.Count = 3
table.Rows[0]["A"] = 1
table.Rows[0]["a"] = 1
table.Rows[0][0] = 1
table.Rows[0][1] = 2
table.Rows[0][2] = 3
table.Columns[0].ColumnName = A
table.Columns[1].ColumnName = a1
table.Columns[2].ColumnName = a2
table.Rows[0]["a1"] = 2
table.Rows[0]["a2"] = 3

I was surprised that I didn't get an ambiguous column name error but the "as" keyword does not seem to mind duplicates ... interesting. How does ADO.NET then handle this since we can see the retrieval of the column by case specific indexer does not affect DataRow.Item[string]'s behavior? It uses weird a1 and a2 columns.

Any ADO.NET gurus know why this happens or where it is documented? (I couldn't find any mention of this behavior in the docs).
(I am using .NET 1.1 in this code and haven't tried it yet with 2.0)

Jonathan Cogley is the CEO and founder of thycotic, a .NET consulting company and ISV in Washington DC. thycotic has just released Thycotic Secret Server which is a secure web-based solution to both "Where is my Hotmail password?" and "Who has the password for our domain name?". Secret Server is the leader in secret management and sharing within companies and teams.

1 Comment