none
Connection untill its open other application quit RRS feed

  • Question

  • Hello,

    I got some question to you guys, i have simplestatment (oracle) but i think it's not important it is sql server, oracle or whatever..

    connection.open()if connection.state = open..........else..........

    I wonder if i could implement in else if connection is closed to try to open it again because i have automation report and if the connection will not open then its a problem is that possible to implement any loop until to connection will open? And then if the loop will never stop if for example server is down then break the application...

    best regards,

    Jimmy

    Sunday, May 27, 2012 8:18 PM

Answers

  • Hello, I am not sure if this is what you want, there are two language extension methods for VS2008 or higher that check if an OleDb or SqlClient connection state is close and if so Open the connection.

    Code module:

    <System.Diagnostics.DebuggerStepThrough()> _
    <Runtime.CompilerServices.Extension()> _
    Public Function IsClosed(ByVal sender As SqlClient.SqlConnection) As Boolean
        Return sender.State = ConnectionState.Closed
    End Function
    <System.Diagnostics.DebuggerStepThrough()> _
    <Runtime.CompilerServices.Extension()> _
    Public Function IsClosed(ByVal sender As OleDb.OleDbConnection) As Boolean
        Return sender.State = ConnectionState.Closed
    End Function

    Usage:

    Private Sub Demo()
        Dim MsAccessConnection As New OleDb.OleDbConnection
        Dim SqlConnection As New SqlClient.SqlConnection
        '
        ' do some work...
        '
        If MsAccessConnection.IsClosed Then
            MsAccessConnection.Open()
        End If
        If SqlConnection.IsClosed Then
            SqlConnection.Open()
        End If
    End Sub

    The remaining information is abstract and most likely more than you want.

    You could create a class the uses IDbConnection and set the provider the use a language extension method setup as shown above. Below shows the basics for such a class. I have this class to work no matter if IBM-DB2 driver is installed or not.

    Option Explicit On
    Option Strict On
    Imports System.Data.Odbc
    Imports System.Data.SqlClient
    Imports System.Data.OleDb
    #If KSG_USE Then
    Imports IBM.Data.DB2.iSeries
    #End If
    #Region " DatasourceManager "
    Public Class DatasourceManager
    #Region " Private properties "
        Private ConnectionString As String
        Private Connection As IDbConnection
    #If KSG_USE Then
        Private ConnectionTypes() As Type = _
           { _
              GetType(OleDbConnection), _
              GetType(SqlConnection), _
              GetType(OdbcConnection) _
           , GetType(iDB2Connection)}
    #Else
        Private ConnectionTypes() As Type = _
           { _
              GetType(OleDbConnection), _
              GetType(SqlConnection), _
              GetType(OdbcConnection)
          }
    #End If
    #End Region
    #Region " Constructors "
        ''' <summary>
        ''' New instance using of the Datasource manager using the specified provider and connection string.
        ''' </summary>
        ''' <param name="ProviderType">Type of data provider (e.g. ODBC, OLEDB, SQLClient, IDB2...)</param>
        ''' <param name="ConnectionString">Connection string (e.g. DSN=myODBCDataSource)</param>
        ''' <remarks></remarks>
        Public Sub New(ByVal ProviderType As EnumProviders, ByVal ConnectionString As String)
            Me.ConnectionString = ConnectionString
            Try
                Me.Connection = CType(Activator.CreateInstance(ConnectionTypes(CInt(ProviderType))), IDbConnection)
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
        End Sub
    #End Region
    #Region " Public properties "
    #If KSG_USE Then
        ''' <summary>
        ''' Database provider types
        ''' </summary>
        ''' <remarks>Database provider types</remarks>
        Public Enum EnumProviders
            OLEDB
            SQLClient
            ODBC
            IDB2
        End Enum
    #Else
        ''' <summary>
        ''' Database provider types
        ''' </summary>
        ''' <remarks>Database provider types</remarks>
        Public Enum EnumProviders
            OLEDB
            SQLClient
            ODBC
        End Enum
    #End If
    #End Region
    #Region " Public Methods "
        ''' <summary>
        ''' Get an initialized connection. 
        ''' </summary>
        ''' <returns>Initialized connection object</returns>
        ''' <remarks></remarks>
        Public Function GetConnection() As IDbConnection
            If Connection.State = ConnectionState.Open Then
                Return Connection
            Else
                Connection.ConnectionString = Me.ConnectionString
                Connection.Open()
            End If
            Return Connection
        End Function
        ''' <summary>
        ''' Close connection
        ''' </summary>
        ''' <remarks>Reminder method to close the connection when done with it.</remarks>
        Public Sub CloseConnection()
            Me.Connection.Close()
        End Sub
    #End Region
    End Class
    #End Region

    Now we would need an extension for IDbConnection

        <System.Diagnostics.DebuggerStepThrough()> _
        <Runtime.CompilerServices.Extension()> _
        Public Function IsClosed(ByVal sender As IDbConnection) As Boolean
            Return sender.State = ConnectionState.Closed
        End Function

    We could check for more as shown below and chain back to the above extension

        <System.Diagnostics.DebuggerStepThrough()> _
        <Runtime.CompilerServices.Extension()> _
        Public Function IsNotReady(ByVal sender As IDbConnection) As Boolean
            Return sender Is Nothing OrElse sender.State = ConnectionState.Broken OrElse sender.IsClosed
        End Function

    Now put the above altogether (please note do not try to implement as I have not provided the new constructor complete code as it reads from xml file which stores multiple data connections as defined in the Enum DataBaseEnvironment as any good project should have at least a development and production environment while our team adds a third for QAC) and have a Singleton method so that when using this code you never issue an Open command.

    Public Enum DataBaseEnvironment
        Production
        Development
        QualityAssurance
    End Enum
    ''' <summary>
    ''' Singleton connection class with enumerations based on Rance's Fed State connection class
    ''' </summary>
    ''' <remarks>
    ''' The New constructor should be reading connection strings from a external source 
    ''' such as a XML configuration file as hard coding is of course less flexible.
    ''' </remarks>
    Public Class ConnectionInfo
        Private Shared mInstance As ConnectionInfo
        Private mProductionConnection As IDbConnection
        Private mQAC_Connnection As IDbConnection
        Private mTestConnection As IDbConnection
        Private mProductionConnectionString As String
        Private mQAC_ConnectionString As String
        Private mTestConnectionString As String
        Public ReadOnly Property MainConnection(ByVal environment As DataBaseEnvironment) As IDbConnection
            Get
                Select Case environment
                    ' Production and QAC use same logic as Test
                    Case DataBaseEnvironment.Production
                        Throw New Exception("Production not implemented")
                    Case DataBaseEnvironment.QualityAssurance
                        Throw New Exception("QAC Not implemented")
                    Case DataBaseEnvironment.Development
                        If mTestConnection.IsNotReady Then
                            Try
                                mTestConnection.Dispose()
                                mTestConnection = Nothing
                            Catch ex As Exception
                            End Try
                            mTestConnection = New DatasourceManager(DatasourceManager.EnumProviders.OLEDB, mTestConnectionString).GetConnection()
                        End If
                        Return mTestConnection
                    Case Else
                        Return Nothing
                End Select
            End Get
        End Property
        Public Shared Function GetInstance() As ConnectionInfo
            If mInstance Is Nothing Then
                mInstance = New ConnectionInfo()
            End If
            Return mInstance
        End Function
        ''' <summary>
        ''' Read settings from external file or database...
        ''' </summary>
        ''' <remarks>
        ''' I use an XML file to read our connection string which is fully
        ''' demoed in ConnectionSettingFromXML project. In an actual application
        ''' all three environments would be setup, here only development is.
        ''' </remarks>
        Protected Sub New()
            Dim Sections As New AppSections("ApplicationSettings.xml")
            mProductionConnectionString = ""
            mQAC_ConnectionString = ""
            mTestConnectionString = Sections.Configuration("Main", "Development").ConnectionString
        End Sub
    End Class


    KSG

    Monday, May 28, 2012 5:33 AM

All replies

  • Hey JimmyJimm,

    I wonder if i could implement in else if connection is closed to try to open it again because i have automation report and if the connection will not open then its a problem is that possible to implement any loop until to connection will open? And then if the loop will never stop if for example server is down then break the application...


    There are many of the connection states possible so don't try to medel with them unnesserly,

    like these

    every time you do the operations check if its open or not

    using(SqlConnection SqlCon=new SqlConnection)//This Ensures you're connection is closed at any unexpected events or at all costs
    {
    try{
     if (!SqlCon.State.Equals(ConnectionState.Open))
                            {
                                SqlCon.Open();
                            }
    while(ConnectionState.Open)
    {
    //do all your operations here
    }
    }
    cath(SqlException ex)
    {
       if(!SqlCon.State.Equals(ConnectionState.Open))//This is not true then their might be other problems 
          {
                //Do failure operations here....
          }
    }
    }

    hope this helps you...


    $Kracker

    Monday, May 28, 2012 5:04 AM
  • Ok, but what if the first timethe connection will not open? I have automation program in scheduler, so if it will not open my work is down... Mayby is some try to open it again if it failered of open for first time?
    Monday, May 28, 2012 5:27 AM
  • Hello, I am not sure if this is what you want, there are two language extension methods for VS2008 or higher that check if an OleDb or SqlClient connection state is close and if so Open the connection.

    Code module:

    <System.Diagnostics.DebuggerStepThrough()> _
    <Runtime.CompilerServices.Extension()> _
    Public Function IsClosed(ByVal sender As SqlClient.SqlConnection) As Boolean
        Return sender.State = ConnectionState.Closed
    End Function
    <System.Diagnostics.DebuggerStepThrough()> _
    <Runtime.CompilerServices.Extension()> _
    Public Function IsClosed(ByVal sender As OleDb.OleDbConnection) As Boolean
        Return sender.State = ConnectionState.Closed
    End Function

    Usage:

    Private Sub Demo()
        Dim MsAccessConnection As New OleDb.OleDbConnection
        Dim SqlConnection As New SqlClient.SqlConnection
        '
        ' do some work...
        '
        If MsAccessConnection.IsClosed Then
            MsAccessConnection.Open()
        End If
        If SqlConnection.IsClosed Then
            SqlConnection.Open()
        End If
    End Sub

    The remaining information is abstract and most likely more than you want.

    You could create a class the uses IDbConnection and set the provider the use a language extension method setup as shown above. Below shows the basics for such a class. I have this class to work no matter if IBM-DB2 driver is installed or not.

    Option Explicit On
    Option Strict On
    Imports System.Data.Odbc
    Imports System.Data.SqlClient
    Imports System.Data.OleDb
    #If KSG_USE Then
    Imports IBM.Data.DB2.iSeries
    #End If
    #Region " DatasourceManager "
    Public Class DatasourceManager
    #Region " Private properties "
        Private ConnectionString As String
        Private Connection As IDbConnection
    #If KSG_USE Then
        Private ConnectionTypes() As Type = _
           { _
              GetType(OleDbConnection), _
              GetType(SqlConnection), _
              GetType(OdbcConnection) _
           , GetType(iDB2Connection)}
    #Else
        Private ConnectionTypes() As Type = _
           { _
              GetType(OleDbConnection), _
              GetType(SqlConnection), _
              GetType(OdbcConnection)
          }
    #End If
    #End Region
    #Region " Constructors "
        ''' <summary>
        ''' New instance using of the Datasource manager using the specified provider and connection string.
        ''' </summary>
        ''' <param name="ProviderType">Type of data provider (e.g. ODBC, OLEDB, SQLClient, IDB2...)</param>
        ''' <param name="ConnectionString">Connection string (e.g. DSN=myODBCDataSource)</param>
        ''' <remarks></remarks>
        Public Sub New(ByVal ProviderType As EnumProviders, ByVal ConnectionString As String)
            Me.ConnectionString = ConnectionString
            Try
                Me.Connection = CType(Activator.CreateInstance(ConnectionTypes(CInt(ProviderType))), IDbConnection)
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
        End Sub
    #End Region
    #Region " Public properties "
    #If KSG_USE Then
        ''' <summary>
        ''' Database provider types
        ''' </summary>
        ''' <remarks>Database provider types</remarks>
        Public Enum EnumProviders
            OLEDB
            SQLClient
            ODBC
            IDB2
        End Enum
    #Else
        ''' <summary>
        ''' Database provider types
        ''' </summary>
        ''' <remarks>Database provider types</remarks>
        Public Enum EnumProviders
            OLEDB
            SQLClient
            ODBC
        End Enum
    #End If
    #End Region
    #Region " Public Methods "
        ''' <summary>
        ''' Get an initialized connection. 
        ''' </summary>
        ''' <returns>Initialized connection object</returns>
        ''' <remarks></remarks>
        Public Function GetConnection() As IDbConnection
            If Connection.State = ConnectionState.Open Then
                Return Connection
            Else
                Connection.ConnectionString = Me.ConnectionString
                Connection.Open()
            End If
            Return Connection
        End Function
        ''' <summary>
        ''' Close connection
        ''' </summary>
        ''' <remarks>Reminder method to close the connection when done with it.</remarks>
        Public Sub CloseConnection()
            Me.Connection.Close()
        End Sub
    #End Region
    End Class
    #End Region

    Now we would need an extension for IDbConnection

        <System.Diagnostics.DebuggerStepThrough()> _
        <Runtime.CompilerServices.Extension()> _
        Public Function IsClosed(ByVal sender As IDbConnection) As Boolean
            Return sender.State = ConnectionState.Closed
        End Function

    We could check for more as shown below and chain back to the above extension

        <System.Diagnostics.DebuggerStepThrough()> _
        <Runtime.CompilerServices.Extension()> _
        Public Function IsNotReady(ByVal sender As IDbConnection) As Boolean
            Return sender Is Nothing OrElse sender.State = ConnectionState.Broken OrElse sender.IsClosed
        End Function

    Now put the above altogether (please note do not try to implement as I have not provided the new constructor complete code as it reads from xml file which stores multiple data connections as defined in the Enum DataBaseEnvironment as any good project should have at least a development and production environment while our team adds a third for QAC) and have a Singleton method so that when using this code you never issue an Open command.

    Public Enum DataBaseEnvironment
        Production
        Development
        QualityAssurance
    End Enum
    ''' <summary>
    ''' Singleton connection class with enumerations based on Rance's Fed State connection class
    ''' </summary>
    ''' <remarks>
    ''' The New constructor should be reading connection strings from a external source 
    ''' such as a XML configuration file as hard coding is of course less flexible.
    ''' </remarks>
    Public Class ConnectionInfo
        Private Shared mInstance As ConnectionInfo
        Private mProductionConnection As IDbConnection
        Private mQAC_Connnection As IDbConnection
        Private mTestConnection As IDbConnection
        Private mProductionConnectionString As String
        Private mQAC_ConnectionString As String
        Private mTestConnectionString As String
        Public ReadOnly Property MainConnection(ByVal environment As DataBaseEnvironment) As IDbConnection
            Get
                Select Case environment
                    ' Production and QAC use same logic as Test
                    Case DataBaseEnvironment.Production
                        Throw New Exception("Production not implemented")
                    Case DataBaseEnvironment.QualityAssurance
                        Throw New Exception("QAC Not implemented")
                    Case DataBaseEnvironment.Development
                        If mTestConnection.IsNotReady Then
                            Try
                                mTestConnection.Dispose()
                                mTestConnection = Nothing
                            Catch ex As Exception
                            End Try
                            mTestConnection = New DatasourceManager(DatasourceManager.EnumProviders.OLEDB, mTestConnectionString).GetConnection()
                        End If
                        Return mTestConnection
                    Case Else
                        Return Nothing
                End Select
            End Get
        End Property
        Public Shared Function GetInstance() As ConnectionInfo
            If mInstance Is Nothing Then
                mInstance = New ConnectionInfo()
            End If
            Return mInstance
        End Function
        ''' <summary>
        ''' Read settings from external file or database...
        ''' </summary>
        ''' <remarks>
        ''' I use an XML file to read our connection string which is fully
        ''' demoed in ConnectionSettingFromXML project. In an actual application
        ''' all three environments would be setup, here only development is.
        ''' </remarks>
        Protected Sub New()
            Dim Sections As New AppSections("ApplicationSettings.xml")
            mProductionConnectionString = ""
            mQAC_ConnectionString = ""
            mTestConnectionString = Sections.Configuration("Main", "Development").ConnectionString
        End Sub
    End Class


    KSG

    Monday, May 28, 2012 5:33 AM
  • If there is no exception then you don't have to worry if it's open or not ... it'll be open as we're using

    try{ conn.open();//this will throw the exception if Open() is not Succefull}

    cath(SqlException ex)
    {
       if(!SqlCon.State.Equals(ConnectionState.Open))//This is not true then their might be other problems 
          {
                //Do failure operations here....
          }
    }


    $Kracker


    • Edited by SKracker Monday, May 28, 2012 5:42 AM Correct sequence
    Monday, May 28, 2012 5:41 AM
  • I am confused for now :) So what your opinion what should i use?
    Monday, May 28, 2012 6:27 AM
  • I am confused for now :) So what your opinion what should i use?
    My suggestion is study the code and only use code that is not confusing to you. Part of being a developer is taking code that you don't understand and learning about said code and if ther are multiple solutions learn them and then decide which is best for you.

    KSG

    Monday, May 28, 2012 6:44 AM
  • Try mine .. put break points and keep hitting F10 you'll get work flow and get the hang of it..

    using(SqlConnection SqlCon=new SqlConnection)//This Ensures you're connection is closed at any unexpected events or at all costs
    {
    try{
     if (!SqlCon.State.Equals(ConnectionState.Open))
                            {
                                SqlCon.Open();//This will throw exception if not excuted properly
                            }
    while(ConnectionState.Open)
    {
    //do all your operations here
    }
    }
    cath(SqlException ex)//this will catch the exception
    {
       if(!SqlCon.State.Equals(ConnectionState.Open))//This is not true then their might be other problems 
          {
                //Do failure operations here....
          }
    }
    }


    $Kracker



    • Edited by SKracker Monday, May 28, 2012 7:05 AM
    Monday, May 28, 2012 7:03 AM
  • Hello SKracker, i am intresting of your soultion, but i wonder if the connection for example for first time will failed, is your loop will try to open it again like i see or the error will take the application to hell :(?

    Please answer my question..

    My actually version of what i am using looks exactly like this, if you could help me implement your soulution on my case in vb.net please:

    Dim conn As New OracleConnection(oradb)
    
                Try
                    conn.ConnectionString = oradb
                    conn.Open()
    
                    If conn.State = ConnectionState.Open Then
    
    Dim sql As String =
    
    Dim cmd As New OracleCommand(sql, conn)
                        cmd.CommandType = CommandType.Text
    
                        Dim DA As OracleDataAdapter
                        Dim DS As New DataSet
    
                        DA = New OracleDataAdapter(cmd)
                        DS = New DataSet
    
    DA.Fill(DS, "PDetails")
    
    rest of my code...
    
    Else
    
    
                    End If
    
                Catch ex As OracleException
                    msgbox (ex.tostring)
                Finally
                    ' Close Connection  
                    conn.Close()
    
    
                End Try
    So as you can see i got one chance to connect and if not .. then nothing.. Please help me rebuilt this code

    • Edited by JimmyJimm Thursday, May 31, 2012 9:48 AM
    Thursday, May 31, 2012 9:47 AM
  • Catch ex As OracleException msgbox (ex.tostring) Finally ' Close Connection conn.Close()

    after catching exception

    catch(SqlException ex) { MessageBox.Show(ex.Message);//This will give time to repair your conn or any other sql related problems

    conn.close();

    OracleConnection(oradb);

    }

    //it'll keep on doing till infinity...

    so create CustomException class inheritting the Exception

    catch(CustomException ex)

    {

    conn.Close();//To make sure your program breaks at some point

    }





    $Kracker




    • Edited by SKracker Thursday, May 31, 2012 10:29 AM
    Thursday, May 31, 2012 10:22 AM
  • Could you please help me with complete my actual code to your proposition?
    Thursday, May 31, 2012 12:41 PM
  • Hi Jimmy,

    Does this issue be resolved? If it is then please mark the reply which answered your question else please update the latest status of this issue here.

    Best Regards,


    Tony Xiao [MSFT]
    MSDN Community Support | Feedback to us

    Thursday, June 7, 2012 10:22 AM
    Moderator