[转载]c# 对SQLite操作类借鉴并修改 - Light Xun - 博客园

[转载]c# 对SQLite操作类借鉴并修改 – Light Xun – 博客园.

/// <summary>
/// SQLite 操作类
/// Author Light
/// </summary>
public class SQLite
{
    /// <summary>
    /// 数据库
    /// </summary>
    public static string dataBasePath;
 
    public static string dataBasePasssord;
 
    /// <summary>
    /// 获取连接
    /// </summary>
    /// <returns></returns>
    private static SQLiteConnection getSQLiteConnection()
    {
        SQLiteConnection conn = null;         
        try
        {
            conn = new SQLiteConnection();
            SQLiteConnectionStringBuilder connStr = new SQLiteConnectionStringBuilder();
            connStr.DataSource = dataBasePath;
            connStr.Password = dataBasePasssord;                        //设置密码，SQLite ADO.NET实现了数据库密码保护
            conn.ConnectionString = connStr.ToString();
        }
        catch (ExceptionUtility ex)
        {
            throw new ExceptionUtility("连接数据库异常:" + ex.Message);
        }                    
        return conn;
    }
 
    #region 执行查询
 
    /// <summary>
    /// 执行SQL，返回影响的记录数
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    public static int ExecuteSql(string sql)
    {
        int iResult = -1;
        using (SQLiteConnection conn = getSQLiteConnection())
        {
            using (SQLiteCommand cmd = new SQLiteCommand(sql, conn))
            {
                try
                {
                    conn.Open();
                    iResult = cmd.ExecuteNonQuery();
                }
                catch (ExceptionUtility ex)
                {
                    throw new ExceptionUtility("执行SQL，返回影响的记录数异常:" + ex.Message);
                }
            }
        }
        return iResult;
    }
 
    /// <summary>
    /// 执行带一个存储过程参数的SQL语句
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="content"></param>
    /// <returns></returns>
    public static int ExecuteSql(string sql, string content)
    {
        int iResult = -1;
        using (SQLiteConnection conn = getSQLiteConnection())
        {
            using (SQLiteCommand cmd = new SQLiteCommand(sql, conn))
            {
                try
                {
                    SqlParameter parameter = new SqlParameter("@content", SqlDbType.NText);
                    parameter.Value = content;
                    cmd.Parameters.Add(parameter);
                    conn.Open();
                    iResult = cmd.ExecuteNonQuery();
                }
                catch (ExceptionUtility ex)
                {
                    throw new ExceptionUtility("执行带一个存储过程参数的SQL语句异常:" + ex.Message);
                }
            }
        }
        return iResult;
    }
 
    /// <summary>
    /// 执行SQL语句，返回影响的记录数
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="sqlParams"></param>
    /// <returns></returns>
    public static int ExecuteSql(string sql, params SqlParameter[] sqlParams)
    {
        int iResult = -1;
        using (SQLiteConnection conn = getSQLiteConnection())
        {
            using (SQLiteCommand cmd = new SQLiteCommand())
            {
                try
                {
                    PrepareCommand(conn, cmd, null, sql, sqlParams);
                    iResult = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                }
                catch (ExceptionUtility ex)
                {
                    throw new ExceptionUtility("执行SQL语句，返回影响的记录数异常:" + ex.Message);
                }
            }
        }
        return iResult;
    }
 
    /// <summary>
    /// 执行查询
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    public static DataSet Query(string sql)
    {
        DataSet dsResult = null;
        using (SQLiteConnection conn = getSQLiteConnection())
        {
            using (SQLiteDataAdapter da = new SQLiteDataAdapter(sql, conn))
            {
                try
                {
                    conn.Open();
                    dsResult = new DataSet();
                    da.Fill(dsResult, "ds");
                }
                catch (ExceptionUtility ex)
                {
                    throw new ExceptionUtility("执行查询异常:" + ex.Message);
                }
            }
        }
        return dsResult;
    }
 
    /// <summary>
    /// 执行查询语句，返回DataSet
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="sqlParams"></param>
    /// <returns></returns>
    public static DataSet Query(string sql, params SqlParameter[] sqlParams)
    {
        DataSet dsResult = null;
        using (SQLiteConnection conn = getSQLiteConnection())
        {
            using (SQLiteCommand cmd = new SQLiteCommand())
            {
                using (SQLiteDataAdapter da = new SQLiteDataAdapter(cmd))
                {
                    try
                    {
                        PrepareCommand(conn, cmd, null, sql, sqlParams);
                        dsResult = new DataSet();
                        da.Fill(dsResult, "ds");
                        cmd.Parameters.Clear();
                    }
                    catch (ExceptionUtility ex)
                    {
                        throw new ExceptionUtility("执行查询返回DataSet异常:" + ex.Message);
                    }
                }
            }
        }
        return dsResult;
    }
 
    /// <summary>
    /// 执行一条计算查询结果语句，返回查询结果（object）
    /// 第一行第一列
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    public static object GetSingle(string sql)
    {
        object oResult = null;
        using (SQLiteConnection conn = getSQLiteConnection())
        {
            using (SQLiteCommand cmd = new SQLiteCommand(sql, conn))
            {
                try
                {
                    conn.Open();
                    oResult = cmd.ExecuteScalar();
                }
                catch (ExceptionUtility ex)
                {
                    throw new ExceptionUtility("执行一条计算查询结果语句，返回查询结果（object）异常:" + ex.Message);
                }
            }
        }
        return oResult;
    }
 
    /// <summary>
    /// 执行一条计算查询结果语句，返回查询结果（object）
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="sqlParams"></param>
    /// <returns></returns>
    public static object GetSingle(string sql, params SqlParameter[] sqlParams)
    {
        object oResult = null;
        using (SQLiteConnection conn = getSQLiteConnection())
        {
            using (SQLiteCommand cmd = new SQLiteCommand())
            {
                try
                {
                    PrepareCommand(conn, cmd, null, sql, sqlParams);
                    oResult = cmd.ExecuteScalar();
                    cmd.Parameters.Clear();
                }
                catch (ExceptionUtility ex)
                {
                    throw new ExceptionUtility("执行查询异常:" + ex.Message);
                }
            }
        }
        return oResult;
    }
 
    /// <summary>
    /// 执行查询，返回sqliteDataReader
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    public static SQLiteDataReader ExecuteReader(string sql)
    {
        SQLiteDataReader rResult = null;
        using (SQLiteConnection conn = getSQLiteConnection())
        {
            using (SQLiteCommand cmd = new SQLiteCommand(sql, conn))
            {
                try
                {
                    conn.Open();
                    rResult = cmd.ExecuteReader();
                }
                catch (ExceptionUtility ex)
                {
                    throw new ExceptionUtility("执行查询返回sqliteDataReader异常:" + ex.Message);
                }
            }
        }
        return rResult;
    }
 
    /// <summary>
    /// 执行查询语句，返回SqliteDataReader
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="sqlParams"></param>
    /// <returns></returns>
    public static SQLiteDataReader ExecuteReader(string sql, params SqlParameter[] sqlParams)
    {
        SQLiteDataReader rResult = null;
        using (SQLiteConnection conn = getSQLiteConnection())
        {
            using (SQLiteCommand cmd = new SQLiteCommand())
            {
                try
                {
                    PrepareCommand(conn, cmd, null, sql, sqlParams);
                    rResult = cmd.ExecuteReader();
                    cmd.Parameters.Clear();
                }
                catch (ExceptionUtility ex)
                {
                    throw new ExceptionUtility("执行查询返回SqliteDataReader异常:" + ex.Message);
                }
            }
        }
        return rResult;
    }
 
    #endregion  执行查询
 
    #region  执行事务
 
    /// <summary>
    /// 执行SQL事务操作
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    public static int ExecuteSqlTran(string sql)
    {
        int iResult = -1;
        using (SQLiteConnection conn = getSQLiteConnection())
        {
            using (SQLiteTransaction tran = conn.BeginTransaction())
            {
                using (SQLiteCommand cmd = new SQLiteCommand(sql, conn, tran))
                {
                    try
                    {
                        if (!string.IsNullOrEmpty(sql))
                        {
                            conn.Open();
                            iResult = cmd.ExecuteNonQuery();
                            tran.Commit();
                        }
                    }
                    catch (ExceptionUtility ex)
                    {
                        tran.Rollback();
                        throw new ExceptionUtility("执行SQL事务操作异常:" + ex.Message);
                    }
                }
            }
        }
        return iResult;
    }
 
    /// <summary>
    /// 执行多条SQL事务操作
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    public static int ExecuteSqlTran(List<string> sqlList)
    {
        int iResult = -1;
        using (SQLiteConnection conn = getSQLiteConnection())
        {
            using (SQLiteTransaction tran = conn.BeginTransaction())
            {
                using (SQLiteCommand cmd = new SQLiteCommand())
                {
                    try
                    {
                        conn.Open();
                        cmd.Connection = conn;
                        cmd.Transaction = tran;
                        foreach (string sql in sqlList)
                        {
                            if (!string.IsNullOrEmpty(sql))
                            {
                                cmd.CommandText = sql;
                                iResult += cmd.ExecuteNonQuery();
                            }
                        }
                        tran.Commit();
                    }
                    catch (ExceptionUtility ex)
                    {
                        tran.Rollback();
                        throw new ExceptionUtility("执行多条SQL事务操作异常:" + ex.Message);
                    }
                }
            }
        }
        return iResult;
    }
 
    /// <summary>
    /// 执行多条SQL语句，实现数据库事务
    /// </summary>
    /// <param name="sqlHashTable"></param>
    /// <returns></returns>
    public static int ExecuteSqlTran(Hashtable sqlHashTable)
    {
        int iResult = -1;
        using (SQLiteConnection conn = getSQLiteConnection())
        {
            using (SQLiteTransaction tran = conn.BeginTransaction())
            {
                using (SQLiteCommand cmd = new SQLiteCommand())
                {
                    try
                    {
                        conn.Open();
                        foreach (DictionaryEntry de in sqlHashTable)
                        {
                            string cmdSql = de.Key.ToString();
                            SqlParameter[] cmdParams = (SqlParameter[])de.Value;
                            PrepareCommand(conn, cmd, tran, cmdSql, cmdParams);
                            iResult = cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();
                            tran.Commit();
                        }
                    }
                    catch (ExceptionUtility ex)
                    {
                        tran.Rollback();
                        throw new ExceptionUtility("执行多条SQL事务异常:" + ex.Message);
                    }
                }
            }
        }
        return iResult;
    }
 
 
 
    /// <summary>
    /// 向数据库中插入图像格式字段
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="fs"></param>
    /// <returns></returns>
    public static int ExecuteSqlInsertImg(string sql, byte[] fs)
    {
        int iResult = -1;
        using (SQLiteConnection conn = getSQLiteConnection())
        {
            using (SQLiteCommand cmd = new SQLiteCommand(sql, conn))
            {
                try
                {
                    SqlParameter sqlParam = new SqlParameter("@fs", SqlDbType.Image);
                    sqlParam.Value = fs;
                    cmd.Parameters.Add(sqlParam);
                    conn.Open();
                    iResult = cmd.ExecuteNonQuery();
                }
                catch (ExceptionUtility ex)
                {
                    throw new ExceptionUtility("插入图像字段异常:" + ex.Message);
                }
            }
        }
        return iResult;
    }
 
    #endregion 执行事务
 
    #region 私有公共方法
 
    private static void PrepareCommand(SQLiteConnection conn, SQLiteCommand cmd, SQLiteTransaction tran, string sql, SqlParameter[] sqlParams)
    {
        if (conn.State != ConnectionState.Open)
        {
            conn.Open();
        }
        cmd.Connection = conn;
        cmd.CommandText = sql;
 
        if (tran != null)
        {
            cmd.Transaction = tran;
        }
        cmd.CommandType = CommandType.Text;
 
        if (sqlParams != null)
        {
            foreach (SqlParameter param in sqlParams)
            {
                cmd.Parameters.Add(param);
            }
        }
    }
 
    #endregion 私有公共方法
 
    #region 存储过程
 
    /// <summary>
    /// 执行存储过程
    /// </summary>
    /// <param name="storedProcName"></param>
    /// <param name="dataParams"></param>
    /// <returns></returns>
    public static SQLiteDataReader RunProcedure(string storedProcName, IDataParameter[] dataParams)
    {
        SQLiteDataReader rResult = null;
        using (SQLiteConnection conn = getSQLiteConnection())
        {
            using (SQLiteCommand cmd = BuildQueryCommand(conn, storedProcName, dataParams))
            {
                try
                {
                    rResult = cmd.ExecuteReader();
                }
                catch (ExceptionUtility ex)
                {
                    throw new ExceptionUtility("执行存储过程异常:" + ex.Message);
                }
            }
        }
        return rResult;
    }
 
    /// <summary>
    /// 执行存储过程
    /// </summary>
    /// <param name="storedProcName"></param>
    /// <param name="dataParams"></param>
    /// <param name="tableName"></param>
    /// <returns></returns>
    public static DataSet RunProcedure(string storedProcName, IDataParameter[] dataParams, string tableName)
    {
        DataSet dsResult = null;
        using (SQLiteConnection conn = getSQLiteConnection())
        {
            using (SQLiteDataAdapter da = new SQLiteDataAdapter())
            {
                try
                {
                    dsResult = new DataSet();
                    da.SelectCommand = BuildQueryCommand(conn, storedProcName, dataParams);
                    da.Fill(dsResult, tableName);
                }
                catch (ExceptionUtility ex)
                {
                    throw new ExceptionUtility("执行存储过程异常:" + ex.Message);
                }
            }
        }
        return dsResult;
    }
 
    /// <summary>
    /// 执行存储过程，返回影响的行数
    /// </summary>
    /// <param name="storedProcName"></param>
    /// <param name="dataParams"></param>
    /// <param name="rowsAffected"></param>
    /// <returns></returns>
    public static int RunProcedure(string storedProcName, IDataParameter[] dataParams, out int rowsAffected)
    {
        int iResult = -1;
        using (SQLiteConnection conn = getSQLiteConnection())
        {
            try
            {
                SQLiteCommand cmd = BuildIntCommand(conn, storedProcName, dataParams);
                rowsAffected = cmd.ExecuteNonQuery();
                iResult = (int)cmd.Parameters["ReturnValue"].Value;
            }
            catch (ExceptionUtility ex)
            {
                throw new ExceptionUtility("执行存储过程异常:" + ex.Message);
            }               
        }
        return iResult;
    }
 
 
    /// <summary>
    /// 创建SQLiteCommand对象实例（用来返回一个整数值）
    /// </summary>
    /// <param name="conn"></param>
    /// <param name="storedProcName"></param>
    /// <param name="dataParams"></param>
    /// <returns></returns>
    private static SQLiteCommand BuildIntCommand(SQLiteConnection conn, string storedProcName, IDataParameter[] dataParams)
    {
        SQLiteCommand cmd = BuildQueryCommand(conn, storedProcName, dataParams);
        cmd.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null));
        return cmd;
    }
 
    /// <summary>
    /// 构建SqliteCommand对象（用来返回一个结果集，而不是一个整数值）
    /// </summary>
    /// <param name="conn"></param>
    /// <param name="storedProcName"></param>
    /// <param name="dataParams"></param>
    /// <returns></returns>
    private static SQLiteCommand BuildQueryCommand(SQLiteConnection conn, string storedProcName, IDataParameter[] dataParams)
    {
        SQLiteCommand cmd = new SQLiteCommand(storedProcName, conn);
        try
        {
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            cmd.CommandType = CommandType.StoredProcedure;
            foreach (SqlParameter param in dataParams)
            {
                cmd.Parameters.Add(param);
            }
        }
        catch (ExceptionUtility ex)
        {
            throw new ExceptionUtility("构建SQLiteCommand异常:" + ex.Message);
        }
        return cmd;
    }
 
    #endregion  存储过程
 
     
}