Global database connection in .net

Question

I've inherited an application that uses a global database sqlconnection object in order to access the database from every form in the application. The connection is established when the application starts.

I think to have the connection open all the time it's not good practice and I would prefer to change it so I would open the database connection and close it every time I need to access the database.

So I would like to know if I am in the right here.

This is what I would use instead, any suggestion for improvement is welcome:

Public Sub UpdateDatabase(ByVal command As SqlCommand, ByRef NumError As Double, ByRef DescError As String)
    Using connection As New SqlConnection(connectionString)
        Try
            command.ExecuteNonQuery()
            command.Dispose()
            NumError = 0
            DescError = ""
        Catch ex As Exception
            NumError = Err.Number
            DescError = Err.Description
        End Try
    End Using
End Sub

I send the SqlCommand object to the method instead of a query string because I can add parameters to an SqlCommand object.

Davide Piras · Accepted Answer · 2011-09-02 10:22:33Z

1

the way you are handling the connection with a using is fine and connection will always be closed and disposed for you.

not really good the way you pass the command from the caller, no database engine isolation. For the parameters you can simply pass a list of names and values and add the parameters in your class above and not in the 100.000 places where you call this code.

don't forget to associate the newly opened connection to the command or it will not work.

some people also put another using around the command so command is disposed, inside the using for the connection...

answered Sep 2, 2011 at 10:22

Davide Piras

44.7k10 gold badges103 silver badges150 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

rfc1484 Over a year ago

For the parameters you can simply pass a list of names and values and add the parameters in your class above and not in the 100.000 places where you call this code.

I don't know how to do this, any chance you could point me to an example where this is done?

Davide Piras Over a year ago

@rfc1484: here is your link and sample :) sharpdeveloper.net/content/archive/2007/05/25/…

Collectives™ on Stack Overflow

Global database connection in .net

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related