3 Replies - 8190 Views - Last Post: 20 November 2007 - 08:29 PM Rate Topic: -----

#1 tommahhh   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 19-November 07

When to close SqlConnection and SqlReader

Post icon  Posted 20 November 2007 - 05:02 PM

Hi Everyone,

I was just wondering when it was appropriate to close a sql connection and a sql reader. Do I need to close them everytime on every error? Here's my code:

protected void loginButton_Click(object sender, EventArgs e)
		{
			int loginID = 0;
			DataAccess DA = new DataAccess();

			try
			{
				// create the parameterized command and execute it
				SqlCommand cmd = new SqlCommand("select LoginID, Login, Password from Login where Login = @Login and Password = @Password");
				cmd.Parameters.AddWithValue("@Login", userIDTextBox.Text);
				cmd.Parameters.AddWithValue("@Password", passwordTextBox.Text);

				SqlDataReader reader = DA.GetDataReader(cmd);

				if (reader != null)
				{
					// if the database returned no rows then we do not have a valid input
					if (reader.HasRows == false)
					{
						loginFailedLabel.Text = "Login Failed.";
						reader.Close();
						DA.CloseConnection();
						return;
					}
					else
					{
						// grab the loginID
						reader.Read();
						loginID = Int32.Parse(reader["LoginID"].ToString());

						reader.Close();
					}
				}
				else
				{
					loginFailedLabel.Text = DA.WriteError();
					return;
				}

				// user is authenticated, set the PartnerID session variable
				cmd = new SqlCommand("select PartnerID from Partner where LoginID = @LoginID");
				cmd.Parameters.Add("LoginID", loginID);
				reader = DA.GetDataReader(cmd);

				if (reader != null)
				{
					reader.Read();
					Session["PartnerID"] = Int32.Parse(reader["PartnerID"].ToString());

					reader.Close();
					DA.CloseConnection();

				}
				else
				{
					loginFailedLabel.Text = DA.WriteError();
					return;
				}

			}
			catch (SqlException exception)
			{
				loginFailedLabel.Text = DA.WriteError("REACH.Master.cs: Line: " + exception.LineNumber + ": " + exception.ToString(), true);
				return;
			}




Btw DA.WriteError opens the connection on initization and closes it on DA.CloseConnection and on DA.WriteError at the moment.

So should I be closing both the reader and connection on each 'else' and on the 'catch'? Or do I really care?

Thanks!

Tom

Is This A Good Question/Topic? 0
  • +

Replies To: When to close SqlConnection and SqlReader

#2 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7483
  • View blog
  • Posts: 15,510
  • Joined: 16-October 07

Re: When to close SqlConnection and SqlReader

Posted 20 November 2007 - 06:13 PM

Normally, you try to spend as little time with a connection open as possible. You open late and close early. If this means you open and close a few times, well, that's better than leaving something open.

I posted a snippet on this pattern here.

The next thing you can do is see how to shorten the database trips. The code you offer here need only visit the database once.

Given what you've written, I might write it like so:
DataAccess DA = new DataAccess();
SqlDataReader reader = null;
SqlCommand cmd = new SqlCommand("select"
	+ " a.LoginID, a.Login, a.Password, b.PartnerID"
	+ " from Login a"
	+ " left outer join Partner b on a.LoginID=b.LoginID"
	+ " where a.Login = @Login"
	+ " and a.Password = @Password"
);
cmd.Parameters.AddWithValue("@Login", userIDTextBox.Text);
cmd.Parameters.AddWithValue("@Password", passwordTextBox.Text);

try {
	//I'm assuming DA.GetDataReader opens the connection, 
	// otherwise you wont get very far.
	reader = DA.GetDataReader(cmd);
	if (reader.read()) {
		Session["LoginID"] = Int32.Parse(reader["LoginID"].ToString());
		Session["PartnerID"] = Int32.Parse(reader["PartnerID"].ToString());
	} else {
		loginFailedLabel.Text = "Login Failed.";
		//loginFailedLabel.Text = DA.WriteError();
	}
} catch (SqlException exception) {
	loginFailedLabel.Text = DA.WriteError("REACH.Master.cs: Line: " + exception.LineNumber + ": " + exception.ToString(), true);
	return;
} finally {
	if (reader!=null) { reader.Close(); }
	DA.CloseConnection();
}



Hope this helps.
Was This Post Helpful? 0
  • +
  • -

#3 tommahhh   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 19-November 07

Re: When to close SqlConnection and SqlReader

Posted 20 November 2007 - 06:31 PM

Thank you baavgai, that is much cleaner. One last question: does the finally block get executed even with the return statement in the catch block?
Was This Post Helpful? 0
  • +
  • -

#4 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7483
  • View blog
  • Posts: 15,510
  • Joined: 16-October 07

Re: When to close SqlConnection and SqlReader

Posted 20 November 2007 - 08:29 PM

View Posttommahhh, on 20 Nov, 2007 - 08:31 PM, said:

Thank you baavgai, that is much cleaner. One last question: does the finally block get executed even with the return statement in the catch block?


Yep. Returns and exceptions will leave the try block, but not before finally executes.

Think of it as your last chance to clean stuff up. You try to keep the code in the block short and sweet, so there's little chance of bad things happening. Yes, you can nest try blocks into absurdity.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1