Sqlite工具类
1 using System; 2 using System.Collections.Generic; 3 using System.Text; 4 using System.Data.SQLite; 5 using System.Data; 6 using System.Data.SqlClient; 7 8 public class SQLiteHelper 9 { 10 /// <summary> 11 /// 数据库连接 12 /// </summary> 13 private static SQLiteConnection conn; 14 15 private static object _osqlLock = new object(); 16 /// <summary> 17 /// 线程中操作使用的锁 18 /// </summary> 19 public static object oSqlLock 20 { 21 get { return _osqlLock; } 22 set { _osqlLock = value; } 23 } 24 25 /// <summary> 26 /// 初始化数据库链接 27 /// </summary> 28 /// <param name="dbPath">数据库路径</param> 29 public static void Initial(string dbPath) 30 { 31 _osqlLock = new object(); 32 conn = new SQLiteConnection("Data Source=" + dbPath + ";Version=3;"); 33 } 34 35 ~SQLiteHelper() 36 { 37 conn.Dispose(); 38 } 39 40 /// <summary> 41 /// 执行单条SQL语句(增删改) 42 /// </summary> 43 /// <param name="sql">单条SQL语句</param> 44 /// <returns>受影响的总行数</returns> 45 public static int ExecuteNonQuerySingleSql(string sql, params SQLiteParameter[] ps) 46 { 47 int count = 0; 48 lock (_osqlLock) 49 { 50 try 51 { 52 conn.Open(); 53 SQLiteCommand cmd = new SQLiteCommand(sql, conn); 54 if (ps != null) 55 { 56 cmd.Parameters.AddRange(ps); 57 } 58 count = cmd.ExecuteNonQuery(); 59 } 60 catch (Exception ex) 61 { 62 } 63 finally 64 { 65 if (conn.State == ConnectionState.Open) 66 { 67 conn.Close(); 68 } 69 } 70 } 71 return count; 72 } 73 74 /// <summary> 75 /// 执行多条SQL语句(增删改)使用了事务 76 /// </summary> 77 /// <param name="sqlList">SQL语句集合</param> 78 /// <returns>受影响的总行数</returns> 79 public static int ExecuteMultiSql(string[] sqlList) 80 { 81 int count = 0; 82 lock (_osqlLock) 83 { 84 try 85 { 86 conn.Open(); 87 SQLiteTransaction sqltran = conn.BeginTransaction(); 88 SQLiteCommand command = new SQLiteCommand(); 89 command.Connection = conn; 90 command.Transaction = sqltran; 91 for (int i = 0; i < sqlList.Length; i++) 92 { 93 try 94 { 95 command.CommandText = sqlList[i]; 96 count += command.ExecuteNonQuery(); 97 } 98 catch (Exception ex) 99 { 100 } 101 } 102 sqltran.Commit(); 103 } 104 catch (Exception ex) 105 { 106 } 107 finally 108 { 109 if (conn.State == ConnectionState.Open) 110 { 111 conn.Close(); 112 } 113 } 114 } 115 return count; 116 } 117 118 /// <summary> 119 /// 执行多条SQL语句(增删改)使用了事务 120 /// </summary> 121 /// <param name="sqlList">SQL语句集合</param> 122 /// <returns>受影响的总行数</returns> 123 public static int ExecuteMultiSql(List<string> sqlList) 124 { 125 return ExecuteMultiSql(sqlList.ToArray()); 126 } 127 128 /// <summary> 129 /// 数据库查询的方法 130 /// </summary> 131 /// <param name="sql">sql语句</param> 132 /// <returns>数据表</returns> 133 public static DataTable Select(string sql) 134 { 135 DataTable dt = new DataTable(); 136 lock (_osqlLock) 137 { 138 try 139 { 140 DataSet ds = new DataSet(); 141 conn.Open(); 142 SQLiteDataAdapter adapter = new SQLiteDataAdapter(sql, conn); 143 adapter.Fill(ds); 144 if (ds != null) 145 { 146 dt = ds.Tables[0]; 147 } 148 } 149 catch (Exception ex) 150 { 151 } 152 finally 153 { 154 if (conn.State == ConnectionState.Open) 155 { 156 conn.Close(); 157 } 158 } 159 } 160 return dt; 161 } 162 163 /// <summary> 164 /// 查询数据条数 165 /// </summary> 166 /// <param name="sql"></param> 167 /// <returns></returns> 168 public static int SelectDataRowCount(string sql) 169 { 170 try 171 { 172 return Select(sql).Rows.Count; 173 } 174 catch 175 { 176 return 0; 177 } 178 } 179 180 /// <summary> 181 /// 判断数据库中是否存在某表 182 /// </summary> 183 /// <param name="TableName">表名</param> 184 /// <returns>是否存在</returns> 185 public static bool ExistTable(string TableName) 186 { 187 bool IsExist = false; 188 try 189 { 190 string sql = "select count(*) from MSysObjects WHERE MSysObjects.Name Like ‘" + TableName + "‘"; 191 DataTable dt = Select(sql); 192 if (dt.Rows.Count != 0) 193 { 194 if (dt.Rows[0].ItemArray[0].ToString().IndexOf(‘1‘) > -1) 195 { 196 IsExist = true; 197 } 198 } 199 } 200 catch (Exception ex) 201 { 202 IsExist = false; 203 } 204 return IsExist; 205 } 206 }
例:
try { // 初始化数据库链接字符串 SQLiteHelper.Initial(strCon); // 赋值 string sqlUpdata_Tiktok = "UPDATE TiktokTable SET Tiktok=@tiktok"; SQLiteParameter[] ps = { new SQLiteParameter("@tiktok",) }; Convert.ToInt32(SQLiteHelper.ExecuteNonQuerySingleSql(sqlUpdata_Tiktok, ps)) return 1; } catch { return 0; }