注意：本文使用的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();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 }例子下载点我
最后说一下解决方案的思路，官方论坛有一个demo是要打开数据库连接的需要在USING transactionscope之前使用using创建一个sqlite数据库连接，然后在操作数据库之前打开即可。根据官方的例子，通过实验，我发现SQLite connection在事务处理开始时只能在开始时打开一次，并且中途不能关闭，事务处理结束后，才可以关闭连接，只有这样，才能使其中的代码正确运行。
转载于:https://www.cnblogs.com/516325/archive/2010/11/10/1873319.html
