SQL data reader reading data performance test

/*author:jiangong sun*/

As I ' ve manipulated a lot of data using SQL data reader in recent project. And people says it ' s not good to access the data by column name.

So I ' ve made a performance test in reading data from SQL data reader.

Firstly, I ' ve created a table with different data types, like int, varchar, date time etc.

CREATE TABLE userinformation (Id BIGINT, FirstName NVARCHAR (255), LastName NVARCHAR (255), Validdate DATETIME, Identification uniqueidentifier)

Then, I ' ve filled the table with 9024728 lines data.

Why is it the exact number? It ' s because the SQL Server Management Studio crashes after 9024728 lines ' insertion. :-)

Then, I'll use 3 methods to read the 9 millions lines data.

Method 1:get data by column index

public void Datareadergetdatabycolumnindex ()        {            using (_dbconnection)            {                var sqlCommand = new SqlCommand (_commandtext, _dbconnection);                _dbconnection.open ();                SqlDataReader reader = SqlCommand.ExecuteReader ();                var user = new Userinformationentity ();                _getbyindextime.start ();                while (reader. Read ())                {                    user. Id = reader. GetInt64 (0);                    User. FirstName = reader. GetString (1);                    User. LastName = reader. GetString (2);                    User. Validdate = reader. GetDateTime (3);                    User. Identification = reader. GetGuid (4);                }                _getbyindextime.stop ();                Console.WriteLine (String. Format ("Getbyindextime Total time:{0}", _getbyindextime.elapsed));                _dbconnection.close ();            }        }

Method 2:get data by column name

public void Datareadergetdatabycolumnname () {using (_dbconnection) {var SqlC                Ommand = new SqlCommand (_commandtext, _dbconnection);                _dbconnection.open ();                SqlDataReader reader = SqlCommand.ExecuteReader ();                var user = new Userinformationentity ();                _getbynametime.start (); while (reader. Read ()) {User.                    id = Convert.toint64 (reader["id"]); User. FirstName = reader["FirstName"].                    ToString (); User. LastName = reader["LastName"].                    ToString (); User.                    Validdate = Convert.todatetime (reader["validdate"]); User. Identification = new Guid (reader["identification").                ToString ());                } _getbynametime.stop (); Console.WriteLine (String.                Format ("Getbynametime Total time:{0}", _getbynametime.elapsed));            _dbconnection.close ();    }    } 

Method 3:get column ordinal by column name, then Get data by column ordinal

public void Datareadergetcolumnindexbycolumnnamethengetdata () {using (_dbconnection) {                var sqlCommand = new SqlCommand (_commandtext, _dbconnection);                _dbconnection.open ();                SqlDataReader reader = SqlCommand.ExecuteReader ();                var user = new Userinformationentity (); var id = reader.                GetOrdinal ("Id"); var firstName = reader.                GetOrdinal ("FirstName"); var lastName = reader.                GetOrdinal ("LastName"); var validdate = reader.                GetOrdinal ("Validdate"); var identification = reader.                GetOrdinal ("identification");                _getbynamethenindextime.start (); while (reader. Read ()) {User. Id = reader.                    GetInt64 (ID); User. FirstName = reader.                    GetString (FirstName); User. LastName = reader.                    GetString (LastName); User. Validdate = reader. GetDateTime (ValiDdate); User. Identification = reader.                GetGuid (identification);                } _getbynamethenindextime.stop (); Console.WriteLine (String.                Format ("Getbynamethenindextime Total time:{0}", _getbynamethenindextime.elapsed));            _dbconnection.close (); }        }

When I run the program to get the execution time:

You can see that Method1 and Method3 have almost the same result, and METHOD2 is about 3 times longer.

So the prefered approach would be the third.