Tuesday, 23 December 2014

ASP.NET Tips #6 - Reduce memory leaks dramatically with the "using" statement

If a type implements IDisposable, wrap the use of it in a "using" statement, so that it automatically disposes of objects properly when the block exits.

Sample of SQLHelper Class file (.cs)

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

public class SqlHelper : IDisposable
{
    private bool disposed = false;
    private string SqlConnectionString = ConfigurationManager.ConnectionStrings["DefaultSQL"].ConnectionString;

    public DataTable SqlExecuteReader(string queryString)
    {
        DataTable dt = new DataTable();
        try
        {
            using (SqlConnection connection = new SqlConnection(SqlConnectionString))
            {
                using (SqlCommand command = connection.CreateCommand())
                {
                    connection.Open();

                    command.CommandType = CommandType.StoredProcedure;
                    command.CommandText = queryString;

                    SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                    dt.Load(reader);

                    reader.Close();
                }
                connection.Close();
            }
        }
        catch (Exception ex)
        {
            throw new Exception("SqlExecuteReader Error: " + ex.Message);
        }
        return dt;
    }

    public void Dispose()
    {
        Dispose(true);
        GC.SuppressFinalize(this);
    }

    private void Dispose(bool disposing)
    {
        if (!disposed)
        {
            if (disposing)
            {
                // Free any other managed objects here.
                if (SqlConnectionString != null)
                {
                    SqlConnectionString = null;
                }
            }
            // Free any unmanaged objects here.
        }
        disposed = true;
    }

    ~SqlHelper()
    {
       Dispose(false);
    }
}

Sample of ASP.NET page (.aspx)

protected void Page_Load(object sender, EventArgs e)
{
    DataTable dt = new DataTable(); 
    using (SqlHelper sql = new SqlHelper())
    {
        dt = sql.SqlExecuteReader("sproc_GetAllCustomers");
    }
}

No comments :

Post a comment