Using the SqlDataReader Class
(Page 1 of 6 )
If you read any of my previous ADO.NET articles you will find that I used the SqlDataReader class in many of the examples. Today we will talk more about the SqlDataReader class and use its methods and properties. Some of you may wonder how the SqlDataReader class gets access to the fields using the array-like syntax; we will talk about that as well.
If you are not familiar with C# Indexers, please consult my article for a Behind the Scenes Look at C#: Indexers.
If you are not familiar with the SqlDataReader class, you should know that it's one of the players on the ADO.NET team of data access classes. We have the SqlConnection class, which is used to create and open a connection to the database using the connection string that you provide, and we have the SqlCommand class, which is used to execute a T-SQL statement, like SELECT, INSERT, UPDATE or DELETE and stored procedures against a database using the connection opened by the SqlConnection object, and retrieve the result.
As you might know, one of the methods of the SqlCommand class is used to retrieve an object of type SqlDataReader; this is the SqlCommand.ExecuteReader() method. The SqlDataReader is what you are going to use to retrieve data -- to the client, for example, or to a business component like the ObjectDataSource control, which we are going to discuss in the next few articles along with the SqlDataSource class.
The SqlDataReader retrieves the data in a read-only forward-only mechanism. Read-only means that you can't use the SqlDataReader class to execute statements that update the data in your databases. Forward-only means that you don't have access to the previous row once you have read it; the SqlDataReader class retrieves the row, discards it, and then retrieves the next row and so on. You have access to the fields of the current row using indexers.
A column-ordinal based indexer uses the column number to retrieve the field's data, as in myDataReader[0]. And a string-based indexer uses the column name to access its field's value. Usually, you would use the string column-name based indexer because it eliminates any confusion about what field you are retrieving. You would write something like myDataReader["LastName"]. But it's faster to use the column-ordinal based indexer because the data reader will not perform any additional operations to retrieve the field's value; it will just go to the column ordinal you want to access and get the value. With the string column-name based indexer, however, as in myDataReader["LastName"], the SqlDataReader has to search for a column with that name, and then returns its value, which slows the performance a bit.
Let's see an example that uses the SqlDataReader class. Note that we are using the SQL Server .NET Data Provider for all the examples.
Next: Retrieving data from the Pubs Database >>
More ASP.NET Articles
More By Michael Youssef
