Retrieving Individual record using data Reader

Question

0.00/5 (No votes)

See more:

Hello everyone, i am having this issue, i have a table in ms sql server database called customer,

and i am doing a select statement like: select top 5 customername, customeraddress, customerPhone, dateOfPurchase

from customer order by dateOfPurchase ASC in a stored procedure. and in my C# code i am using

datareader to retrieve the records.

C#

while (rdr.Read())
            {
                customername = rdr["customername"].ToString();
                customeraddress = rdr["customeraddress"].ToString();
                customerPhone = rdr["customerPhone"].ToString();
                dateOfPurchase = rdr["dateOfPurchase"].ToString();
            }

the above code is returning only the last record, i want to be able to store each of the customer record

in a variable. i.e.

C#

while (rdr.Read())
            {

//customer#1

string customer_1_Name= rdr["customername"].ToString();
string customer_1_customeraddress= rdr["customeraddress"].ToString();

string customer_1_customerPhone=rdr["customerPhone"].ToString();

string customer_1_dateOfPurchase = rdr["dateOfPurchase"].ToString();

//customer#2

string customer_2_Name= rdr["customername"].ToString();
string customer_2_customeraddress= rdr["customeraddress"].ToString();

string customer_2_customerPhone=rdr["customerPhone"].ToString();

string customer_2_dateOfPurchase = rdr["dateOfPurchase"].ToString();

//etc

}

Please assist, thanks in advance...

Posted 31-Mar-14 2:18am

Uwakpeter

Add a Solution

3 solutions

Solution 2

1. Create a class called Customer:

C#

public class Customer
{
    public string name { get; set; }
    // add more properties like address, phone,...
}

2. At each loop of datareader, instantiate a Customer class and assign the data to the appropriate property, and add each instance into a list for later manipulation.

XML

List<Customer> customers = new List<Customer>();

// you database operation here...

while(rdr.read())
{
     Customer c = new Customer();
     c.name = rdr["customername"].ToString();
     customers.add(c);
}

// other code

The End.

Posted 31-Mar-14 2:45am

Peter Leow

Updated 31-Mar-14 2:47am

v3

Comments

Uwakpeter 31-Mar-14 8:58am

Then how will i know c.name is retrieving customer#1 name?

Peter Leow 31-Mar-14 9:05am

Once you have added all customers as Customer object into the customers list, you can find a particular customer by looping thru this list and look up some property say name, refer: http://www.dotnetperls.com/list

Solution 1

Hi ,

Instead of reading by specific column name ,
Iterate the data reader using forloop..

C#

int count = reader.FieldCount;
while(reader.Read()) {
    for(int i = 0 ; i < count ; i++) {
        Console.WriteLine(reader.GetValue(i));
    }
}

Posted 31-Mar-14 2:23am

Ashok8033

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

King Fisher · Accepted Answer · 2014-03-31T03:00:00

use list

store the data's to list:

C#

using System.Collections.Generic;

List<string> customername = new List<string>(); //globally
List<int> cusomerid= new List<int>();           //globally
List<string> customeraddr= new List<string>();  //globally
//etc..
while (dr.Read())
 {
cusomerid.Add(dr["cusromerid"].ToString());
customername.Add(dr["customername"].ToString());
customeraddr.Add(dr["address"].ToString());
//etc.
}

get the records from list:

C#

string customerid_1=cusomerid[0].ToString();
string customerid_2=cusomerid[1].ToString();

string customename_1=customername[0].ToString();
string customername_2=customername[1].ToString();

List in C#