CODEWITHSUNDEEP
c#sql-serverenterprise-library
Gabbyboy
Gabbyboy

Reputation: 1524

How to close connection of Microsoft.Practices.EnterpriseLibrary.Data.ExecuteNonQuery

I'm using the library microsoft.practices.enterpriselibrary, to access a SQL Server database. I'm wondering how to close connection when I use the ExecuteNonQuery method?

ie:

using Microsoft.Practices.EnterpriseLibrary.Data.Sql;

SqlDatabase db = null;
try
{
    db = new SqlDatabase(stringConnection);
    db.ExecuteNonQuery("storedprocedure", someParams);
}
catch (Exception ex)
{
}

I can't do something like 

finally
{
    if (db != null)
    {
        ((IDisposable)db).Dispose();
    }
}

So... how can I avoid connection leaks?

Thank you.

Upvotes: 4

Views: 11845

Answers (3)

John Reynolds
John Reynolds

Reputation: 91

Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetStoredProcCommand("GetProductsByCategory"); 
db.AddInParameter(cmd, "@requestId", DbType.Int32, requestId);
db.ExecuteNonQuery(cmd);

The Enterprise Library handles closing connections for you, except for the case of using Data Readers. When using a IDataReader, you should either use the close or a using to call the dispose (same as calling close)

Database db = DatabaseFactory.CreateDatabase();

DbCommand cmd = db.GetSqlStringCommand("Select Name, Address From Customers");
using (IDataReader reader = db.ExecuteReader(cmd))
{
   // Process results
}

The dispose in this case will close the connection. They have a great section on connection handling in the documentation.

https://msdn.microsoft.com/en-us/library/ff648933.aspx

Upvotes: 4

Tushar Kesare
Tushar Kesare

Reputation: 790

Generally you do not need to worry about closing the connection, as Data Access Block manages connections more efficiently. check "Managing Connections" section in below link: http://msdn.microsoft.com/en-us/library/ff953187(v=pandp.50).aspx

Upvotes: 5

Tony Li
Tony Li

Reputation: 116

You can put the code inside the "using" block. This will ensure the connection closed after finishing.

using Microsoft.Practices.EnterpriseLibrary.Data.Sql;

SqlDatabase db = new SqlDatabase(stringConnection);
using (DbConnection con = db.CreateConnection())
{
    db.ExecuteNonQuery("storedprocedure", someParams);
}

or you can use the con.Close().

Upvotes: 6

Related Questions