Does SQLDataReader Field Order matter?

Question

Say I have a block of code that retrieves rows from my database and writes them to a variable (html)

using (SqlConnection conn = new SqlConnection())
{
    conn.ConnectionString = ConfigurationManager.ConnectionStrings["connection_string"].ConnectionString;
    SqlCommand cmd = new SqlCommand("SELECT * FROM Table WHERE order_id = @id", conn);
    cmd.Parameters.Add("id", SqlDbType.Int).Value = Request.QueryString["id"];
    conn.Open();
    SqlDataReader sdr = cmd.ExecuteReader();
    while (sdr.Read())
    {                   
        html += sdr.IsDBNull(2) ? "" : sdr.GetString(2);
        html += sdr.IsDBNull(1) ? 0 : sdr.GetInt32(1);
        html += sdr.IsDBNull(4) ? 0 : sdr.GetInt32(4);
        html += sdr.IsDBNull(3) ? "" : sdr.GetString(3);
        html += sdr.IsDBNull(0) ? 0 : sdr.GetInt32(0);
    }
}

lets assume I need them output in that exact order - 2, 1, 4, 3, 0. Should I do it this way instead?

while (sdr.Read())
{        
    html += string.Format("{2}{1}{4}{3}{0}",
        sdr.IsDBNull(0) ? 0 : sdr.GetInt32(0),
        sdr.IsDBNull(1) ? 0 : sdr.GetInt32(1),
        sdr.IsDBNull(2) ? "" : sdr.GetString(2),
        sdr.IsDBNull(3) ? "" : sdr.GetString(3),
        sdr.IsDBNull(4) ? 0 : sdr.GetInt32(4)
    );
}

Will the second method be more efficient because the SQLDataReader reads them in the correct order?

Short answer: No, it doesn't matter. Do what is most readable and less error-prone. But you should not write `SELECT * FROM` but `SELECT col1,col2,col3,... FROM`. — Tim Schmelter, May 31 '13 at 11:32
Personally I wouldn't worry about performance unless it's too slow. I'd use the column names to make it more readable. Also look into using StringBuilder rather than concatinating strings, as that can be much faster. — Chris Diver, May 31 '13 at 11:35
I usually do use column names, but this was just an example so I couldn't be bothered making them up =) out of interest, is there a performance benefit in specifying column names if you are still retrieving all columns? — roryok, May 31 '13 at 11:45

score 1 · Accepted Answer · answered May 31 '13 at 11:33

1

No the order does not matter. You can access them in any order you like. You already have both test cases, you can test it yourself. It's always better to check for yourself than listening to random strangers on the internet.

answered May 31 '13 at 11:33

nvoigt

75,013
26
93
142

score 0 · Answer 2 · edited May 23 '17 at 10:25

Should I do it this way instead?

Of course you can. SqlDataReader doesn't force you to read rows as their order. You can use them any order you want.

Will the second method be more efficient because the SQLDataReader reads them in the correct order?

I don't think it matters. SqlDataReader doesn't read first four rows for read only 5th row. So there is no performance issue between them. Use them which one is more readable for you.

As Tim suggest, you can use column names in your select statement so it can be more readable for you specially when you have a lot of columns.

Is there a performance benefit in specifying column names if you are still retrieving all columns?

No.

There is no performance benefit between SELECT * FROM and SELECT col1,col2,col3, col4, col5 FROM They are quite equivalent. (I assume you have only 5 columns in your table)

Does SQLDataReader Field Order matter?

2 Answers2