setting Best Practice ADO.NET connections

[Steve]

Hi,

I am a relatively new user to vs.NET, and currently developing a
ASP.NET web site using MS access, with the view to moving it to
SQLServer. My problem is, how do I set up the connection to be utilise
connection pooling, and to be easily maintained and modified. I was
thinking of setting up a DSN, which is how I am currently connecting
in ASP but I'm not sure how to do it in .NET. So I created a class
which opens a connection and puts it in a readonly property, imports
the class in each page and sets the connection to that property. but
this is eatreamly sloppy, and cant be the best way to do this.
any help would be greatly appreciated. Thanks

 

[Scott M.]

Hi,

I am a relatively new user to vs.NET, and currently developing a
ASP.NET web site using MS access, with the view to moving it to
SQLServer. My problem is, how do I set up the connection to be utilise
connection pooling,

Connections in .NET automatically use Connection Pooling.

and to be easily maintained and modified.

You can store the connection string in the web.config file, so it doesn't
have to be included (or repeated) in the actual modules.

I was thinking of setting up a DSN, which is how I am currently connecting
in ASP but I'm not sure how to do it in .NET. So I created a class
which opens a connection and puts it in a readonly property, imports
the class in each page and sets the connection to that property. but
this is eatreamly sloppy, and cant be the best way to do this.
any help would be greatly appreciated. Thanks

Personally, I stopped using DSN's about 5 years ago when OLE DB hit the
scene.

 

[Carl Prothman [MVP]]

I am a relatively new user to vs.NET, and currently developing a
ASP.NET web site using MS access, with the view to moving it to
SQLServer. My problem is, how do I set up the connection to be utilise
connection pooling, and to be easily maintained and modified. I was
thinking of setting up a DSN, which is how I am currently connecting
in ASP but I'm not sure how to do it in .NET.

If you are moving to SQL Server 2000, then you will want to use the
Sql Server .NET Data Provider (SqlClient). If you use a DSN, it will
use the older ODBC Sql Driver. You can also use OLEDB SQL Server
Provider. But you best bet is to use SqlClient.
http://www.able-consulting.com/dotnet/adonet/Data_Providers.htm

So I created a class
which opens a connection and puts it in a readonly property, imports
the class in each page and sets the connection to that property. but
this is eatreamly sloppy, and cant be the best way to do this.
any help would be greatly appreciated.

Take advantage of connection pooling by opening, using, and then closing
the connection each time you need to access the database.
http://msdn.microsoft.com/library/e...nectionPoolingForSQLServerNETDataProvider.asp

For best practices, check out
http://msdn.microsoft.com/library/en-us/dnbda/html/daag.asp

--

Thanks,
Carl Prothman
Microsoft ASP.NET MVP

Hire top-notch developers at
http://www.able-consulting.com