Is it better to use a column name or column index in a .Net DataSet?

Question

When retrieving values ​​from a DataRow, is it better to use a column name or column index?

The column name is more readable and easier to maintain:

int price = (int)dr["Price"]; 

Although the column index is faster (I think):

 int price = (int)dr[3]; 

Are column names used if you decide to obfuscate the database?

Answer 1

I usually prefer readability and understanding of speed. Go with the name. You can (should) use string constants that can be updated in one place if you decide to change the database column names.

Answer 2

Access to column / row values ​​through column names is better for human reading and for direct compatibility (if in the future someone changes the order or number of columns).

Maximum column / row values ​​for column indices are better for performance.

So, if you want to change some value in one / two / ..... rows, the column names are fine. But if you want to change the value in thousands of rows, you must use the column index computed from the column name:

 int ndxMyColumn = table.Columns.IndexOf( "MyColumn" ); foreach(DataRow record in table.Rows ) { record[ndxMyColumn] = 15; } 

Answer 3

Totally aggression with other people. go for readability and maintainability in speed. However, I had a general method that required to get named columns passed as parameters, so it makes sense to figure out what the column indexes were.

There was a significant improvement in the comparative analysis below using the column index, so if this is a narrow area or a critical part of your code, this might be useful.

The output from the code below:

515ms with ColumnIndex

1031ms with ColumnName

  static void Main(string[] args) { DataTable dt = GetDataTable(10000, 500); string[] columnNames = GetColumnNames(dt); DateTime start = DateTime.Now; TestPerformance(dt, columnNames, true); TimeSpan ts = DateTime.Now.Subtract(start); Console.Write("{0}ms with ColumnIndex\r\n", ts.TotalMilliseconds); start = DateTime.Now; TestPerformance(dt, columnNames, false); ts = DateTime.Now.Subtract(start); Console.Write("{0}ms with ColumnName\r\n", ts.TotalMilliseconds); } private static DataTable GetDataTable(int rows, int columns) { DataTable dt = new DataTable(); for (int j = 0; j < columns; j++) { dt.Columns.Add("Column" + j.ToString(), typeof(Double)); } Random random = new Random(DateTime.Now.Millisecond); for (int i = 0; i < rows; i++) { object[] rowValues = new object[columns]; for (int j = 0; j < columns; j++) { rowValues[j] = random.NextDouble(); } dt.Rows.Add(rowValues); } return dt; } private static void TestPerformance(DataTable dt, string[] columnNames, bool useIndex) { object obj; DataRow row; for (int i =0; i < dt.Rows.Count; i++) { row = dt.Rows[i]; for(int j = 0; j < dt.Columns.Count; j++) { if (useIndex) obj = row[j]; else obj = row[columnNames[j]]; } } } private static string[] GetColumnNames(DataTable dt) { string[] columnNames = new string[dt.Columns.Count]; for (int j = 0; j < columnNames.Length; j++) { columnNames[j] = dt.Columns[j].ColumnName; } return columnNames; } 

Answer 4

I would think a column name is the best way. It’s easier to determine what you are pulling, and the order of the columns is determined by the select statement, which may change sometime along the way. You could argue that the column name may also change, but I think it will be much less likely.

EDIT:

Actually, if you were really inclined to use column indexes, you could create column index constants and call the constant the column name. So:

 PRIMARY_KEY_COLUMN_NAME_INDEX = 0 

This will at least make it readable.

Answer 5

It depends on what you need. In my case, I had a situation where speed was paramount, since I was doing heavy processing on thousands of rows in a DataSet, so I decided to write a piece of code that cached column indices by name. Then, in the loop code, I used cached indexes. This gave a reasonable increase in performance using the column name directly.

Of course, your mileage may vary. My situation was a rather far-fetched and unusual case, but in this case it worked pretty well.

Answer 6

My opinion is that you should only switch to indexes if you have profiled your code, and this showed as a bottleneck. I do not think this will happen.

Naming the material is good, it makes our limited brain understand the problems and make link building easier. That's why we are given names like Fred, Martin, Jamie, and not Man [189333847], Man [138924342] and Man [239333546].

Answer 7

If you decide to obfuscate the database by changing the column names in the future, you can use these columns in your query to maintain the functionality of the indexer. I suggest indexing by name.

Answer 8

Go with the name, you will get an error message :)

Answer 9

I select strings for readability and maintenance. I use row constants to determine column name values. Example:

 public class ExampleDataColumns { public const string ID = "example_id"; public const string Name = "example_name"; .... } 

Then I can refer to it as follows:

 row[ExampleDataColumns.ID] 

Answer 10

Use the column names for the DataRow with the same token that RDBMS will not receive speed, requiring programmers to specify the column index in SQL. But you can probably imitate the way the RDBMS works when you issue the SELECT statement, inside the RDBMS mechanism it asks for the column index / offset of the columns specified in the SELECT clause before crossing the rows so it can work faster.

If you really want to get speed, don't , make it a const / enum path (the order of the columns may change in your database or at the ORM level). Do it like TcKs (before the actual loop):

 int ndxMyColumn = table.Columns.IndexOf( "MyColumn" ); foreach(DataRow record in table.Rows ) { record[ndxMyColumn] = 15; } 

Answer 11

for me, I use reflection (not sure if this is the right way to name what I'm doing) to get the column name from the table

no "hardcoding" is better

  int price = (int)dr[DatableVar.PriceColumn];