Executereader and execute non-query in a single connection in ado.net

The below snippet employs multiple queries inside the same connection string. first i'm selecting the some datas from the DB then before closing the datareader(dr) i would like to do some validations so if satisfies i ll insert those value into the table in the DB within the while loop of dr. There i get the error as there's already an open datareader so i cannot use another query before closing it. so i ve temporarily used another connection string pointing to the same database and i do the insert qry using that connection object(conn1). but wat is the other way to do this without using another connection string. Any help ll be greatly appreciated. Thx.

public XmlDocument LBS_Offer_Scheduler(string dev_token, float lat, float llong)
    {
        XmlDocument xml_doc = null;
        bool result = false;
        int row_affect = 0;
        int x = (int)Math.Truncate(lat);
        int y开发者_JAVA技巧 = (int)Math.Truncate(llong);

        string qry = "select Store_id,Store_Latitude,Store_Longitude from tbl_FB_Store_Details where Store_Latitude like '"
            + x + "%' and Store_Longitude like '" + y + "%'";
        conn.Open();
        cmd = new SqlCommand(qry, conn);
        dr = cmd.ExecuteReader();
        while (dr.Read())
        {
            Store_Id = dr[0].ToString();
            str_dlat = dr[1].ToString();
            str_dlon = dr[2].ToString();
            double dlat = Convert.ToDouble(str_dlat);
            double dlon = Convert.ToDouble(str_dlon);
            dist_in_sqmts = obj_distance.distance(lat, llong, dlat, dlon);
            id = Convert.ToInt32(Store_Id);
            if (dist_in_sqmts < 200.00)
            {
                string insert_qry = "insert into tbl_FB_Offer_PushNote values('" + dev_token + "', "+ id + ",'" + DateTime.Now + "','N')";
                conn1.Open();
                cmd = new SqlCommand(insert_qry, conn1);
                row_affect = cmd.ExecuteNonQuery();
                conn1.Close();
            }
        }
        conn.Close();
        if (row_affect > 0)
            result = true;
        xml_doc = x_doc.result(result);
        return xml_doc;
    }

---

You need to set MultipleActiveResultSets=True in the connection string. See MSDN

---

You have to use DataSet class - SqlDataAdapter and DataTable to retrieve result from the database.

string qry = "select Store_id,Store_Latitude,Store_Longitude
      from tbl_FB_Store_Details where Store_Latitude 
            like @x and Store_Longitude like @y";
SqlCommand cmd=new SqlCommand(qry,conn);
cmd.Parameters.AddWithValue("@x", x + "%");
cmd.Parameters.AddWithValue("@y", y + "%");

SqlDataAdapter adp=new SqlDataAdapter(cmd);
DataTable dt=new DataTable();
adp.Fill(dt);

foreach(DataRow dr in dt.Rows)
 {
     Store_Id = dr[0].ToString();
     str_dlat = dr[1].ToString();
     str_dlon = dr[2].ToString();
     double dlat = Convert.ToDouble(str_dlat);
     double dlon = Convert.ToDouble(str_dlon);
     dist_in_sqmts = obj_distance.distance(lat, llong, dlat, dlon);
     id = Convert.ToInt32(Store_Id);
     if (dist_in_sqmts < 200.00)
      {
           .....
       }
 }