Home > .NET, Fun > Duplicate column name weirdness in ADO.NET

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 errorbut 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).
(Iam using .NET 1.1 in this
code and haven’t tried it yet with 2.0)

Jonathan Cogley isthe 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
isthe leader in secret management and sharing within companies and
teams.

Categories: .NET, Fun
  1. Jonathan Cogley
    April 10, 2006 at 11:25 pm

    I just confirmed the same behavior on .NET 2.0 with SQL Server 2005.

  2. Nicole Calinoiu
    April 11, 2006 at 12:04 am

    It’s documented behaviour. See the “Remarks” section at http://msdn2.microsoft.com/en-us/library/zxkb3c3d(VS.80).aspx.

  3. Jonathan Cogley
    April 11, 2006 at 1:00 pm

    Thanks Nicole. I didn’t think to check the Fill method documentation!

    I wonder what our Data Access Layer (Thycotic.Data) would do with this problem since it doesn’t use an Adapter but rather creates the DataTable directly from the DataReader using some neat code from Steve Smith (http://authors.aspalliance.com/stevesmith/articles/convertReadertoSet.asp). Looks like we would have to handle unique column name creation in some similar manner.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: