0

My client server is not supporting MARS. So I need to close each sql connection after executing each query. But I have doubt on closing connection when its coming to multiple nested loop query . My code is

  protected void btn_upload_Click(object sender, ImageClickEventArgs e)
{
   try
   {
      SqlConnection con = obj.getcon();
      con.Open();
      SqlCommand cmd77 = new SqlCommand("select * from emp_details where emp_id='"+emp_id+"'", con);
      SqlDataReader dr77 = cmd77.ExecuteReader();
       if (dr77.HasRows)//dont insert if employee already exist
         {
       string query1 = "UPDATE emp_details SET emp_name= @emp_name  where emp_id=@emp_id";
        SqlCommand cmd = new SqlCommand(query1, con);
        cmd.Parameters.Add(new SqlParameter("emp_id", emp_id));
        cmd.Parameters.Add(new SqlParameter("emp_name", emp_name))
        cmd.ExecuteNonQuery();
         }
         else 
         {
        insertdataintosql(GetempID,GetempName);
         }    
        con.Close();
     }
    catch (Exception ex)
    {
       string ex=ex.Message;
    }
}
public void insertdataintosql(string emp_id, string emp_name)
{   
        SqlConnection con = obj.getcon();
        con.Open();
        string query = "insert into emp_details(emp_id,emp_name) values(@emp_id,@emp_name)";
        SqlCommand cmd = new SqlCommand(query, con);
        cmd.Parameters.Add(new SqlParameter("emp_id", emp_id));
        cmd.Parameters.Add(new SqlParameter("emp_name", emp_name))
        cmd.ExecuteNonQuery();
        con.Close();
}

Where should I close the first connection for SqlDataReader (dr77) since it is used in if else loop? If not using MARS , Shall I need to open/close a new connection on update query? So is it necessary to close dr77 to before it?

H.NS
  • 75
  • 7
  • _"if else loop"_? Maybe `if/else` statement? – SᴇM Mar 01 '19 at 05:53
  • 2
    Most common way of closing `SqlConnection` is to use `using`: `using(SqlConnection con = obj.getcon()) { /* rest of the code */ }` – SᴇM Mar 01 '19 at 05:54
  • no need of closing if use using? – H.NS Mar 01 '19 at 07:09
  • The connection will automatically close at the end of the `using` block. Ref: [SqlConnection.Close Method](https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnection.close?view=netframework-4.7.2) – SᴇM Mar 01 '19 at 07:17
  • Will following post solve your problem? **[Do I have to Close() a SQLConnection before it gets disposed?](https://stackoverflow.com/questions/1195829/do-i-have-to-close-a-sqlconnection-before-it-gets-disposed)** – SᴇM Mar 01 '19 at 07:26

0 Answers0