C#操作SQLite数据库增、删、改、查欢迎转载

转载记得留下链接地址哦！！！

最近项目上要使用SQLite数据库，不怕大伙笑话毕业四年多了，一直使用Oracle或者MySQL或者SQLServer，但是真的是没有用过SQLite数据库，据说非常轻量级，但是真没有用过，于是网上大概搜索下，例子比较多，但是么有一个完整的，即使有，也五花八门。。看的人头晕，可能对于他们大神来讲这都太简单了，算求了更新一篇，新来的小伙伴请看下文，大神你就直接飘过吧!

本例子运行效果

正式开始吧

1.SQLite的下载安装

自然官网下载对应版本即可，我这里下载的是 Precompiled Binaries for 32-bit Windows (.NET Framework 4.0) 不愿意下载的可以直接使用我下载过的点击这里下载

2.减压找出我们所需要的库文件这里我们只需要俩个文件下图所示

3.下载一个数据库管理系统来创建数据库

我是用的是SQLiteStudio3.1.1版本自己下载吧下载地址：https://sqlitestudio.pl/index.rvt?act=download

4.创建数据库以及表

这里创建一个简单的表 UserInfo 完了代码中会有这个数据库在Debug\bin目录下

SQLite数据库文件就一个后缀为.db的文件，就没了。。

通过SQLiteStudio打开该数据库

5.开始使用大伙所熟悉的VS了创建相关类

userinfo的Mode类

 1  /// <summary>
 2     /// UserInfo:实体类(属性说明自动提取数据库字段的描述信息)
 3     /// </summary>
 4     [Serializable]
 5     public partial class UserInfo
 6     {
 7         public UserInfo()
 8         { }
 9         #region Model
10         private int _id;
11         private string _username;
12         private string _pwd;
13         private int? _age;
14         /// <summary>
15         /// 
16         /// </summary>
17         public int ID
18         {
19             set { _id = value; }
20             get { return _id; }
21         }
22         /// <summary>
23         /// 
24         /// </summary>
25         public string UserName
26         {
27             set { _username = value; }
28             get { return _username; }
29         }
30         /// <summary>
31         /// 
32         /// </summary>
33         public string Pwd
34         {
35             set { _pwd = value; }
36             get { return _pwd; }
37         }
38         /// <summary>
39         /// 
40         /// </summary>
41         public int? Age
42         {
43             set { _age = value; }
44             get { return _age; }
45         }
46         #endregion Model
47 
48     }

UserInfo 的Dal类

  1     /// <summary>
  2     /// 数据访问类:UserInfo
  3     /// </summary>
  4     public partial class UserInfo
  5     {
  6         public UserInfo()
  7         { }
  8         #region  BasicMethod
  9 
 10         /// <summary>
 11         /// 得到最大ID
 12         /// </summary>
 13         public int GetMaxId()
 14         {
 15             return DbHelperSQLite.GetMaxID("ID", "UserInfo");
 16         }
 17 
 18         /// <summary>
 19         /// 是否存在该记录
 20         /// </summary>
 21         public bool Exists(int ID)
 22         {
 23             StringBuilder strSql = new StringBuilder();
 24             strSql.Append("select count(1) from UserInfo");
 25             strSql.Append(" where ID=@ID ");
 26             SQLiteParameter[] parameters = {
 27                     new SQLiteParameter("@ID", DbType.Int32,8)          };
 28             parameters[0].Value = ID;
 29 
 30             return DbHelperSQLite.Exists(strSql.ToString(), parameters);
 31         }
 32 
 33 
 34         /// <summary>
 35         /// 增加一条数据
 36         /// </summary>
 37         public bool Add(Model.UserInfo model)
 38         {
 39             StringBuilder strSql = new StringBuilder();
 40             strSql.Append("insert into UserInfo(");
 41             strSql.Append("UserName,Pwd,Age)");
 42             strSql.Append(" values (");
 43             strSql.Append("@UserName,@Pwd,@Age)");
 44             SQLiteParameter[] parameters = {
 45                     new SQLiteParameter("@UserName", DbType.String,50),
 46                     new SQLiteParameter("@Pwd", DbType.String,25),
 47                     new SQLiteParameter("@Age", DbType.Int32,8)};
 48             parameters[0].Value = model.UserName;
 49             parameters[1].Value = model.Pwd;
 50             parameters[2].Value = model.Age;
 51 
 52             int rows = DbHelperSQLite.ExecuteSql(strSql.ToString(), parameters);
 53             if (rows > 0)
 54             {
 55                 return true;
 56             }
 57             else
 58             {
 59                 return false;
 60             }
 61         }
 62         /// <summary>
 63         /// 更新一条数据
 64         /// </summary>
 65         public bool Update(Model.UserInfo model)
 66         {
 67             StringBuilder strSql = new StringBuilder();
 68             strSql.Append("update UserInfo set ");
 69             strSql.Append("UserName=@UserName,");
 70             strSql.Append("Pwd=@Pwd,");
 71             strSql.Append("Age=@Age");
 72             strSql.Append(" where ID=@ID ");
 73             SQLiteParameter[] parameters = {
 74                     new SQLiteParameter("@UserName", DbType.String,50),
 75                     new SQLiteParameter("@Pwd", DbType.String,25),
 76                     new SQLiteParameter("@Age", DbType.Int32,8),
 77                     new SQLiteParameter("@ID", DbType.Int32,8)};
 78             parameters[0].Value = model.UserName;
 79             parameters[1].Value = model.Pwd;
 80             parameters[2].Value = model.Age;
 81             parameters[3].Value = model.ID;
 82 
 83             int rows = DbHelperSQLite.ExecuteSql(strSql.ToString(), parameters);
 84             if (rows > 0)
 85             {
 86                 return true;
 87             }
 88             else
 89             {
 90                 return false;
 91             }
 92         }
 93 
 94         /// <summary>
 95         /// 删除一条数据
 96         /// </summary>
 97         public bool Delete(int ID)
 98         {
 99 
100             StringBuilder strSql = new StringBuilder();
101             strSql.Append("delete from UserInfo ");
102             strSql.Append(" where ID=@ID ");
103             SQLiteParameter[] parameters = {
104                     new SQLiteParameter("@ID", DbType.Int32,8)          };
105             parameters[0].Value = ID;
106 
107             int rows = DbHelperSQLite.ExecuteSql(strSql.ToString(), parameters);
108             if (rows > 0)
109             {
110                 return true;
111             }
112             else
113             {
114                 return false;
115             }
116         }
117         /// <summary>
118         /// 批量删除数据
119         /// </summary>
120         public bool DeleteList(string IDlist)
121         {
122             StringBuilder strSql = new StringBuilder();
123             strSql.Append("delete from UserInfo ");
124             strSql.Append(" where ID in (" + IDlist + ")  ");
125             int rows = DbHelperSQLite.ExecuteSql(strSql.ToString());
126             if (rows > 0)
127             {
128                 return true;
129             }
130             else
131             {
132                 return false;
133             }
134         }
135 
136 
137         /// <summary>
138         /// 得到一个对象实体
139         /// </summary>
140         public Model.UserInfo GetModel(int ID)
141         {
142 
143             StringBuilder strSql = new StringBuilder();
144             strSql.Append("select ID,UserName,Pwd,Age from UserInfo ");
145             strSql.Append(" where ID=@ID ");
146             SQLiteParameter[] parameters = {
147                     new SQLiteParameter("@ID", DbType.Int32,8)          };
148             parameters[0].Value = ID;
149 
150             Model.UserInfo model = new Model.UserInfo();
151             DataSet ds = DbHelperSQLite.Query(strSql.ToString(), parameters);
152             if (ds.Tables[0].Rows.Count > 0)
153             {
154                 return DataRowToModel(ds.Tables[0].Rows[0]);
155             }
156             else
157             {
158                 return null;
159             }
160         }
161 
162 
163         /// <summary>
164         /// 得到一个对象实体
165         /// </summary>
166         public Model.UserInfo DataRowToModel(DataRow row)
167         {
168             Model.UserInfo model = new Model.UserInfo();
169             if (row != null)
170             {
171                 if (row["ID"] != null && row["ID"].ToString() != "")
172                 {
173                     model.ID = int.Parse(row["ID"].ToString());
174                 }
175                 if (row["UserName"] != null)
176                 {
177                     model.UserName = row["UserName"].ToString();
178                 }
179                 if (row["Pwd"] != null)
180                 {
181                     model.Pwd = row["Pwd"].ToString();
182                 }
183                 if (row["Age"] != null && row["Age"].ToString() != "")
184                 {
185                     model.Age = int.Parse(row["Age"].ToString());
186                 }
187             }
188             return model;
189         }
190 
191         /// <summary>
192         /// 获得数据列表
193         /// </summary>
194         public DataSet GetList(string strWhere)
195         {
196             StringBuilder strSql = new StringBuilder();
197             strSql.Append("select ID,UserName,Pwd,Age ");
198             strSql.Append(" FROM UserInfo ");
199             if (strWhere.Trim() != "")
200             {
201                 strSql.Append(" where " + strWhere);
202             }
203             return DbHelperSQLite.Query(strSql.ToString());
204         }
205 
206         /// <summary>
207         /// 获取记录总数
208         /// </summary>
209         public int GetRecordCount(string strWhere)
210         {
211             StringBuilder strSql = new StringBuilder();
212             strSql.Append("select count(1) FROM UserInfo ");
213             if (strWhere.Trim() != "")
214             {
215                 strSql.Append(" where " + strWhere);
216             }
217             object obj = DbHelperSQLite.GetSingle(strSql.ToString());
218             if (obj == null)
219             {
220                 return 0;
221             }
222             else
223             {
224                 return Convert.ToInt32(obj);
225             }
226         }
227         /// <summary>
228         /// 分页获取数据列表
229         /// </summary>
230         public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
231         {
232             StringBuilder strSql = new StringBuilder();
233             strSql.Append("SELECT * FROM ( ");
234             strSql.Append(" SELECT ROW_NUMBER() OVER (");
235             if (!string.IsNullOrEmpty(orderby.Trim()))
236             {
237                 strSql.Append("order by T." + orderby);
238             }
239             else
240             {
241                 strSql.Append("order by T.ID desc");
242             }
243             strSql.Append(")AS Row, T.*  from UserInfo T ");
244             if (!string.IsNullOrEmpty(strWhere.Trim()))
245             {
246                 strSql.Append(" WHERE " + strWhere);
247             }
248             strSql.Append(" ) TT");
249             strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
250             return DbHelperSQLite.Query(strSql.ToString());
251         }
252 
253         #endregion  BasicMethod
254     }

Userinfo的BLL类

  1     public partial class UserInfo
  2     {
  3         private readonly DAL.UserInfo dal = new DAL.UserInfo();
  4         public UserInfo()
  5         { }
  6         #region  BasicMethod
  7 
  8         /// <summary>
  9         /// 得到最大ID
 10         /// </summary>
 11         public int GetMaxId()
 12         {
 13             return dal.GetMaxId();
 14         }
 15 
 16         /// <summary>
 17         /// 是否存在该记录
 18         /// </summary>
 19         public bool Exists(int ID)
 20         {
 21             return dal.Exists(ID);
 22         }
 23 
 24         /// <summary>
 25         /// 增加一条数据
 26         /// </summary>
 27         public bool Add(Model.UserInfo model)
 28         {
 29             return dal.Add(model);
 30         }
 31 
 32         /// <summary>
 33         /// 更新一条数据
 34         /// </summary>
 35         public bool Update(Model.UserInfo model)
 36         {
 37             return dal.Update(model);
 38         }
 39 
 40         /// <summary>
 41         /// 删除一条数据
 42         /// </summary>
 43         public bool Delete(int ID)
 44         {
 45 
 46             return dal.Delete(ID);
 47         }
 48         /// <summary>
 49         /// 删除一条数据
 50         /// </summary>
 51         public bool DeleteList(string IDlist)
 52         {
 53             return dal.DeleteList(IDlist);
 54         }
 55 
 56         /// <summary>
 57         /// 得到一个对象实体
 58         /// </summary>
 59         public Model.UserInfo GetModel(int ID)
 60         {
 61 
 62             return dal.GetModel(ID);
 63         }
 64 
 65         ///// <summary>
 66         ///// 得到一个对象实体，从缓存中
 67         ///// </summary>
 68         //public Model.UserInfo GetModelByCache(int ID)
 69         //{
 70 
 71         //    string CacheKey = "UserInfoModel-" + ID;
 72         //    object objModel = Common.DataCache.GetCache(CacheKey);
 73         //    if (objModel == null)
 74         //    {
 75         //        try
 76         //        {
 77         //            objModel = dal.GetModel(ID);
 78         //            if (objModel != null)
 79         //            {
 80         //                int ModelCache = Maticsoft.Common.ConfigHelper.GetConfigInt("ModelCache");
 81         //                Maticsoft.Common.DataCache.SetCache(CacheKey, objModel, DateTime.Now.AddMinutes(ModelCache), TimeSpan.Zero);
 82         //            }
 83         //        }
 84         //        catch { }
 85         //    }
 86         //    return (Maticsoft.Model.UserInfo)objModel;
 87         //}
 88 
 89         /// <summary>
 90         /// 获得数据列表
 91         /// </summary>
 92         public DataSet GetList(string strWhere)
 93         {
 94             return dal.GetList(strWhere);
 95         }
 96         /// <summary>
 97         /// 获得数据列表
 98         /// </summary>
 99         public List<Model.UserInfo> GetModelList(string strWhere)
100         {
101             DataSet ds = dal.GetList(strWhere);
102             return DataTableToList(ds.Tables[0]);
103         }
104         /// <summary>
105         /// 获得数据列表
106         /// </summary>
107         public List<Model.UserInfo> DataTableToList(DataTable dt)
108         {
109             List<Model.UserInfo> modelList = new List<Model.UserInfo>();
110             int rowsCount = dt.Rows.Count;
111             if (rowsCount > 0)
112             {
113                 Model.UserInfo model;
114                 for (int n = 0; n < rowsCount; n++)
115                 {
116                     model = dal.DataRowToModel(dt.Rows[n]);
117                     if (model != null)
118                     {
119                         modelList.Add(model);
120                     }
121                 }
122             }
123             return modelList;
124         }
125 
126         /// <summary>
127         /// 获得数据列表
128         /// </summary>
129         public DataSet GetAllList()
130         {
131             return GetList("");
132         }
133 
134         /// <summary>
135         /// 分页获取数据列表
136         /// </summary>
137         public int GetRecordCount(string strWhere)
138         {
139             return dal.GetRecordCount(strWhere);
140         }
141         /// <summary>
142         /// 分页获取数据列表
143         /// </summary>
144         public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
145         {
146             return dal.GetListByPage(strWhere, orderby, startIndex, endIndex);
147         }
148         /// <summary>
149         /// 分页获取数据列表
150         /// </summary>
151         //public DataSet GetList(int PageSize,int PageIndex,string strWhere)
152         //{
153         //return dal.GetList(PageSize,PageIndex,strWhere);
154         //}
155 
156         #endregion  BasicMethod
157         #region  ExtensionMethod
158 
159         #endregion  ExtensionMethod
160     }

创建SQLite帮助类

  1     /// <summary>
  2     /// 数据访问基础类(基于SQLite)
  3     /// 可以用户可以修改满足自己项目的需要。
  4     /// </summary>
  5     public abstract class DbHelperSQLite
  6     {
  7         //数据库连接字符串(web.config来配置)，可以动态更改connectionString支持多数据库.        
  8         public static string connectionString = CreateConnectionString();
  9         public DbHelperSQLite()
 10         {
 11         }
 12         private static string CreateConnectionString()
 13         {
 14 
 15             string dbName = ConfigurationManager.AppSettings["SQLiteDB"];
 16             string sqlLitePath = "data source=" + System.Environment.CurrentDirectory+"\\"+dbName + ";version=3;";
 17             return sqlLitePath;
 18         }
 19 
 20         #region 公用方法
 21 
 22         public static int GetMaxID(string FieldName, string TableName)
 23         {
 24             string strsql = "select max(" + FieldName + ")+1 from " + TableName;
 25             object obj = GetSingle(strsql);
 26             if (obj == null)
 27             {
 28                 return 1;
 29             }
 30             else
 31             {
 32                 return int.Parse(obj.ToString());
 33             }
 34         }
 35         public static bool Exists(string strSql)
 36         {
 37             object obj = GetSingle(strSql);
 38             int cmdresult;
 39             if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
 40             {
 41                 cmdresult = 0;
 42             }
 43             else
 44             {
 45                 cmdresult = int.Parse(obj.ToString());
 46             }
 47             if (cmdresult == 0)
 48             {
 49                 return false;
 50             }
 51             else
 52             {
 53                 return true;
 54             }
 55         }
 56         public static bool Exists(string strSql, params SQLiteParameter[] cmdParms)
 57         {
 58             object obj = GetSingle(strSql, cmdParms);
 59             int cmdresult;
 60             if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
 61             {
 62                 cmdresult = 0;
 63             }
 64             else
 65             {
 66                 cmdresult = int.Parse(obj.ToString());
 67             }
 68             if (cmdresult == 0)
 69             {
 70                 return false;
 71             }
 72             else
 73             {
 74                 return true;
 75             }
 76         }
 77 
 78         #endregion
 79 
 80         #region  执行简单SQL语句
 81 
 82         /// <summary>
 83         /// 执行SQL语句，返回影响的记录数
 84         /// </summary>
 85         /// <param name="SQLString">SQL语句</param>
 86         /// <returns>影响的记录数</returns>
 87         public static int ExecuteSql(string SQLString)
 88         {
 89             using (SQLiteConnection connection = new SQLiteConnection(connectionString))
 90             {
 91                 using (SQLiteCommand cmd = new SQLiteCommand(SQLString, connection))
 92                 {
 93                     try
 94                     {
 95                         connection.Open();
 96                         int rows = cmd.ExecuteNonQuery();
 97                         return rows;
 98                     }
 99                     catch (System.Data.SQLite.SQLiteException E)
100                     {
101                         connection.Close();
102                         throw new Exception(E.Message);
103                     }
104                 }
105             }
106         }
107 
108         /// <summary>
109         /// 执行多条SQL语句，实现数据库事务。
110         /// </summary>
111         /// <param name="SQLStringList">多条SQL语句</param>        
112         public static void ExecuteSqlTran(ArrayList SQLStringList)
113         {
114             using (SQLiteConnection conn = new SQLiteConnection(connectionString))
115             {
116                 conn.Open();
117                 SQLiteCommand cmd = new SQLiteCommand();
118                 cmd.Connection = conn;
119                 SQLiteTransaction tx = conn.BeginTransaction();
120                 cmd.Transaction = tx;
121                 try
122                 {
123                     for (int n = 0; n < SQLStringList.Count; n++)
124                     {
125                         string strsql = SQLStringList[n].ToString();
126                         if (strsql.Trim().Length > 1)
127                         {
128                             cmd.CommandText = strsql;
129                             cmd.ExecuteNonQuery();
130                         }
131                     }
132                     tx.Commit();
133                 }
134                 catch (System.Data.SQLite.SQLiteException E)
135                 {
136                     tx.Rollback();
137                     throw new Exception(E.Message);
138                 }
139             }
140         }
141         /// <summary>
142         /// 执行带一个存储过程参数的的SQL语句。
143         /// </summary>
144         /// <param name="SQLString">SQL语句</param>
145         /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
146         /// <returns>影响的记录数</returns>
147         public static int ExecuteSql(string SQLString, string content)
148         {
149             using (SQLiteConnection connection = new SQLiteConnection(connectionString))
150             {
151                 SQLiteCommand cmd = new SQLiteCommand(SQLString, connection);
152                 SQLiteParameter myParameter = new SQLiteParameter("@content", DbType.String);
153                 myParameter.Value = content;
154                 cmd.Parameters.Add(myParameter);
155                 try
156                 {
157                     connection.Open();
158                     int rows = cmd.ExecuteNonQuery();
159                     return rows;
160                 }
161                 catch (System.Data.SQLite.SQLiteException E)
162                 {
163                     throw new Exception(E.Message);
164                 }
165                 finally
166                 {
167                     cmd.Dispose();
168                     connection.Close();
169                 }
170             }
171         }
172         /// <summary>
173         /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
174         /// </summary>
175         /// <param name="strSQL">SQL语句</param>
176         /// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
177         /// <returns>影响的记录数</returns>
178         public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
179         {
180             using (SQLiteConnection connection = new SQLiteConnection(connectionString))
181             {
182                 SQLiteCommand cmd = new SQLiteCommand(strSQL, connection);
183                 SQLiteParameter myParameter = new SQLiteParameter("@fs", DbType.Binary);
184                 myParameter.Value = fs;
185                 cmd.Parameters.Add(myParameter);
186                 try
187                 {
188                     connection.Open();
189                     int rows = cmd.ExecuteNonQuery();
190                     return rows;
191                 }
192                 catch (System.Data.SQLite.SQLiteException E)
193                 {
194                     throw new Exception(E.Message);
195                 }
196                 finally
197                 {
198                     cmd.Dispose();
199                     connection.Close();
200                 }
201             }
202         }
203 
204         /// <summary>
205         /// 执行一条计算查询结果语句，返回查询结果（object）。
206         /// </summary>
207         /// <param name="SQLString">计算查询结果语句</param>
208         /// <returns>查询结果（object）</returns>
209         public static object GetSingle(string SQLString)
210         {
211             using (SQLiteConnection connection = new SQLiteConnection(connectionString))
212             {
213                 using (SQLiteCommand cmd = new SQLiteCommand(SQLString, connection))
214                 {
215                     try
216                     {
217                         connection.Open();
218                         object obj = cmd.ExecuteScalar();
219                         if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
220                         {
221                             return null;
222                         }
223                         else
224                         {
225                             return obj;
226                         }
227                     }
228                     catch (System.Data.SQLite.SQLiteException e)
229                     {
230                         connection.Close();
231                         throw new Exception(e.Message);
232                     }
233                 }
234             }
235         }
236         /// <summary>
237         /// 执行查询语句，返回SQLiteDataReader
238         /// </summary>
239         /// <param name="strSQL">查询语句</param>
240         /// <returns>SQLiteDataReader</returns>
241         public static SQLiteDataReader ExecuteReader(string strSQL)
242         {
243             SQLiteConnection connection = new SQLiteConnection(connectionString);
244             SQLiteCommand cmd = new SQLiteCommand(strSQL, connection);
245             try
246             {
247                 connection.Open();
248                 SQLiteDataReader myReader = cmd.ExecuteReader();
249                 return myReader;
250             }
251             catch (System.Data.SQLite.SQLiteException e)
252             {
253                 throw new Exception(e.Message);
254             }
255 
256         }
257         /// <summary>
258         /// 执行查询语句，返回DataSet
259         /// </summary>
260         /// <param name="SQLString">查询语句</param>
261         /// <returns>DataSet</returns>
262         public static DataSet Query(string SQLString)
263         {
264             using (SQLiteConnection connection = new SQLiteConnection(connectionString))
265             {
266                 DataSet ds = new DataSet();
267                 try
268                 {
269                     connection.Open();
270                     SQLiteDataAdapter command = new SQLiteDataAdapter(SQLString, connection);
271                     command.Fill(ds, "ds");
272                 }
273                 catch (System.Data.SQLite.SQLiteException ex)
274                 {
275                     throw new Exception(ex.Message);
276                 }
277                 return ds;
278             }
279         }
280 
281 
282         #endregion
283 
284         #region 执行带参数的SQL语句
285 
286         /// <summary>
287         /// 执行SQL语句，返回影响的记录数
288         /// </summary>
289         /// <param name="SQLString">SQL语句</param>
290         /// <returns>影响的记录数</returns>
291         public static int ExecuteSql(string SQLString, params SQLiteParameter[] cmdParms)
292         {
293             using (SQLiteConnection connection = new SQLiteConnection(connectionString))
294             {
295                 using (SQLiteCommand cmd = new SQLiteCommand())
296                 {
297                     try
298                     {
299                         PrepareCommand(cmd, connection, null, SQLString, cmdParms);
300                         int rows = cmd.ExecuteNonQuery();
301                         cmd.Parameters.Clear();
302                         return rows;
303                     }
304                     catch (System.Data.SQLite.SQLiteException E)
305                     {
306                         throw new Exception(E.Message);
307                     }
308                 }
309             }
310         }
311 
312 
313         /// <summary>
314         /// 执行多条SQL语句，实现数据库事务。
315         /// </summary>
316         /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SQLiteParameter[]）</param>
317         public static void ExecuteSqlTran(Hashtable SQLStringList)
318         {
319             using (SQLiteConnection conn = new SQLiteConnection(connectionString))
320             {
321                 conn.Open();
322                 using (SQLiteTransaction trans = conn.BeginTransaction())
323                 {
324                     SQLiteCommand cmd = new SQLiteCommand();
325                     try
326                     {
327                         //循环
328                         foreach (DictionaryEntry myDE in SQLStringList)
329                         {
330                             string cmdText = myDE.Key.ToString();
331                             SQLiteParameter[] cmdParms = (SQLiteParameter[])myDE.Value;
332                             PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
333                             int val = cmd.ExecuteNonQuery();
334                             cmd.Parameters.Clear();
335 
336                             trans.Commit();
337                         }
338                     }
339                     catch
340                     {
341                         trans.Rollback();
342                         throw;
343                     }
344                 }
345             }
346         }
347 
348 
349         /// <summary>
350         /// 执行一条计算查询结果语句，返回查询结果（object）。
351         /// </summary>
352         /// <param name="SQLString">计算查询结果语句</param>
353         /// <returns>查询结果（object）</returns>
354         public static object GetSingle(string SQLString, params SQLiteParameter[] cmdParms)
355         {
356             using (SQLiteConnection connection = new SQLiteConnection(connectionString))
357             {
358                 using (SQLiteCommand cmd = new SQLiteCommand())
359                 {
360                     try
361                     {
362                         PrepareCommand(cmd, connection, null, SQLString, cmdParms);
363                         object obj = cmd.ExecuteScalar();
364                         cmd.Parameters.Clear();
365                         if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
366                         {
367                             return null;
368                         }
369                         else
370                         {
371                             return obj;
372                         }
373                     }
374                     catch (System.Data.SQLite.SQLiteException e)
375                     {
376                         throw new Exception(e.Message);
377                     }
378                 }
379             }
380         }
381 
382         /// <summary>
383         /// 执行查询语句，返回SQLiteDataReader
384         /// </summary>
385         /// <param name="strSQL">查询语句</param>
386         /// <returns>SQLiteDataReader</returns>
387         public static SQLiteDataReader ExecuteReader(string SQLString, params SQLiteParameter[] cmdParms)
388         {
389             SQLiteConnection connection = new SQLiteConnection(connectionString);
390             SQLiteCommand cmd = new SQLiteCommand();
391             try
392             {
393                 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
394                 SQLiteDataReader myReader = cmd.ExecuteReader();
395                 cmd.Parameters.Clear();
396                 return myReader;
397             }
398             catch (System.Data.SQLite.SQLiteException e)
399             {
400                 throw new Exception(e.Message);
401             }
402 
403         }
404 
405         /// <summary>
406         /// 执行查询语句，返回DataSet
407         /// </summary>
408         /// <param name="SQLString">查询语句</param>
409         /// <returns>DataSet</returns>
410         public static DataSet Query(string SQLString, params SQLiteParameter[] cmdParms)
411         {
412             using (SQLiteConnection connection = new SQLiteConnection(connectionString))
413             {
414                 SQLiteCommand cmd = new SQLiteCommand();
415                 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
416                 using (SQLiteDataAdapter da = new SQLiteDataAdapter(cmd))
417                 {
418                     DataSet ds = new DataSet();
419                     try
420                     {
421                         da.Fill(ds, "ds");
422                         cmd.Parameters.Clear();
423                     }
424                     catch (System.Data.SQLite.SQLiteException ex)
425                     {
426                         throw new Exception(ex.Message);
427                     }
428                     return ds;
429                 }
430             }
431         }
432 
433 
434         private static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, SQLiteTransaction trans, string cmdText, SQLiteParameter[] cmdParms)
435         {
436             if (conn.State != ConnectionState.Open)
437                 conn.Open();
438             cmd.Connection = conn;
439             cmd.CommandText = cmdText;
440             if (trans != null)
441                 cmd.Transaction = trans;
442             cmd.CommandType = CommandType.Text;//cmdType;
443             if (cmdParms != null)
444             {
445                 foreach (SQLiteParameter parm in cmdParms)
446                     cmd.Parameters.Add(parm);
447             }
448         }
449 
450         #endregion
451 
452 
453 
454     }

6.创建一个WinForm窗体用于测试

大概是这样的，我用的是VS2017

数据库配置文件新增App.config文件其余的没有用，是Mysql的连接字符串，可以不用管。。。

窗体代码大概是这样的比较简单，只是实现简单的操作，具体细节那就交个大家了。。。

 1    public partial class Form1 : Form
 2     {
 3         public Form1()
 4         {
 5             InitializeComponent();
 6         }
 7 
 8 
 9         BLL.UserInfo bll = new BLL.UserInfo();
10        
11 
12         //数据库连接
13         SQLiteConnection m_dbConnection;
14         private void button1_Click(object sender, EventArgs e)
15         {
16 
17             DataSet ds =  bll.GetAllList();
18 
19             this.dataGridView1.DataSource = ds.Tables[0];
20 
21         }
22 
23         int count = 0;
24         private void button2_Click(object sender, EventArgs e)
25         {
26             count++;
27             Model.UserInfo userInfo = new Model.UserInfo();
28             userInfo.UserName = "Count" + count;
29             userInfo.Pwd = "123456";
30             userInfo.Age = count;
31 
32             bll.Add(userInfo);
33 
34             DataSet ds = bll.GetAllList();
35             this.dataGridView1.DataSource = ds.Tables[0];
36 
37         }
38 
39         private void button3_Click(object sender, EventArgs e)
40         {
41 
42             Model.UserInfo userInfo = new Model.UserInfo();
43             userInfo.ID = int.Parse(this.label1.Text);
44             userInfo.UserName = this.textBox1.Text;
45             userInfo.Pwd = this.textBox2.Text;
46             userInfo.Age = int.Parse(this.textBox3.Text);
47             bll.Update(userInfo);
48 
49 
50             DataSet ds = bll.GetAllList();
51             this.dataGridView1.DataSource = ds.Tables[0];
52 
53 
54         }
55 
56         private void dataGridView1_MouseClick(object sender, MouseEventArgs e)
57         {
58             DataGridViewRow row = this.dataGridView1.CurrentRow;
59             this.label1.Text = row.Cells[0].Value.ToString();
60             this.textBox1.Text = row.Cells[1].Value.ToString();
61             this.textBox2.Text = row.Cells[2].Value.ToString();
62             this.textBox3.Text = row.Cells[3].Value.ToString();
63 
64 
65         }
66 
67         private void button4_Click(object sender, EventArgs e)
68         {
69             bll.Delete(int.Parse(this.label1.Text));
70 
71 
72             DataSet ds = bll.GetAllList();
73             this.dataGridView1.DataSource = ds.Tables[0];
74         }
75 
76         private void linkLabel1_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
77         {
78          
79             Process.Start("IExplore", "https://www.cnblogs.com/JiYF/");
80         }
81     }

7.运行效果

8.项目结构文件

到这里就完了，新手自己去下载调试吧，。。。。。爱你们哟么么哒！❥(^_-)

源代码工程下载地址

或者博客留言，给出邮箱，发你！！！

直接下载：https://files.cnblogs.com/files/JiYF/SQLiteDBTest.rar

温馨小提示：

SQLite版本问题，如果你使用的32位，记得项目编译采用X86 如果你是64位则采用X64 否则会提示错误信息，就酱紫。。。。。。。。。。

posted @ 2019-07-28 17:41 JiYF 阅读(24232) 评论(54) 编辑收藏举报

刷新页面返回顶部

JiYF大男孩

没错，我就是那个大男孩！

C#操作SQLite数据库增、删、改、查欢迎转载

C#操作SQLite数据库增、删、改、查欢迎转载

转载记得留下链接地址哦！！！

1.SQLite的下载安装

2.减压找出我们所需要的库文件这里我们只需要俩个文件下图所示

3.下载一个数据库管理系统来创建数据库

4.创建数据库以及表

5.开始使用大伙所熟悉的VS了创建相关类

6.创建一个WinForm窗体用于测试

7.运行效果

8.项目结构文件

源代码工程下载地址

温馨小提示：

JiYF大男孩

没错，我就是那个大男孩！

C#操作SQLite数据库增、删、改、查 欢迎转载

C#操作SQLite数据库增、删、改、查 欢迎转载

转载记得留下链接地址哦！！！

1.SQLite的下载安装

2.减压找出我们所需要的库文件 这里我们只需要俩个文件下图所示

3.下载一个数据库管理系统来创建数据库

4.创建数据库以及表

5.开始使用大伙所熟悉的VS了创建相关类

6.创建一个WinForm窗体用于测试

7.运行效果

8.项目结构文件

源代码工程下载地址

温馨小提示：

C#操作SQLite数据库增、删、改、查欢迎转载

C#操作SQLite数据库增、删、改、查欢迎转载

2.减压找出我们所需要的库文件这里我们只需要俩个文件下图所示