Sqlite操作帮助类
程序员文章站
2022-04-28 09:17:11
控制台 ......
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 }
控制台