How to populate List <String> using SqlDataAdapter?
I am trying to populate a List using SqlDataAdapter. But I cannot do this. Is there any other way to populate the list of strings besides the Data Adapter. Please help me 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);
}
source to share
The easiest way is to read your data with SqlDataReader
and then populate a list that repeats your result set. How:
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 cannot do this. Instead, you can fill DataTable
in and then get 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 . This will lead to several iterations, first for filling DataTable
and second for creatingList<string>
source to share