C#关于SQLite数据库增、删、改、查Demo程序设计开发

C#语言基础

下载此实例

开发语言：C#
实例大小：3.02M
下载次数：192
浏览次数：769
发布时间：2020-12-08
实例类别：C#语言基础
发布人：qingfengjyf
文件格式：.rar
所需积分：2

相关标签： SQLite 数据库 sql 欢迎操作

网友评论举报投诉收藏该页

下载此实例

实例介绍

[下载地址]

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.项目结构文件

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

标签： SQLite 数据库 sql 欢迎操作

实例下载地址