 |
| |
 |
| |
 |
| |
 |
| |
 |
| |
 |
| |
 |
| |
 |
| |
 |
| |
 |
| |
 |
| |
 |
| |
 |
| |
 |
Simple DataReader in C
Hello.
Today, I want to tell the absolute beginner how to read a value from a SQLDataReader
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 how to work with
databases in C#. So maybe I can help out at least one person!
What is an SQL DataReader? DataReaders are a fast way to pull records from a
database when all you want to do is simply READ. You may have heard the term
"Firehose Cursor" used to describe a DataReader. A firehose is a good comparison
because the water (data) only flows one way and it flows fast. DataReaders can not be
used to update data, delete data, or anything else other than reading. A good example
of when to use a DataReader would be cities in a state. You may want to read out all
cities in New York and since they aren't exactly changing every day, you would want to
pull them down as fast as possible.
Ok, I promised fast and easy so here goes.
First, you must instantiate (create) a new database connection. Now, I am only working
with Microsoft's SQL server today. If you need help converting this article to other
database platforms like Oracle or MySQL then please let me know.
Make sure you are also using the needed namespaces before you begin.
using System.Data;
using System.Data.SqlClient;
SqlConnection adoConn = new SqlConnection("Data Source=server;Initial
Catalog=database;Persist Security Info=True;User ID=username;Password=password");
adoConn.Open();
Database is now created and opened. The string that we passed is 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 an open connection. The database is
sitting there waiting on a command. And that is exactly what we setup next. A command.
Think of a command as a direct order you give the server (even though it may or may
not listen!).
// new command
string sql = "SELECT CustomerName FROM MyTable";
SqlCommand adoCmd = new SqlCommand(sql, adoConn);
The sql string is simply a SQL command we are passing. The adoConn is telling the
command which connection to use. Simple, huh?
Ok, now we have an open connection and a command (using the sql string). Our next
move is to create the DataReader and display some data.
SqlDataReader adoDR = adoCmd.ExecuteReader();
if (adoDR.HasRows)
{
while (adoDR.Read())
{
Response.Write(adoDR["CustomerName"].ToString());
}
}
The ExecuteReader() method sends the SQL data from the command (our SELECT
statement) and if there are records, brings them one at a time down to the DataReader
(adoDR).
You'll notice that we first called the .HasRows condition. It's always good to first make
sure there is data returned before you do anything with it. The next statement might
look a little confusing. This while loop brings each record down one at a time. See, when
you call the ExecuteReader and assuming there are rows, you actually start at position
"-1". Strange, huh? For example, let's say that SELECT statement returned 50 rows of
data. The first record number would be 0, the next would be 1, then so on until record
49. 0-49 records. Everytime you call the .Read() on the DataReader, you advance a
record. So, if you started at -1 and advanced a record you would be at the beginning.
Record 0. Calling .Read() will continue to return TRUE until you reach the last record.
So as you can see, this makes it convenient to cycle through all records. Also I should
mention you HAVE to call it at least once to advance to the first record.
The Response.Write command simply sends the data to the web page. This could have
been Console.WriteLine, etc. Notice how the "CustomerName" was used. Be careful
here because you want to make sure you don't try to call a field in a table that you didn't
SELECT.
Ok, the last thing to do is close connections and dispose so that we don't create
memory leaks on the server.
adoDR.Close();
adoDR.Dispose();
adoCmd.Dispose();
adoConn.Close();
adoConn.Dispose();
Noticed I reversed the order that I used when creating the objects. DataReaders are
opened when you call the ExecuteReader() and when you open something, you should
close it. Calling .Dispose() on these objects would also close them but closing them
myself has always been a habbit of mine. Command objects aren't opened or closed so
no Close() is needed. And finally we close/dispose of the database connection.
There. Was that so hard? We created a database connection, opened it, created a
command (using a custom SQL query) and executed the DataReader. Then, we looped
through the records. Finally, we closed and disposed of all the objects.
There you have it. Simple. ADO.NET has made it really easy to display data. This is just
a tiny scratch on the Titanic. ADO.NET could fill 50,000 pages!
I hope you enjoyed this article. I have to admit, I'm not much of a writer but I remember
the first time I pulled data from a database and I wished I had someone telling me in
plain English how to get right to the point.
Obviously, we didn't cover other topics like error trapping, DataGrids, DataSets, etc.
Those will come in time!
Hello.
Today, I want to tell the absolute beginner how to read a value from a SQLDataReader
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 how to work with
databases in C#. So maybe I can help out at least one person!
What is an SQL DataReader? DataReaders are a fast way to pull records from a
database when all you want to do is simply READ. You may have heard the term
"Firehose Cursor" used to describe a DataReader. A firehose is a good comparison
because the water (data) only flows one way and it flows fast. DataReaders can not be
used to update data, delete data, or anything else other than reading. A good example
of when to use a DataReader would be cities in a state. You may want to read out all
cities in New York and since they aren't exactly changing every day, you would want to
pull them down as fast as possible.
Ok, I promised fast and easy so here goes.
First, you must instantiate (create) a new database connection. Now, I am only working
with Microsoft's SQL server today. If you need help converting this article to other
database platforms like Oracle or MySQL then please let me know.
Make sure you are also using the needed namespaces before you begin.
using System.Data;
using System.Data.SqlClient;
SqlConnection adoConn = new SqlConnection("Data Source=server;Initial
Catalog=database;Persist Security Info=True;User ID=username;Password=password");
adoConn.Open();
Database is now created and opened. The string that we passed is 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 an open connection. The database is
sitting there waiting on a command. And that is exactly what we setup next. A command.
Think of a command as a direct order you give the server (even though it may or may
not listen!).
// new command
string sql = "SELECT CustomerName FROM MyTable";
SqlCommand adoCmd = new SqlCommand(sql, adoConn);
The sql string is simply a SQL command we are passing. The adoConn is telling the
command which connection to use. Simple, huh?
Ok, now we have an open connection and a command (using the sql string). Our next
move is to create the DataReader and display some data.
SqlDataReader adoDR = adoCmd.ExecuteReader();
if (adoDR.HasRows)
{
while (adoDR.Read())
{
Response.Write(adoDR["CustomerName"].ToString());
}
}
The ExecuteReader() method sends the SQL data from the command (our SELECT
statement) and if there are records, brings them one at a time down to the DataReader
(adoDR).
You'll notice that we first called the .HasRows condition. It's always good to first make
sure there is data returned before you do anything with it. The next statement might
look a little confusing. This while loop brings each record down one at a time. See, when
you call the ExecuteReader and assuming there are rows, you actually start at position
"-1". Strange, huh? For example, let's say that SELECT statement returned 50 rows of
data. The first record number would be 0, the next would be 1, then so on until record
49. 0-49 records. Everytime you call the .Read() on the DataReader, you advance a
record. So, if you started at -1 and advanced a record you would be at the beginning.
Record 0. Calling .Read() will continue to return TRUE until you reach the last record.
So as you can see, this makes it convenient to cycle through all records. Also I should
mention you HAVE to call it at least once to advance to the first record.
The Response.Write command simply sends the data to the web page. This could have
been Console.WriteLine, etc. Notice how the "CustomerName" was used. Be careful
here because you want to make sure you don't try to call a field in a table that you didn't
SELECT.
Ok, the last thing to do is close connections and dispose so that we don't create
memory leaks on the server.
adoDR.Close();
adoDR.Dispose();
adoCmd.Dispose();
adoConn.Close();
adoConn.Dispose();
Noticed I reversed the order that I used when creating the objects. DataReaders are
opened when you call the ExecuteReader() and when you open something, you should
close it. Calling .Dispose() on these objects would also close them but closing them
myself has always been a habbit of mine. Command objects aren't opened or closed so
no Close() is needed. And finally we close/dispose of the database connection.
There. Was that so hard? We created a database connection, opened it, created a
command (using a custom SQL query) and executed the DataReader. Then, we looped
through the records. Finally, we closed and disposed of all the objects.
There you have it. Simple. ADO.NET has made it really easy to display data. This is just
a tiny scratch on the Titanic. ADO.NET could fill 50,000 pages!
I hope you enjoyed this article. I have to admit, I'm not much of a writer but I remember
the first time I pulled data from a database and I wished I had someone telling me in
plain English how to get right to the point.
Obviously, we didn't cover other topics like error trapping, DataGrids, DataSets, etc.
Those will come in time!
FREE Estimates on all PC and Laptop Computer Repairs + FREE Collection and Return*