System.Data.SQLite

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

anonymous added on 2011-07-06 18:06:43 UTC:
Hello,
Joe have 5 Questions:

The Database-Code is i a class "database"
1. What did the code that opened the connection look like?
============================================================================

        /// 
Sperrt die Database
        /// <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;
        }

/// 
unlockt die Datenbank
        /// <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;
        }

        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;
            }
        }

       public void DBDisconnect()
        {
            if (dbConnection != null)
                if (dbConnection.State != System.Data.ConnectionState.Closed)
                {
                    dbConnection.Close();
                    UnlockDataBase();
                }
        }

        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;
            }
        }

mistachkin added on 2011-07-06 20:34:27 UTC:
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.

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

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