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
Can Predictive Dialing Make Telemarketing at Next Level?
The best way for a business to increase your sales is through telemarketing. With telemarketing campaigns, a business can reach millions of potential customers who are using the phone. However, the biggest obstacle for the company is the creation of a call center, where activities can be performed effectively.
Importance of VPN Reviews
VPN acronyms for virtual private network, it is form of private network which functions on already existing public infrastructure through following tunneling protocols in order to a secured tunnel.
Field Service Management Software Would Help Your Business Grow Substantially
Business operation refers to the activities that are involved in the day-to-day functions of the business that is conducted for the sole purpose of generating profits.
Best Way to Integrate Your Online Business with Magento E-Commerce Platform
Magneto is a most advanced features attracts on-line stores merchant across the world. If you are thinking to launch your shopping site on line then magento is the perfect e-commerce solution for your business. Magento development basically recommended for medium and large business.
Expert Ways of Hiring Mobile Development Company
There is a widespread need in the present day corporate houses to opt for illustrative and problem solving mobile apps. The same has given rise to businesses outsourcing the development task to various offshore centers.
What are the Benefits of Independent Software Testing Services
Software testing process is an analysis performed to check the quality of products or services as well as to provide all valuable information to the stakeholders.
The Primary Things To Be Considered In A Mobile Application Development
Not so long ago, PC was one of the greatest achievements in the history of humankind. People find it as a quite useful device. It was adopted in offices by businesses and in homes by individual users
Manage Your Business Through Top-Notch Android POS Apps And Software
The new breakthrough in the POS industry is the tablet point of sale systems. Tablets, especially tech giant Appleās iPads have evolved the traditional point of sale systems.
Avast Antivirus Technical Support Is Extremely Convenient
Avast technical support is a convenient way to get your PC repaired as getting in touch with the technicians just requires you to give them a phone call. Once you do that, they will ask you for your permission to gain remote access to your PC so that the diagnosis and the resolution of the problem can be done suitably.
Iphone Application Development — Simplifying Different Aspects Of Life
Over the past few decades, mobile phones have become an essential part of people's life. It would not be wrong if we call a mobile phone similar to portable computers as they well-equipped with number of additional functions and range of applications.
How Predictive Dialer helpful in Call Centers and how IVR Invention in Telecommunication Industry?
Predictive Dialer is a very useful device for many call centers, and makes outgoing calls automatically and eliminates the need for manual dialing, currently being used frequently in many business purposes.
Backup software For Your Windows Computer
Backing up your files is probably the most important thing to do regularly, regardless of whether you are running a small office or just keeping track of your personal stuff. Having lost all my data in my Windows XP computer, I know the pain of losing all my documents and photos.
Bookmark Managers: Programs vs. Web Services
The rapid development of the World Wide Web in recent years has led to an explosive growth of information on the Internet. Our contemporary lifestyle would be unimaginable without access to such a super-abundant cornucopia of valuable information and web surfing has now become an everyday occupation for even the most diverse sections of society.
Tips on Understanding the Dangers of Spyware and Adware
Money is the very crux of adware and spyware. These pose several problems and are a security risk.
Annoying Spyware And Its Elimination
Nowadays, you might have noticed of a threatening word "Spyware". Spyware software is becoming annoyance for the companies. Spyware is likely to be verified as a great damager to the companies. It is not so noticeable like spam and virus assaults to the systems. Spyware could be destructive and cause financial losses.