C#操作SQLite数据库增、删、改、查 实例
本文共 33355 字，大约阅读时间需要 111 分钟。
最近项目上要使用SQLite数据库，不怕大伙笑话毕业四年多了，一直使用Oracle或者MySQL或者SQLServer，但是真的是没有用过SQLite数据库，据说非常轻量级，但是真没有用过，于是网上大概搜索下，例子比较多，但是么有一个完整的，即使有，也五花八门。。看的人头晕，可能对于他们大神来讲这都太简单了，算求了更新一篇，新来的小伙伴请看下文，大神你就直接飘过吧!
本例子运行效果
正式开始吧
1.SQLite的下载安装
自然官网下载对应版本即可，我这里下载的是 Precompiled Binaries for 32-bit Windows (.NET Framework 4.0) 不愿意下载的 可以直接使用我下载过的
2.减压找出我们所需要的库文件 这里我们只需要俩个文件下图所示
3.下载一个数据库管理系统来创建数据库
我是用的是SQLiteStudio3.1.1版本 自己下载吧 下载地址：
4.创建数据库以及表
这里创建一个简单的表 UserInfo 完了代码中会有这个数据库 在Debug\bin目录下
SQLite数据库文件就一个后缀为.db的文件，就没了。。
通过SQLiteStudio打开该数据库
5.开始使用大伙所熟悉的VS了创建相关类
userinfo的Mode类
1 ///2 /// UserInfo:实体类(属性说明自动提取数据库字段的描述信息) 3 /// 4 [Serializable] 5 public partial class UserInfo 6 { 7 public UserInfo() 8 { } 9 #region Model10 private int _id;11 private string _username;12 private string _pwd;13 private int? _age;14 ///15 /// 16 /// 17 public int ID18 {19 set { _id = value; }20 get { return _id; }21 }22 ///23 /// 24 /// 25 public string UserName26 {27 set { _username = value; }28 get { return _username; }29 }30 ///31 /// 32 /// 33 public string Pwd34 {35 set { _pwd = value; }36 get { return _pwd; }37 }38 ///39 /// 40 /// 41 public int? Age42 {43 set { _age = value; }44 get { return _age; }45 }46 #endregion Model47 48 }
UserInfo 的Dal类
1 ///2 /// 数据访问类:UserInfo 3 /// 4 public partial class UserInfo 5 { 6 public UserInfo() 7 { } 8 #region BasicMethod 9 10 ///11 /// 得到最大ID 12 /// 13 public int GetMaxId() 14 { 15 return DbHelperSQLite.GetMaxID("ID", "UserInfo"); 16 } 17 18 ///19 /// 是否存在该记录 20 /// 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 ///35 /// 增加一条数据 36 /// 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 ///63 /// 更新一条数据 64 /// 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 ///95 /// 删除一条数据 96 /// 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 else113 {114 return false;115 }116 }117 ///118 /// 批量删除数据119 /// 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 else131 {132 return false;133 }134 }135 136 137 ///138 /// 得到一个对象实体139 /// 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 else157 {158 return null;159 }160 }161 162 163 ///164 /// 得到一个对象实体165 /// 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 ///192 /// 获得数据列表193 /// 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 ///207 /// 获取记录总数208 /// 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 else223 {224 return Convert.ToInt32(obj);225 }226 }227 ///228 /// 分页获取数据列表229 /// 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 else240 {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 BasicMethod254 }
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 /// 9 /// 得到最大ID 10 /// 11 public int GetMaxId() 12 { 13 return dal.GetMaxId(); 14 } 15 16 /// 17 /// 是否存在该记录 18 /// 19 public bool Exists(int ID) 20 { 21 return dal.Exists(ID); 22 } 23 24 /// 25 /// 增加一条数据 26 /// 27 public bool Add(Model.UserInfo model) 28 { 29 return dal.Add(model); 30 } 31 32 /// 33 /// 更新一条数据 34 /// 35 public bool Update(Model.UserInfo model) 36 { 37 return dal.Update(model); 38 } 39 40 /// 41 /// 删除一条数据 42 /// 43 public bool Delete(int ID) 44 { 45 46 return dal.Delete(ID); 47 } 48 /// 49 /// 删除一条数据 50 /// 51 public bool DeleteList(string IDlist) 52 { 53 return dal.DeleteList(IDlist); 54 } 55 56 /// 57 /// 得到一个对象实体 58 /// 59 public Model.UserInfo GetModel(int ID) 60 { 61 62 return dal.GetModel(ID); 63 } 64 65 / 66 / 得到一个对象实体，从缓存中 67 / 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 /// 90 /// 获得数据列表 91 /// 92 public DataSet GetList(string strWhere) 93 { 94 return dal.GetList(strWhere); 95 } 96 /// 97 /// 获得数据列表 98 /// 99 public List GetModelList(string strWhere)100 {101 DataSet ds = dal.GetList(strWhere);102 return DataTableToList(ds.Tables[0]);103 }104 /// 105 /// 获得数据列表106 /// 107 public List DataTableToList(DataTable dt)108 {109 List modelList = new List ();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 /// 127 /// 获得数据列表128 /// 129 public DataSet GetAllList()130 {131 return GetList("");132 }133 134 /// 135 /// 分页获取数据列表136 /// 137 public int GetRecordCount(string strWhere)138 {139 return dal.GetRecordCount(strWhere);140 }141 /// 142 /// 分页获取数据列表143 /// 144 public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)145 {146 return dal.GetListByPage(strWhere, orderby, startIndex, endIndex);147 }148 /// 149 /// 分页获取数据列表150 /// 151 //public DataSet GetList(int PageSize,int PageIndex,string strWhere)152 //{153 //return dal.GetList(PageSize,PageIndex,strWhere);154 //}155 156 #endregion BasicMethod157 #region ExtensionMethod158 159 #endregion ExtensionMethod160 }
创建SQLite帮助类
1 ///2 /// 数据访问基础类(基于SQLite) 3 /// 可以用户可以修改满足自己项目的需要。 4 /// 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 ///83 /// 执行SQL语句，返回影响的记录数 84 /// 85 /// SQL语句 86 ///影响的记录数 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 ///109 /// 执行多条SQL语句，实现数据库事务。110 /// 111 /// 多条SQL语句 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 try122 {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 ///142 /// 执行带一个存储过程参数的的SQL语句。143 /// 144 /// SQL语句145 /// 参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加146 ///影响的记录数 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 try156 {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 finally166 {167 cmd.Dispose();168 connection.Close();169 }170 }171 }172 ///173 /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)174 /// 175 /// SQL语句176 /// 图像字节,数据库的字段类型为image的情况177 ///影响的记录数 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 try187 {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 finally197 {198 cmd.Dispose();199 connection.Close();200 }201 }202 }203 204 ///205 /// 执行一条计算查询结果语句，返回查询结果（object）。206 /// 207 /// 计算查询结果语句208 ///查询结果（object） 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 try216 {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 else224 {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 ///237 /// 执行查询语句，返回SQLiteDataReader238 /// 239 /// 查询语句240 ///SQLiteDataReader 241 public static SQLiteDataReader ExecuteReader(string strSQL)242 {243 SQLiteConnection connection = new SQLiteConnection(connectionString);244 SQLiteCommand cmd = new SQLiteCommand(strSQL, connection);245 try246 {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 ///258 /// 执行查询语句，返回DataSet259 /// 260 /// 查询语句261 ///DataSet 262 public static DataSet Query(string SQLString)263 {264 using (SQLiteConnection connection = new SQLiteConnection(connectionString))265 {266 DataSet ds = new DataSet();267 try268 {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 #endregion283 284 #region 执行带参数的SQL语句285 286 ///287 /// 执行SQL语句，返回影响的记录数288 /// 289 /// SQL语句290 ///影响的记录数 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 try298 {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 ///314 /// 执行多条SQL语句，实现数据库事务。315 /// 316 /// SQL语句的哈希表（key为sql语句，value是该语句的SQLiteParameter[]）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 try326 {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 catch340 {341 trans.Rollback();342 throw;343 }344 }345 }346 }347 348 349 ///350 /// 执行一条计算查询结果语句，返回查询结果（object）。351 /// 352 /// 计算查询结果语句353 ///查询结果（object） 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 try361 {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 else370 {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 ///383 /// 执行查询语句，返回SQLiteDataReader384 /// 385 /// 查询语句386 ///SQLiteDataReader 387 public static SQLiteDataReader ExecuteReader(string SQLString, params SQLiteParameter[] cmdParms)388 {389 SQLiteConnection connection = new SQLiteConnection(connectionString);390 SQLiteCommand cmd = new SQLiteCommand();391 try392 {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 ///406 /// 执行查询语句，返回DataSet407 /// 408 /// 查询语句409 ///DataSet 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 try420 {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 #endregion451 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.项目结构文件
SQLite版本问题，如果你使用的32位，记得项目编译采用X86 如果你是64位则采用X64 否则会提示错误信息
你可能感兴趣的文章
Java中Synchronized的用法
查看>>
阻塞队列
查看>>
linux的基础知识
查看>>
接口技术原理
查看>>
五大串口的基本原理
查看>>
PCB设计技巧与注意事项
查看>>
linux进程之间通讯常用信号
查看>>
main函数带参数
查看>>
PCB布线技巧
查看>>
关于PCB设计中过孔能否打在焊盘上的两种观点
查看>>
PCB反推理念
查看>>
京东技术架构（一）构建亿级前端读服务
查看>>
git 提示：error: unable to rewind rpc post data - try increasing http.postBuffer
查看>>
php 解决json_encode中文UNICODE转码问题
查看>>
LNMP 安装 thinkcmf提示404not found
查看>>
PHP empty、isset、innull的区别
查看>>
apache+nginx 实现动静分离
查看>>
通过Navicat远程连接MySQL配置
查看>>
phpstorm开发工具的设置用法
查看>>
Linux 系统挂载数据盘
查看>>