?
Solved

use of connections to database

Posted on 2006-11-08
7
Medium Priority
?
177 Views
Last Modified: 2010-04-16
I have a page where I am doing 2 seperate queries to an access database. My question is should I close the connection after the first query and the re-open it.....or do I just leave it open, then close at the end.
What is the best and efficient way to do this?

// Connect to the Access database using a query to get all the data from the table.
OleDbDataAdapter myAdapter = new OleDbDataAdapter("SELECT * FROM table1);

// Create and fill a DataSet.
DataSet ds = new DataSet();                  
myAdapter.Fill(ds);

connection.Close(); ////////////////////the magic line, should I have this here or not?

// Bind MyRepeater to the DataSet.                   
MyRepeater.DataSource = ds;
MyRepeater.DataBind();

//Use a variable to hold the SQL statement.
string selectString = "SELECT * FROM [table2]";

//Open the connection.
connection.Open();

//Create an OleDbCommand object.
OleDbCommand cmd = new OleDbCommand(selectString,connection);

OleDbDataReader reader = cmd.ExecuteReader();

while(reader.Read())
{                              
      this.field1.Text = reader["field1"].ToString();
      this.field2.Text = reader["field2"].ToString();
}

//Close the reader and the related connection.
reader.Close();
connection.Close();

I look forward in hearing thoughts on this.
0
Comment
Question by:lobos
  • 3
  • 2
6 Comments
 
LVL 15

Assisted Solution

by:ozymandias
ozymandias earned 100 total points
ID: 17900980
If this is a web page leave it open.
It all happens in the scape of a single http request so there's no point closing and reopening the connection.
0
 
LVL 15

Expert Comment

by:ozymandias
ID: 17900983
scope*
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 17901212
Erm, where do you open it in the first place ? If you are using a DataAdapter then the DataAdapter itself takes care of opening and closing the connection when required.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
LVL 8

Accepted Solution

by:
redpipe earned 100 total points
ID: 17905979
From MSDN: "To ensure that connections are always closed, open the connection inside of a using block, as shown in the following code fragment. Doing so ensures that the connection is automatically closed when the code exits the block."

using (OleDbConnection cn = new OleDbConnection(connectionString))
{
    using (OleDbCommand cm = new OleDbCommand(commandString, cn))
    {
        cn.Open();
        cm.ExecuteNonQuery();
    }
}
0
 
LVL 8

Expert Comment

by:redpipe
ID: 18084235
ozymandias has answered the question on when a connection should be kept open and when to close it, and I have proposed the "best and efficient way" to do it
0
 
LVL 15

Expert Comment

by:ozymandias
ID: 18084369
happy for points to go to redpipe,
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Introduction This article series is supposed to shed some light on the use of IDisposable and objects that inherit from it. In essence, a more apt title for this article would be: using (IDisposable) {}. I’m just not sure how many people would ge…
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
In this video I will demonstrate how to set up Nine, which I now consider the best alternative email app to Touchdown.
Watch the video to learn how one can deal with PST file corruption issue with an outstanding Kernel for Outlook PST Repair Tool easily. Using this tool, non-technical users can swiftly perform the repair process to restore their essential data witho…

590 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question