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.
[email protected]<mailto:[email protected]> | www.fugro.com
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
