Open Close db Connection: Best Practice

[itHighway]

Hi,

I am a Classic ASP developer and now learning ASP.net.
In Classic ASP, I developed a few functions that Connects, Executes Queries & Disconnect databases like for example:

//To Connection to db
  set objConn = connectDB() 

//Exceute Select Query
  set rRecordSet = executeQuery("sql query here", objConn) 

//Disconnect database
  disConnect(objConn) 

Is there any Expert .Net developer who can guide me on how to create similar functions in .Net so that I do not have to write same db connection code in each page of application.


Thanks!

[davejuk]

Do not access your database at all from within the page.

And why would you want to create your own methods to open and close your database. You can't get much simpler than objConn.Close.

[itHighway]

Actually I am just looking for best way to connect to database and execute sql statement and then close the connection. Right now I have to write following code to each page of website I am working on.

Dim strConnection As String = ConfigurationManager.ConnectionStrings("objConn").ConnectionString
Dim objConn As New SqlConnection(strConnection)

objConn.Open()

Dim dAdapter As New SqlDataAdapter
dAdapter.SelectCommand = New SqlCommand("SELECT * FROM tblCMS WHERE lngContentId=1", objConn)

Dim dSet As New DataSet
dAdapter.Fill(dSet)

objConn.Close()

[itHighway]

Please help.

[behati]

Hi there,

I find that the "using" function keyword is the way to go. Basically it lets you write any method and set it to be "using" your SQLconnection, meaning that the connection is automatically closed upon leaving the method scope. What you do is you create connection Pools depending on a connection string, if the connection string is the same for a connection then the same pool is used, if not a new one is created as described in this MSDN article.

Here's an example from the article, working with Connection pools with the "using" keyword:

using (SqlConnection connection = new SqlConnection(
"Integrated Security=SSPI;Initial Catalog=Northwind"))
{
connection.Open();
// Pool A is created.
}

using (SqlConnection connection = new SqlConnection(
"Integrated Security=SSPI;Initial Catalog=pubs"))
{
connection.Open();
// Pool B is created because the connection strings differ.
}

using (SqlConnection connection = new SqlConnection(
"Integrated Security=SSPI;Initial Catalog=Northwind"))
{
connection.Open();
// The connection string matches pool A.
}

Hope that helps

In all honesty, .NET takes care of most of it for you, it opens/closes the connections as described above and even manages your resources while doing so. Such is the power of .NET

[bitware]

Microsoft ADO. NET is a bad design.
Although easy to learn but programmers spend a lot of work with it just to do simple things.

If you want to simplify programming, you should use the class SQLhelper from Data Access Application Block in Microsoft Enterprise Library .
It is easy to understand and save the energy to type the code.

[IrishChieftain]

Store your connection string in your Web.Config and access it from a DALC class:

static string connectionString = ConfigurationManager.ConnectionStrings["cccConn"].ConnectionString;


using (SqlConnection connection = new SqlConnection(connectionString))
{
...
}