Singleton+database Coonetion

Can i use singleton design pattern to establish connection to data

Yes you can, the question is why would you want to create your own connection pool (the usual reason for a singleton) when the framework itself already handles it for you?

If this is a web application, be VERY careful when doing this yourself, just let the framework handle it for you.

Imports System.Data.Sql
Imports System.Data.SqlClient

Public Class SqlServerConnection

Private Shared m_Instance As SqlServerConnection
Private Server As String
Private UId As String
Private Database As String
Private Conn As New SqlClient.SqlConnection()
Private Cmd As SqlClient.SqlCommand
Private Sub New(ByVal server As String, ByVal uId As String, ByVal password As String, ByVal Database As String)

'wecan set the connection string by using this arguments also..in real time
Me.Conn.ConnectionString = "server=localhost;uid=;pwd=;database=EmailTemplates"
End Sub
Public Sub Dispose()
' Perform clean up
' at determined point
Me.Conn.Close()
End Sub

Private Sub OpenConnection()
Try
If Me.Conn.State = ConnectionState.Closed Then
Me.Conn.Open()
End If

Catch ex As Exception
Throw ex
End Try

End Sub
Public Shared Function GetInstace(ByVal server As String, ByVal uId As String, ByVal password As String, ByVal Database As String) As SqlServerConnection
If m_Instance Is Nothing Then
Return New SqlServerConnection(server, uId, password, Database)
End If
Return m_Instance
End Function

Public Function ExecuteReader(ByVal cmd As SqlClient.SqlCommand) As SqlDataReader
OpenConnection()
Return cmd.ExecuteReader()
End Function
Public Function ExecuteAndFillAdapter(ByVal cmd As SqlClient.SqlCommand) As DataSet
Dim Dset As New DataSet
OpenConnection()
Dim Adapter As New SqlDataAdapter(cmd)
Adapter.Fill(Dset)
Return Dset
End Function

Public Sub ExecuteNonQuery(ByVal cmd As SqlClient.SqlCommand)
OpenConnection()
cmd.ExecuteNonQuery()
End Sub
Public Sub Close()
Me.Conn.Close()
Me.Cmd.Dispose()
End Sub

End Class

Do this make any sence ?

Public Class SqlServerConnection
Private Shared m_Instance As SqlServerConnection
Private m_source As String
Private m_catalog As String
Private m_integratedSecurity As String
Public Shared Conn As New SqlClient.SqlConnection()
Private Cmd As SqlClient.SqlCommand
Private Sub New(ByVal source As String, ByVal catalog As String, ByVal integratedSecurity As String)
Conn.ConnectionString = "Data Source=" + source + ";Initial Catalog=" + catalog + " ;Integrated Security=" + integratedSecurity
End Sub

Public Shared Function GetConnection(ByVal source As String, ByVal catalog As String, ByVal integratedSecurity As String) As SqlConnection
If m_Instance Is Nothing Then
m_Instance = New SqlServerConnection(source, catalog, integratedSecurity)
End If
Try
If Conn.State = ConnectionState.Closed Then
Conn.Open()
End If
Catch ex As Exception
Throw ex
End Try
Return Conn
End Function

Public Shared Sub Close()
Conn.Close()
End Sub
Public Sub Dispose()
Conn.Close()
End Sub

End Class

................................................

what is about this?

Please comment on it

................................................

Hi sujithukvl,

Forgive me cuz i'm not very familiar with this Singleton+database thing you have mentioned in your post. While i searched thru the internet and collected the following information for u. anyway i still hope it will be helpful to solve your problems. thanks.

http://forums.asp.net/thread/1671366.aspx

http://aspalliance.com/1048_Applying_the_Singleton_Design_Pattern_in_NET_Remoting

http://dotnetslackers.com/community/blogs/simoneb/archive/2007/04/17/ASP.NET-Singleton_2D00_per_2D00_Page-pattern.aspx

Hope my suggestion helps :)

This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.