Solved

SQLConnection class

Posted on 2004-09-14
6
2,274 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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

760 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

23 Experts available now in Live!

Get 1:1 Help Now