How to read stored procedure output and return it as list

Question

I have table which has composite keys in order to retrieve data from two different tables. I have created stored procedures to do that and it works fine:

Stored procedure:

ALTER PROC dbo.spp_adm_user_user_group_sel
AS 
BEGIN 
    SET NOCOUNT ON

    SELECT 
        g.name AS Group_Name, u.fullname, u.designation, 
        u.email, u.mobile 
    FROM 
        TBL_ADM_USER_GROUP g, TBL_ADM_USER u
    WHERE 
        g.id = u.group_id 
        AND (g.deleted IS NULL OR g.deleted <> 1)
END

The result is like this:

Group_name  fullname    designation  email          mobile
Alex        fffffffff   Engineer     sss@mail.come  3333333333
Jon        hhhhhhhhh    programmer   hh@mail.com    020202028347

As you can see, the stored procedure does not have any parameters. How to read this output and return it as list using C#?

Code:

public List<string> GetData()
{
    using (SqlConnection con = new SqlConnection(Global.Config.ConnStr))
    {
        string group;

        DataTable dt = new DataTable();
        List<string> details = new List<string>();

        SqlCommand cmd = new SqlCommand("spp_adm_user_user_group_sel", con);
        cmd.CommandType = CommandType.StoredProcedure;

        SqlDataAdapter da = new SqlDataAdapter(cmd);
        da.Fill(dt);

        foreach (DataRow dr in dt.Rows)
        {
            details.Add(group);
        }
    }

    return details;
}

Answer 1

Change your code to this

public List<yourClass> GetData()
{
using (SqlConnection con = new SqlConnection(Global.Config.ConnStr))
{

    DataTable dt = new DataTable();
    List<yourClass> details = new List<yourClass>();

    SqlCommand cmd = new SqlCommand("spp_adm_user_user_group_sel", con);
    cmd.CommandType = CommandType.StoredProcedure;

    SqlDataAdapter da = new SqlDataAdapter(cmd);
    da.Fill(dt);

     foreach(DataRow dr in dt.Rows)
            {
                yourClass obj = new yourClass();

                obj.fullname= dr["fullname"].ToString();
                obj.email= dr["email"].ToString();

                details.Add(obj);
            }


            return details;
        }

}

Answer 2

you can call the stored procedure and take the result into a dataset.

        SqlConnection Cn = new SqlConnection("Constr");
        SqlCommand Cmd = new SqlCommand("StoredProcName", Cn);
        SqlDataAdapter Da = new SqlDataAdapter(Cmd);
        DataSet Ds = new DataSet();
        Da.Fill(Ds, "TableName"); //Da.Fill(Ds);

Copy the rows of the data table to an array using CopyTo method of Rows collection of Data table and then convert it into list. Of course the List type will be DataRow and not any custom class.

        DataTable Dt = Ds.Tables["TableName"]; // DataTable Dt=Ds.Tables[0];
        DataRow[] Array = new DataRow[Dt.Rows.Count];
        Dt.Rows.CopyTo(Array, 0);
        List<DataRow> List=Array.ToList();            

if you want to use a custom class then create the class and convert this List of type DataRow to List of type CustomClass as follows.

        List<customclass> List1=new List<customclass>();
        foreach(var L in List)
        {
             CustomClass C=new CustomClass();
             C.Field1=L[0].ToString();
             C.Field2=int.Parse(L[1].ToString());
             .....//Assign values to all fields in the custom class 
             List1.Add(C);
        }

Answer 3

If You Got The Code To Execute Store Procedure,and

SqlDataAdapter da = new SqlDataAdapter(cmd);
        da.Fill(dt);

Code Returning Your Desired Result In Dt,Then You Can Just Try Something like That.

 foreach (DataRow dr in dt.Rows)
        {
            details.Add(dr.Field<String>("Your_Coumn_Name_In_Dt"));
        }

Or

foreach (DataRow dr in dt.Rows)
            {
                details.Add(Convert.ToString(dr[0]));// 0 is the Column Index
            }

Returning A DetailList,

Way1: create a Model Class that contains your properties and return list of that model Class.

public class Details{
     public string Group_name  {get;set;}
     public string fullname { get; set; }
     public string designation { get; set; }
     public string email{ get; set; }
     public string mobile{ get; set; }

} 

And Change Your Method.

public List<Details> GetData()
{
    using (SqlConnection con = new SqlConnection(Global.Config.ConnStr))
    {

        DataTable dt = new DataTable();
        List<Details> details = new List<string>();

        SqlCommand cmd = new SqlCommand("spp_adm_user_user_group_sel", con);
        cmd.CommandType = CommandType.StoredProcedure;

        SqlDataAdapter da = new SqlDataAdapter(cmd);
        da.Fill(dt);

        foreach (DataRow dr in dt.Rows)
        {
           Details group=new Details();

            group.Group_name  =dr.Field<string>("Group_name");
            group.fullname =dr.Field<string>("fullname");
            group.designation =dr.Field<string>("designation");
            group.email=dr.Field<string>("email");
            group.mobile=dr.Field<string>("mobile");

            details.add(group);
        }
    }

    return details;
}

Way2: If You Don't Want To Create A Model Then a bad solution will be to return a list of objects

 public List<Object> GetData() {
using (SqlConnection con = new SqlConnection(Global.Config.ConnStr))
        {

            DataTable dt = new DataTable();
            SqlCommand cmd = new SqlCommand("spp_adm_user_user_group_sel", con);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dt);


        var result = from o in dt.AsENumerable()
                     select (new
                     {
                        Group_name  =dr.Field<string>("Group_name"),
                        fullname =dr.Field<string>("fullname"),
                        designation =dr.Field<string>("designation"),
                        email=dr.Field<string>("email"),
                        mobile=dr.Field<string>("mobile")
                     } as Object);

        }

        return result.ToList();
    }