[Firebird-net-provider] Select statements and transactions

[Martin <mc...@we...>]

Hi,

      After I have done a select statement through the .net provider do I
have to commit it? and if so what is the procedure.

The reason I ask is that I have a drop down box in my web app which is
populated with a datareader. The code populates the drop down box perfectly,
however I noticed that straight after running this code I was unable to
delete the actual .GDB file from windows explorer (NOTE. at present this is
the only data access function in my entire app). The error message when I
tried to delete the file was "sharing violation","..The source file may be
in use"

Now I don't actual want to delete the file, I am just using this method as a
test to see if there has been any sharing violation caused with my code
(which may cause other issues in the future).

I have also noted the following behaviour. If I run my piece of code that
populates my drop down box once and then go to the "firebird guardian" in
control panel and attempt to stop the firebird service, I get the error
message "Overlapped I/O operation in progress", although upon pressing OK
the firebird service is stopped successfully, and I can now delete the file.

so now I start over again with another copy of my .GDB file, I run the code
to populate the drop down box again and it runs successfully, but after
running it I can't delete the .GDB file because of the same error message
that states there has been a sharing violation , despite the fact that my
connection is now closed.

The code I am running to populate my drop dowm box is

        Dim strSQL As String = "select  ID, DISPLAY_TEXT from EE_SP_NAME
ORDER BY DISPLAY_TEXT"
        Dim objConn As FirebirdSql.Data.Firebird.FbConnection = New
FirebirdSql.Data.Firebird.FbConnection
        Dim objCMD As FirebirdSql.Data.Firebird.FbCommand = New
FirebirdSql.Data.Firebird.FbCommand
        objConn.ConnectionString =
ConfigurationSettings.AppSettings("DbConnectionString")
        objCMD.CommandText = strSQL
        objCMD.CommandType = CommandType.Text
        objConn.Open()
        objCMD.Connection = objConn
        CmbTargetGroup.DataSource = objCMD.ExecuteReader()
        CmbTargetGroup.DataValueField = "ID"
        CmbTargetGroup.DataTextField = "DISPLAY_TEXT"
        CmbTargetGroup.DataBind()
        objConn.Close()

This code seems to close the connection ok (I've never had similar problems
with it when using ms access or ms sql server)

Now I realize that the above code does not have any transactions in it, so I
figured this may be the problem (after all IB Expert always asks me if I
want to commit SELECT statements..although I have no idea why..) so I put
a transaction in the code as follows


        Dim strSQL As String = "select  ID, DISPLAY_TEXT from EE_SP_NAME
ORDER BY DISPLAY_TEXT"
        Dim objConn As FirebirdSql.Data.Firebird.FbConnection = New
FirebirdSql.Data.Firebird.FbConnection
        Dim objCMD As FirebirdSql.Data.Firebird.FbCommand = New
FirebirdSql.Data.Firebird.FbCommand
        Dim objTran As FirebirdSql.Data.Firebird.FbTransaction
        Dim objReader As FirebirdSql.Data.Firebird.FbDataReader
        objConn.ConnectionString =
ConfigurationSettings.AppSettings("DbConnectionString")
        objCMD.CommandText = strSQL
        objCMD.CommandType = CommandType.Text
        objConn.Open()
        objTran = objConn.BeginTransaction()
        objCMD.Connection = objConn
        objCMD.Transaction = objTran
        objReader = objCMD.ExecuteReader()
        CmbTargetGroup.DataSource = objReader
        CmbTargetGroup.DataValueField = "ID"
        CmbTargetGroup.DataTextField = "DISPLAY_TEXT"
        CmbTargetGroup.DataBind()
        objReader.Close()
        objTran.Commit()
        objConn.Close()

But still I get the shareing violation when I go to delete the file.

I would be greatful if somebody could shed some light on this matter for me.
I am assuming this sharing violation is being caused because I am not
handling my data access correctly through the .net provider, but this has me
a little stumped.

many thanks in advance for any help.

martin.