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
OSI Layers Model
IntroductionDuring the early years of our modern computer era, very few standards and protocols existed between various manufacturers. However, as time went on and computer technology continued to improve and become more widespread, it became apparent that standards would be necessary to ensure compatibility.
Device Driver Basics
Most people understand that the "hardware" part of their computer is the real physical parts, like the keyboard, mouse, modem, hard drive and so on. They understand that the "software" is computer bits stored on the hard drive, CD-ROM, or other storage media.
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 CRM Conversation Gateway: VoIP - Implementation & Customization
Microsoft CRM is winning market share step-by-step from such the traditional CRM providers as Siebel, Saleslogix. In this article we'll show Microsoft Business Solutions CRM potential in the VoIP direction.
You Can Write Microsoft Office Program in About 60 Minutes
Microsoft Office program is a programming tool called Visual Basic for Applications (VBA) that comes with Microsoft Excel, Word, Outlook, Power Point, and Publisher. VBA can make your repeating jobs automatic.
CRM and Customer Life Cycle
Customer Relationship Management or CRM is a combination of enterprise strategies, business processes and information technologies used to learn more about customers' needs and behaviors in order to develop stronger relationships with them. CRM software systems automate many customer-related business tasks.
Cisco Certification: Introduction To ISDN, Part III
Configuring PPP PAP AuthenticationNow we know how the ISDN link comes up (interesting traffic), and some scenarios that might cause the link to stay up, we need to look at ISDN authentication schemes. The two methods Cisco certification candidates must be familiar with are PAP and CHAP.
ERP System of the Future: Database, Business Logic and Interface
We will base our prognosis on our Microsoft Business Solutions ERP consulting practice: Microsoft Great Plains, Navision, Axapta, MS CRM. Also we've analyzed Oracle products: Oracle Financials, JD Edwards, PeopleSoft, and IBM Lotus Notes Domino.
Crystal Reports - Microsoft SQL Server
Microsoft SQL Server is the leader for inexpensive and middle size SQL solutions. Plus it is now capable to serve large enterprise on Microsoft Windows platform.
SyncUp - A File/Folder Synchronizer For Windows
SyncUp, a file synchronizer is designed to assist the home and advanced users. The product has a simple and familiar windows explorer type interface for easy file handling.
Dig Out That Worm
Internet worms.Is your PC infected?If your computer has become infected with a worm, don't panic, it is not the end of the world.
Navision Customization: C/SIDE, C/ODBC, C/FRONT, XBRL - Development Options
Microsoft bought Navision, Denmark based software development company, along with Great Plains Software. Now Microsoft Business Solutions offers following ERP applications: Navision (former Navision Attain), Microsoft Great Plains (former Great Plains Dynamics/eEnterprise), Solomon, Axapta.
Hubris - Definition: Microsofts Passport
Before September of 1995, Microsoft ignored the Internet because their 16-bit Windows 3.1 operating system couldn't handle the 32-bit Internet world.
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".
Software Upgrades Arent Always the Best Move
When my daughter was getting into AOL instant messaging (AIM) and using all the cool add-ons, I looked for more as it's a great way to learn about extending applications. While doing research, I learned that if you wanted to use AIM themes, you don't want to upgrade to AIM 5.
Great Plains Sales Order Processing and Invoicing Modules - Tips For Consultants
We'll give you non formal view, based on our consulting practice.Common Features.
Software Tools To Help Your Business Sell More
Sales are all about leverage, because there is only so much you can do on your own. But, with the help of these simple tools, you can easily double, triple and quadruple your productiviting and make more sales.
Microsoft Small Business Manager Customization Options - Overview
Microsoft Business Solutions Small Business Manager is Great Plains Dexterity based application and successor and small business market niche version of Microsoft Great Plains. Microsoft Great Plains itself has variety of customization and integration tools: Integration Manager, VBA/Modifier, Great Plains Dexterity, SQL Server stored procedures to name a few.
Pros and Cons of Using FREE Software in Your Business
Itâ??s easy to understand why you might be drawn to the allure of free software. After all, it is free and free is always a good price to pay.
Microsoft CRM: Data Conversion - Import from Act!
Best Software Act! is very popular CRM for small and mid-size organization. This system attracts business owner by its low price, plus system is very easy to use.