C#操作SQLite数据库帮助类——SQLiteHelper

using System;using System.Collections.Generic;using System.Data.SQLite;using System.Data;namespace Com.ZCWL.Rock.Helper{public class SQLiteHelper{private static string connectionString = string.Empty;/// <summary>/// 根据数据源、密码、版本号设置连接字符串。/// </summary>/// <param name="datasource">数据源。</param>/// <param name="password">密码。</param>/// <param name="version">版本号（缺省为3）。</param>public static void SetConnectionString(string datasource, string password, int version = 3){connectionString = string.Format("Data Source={0};Version={1};password={2}", datasource, version, password);}/// <summary>/// 创建一个数据库文件。如果存在同名数据库文件，则会覆盖。/// </summary>/// <param name="dbName">数据库文件名。为null或空串时不创建。</param>/// <param name="password">（可选）数据库密码，默认为空。</param>/// <exception cref="Exception"></exception>public static void CreateDB(string dbName){if (!string.IsNullOrEmpty(dbName)){try { SQLiteConnection.CreateFile(dbName); }catch (Exception) { throw; }}}/// <summary> /// 对SQLite数据库执行增删改操作，返回受影响的行数。 /// </summary> /// <param name="sql">要执行的增删改的SQL语句。</param> /// <param name="parameters">执行增删改语句所需要的参数，参数必须以它们在SQL语句中的顺序为准。</param> /// <returns></returns> /// <exception cref="Exception"></exception>public int ExecuteNonQuery(string sql, params SQLiteParameter[] parameters){int affectedRows = 0;using (SQLiteConnection connection = new SQLiteConnection(connectionString)){using (SQLiteCommand command = new SQLiteCommand(connection)){try{connection.Open();command.CommandText = sql;if (parameters.Length != 0){command.Parameters.AddRange(parameters);}affectedRows = command.ExecuteNonQuery();}catch (Exception) { throw; }}}return affectedRows;}/// <summary>/// 批量处理数据操作语句。/// </summary>/// <param name="list">SQL语句集合。</param>/// <exception cref="Exception"></exception>public void ExecuteNonQueryBatch(List<KeyValuePair<string, SQLiteParameter[]>> list){using (SQLiteConnection conn = new SQLiteConnection(connectionString)){try { conn.Open(); }catch { throw; }using (SQLiteTransaction tran = conn.BeginTransaction()){using (SQLiteCommand cmd = new SQLiteCommand(conn)){try{foreach (var item in list){cmd.CommandText = item.Key;if (item.Value != null){cmd.Parameters.AddRange(item.Value);}cmd.ExecuteNonQuery();}tran.Commit();}catch (Exception) { tran.Rollback(); throw; }}}}}/// <summary>/// 执行查询语句，并返回第一个结果。/// </summary>/// <param name="sql">查询语句。</param>/// <returns>查询结果。</returns>/// <exception cref="Exception"></exception>public object ExecuteScalar(string sql, params SQLiteParameter[] parameters){using (SQLiteConnection conn = new SQLiteConnection(connectionString)){using (SQLiteCommand cmd = new SQLiteCommand(conn)){try{conn.Open();cmd.CommandText = sql;if (parameters.Length != 0){cmd.Parameters.AddRange(parameters);}return cmd.ExecuteScalar();}catch (Exception) { throw; }}}}/// <summary> /// 执行一个查询语句，返回一个包含查询结果的DataTable。 /// </summary> /// <param name="sql">要执行的查询语句。</param> /// <param name="parameters">执行SQL查询语句所需要的参数，参数必须以它们在SQL语句中的顺序为准。</param> /// <returns></returns> /// <exception cref="Exception"></exception>public DataTable ExecuteQuery(string sql, params SQLiteParameter[] parameters){using (SQLiteConnection connection = new SQLiteConnection(connectionString)){using (SQLiteCommand command = new SQLiteCommand(sql, connection)){if (parameters.Length != 0){command.Parameters.AddRange(parameters);}SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);DataTable data = new DataTable();try { adapter.Fill(data); }catch (Exception) { throw; }return data;}}}/// <summary> /// 执行一个查询语句，返回一个关联的SQLiteDataReader实例。 /// </summary> /// <param name="sql">要执行的查询语句。</param> /// <param name="parameters">执行SQL查询语句所需要的参数，参数必须以它们在SQL语句中的顺序为准。</param> /// <returns></returns> /// <exception cref="Exception"></exception>public SQLiteDataReader ExecuteReader(string sql, params SQLiteParameter[] parameters){SQLiteConnection connection = new SQLiteConnection(connectionString);SQLiteCommand command = new SQLiteCommand(sql, connection);try{if (parameters.Length != 0){command.Parameters.AddRange(parameters);}connection.Open();return command.ExecuteReader(CommandBehavior.CloseConnection);}catch (Exception) { throw; }}/// <summary> /// 查询数据库中的所有数据类型信息。/// </summary> /// <returns></returns> /// <exception cref="Exception"></exception>public DataTable GetSchema(){using (SQLiteConnection connection = new SQLiteConnection(connectionString)){try{connection.Open();return connection.GetSchema("TABLES");}catch (Exception) { throw; }}} }}

     注意：在使用该类的时候，需要引用System.Data.SQLite程序集。