Hi Clemens, As per my original post, all C# access code is making extensive use of "using" statements. However we do obviously rely on the connection pool being thread safe, because many threads are writing to different databases (connections) concurrently.
There is no direct relationship between threads and databases. However I do enforce that within a process only one thread can be writing to a specific database (because sqlite does not support parallel writing) at a time. All threads are running on the Pfx thread pool. As data is received from a device it is assigned to a specific data stream. If the buffer for the stream is then full a write action is assigned to the thread pool. When that runs it will call into the ADO.NET code, and send the data to the database for that connection. Here is some test code, that shows how we access the connections normally. Imagine that the "Insert" method is called whenever we have data to flush for a specific database, and there are normally 200-500 database connections active. Obviously at the end of the using statements the connection is effectively handed back to the connection pool, which is outside of my direct control. public void Insert([NotNull] IEnumerable<T> records) { ArgumentHelper.AssertNotNull(records, "records"); var sb = new StringBuilder(); sb.AppendLine("INSERT INTO"); sb.AppendLine(m_TableName); sb.AppendLine("(identity, time, binary)"); sb.AppendLine("VALUES"); sb.AppendLine("(@identity, @time, @binary)"); using (IDbConnection connection = CreateConnection()) { connection.Open(); using (IDbTransaction transaction = connection.BeginTransactionSerializable()) { using (IDbCommand command = CreateCommand(connection)) { command.Transaction = transaction; command.CommandText = sb.ToString(); command.Parameters.Add(new SQLiteParameter("@identity", DbType.Int32)); command.Parameters.Add(new SQLiteParameter("@time", DbType.Int64)); command.Parameters.Add(new SQLiteParameter("@binary", DbType.Binary)); if (m_Factory.Configuration.UsePreparedStatements) { command.Prepare(); } var stream = new ByteStream(); IDataParameterCollection parameters = command.Parameters; IDataParameter parameter01 = ((IDbDataParameter) parameters[1]); IDataParameter parameter02 = ((IDbDataParameter) parameters[2]); foreach (var record in records) { stream.Clear(); record.Serialize(stream); parameter01.Value = record.TimeOfValidity.TaiTicks; parameter02.Value = stream.GetContents(); command.ExecuteNonQuery(); } } try { transaction.Commit(); } catch { transaction.Rollback(); throw; } } } } [NotNull] public IDbConnection CreateConnection() { var connection = new SQLiteConnection { ConnectionString = m_Builder.ConnectionString }; return connection; } [NotNull] public IDbCommand CreateCommand([NotNull] IDbConnection connection) { IDbCommand command = new SQLiteCommand(); command.Connection = connection.NotNull("connection"); return command; } Kind Regards, Barry Roberts. b.robe...@fugro.com<mailto:b.robe...@fugro.com> | www.fugro.com _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users