[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2283
  • Last Modified:

SQLConnection class

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
MrKevorkian
Asked:
MrKevorkian
1 Solution
 
123654789987Commented:
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
 
Rahul Goel ITILSenior Consultant - DeloitteCommented:
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
 
RacinRanCommented:
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
MrKevorkianAuthor Commented:
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
 
RacinRanCommented:
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
 
MajinLokiCommented:
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now