DuplicateNameException when adding columns to a DataTable dynamically

David C. Williams

4.00/5 (1 vote)

Jun 24, 2011

CPOL

26826

When building an ADO.NET DataTable dynamically in your code, each column name must be unique or you get a System.Data.DuplicateNameException. If you don't know at design time that the columns you will be adding are certain to have unique names, you can use this method to avoid a runtime error.

The method takes as parameters the DataTable you want to add the column to and a StringBuilder representing the name of the column. Call the method, passing it both parameters:

System.Data.DataTable dt = new DataTable();
System.Data.SqlClient.SqlCommand cmd = 
new System.Data.SqlClient.SqlCommand("SELECT ColumnName FROM Foo", connString);

System.Data.SqlClient.SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
    while (dr.Read())
    {
       System.Text.StringBuilder name = new System.Text.StringBuilder(dr.GetString(0));
       AddTableColumn(dt, name)
    }
}

The method calls itself recursively, trying to add the column and appending a space each time it fails, until the column can be added to the table. Extra spaces make the name unique and avoid the DuplicateNameException.

private static void AddTableColumn(DataTable resultsTable, StringBuilder ColumnName)
        {
            try
            {
                DataColumn tableCol = new DataColumn(ColumnName.ToString());
                resultsTable.Columns.Add(tableCol);
            }
            catch (System.Data.DuplicateNameException)
            {
                ColumnName.Append(" ");
                AddTableColumn(resultsTable, ColumnName);
            }
        }