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!
Author Bio
cbmeeks is the owner of the new website:
www.codershangout.com
Article Source: http://www.ArticleGeek.com - Free Website Content
More Resources
Unable to open RSS Feed $XMLfilename with error HTTP ERROR: 404, exitingMore Software Information:
- Group 1
- Group 2
- Group 3
- Group 4
- Group 5
- Group 6
- Group 7
- Group 8
- Group 9
- Group 10
- Group 11
- Group 12
- Group 13
- Group 14
- Group 15
- Group 16
- Group 17
- Group 18
- Group 19
- Group 20
- Group 21
- Group 22
- Group 23
- Group 24
- Group 25
- Group 26
- Group 27
- Group 28
- Group 29
- Group 30
- Group 31
- Group 32
- Group 33
- Group 34
- Group 35
- Group 36
- Group 37
- Group 38
- Group 39
- Group 40
- Group 41
- Group 42
- Group 43
Related Articles
Simple Steps - Protect your Computer Online
So you've bought your computer and want to get online? It's not as simple as connect the wire and off you go these day's. The internet is a weird and wonderful place as long as you can control what you do.
Practical Use of Open Source Code Software
As we started the project of our web site, we knew that the proprietary software costs would be too high for our financial resources. Our only option then was to make use of Open Source Code softwares.
So You Think You Know CRM Software?
A year ago if someone asked me if I knew my way around the CRM Software Industry I would have confidently said yes, however, as it turns out the old saying the more you learn the more knowledge you realize you lack, is true. When talking about the CRM Software Industry it is almost impossible to actually say that you know it inside out since there are so many CRM vendors around the world, all developing their technologies at such a rapid pace. As if it wasn't hard enough for a company to make a decision regarding; what they require, how it can help their business and so on, these technological advancements are always followed with a marketing campaign each speaking of how much this new feature or functionality will help you.
Manage School Operations With School Administration Solution Software
We aim to create Digital Schools with the help of Tablet PC based Digital Register by using foremost technologies. Our School Management Solution helps in complete Digitization of Fees, Library, Admission, Transport, HR, and Payroll Management System of school at industry leading price.
Make All The Schools Activities Accurate With School Management And Administration Software
We have designed Online Student Information Management System to Manage Student Information, and Monitor Student's Performance there by showing overall progress to Parents, Teachers and School.
Why Should You Create A Questionnaire Survey?
The questionnaire survey is a well known term for the marketing professionals. If you want to create a survey questionnaire but don't know the process, then this article can help you.
Using Real Estate CRM to Convert Leads into Clients
As you know, not all leads are prepared to buy or offer right away. If you are failing to remember to keep in contact with these leads, then in several weeks, four several weeks, or one season, when they are serious about dealing with an broker, it probably will not be you that they will seek the services of.
Reasons Why You Should Employ Field Service Management Software
With the growth of the company, it becomes necessary for the company to enhance its services in order to minimize errors. The best way to do it is taking the help of field agents to reach the client and customer through sales and supportive services.
It's raining Beta versions for iOS 7: What's there for developers?
The iO7 might just be the most anticipated release ever from the stables of Apple. The anticipation has been further augmented with Apple's release of Beta versions at periodic intervals.
Can hosted PBX services for customer management system come true?
PBX systems allow you to configure extensions, customer management systems, auto-attendants, call redirects and more to route calls within your organization. These operations are handled through a private branch exchange server (PBX).
How To Fix Checkers.Dll Error—What Is The Best Way To Fix Checkers.Dll Error
Have you seen checkers.dll error messages popping up on your computer screen? Whether you have tried many ways but it is failed to remove this error successfully? Do you want to fix it without spending lots of money and energy? Thankfully, in this article, you can find the efficient and safe way to repair your computer.
Advantages Of ASP.NET Web Development
ASP.NET is a Microsoft technology and is a framework for developing dynamic web pages, web applications and web services.
Avoid outlook problems in the easiest way
You can follow the above set of guidelines to avoid many problems while using Outlook.
Keep a Close Security Check with-Best Surveillance Software
Buy blue iris professional surveillance software and keep an eye on your home, place of business and cars from anywhere in the world. Receive alerts via loudspeaker, e-mail, instant messaging or phone.
Use QR Code To Experience The Best Online Marketing
QR code is playing a significant role as the best online marketing campaign. It is known to be an effective tool for setting the marketing strategies. Use QR code today to experience the best online marketing.