注意:本文使用的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在事务处理开始时只能在开始时打开一次,并且中途不能关闭,事务处理结束后,才可以关闭连接,只有这样,才能使其中的代码正确运行。