注意：本文使用的SQLite连接库是System.Data.SQLite From sqlite.phxsoftware.com
最近在研究SQLite，研究出一个比较优雅的SQLite与TransactionScope兼容的方案。
最终的业务层代码看起来像下面这样。
1 //init article
2 Article article = new Article();
3 article.Title = "my title";
4 article.Body = "this is body";
5 article.Tags = new List<Tag>();
6 //assemble tags
7 Tag tag1 = new Tag();
8 Tag tag2 = new Tag();
9 tag1.Name = "tag1";
10 tag2.Name = "tag2";
11 article.Tags.Add(tag1);
12 article.Tags.Add(tag2);
13
14 // begin transaction scope
15 using (TransactionScope ts = new TransactionScope())
16 {
17 SQLiteHelper.BeginTransactionScope();
18 //insert article
19 int articleId = Singleton<ArticleDAO>.Instance.Insert(article);
20 int tagId = 0;
21 //tag & relation
22 foreach (Tag tag in article.Tags)
23 {
24 //insert tag
25 tagId = Singleton<TagDAO>.Instance.Inser(tag);
26 //insert relation
27 Singleton<ArticleTagDAO>.Instance.Insert(articleId, tagId);
28 }
29 //commit
30 ts.Complete();
31 SQLiteHelper.EndTransactionScope();
32 }
33 Console.WriteLine("TransactionScope insert successfully");
34 //normal insert
35 // only inser tag
36 Tag myTag = new Tag();
37 myTag.Name = "my tag";
38 Singleton<TagDAO>.Instance.Inser(myTag);
39 Console.WriteLine("normal insert successfully");
40 Console.Read();
2 Article article = new Article();
3 article.Title = "my title";
4 article.Body = "this is body";
5 article.Tags = new List<Tag>();
6 //assemble tags
7 Tag tag1 = new Tag();
8 Tag tag2 = new Tag();
9 tag1.Name = "tag1";
10 tag2.Name = "tag2";
11 article.Tags.Add(tag1);
12 article.Tags.Add(tag2);
13
14 // begin transaction scope
15 using (TransactionScope ts = new TransactionScope())
16 {
17 SQLiteHelper.BeginTransactionScope();
18 //insert article
19 int articleId = Singleton<ArticleDAO>.Instance.Insert(article);
20 int tagId = 0;
21 //tag & relation
22 foreach (Tag tag in article.Tags)
23 {
24 //insert tag
25 tagId = Singleton<TagDAO>.Instance.Inser(tag);
26 //insert relation
27 Singleton<ArticleTagDAO>.Instance.Insert(articleId, tagId);
28 }
29 //commit
30 ts.Complete();
31 SQLiteHelper.EndTransactionScope();
32 }
33 Console.WriteLine("TransactionScope insert successfully");
34 //normal insert
35 // only inser tag
36 Tag myTag = new Tag();
37 myTag.Name = "my tag";
38 Singleton<TagDAO>.Instance.Inser(myTag);
39 Console.WriteLine("normal insert successfully");
40 Console.Read();
SQLiteHelper简化版代码：
代码
1 /// <summary>
2 /// The SQLiteHelper class
3 /// transaction support
4 /// </summary>
5 public abstract class SQLiteHelper
6 {
7 //Database connection strings
8 public static readonly string ConnectionStringLocalTransaction = ConfigurationManager.ConnectionStrings["SQLiteConnectString"].ConnectionString;
9 //use transaction tag
10 private static bool isUseTransactionScope = false;
11 //sqlite connection object
12 private static SQLiteConnection current;
13 //
14 private static object syncLock = new object();
15 /// <summary>
16 ///
17 /// </summary>
18 public static SQLiteConnection Current
19 {
20 get
21 {
22 if (current == null)
23 {
24 lock (syncLock)
25 {
26 if(current == null)
27 current = new SQLiteConnection(ConnectionStringLocalTransaction);
28 }
29 }
30 return current;
31 }
32 }
33 /// <summary>
34 /// begin transaction scope
35 /// </summary>
36 public static void BeginTransactionScope()
37 {
38 isUseTransactionScope = true;
39 if (Current.State != ConnectionState.Open) Current.Open();
40 }
41 /// <summary>
42 /// end transaction scope
43 /// </summary>
44 public static void EndTransactionScope()
45 {
46 isUseTransactionScope = false;
47 if (Current.State != ConnectionState.Closed) Current.Close();
48 }
49 /// <summary>
50 /// create IDbConnection
51 /// </summary>
52 /// <returns></returns>
53 public static IDbConnection CreateDBConnection()
54 {
55 return (IDbConnection)CreateSQLiteConnection();
56 }
57
58 /// <summary>
59 /// Execute a SQLiteCommand that returns a resultset against the database specified in the connection string
60 /// using the provided parameters.
61 /// </summary>
62 /// <remarks>
63 /// e.g.:
64 /// SQLiteDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new SQLiteParameter("@prodid", 24));
65 /// </remarks>
66 /// <param name="connectionString">a valid connection string for a SQLiteConnection</param>
67 /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
68 /// <param name="commandText">the stored procedure name or T-SQL command</param>
69 /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
70 /// <returns>A SQLiteDataReader containing the results</returns>
71 public static SQLiteDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SQLiteParameter[] commandParameters)
72 {
73 SQLiteConnection conn = CreateSQLiteConnection();
74 SQLiteCommand cmd = new SQLiteCommand();
75 // we use a try/catch here because if the method throws an exception we want to
76 // close the connection throw code, because no datareader will exist, hence the
77 // commandBehaviour.CloseConnection will not work
78 try
79 {
80 PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
81 SQLiteDataReader rdr =
82 isUseTransactionScope ? cmd.ExecuteReader() : cmd.ExecuteReader(CommandBehavior.CloseConnection);
83 cmd.Parameters.Clear();
84 return rdr;
85 }
86 catch
87 {
88 conn.Close();
89 throw;
90 }
91 }
92
93 /// <summary>
94 /// Prepare a command for execution
95 /// </summary>
96 /// <param name="cmd">SQLiteCommand object</param>
97 /// <param name="conn">SQLiteConnection object</param>
98 /// <param name="trans">SQLiteTransaction object</param>
99 /// <param name="cmdType">Cmd type e.g. stored procedure or text</param>
100 /// <param name="cmdText">Command text, e.g. Select * from Products</param>
101 /// <param name="cmdParms">SQLiteParameters to use in the command</param>
102 private static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, SQLiteTransaction trans, CommandType cmdType, string cmdText, SQLiteParameter[] cmdParms)
103 {
104
105 if (!isUseTransactionScope && conn.State != ConnectionState.Open)
106 conn.Open();
107
108 cmd.Connection = conn;
109 cmd.CommandText = cmdText;
110
111 if (trans != null)
112 cmd.Transaction = trans;
113
114 cmd.CommandType = cmdType;
115
116 if (cmdParms != null)
117 {
118 foreach (SQLiteParameter parm in cmdParms)
119 cmd.Parameters.Add(parm);
120 }
121 }
122
123 /// <summary>
124 /// create sqlite connection
125 /// </summary>
126 /// <returns></returns>
127 private static SQLiteConnection CreateSQLiteConnection()
128 {
129 return Current;
130 }
131 }
2 /// The SQLiteHelper class
3 /// transaction support
4 /// </summary>
5 public abstract class SQLiteHelper
6 {
7 //Database connection strings
8 public static readonly string ConnectionStringLocalTransaction = ConfigurationManager.ConnectionStrings["SQLiteConnectString"].ConnectionString;
9 //use transaction tag
10 private static bool isUseTransactionScope = false;
11 //sqlite connection object
12 private static SQLiteConnection current;
13 //
14 private static object syncLock = new object();
15 /// <summary>
16 ///
17 /// </summary>
18 public static SQLiteConnection Current
19 {
20 get
21 {
22 if (current == null)
23 {
24 lock (syncLock)
25 {
26 if(current == null)
27 current = new SQLiteConnection(ConnectionStringLocalTransaction);
28 }
29 }
30 return current;
31 }
32 }
33 /// <summary>
34 /// begin transaction scope
35 /// </summary>
36 public static void BeginTransactionScope()
37 {
38 isUseTransactionScope = true;
39 if (Current.State != ConnectionState.Open) Current.Open();
40 }
41 /// <summary>
42 /// end transaction scope
43 /// </summary>
44 public static void EndTransactionScope()
45 {
46 isUseTransactionScope = false;
47 if (Current.State != ConnectionState.Closed) Current.Close();
48 }
49 /// <summary>
50 /// create IDbConnection
51 /// </summary>
52 /// <returns></returns>
53 public static IDbConnection CreateDBConnection()
54 {
55 return (IDbConnection)CreateSQLiteConnection();
56 }
57
58 /// <summary>
59 /// Execute a SQLiteCommand that returns a resultset against the database specified in the connection string
60 /// using the provided parameters.
61 /// </summary>
62 /// <remarks>
63 /// e.g.:
64 /// SQLiteDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new SQLiteParameter("@prodid", 24));
65 /// </remarks>
66 /// <param name="connectionString">a valid connection string for a SQLiteConnection</param>
67 /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
68 /// <param name="commandText">the stored procedure name or T-SQL command</param>
69 /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
70 /// <returns>A SQLiteDataReader containing the results</returns>
71 public static SQLiteDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SQLiteParameter[] commandParameters)
72 {
73 SQLiteConnection conn = CreateSQLiteConnection();
74 SQLiteCommand cmd = new SQLiteCommand();
75 // we use a try/catch here because if the method throws an exception we want to
76 // close the connection throw code, because no datareader will exist, hence the
77 // commandBehaviour.CloseConnection will not work
78 try
79 {
80 PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
81 SQLiteDataReader rdr =
82 isUseTransactionScope ? cmd.ExecuteReader() : cmd.ExecuteReader(CommandBehavior.CloseConnection);
83 cmd.Parameters.Clear();
84 return rdr;
85 }
86 catch
87 {
88 conn.Close();
89 throw;
90 }
91 }
92
93 /// <summary>
94 /// Prepare a command for execution
95 /// </summary>
96 /// <param name="cmd">SQLiteCommand object</param>
97 /// <param name="conn">SQLiteConnection object</param>
98 /// <param name="trans">SQLiteTransaction object</param>
99 /// <param name="cmdType">Cmd type e.g. stored procedure or text</param>
100 /// <param name="cmdText">Command text, e.g. Select * from Products</param>
101 /// <param name="cmdParms">SQLiteParameters to use in the command</param>
102 private static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, SQLiteTransaction trans, CommandType cmdType, string cmdText, SQLiteParameter[] cmdParms)
103 {
104
105 if (!isUseTransactionScope && conn.State != ConnectionState.Open)
106 conn.Open();
107
108 cmd.Connection = conn;
109 cmd.CommandText = cmdText;
110
111 if (trans != null)
112 cmd.Transaction = trans;
113
114 cmd.CommandType = cmdType;
115
116 if (cmdParms != null)
117 {
118 foreach (SQLiteParameter parm in cmdParms)
119 cmd.Parameters.Add(parm);
120 }
121 }
122
123 /// <summary>
124 /// create sqlite connection
125 /// </summary>
126 /// <returns></returns>
127 private static SQLiteConnection CreateSQLiteConnection()
128 {
129 return Current;
130 }
131 }
例子下载
点我
最后说一下解决方案的思路，官方论坛有一个demo是要打开数据库连接的需要在USING transactionscope之前使用using创建一个sqlite数据库连接，然后在操作数据库之前打开即可。根据官方的例子，通过实验，我发现SQLite connection在事务处理开始时只能在开始时打开一次，并且中途不能关闭，事务处理结束后，才可以关闭连接，只有这样，才能使其中的代码正确运行。
