Simple DataReader in C#

Today, I need to advise the supreme novice how to peruse a worth from a SQLDataReader in C#.

On the off chance that you are an accomplished ADO.NET engineer then this article will be a finished bore for you. Be that as it may, trust it or not, there are individuals who are attempting to figure out how to function with databases in C#. So perhaps I can assist no less than one individual!

What is a SQL DataReader? DataReaders are a quick approach to force records from a database when all you need to do is essentially READ. You may have heard the expression "Firehose Cursor" used to portray a DataReader. A firehose is a decent correlation in light of the fact that the water (information) just streams one way and it streams quick. DataReaders can not be utilized to redesign information, erase information, or whatever else other than perusing. A decent case of when to utilize a DataReader would be urban areas in a state. You might need to peruse out all urban communities in New York and since they aren't precisely changing each day, you would need to force them down as quick as could reasonably be expected.

Alright, I guaranteed quick and simple so here goes.

To start with, you should instantiate (make) another database association. Presently, I am just working with Microsoft's SQL server today. In the event that you require help changing over this article to other database stages like Oracle or MySQL then please let me know.

Ensure you are additionally utilizing the required namespaces before you start.

utilizing System.Data;

utilizing 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 currently made and opened. The string that we passed is known as the "Association String". Whatever it does is advise the database how and where to open the association. Substitute "server", "Beginning Catalog", and "Client ID/Password" with your database data. Recall that, this is ONLY an open association. The database is staying there tending to a charge. What's more, that is precisely what we setup next. A summon. Think about a charge as an immediate request you give the server (despite the fact that it might possibly tune in!).

/new summon

string sql = "SELECT CustomerName FROM MyTable";

SqlCommand adoCmd = new SqlCommand(sql, adoConn);

The sql string is just a SQL order we are passing. The adoConn is advising the charge which association with use. Basic, huh?

Alright, now we have an open association and a summon (utilizing the sql string). Our best course of action is to make the DataReader and show a few information.

SqlDataReader adoDR = adoCmd.ExecuteReader();

in the event that (adoDR.HasRows)

{

while (adoDR.Read())

{

Response.Write(adoDR["CustomerName"].ToString());

}

}

The ExecuteReader() strategy sends the SQL information from the order (our SELECT articulation) and if there are records, brings them each one in turn down to the DataReader (adoDR).

You'll see that we initially called the .HasRows condition. It's generally great to first ensure there is information returned before you do anything with it. The following explanation may look a bit of confounding. This while circle cuts every record down each one in turn. It's just plain obvious, when you call the ExecuteReader and accepting there are lines, you really begin at position "- 1". Interesting, huh? For instance, suppose that SELECT explanation returned 50 lines of information. The principal record number would be 0, the following would be 1, then so on until record 49. 0-49 records. Everytime you call the .Read() on the DataReader, you propel a record. Along these lines, in the event that you began at - 1 and propelled a record you would be toward the starting. Record 0. Calling .Read() will keep on returning TRUE until you achieve the last record. So as should be obvious, this makes it advantageous to go through all records. Additionally I ought to say you HAVE to call it at any rate once to progress to the main record.

The Response.Write charge just sends the information to the site page. This could have been Console.WriteLine, and so forth. See how the "CustomerName" was utilized. Be watchful here on the grounds that you need to ensure you don't attempt to call a field in a table that you didn't SELECT.

Alright, the exact opposite thing to do is close associations and arrange with the goal that we don't make memory spills on the server.

adoDR.Close();

adoDR.Dispose();

adoCmd.Dispose();

adoConn.Close();

adoConn.Dispose();

Seen I turned around the request that I utilized while making the articles. DataReaders are opened when you call the ExecuteReader() and when you open something, you ought to close it. Calling .Dispose() on these articles would likewise close them however shutting them myself has dependably been a habbit of mine. Charge items aren't opened or shut so no Close() is required. Lastly we close/discard the database association.

There. Was that so hard? We made a database association, opened it, made a charge (utilizing a custom SQL question) and executed the DataReader. At that point, we circled through the records. At long last, we shut and discarded all the items.

There you have it. Basic. ADO.NET has made it truly simple to show information. This is only a little scratch on the Titanic. ADO.NET could fill 50,000 pages!

I trust you appreciated this article. I need to concede, I'm not a lot of an author but rather I recollect the first occasion when I pulled information from a database and I wished I had somebody letting me know in plain English how to come to the heart of the matter.

Clearly, we didn't cover different themes like blunder catching, DataGrids, DataSets, and so on. Those will come in time!