CODEWITHSUNDEEP
c#
First Last
First Last

Reputation: 59

Try Catch Finally - dispose of a reader

In C#, I am using a TRY..CATCH..FINALLY block in my code surrounding the executing of a stored procedure.

If there is an exception, then in my FINALLY I want to close the reader - PolicyResult as well as the connections.

However, I get an error

The name PolicyResult does not exist in the current context

PolicyResult is the DataReader defined in the TRY but it seems to not be recognized in the FINALLY section.

Why?

        public static IEnumerable GetPolicies(int? MasterPkgID)
        {
            // Create a list of policies belonging to the master package.
            List<AdditionalInterestPolicyData> additionalInterestPolicyData = new List<AdditionalInterestPolicyData>();

            // Set the SQL connection to the database.
            SqlConnection objConn = new SqlConnection(ConfigurationManager.ConnectionStrings["QUESTIONNAIRE2"].ConnectionString);

            try
            {
               // Open the connection.
               objConn.Open();

               // Get the list of policies by executing a stored procedure.
               SqlCommand PolicyCmd = new SqlCommand("p_expapp_get_policy_detail_by_master_pkg", objConn);
               PolicyCmd.Parameters.Clear();
               PolicyCmd.CommandType = CommandType.StoredProcedure;
               PolicyCmd.Parameters.Add("@a_master_package_iden_key", SqlDbType.Int).Value = MasterPkgID;

               SqlDataReader PolicyResult = PolicyCmd.ExecuteReader();

               // Loop thru the results returned.
               while (PolicyResult.Read())
               {
                  // Add to the list of policies - creates a new row for the collection.
                  additionalInterestPolicyData.Add(new AdditionalInterestPolicyData(
                                                         Int32.Parse(PolicyResult["affiliate_id"].ToString()),
                                                         Int32.Parse(PolicyResult["master_package_iden_key"].ToString())
                                                                                   )
                                                  );
               }
            }
            catch (Exception ex)
            {
                bError = true;
            }
            finally
            {
                PolicyResult.Close();
                objConn.Close();
            }

            return additionalInterestPolicyData;
        }
    }

Upvotes: 1

Views: 9010

Answers (11)

Alex Filipovici
Alex Filipovici

Reputation: 32571

Consider using the using keyword

using (System.Data.SqlClient.SqlDataReader r = PolicyCmd.ExecuteReader())
{
    //DO STUFF
}

You can also define using scopes for the SqlConnection and the SqlCommand objects. After the scopes are closed, the objects will be disposed.

using (System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection())
{
    ...
    using (System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand())
    {
        ...
        using (System.Data.SqlClient.SqlDataReader reader = new System.Data.SqlClient.SqlDataReader())
        {
            //DO STUFF
        }
    }
}

Upvotes: 5

Anri
Anri

Reputation: 6265

Because everything you declare in try block is not visible in other blocks excluding nested and parent. Declare it outside. 

 SqlDataReader PolicyResult=null;
 try
 {
   ...
   PolicyResult = PolicyCmd.ExecuteReader();
 }

Than check if it's not null

 finally
 {
   if(PolicyResult!=null) PolicyResult.Close();
   objConn.Close();
 }

Upvotes: 5

Spencer Ruport
Spencer Ruport

Reputation: 35117

You need to at least declare PolicyResult outside of the try block and I'd recommend instantiating it outside as well. Something like this should work:

public static IEnumerable GetPolicies(int? MasterPkgID)
{
    // Create a list of policies belonging to the master package.
    List<AdditionalInterestPolicyData> additionalInterestPolicyData = new List<AdditionalInterestPolicyData>();

    // Set the SQL connection to the database.
    SqlConnection objConn = new SqlConnection(ConfigurationManager.ConnectionStrings["QUESTIONNAIRE2"].ConnectionString);

    SqlDataReader PolicyResult = null;

    try
    {
        // Open the connection.
        objConn.Open();

        // Get the list of policies by executing a stored procedure.
        SqlCommand PolicyCmd = new SqlCommand("p_expapp_get_policy_detail_by_master_pkg", objConn);
        PolicyCmd.Parameters.Clear();
        PolicyCmd.CommandType = CommandType.StoredProcedure;
        PolicyCmd.Parameters.Add("@a_master_package_iden_key", SqlDbType.Int).Value = MasterPkgID;

        PolicyResult = PolicyCmd.ExecuteReader();
        // Loop thru the results returned.
        while (PolicyResult.Read())
        {
            // Add to the list of policies - creates a new row for the collection.
            additionalInterestPolicyData.Add(new AdditionalInterestPolicyData(
                                                    Int32.Parse(PolicyResult["affiliate_id"].ToString()),
                                                    Int32.Parse(PolicyResult["master_package_iden_key"].ToString())
                                                                            )
                                            );
        }
    }
    catch (Exception ex)
    {
        bError = true;
    }
    finally
    {
        if(PolicyResult != null) PolicyResult.Close();
        objConn.Close();
    }

    return additionalInterestPolicyData;
}

It's worth noting however that this may not be the best approach. First of all you should probably already have your database connection open. Opening and closing your database connection for each transaction gets really messy really quick. You can easily forget to close the connection and if there's an error logging can be tricking depending on the context. If this is a web app it's best to initialize the connection through a session management system that will either open the connection as soon as the page loads or as soon as the first query is executed. This way your connection opening and closing logic is all in one place. Then it becomes safe to assume that the connection did actually connect in any subsequent code and you can handle any transaction specific errors with a bit more ease.

Upvotes: 0

marc_s
marc_s

Reputation: 755083

Any defined inside the try {...} is visible only there - inside the try { ... }. If you want to access the PolicyResult in your finally block - you need to declare it outside the try { ... } block.

The preferred way of doing this would be to use a using (....) { .... } block, however, which automatically guarantees proper disposal and basically makes the finally block obsolete:

try
{
    // Open the connection.
    objConn.Open();

    // Get the list of policies by executing a stored procedure.
    using (SqlCommand PolicyCmd = new SqlCommand("p_expapp_get_policy_detail_by_master_pkg", objConn))
    {
           PolicyCmd.Parameters.Clear();
           PolicyCmd.CommandType = CommandType.StoredProcedure;
           PolicyCmd.Parameters.Add("@a_master_package_iden_key", SqlDbType.Int).Value = MasterPkgID;

           using (SqlDataReader PolicyResult = PolicyCmd.ExecuteReader())
           {
               // Loop thru the results returned.
               while (PolicyResult.Read())
               {
                  // do your stuff here....
               }
           }
    }
}

Upvotes: 1

lante
lante

Reputation: 7336

Thats becouse they are in different scopes.

Try to declare the variable before the try catch:

SqlDataReader PolicyResult;

try
{
    // assign PolicyResult
}
catch
{
}
finally
{
    // use PolicyResult
}

Upvotes: 0

Matthew Watson
Matthew Watson

Reputation: 109762

The try {} defines a scope separate to the finally {}

You must declare PolicyResult outside the try and finally, initialise it to null, and check if for nullness in finally before you close it.

Upvotes: 0

mreyeros
mreyeros

Reputation: 4379

The PolicyResult object is declared within the scope of the try block therefore it does not exist or is not accessible from within the finally block. Move the declaration outside of the try next to your declaration of the connection object.

Upvotes: 0

Chris
Chris

Reputation: 2481

Declare SqlDataReader PolicyResult before the try block if you wish to use it in catch and/or finally

Upvotes: 0

Barry Kaye
Barry Kaye

Reputation: 7759

Define PolicyResult before the try statement:

SqlDataReader PolicyResult = null;

Upvotes: 0

Blachshma
Blachshma

Reputation: 17395

The error is correct, it really dosn't exist, since when you use try/catch you create a scope, any variables created in that scope will not be known outside the scope.

You can easily fix this by moving the decleration to before the try/catch block:

SqlDataReader PolicyResult;
// Other variable declerations

try
{
}
finally
{
  // PolicyResult was decleared OUTSIDE the try block so it is known here.
}

Upvotes: 0

ZombieSheep
ZombieSheep

Reputation: 29963

PolicyResult is defined within the scope of the try block, and is therefore not available outside it (in the finally block, for example.

Upvotes: 1

Related Questions