Duplicate column name weirdness in ADO.NET
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.