Cuckoo
Cuckoo

Reputation: 177

Is this a good way to dispose sql connection?

I just wrote this. Is this a good way to dispose an open sql connection ? My senior told me that this is the best practice but still I want to make my self sure by asking here.

public bool SaveBulkpayment(List<ClsCombinePayment> list)
{
    bool Result = false;
    ClsDatabaseManager Manager = ClsDatabaseManager.InitializeDbManager(Constants.Databases.POSSystem);
    Manager.Open();
    Manager.CreateParameters(10);
    try
    {
        foreach (var item in list)
        {
            Manager.AddParameters(0, "@Batch", item.Batch);
            Manager.AddParameters(1, "@TransactionDateTime", TransactionDateTime);
            Manager.AddParameters(2, "@BatchNo", BatchNo);
            Manager.AddParameters(3, "@Salary", item.Salary);
            Manager.AddParameters(4, "@EDRRecord_ID", item.EDRRecord_ID);
            Manager.AddParameters(5, "@User_ID", item.User_ID);
            Manager.AddParameters(6, "@Branch_ID", item.Branch_ID);
            Manager.AddParameters(7, "@PafFile_ID", item.PAFFile_ID);

            Manager.AddParameters(8, "@PinCode", item.PinCode);
            Manager.AddParameters(9, "@ifDiff", item.ifDiff);
            Result = Manager.ExecuteNonQuery("usp_SaveBulkPayment").ToBool();
        }
        Manager.Dispose();
    }
    catch (Exception ex)
    {
        throw ex;
    }
    return Result;
}

Upvotes: 2

Views: 113

Answers (2)

DVJex
DVJex

Reputation: 314

An open SQL connection should be disposed as early as possible.

In your code a better way though would be to dispose the SQL connection in a finally block. This way even in case of an exception, the connection will be disposed safely

try
{
    //Code         
}
catch (Exception ex)
{
    //Handle exception
}
finally{
    //Dispose the Connection
    Manager.Dispose();
}

Upvotes: 1

Gilad Green
Gilad Green

Reputation: 37281

Not really.. in the case of an exception it will not be disposed. Use the using in order to do so.

Something like:

using(var manager = ClsDatabaseManager.InitializeDbManager(Constants.Databases.POSSystem); Manager.Open())
{
    //Your code
}

The using which can be used only on objects that implement IDisposable make sure that the Dispose() method will be called even if there is an exception or a return in the block. It is implemented similar to:

public void Using<T>( T input, Action<T> predicate) where T : IDisposable
{
    try
    {
        predicate(input);
    }
    finally
    {
        input.Dispose();
    }
}

Upvotes: 4

Related Questions