Simple DataReader in C#

If you are an experienced ADO.NET developer then this article will be a complete bore for you. But, believe it or not, there are people who are trying to learn to work with databases in C #.

What is a SQL Data Reader? Data Reader is a quick way to pull records from a database where all you want to do is simply READ. You may have heard the term "Fire Hose Cursor" used to describe a Data Reader. A four hose is a good comparison, because the water (data) only flows one way and it runs fast. Data Readers may not be used to update data, delete data, or something different to read. A good example of when to use a Data Reader would be cities within a state. You may want to read all cities in New York, and since they are not completely changing every day, would you want to drag them down as quickly as possible.

First, you instantiate (create) a new database connection. Now we only work with Microsoft SQL Server today. If you need help to convert this article to other database platforms like Oracle or MySQL then let us know.

Make sure you also use the necessary namespaces before you begin.

using System.Data;

using System.Data.SqlClient;

AdoConn SqlConnection = new SqlConnection ( "Data Source = server; Initial Catalog = database; Persist Security Info = True; User ID = username, Password = password");

adoConn.Open ();

The database is now created and opened. The string that we were called the "Connection String". All it does is tell the database how and where to open the connection. Substitute "server" "Initial Catalog" and "User ID / password" with your database information. Remember, this is only one open connection. The database is sitting there waiting for a command. And that is exactly what we setup next. A command. Think of a command as a direct order you give the server (although it may or may not hear!).

/ / New command

string sql = "SELECT Customer Name FROM MyTable";

AdoCmd SqlCommand = new SqlCommand (sql, adoConn);

SQL string is simply a SQL command, we pass. The adoConn tells the command which connection to use. Simple, huh?

Ok, now we have an open connection and a command (using the SQL string). Our next step is to create a Data Reader and show some data.

SqlDataReader adoDR = adoCmd.ExecuteReader ();

if (adoDR.HasRows)

(

while (adoDR.Read ())

(

Response.Write (adoDR [ "Customer Name"]. ToString ());

)

)

The ExecuteReader () method sends the SQL data from the command (our SELECT statement) and if there are items that brings them one at a time down to Data Reader (adoDR).

You will discover that we first called it. HasRows mode. It's always good to first make sure that the returned data before you do anything with it. The next sentence may look a little confusing. This while loop brings each item down one by one. See, when you call ExecuteReader and assuming there are lines you actually start at position "-1". Strange, huh? For example, let us say that the SELECT statement returns 50 rows of data. The first record number would be 0, the next would be 1, then so on until record 49th 0-49 items. Each time you call. Read () on the Data Reader, you advance a record. So if you started at -1 and advanced a record you would be in the beginning. Record 0 Dial. Read () will continue to return TRUE until you reach the last record. So as you can see, it makes it easy to browse through all entries. We should also mention you have to call it at least once to go to the first mailing.

The Response.Write command simply sends data to the Web page. This could have been Console.WriteLine, etc. Notice how the "Customer Name" was used. Be careful here because you want to be sure that you do not try to call a field in a table that you did not choose.

Ok, the last thing to do is close relationship and disposed of, so we do not create memory leaks on the server.

adoDR.Close ();

adoDR.Dispose ();

adoCmd.Dispose ();

adoConn.Close ();

adoConn.Dispose ();

Felt we reversed the order in which we used when creating objects. Data Readers opens when you call the ExecuteReader (), and when you open something, you should close it. Dial. Dispose () on these objects will also close them, but closing them has always been a habbit of mine. Command objects are not opened or closed, so no Close () is necessary. And finally, we close / dispose of the connection to the database.

There. Was that so hard? We have created a database connection, opened it, set up a command (using a custom SQL query) and executed the Data Reader. So we looped through the records. Finally, we closed and disposed all objects.

There you have it. Simple. ADO.NET made it really easy to display data. This is just a tiny scratch on the Titanic. ADO.NET could fill 50,000 pages!

We hope you enjoyed this article. Of course we do not cover other issues such as error trapping DataGrids, data sets, etc. These will get started!