随笔- 567 文章- 0 评论- 0 阅读- 55万
SQLiteHelper类

本文章涉及的源代码下载 : https://files.cnblogs.com/files/lmp138/Sqlite4.7z
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using SQLite;
 
namespace Sqlite4
{
    /// <summary>
    /// 处理数据库的操作。
    /// 需要 sqlite3.dll的配合，项目文件应添加该文件
    /// 本类，包含一个静态的字典，储存对数据库文件的连接。
    /// 构造函数可以指定不同的数据库文件
    /// </summary>
    public class SQLiteHelper : IDisposable
    {
        /// <summary>
        /// 互斥锁对象
        /// </summary>
        private static readonly object objForLock = new object();
        /// <summary>
        /// 连接字典: Key : 数据库的文件名，Value: 该文件的数据库连接
        /// </summary>
        private static readonly Dictionary<string, SQLiteConnection> ConnDic = new Dictionary<string, SQLiteConnection>();
 
        /// <summary>
        /// 连接
        /// </summary>
        private SQLiteConnection cnn = null;
 
        /// <summary>
        ///   打开数据库连接，日期时间采用字符串方式。
        /// </summary>
        /// <param name="dbPath">数据库的文件名</param>
        public SQLiteHelper(string dbPath)
        {
            if (ConnDic.ContainsKey(dbPath))
            {
                cnn = ConnDic[dbPath];
            }
            else
            {
                cnn = new SQLiteConnection(dbPath, false); // 数据库中，日期使用DateTime类型，而不是 Ticks
                ConnDic.Add(dbPath, cnn);
            }
        }
        /// <summary>
        /// 关闭打开的文件连接
        /// </summary>
        public void Close()
        {
            ConnDic.Remove(cnn.DatabasePath);  // 从字典里删除
            cnn.Close();  // 关闭连接
        }
        /// <summary>
        /// 创建所有的表到数据库,表已存在不会重复创建
        /// 要增加表到数据库, 参考:ClassLibraryEF空间下的 CodeFirstClass.css文件
        /// </summary>
        public void CreateMyTables()
        {
            cnn.CreateTable<统计表>();
 
        }
 
 
 
 
        /// <summary>
        /// 删除一项
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public int Delete(object obj)
        {
            lock (objForLock)
            {
                return cnn.Delete(obj);
            }
        }
 
        /// <summary>
        /// 删除一组
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        /// <returns></returns>
        public int DeleteRange<T>(IEnumerable<T> list)
        {
            lock (objForLock)
            {
                int c = 0;
                cnn.RunInTransaction(() =>
                {
                    foreach (var item in list)
                    {
                        c += cnn.Delete(item);
                    }
                });
 
                return c;
            }
        }
 
        /// <summary>
        /// 删除表的所有内容
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public int DeleteAll<T>()
        {
            lock (objForLock)
            {
                return cnn.DeleteAll<T>();
            }
        }
 
        /// <summary>
        /// 插入数据
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public int Insert(object data)
        {
            lock (objForLock)
            {
                return cnn.Insert(data);
            }
        }
        /// <summary>
        /// 插入数据
        /// </summary>
        /// <param name="objects"></param>
        /// <returns></returns>
        public int InsertAll(System.Collections.IEnumerable objects)
        {
            lock (objForLock)
            {
                return cnn.InsertAll(objects);
            }
        }
 
        /// <summary>
        /// 更新一项
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public int Update(object obj)
        {
            lock (objForLock)
            {
                return cnn.Update(obj);
            }
        }
 
        public int UpdateAll(System.Collections.IEnumerable objects)
        {
            lock (objForLock)
            {
                return cnn.UpdateAll(objects);
            }
        }
 
        /// <summary>
        /// 执行SQL命令
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int Execute(string sql)
        {
            lock (objForLock)
            {
                return cnn.Execute(sql);
            }
        }
 
        /// <summary>
        /// 备份
        /// </summary>
        /// <param name="destFileName"></param>
        public void Backup(string destFileName)
        {
            lock (objForLock)
            {
                File.Copy(cnn.DatabasePath, destFileName);
            }
        }
 
 
 
        /// <summary>
        /// 返回所有记录
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public List<T> ToListAll<T>() where T : new()
        {
            lock (objForLock)
            {
                Type tp = typeof(T);
                string sql = $"select * from {tp.Name}";
                return cnn.Query<T>(sql);
            }
        }
 
        /// <summary>
        /// 查询sql表示的记录
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <returns></returns>
        public List<T> ToListSql<T>(string sql) where T : new()
        {
            lock (objForLock)
            {
                return cnn.Query<T>(sql);
            }
        }
 
        /// <summary>
        /// 返回sql描述的记录
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <returns></returns>
        public List<T> ToList<T>(string sql) where T : new()
        {
            lock (objForLock)
            {
                return cnn.Query<T>(sql);
            }
        }
 
        /// <summary>
        /// 查询表的内容，使用SQL语句
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="sqlText"></param>
        /// <returns></returns>
        public IList<object> Query(string tableName, string sqlText)
        {
            lock (objForLock)
            {
                Type tp = GetTypeByTableName(tableName);
                if (tp == null)
                    return null;
                SQLite.TableMapping tm = cnn.GetMapping(tp);
                return cnn.Query(tm, sqlText);
            }
        }
 
        /// <summary>
        /// 获取表
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public IList<object> ToListByTableName(string tableName)
        {
            lock (objForLock)
            {
                Type tp = GetTypeByTableName(tableName);
                if (tp == null)
                    return null;
 
                SQLite.TableMapping tm = cnn.GetMapping(tp);
                return cnn.Query(tm, $"select * from {tableName}");
            }
        }
 
        /// <summary>
        /// 根据表名称，获得类型
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public Type GetTypeByTableName(string tableName)
        {
            Assembly assembly = Assembly.Load("ClassLibraryEF");
            Type[] types = assembly.GetTypes();
 
            foreach (Type tp in types)
            {
                if (tp.Name == tableName)
                    return tp;
            }
            return null;
        }
 
 
        /// <summary>
        /// 获取表的行数
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public int GetCountOfTable(string tableName)
        {
            lock (objForLock)
            {
                return cnn.CreateCommand($"select count(*) from {tableName}").ExecuteScalar<int>();
            }
        }
 
        /// <summary>
        /// Sqlite标准时间
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public string SqliteDateTimeStr(DateTime dt)
        {
            return dt.ToString("yyyy-MM-ddTHH:mm:ss");
        }
 
        /// <summary>
        /// Sqlite标准时间字符串，日期的最后一秒
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public string SqliteDateTimeStr_EndDayTime(DateTime dt)
        {
            return dt.ToString("yyyy-MM-ddT23:59:59");
        }
 
        /// <summary>
        /// Sqlite标准时间字符串，日期的开始0秒
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public string SqliteDateTimeStr_BeginDayTime(DateTime dt)
        {
            return dt.ToString("yyyy-MM-ddT00:00:00");
        }
 
        /// <summary>
        /// 释放时，无需释放任何对象，因为采用静态的Sqlite连接。
        /// 如果释放静态连接，之后的实例将重新创建连接，太频繁
        /// </summary>
        public void Dispose()
        {
 
        }
    }
}