How to Store list of data from Database to ArrayList or List in C#

Question

Table: employee

code|Name|Left
----+----+----
1   | A  | Y
2   | B  | N
3   | C  | N
4   | D  | Y
5   | E  | N
6   | F  | Y

now i am doing ,

        SqlConnection cn=new SqlConnection();
        SqlCommand cmd = new SqlCommand();
        cn.ConnectionString="<CONNECTION STRING>"
        List<string> str = new List<string>();
        cmd.Connection=cn;
        cmd.Connection.Open();
        cmd.CommandText="Select code from employee where Left='Y'";
        SqlDataReader dr=cmd.executeReader();
        while(dr.Read())
        {
                       str.Add(dr1.GetValue(0).ToString());

        }

        foreach (string p in str)
        {
                 Response.Write(p);
        }

This code fetch only 1 data, how may i get all data whose Left='Y'

Solution

There are several basic flaws, but as it also sounds like you've successfully retrieved at least one record or column, I'll treat those as simple typos.

With that in mind, four changes are still needed:

1. Use a type other than string for the collection, that has a place for each attribute you need.
2. Ask for more than one column in the SQL string's SELECT clause
3. Save all of the attributes from the query results with the collection
4. Write all of the fields to the Response

There are some other changes I will demonstrate here as well, to help show some better practices.

//Change #1 -- new type with space for each attribute
public class Employee
{
    public string Name {get;set;}
    public string code {get;set;}
    public bool Left {get;set;}
}

// ...

public IEnumerable<Employee> GetEmployees(bool Left)
{
    //Change #2 -- ask for other fields in the SQL select clause
    string sql = "SELECT code, Name, Left FROM employee WHERE Left= @Left";

    using (var cn = new SqlConnection("<CONNECTION STRING>"))
    using (var cmd = new SqlCommand(sql, cn))
    {
        cmd.Parameters.Add("@Left", SqlDbType.Char, 1).Value = Left?"Y":"N";
        cn.Open();

        using (var rdr = cmd.ExecuteReader())
        {
            while(rdr.Read())
            {
                //Change #3 -- use all fields from the query results 
                yield return new Employee() {
                    Name = rdr["Name"], 
                    code = rdr["code"], 
                    Left = (rdr["Left"] == "Y")?true:false
                };
            }
            rdr.Close();
        }
    }
}

// ...

var employees = GetEmployees(true);
foreach (var e in employees)
{ 
    //Change #4 -- Write all fields to the response.
    Response.Write($"<span>{e.Name}</span><span>{e.code}</span><span>{e.Left}</span>");
}

Comments

[Brad]

you are adding a string from dr1 but your datareader is just dr??

[Gulshan Sonwar]

yeah , that's my mistake, there will be dr instead of dr1

[Brad]

That was a typo and your still getting the error? If you debug and step through is it looping multiple times for your str.Add? When you debug are all the values appearing in the dr.Read when looping? Just the str array is not being populateed correctly?

[Gulshan Sonwar]

well i supposed to get all code in that list and show all code through foreach loop but i am wrong somewhere, it just shows 1 data

[Brad]

Your code looks right, that is why I suggested you debug and step through it line by line (also fix the error in your post I mentioned)

[Dour High Arch]

Please read How To Debug.

[edixon]

You only chnaged dr1 to dr? That has already been noticed and fixed in the first comments.

[Juan Luis Olguin Lopez]

I also wrote ";" at the end of the connection string and I changed executeReader() for ExecuteReader()

[edixon]

These are typo errors that cause compilation errors. This does not answer the question why only the first row is returned?

[Juan Luis Olguin Lopez]

Follow this code and you will get the list with all the rows.