Disconnection Problem In Vb.net with Firebird

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Disconnection Problem In Vb.net with Firebird

SUKHEN DASS
Dear Team,

I am facing a problem of connection still in use after clearing the pool and dispose connection object
but as i close the vb.net application it free the database connection and i can rename the database manually which i want to do at run time

Code as below
Imports System.Data
Imports System.Data.SqlClient
Imports FirebirdSql.Data.FirebirdClient
'Imports FirebirdSql.Data.Firebird

Imports System.Windows.Forms
Imports System.IO
Imports System.Text
Imports System.Globalization

Public myConnection As New FbConnection
Public Function OpenConnection(Optional ByVal ByPass_Message As Boolean = False) As Boolean
        Dim strConString As String = ""

        Try
            If myConnection.State = ConnectionState.Closed Then
                Dim fbConn As New FbConnectionStringBuilder()
                fbConn.Password = Password
                fbConn.UserID = UserID
                fbConn.Pooling = True
                fbConn.Role = "sysadmin"

                fbConn.MaxPoolSize = 20
                'fbConn.ClientLibrary = "fbclient.dll"
                If Is_Local_Connection = True Then
                    fbConn.Database = strLocalDatabase
                Else
                    fbConn.Database = Database_Path

                End If

                fbConn.DataSource = DB_Server
                fbConn.ConnectionLifeTime = 0
                fbConn.ConnectionTimeout = 30

                fbConn.Charset = "UTF8"
                fbConn.ServerType = ServerType
                fbConn.Dialect = 3

                If Val(strPort) = 0 Then
                    strPort = 3050
                End If
                fbConn.Port = strPort

                strConString = fbConn.ToString()

                myConnection.ConnectionString = fbConn.ToString
                If fbConn IsNot Nothing Then
                    fbConn = Nothing
                End If
                myConnection.Open()

            End If
            Return True
        Catch ex As Exception
            If ByPass_Message = False Then
                WriteError(ex.ToString() & vbCrLf & "DB Path :" & Database_Path & "," & vbCrLf & "Local DB:" & strLocalDatabase & vbCrLf & "Conn String:" & Replace(strConString, Password, ""))
                MessageBox.Show("Path is not valid...Please,check data direcotry setting...!" & vbCrLf & vbCrLf & _
                ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End If
            Return False
        End Try
    End Function
Sub Rename_DB
myConnection.ClearAllPools()
            FbConnection.ClearPool(myConnection)
            FbConnection.ClearAllPools()
            myConnection.Close()
            'AppDomain.Unload(AppDomain.CurrentDomain)
            myConnection.Dispose()
            myConnection = Nothing

IO.File.Replace(txtFile_Path.Text, strCurrent_Database_Path, "D:\Backup\Bkp.fdb")--this line is giving error file in used 
End Sub


------------------------------------------------------------------------------
Site24x7 APM Insight: Get Deep Visibility into Application Performance
APM + Mobile APM + RUM: Monitor 3 App instances at just $35/Month
Monitor end-to-end web transactions and take corrective actions now
Troubleshoot faster and improve end-user experience. Signup Now!
http://pubads.g.doubleclick.net/gampad/clk?id=267308311&iu=/4140
_______________________________________________
Firebird-net-provider mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
Reply | Threaded
Open this post in threaded view
|

Re: Disconnection Problem In Vb.net with Firebird

SUKHEN DASS
first i call openconnection method to update some field then i am calling Rename method


On Tuesday, February 2, 2016 11:16 AM, SUKHEN DASS <[hidden email]> wrote:


Dear Team,

I am facing a problem of connection still in use after clearing the pool and dispose connection object
but as i close the vb.net application it free the database connection and i can rename the database manually which i want to do at run time

Code as below
Imports System.Data
Imports System.Data.SqlClient
Imports FirebirdSql.Data.FirebirdClient
'Imports FirebirdSql.Data.Firebird

Imports System.Windows.Forms
Imports System.IO
Imports System.Text
Imports System.Globalization

Public myConnection As New FbConnection
Public Function OpenConnection(Optional ByVal ByPass_Message As Boolean = False) As Boolean
        Dim strConString As String = ""

        Try
            If myConnection.State = ConnectionState.Closed Then
                Dim fbConn As New FbConnectionStringBuilder()
                fbConn.Password = Password
                fbConn.UserID = UserID
                fbConn.Pooling = True
                fbConn.Role = "sysadmin"

                fbConn.MaxPoolSize = 20
                'fbConn.ClientLibrary = "fbclient.dll"
                If Is_Local_Connection = True Then
                    fbConn.Database = strLocalDatabase
                Else
                    fbConn.Database = Database_Path

                End If

                fbConn.DataSource = DB_Server
                fbConn.ConnectionLifeTime = 0
                fbConn.ConnectionTimeout = 30

                fbConn.Charset = "UTF8"
                fbConn.ServerType = ServerType
                fbConn.Dialect = 3

                If Val(strPort) = 0 Then
                    strPort = 3050
                End If
                fbConn.Port = strPort

                strConString = fbConn.ToString()

                myConnection.ConnectionString = fbConn.ToString
                If fbConn IsNot Nothing Then
                    fbConn = Nothing
                End If
                myConnection.Open()

            End If
            Return True
        Catch ex As Exception
            If ByPass_Message = False Then
                WriteError(ex.ToString() & vbCrLf & "DB Path :" & Database_Path & "," & vbCrLf & "Local DB:" & strLocalDatabase & vbCrLf & "Conn String:" & Replace(strConString, Password, ""))
                MessageBox.Show("Path is not valid...Please,check data direcotry setting...!" & vbCrLf & vbCrLf & _
                ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End If
            Return False
        End Try
    End Function
Sub Rename_DB
myConnection.ClearAllPools()
            FbConnection.ClearPool(myConnection)
            FbConnection.ClearAllPools()
            myConnection.Close()
            'AppDomain.Unload(AppDomain.CurrentDomain)
            myConnection.Dispose()
            myConnection = Nothing

IO.File.Replace(txtFile_Path.Text, strCurrent_Database_Path, "D:\Backup\Bkp.fdb")--this line is giving error file in used 
End Sub




------------------------------------------------------------------------------
Site24x7 APM Insight: Get Deep Visibility into Application Performance
APM + Mobile APM + RUM: Monitor 3 App instances at just $35/Month
Monitor end-to-end web transactions and take corrective actions now
Troubleshoot faster and improve end-user experience. Signup Now!
http://pubads.g.doubleclick.net/gampad/clk?id=267308311&iu=/4140
_______________________________________________
Firebird-net-provider mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
Reply | Threaded
Open this post in threaded view
|

Re: Disconnection Problem In Vb.net with Firebird

Jiří Činčura-2
Calling `FbConnection.ClearAllPools` is enough. You don't have to call
anything else. Though you might give server few ms to release the file -
it's not guaranteed that closing all connections will immediately
release the file.

--
Mgr. Jiří Činčura
Independent IT Specialist


------------------------------------------------------------------------------
Site24x7 APM Insight: Get Deep Visibility into Application Performance
APM + Mobile APM + RUM: Monitor 3 App instances at just $35/Month
Monitor end-to-end web transactions and take corrective actions now
Troubleshoot faster and improve end-user experience. Signup Now!
http://pubads.g.doubleclick.net/gampad/clk?id=267308311&iu=/4140
_______________________________________________
Firebird-net-provider mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
Reply | Threaded
Open this post in threaded view
|

Re: Disconnection Problem In Vb.net with Firebird

SUKHEN DASS
its a single user application which only i am using and i am using 
this code before IO.File.Replace is being called... which i forgot to include in the code
For iLoop = 0 To 100
            Application.DoEvents()
Next


On Tuesday, February 2, 2016 11:28 AM, Jiří Činčura <[hidden email]> wrote:


Calling `FbConnection.ClearAllPools` is enough. You don't have to call
anything else. Though you might give server few ms to release the file -
it's not guaranteed that closing all connections will immediately
release the file.

--
Mgr. Jiří Činčura
Independent IT Specialist


------------------------------------------------------------------------------
Site24x7 APM Insight: Get Deep Visibility into Application Performance
APM + Mobile APM + RUM: Monitor 3 App instances at just $35/Month
Monitor end-to-end web transactions and take corrective actions now
Troubleshoot faster and improve end-user experience. Signup Now!
http://pubads.g.doubleclick.net/gampad/clk?id=267308311&iu=/4140
_______________________________________________
Firebird-net-provider mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/firebird-net-provider



------------------------------------------------------------------------------
Site24x7 APM Insight: Get Deep Visibility into Application Performance
APM + Mobile APM + RUM: Monitor 3 App instances at just $35/Month
Monitor end-to-end web transactions and take corrective actions now
Troubleshoot faster and improve end-user experience. Signup Now!
http://pubads.g.doubleclick.net/gampad/clk?id=267308311&iu=/4140
_______________________________________________
Firebird-net-provider mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
Reply | Threaded
Open this post in threaded view
|

Re: Disconnection Problem In Vb.net with Firebird

Mark Rotteveel-2
In reply to this post by Jiří Činčura-2
On Tue, 02 Feb 2016 06:57:03 +0100, Jiří Činčura <[hidden email]> wrote:
> Calling `FbConnection.ClearAllPools` is enough. You don't have to call
> anything else. Though you might give server few ms to release the file -
> it's not guaranteed that closing all connections will immediately
> release the file.

Maybe the problem might be related to not reading the op_response to the
op_detach request (which for Jaybird 3 caused crashes with Firebird 2.1),
maybe connections linger longer than necessary if that response packet
isn't read.

Mark

------------------------------------------------------------------------------
Site24x7 APM Insight: Get Deep Visibility into Application Performance
APM + Mobile APM + RUM: Monitor 3 App instances at just $35/Month
Monitor end-to-end web transactions and take corrective actions now
Troubleshoot faster and improve end-user experience. Signup Now!
http://pubads.g.doubleclick.net/gampad/clk?id=267308311&iu=/4140
_______________________________________________
Firebird-net-provider mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
Reply | Threaded
Open this post in threaded view
|

Re: Disconnection Problem In Vb.net with Firebird

Gerdus van Zyl
In reply to this post by SUKHEN DASS
What I do in my code is issue the database shutdown command(see http://firebirdsql.org/manual/gfix-dbstartstop.html) after closing all connections I can.

Imports FirebirdSql.Data.Services
Dim s As New FbConfiguration()
s.ConnectionString = Me.Connection.ConnectionString
s.DatabaseShutdown(FbShutdownMode.Forced, 1) 'aborts all connections and transactions, 1 second timeout

'Copy File,etc

s.DatabaseOnline() 'bring database online again

But if you want to make a backup... FbBackup is of course much better and safer.
        Dim bkp As New FbBackup()
        bkp.BackupFiles.Add(New FbBackupFile(bkpfn, Nothing))
        bkp.ConnectionString = constr

        bkp.Options = FbBackupFlags.IgnoreLimbo + FbBackupFlags.Expand

        'so that program blocks till complete
        bkp.Verbose = True
        AddHandler bkp.ServiceOutput, Sub(sender As Object, e As ServiceOutputEventArgs)

                                      End Sub
        RaiseEvent BackupProgressEvent(String.Format("{0}: Creating Database Backup...", database.DatabaseName), 0)
        bkp.Execute()


On Tue, Feb 2, 2016 at 8:26 AM, SUKHEN DASS <[hidden email]> wrote:
its a single user application which only i am using and i am using 
this code before IO.File.Replace is being called... which i forgot to include in the code
For iLoop = 0 To 100
            Application.DoEvents()
Next


On Tuesday, February 2, 2016 11:28 AM, Jiří Činčura <[hidden email]> wrote:


Calling `FbConnection.ClearAllPools` is enough. You don't have to call
anything else. Though you might give server few ms to release the file -
it's not guaranteed that closing all connections will immediately
release the file.

--
Mgr. Jiří Činčura
Independent IT Specialist


------------------------------------------------------------------------------
Site24x7 APM Insight: Get Deep Visibility into Application Performance
APM + Mobile APM + RUM: Monitor 3 App instances at just $35/Month
Monitor end-to-end web transactions and take corrective actions now
Troubleshoot faster and improve end-user experience. Signup Now!
http://pubads.g.doubleclick.net/gampad/clk?id=267308311&iu=/4140
_______________________________________________
Firebird-net-provider mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/firebird-net-provider



------------------------------------------------------------------------------
Site24x7 APM Insight: Get Deep Visibility into Application Performance
APM + Mobile APM + RUM: Monitor 3 App instances at just $35/Month
Monitor end-to-end web transactions and take corrective actions now
Troubleshoot faster and improve end-user experience. Signup Now!
http://pubads.g.doubleclick.net/gampad/clk?id=267308311&iu=/4140
_______________________________________________
Firebird-net-provider mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/firebird-net-provider






------------------------------------------------------------------------------
Site24x7 APM Insight: Get Deep Visibility into Application Performance
APM + Mobile APM + RUM: Monitor 3 App instances at just $35/Month
Monitor end-to-end web transactions and take corrective actions now
Troubleshoot faster and improve end-user experience. Signup Now!
http://pubads.g.doubleclick.net/gampad/clk?id=267308311&iu=/4140
_______________________________________________
Firebird-net-provider mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/firebird-net-provider