How should I refer to data bound columns?

Question

I work often with data-bound applications with WinForms; records are pulled from a database and updated in a client application that I write. Stored procedures are used for all database transactions in the client.

I need to programmatically refer to columns from the database - for example, to format a dollar value.

resultsTable.Columns[<index>].DefaultCellStyle.Format = "0ドル.00"

I strongly dislike using hard-coded integer indices.
I've played with enums, like this:

enum Columns
 {
 ID,
 OrderID,
 Notes,
 CustomerName,
 AssembledBy,
 Status,
 CreateDate
 }

But doing this, I always have to make an explicit cast resultsTable.Columns[(int)Columns.CreateDate]
and I have to update the enumeration if the stored procedure changes.

I can reference columns by name resultsTable.Columns["create_date"]. This is easy to read but seems very brittle.

What's a good way to reference the column I'm looking for? I'd love some dynamic way of getting column indices, but I don't know where to start.

Answer 1

Most databases have tables that store schema information about tables that are being maintained. For example, in SQL Server:

SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @Table ORDER BY COLUMN_NAME

Will get you a list of columns for a particular table.

However, in your case, I would just create a list of string constants for each column name. Refer to your columns by name.

public const string Name_Of_Column_Goes_Here = "NameOfColumn";

Then in code refer to the constant string. If the column name ever changes, one will only have to update the constant and then that change will propagate through the code. This is better than maintaining indexes or an enum of indexes.

• paparazzo: But that does not extend to stored procedures. A name can be changed and and aggregate must product a new name.
• Jon Raynor: If you change a column name and stored procedures refers to the previous name, it must be updated. No way around that.

Answer 2

Pretty sure they always iterate in order
If not use the Ordinal
There is a DataType if you have a standard format for a type
Dictionary is not going to be the easiest syntax but it will be the fastest

Dictionary<string, int> dicColMap = new Dictionary<sting, int>();
foreach(DataColumn column in table.Columns)
{
 Console.WriteLine(column.ColumnName);
 Console.WriteLine(column.Ordinal);
 Console.WriteLine(column.DataType);
 dicCo,Map.Add(column.ColumnName, column.Ordinal);
}