Hi,
I have begun using SQLite in my C#/.NET 4.0 project and have created some
code already (which seems to work) but I am unsure about the multithread use
of SQLite in this context. I have tried to get wiser using the ADO.NET
documentation, but much of it is Sql (MS) specific. :-/
I am using a non-shared memory based SQLite db and have in VS2010 created an
entity based on an existing file-based SQlite database. I change the
connection string to :memory: once the entity is final:
// initialization (run only once)
string constring =
"metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;provide
r=System.Data.SQLite;provider connection string=\";data source=:memory:\"";
sqlite = new testEntities(constring);
sqlite.Connection.Open();
/// create table (testEntities is built upon this table)
string query = @"CREATE TABLE IF NOT EXISTS DeviceMap(
UserId TEXT NOT NULL,
UserInstance TEXT NOT NULL,
GatewayHierarchyId TEXT NOT NULL,
GatewayHardwareId TEXT NOT NULL,
DeviceHierarchyId TEXT NOT NULL,
DeviceHardwareId TEXT NOT NULL,
Active BOOL NOT NULL,
PRIMARY KEY (UserId, UserInstance, GatewayHierarchyId,
DeviceHierarchyId)
);";
sqlite.ExecuteStoreCommand(query, null);
// I also created some index
(not shown, but using ExecuteStoreCommand).
I save the sqlite instance for later use. This is the instance that is
access from multiple threads. Is that safe? If maybe, what is required to
make it safe? If not, would a separate testEntities instance make it
thread-safe (:memory: db has to be of the shared type)?
In my later queries, I access sqlite using ExecuteStoreCommand, but also
using Linq, probably from different threads and without any locks (not sure
if it is necessary). I make select, insert, update, delete queries.
Some of the queries Id like to put in transactions. I am not sure how to
make transactions with SQLite in ADO.NET and certainly not if the methods I
make need to be multithread safe. One method could be this (unknown
multithread/transaction safe, would like some details):
public void Set(DeviceMap devObj)
{
// don't know how to use "insert or replace into" via Linq
string query = @"INSERT OR REPLACE INTO DeviceMap
(UserId, UserInstance, GatewayHierarchyId, GatewayHardwareId,
DeviceHierarchyId, DeviceHardwareId, Active)
VALUES({0},{1},{2},{3},{4},{5},1);";
sqlite.ExecuteStoreCommand(query,
devObj.UserId,
devObj.UserInstance,
devObj.GatewayHierarchyId,
devObj.GatewayHardwareId,
devObj.DeviceHierarchyId,
devObj.DeviceHardwareId);
}
A select method could be this:
public List<DeviceMap> GetViaUserIdAndDeviceHardwareId(string
UserId, string DeviceHardwareId)
{
List<DeviceMap> x;
var q = from t in sqlite.DeviceMap
where t.UserId == UserId && t.DeviceHardwareId ==
DeviceHardwareId && t.Active == true
select t;
using (var t = new TransactionScope())
{
x = q.ToList<DeviceMap>();
t.Complete();
}
return x;
}
I have tried to add transaction to it but is it multithread safe? Am I doing
the transaction right?
I hope I am somewhat on the right track, but if not, any help could be
valuable to me.
Cheers,
Bernhard
=============================
Bernhard Mogens Ege
Software developer
E: <mailto:b...@saseco.dk> b...@saseco.dk
M: +45 51 90 50 86
Saseco
Åbogade 15
8200 Aarhus N
Denmark
=============================
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
