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'

Answer 1

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>");
}

Answer 2

I think it would be better if you work with stored procedures in sql server... you can create one for the query

USE [DBName]
GO
/****** Object:  StoredProcedure [dbo].[GetCode]    Script Date: 03/05/2018 10:51:19 a.m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetCode]
(
@Left char
)
AS 
BEGIN
SET NOCOUNT ON;
SELECT Code FROM Employee WHERE Employee.Left in (@Left) 


END

...

Then, you can call it from c# this way:

private List<Type> GetCode(string Left)
        {
            List<Type> List = new List<Type>();

            DbConnection();
            con.Open();
            DynamicParameters Parm = new DynamicParameters();
            Parm.Add("@Left", Left, DbType.String, ParameterDirection.Input, 2);
//Parm.Add(nameOfParameter,valueOfParameter,typeOfParameter,directionOfParameter,sizeOfParameter);
            List = SqlMapper.Query<Type>(con, "Entities.GetCode", Parm, commandType: CommandType.StoredProcedure).ToList();
            con.Close();
            return List;
        }

Answer 3

I suppose that the Left values aren't exactly 'Y'. Maybe some rows have it in lower case or some spaces trailing... To cover these cases you could try:

WHERE UPPER(Left) LIKE '%Y%'

Answer 4

Use LINQ to get the data. "db" is you Database Context.

List<String> codes = db.Employee.Where(c => c.Left == "Y").Select(c => c.code).ToList();

Answer 5

These are the errors I founded.

executeReader() should be ExecuteReader()
dr1.GetValue(0) should be dr.GetValue(0)
"<CONNECTION STRING>" should be "<CONNECTION STRING>"; (';' is missing)

If you fix these errors (I'm assuming these are typing mistakes?), then your code should be working fine. I have checked your code and it's fetching data.

Please double check your connection string (since it's fetching data, we can assume it's working. but, check whether you're connected to the right database or not) & query (query also looks good. But, check the table and data again. run it on SSMS).

SqlConnection cn = new SqlConnection();
SqlCommand cmd = new SqlCommand();
cn.ConnectionString = @"Data Source=localhost; Initial Catalog=Testing; 
Integrated Security = True"; // Changed
List<string> str = new List<string>();
cmd.Connection = cn;
cmd.Connection.Open();
cmd.CommandText = "SELECT distinct column1 FROM testTable"; // Changed
SqlDataReader dr = cmd.ExecuteReader();  // Changed
while (dr.Read())
{
    str.Add(dr.GetValue(0).ToString());  // Changed
}

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

If your code is still not working as you expected, please debug your code (as already suggested in the comments).

YouTube: Debugging C# Code in Visual Studio by Programming with Mosh
Microsoft: Navigating through Code with the Debugger
Dot Net Perls: Debugging
Csharp.Net-Tutorials.com: Introduction to debugging
Michael's Coding Spot: 7 Debugging Techniques you should know in C#.NET

Answer 6

The only error that I saw was you iterate dr and then you try to get data from dr1?

Try this code:

SqlConnection cn = new SqlConnection();
            SqlCommand cmd = new SqlCommand();
            cn.ConnectionString = "ConnectionString";
            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(dr.GetValue(0).ToString());
            }

            foreach (string p in str)
            {
                Console.WriteLine(p);
            }