A transport-level error has occurred when receiving results from the server. (Provider: session provider, error:19 - physical connection is not usable)

See more:

C#

ASP.NET

SQL

SQL-Server

I am developing a web application using VS 2013, framework 4.0.

I am getting distinct values from a table in the database (Row count fetched is 282752). After that i am fetching several values from different tables based on the distinct values i got.

The error i get when i run this code is:

A transport-level error has occurred when receiving results from the server. (provider: session Provider, error:19 - Physical connection is not usable)

Is there a way to implement my code without having this error?


The following Code explains what i'm doing exactly.

public void GetData()
      {
          string yard_date = "08/30/2015 16:00:00";
          string chosen_from_date = "01/10/2016 23:00:00";
          string chosen_to_date = "01/10/2016 23:00:00";
          List<string> unit_ids = new List<string>();
          //  int count = 0;

          using (SqlConnection connection2 = new SqlConnection(ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString))
          {
              connection2.Open();

              string sql2 = "select distinct id from inv_unit where category <> 'THRGH'";

              SqlCommand command2 = new SqlCommand(sql2, connection2);

              SqlDataReader reader2 = command2.ExecuteReader();


              while (reader2.Read())
              {

                  string unit_no = reader2.GetValue(0).ToString();
                  unit_ids.Add(unit_no);
              }
              reader2.Close();
          }

          Response.Write("IN");

          using (SqlConnection connection3 = new SqlConnection(ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString))
          {
              connection3.Open();
              //count for each block
              int count_1a = 0;
              int count_1a_20 = 0;
              int count_1a_40 = 0;
              foreach (string id in unit_ids)
              {
                  foreach (DateTime date in RangeOfDates(chosen_from_date, chosen_to_date))
                  {
                      string sql3 = "SELECT TOP 1 inv_unit_fcy_visit.gkey, ref_equipment.length_mm, inv_move_event.t_put, inv_move_event.to_pos_loctype, inv_move_event.to_pos_slot "
                                      + "FROM inv_unit, inv_unit_fcy_visit, inv_unit_equip, ref_equipment, inv_move_event "
                                      + "WHERE inv_unit.gkey = inv_unit_fcy_visit.unit_gkey AND inv_unit.gkey = inv_unit_equip.unit_gkey AND inv_unit_equip.eq_gkey = ref_equipment.gkey AND "
                                      + " inv_unit_fcy_visit.gkey = inv_move_event.ufv_gkey AND inv_unit.id = '" + id + "' AND  inv_move_event.t_put <= '" + date + "' "
                                      + "ORDER BY inv_move_event.t_put DESC";

                      SqlCommand command3 = new SqlCommand(sql3, connection3);

                      SqlDataReader reader3 = command3.ExecuteReader();

                      while (reader3.Read())
                      {
                          // string length_ft = reader3.GetValue(1).ToString();
                          string to_pos_slot = reader3.GetValue(4).ToString();

                          if (to_pos_slot != "")
                          {
                              double length_ft = Math.Round((Convert.ToDouble(reader3.GetValue(1).ToString())) / 304.8, 0);

                              string to_pos_loctype = reader3.GetValue(3).ToString();
                              char block_char = Convert.ToChar(to_pos_slot.Substring(0, 1));
                              if (to_pos_loctype == "YARD" && Char.IsDigit(block_char))
                              {
                                  string block = to_pos_slot.Substring(0, 2);

                                  if (length_ft == 20)
                                  {
                                      if (block == "1A")
                                      {
                                          count_1a++;
                                          count_1a_20++;
                                      }

                                  }
                                  else if (length_ft == 40)
                                  {
                                      if (block == "1A")
                                      {
                                          count_1a++;
                                          count_1a_40 += 2;
                                      }

                                  }
                                  else if (length_ft == 45)
                                  {
                                      if (block == "1A")
                                      {
                                          count_1a++;
                                          count_1a_40 += 2;
                                      }

                                  }

                                  // Response.Write("IN");
                              }
                          }

                      }

                  }
              }

              Response.Write("count1:  " + count_1a + "   cpunt2:   " + count_1a_20 + "   count3" + count_1a_40);

          }
          //  }


          //  }
      }

What I have tried:

I checked to run the following SQL query based on specific given values separately in Microsoft SQL server Management studio and it works fine.

SELECT TOP 1 inv_unit_fcy_visit.gkey, ref_equipment.length_mm, inv_move_event.t_put, inv_move_event.to_pos_loctype, inv_move_event.to_pos_slot
FROM inv_unit, inv_unit_fcy_visit, inv_unit_equip, ref_equipment, inv_move_event
WHERE inv_unit.gkey = inv_unit_fcy_visit.unit_gkey AND inv_unit.gkey = inv_unit_equip.unit_gkey AND inv_unit_equip.eq_gkey = ref_equipment.gkey AND
inv_unit_fcy_visit.gkey = inv_move_event.ufv_gkey AND inv_unit.id = 'ZFRU2211874' AND inv_move_event.t_put <= '08/09/2015 23:00:00'
ORDER BY inv_move_event.t_put DESC

However, it crashes and displays the error only in the code behind page in my web application.