How to manage database connection?

Posted by MF-13693 under VB.NET on 2/14/2012 | Points: 10 | Views : 9128 | Status : [Member] | Replies : 3

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Hi to all,

I am writing a application which will access the SQL Server database, the application is a desktop application using VB.NET. What is the best way to manage the database connections. Like While the application is lauanched i will open the connection to database untill the application is closed, or i should open and close the database connection for each database operation that i will do. Like for insert, update and delete.

Kindly advice.
Thanks.

M@F
Index was outside the bounds !!!

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Akiii on: 2/14/2012 [Member] Bronze | Points: 25

0	Database connections are valuable resources, once must optimize this connectivity. Open the database, do whatever you want and close the database as soon as possible. That way you will be using disconnected ado.net architecture. Thanks and Regards Akiii MF-13693, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Pradeepkumar417 on: 2/14/2012 [Member] Starter | Points: 25

0	Hi, 1. In windows application you can maintain connection string in App.config file. 2. You don't need to open the connection to database untill the application is closed. 3. By using dataset you can do all database operations like insert,update and delete in a disconnected way. First you have to get the data from database using sqldataadapter and fill the dataset with fill() method of adapter. then close the connection. Then you can insert , update, delete from these dataset. Then by using dataset update() method you can update the data in database. Thanks & Regards, Software Engineer, Pradeep Kumar MF-13693, if this helps please login to Mark As Answer. \| Alert Moderator

Hi,

1. In windows application you can maintain connection string in App.config file.

2. You don't need to open the connection to database untill the application is closed.

3. By using dataset you can do all database operations like insert,update and delete in a disconnected way.

First you have to get the data from database using sqldataadapter and fill the dataset with fill() method of adapter. then close the connection.
Then you can insert , update, delete from these dataset. Then by using dataset update() method you can update the data in database.

Thanks & Regards,
Software Engineer,
Pradeep Kumar

MF-13693, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: MF-13693 on: 2/14/2012 [Member] Starter | Points: 25

0	Thanks for responding. Currently I am using the SQLDataReader for any Select Queries, and SqlCommand for Insert, Update and Delete statements. I am developing a class for all these database operations. Find below the code for it. Imports System.Data Imports System.Data.SqlClient Imports System.Data.OleDb Public Class DBLib Private _constr As String Public sqlDBUser_ As String Public sqlDBPass_ As String Public sqlDBName_ As String Public sqlDBServer_ As String Public Property ConnectionString() As String Get _constr = "Data Source=" & sqlDBServer_ & ";Initial Catalog=" & sqlDBName_ & ";User ID=" & sqlDBUser_ & ";Password=" & sqlDBPass_ & ";Integrated Security=false;" Return _constr End Get Set(ByVal value As String) _constr = value End Set End Property Public Function TestSQLConnection() As Boolean Dim retVal As String = False Try Dim con As New SqlConnection con.ConnectionString = Me.ConnectionString con.Open() retVal = True con.Close() Catch ex As Exception retVal = False End Try Return retVal End Function Public Function SelectRowsSQL(ByVal sSql As String) As SqlDataReader Try Dim con As New SqlConnection con.ConnectionString = Me.ConnectionString con.Open() Dim dr As SqlDataReader Dim cmd As New SqlCommand(sSql, con) dr = cmd.ExecuteReader con.Close() Return dr Catch ex As Exception MsgBox(ex.Message) Return Nothing End Try End Function Public Function InsertSQL(ByVal iSql As String) As Integer Dim retVal As Integer Try Dim con As New SqlConnection con.ConnectionString = Me.ConnectionString con.Open() Dim cmd As New SqlCommand(iSql, con) retVal = cmd.ExecuteNonQuery() con.Close() Catch ex As Exception MsgBox(ex.Message) Return 0 End Try Return retVal End Function Public Sub UpdateSQL(ByVal uSql As String) Try Dim con As New SqlConnection con.ConnectionString = Me.ConnectionString con.Open() Dim cmd As New SqlCommand(uSql, con) cmd.ExecuteNonQuery() con.Close() Catch ex As Exception MsgBox(ex.Message) End Try End Sub Public Function DeleteSQL(ByVal dSql As String) As Integer Dim retVal As Integer Try Dim con As New SqlConnection con.ConnectionString = Me.ConnectionString con.Open() Dim cmd As New SqlCommand(dSql, con) retVal = cmd.ExecuteNonQuery() con.Close() Catch ex As Exception MsgBox(ex.Message) Return 0 End Try Return retVal End Function End Class I want to write good code using the best coding practices, kindly guide me. Your valuable comments on the above code is highly appreciated. Thanks. M@F Index was outside the bounds !!! MF-13693, if this helps please login to Mark As Answer. \| Alert Moderator

Thanks for responding.

Currently I am using the SQLDataReader for any Select Queries, and SqlCommand for Insert, Update and Delete statements.
I am developing a class for all these database operations. Find below the code for it.


Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.OleDb



Public Class DBLib

    Private _constr As String

    Public sqlDBUser_ As String

    Public sqlDBPass_ As String

    Public sqlDBName_ As String

    Public sqlDBServer_ As String

    Public Property ConnectionString() As String



        Get

                _constr = "Data Source=" & sqlDBServer_ & ";Initial Catalog=" & sqlDBName_ & ";User ID=" & sqlDBUser_ & ";Password=" & sqlDBPass_ & ";Integrated Security=false;"          



            Return _constr

        End Get





        Set(ByVal value As String)

            _constr = value

        End Set

   End Property







    Public Function TestSQLConnection() As Boolean

        Dim retVal As String = False



        Try

            Dim con As New SqlConnection

            con.ConnectionString = Me.ConnectionString

            con.Open()

            retVal = True

            con.Close()

        Catch ex As Exception

            retVal = False

        End Try



        Return retVal



    End Function



    Public Function SelectRowsSQL(ByVal sSql As String) As SqlDataReader

        Try

            Dim con As New SqlConnection

            con.ConnectionString = Me.ConnectionString

            con.Open()



            Dim dr As SqlDataReader

            Dim cmd As New SqlCommand(sSql, con)

            dr = cmd.ExecuteReader

            con.Close()



            Return dr



        Catch ex As Exception

            MsgBox(ex.Message)

            Return Nothing

        End Try

    End Function





Public Function InsertSQL(ByVal iSql As String) As Integer



        Dim retVal As Integer



        Try

            Dim con As New SqlConnection

            con.ConnectionString = Me.ConnectionString

            con.Open()



            Dim cmd As New SqlCommand(iSql, con)

            retVal = cmd.ExecuteNonQuery()

            con.Close()

        Catch ex As Exception

            MsgBox(ex.Message)

            Return 0

        End Try



        Return retVal

    End Function







  Public Sub UpdateSQL(ByVal uSql As String)

        Try

            Dim con As New SqlConnection

            con.ConnectionString = Me.ConnectionString

            con.Open()



            Dim cmd As New SqlCommand(uSql, con)

            cmd.ExecuteNonQuery()



            con.Close()

        Catch ex As Exception

            MsgBox(ex.Message)

        End Try

    End Sub



   Public Function DeleteSQL(ByVal dSql As String) As Integer



        Dim retVal As Integer



        Try

            Dim con As New SqlConnection

            con.ConnectionString = Me.ConnectionString

            con.Open()



            Dim cmd As New SqlCommand(dSql, con)

            retVal = cmd.ExecuteNonQuery()



            con.Close()

        Catch ex As Exception

            MsgBox(ex.Message)

            Return 0

        End Try



        Return retVal



    End Function

End Class

I want to write good code using the best coding practices, kindly guide me.
Your valuable comments on the above code is highly appreciated.

Thanks.

M@F
Index was outside the bounds !!!

MF-13693, if this helps please login to Mark As Answer. | Alert Moderator

Latest Posts