Solved

SQLConnection class

Posted on 2004-09-14
6
2,277 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 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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

 
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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

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 …
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

828 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