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

跪谢發表於 2019-7-28 17:41:00

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

<h1>C#操作SQLite数据库增、删、改、查欢迎转载</h1>
<h2>转载记得留下链接地址哦！！！</h2>
最近项目上要使用SQLite数据库，不怕大伙笑话毕业四年多了，一直使用Oracle或者MySQL或者SQLServer，但是真的是没有用过SQLite数据库，据说非常轻量级，但是真没有用过，于是网上大概搜索下，例子比较多，但是么有一个完整的，即使有，也五花八门。。看的人头晕，可能对于他们大神来讲这都太简单了，算求了更新一篇，新来的小伙伴请看下文，大神你就直接飘过吧!
本例子运行效果
<img src="https://img2018.cnblogs.com/blog/1070330/201907/1070330-20190728173959495-1491839690.gif" alt="">
正式开始吧
<h2>1.SQLite的下载安装</h2>
自然官网下载对应版本即可，我这里下载的是  Precompiled Binaries for 32-bit Windows (.NET Framework 4.0)  不愿意下载的可以直接使用我下载过的  点击这里下载
<img src="https://img2018.cnblogs.com/blog/1070330/201907/1070330-20190728170750649-1891493148.png" alt="">
<h2>2.减压找出我们所需要的库文件   这里我们只需要俩个文件下图所示</h2>
<img src="https://img2018.cnblogs.com/blog/1070330/201907/1070330-20190728171303224-1222411051.png" alt="">
<h2>3.下载一个数据库管理系统来创建数据库 </h2>
我是用的是SQLiteStudio3.1.1版本  自己下载吧  下载地址：https://sqlitestudio.pl/index.rvt?act=download 
<img src="https://img2018.cnblogs.com/blog/1070330/201907/1070330-20190728171607462-2002888896.png" alt="">
<h2>4.创建数据库以及表</h2>
这里创建一个简单的表 UserInfo  完了代码中会有这个数据库在Debug\bin目录下
SQLite数据库文件就一个后缀为.db的文件，就没了。。
<img src="https://img2018.cnblogs.com/blog/1070330/201907/1070330-20190729103409181-369025457.png" alt="">
通过SQLiteStudio打开该数据库
<img src="https://img2018.cnblogs.com/blog/1070330/201907/1070330-20190728171811835-1580710890.png" alt="">
<h2>5.开始使用大伙所熟悉的VS了创建相关类</h2>
userinfo的Mode类
<div class="cnblogs_code">
<pre> 1/// <summary>
 2 /// UserInfo:实体类(属性说明自动提取数据库字段的描述信息)
 3 /// </summary>
 4 
 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 }</pre>
</div>
UserInfo 的Dal类
<div class="cnblogs_code">
<pre>1 /// <summary>
2 /// 数据访问类:UserInfo
3 /// </summary>
4 public partial class UserInfo
5 {
6 public UserInfo()
7 { }
8 #regionBasicMethod
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 #endregionBasicMethod
254 }</pre>
</div>
Userinfo的BLL类
<div class="cnblogs_code">
<pre>1 public partial class UserInfo
2 {
3 private readonly DAL.UserInfo dal = new DAL.UserInfo();
4 public UserInfo()
5 { }
6 #regionBasicMethod
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);
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 #endregionBasicMethod
157 #regionExtensionMethod
158
159 #endregionExtensionMethod
160 }</pre>
</div>
创建SQLite帮助类
<div class="cnblogs_code">
<pre>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.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 }</pre>
</div>
<h2>6.创建一个WinForm窗体用于测试</h2>
大概是这样的，我用的是VS2017
<img src="https://img2018.cnblogs.com/blog/1070330/201907/1070330-20190728172435837-716839749.png" alt="">
数据库配置文件新增App.config文件  其余的没有用，是Mysql的连接字符串，可以不用管。。。
<img src="https://img2018.cnblogs.com/blog/1070330/201907/1070330-20190729103606473-351721560.png" alt="">
窗体代码大概是这样的比较简单，只是实现简单的操作，具体细节那就交个大家了。。。
<div class="cnblogs_code">
<pre> 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 }</pre>
</div>
<h2>7.运行效果</h2>
<img src="https://img2018.cnblogs.com/blog/1070330/201907/1070330-20190728173036950-1723061288.png" alt="">
<img src="https://img2018.cnblogs.com/blog/1070330/201907/1070330-20190728173959495-1491839690.gif" alt="">
<h2>8.项目结构文件</h2>
 <img src="https://img2018.cnblogs.com/blog/1070330/201907/1070330-20190728172944307-457444835.png" alt="">
 到这里就完了，新手自己去下载调试吧，。。。。。爱你们哟  么么哒！❥(^_-)
<h1>源代码工程下载地址 </h1>
或者博客留言，给出邮箱，发你！！！
 
直接下载：https://files.cnblogs.com/files/JiYF/SQLiteDBTest.rar
<h2>温馨小提示：</h2>
SQLite版本问题，如果你使用的32位，记得项目编译采用X86   如果你是64位则采用X64  否则会提示错误信息，就酱紫。。。。。。。。。。
  
来源：https://www.cnblogs.com/JiYF/p/11260178.html

頁: [1]

琼殿技术论坛's Archiver

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

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