CODEWITHSUNDEEP
c#
Nehemiah Cheburet
Nehemiah Cheburet

Reputation: 27

Returning a list from database by making a request using stored procedure

I want to return a list of data from database, I pass params to a stored procedure, it then returns results to a list

List<DataDetail> FetchData(GenRequest Request)
{
    List<DataDetail> details = new List<DataDetail>();

    using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
    {
        // con.Open();
        DataTable dt = new DataTable();

        SqlCommand cmd = new SqlCommand("sp_Gen", con); 

        con.Open();

        cmd.Parameters.AddWithValue("@ForcedATMAmt", ForcedATMAmt);
        cmd.Parameters.AddWithValue("@OthersValue", OthersValue);

        con.Close();

        cmd.ExecuteReader();

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

        foreach (DataRow dr in dt.Rows)
        {
            DataDetailobj = new DataDetail();

            obj.AdditionalPremium = dr["AdditionalPremium"].ToString();
            obj.ATMLimit = dr["ATMLimit"].ToString();

            details.Add(obj);
        }
    }

    return details
}

I tried the approach in the code above but when I hover over dr, the dr.HasRows is false. any way I can return the data to a list without DataRows?

Upvotes: 0

Views: 106

Answers (1)

Oak
Oak

Reputation: 46

Generally speaking, when working with a database connection your order of operations is:

  1. Open the database connection
  2. Run your queries/commands
  3. Close the database connection

In your case, it should go something like this:

    con.Open()
    SqlCommand cmd = new SqlCommand("sp_Gen", con);
    // add parameters
    SqlDataAdapter da = new SqlDataAdapter(cmd);​
    da.Fill(dt);
    // run your code to get values from da
    con.Close()

As a side note, I don't believe you need to call cmd.ExecuteReader(); yourself, but that SqlDataAdapter.Fill() will execute the command for you.

Upvotes: 1

Related Questions