How to manage database connection?

Posted by MF-13693 under VB.NET on 2/14/2012 | Points: 10 | Views : 7865 | Status : [Member] | Replies : 3
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 !!!



Responses

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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Login to post response