none
Try Catch Finally with multiple SQL Queries RRS feed

  • Question

  • Hallo

    Hope somebody got a better way to do this, feel very tedious and sure I'm on the wrong track.

    I got a simple Windows form where we type company information, I got two tables to start with, CompanyHeader and CompanyDetail in SQL.

    CompanyHeader only hold the Company Name and CompanyID.

    The CompanyDetails keeps all the other information regarding the company, because multiple companies can have different sub divisions it's linked to companyHeader via the CompanyID.. 

    We got a simple add company form which allows to save a new company, but we need to check a couple of this, for one if all the neccesary fields has been typed in on the form, then check if the company and the subdivision already exist, if neither the company nor the subdivision exist we will create a new entry in the CompanyHeader field which updates the CompanyName, we then do a Max number on CompanyID and use this as the CompanyID link in the details field.. (Maybe a better way??).

    The try and catch and finally can we do multiple sql queries inside the try part of the code or must we have seperate try catch and finally for all the sql queries?

    Below some parts of the code which I think is unnecessary coding??

            private void btnSave_Click(object sender, EventArgs e)
            {
                bool EntryComplete = false;
                string CompanyNameExist ="";
    
                if ((this.txtCompanyName.TextLength) != 0)
                {
                    EntryComplete = true;
                    this.labCompanyName.ForeColor = System.Drawing.Color.Black;
                }
                else
                {
                    EntryComplete = false;
                    this.labCompanyName.ForeColor = System.Drawing.Color.Red;                
                }
    
                if ((this.txtSubDivision.TextLength) != 0)
                {
                    EntryComplete = true;
                    this.labSubDivision.ForeColor = System.Drawing.Color.Black;
                }
                else
                {
                    EntryComplete = false;
                    this.labSubDivision.ForeColor = System.Drawing.Color.Red;
                }
                if ((this.txtLandline.TextLength) != 0)
                {
                    EntryComplete = true;
                    this.labLandLine.ForeColor = System.Drawing.Color.Black;
                }
                else
                {
                    EntryComplete = false;
                    this.labLandLine.ForeColor = System.Drawing.Color.Red;
                }
    #region Check If Company Name and Sub Division Already Exist
               
                if (EntryComplete == true)                
                {                
                    ConnectionStringSettings conSettings = ConfigurationManager.ConnectionStrings["BusString"];
                    string constring = conSettings.ConnectionString;
                    int CompanyID;                
                    int.TryParse(this.TxtCompanyID.Text, out CompanyID);
                    this.lstSubDivision.Items.Clear();
                    try
                    {
                        con = new SqlConnection(constring);
                        con.Open();
                        cmd = new SqlCommand("SELECT CompanyName,SubDivision FROM [ViewCompanyDetail] where CompanyName like @CompanyName and SubDivision like @SubDivision ", con);
                        cmd.Parameters.AddWithValue("@CompanyName", this.txtCompanyName.Text);
                        cmd.Parameters.AddWithValue("@SubDivision", this.txtSubDivision.Text);
    
                        dr = cmd.ExecuteReader();
                        while (dr.Read())
                        {
                            MessageBox.Show(this.txtCompanyName.Text.ToString() + ", " + this.txtSubDivision.Text.ToString() + " already Exist");
                            CompanyNameExist = dr[0].ToString();
                            MessageBox.Show(CompanyNameExist.Length.ToString());
                        }                    
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message);
                    }
                    finally
                    {                   
                        con.Close();
                    }
                }
                
                if (CompanyNameExist.Length == 0)
                {               
                    ConnectionStringSettings conSettings = ConfigurationManager.ConnectionStrings["BusString"];
                    string constring = conSettings.ConnectionString;     
                                  
                    try
                    {
                        con = new SqlConnection(constring);
                        con.Open();
                        cmd = new SqlCommand("INSERT INTO CompanyHeader (CompanyName) VALUES('Whatever') ", con);
                        cmd.ExecuteNonQuery();
                        MessageBox.Show("Succesfully Posted");                    
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message);
                    }
                    finally
                    {
                        con.Close();
                    }
                    
                }
    #endregion



    labjac

    • Moved by Tina-Shi Wednesday, April 8, 2015 5:46 AM the issue is related to the ado.net
    Monday, April 6, 2015 4:53 PM

Answers

  • Hello labjac,

    >> The try and catch and finally can we do multiple sql queries inside the try part of the code or must we have seperate try catch and finally for all the sql queries?

    Yes, we can, the C# try-catch-finally block does not prevent us from writing multi sql queries inside it. In your case, I suggest that you could have a try so that you would only create a one connection instance. You could write the “if (CompanyNameExist.Length == 0)“ code block after the “while (dr.Read())” code block as:

    if (EntryComplete == true)                
                {                
                    ConnectionStringSettings conSettings = ConfigurationManager.ConnectionStrings["BusString"];
                    string constring = conSettings.ConnectionString;
                    int CompanyID;                
                    int.TryParse(this.TxtCompanyID.Text, out CompanyID);
                    this.lstSubDivision.Items.Clear();
                    try
                    {
                        con = new SqlConnection(constring);
                        con.Open();
                        cmd = new SqlCommand("SELECT CompanyName,SubDivision FROM [ViewCompanyDetail] where CompanyName like @CompanyName and SubDivision like @SubDivision ", con);
                        cmd.Parameters.AddWithValue("@CompanyName", this.txtCompanyName.Text);
                        cmd.Parameters.AddWithValue("@SubDivision", this.txtSubDivision.Text);
    
                        dr = cmd.ExecuteReader();
                        while (dr.Read())
                        {
                            MessageBox.Show(this.txtCompanyName.Text.ToString() + ", " + this.txtSubDivision.Text.ToString() + " already Exist");
                            CompanyNameExist = dr[0].ToString();
                            MessageBox.Show(CompanyNameExist.Length.ToString());
                        }
                        if (CompanyNameExist.Length == 0)
                       {               
                        cmd = new SqlCommand("INSERT INTO CompanyHeader (CompanyName) VALUES('Whatever') ", con);
                        cmd.ExecuteNonQuery();
                        MessageBox.Show("Succesfully Posted");                    
                       }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message);
                    }
                    finally
                    {                   
                        con.Close();
                    }
                }
                
                

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by labjac Wednesday, April 8, 2015 6:54 PM
    • Unmarked as answer by labjac Wednesday, April 8, 2015 6:55 PM
    • Marked as answer by labjac Wednesday, April 8, 2015 6:55 PM
    Wednesday, April 8, 2015 6:04 AM
    Moderator

All replies

  • Hi labjac,

    Thank you for posting in MSDN forum.

    Since this issue is related to the ADO.NET , so we will move this case to this ADO.NET Provider forum:https://social.msdn.microsoft.com/Forums/en-US/home?forum=adodotnetdataproviders , you will get better support.

    Best Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, April 8, 2015 5:46 AM
  • Hello labjac,

    >> The try and catch and finally can we do multiple sql queries inside the try part of the code or must we have seperate try catch and finally for all the sql queries?

    Yes, we can, the C# try-catch-finally block does not prevent us from writing multi sql queries inside it. In your case, I suggest that you could have a try so that you would only create a one connection instance. You could write the “if (CompanyNameExist.Length == 0)“ code block after the “while (dr.Read())” code block as:

    if (EntryComplete == true)                
                {                
                    ConnectionStringSettings conSettings = ConfigurationManager.ConnectionStrings["BusString"];
                    string constring = conSettings.ConnectionString;
                    int CompanyID;                
                    int.TryParse(this.TxtCompanyID.Text, out CompanyID);
                    this.lstSubDivision.Items.Clear();
                    try
                    {
                        con = new SqlConnection(constring);
                        con.Open();
                        cmd = new SqlCommand("SELECT CompanyName,SubDivision FROM [ViewCompanyDetail] where CompanyName like @CompanyName and SubDivision like @SubDivision ", con);
                        cmd.Parameters.AddWithValue("@CompanyName", this.txtCompanyName.Text);
                        cmd.Parameters.AddWithValue("@SubDivision", this.txtSubDivision.Text);
    
                        dr = cmd.ExecuteReader();
                        while (dr.Read())
                        {
                            MessageBox.Show(this.txtCompanyName.Text.ToString() + ", " + this.txtSubDivision.Text.ToString() + " already Exist");
                            CompanyNameExist = dr[0].ToString();
                            MessageBox.Show(CompanyNameExist.Length.ToString());
                        }
                        if (CompanyNameExist.Length == 0)
                       {               
                        cmd = new SqlCommand("INSERT INTO CompanyHeader (CompanyName) VALUES('Whatever') ", con);
                        cmd.ExecuteNonQuery();
                        MessageBox.Show("Succesfully Posted");                    
                       }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message);
                    }
                    finally
                    {                   
                        con.Close();
                    }
                }
                
                

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by labjac Wednesday, April 8, 2015 6:54 PM
    • Unmarked as answer by labjac Wednesday, April 8, 2015 6:55 PM
    • Marked as answer by labjac Wednesday, April 8, 2015 6:55 PM
    Wednesday, April 8, 2015 6:04 AM
    Moderator
  • Hallo Fred

    Thanks for the reply, at the end I found the actual problem, I forgot the close the reader so it didn't allow me to run multiple queries, ("maybe I should just learn to read the error" :-)

    I thought the error was related in some way to the try catch block and didn't bother to ask the question that was giving the error.

    I also changed the code to what you suggested, so got one connection string on top, do my multiple queries and then close the connection, works perfect.

    Thanks for the time to answer my question.

    Regards,

    Jacques


    labjac

    Wednesday, April 8, 2015 6:58 PM