Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

use of connections to database

Posted on 2006-11-08
7
169 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
7 Comments
 
LVL 15

Assisted Solution

by:ozymandias
ozymandias earned 25 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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
LVL 8

Accepted Solution

by:
redpipe earned 25 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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

If you are experiencing a similar issue, please ask a related question

This article describes a simple method to resize a control at runtime.  It includes ready-to-use source code and a complete sample demonstration application.  We'll also talk about C# Extension Methods. Introduction In one of my applications…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

839 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