Where / when to use connection.close()

Question

Can anyone help me about the connection.Open() and close(). I'm not very sure about when to close and open the connection. The code that is giving me an error is added below.

If anyone who can give me a tip about this, I would appreciate it. Please feel free to edit my code showing where to close the connection in order for me to learn from it.

I am still a student. Thank you. =)

public class LoanDAL
{
string connString = ConfigurationManager.ConnectionStrings["Oakhorizons"].ToString();
public LoanDAL()
{
    //
    // TODO: Add constructor logic here
    //
}
public DataTable getAllLoanInfoDT()
{
        using (SqlConnection conn = new SqlConnection(connString))
        {
            SqlCommand cmd2 = new SqlCommand();
            cmd2.Connection = conn;
            // cmd.CommandType = CommandType.StoredProcedure;
            cmd2.CommandText = "SELECT DISTINCT loanUpdateDate FROM LoanPortfolio WHERE (custID LIKE 'OH00002') AND (loanType LIKE 'Personal Loan')";
            cmd2.Parameters.AddWithValue("@custID", "OH00002");
            cmd2.Parameters.AddWithValue("@loanType", "Personal Loan");
            conn.Open();
            DateTime loanUpdateDate = DateTime.Now;
            SqlDataReader myReader = cmd2.ExecuteReader();
            while (myReader.Read())
            {
                loanUpdateDate = Convert.ToDateTime(myReader[0]); 
                break; 
            }

            DateTime currDateTime = DateTime.Now;

            int loanToBeAdded = (((currDateTime.Year - loanUpdateDate.Year) * 12) + currDateTime.Month - loanUpdateDate.Month) * 500;
            if (loanToBeAdded > 0)
            {
                String sql = "UPDATE LoanPortfolio SET loanPaid = loanPaid + " + loanToBeAdded.ToString() + ", LastUpdatedLoanPaidDate = " + DateTime.Now.ToString();
                sql += " WHERE (loanType LIKE 'Personal Loan') AND (custID LIKE 'OH00002')";
                cmd2.Connection = conn;
                cmd2.CommandText = sql;
                cmd2.ExecuteNonQuery();

            }
            conn.Close();
            using (SqlDataAdapter dAd = new SqlDataAdapter("SELECT * FROM LoanPortfolio where custID like 'OH00002'", conn))
            {
                DataTable dTable = new DataTable();
                dAd.Fill(dTable);
                return dTable;
            }

        }

}

//Returning a DataSet which contains all the information in the Player Table
public DataSet getAllLoanInfoDS()
{
    using (SqlConnection conn = new SqlConnection(connString))
    {

        using (SqlDataAdapter dAd = new SqlDataAdapter("SELECT * FROM LoanPortfolio where custID like 'OH00002", conn))
        {
            DataSet myDS = new DataSet();
            dAd.Fill(myDS);
            return myDS;
        }



    }
}
}

Answer 1

Sql dataAdapter itself manages connection .it opens and closes after fill command , However using ExecuteReader or ExecuteNonQuery explicitly requires connection to open and close, you can open conection just before executing nonquery and executereader command , How ever one more scenario could be if your code faces an error and code does not reach to conn.close(); command .

   public class LoanDAL
{
string connString = ConfigurationManager.ConnectionStrings["Oakhorizons"].ToString();
public LoanDAL()
{
    //
    // TODO: Add constructor logic here
    //
}
public DataTable getAllLoanInfoDT()
{
        using (SqlConnection conn = new SqlConnection(connString))
        {
            SqlCommand cmd2 = new SqlCommand();
            cmd2.Connection = conn;
            // cmd.CommandType = CommandType.StoredProcedure;
            cmd2.CommandText = "SELECT DISTINCT loanUpdateDate FROM LoanPortfolio WHERE (custID LIKE 'OH00002') AND (loanType LIKE 'Personal Loan')";
            cmd2.Parameters.AddWithValue("@custID", "OH00002");
            cmd2.Parameters.AddWithValue("@loanType", "Personal Loan");

            DateTime loanUpdateDate = DateTime.Now;
            try
            {
             conn.Open();
            SqlDataReader myReader = cmd2.ExecuteReader();
            while (myReader.Read())
            {
                loanUpdateDate = Convert.ToDateTime(myReader[0]); 
                break; 
            }
               conn.Close();   
            DateTime currDateTime = DateTime.Now;
            }
            Catch(Exception Ex)
            {//Close connection in case of any exception .
              conn.Close();
            }
            int loanToBeAdded = (((currDateTime.Year - loanUpdateDate.Year) * 12) + currDateTime.Month - loanUpdateDate.Month) * 500;
            if (loanToBeAdded > 0)
            {
               try
               {
                String sql = "UPDATE LoanPortfolio SET loanPaid = loanPaid + " + loanToBeAdded.ToString() + ", LastUpdatedLoanPaidDate = " + DateTime.Now.ToString();
                sql += " WHERE (loanType LIKE 'Personal Loan') AND (custID LIKE 'OH00002')";
                cmd2.Connection = conn;
                cmd2.CommandText = sql;
                 conn.Open();
                cmd2.ExecuteNonQuery();
                conn.Close();
                }
                Catch(Exception Ex)
                 {
                 //Close connection in case of exception .
                  Conn.close()
                  throw Ex;
                 }
            }            
            using (SqlDataAdapter dAd = new SqlDataAdapter("SELECT * FROM LoanPortfolio where custID like 'OH00002'", conn))
            {
                DataTable dTable = new DataTable();
                dAd.Fill(dTable);
                return dTable;
            }

        }

}

//Returning a DataSet which contains all the information in the Player Table
public DataSet getAllLoanInfoDS()
{
    using (SqlConnection conn = new SqlConnection(connString))
    {

        using (SqlDataAdapter dAd = new SqlDataAdapter("SELECT * FROM LoanPortfolio where custID like 'OH00002", conn))
        {
            DataSet myDS = new DataSet();
            dAd.Fill(myDS);
            return myDS;
        }



    }
}
}

Your connection string needs to have "MultipleActiveResultSets=True" added to it.If you are using multiple command like you are having two commands in a single connection ,Either enable "MultipleActiveResultSets=True" in your connection string or ,Open and close after every transaction with server (Single command per connection).

Answer 2

This section:

conn.Close();
using (SqlDataAdapter dAd = new SqlDataAdapter("SELECT * FROM LoanPortfolio where custID like 'OH00002'", conn))
{
    DataTable dTable = new DataTable();
    dAd.Fill(dTable);
    return dTable;
}

You are re-using the same connection after closing it. Re-open and re-use. Or don't close at all as you are still in the same using block.

Update: MSDN: http://msdn.microsoft.com/en-us/library/bh8kx08z(v=vs.100).aspx

The Fill method implicitly opens the Connection that the DataAdapter is using if it finds that the connection is not already open. If Fill opened the connection, it will also close the connection when Fill is finished.

So, in the above section "conn.close()" shouldn't matter, "Fill" will open the connection and close it.

The using block will dispose off the connection once done with it. So, explicitly closing a connection is not required.

Answer 3

you don't have close the connection explicitly since you're having using {} block with sqlconnection object. The connection will automatically get closed.

second if you want to close the connection explicitly then close it once all your db operations are finished.

for e.g. close it when your adapter fill operation is finished.

....
....
....
using (SqlDataAdapter dAd = new SqlDataAdapter("SELECT * FROM LoanPortfolio where custID like 'OH00002'", conn))
                {
                    DataTable dTable = new DataTable();
                    dAd.Fill(dTable);
                    conn.Close();
                    return dTable;
                }