Share ADO.NET database connection across projects.

[Guest]

Hi,

I have a C# Solution/Application that contain 4 projects.

Each of these projects needs at some time to access the same database.

I would like to know how to share a single connection between these projects
as i assume this would be more efficient than having a separate connection
string for each project.

Also what is the best way to control the opening and closing of the database
in these projects through this connection string.

When is it best to access the database directly and when is it best to go
through a dataset?

Thanks In Advance
Macca

 

[Nicholas Paldino [.NET/C# MVP]]

Macca,

In order to share the same connection across projects, I would have one
assembly that all the projects reference. This assembly would have a type
in it which would expose a static member which would return your connection
to you, configured properly.

This leads into how to control the opening and closing of the
connection. The method should return a new connection, not an instance of a
connection you share among everyone. Whether or not to open the connection
before passing it back is up to you.

Now, when you return your connection, you should use it in a using
statement, so that the connection is disposed/closed properly when you are
done with it. Whenever you get a database connection, you should get the
connection, do your work, and close it as soon as possible.

As for when it is best to access the database directly, I don't know
what you mean by that. In .NET, when you get data, you get a DataSet, which
is populated with the data, or use a data reader, which allows you to cycle
through the returned result set. The data adapter, in reality, just cycles
through a data adapter and populates the data set.

As for updating a database, you can use command objects directly, but
using data adapters is much easier.

Hope this helps.