1  public static class SQLiteHelper  2     {  3         private static string connectionString = string.Empty;  4   5         #region void SetConnectionString(string datasource, string password, int version = 3) 根据数据源、密码、版本号设置连接字符串  6         /// <summary>  7         /// 根据数据源、密码、版本号设置连接字符串。  8         /// </summary>  9         /// <param name="datasource">数据源。</param> 10         /// <param name="password">密码。</param> 11         /// <param name="version">版本号（缺省为3）。</param> 12         public static void SetConnectionString(string datasource, string password, int version = 3) 13         { 14             connectionString = string.Format("Data Source={0};Version={1};password={2};Pooling=true;FailIfMissing=false", 15                 datasource, version, password); 16         } 17         #endregion 18  19         #region void CreateDB(string dbName) 创建一个数据库文件。如果存在同名数据库文件，则会覆盖。 20         /// <summary> 21         /// 创建一个数据库文件。如果存在同名数据库文件，则会覆盖。 22         /// </summary> 23         /// <param name="dbName">数据库文件名。为null或空串时不创建。</param> 24         /// <param name="password">（可选）数据库密码，默认为空。</param> 25         /// <exception cref="Exception"></exception> 26         public static void CreateDB(string dbName) 27         { 28             if (!string.IsNullOrEmpty(dbName)) 29             { 30                 try { SQLiteConnection.CreateFile(dbName); } 31                 catch (Exception) { throw; } 32             } 33         } 34         #endregion 35  36         #region void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, string cmdText, params SQLiteParameter[] parameters)准备操作命令参数 37         /// <summary> 38         /// 准备操作命令参数 39         /// </summary> 40         /// <param name="cmd">SQLiteCommand</param> 41         /// <param name="conn">SQLiteConnection</param> 42         /// <param name="cmdText">Sql命令文本</param> 43         private static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, string cmdText, params SQLiteParameter[] parameters) 44         { 45             if (conn.State != ConnectionState.Open) 46                 conn.Open(); 47             cmd.Parameters.Clear(); 48             cmd.Connection = conn; 49             cmd.CommandText = cmdText; 50             cmd.CommandType = CommandType.Text; 51             cmd.CommandTimeout = 30; 52             if (parameters.Length != 0) 53             { 54                 cmd.Parameters.AddRange(parameters); 55             } 56         } 57         #endregion 58  59         #region ExecuteNonQuery(string sql, params SQLiteParameter[] parameters) 对SQLite数据库执行增删改操作，返回受影响的行数。  60         /// <summary>  61         /// 对SQLite数据库执行增删改操作，返回受影响的行数。  62         /// </summary>  63         /// <param name="sql">要执行的增删改的SQL语句。</param>  64         /// <param name="parameters">执行增删改语句所需要的参数，参数必须以它们在SQL语句中的顺序为准。</param>  65         /// <returns></returns>  66         /// <exception cref="Exception"></exception> 67         public static int ExecuteNonQuery(string sql, params SQLiteParameter[] parameters) 68         { 69             int affectedRows = 0; 70             using (SQLiteConnection connection = new SQLiteConnection(connectionString)) 71             { 72                 using (SQLiteCommand command = new SQLiteCommand()) 73                 { 74                     try 75                     { 76                         PrepareCommand(command, connection, sql, parameters); 77                         affectedRows = command.ExecuteNonQuery(); 78                     } 79                     catch (Exception) { throw; } 80                 } 81             } 82             return affectedRows; 83         } 84         #endregion 85  86         #region void ExecuteNonQueryBatch(List<KeyValuePair<string, SQLiteParameter[]>> list) 批量处理数据操作语句 87         /// <summary> 88         /// 批量处理数据操作语句。 89         /// </summary> 90         /// <param name="list">SQL语句集合。</param> 91         /// <exception cref="Exception"></exception> 92         public static void ExecuteNonQueryBatch(List<KeyValuePair<string, SQLiteParameter[]>> list) 93         { 94             using (SQLiteConnection conn = new SQLiteConnection(connectionString)) 95             { 96                 if (conn.State != ConnectionState.Open) 97                     conn.Open(); 98                 using (SQLiteTransaction tran = conn.BeginTransaction()) 99                 {100                     using (SQLiteCommand cmd = new SQLiteCommand(conn))101                     {102                         try103                         {104                             foreach (var item in list)105                             {106                                 cmd.CommandText = item.Key;107                                 if (item.Value != null)108                                 {109                                     cmd.Parameters.AddRange(item.Value);110                                 }111                                 cmd.ExecuteNonQuery();112                             }113                             tran.Commit();114                         }115                         catch (Exception) { tran.Rollback(); throw; }116                     }117                 }118             }119         }120         #endregion121 122         #region object ExecuteScalar(string sql, params SQLiteParameter[] parameters) 执行查询语句，并返回第一个结果。123         /// <summary>124         /// 执行查询语句，并返回第一个结果。125         /// </summary>126         /// <param name="sql">查询语句。</param>127         /// <returns>查询结果。</returns>128         /// <exception cref="Exception"></exception>129         public static object ExecuteScalar(string sql, params SQLiteParameter[] parameters)130         {131             using (SQLiteConnection connection = new SQLiteConnection(connectionString))132             {133                 using (SQLiteCommand command = new SQLiteCommand())134                 {135                     try136                     {137                         PrepareCommand(command, connection, sql, parameters);138 139                         return command.ExecuteScalar();140                     }141                     catch (Exception) { throw; }142                 }143             }144         }145         #endregion146 147         #region DataTable ExecuteQuery(string sql, params SQLiteParameter[] parameters)执行一个查询语句，返回一个包含查询结果的DataTable。 148         /// <summary> 149         /// 执行一个查询语句，返回一个包含查询结果的DataTable。 150         /// </summary> 151         /// <param name="sql">要执行的查询语句。</param> 152         /// <param name="parameters">执行SQL查询语句所需要的参数，参数必须以它们在SQL语句中的顺序为准。</param> 153         /// <returns></returns> 154         /// <exception cref="Exception"></exception>155         public static DataTable ExecuteQuery(string sql, params SQLiteParameter[] parameters)156         {157             using (SQLiteConnection connection = new SQLiteConnection(connectionString))158             {159                 using (SQLiteCommand command = new SQLiteCommand())160                 {161                     PrepareCommand(command, connection, sql, parameters);162 163                     SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);164                     DataTable data = http://www.mamicode.com/new DataTable();165                     try { adapter.Fill(data); }166                     catch (Exception) { throw; }167                     return data;168                 }169             }170         }171 172         #endregion173 174         #region SQLiteDataReader ExecuteReader(string sql, params SQLiteParameter[] parameters) 执行一个查询语句，返回一个关联的SQLiteDataReader实例。 175         /// <summary> 176         /// 执行一个查询语句，返回一个关联的SQLiteDataReader实例。 177         /// </summary> 178         /// <param name="sql">要执行的查询语句。</param> 179         /// <param name="parameters">执行SQL查询语句所需要的参数，参数必须以它们在SQL语句中的顺序为准。</param> 180         /// <returns></returns> 181         /// <exception cref="Exception"></exception>182         public static SQLiteDataReader ExecuteReader(string sql, params SQLiteParameter[] parameters)183         {184             using (SQLiteConnection connection = new SQLiteConnection(connectionString))185             {186                 using (SQLiteCommand command = new SQLiteCommand())187                 {188                     try189                     {190                         PrepareCommand(command, connection, sql, parameters);191                         return command.ExecuteReader(CommandBehavior.CloseConnection);192                     }193                     catch (Exception) { throw; }194                 }195             }196         }197 198         #endregion199 200         #region DataSet ExecutePager(ref int recordCount, int pageIndex, int pageSize, string cmdText, string countText, params SQLiteParameter[] parameters)分页查询201         /// <summary>202         /// 分页查询203         /// </summary>204         /// <param name="recordCount">总记录数</param>205         /// <param name="pageIndex">页牵引</param>206         /// <param name="pageSize">页大小</param>207         /// <param name="cmdText">Sql命令文本</param>208         /// <param name="countText">查询总记录数的Sql文本</param>209         /// <param name="parameters">命令参数</param>210         /// <returns>DataSet</returns>211         public static DataSet ExecutePager(ref int recordCount, int pageIndex, int pageSize, string cmdText, string countText, params SQLiteParameter[] parameters)212         {213             if (recordCount < 0)214                 recordCount = int.Parse(ExecuteScalar(countText).ToString());215             var ds = new DataSet();216             using (SQLiteConnection connection = new SQLiteConnection(connectionString))217             {218                 using (SQLiteCommand command = new SQLiteCommand())219                 {220                     PrepareCommand(command, connection, cmdText, parameters);221 222                     SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);223                     adapter.Fill(ds, (pageIndex - 1) * pageSize, pageSize, "result");224                 }225                 return ds;226             }227         } 228         #endregion229 230         #region DataTable GetSchema()查询数据库中的所有数据类型信息231         /// <summary> 232         /// 查询数据库中的所有数据类型信息。233         /// </summary> 234         /// <returns></returns> 235         /// <exception cref="Exception"></exception>236         public static DataTable GetSchema()237         {238             using (SQLiteConnection connection = new SQLiteConnection(connectionString))239             {240                 try241                 {242                     connection.Open();243                     return connection.GetSchema("TABLES");244                 }245                 catch (Exception) { throw; }246             }247         }248         #endregion249 250         #region int ResetDataBass() 重置自动增长列，如果执行了删除操作，自动增长列就会变的不连续，通过使用VACUUM方式重置251         /// <summary>252         /// 重置自动增长列，如果执行了删除操作，自动增长列就会变的不连续，通过使用VACUUM方式重置253         /// </summary>254         public static int ResetDataBass()255         {256             using (SQLiteConnection conn = new SQLiteConnection(connectionString))257             {258                 using (SQLiteCommand command = new SQLiteCommand())259                 {260                     PrepareCommand(command, conn, "vacuum");261 262                     return command.ExecuteNonQuery();263                 }264             }265         }266         #endregion267     }