Sqlite操作帮助类
sqlite帮助类
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text; 5 using System.Threading.Tasks; 6 using System.Data.SQLite; 7 using System.Data; 8 9 namespace SqliteDemo 10 { 11 /// <summary> 12 /// SQLite 操作类 13 /// </summary> 14 class SqLiteHelper 15 { 16 17 /// <summary> 18 /// 数据库连接定义 19 /// </summary> 20 private SQLiteConnection dbConnection; 21 22 /// <summary> 23 /// SQL命令定义 24 /// </summary> 25 private SQLiteCommand dbCommand; 26 27 /// <summary> 28 /// 数据读取定义 29 /// </summary> 30 private SQLiteDataReader dataReader; 31 32 /// <summary> 33 /// 数据读取 34 /// </summary> 35 private SQLiteDataAdapter dataAdapter; 36 37 /// <summary> 38 /// 构造函数 39 /// </summary> 40 /// <param name="connectionString">连接SQLite库字符串</param> 41 public SqLiteHelper(string connectionString) 42 { 43 try 44 { 45 dbConnection = new SQLiteConnection(connectionString); 46 dbConnection.Open(); 47 } 48 catch (Exception e) 49 { 50 Log(e.ToString()); 51 } 52 } 53 /// <summary> 54 /// 执行SQL命令 55 /// </summary> 56 /// <returns>The query.</returns> 57 /// <param name="queryString">SQL命令字符串</param> 58 public SQLiteDataReader ExecuteQuery(string queryString) 59 { 60 try 61 { 62 dbCommand = dbConnection.CreateCommand(); 63 dbCommand.CommandText = queryString; 64 dataReader = dbCommand.ExecuteReader(); 65 } 66 catch (Exception e) 67 { 68 Log(e.Message); 69 } 70 71 return dataReader; 72 } 73 74 public DataTable GetDataTable(string tableName) 75 { 76 string queryString = "SELECT * FROM " + tableName; 77 DataTable dt = new DataTable(); 78 try 79 { 80 dbCommand = dbConnection.CreateCommand(); 81 dbCommand.CommandText = queryString; 82 SQLiteDataAdapter ad = new SQLiteDataAdapter(dbCommand); 83 ad.Fill(dt); 84 } 85 catch (Exception e) 86 { 87 Log(e.Message); 88 } 89 return dt; 90 } 91 92 93 /// <summary> 94 /// 关闭数据库连接 95 /// </summary> 96 public void CloseConnection() 97 { 98 //销毁Commend 99 if (dbCommand != null) 100 { 101 dbCommand.Cancel(); 102 } 103 dbCommand = null; 104 //销毁Reader 105 if (dataReader != null) 106 { 107 dataReader.Close(); 108 } 109 dataReader = null; 110 //销毁Connection 111 if (dbConnection != null) 112 { 113 dbConnection.Close(); 114 } 115 dbConnection = null; 116 117 } 118 119 /// <summary> 120 /// 读取整张数据表 121 /// </summary> 122 /// <returns>The full table.</returns> 123 /// <param name="tableName">数据表名称</param> 124 public SQLiteDataReader ReadFullTable(string tableName) 125 { 126 string queryString = "SELECT * FROM " + tableName; 127 return ExecuteQuery(queryString); 128 } 129 130 131 /// <summary> 132 /// 向指定数据表中插入数据 133 /// </summary> 134 /// <returns>The values.</returns> 135 /// <param name="tableName">数据表名称</param> 136 /// <param name="values">插入的数值</param> 137 public SQLiteDataReader InsertValues(string tableName, string[] values) 138 { 139 //获取数据表中字段数目 140 int fieldCount = ReadFullTable(tableName).FieldCount; 141 //当插入的数据长度不等于字段数目时引发异常 142 if (values.Length != fieldCount) 143 { 144 throw new SQLiteException("values.Length!=fieldCount"); 145 } 146 147 string queryString = "INSERT INTO " + tableName + " VALUES (" + "'" + values[0] + "'"; 148 for (int i = 1; i < values.Length; i++) 149 { 150 queryString += ", " + "'" + values[i] + "'"; 151 } 152 queryString += " )"; 153 return ExecuteQuery(queryString); 154 } 155 156 /// <summary> 157 /// 更新指定数据表内的数据 158 /// </summary> 159 /// <returns>The values.</returns> 160 /// <param name="tableName">数据表名称</param> 161 /// <param name="colNames">字段名</param> 162 /// <param name="colValues">字段名对应的数据</param> 163 /// <param name="key">关键字</param> 164 /// <param name="value">关键字对应的值</param> 165 /// <param name="operation">运算符：=,<,>,...，默认“=”</param> 166 public SQLiteDataReader UpdateValues(string tableName, string[] colNames, string[] colValues, string key, string value, string operation = "=") 167 { 168 //当字段名称和字段数值不对应时引发异常 169 if (colNames.Length != colValues.Length) 170 { 171 throw new SQLiteException("colNames.Length!=colValues.Length"); 172 } 173 174 string queryString = "UPDATE " + tableName + " SET " + colNames[0] + "=" + "'" + colValues[0] + "'"; 175 for (int i = 1; i < colValues.Length; i++) 176 { 177 queryString += ", " + colNames[i] + "=" + "'" + colValues[i] + "'"; 178 } 179 queryString += " WHERE " + key + operation + "'" + value + "'"; 180 return ExecuteQuery(queryString); 181 } 182 183 /// <summary> 184 /// 删除指定数据表内的数据 185 /// </summary> 186 /// <returns>The values.</returns> 187 /// <param name="tableName">数据表名称</param> 188 /// <param name="colNames">字段名</param> 189 /// <param name="colValues">字段名对应的数据</param> 190 public SQLiteDataReader DeleteValuesOR(string tableName, string[] colNames, string[] colValues, string[] operations) 191 { 192 //当字段名称和字段数值不对应时引发异常 193 if (colNames.Length != colValues.Length || operations.Length != colNames.Length || operations.Length != colValues.Length) 194 { 195 throw new SQLiteException("colNames.Length!=colValues.Length || operations.Length!=colNames.Length || operations.Length!=colValues.Length"); 196 } 197 198 string queryString = "DELETE FROM " + tableName + " WHERE " + colNames[0] + operations[0] + "'" + colValues[0] + "'"; 199 for (int i = 1; i < colValues.Length; i++) 200 { 201 queryString += "OR " + colNames[i] + operations[0] + "'" + colValues[i] + "'"; 202 } 203 return ExecuteQuery(queryString); 204 } 205 206 /// <summary> 207 /// 删除指定数据表内的数据 208 /// </summary> 209 /// <returns>The values.</returns> 210 /// <param name="tableName">数据表名称</param> 211 /// <param name="colNames">字段名</param> 212 /// <param name="colValues">字段名对应的数据</param> 213 public SQLiteDataReader DeleteValuesAND(string tableName, string[] colNames, string[] colValues, string[] operations) 214 { 215 //当字段名称和字段数值不对应时引发异常 216 if (colNames.Length != colValues.Length || operations.Length != colNames.Length || operations.Length != colValues.Length) 217 { 218 throw new SQLiteException("colNames.Length!=colValues.Length || operations.Length!=colNames.Length || operations.Length!=colValues.Length"); 219 } 220 221 string queryString = "DELETE FROM " + tableName + " WHERE " + colNames[0] + operations[0] + "'" + colValues[0] + "'"; 222 for (int i = 1; i < colValues.Length; i++) 223 { 224 queryString += " AND " + colNames[i] + operations[i] + "'" + colValues[i] + "'"; 225 } 226 return ExecuteQuery(queryString); 227 } 228 229 230 /// <summary> 231 /// 创建数据表 232 /// </summary> + 233 /// <returns>The table.</returns> 234 /// <param name="tableName">数据表名</param> 235 /// <param name="colNames">字段名</param> 236 /// <param name="colTypes">字段名类型</param> 237 public SQLiteDataReader CreateTable(string tableName, string[] colNames, string[] colTypes) 238 { 239 string queryString = "CREATE TABLE IF NOT EXISTS " + tableName + "( " + colNames[0] + " " + colTypes[0]; 240 for (int i = 1; i < colNames.Length; i++) 241 { 242 queryString += ", " + colNames[i] + " " + colTypes[i]; 243 } 244 queryString += " ) "; 245 return ExecuteQuery(queryString); 246 } 247 248 /// <summary> 249 /// Reads the table. 250 /// </summary> 251 /// <returns>The table.</returns> 252 /// <param name="tableName">Table name.</param> 253 /// <param name="items">Items.</param> 254 /// <param name="colNames">Col names.</param> 255 /// <param name="operations">Operations.</param> 256 /// <param name="colValues">Col values.</param> 257 public SQLiteDataReader ReadTable(string tableName, string[] items, string[] colNames, string[] operations, string[] colValues) 258 { 259 string queryString = "SELECT " + items[0]; 260 for (int i = 1; i < items.Length; i++) 261 { 262 queryString += ", " + items[i]; 263 } 264 queryString += " FROM " + tableName + " WHERE " + colNames[0] + " " + operations[0] + " " + colValues[0]; 265 for (int i = 0; i < colNames.Length; i++) 266 { 267 queryString += " AND " + colNames[i] + " " + operations[i] + " " + colValues[0] + " "; 268 } 269 return ExecuteQuery(queryString); 270 } 271 272 /// <summary> 273 /// 本类log 274 /// </summary> 275 /// <param name="s"></param> 276 static void Log(string s) 277 { 278 Console.WriteLine("class SqLiteHelper:::" + s); 279 } 280 } 281 }
控制台调用
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text; 5 using System.Threading.Tasks; 6 7 namespace ConsoleApplication3 8 { 9 class Program 10 { 11 private static SqLiteHelper sql; 12 static void Main(string[] args) 13 { 14 sql = new SqLiteHelper(@"data source=E:\620000S00000_20160707\Data.db"); 15 16 //读取整张表 17 SQLiteDataReader reader = sql.ReadFullTable("SYS_Statistics"); 18 19 while (reader.Read()) 20 { 21 //读取表名 22 string tableName = reader.GetString(reader.GetOrdinal("TableName")); 23 //读取表的数据 24 int Rowcount = reader.GetInt32(reader.GetOrdinal("DataStatistics")); 25 Console.WriteLine(tableName+Rowcount); 26 } 27 28 Console.Read(); 29 } 30 } 31 }
注意事项:
1：引入System.Data.SQLite.dll 文件。 下载地址：http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki
2：引入System.Data.SQLite.dll的同时，需要将 System.Data.SQLite.dll.config 文件放在同级目录下。否则加载dll文件时报错。以及System.Data.SQLite.xml。
