Please fire at this class...

Hi,
To make data-life a little easier, and not having to declare a
SqlConnection on each page, I came up with this Sql-Data-class:
' --
using System;
using System.Data;
using System.Data.SqlClient;
namespace MyFirstMaybeLeakyClasses
{
public class Data {
public Data()
{
oConn.Open()
}
private static SqlConnection oConn;
public static SqlConnection Connection {
get {
if (oConn == null)
oConn = new SqlConnection([connectionstring));
if (oConn.State != ConnectionState.Open)
oConn.Open();
return oConn;
}
}
}
}
' --
What are the cons of using a SqlConnection like this (as a static)? And
should I use it like this at all?
I understood each user-request will instantiate a new connection, so
multiple readers on the same connection shouldn't be a problem. Or am I
mistaking?
From time to time it throws a SqlException stating multiple
SqlDataReaders are trying to use the same connection. Eventhough I'm
using a DataAdapter... I suspect this happens whenever more that one
user is requesting the page(s).
Finally I have this problem that sometimes it complains it can't get a
connection from the connectionpool. I am closing the connection at the
end of each page. Does a connectionclose take several minutes to fully
terminate the connection or could it be something else?
Thanks in advance!WHOOOA!
Let's... just step away from the keyboard for a moment... What you've
got there is a single connection. Declaring it as static means that it
is shared amongst all instances of that class. If one instance of that
class modifies the connection, all other instances are talking to the
same connection. If someone opens the connection to use in a query,
nobody else can use it.
Why did you do this? Why do you want to declare a SqlConnection on a
page _at all_? Your pages really shouldn't know anything about
SqlConnections or any other of them thar fancy data access classes.
That said, your first sentence gives me hope, Sjaakie: "To make
data-life a little easier, and not having to declare a SqlConnection on
each page, I came up with this Sql-Data-class"
Okay, that's good, really good. Keep your data access code in data
access classes and return business objects from those classes. Accept
business objects as parameters to your save methods. Make sure you're
using try/finally to guarantee the connection is closed like
try
{
cn.open();
cmd.ExecuteNonQuery();
}
finally
{
cn.close();
}
Now your data access code is all nice and neat and out your pages, and
you can use it elsewhere. I'm presuming you can make this data access
class static but I've always been wary of doing so out of sheer
ignorance re:threading (anyone care to enlighten me while we're here?).
I generally use the singleton pattern.
Flinky Wisty Pomm schreef:
> That said, your first sentence gives me hope, Sjaakie: "To make
> data-life a little easier, and not having to declare a SqlConnection on
> each page, I came up with this Sql-Data-class"
> Okay, that's good, really good. Keep your data access code in data
> access classes and return business objects from those classes. Accept
> business objects as parameters to your save methods. Make sure you're
> using try/finally to guarantee the connection is closed like
> try
> {
> cn.open();
> cmd.ExecuteNonQuery();
> }
> finally
> {
> cn.close();
> }
>
> Now your data access code is all nice and neat and out your pages, and
> you can use it elsewhere. I'm presuming you can make this data access
> class static but I've always been wary of doing so out of sheer
> ignorance re:threading (anyone care to enlighten me while we're here?).
> I generally use the singleton pattern.
>
As you might have noticed, I'm somewhat noob in developing OO-applications.
What I'm trying to achieve here is a Data-class which opens a connection
which can be used throughout the entire page/request and is closed at
the end. To me, this looks faster than opening and closing a connection
for each query.
Can you advice me or perhaps point me to a properly written data-layer
example which generally does what I'm looking for?
Thanks
"Sjaakie" <keep@.secret.it> wrote in message
news:44323b7d$0$11061$e4fe514c@.news.xs4all.nl...

> Can you advice me or perhaps point me to a properly written data-layer
> example which generally does what I'm looking for?
http://aspnet.4guysfromrolla.com/articles/070203-1.aspx
Mark Rae schreef:
> "Sjaakie" <keep@.secret.it> wrote in message
> news:44323b7d$0$11061$e4fe514c@.news.xs4all.nl...
>
> http://aspnet.4guysfromrolla.com/articles/070203-1.aspx
>
Thanks!