Using the column index is faster than using the column name. If you know the
name but not the index, you can get the index once and then use it
repeatedly, improving your performance. This is from Dave Sceppa's book on
ADO.Net; here's his example. This assumes you are using a SqlDataReader. If
using a dataset, it's still faster to use the column index, but I don't know
if it has the GetOrdinal method.
'example of GetOrdinal -- faster to use ordinal to retrieve data
' than to use name of field
'Also uses GetString instead of just rdr(ordinal), which is a strongly typed
Getter
' that corresponds to the data returned by the column in the resultset.
'This gives better performance and avoids boxing/unboxing.
string SQLString = "SELECT CustomerID, CompanyName From Customers";
SqlConnection cn = New SqlConnection(connString);
cn.Open();
SqlCommand cmd = New SqlCommand(SQLString, cn);
SqlDataReader rdr = SqlDataReader = cmd.ExecuteReader();
int CustomerIDOrdinal = rdr.GetOrdinal("CustomerID");
int CustomerNameOrdinal = rdr.GetOrdinal("CompanyName");
While (rdr.Read())
{
Console.WriteLine("{0}: {1}", rdr[CustomerIDOrdinal], _
rdr[CompanyNameOrdinal]);
}
rdr.Close();
cn.Close();
If you want to speed it up even more, and you know what type the column is
and are using SQLServer, you can pull it this way and it will be even
faster. In this case, both columns are strings:
Console.WriteLine("{0}: {1}", rdr.GetString(CustomerIDOrdinal), _
rdr.GetString(CompanyNameOrdinal));
This eliminates some boxing and unboxing by pulling the exact type.
RobinS.
GoldMail, Inc.