?
Solved

SQLConnection class

Posted on 2004-09-14
6
Medium Priority
?
2,280 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 ITIL
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 400 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Suggested Courses
Course of the Month11 days, 18 hours left to enroll

752 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