sqlitehelper java_分享一个SqliteHelper类

1 using System;2 using System.Data.SQLite;3 using System.Data;4 using System.Data.Common;5

6 namespace DXPlatformClientFramework.UC.StatAnalyzeCommon7 {8 public class SqliteHelper : IDisposable9 {10 public SQLiteConnection conn;11

12 public void Dispose()13 {14 Dispose(true);15 GC.SuppressFinalize(this);16 }17

18 protected virtual void Dispose(bool disposing)19 {20 if(disposing)21 if(conn != null)22 {23 conn.Dispose();24 conn = null;25 }26 }27

28 ~SqliteHelper()29 {30 Dispose(false);31 }32

33 ///

34 ///构造函数。35 ///

36 /// 数据库名

37 public SqliteHelper(string dataBaseName)38 {39 string connString = string.Format(@"Data Source={0}", dataBaseName);40 conn =new SQLiteConnection(connString);41 conn.Open();42 }43

44 ///

45 ///手动打开数据库。46 ///

47 public void SqliteOpen()48 {49 if(conn != null&& conn.State ==ConnectionState.Closed)50 conn.Open();51 }52

53 ///

54 ///通过执行SQL语句，获取表中数据。55 ///

56 /// 错误信息

57 /// 执行的SQL语句

58 public DataTable GetDataTable(out string sError, string sSQL)59 {60 DataTable dt = null;61 sError = string.Empty;62 try

63 {64 SQLiteCommand cmd = newSQLiteCommand() { CommandText = sSQL, Connection =conn };65 SQLiteDataAdapter dao =newSQLiteDataAdapter(cmd);66 dt =newDataTable();67 dao.Fill(dt);68 }69 catch(Exception e)70 {71 sError =e.Message;72 }73 return dt;74 }75

76 ///

77 ///通过执行SQL语句，获取表中数据个数。78 ///

79 /// 错误信息

80 /// 执行的SQL语句

81 public int GetDataCount(out string sError, string sSQL)82 {83 DataTable dt =newDataTable();84 sError = string.Empty;85 SQLiteCommand cmd = newSQLiteCommand() { CommandText = sSQL, Connection =conn };86 try

87 {88 SQLiteDataAdapter dao =new SQLiteDataAdapter(cmd);89 dao.Fill(dt);90 cmd.Dispose();91 }92 catch(Exception e)93 {94 sError =e.Message;95 }96 finally{ cmd.Dispose(); }97 return int.Parse(dt.Rows[0][0].ToString());98 }99

100 ///

101 ///通过执行SQL语句，执行insert,update,delete 动作，也可以使用事务。102 ///

103 /// 错误信息

104 /// 执行的SQL语句

105 /// 是否使用事务

106 public bool UpdateData(out string sError, string sSQL, bool bUseTransaction=false)

108 　　　　{109 bool iResult = false;110 sError = string.Empty;111 if(!bUseTransaction)112 {113 try

114 {115 SQLiteCommand comm = new SQLiteCommand(conn) { CommandText =sSQL };116 iResult = comm.ExecuteNonQuery()>0;117 comm.Dispose();118 }119 catch(Exception ex)120 {121 sError =ex.Message;122 }123 }124 else//使用事务

125 {126 DbTransaction trans = null;127 trans =conn.BeginTransaction();128 SQLiteCommand comm = new SQLiteCommand(conn) { CommandText =sSQL };129 try

130 {131 iResult = comm.ExecuteNonQuery()>0;132 trans.Commit();133 }134 catch(Exception ex)135 {136 sError =ex.Message;137 iResult = false;138 trans.Rollback();139 }140 finally{comm.Dispose();trans.Dispose();}141 }142 return iResult;143 }144

145 ///

146 ///使用事务执行多条相同的带参数的SQL语句。147 ///

148 /// SQL语句

149 /// 每次SQL执行的参数

150 public void ExecuteSqlTran(string sqlString, object[][] sqLiteParameters)151 {152 if(sqLiteParameters.Length == 0)153 return;154 using(DbTransaction trans =conn.BeginTransaction())155 {156 if(conn.State !=ConnectionState.Open)157 conn.Open();158 SQLiteCommand cmd =conn.CreateCommand();159 cmd.Connection =conn;160 try

161 {162 for(inti = 0; i < sqLiteParameters[0].Length; i++)163 {164 cmd.Parameters.Add(cmd.CreateParameter());165 }166 //循环

167 foreach(object[] sqlParameters insqLiteParameters)168 {169 ExecuteSqlNonQuery(cmd, sqlString, sqlParameters);170 }171 trans.Commit();172 }173 catch(Exception ex)174 {175 trans.Rollback();176 throw;177 }178 finally

179 {180 cmd.Dispose();trans.Dispose();181 }182 }183 }184

185 ///

186 ///不使用事务执行一条带参数的SQL语句。187 ///

188 /// SQL语句

189 /// SQL执行的参数

190 public void ExecuteSql(string sqlString, object[] sqLiteParameters)191 {192 if(conn.State !=ConnectionState.Open)193 conn.Open();194 SQLiteCommand cmd =conn.CreateCommand();195 cmd.Connection =conn;196 cmd.CommandText =sqlString;197 try

198 {199 for(inti = 0; i < sqLiteParameters.Length; i++)200 {201 cmd.Parameters.Add(cmd.CreateParameter());202 cmd.Parameters[i].Value =sqLiteParameters[i];203 }204 cmd.ExecuteNonQuery();205 }206 finally

207 {208 cmd.Dispose();209 }210 }211

212 private void ExecuteSqlNonQuery(SQLiteCommand cmd, string cmdText, object[] cmdParms)

214 　　　　{215 cmd.CommandText =cmdText;216 if(cmdParms != null)217 {218 for(inti = 0; i < cmdParms.Length; i++)219 {220 cmd.Parameters[i].Value =cmdParms[i];221 }222 }223 cmd.ExecuteNonQuery();224 }225 }226 }