SQLite使用笔记

前言

客户端存储信息的方法有好多种，在以往的项目中采用的是序列化记录到文件中的方式，即时通信项目中客户端的一些系统配置的保存也使用的这种方式，然而客户端保存聊天记录就不能使用这种方式（保存、读取、修改都需要进行序列化操作比较费时，不同会话聊天记录到不同文件中，将需要创建大量的文件），调研相关技术后，决定使用SQLite。

SQLite

我的认知如下：
SQLite是一款轻量级文件数据库，无需部署安装，特点是嵌入式与事务（原子性、一致性、隔离性和持久性）。使用时只需创建一个db文件（db文件即代表一个数据库），然后利用ADO.NET进行连接库、创建、插入、查询、修改表就可以了，非常的简单方便。

步骤

1.添加引用
右键项目引用选择管理Nuget程序包搜索SQLite,选择第一个进行安装

2.创建数据库文件

string dbPath = AppDomain.CurrentDomain.BaseDirectory + "local.db";
SQLiteConnection.CreateFile(dbPath);

注意需要添加引用：using System.Data.SQLite;
3.连接SQLite

SQLiteConnection conn = new SQLiteConnection();
SQLiteConnectionStringBuilder connstr = new SQLiteConnectionStringBuilder();
connstr.DataSource = datasource;
connstr.Password = "123456";//设置密码，SQLite ADO.NET实现了数据库密码保护
conn.ConnectionString = connstr.ToString();
conn.Open();

4.创建表

SQLiteCommand cmd = new SQLiteCommand();
string sql = "CREATE TABLE FirstTable(ID varchar(36),UserName varchar(30),PassWord varchar(30))";
cmd.CommandText = sql;
cmd.Connection = conn;
cmd.ExecuteNonQuery();

5.增删改查
增：

SQLiteCommand cmd = new SQLiteCommand();
string sql = "INSERT INTO FirstTable VALUES('1','ading3','123')";
cmd.Connection = conn;
cmd.CommandText = sql; 
cmd.ExecuteNonQuery();

View Code

删：

string sql = "DELETE FROM FirstTable WHERE ID = @ID";
SQLiteParameter[] parms =
{
new SQLiteParameter("@ID",id)
};
command.CommandText = sql;
command.Parameters.AddRange(parameters);
int counts = command.ExecuteNonQuery();

View Code

改：

string sql= @"UPDATE FirstTable
SET UserName=@UserName,
PassWord=@PassWord 
WHERE UserName='admin'
";
SQLiteParameter[] parms1 =
{
new SQLiteParameter("@UserName","adminading"),
new SQLiteParameter("@PassWord","2622020")
};
command.CommandText = sql;
command.Parameters.AddRange(parameters);
int counts = command.ExecuteNonQuery();

View Code

查：

string query = "SELECT * FROM FirstTable"; 
DataTable dt = SQLiteHelper.ExecuteQuery(query, null);
List<Test> tlist = new List<Test>();
foreach (var item in dt1.Rows)
{
Test tt = new Test();
tt.ID=(item as DataRow)["ID"].ToString();
tt.UserName = (item as DataRow)["UserName"].ToString();
tt.PassWord = (item as DataRow)["PassWord"].ToString();

tlist.Add(tt);
}
public class Test
{
public string ID { get; set; }
public string UserName { get; set; }
public string PassWord { get; set; }
}

View Code

帮助类

  1  /// <summary>
  2     /// SQLite帮助类
  3     /// 说明：使用SQLite很简单，只需要Nuget搜SQLite 使用第一个System.Data.SQLite安装即可
  4     /// 使用CreateDB方法 创建一个数据库文件 传入路径即可  （注意：一个数据库文件代表一个数据库）
  5     /// 使用前请先指定连接字符串 使用SetConnectionString()方法
  6     /// SQLite作为本地文件数据库 具有独立运行、无服务器、零配置、支持事务、低内存、原子性等特点
  7     /// </summary>
  8     public class SQLiteHelper
  9     {
 10         #region 属性
 11         /// <summary>
 12         /// 连接字符串
 13         /// </summary>
 14         private static string connectionString = string.Empty;
 15         #endregion 属性
 16 
 17         #region 设置连接字符串与创建数据库文件
 18         /// <summary>
 19         /// 根据数据源、密码、版本号设置连接字符串。
 20         /// </summary>
 21         /// <param name="datasource">数据源。</param>
 22         /// <param name="password">密码。</param>
 23         /// <param name="version">版本号（缺省为3）。</param>
 24         public static void SetConnectionString(string datasource, string password, int version = 3)
 25         {
 26             connectionString = string.Format("Data Source={0};Version={1};password={2}",
 27                 datasource, version, password);
 28         }
 29 
 30         /// <summary>
 31         /// 创建一个数据库文件。如果存在同名数据库文件，则会覆盖。
 32         /// </summary>
 33         /// <param name="dbName">数据库文件名。为null或空串时不创建。</param>
 34         /// <param name="password">（可选）数据库密码，默认为空。</param>
 35         /// <exception cref="Exception"></exception>
 36         public static void CreateDB(string dbName)
 37         {
 38             if (!string.IsNullOrEmpty(dbName))
 39             {
 40                 try
 41                 {
 42                     CreateDirectory(dbName);
 43                     SQLiteConnection.CreateFile(dbName);
 44                 }
 45                 catch (Exception ex)
 46                 {
 47                     string errormes = ex.Message;
 48                     errormes += "\r\n";
 49                     errormes += LogHelper.ToMessage(ex);
 50                     string path = string.Empty;
 51                     path += AppDomain.CurrentDomain.BaseDirectory;
 52                     path += @"Unlog\SQLiteError";
 53                     path += DateTime.Now.ToString("yyyyMMddHHmm");
 54                     path += ".txt";
 55                     LogHelper.Instance.WriteLog(path, errormes);
 56                 }
 57             }
 58         }
 59 
 60         #region 辅助方法
 61         /// <summary>
 62         /// 创建父级路径
 63         /// </summary>
 64         /// <param name="infoPath"></param>
 65         private static void CreateDirectory(string infoPath)
 66         {
 67             DirectoryInfo directoryInfo = Directory.GetParent(infoPath);
 68             if (!directoryInfo.Exists)
 69             {
 70                 directoryInfo.Create();
 71             }
 72         }
 73         #endregion 辅助方法
 74         #endregion 设置连接字符串与创建数据库文件
 75 
 76         #region 命令参数封装
 77         // <summary>
 78         /// 准备操作命令参数
 79         /// </summary>
 80         /// <param name="cmd">SQLiteCommand</param>
 81         /// <param name="conn">SQLiteConnection</param>
 82         /// <param name="cmdText">Sql命令文本</param>
 83         /// <param name="data">参数数组</param>
 84         private static void PrepareCommand(SQLiteConnection conn, SQLiteCommand cmd, string cmdText, params SQLiteParameter[] parms)
 85         {
 86             if (conn.State != ConnectionState.Open)
 87                 conn.Open();
 88             cmd.Parameters.Clear();
 89             cmd.Connection = conn;
 90             cmd.CommandText = cmdText;
 91             cmd.CommandType = CommandType.Text;
 92             cmd.CommandTimeout = 30;
 93             if (parms != null && parms.Length > 0)
 94             {
 95                 foreach (SQLiteParameter parameter in parms)
 96                 {
 97                     if ((parameter.Direction == ParameterDirection.Input || parameter.Direction == ParameterDirection.InputOutput) && (parameter.Value == null))
 98                     {
 99                         parameter.Value = DBNull.Value;
100                     }
101                 }
102                 cmd.Parameters.AddRange(parms);
103             }
104         }
105 
106         #endregion 命令参数封装
107 
108         #region 数据库操作
109         #region 创建表
110         /// <summary>
111         /// 创建表
112         /// </summary>
113         /// <param name="sql"></param>
114         /// <returns></returns>
115         public static bool CreateTable(string sql)
116         {
117             bool rr = true;
118             try
119             {
120                 using (SQLiteConnection connection = new SQLiteConnection(connectionString))
121                 {
122                     using (SQLiteCommand command = new SQLiteCommand(connection))
123                     {
124                         try
125                         {
126                             PrepareCommand(connection, command, sql, null);
127                             int count = command.ExecuteNonQuery();
128                             if (count > 0) rr = true;
129                             else rr = false;
130                         }
131                         catch (Exception ex)
132                         {
133                             return false;
134                         }
135                     }
136                 }
137             }
138             catch (Exception ex)
139             {
140 
141                 return false;
142             }
143             return rr;
144         }
145         #endregion 创建表
146 
147         #region 增删改操作
148         /// <summary> 
149         /// 对SQLite数据库执行增删改操作，返回受影响的行数。 
150         /// </summary> 
151         /// <param name="sql">要执行的增删改的SQL语句。</param> 
152         /// <param name="parameters">执行增删改语句所需要的参数，参数必须以它们在SQL语句中的顺序为准。</param> 
153         /// <returns></returns> 
154         /// <exception cref="Exception"></exception>
155         public static int ExecuteNonQuery(string sql, params SQLiteParameter[] parameters)
156         {
157             int affectedRows = 0;
158             using (SQLiteConnection connection = new SQLiteConnection(connectionString))
159             {
160                 using (SQLiteCommand command = new SQLiteCommand(connection))
161                 {
162                     try
163                     {
164                         PrepareCommand(connection, command, sql, parameters);
165                         //connection.Open();
166                         //command.CommandText = sql;
167                         //if (parameters.Length != 0)
168                         //{
169                         //    command.Parameters.AddRange(parameters);
170                         //}
171                         affectedRows = command.ExecuteNonQuery();
172                     }
173                     catch (Exception) { throw; }
174                 }
175             }
176             return affectedRows;
177         }
178         #endregion 增删改操作
179 
180         #region 批量操作
181         /// <summary>
182         /// 批量处理数据操作语句。
183         /// </summary>
184         /// <param name="list">SQL语句集合。</param>
185         /// <exception cref="Exception"></exception>
186         public static void ExecuteNonQueryBatch(List<KeyValuePair<string, SQLiteParameter[]>> list)
187         {
188             using (SQLiteConnection conn = new SQLiteConnection(connectionString))
189             {
190                 try { conn.Open(); }
191                 catch { throw; }
192                 using (SQLiteTransaction tran = conn.BeginTransaction())
193                 {
194                     using (SQLiteCommand cmd = new SQLiteCommand(conn))
195                     {
196                         try
197                         {
198                             foreach (var item in list)
199                             {
200                                 PrepareCommand(conn, cmd, item.Key, item.Value);
201                                 //cmd.CommandText = item.Key;
202                                 //if (item.Value != null)
203                                 //{
204                                 //    cmd.Parameters.AddRange(item.Value);
205                                 //}
206                                 cmd.ExecuteNonQuery();
207                             }
208                             tran.Commit();
209                         }
210                         catch (Exception) { tran.Rollback(); throw; }
211                     }
212                 }
213             }
214         }
215         #endregion 批量操作
216 
217         #region 查询 返回第一个
218         /// <summary>
219         /// 执行查询语句，并返回第一个结果。
220         /// </summary>
221         /// <param name="sql">查询语句。</param>
222         /// <returns>查询结果。</returns>
223         /// <exception cref="Exception"></exception>
224         public static object ExecuteScalar(string sql, params SQLiteParameter[] parameters)
225         {
226             using (SQLiteConnection conn = new SQLiteConnection(connectionString))
227             {
228                 using (SQLiteCommand cmd = new SQLiteCommand(conn))
229                 {
230                     try
231                     {
232                         PrepareCommand(conn, cmd, sql, parameters);
233                         //conn.Open();
234                         //cmd.CommandText = sql;
235                         //if (parameters.Length != 0)
236                         //{
237                         //    cmd.Parameters.AddRange(parameters);
238                         //}
239                         return cmd.ExecuteScalar();
240                     }
241                     catch (Exception) { throw; }
242                 }
243             }
244         }
245         #endregion 查询 返回第一个
246 
247         #region 查询 返回DT
248         /// <summary> 
249         /// 执行一个查询语句，返回一个包含查询结果的DataTable。 
250         /// </summary> 
251         /// <param name="sql">要执行的查询语句。</param> 
252         /// <param name="parameters">执行SQL查询语句所需要的参数，参数必须以它们在SQL语句中的顺序为准。</param> 
253         /// <returns></returns> 
254         /// <exception cref="Exception"></exception>
255         public static DataTable ExecuteQuery(string sql, params SQLiteParameter[] parameters)
256         {
257             using (SQLiteConnection connection = new SQLiteConnection(connectionString))
258             {
259                 using (SQLiteCommand command = new SQLiteCommand(sql, connection))
260                 {
261                     PrepareCommand(connection, command, sql, parameters);
262                     //if (parameters != null)
263                     //{
264                     //    if (parameters.Length != 0)
265                     //    {
266                     //        command.Parameters.AddRange(parameters);
267                     //    }
268                     //}
269 
270                     SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
271                     DataTable data = new DataTable();
272                     try { adapter.Fill(data); }
273                     catch (Exception) { throw; }
274                     return data;
275                 }
276             }
277         }
278         #endregion 查询 返回DT
279 
280         #region 查询 返回SQLiteDataReader
281         /// <summary> 
282         /// 执行一个查询语句，返回一个关联的SQLiteDataReader实例。 
283         /// </summary> 
284         /// <param name="sql">要执行的查询语句。</param> 
285         /// <param name="parameters">执行SQL查询语句所需要的参数，参数必须以它们在SQL语句中的顺序为准。</param> 
286         /// <returns></returns> 
287         /// <exception cref="Exception"></exception>
288         public static SQLiteDataReader ExecuteReader(string sql, params SQLiteParameter[] parameters)
289         {
290             SQLiteConnection connection = new SQLiteConnection(connectionString);
291             SQLiteCommand command = new SQLiteCommand(sql, connection);
292             try
293             {
294                 PrepareCommand(connection, command, sql, parameters);
295                 //if (parameters.Length != 0)
296                 //{
297                 //    command.Parameters.AddRange(parameters);
298                 //}
299                 //connection.Open();
300                 return command.ExecuteReader(CommandBehavior.CloseConnection);
301             }
302             catch (Exception) { throw; }
303         }
304         #endregion 查询 返回SQLiteDataReader
305 
306         #region 查询数据库中的所有数据类型信息
307         /// <summary> 
308         /// 查询数据库中的所有数据类型信息。
309         /// </summary> 
310         /// <returns></returns> 
311         /// <exception cref="Exception"></exception>
312         public static DataTable GetSchema()
313         {
314             using (SQLiteConnection connection = new SQLiteConnection(connectionString))
315             {
316                 try
317                 {
318                     connection.Open();
319                     return connection.GetSchema("TABLES");
320                 }
321                 catch (Exception) { throw; }
322             }
323         }
324 
325         #endregion 查询数据库中的所有数据类型信息
326 
327         #region 判断表是否存在
328         public static bool IsTableExist(string tableName)
329         {
330             bool isTableExist = true;
331             using (SQLiteConnection connection = new SQLiteConnection(connectionString))
332             {
333                 string sql = "SELECT name FROM sqlite_master WHERE type='table' AND name = '";
334                 sql += tableName;
335                 sql += "'";
336                 using (SQLiteCommand command = new SQLiteCommand(sql, connection))
337                 {
338                     PrepareCommand(connection, command, sql, null);
339                     int count = command.ExecuteNonQuery();
340                     if (count <= 0) isTableExist = false;
341                 }
342             }
343             return isTableExist;
344 
345         }
346         #endregion 判断表是否存在
347 
348         #endregion 数据库操作
349 
350         #region 整理数据库
351         /// <summary>
352         /// 重新组织数据库
353         /// SQLite 的自带命令 VACUUM。用来重新整理整个数据库达到紧凑之用，比如把删除的彻底删掉等等
354         /// </summary>
355         public static void ResetDataBass()
356         {
357             using (SQLiteConnection conn = new SQLiteConnection(connectionString))
358             {
359                 var cmd = new SQLiteCommand();
360 
361                 if (conn.State != ConnectionState.Open)
362                     conn.Open();
363                 cmd.Parameters.Clear();
364                 cmd.Connection = conn;
365                 cmd.CommandText = "vacuum";
366                 cmd.CommandType = CommandType.Text;
367                 cmd.CommandTimeout = 30;
368                 cmd.ExecuteNonQuery();
369             }
370         }
371         #endregion 整理数据库
372     }