Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to fill List<String> with SqlDataAdapter?

I am trying to fill List with SqlDataAdapter. But i am not able to do it.Is there any other method to fill List of string apart from Data Adapter. Please help me out to solve this problem.Thanks in advance.

This is my code:

public List<String> JSONDataAll()
        {
            List<String> Users = new List<String>();
            con.Open();
            SqlCommand cmd = new SqlCommand("sp_sample_Proc", con);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(Users);
        }
like image 492
Vetri Avatar asked Dec 15 '22 19:12

Vetri


2 Answers

Simplest way would be read your data through SqlDataReader and then fill your list iterating your resultset. Like:

public List<String> JSONDataAll()
{
    List<String> Users = new List<String>();
    using (SqlConnection con = new SqlConnection("connectionString"))
    {
        con.Open();
        using (SqlCommand cmd = new SqlCommand("sp_sample_Proc", con))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    Users.Add(reader.GetString(0)); //Specify column index 
                }
            }
        }
    }
    return Users;
}

In your current code you are trying to fill List<string> using DataAdapter. You can't do that. Instead you can fill a DataTable and then get a List<string> like:

DataTable dt = new DataTable();
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
Users = dt.AsEnumerable()
    .Select(r => r.Field<string>("ColumnName"))
    .ToList();

But, You shouldn't do that. It would result in multiple iterations, first for filling up the DataTable and second for creating List<string>

like image 71
Habib Avatar answered Jan 01 '23 10:01

Habib


Please try this

DataTable dt = new DataTable();
da.Fill(dt);
Users = dt.AsEnumerable().Select(n => n.Field<string>(0)).ToList();
like image 45
Jawed Ahmad Avatar answered Jan 01 '23 10:01

Jawed Ahmad