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, exiting
More 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
How to Get The Best Accounting Software For Your Small Business
Buying accounting software is a major investment. It's an important decision and you need to be sure of all the facts before you buy.
Top Questions to Ask Before Buying a Help Desk & Asset Management Software
#5 All your hardware components like Printers, PCs etc come under various maintenance contracts with different maintenance vendors. Do you know which batch of PCs are under warranty & for what number of PCs is the warranty about to expire (so that you can plan for an AMC)? Can you split up & tell how much AMC charges do you pay for each hardware component & how much for each vendor? Can you re-calculate the costs if an AMC with a vendor is to be renewed?#4 Typically your company gets hardware parts and accessories from different vendors.
ERP Remote Support: Microsoft Great Plains Analysis - Pluses & Minuses
Former Great Plains Software Dynamics/eEnterprise and currently Microsoft Business Solutions Great Plains serves midsize and corporate clients as ERP system in the following countries and regions: USA, Canada, Mexico and Latin America, Brazil (where MBS actually promotes Navision and has GP for multinational corporations), Saudi Arabia, OAE, Egypt and the rest of Middle East, South Africa, Nigeria and the whole African continent, U.K.
Lotus Notes Domino and Web: Application Development - Tips for Programmers
Beginning with Domino version R4 it has integration with the Web, and the server itself becomes a HTTP-server. The next releases of Domino Web-server add new functionality (for example servlet managers, supported JVM versions upgrade, etc).
Microsoft Navision Customization and Reporting - Tips For Programmer/IT Specialist
C/SIDE (Client/Server Integrated Development Environment) - The core of Navision is the C/SIDE. C/SIDE is the foundation for all the business management functionality of Navision.
Corporate ERP: Microsoft Great Plains For The Plant In Brazil - Overview For CIO
Microsoft Business Solutions Great Plains as new ERP for multinational corporation manufacturing facility in Brazil.If your company plans to outsource manufacturing in Brazil by purchasing existing facility there, the first step would be to implant new management team and revise business processes and procedures.
Protect Your Computer...and Your Business!
We all take the computer for granted. I mean, all we have to do is switch it on and it's ready to go.
Does Microsoft have any real competition?
Does Microsoft Have any Real Competition? Copyright (c) 2003 Gregory S. Diehl In a word, yes.
RSS: Get Notified When Your Favorite Websites Are Updated
RSS (Really Simple Syndication) is a way for a site to publish it's updates. RSS readers can read these notifications and display a listing of the updates to you, the user.
Beware of The Pirated Software E-Mail Scams!
You have gotten those E_Mails buy software at deep discounts. It is very tempting to save 85% to 95% on your favorite Software Titles.
Corporate Accounting System: Microsoft Great Plains - Overview
Corporate ERP/MRP selection might be tough one, especially considering very turbulent ERP world and fierce competition between Microsoft Business Solutions (Great Plains, Navision, Axapta, MS CRM), Oracle (Oracle Financials, PeopleSoft, JD Edwards), IBM (including Lotus Domino), SAP, Microsiga (Brazil, Portugal). We recommend you, IT director of large corporation, make your home work and decide on Microsoft or Java/EJB/J2EE platform (however you should consider vendor dependence minimization, and stake on both with XML streams integration between the two).
Microsoft Great Plains Implementation for Large Corporation - Overview for VP IT
Microsoft Business Solutions Great Plains was historically designed to serve mid-market first and then with addition and acquisition of new modules - Great Plains Dynamics architects planned to enter into corporate market. When Microsoft SQL Server took its place as relatively reliable and stable database platform, Great Plains gained scalability.
Free Software for Newbies and Web Developers
Here is some free software tools to help you build your own websites and create web products.Learning new skills is not difficult.
10 Things You Can Do With Photoshop CS2 That You Couldnt Do Before Now
Is Photoshop CS2 worth the upgrade? You bet it is! Here are just 10 things you can do with this fantastic new version of Photoshop that were impossible (or difficult) to do with previous versions:1. Manipulate objects in the Adobe BridgeThis new generation of file browser enables you to look at photographs and artwork in detail before you open the file, process multiple Camera Raw images, flip through pages in PDF documents, size, rate and organise your work in a convenient and highly user-friendly way.
Great Plains Dexterity - Microsoft Great Plains Customization Overview
Microsoft Business Solutions Great Plains, former Great Plains Software Dynamics and eEnterprise are Dexterity-written applications. Also small business line: Microsoft Small Business Manager or Small Business Financials is written in Dexterity and uses the same code base as Great Plains.
Navision Sales Module & Reporting: Jet Reports, C/ODBC, XBRL, Business Analytics - highlights
Microsoft bought Navision, Denmark based software development company, along with Great Plains Software. Now Microsoft Business Solutions offers the following ERP applications: Navision (former Navision Attain), Microsoft Great Plains (former Great Plains Dynamics/eEnterprise), Solomon, Axapta.
Crystal Reports for Microsoft Great Plains - Overview for Developer
Microsoft Great Plains is main accounting / ERP application from Microsoft Business Solutions, targeted to the US market. It serves the whole of vertical and horizontal market: most of the industries and company sizes.
What is Fleet Maintenance Software?
Simply put, fleet maintenance allows companies to monitor and maintain their fleets of commercial vehicles more efficiently.Fleet maintenance requires a great deal of organization and management skills.
Basic Tips and tricks for Windows XP
Running Applications in Compatibility ModeWith Windows XP, you can run programs as if though they were being run under a different operating system. (This is known as "emulation".
What is Shareware?
Shareware is software that you can try before you buy; shareware is a kind of marketing method for software. Software developers post trial versions of their software on websites.