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

缺乏、安全感 2021-11-01 06:58 1156阅读 0赞

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

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

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

本例子运行效果

![1070330-20190728173959495-1491839690.gif][]

正式开始吧

## 1.SQLite的下载安装 ##

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

![1070330-20190728170750649-1891493148.png][]

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

![1070330-20190728171303224-1222411051.png][]

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

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

![1070330-20190728171607462-2002888896.png][]

## 4.创建数据库以及表 ##

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

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

![1070330-20190729103409181-369025457.png][]

通过SQLiteStudio打开该数据库

![1070330-20190728171811835-1580710890.png][]

## 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

![1070330-20190728172435837-716839749.png][]

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

![1070330-20190729103606473-351721560.png][]

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

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.运行效果 ##

![1070330-20190728173036950-1723061288.png][]

![1070330-20190728173959495-1491839690.gif][]

## 8.项目结构文件 ##

![1070330-20190728172944307-457444835.png][]

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

# [源代码工程下载地址][Link 2]  #

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

## 温馨小提示： ##

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

转载于:https://www.cnblogs.com/JiYF/p/11260178.html

[1070330-20190728173959495-1491839690.gif]: /images/20211101/a0517139ec914c6fbcacef625e42aea4.png
[Link 1]: https://files.cnblogs.com/files/JiYF/sqlite-netFx40-binary-Win32-2010-1.0.111.0.zip
[1070330-20190728170750649-1891493148.png]: /images/20211101/09ba4c19202048a5b6c31f61d2dce5c9.png
[1070330-20190728171303224-1222411051.png]: /images/20211101/0c0dbb8a62364e479c12948e2926d36c.png
[https_sqlitestudio.pl_index.rvt_act_download_]: https://sqlitestudio.pl/index.rvt?act=download
[1070330-20190728171607462-2002888896.png]: /images/20211101/53f8c274caad496c870048683f29bc85.png
[1070330-20190729103409181-369025457.png]: /images/20211101/f0430dc712f94c569b98bc148efeada8.png
[1070330-20190728171811835-1580710890.png]: /images/20211101/c2f748e586254f03bf1bf789eb5a06d6.png
[1070330-20190728172435837-716839749.png]: /images/20211101/00500dd7f541485b9b68cb1b684a63f6.png
[1070330-20190729103606473-351721560.png]: /images/20211101/529439ffb08d444e9e045a075fa6f5d7.png
[1070330-20190728173036950-1723061288.png]: /images/20211101/bea9555096834c4a83d3e9b624206d4c.png
[1070330-20190728172944307-457444835.png]: /images/20211101/52c3ed9453ab47ab9e6a597960da48b5.png
[Link 2]: https://download.csdn.net/download/coderjyf/11441858