System.Data.SQLite

Connection was closed, statement was terminated <br>
   bei System.Data.SQLite.SQLiteDataReader.CheckClosed() in
c:\work\SQLite\dotnet\System.Data.SQLite\SQLiteDataReader.cs:Zeile 163. <br>
   bei System.Data.SQLite.SQLiteDataReader.NextResult() in c:\work\SQLite\dotnet\System.Data.SQLite\SQLiteDataReader.cs:Zeile 852. <br>
   bei System.Data.SQLite.SQLiteDataReader.Close() in c:\work\SQLite\dotnet\System.Data.SQLite\SQLiteDataReader.cs:Zeile 113. <br>
   bei System.Data.Common.DbDataReader.Dispose(Boolean disposing)
   bei System.Data.Common.DbDataReader.Dispose()
 <br>
Code: <br>
 <br>
                using (SQLiteTransaction dbTransaction = dbConnection.BeginTransaction()) <br>
                { <br>
                    using (SQLiteCommand dbCommand = dbConnection.CreateCommand ()) <br>
                    { <br>
                        dbCommand.CommandText = "select * from bandmassquer where idmessungquer=" + messung.Id.ToString("0").Trim() + " order by abszisse;"; <br>
                        using (SQLiteDataReader dbDatareader = dbCommand.ExecuteReader()) <br>
                        { <br>
                            if (!dbDatareader.HasRows) <br>
                            { <br>
                                return ergebnis; <br>
                            } <br>
                            BeanBandmassQuer item; <br>
                            while (dbDatareader.Read()) <br>
                            { <br>
                                try <br>
                                { <br>
                                    item = new BeanBandmassQuer(); <br>
                                    item.Id = getInt32(dbDatareader, 0); <br>
...  <br>
                                    ergebnis.Add(item); <br>
                                } <br>
                                catch (Exception ex) <br>
                                { <br>
                                    Log.LogException(ex); <br>
                                } <br>
                            } <br>
                        } <B><I>// XXX Connection was closed, statement was terminated</I></B> <br>

<hr /><i>mistachkin added on 2011-07-05 09:59:51 UTC:</i><br />
Where was the connection created and opened?  Were any extra options used when opening the connection?


<hr /><i>anonymous added on 2011-07-06 18:06:43 UTC:</i><br />
Hello,
<br>
Joe have 5 Questions:<br>
<br>
The Database-Code is i a class "database"<br>
1. What did the code that opened the connection look like?<br>
============================================================================
<pre>
        /// <summary>Sperrt die Database</summary>
        /// <returns></returns>        
        private bool LockDataBase()
        {
            if (String.IsNullOrEmpty(fileName)) return false;
            String lockFileName = System.IO.Path.ChangeExtension(fileName, ".loc");
            if (System.IO.File.Exists(lockFileName))
            {
                String locktUser = System.IO.File.ReadAllText(lockFileName);
                if (!locktUser.ToLower().Equals(pcAndUser.ToLower()))
                {
                    connectionError = locktUser;
                    return false;
                }
            }
            else
            {
                System.IO.File.WriteAllText(lockFileName, pcAndUser);
            }
            connectionError = "";
            return true;
        }

        /// <summary>unlockt die Datenbank</summary>
        /// <returns></returns>
        private bool UnlockDataBase()
        {
            if (String.IsNullOrEmpty(fileName)) return false;
            String lockFileName = System.IO.Path.ChangeExtension(fileName, ".loc");
            if (System.IO.File.Exists(lockFileName))
            {
                System.IO.File.Delete(lockFileName);
            }
            return true;
        }
</pre>

<pre>
        public bool DBConnect()
        {
            if (String.IsNullOrEmpty(fileName)) return false;
            try
            {
                if (!LockDataBase()) return false;
                connectionString = "Data Source=" + fileName + ";Version=3;UseUTF16Encoding=True;";
                dbConnection = new SQLiteConnection(connectionString);
                dbConnection.Open();
                return true;
            }
            catch (Exception ex)
            {
                Log.LogException(ex);
                return false;
            }
        }
</pre>
<br>
2. Was the connection closed at some point by external code?<br>
============================================================================<br>
<pre>
       public void DBDisconnect()
        {
            if (dbConnection != null)
                if (dbConnection.State != System.Data.ConnectionState.Closed)
                {
                    dbConnection.Close();
                    UnlockDataBase();
                }
        }
</pre>
<br>
3. What does "Log.LogException" do? I assume it does not access the
database?<br>
============================================================================<br>
- is my Class to logging exceptions infos etc. (write to logfile)<br>
<br>
4. Was dbTransaction.Commit or dbTransaction.Rollback called at some point?<br>
============================================================================<br>
the full FunctionCode<br>
<br>
<pre>
        public List<BeanBandmassQuer> getBandmasseQuerAnkommend(BeanMessung messung)
        {
            List<BeanBandmassQuer> ergebnis = new List<BeanBandmassQuer>();
            if (messung == null) return ergebnis;
            if (messung.Id < 1) return ergebnis;
            try
            {
                if (!DBConnect())
                {
                    return ergebnis;
                }
                using (SQLiteTransaction dbTransaction = dbConnection.BeginTransaction())
                {
                    using (SQLiteCommand dbCommand = dbConnection.CreateCommand())
                    {
                        dbCommand.CommandText = "select * from bandmassquer where idmessungquer=" + messung.Id.ToString("0").Trim() + " order by abszisse;";
                        using (SQLiteDataReader dbDatareader = dbCommand.ExecuteReader())
                        {
                            if (!dbDatareader.HasRows)
                            {
                                // Keine Daten in der DB
                                DBDisconnect();
                                return ergebnis;
                            }
                            BeanBandmassQuer item;
                            while (dbDatareader.Read())
                            {
                                try
                                {
                                    item = new BeanBandmassQuer();
                                    item.Id = getInt32(dbDatareader, 0);
                                    item.IdMessung = getInt32(dbDatareader, 1);
                                    item.IdMessungQuer = getInt32(dbDatareader, 2);
                                    item.Abszisse = getDouble(dbDatareader, 3);
                                    item.RiwiPosAbszisse = getDouble(dbDatareader, 4);
                                    item.AbszisseQuer = getDouble(dbDatareader, 5);
                                    item.RiwiPosAbszisseQuer = getDouble(dbDatareader, 6);
                                    ergebnis.Add(item);
                                }
                                catch (Exception ex)
                                {
                                    Log.LogException(ex);
                                }
                            }
                        } // Connection was closed, statement was terminated ==>>
                    }
                    dbTransaction.Rollback();
                }
                DBDisconnect();
                return ergebnis;
            }
            catch (Exception ex)
            {
                Log.LogException(ex);
                return ergebnis;
            }
        }
</pre>
<br>
5. Do you have any additional information or special notes?<br>
============================================================================<br>
I have many other functions with same functionality all the function thow this <br>
exception after the closing brace for<br>
"using (SQLiteDataReader dbDatareader = dbCommand.ExecuteReader())", in the <br>DBDataReader.Dispose().<br>
<br>
I hope i can help.<br>
The exception is not deadly for my project, only unpleasant.<br>
<br>
with best regards<br>
Mario



<hr /><i>mistachkin added on 2011-07-06 20:34:27 UTC:</i><br />
The exception appears to be caused by closing the connection prior to exiting the using block responsible for disposing of the DataReader.

It might be better to use a using block for the connection object as well.  You could create a static method that returns the new connection after performing your extra locking logic.  You could then eliminate the need to manually close the connection from inside your "getBandmasseQuerAnkommend" method.

<hr /><i>mistachkin added on 2011-07-07 01:35:10 UTC:</i><br />
Technically, this is a bug in System.Data.SQLite because the Dispose method is NEVER supposed to throw exceptions.


<hr /><i>mistachkin added on 2011-07-07 02:16:58 UTC:</i><br />
Fixed by check-in [fa8d4d5773], complete with updated test case.