Sql connection question

[CSharper]

We have a good size project and there we will connect to sql from
different projects. What is the best practice to connect to sql? I am
pretty sure, writing the hard coded is not a good solution. I could
create class which could encapsulate all the tasks for the project and
we create the instance and consume them in the project. But with this
every time, I try to create a new instance I would end up creating a
new sql connection. Do you think, it is a right way to do it or create
a singleton class and all the project share the same instance of
connection?

Thanks,

 

[sloan]

Use the EnterpriseLibrary.Data "helper" library.

Create your own DAL layer (assembly?) .....

Here is a sample:
http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!139.entry

Also go to the "Bird's Eye View" article I mention at that blog.

........

That's a 1.1 project, check my blog for 2.0 code. I gave you the 1.1
because of the reading content, and the link to the MS Bird's Eye View
article.

 

[Marc Gravell]

Do you think, it is a right way to do it or create

a singleton class and all the project share the same instance of
connection?

In my view, quiet the opposite: keep connections as tightly scoped as
possible; create them, use them and close them - ideally with "using"
etc. Let the inbuilt connection pooling handle the underlying
connection to the server - so even after multiple SqlConnection
instances you may well find you have only used a single underling
*actual* connection. This also lets you use things like transaction
enlistment (TransactionScope) which gets tricky with a single
transaction. It also avoids concurrency issues ("MARS" is good, but
not /that/ good).

Marc

 

[Marc Gravell]

If it wasn't clear - I mean that my common use-case is for a
SqlConnection to not leave a method scope; I might perform a few
operations within that method (although a single operation is fine) -
but I don't expose it outside that method.

 

[Marc Gravell]

with a single transaction

typo: should have read "connection"

 

[Rogelio]

I like to use an XML file with a sql connection in it. and if many
applications use it, place it on a network share or online resource. if
security is an issue I encrypt the value first.

then in my constructor for the class I grab the connection string from he
XML file.

I guess my method is nothing like the consensus here though.

good luck!

 

[Shree]

I like to use an XML file with a sql connection in it. and if many
applications use it, place it on a network share or online resource. if
security is an issue I encrypt the value first.

then in my constructor for the class I grab the connection string from he
XML file.

I guess my method is nothing like the consensus here though.

good luck!

Let me try to answer this question from the ground up...
The connection will require a connection string. You can encrypt this
connection string and store in app.config.(There is special API
in .NET library) Besides this you can keep the other connection
properties in the app.config. Then have a class something like
SqlConnectionFactory that will give you new sqlConnection will the
connection string and properties from the app.config. Close the
connection one you are done using it. I would not recommend using your
own sqlConnection pool. Let ADO.NET manage that.
This is off course not the only way to do it...