Alma Alma - 20 days ago 11
C# Question

Return List of Objects from stored procedure return data C#

I have a method that is calling a stored procedure and returning a list of objects. I am not sure how to add the result from the stored procedure to the list of objects. I tried to use model. Add but anyway I am using it I am getting error. I have identify the place I need help inside the code.

This is my code:

public List<Models.Type> get_Type(string Type_Group)
{
string connectionName = getConnectionStr();
List<Models.Type> model = null;

string myConnection = System.Configuration.ConfigurationManager.ConnectionStrings[connectionName].ToString();
SqlDatabase db = new SqlDatabase(myConnection);

using (DbCommand command = db.GetStoredProcCommand("LA_Get_Type"))
{
db.AddInParameter(command, "Type_Group", DbType.String, Type_Group);

var result = db.ExecuteReader(command);

try
{
if (result.FieldCount == 0)
model = null;
else
{
while (result.Read())
{
model = new List<Models.Type>()
{

//This is the place I don't know I tried model.Add but not sure what
to have after.
This code is when I have returning just 1 object but I want to
return list of objects


typeID = Convert.ToInt32(result["typeID"].ToString()),
type_group = result["type_group"].ToString(),
type_value = result["type_value"].ToString(),
type_desc = result["type_desc"].ToString(),
type_sort = Convert.ToInt32(result["type_sort"].ToString())
};
}
}
}
catch (Exception ex)
{
}
result.Close();
return model;
}
}


and this is my object:

public class Type
{

public int typeID { get; set; }
public string type_group { get; set; }
public string type_value { get; set; }
public string type_desc { get; set; }
public int type_sort { get; set; }

}

Answer

Change

                while (result.Read())
                {
                 model = new List<Models.Type>()
                    {

//This is the place I don't know I tried   model.Add but not sure what 
 to have after. 
 This code is when I have returning just 1 object but I want to 
 return list of objects                           


                        typeID = Convert.ToInt32(result["typeID"].ToString()),
                        type_group = result["type_group"].ToString(),
                        type_value = result["type_value"].ToString(),
                        type_desc = result["type_desc"].ToString(),
                        type_sort = Convert.ToInt32(result["type_sort"].ToString())


                    };
                }

to something like this:

                model = new List<Models.Type>();
                while (result.Read())
                {
                    Models.Type aModel = new Model(){
                        typeID = Convert.ToInt32(result["typeID"].ToString()),
                        type_group = result["type_group"].ToString(),
                        type_value = result["type_value"].ToString(),
                        type_desc = result["type_desc"].ToString(),
                        type_sort = Convert.ToInt32(result["type_sort"].ToString())
                    };
                    model.Add(aModel);
                }

Note that I'm creating a new object for each result and then adding them one by one to the list.