Solved

SQLConnection class

Posted on 2004-09-14
6
2,275 Views
Last Modified: 2011-10-03
i am building a data access layer in vb.net

what is the best way to manage connections to the sql database.

Should i have a "connection class" that i instanciate that returns me a live connection?
this way i can call it from all over the dataaccess layer?

thanks
0
Comment
Question by:MrKevorkian
6 Comments
 
LVL 10

Expert Comment

by:123654789987
ID: 12052848
Ya the best way is to use the "connection class" since u can open and close connection easily using this. U can verify the state of the connection
0
 
LVL 9

Expert Comment

by:Rahul Goel
ID: 12054155
Hi,

As per question to build a data access layer in vb.net

U can go for a Module or Any XML file....
For maintaing the live connection for ur whole application..

Rahul
0
 
LVL 3

Expert Comment

by:RacinRan
ID: 12054710
I created my own data access layer and did it using a shared function for each of the 3 different types of query's.  It works fast and efficient and will with web or windows.  Here's the class ... use it or modify it:

Public Class SqlConnector

    Public Shared Function ExecuteNonQuery(ByVal Command As SqlCommand, ByVal Connect As String)
        'executes an insert, update, or delete and returns the number of rows affected

        Dim oConn As New SqlConnection(Connect)

        Try
            With Command
                .CommandType = CommandType.StoredProcedure
                .Connection = oConn
            End With

            'Open Connection and return results
            oConn.Open()

            Dim intCount As Integer = Command.ExecuteNonQuery()

            Return intCount

        Catch exSQL As SqlException

Throw exSQL

        Catch ex As Exception

Throw ex

        Finally
            If oConn.State = ConnectionState.Open Then
                oConn.Close()
            End If

            oConn = Nothing

        End Try



    End Function

    Public Shared Function ExecuteDataTable(ByVal Command As SqlCommand, ByVal Connect As String) As DataTable

        Dim oConn As New SqlConnection(Connect)
        Dim oDA As New SqlDataAdapter(Command)
        Dim oDS As New DataSet

        Try
            With Command
                .CommandType = CommandType.StoredProcedure
                .Connection = oConn
            End With

            'Open Connection and return results
            oConn.Open()

            oDA.Fill(oDS)

            Return oDS.Tables(0)

        Catch exSQL As SqlException

           
            Throw exSQL

        Catch ex As Exception

       
            Throw ex

        Finally
            If oConn.State = ConnectionState.Open Then
                oConn.Close()
            End If

            oConn = Nothing
            oDA = Nothing
            oDS = Nothing

        End Try

    End Function

    Public Shared Function ExecuteScalar(ByVal command As SqlCommand, ByVal Connect As String) As Object

        Dim oConn As New SqlConnection(Connect)

        Try
            With command
                .CommandType = CommandType.StoredProcedure
                .Connection = oConn
            End With

            'Open Connection and return results
            oConn.Open()

            Dim obj As Object = command.ExecuteScalar

            Return obj

        Catch exSQL As SqlException

           
            Throw exSQL

        Catch ex As Exception

            Throw ex

        Finally
            If oConn.State = ConnectionState.Open Then
                oConn.Close()
            End If

            oConn = Nothing

        End Try


    End Function

End Class
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 1

Author Comment

by:MrKevorkian
ID: 12055650
hi
thanks everyone.

what i actually was thinking about was a class that returned a live, open SQLconnection.

so you could somehow control all your connections from one place.

rather than opening connections all through your dataaccess layer.

is this the best way to do it?

thanks
0
 
LVL 3

Expert Comment

by:RacinRan
ID: 12055773
Do you want to open a connection then leave that open while the app is running and have all the database commands access the database through that open connection?
0
 
LVL 4

Accepted Solution

by:
MajinLoki earned 100 total points
ID: 12058902
I have done this.  Here is what I did.

the top should have Imports System.Data.SqlClient

My application runs a form called Login as the starting form.  In the Login_Load method, I do a primary connection to my SQL server like this:

Dim connect As SqlConnection

connect = SetConnect("server=servername;trusted_connection=true;database=databasename;")
connect.Open()

Just exchange servername and database name for the appropriate names.  The function SetConnection is located in a module and looks like this:

Module Manager

Private connect As String

Public Function setConnect(ByVal conIn As String) As System.Data.SqlClient.SqlConnection
        connect = conIn
        setConnect = New sql.SqlConnection(conIn)
End Function

Public Function getconnect() As System.Data.SqlClient.SqlConnection
        getconnect = New sql.SqlConnection(getConnectString)
End Function

End Module

There are other ways to do this, but my program crated the initial connection, sets the connection string into the private variable located in the module, and then, any time after the initial load that my program needs a connection, it is as simple as this:

Dim Connect as SqlConnection

connect = getconnect()
connect.open()

any more questions, let me know.

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now