none
Oracle SQL Server - Database - exception - not closed RRS feed

  • Question

  • Hello,
    When I during the using get an exception, my application can't close it.
    I think in this case the using close not the connection.
    How can I solve it?

    Reason,  sometimes  I close the app and inside the taskmanager i see the app. Well I'm Looking for a solution.

    https://stackoverflow.com/questions/698397/how-do-i-close-an-oracleconnection-in-net

    try
    {
    	using (OracleConnection connection = new OracleConnection(DataBase.ConnectionString))
    	{
    		OracleCommand cmd = new OracleCommand("NUMBERS.GET_N_UNPROCESSED", connection);
    		cmd.CommandType = CommandType.StoredProcedure;
    
    		cmd.Parameters.Add("p_in_batch_id", OracleDbType.Varchar2, CurrentOrder.BatchID, ParameterDirection.Input);
    	
    
    		connection.Open();
    		OracleDataAdapter da = new OracleDataAdapter(cmd);
    		cmd.ExecuteNonQuery();
    
    		if (cmd.Parameters["p_out_error_message"].Value.ToString() != "null")
    		{
    			throw new Exception
    		}	
    	}
    }
    catch (Exception e)
    {
    	throw new Extension
    }
    finally
    {
    }
    return ret;
    }
    

    I found this, is it right?
    using (SqlConnection sqlConn = new SqlConnection("connectionstring"))
    {
          sqlConn.Open();
          ...
    }
    
    The compiler will convert this code as below.
    
    try
    {
         SqlConnection sqlConn = new SqlConnection("connectionstring");
         sqlConn.Open();
         ...
    }
    finally
    {
         sqlConn.Close();
    }
    

    With best regards Markus

    Wednesday, July 17, 2019 4:41 PM

Answers

  • I use using and wanted to be sure when it comes to an exception there that the database connection is closed.

    The 'using' statement disposes of all objects within the its scope, and the connection is closed on the dispose  even if an exception is thrown. 

    About centralized or global exception handler, which applies to any unhandled exception thrown in the form project or a classlib project the form project is referencing, the GEH will catch the unhandled exception. If you don't put a try/catch in any code nowhere in any code, then it's a unhandled exception that the GEH will catch, if an exception is thrown.

    https://www.codeproject.com/Articles/43182/Centralised-Exception-Handling-in-C-Windows-Applic

    Saturday, July 20, 2019 1:08 PM
  • So your code throws an exception and the form closes? That is expected. An exception on the UI thread will terminate the UI thread (or actually any thread it happens on) unless you handle it. 

    In general you want to ensure that any exception that occurs on the UI thread is handled so it can be reported to the user. Whether the form closes after that is dependent upon what failed. For example if you are attempting to read data from a database in response to the user clicking something and it fails you'd display some message about being unable to load data and then continue. 

    //Called from the UI
    void btn_Click ( object sender, EventArgs e )
    {
       try
       {
          DoWork();
       } catch (Exception ex)
       {
          DisplayError(ex);
    
          //Unhandled errors should terminate the app
          if (!ICanHandleThisException(ex))
             throw;
       };
    }

    Of course littering all your UI with this code will bloat it so either an error handling framework or some helper types can reduce this down but at some point it is still going to be method-specific calls. For example a bad DB read in one method could be fatal to the app in another. So it is case by case basis.

    Specifically for Winforms you can use the Application.ThreadException to respond to any exception on the UI thread. This is generally where your fatal error handling goes (such as logging the error and displaying a message about the app going way). This event cannot stop the exception as it has already happened. It is just about reporting. Many winforms apps use standard try-catch in UI functions for errors they can handle and let everything else go to the ThreadException to terminate the app (after logging and reporting).

    Additionally all apps have the AppDomain.UnhandledException event. This serves a similar event but is called for any thread in the appdomain.

    For your case, wrap the UI call that calls your Oracle code that is failing with a try-catch and display a simple message box. This should prevent the form from going away and you can then start working on your larger error handling processing.


    Michael Taylor http://www.michaeltaylorp3.net

    Saturday, July 20, 2019 2:31 PM
    Moderator

All replies

  • The Oracle code block you posted doesn't compile. We cannot tell you what is wrong there but the `using` statement is guaranteed to dispose of the expression given the using statement provided it is not null. This happens irrelevant of whether an exception occurs or not.

    I don't understand what you mean by your app cannot close it when an exception occurs. There is nothing to close so it doesn't need to do anything. Also, in your Oracle code, you can completely remove the try-catch-finally as it isn't doing anything.

    using (OracleConnection connection = new OracleConnection(DataBase.ConnectionString))
    {
       OracleCommand cmd = new OracleCommand("NUMBERS.GET_N_UNPROCESSED", connection);
       cmd.CommandType = CommandType.StoredProcedure;
    
       cmd.Parameters.Add("p_in_batch_id", OracleDbType.Varchar2, CurrentOrder.BatchID, ParameterDirection.Input);
    	
       connection.Open();
       cmd.ExecuteNonQuery();
    
       //This expression doesn't make sense as the string wouldn't be "null"
       //Additionally you never added it to the command so it wouldn't likely be there anyway - Oracle is a little finicky
       //about parameters. I cannot remember the rules but I believe
       //you must specify all the parameters when calling an oracle
       //sproc or function, if you don't then it fails the call.
    //   if (cmd.Parameters["p_out_error_message"].Value.ToString() != "null")
       var msg = cmd.Parameters["p_out_error_message"].Value;
       if (!String.IsNullOrEmpty(msg))
          throw new Exception("Something failed");
    };
    Question related to Oracle itself should be posted in the ODP.NET forums. The .NET Framework implementation of the System.Data.Oracle namespace has been deprecated for a long time. ODP.NET is the framework to use and it is provided by Oracle.


    Michael Taylor http://www.michaeltaylorp3.net

    Wednesday, July 17, 2019 5:46 PM
    Moderator
  • How is the code being used? In what kind of a solution is the code being used? You really need to think about using global exception handling to eliminate try/catches and let the GEH catch all exceptions.
    Wednesday, July 17, 2019 8:28 PM

  • Hello,
    >I don't understand what you mean by your app cannot close it when an exception occurs.
    >How is the code being used? In what kind of a solution is the code being used?
    >You really need to think about using global exception handling
    >to eliminate try/catches and let the GEH catch all exceptions.
    I use a C# Desktop WinForm application. Can you make here a sample code?
    If it comes to an exception, I noticed that the form disappears, is still visible in the task manager.
    I use using and wanted to be sure when it comes to an exception there that the database connection is closed.
    cmd.Parameters.Add("p_out_error_message", OracleDbType.Varchar2, ParameterDirection.Output);
    cmd.Parameters["p_out_error_message"].Size = 2000;


    Yes I know, I must add all Output variables. It was just a code snippet.
    With best regards Markus

    Saturday, July 20, 2019 10:31 AM
  • I use using and wanted to be sure when it comes to an exception there that the database connection is closed.

    The 'using' statement disposes of all objects within the its scope, and the connection is closed on the dispose  even if an exception is thrown. 

    About centralized or global exception handler, which applies to any unhandled exception thrown in the form project or a classlib project the form project is referencing, the GEH will catch the unhandled exception. If you don't put a try/catch in any code nowhere in any code, then it's a unhandled exception that the GEH will catch, if an exception is thrown.

    https://www.codeproject.com/Articles/43182/Centralised-Exception-Handling-in-C-Windows-Applic

    Saturday, July 20, 2019 1:08 PM
  • So your code throws an exception and the form closes? That is expected. An exception on the UI thread will terminate the UI thread (or actually any thread it happens on) unless you handle it. 

    In general you want to ensure that any exception that occurs on the UI thread is handled so it can be reported to the user. Whether the form closes after that is dependent upon what failed. For example if you are attempting to read data from a database in response to the user clicking something and it fails you'd display some message about being unable to load data and then continue. 

    //Called from the UI
    void btn_Click ( object sender, EventArgs e )
    {
       try
       {
          DoWork();
       } catch (Exception ex)
       {
          DisplayError(ex);
    
          //Unhandled errors should terminate the app
          if (!ICanHandleThisException(ex))
             throw;
       };
    }

    Of course littering all your UI with this code will bloat it so either an error handling framework or some helper types can reduce this down but at some point it is still going to be method-specific calls. For example a bad DB read in one method could be fatal to the app in another. So it is case by case basis.

    Specifically for Winforms you can use the Application.ThreadException to respond to any exception on the UI thread. This is generally where your fatal error handling goes (such as logging the error and displaying a message about the app going way). This event cannot stop the exception as it has already happened. It is just about reporting. Many winforms apps use standard try-catch in UI functions for errors they can handle and let everything else go to the ThreadException to terminate the app (after logging and reporting).

    Additionally all apps have the AppDomain.UnhandledException event. This serves a similar event but is called for any thread in the appdomain.

    For your case, wrap the UI call that calls your Oracle code that is failing with a try-catch and display a simple message box. This should prevent the form from going away and you can then start working on your larger error handling processing.


    Michael Taylor http://www.michaeltaylorp3.net

    Saturday, July 20, 2019 2:31 PM
    Moderator