数据库访问驱动

以下是两种数据库访问的公用方法：

1、OleDb驱动：

    /// <summary>
    /// Oledb数据库驱动
    /// </summary>
    public class OledbHelper
    {
        #region ExcelHandler
        /// <summary>
        /// 获取OleDbConnection
        /// </summary>
        /// <param name="filePath">文件全路径</param>
        /// <returns></returns>
        private static OleDbConnection GetCon(string filePath)
        {
            string strCon = string.Empty;
            string extension = Path.GetExtension(filePath);
            if (extension == ".xls")
                strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;IMEX=1'";
            else
                strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + filePath + "';Extended Properties='Excel 12.0;HDR=YES'";
            return new OleDbConnection(strCon);
        }
        /// <summary>
        /// 获取Excel中全部的的sheet名称
        /// </summary>
        /// <param name="filePath">文件全路径</param>
        /// <returns></returns>
        public static DataTable GetSheets(string filePath)
        {
            DataTable dtSn = new DataTable();
            dtSn.Columns.Add("工作表", typeof(string));
            OleDbConnection con = GetCon(filePath);
            try
            {
                DataTable dtSheetName = new DataTable();
                string sheetName = string.Empty;
                con.Open();
                DataTable dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                foreach (DataRow row in dt.Rows)
                {
                    DataRow dr = dtSn.NewRow();
                    dr["工作表"] = row["TABLE_NAME"];
                    dtSn.Rows.Add(dr);
                }
            }
            catch (System.Exception ex)
            {
                con.Close();
                throw ex;
            }
            con.Close();
            return dtSn;

        }
        /// <summary>
        /// 获取excel中的数据DataTable
        /// </summary>
        /// <param name="filePath">文件全路径</param>
        /// <param name="sheet">工作表名称</param>
        /// <returns></returns>
        public static DataTable GetDataTable(string filePath, string sheet)
        {
            OleDbConnection con = GetCon(filePath);
            con.Open();
            DataTable dt = new DataTable();
            try
            {
                string sql = "SELECT * FROM [" + sheet + "]";
                System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(sql, con);
                myCommand.Fill(dt);
            }
            catch (System.Exception ex)
            {
                con.Close();
                throw;
            }
            con.Close();
            return dt;
        }
        /// <summary>
        /// 获取excel中的数据DataSet
        /// </summary>
        /// <param name="filePath">文件全路径</param>
        /// <param name="sheet">工作表名称</param>
        /// <returns></returns>
        public static DataSet GetDataSet(string filePath, string[] sheets)
        {
            OleDbConnection con = GetCon(filePath);
            con.Open();
            DataSet ds = new DataSet();
            try
            {
                foreach (string sheet in sheets)
                {
                    if (ds.Tables.Contains(sheet))
                        continue;
                    string sql = "SELECT * FROM [" + sheet + "]";
                    OleDbDataAdapter myCommand = new OleDbDataAdapter(sql, con);
                    myCommand.Fill(ds, sheet);
                }
            }
            catch (System.Exception ex)
            {
                con.Close();
                throw;
            }
            con.Close();
            return ds;
        }
        #endregion
    }

2、SqlDb驱动：

    /// <summary>
    /// sql数据库驱动
    /// </summary>
    public abstract class SqlHelper
    {
        private static string constr = Config.ConString;

        #region AddReturnNewID
        /// <summary>
        /// 添加数据并返回新ID
        /// </summary>
        /// <param name="safeSql">Insert语句</param>
        /// <returns></returns>
        public static int AddReturnNewID(string safeSql)
        {
            return AddReturnNewID(safeSql, null);
        }
        /// <summary>
        /// 添加并返回新IDed
        /// </summary>
        /// <param name="safeSql">Insert语句</param>
        /// <param name="paras">参数</param>
        /// <returns></returns>
        public static int AddReturnNewID(string safeSql, params SqlParameter[] paras)
        {
            string sql = safeSql + " select SCOPE_IDENTITY()";
            return ExecuteScalar(sql, paras).ToStr().ToInt();
        }
        #endregion

        #region ExecuteSql
        /// <summary>
        /// 执行sql语句，返回受影响行数
        /// </summary>
        /// <param name="safeSql">sql语句</param>
        /// <returns></returns>
        public static int ExecuteSql(string safeSql)
        {
            return ExecuteSql(safeSql, null);
        }
        /// <summary>
        /// 执行sql语句，返回受影响行数
        /// </summary>
        /// <param name="safeSql">sql语句</param>
        /// <param name="paras">参数</param>
        /// <returns></returns>
        public static int ExecuteSql(string safeSql, params SqlParameter[] paras)
        {
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        con.Open();
                        SetCmdParam(con, cmd, CommandType.Text, safeSql, paras);
                        int result = cmd.ExecuteNonQuery();
                        return result;
                    }
                    catch (SqlException e)
                    {
                        cmd.Dispose();
                        con.Close();
                        throw e;
                    }
                }
            }
        }
        #endregion

        #region ExecuteSqls

        /// <summary>
        /// 批量执行查询语句（包括存储过程），放到DataSet中
        /// </summary>
        /// <param name="SQLStringList">查询语句集合</param>
        /// <param name="LstParas">每条sql对应的参数组</param>        
        /// <returns></returns>
        public static DataSet ExecuteSqls(List<String> SQLStringList)
        {
            return ExecuteSqls(SQLStringList, null);
        }
        /// <summary>
        /// 批量执行查询语句（包括存储过程），放到DataSet中
        /// </summary>
        /// <param name="SQLStringList">查询语句集合</param>
        /// <param name="LstParas">每条sql对应的参数组</param>        
        /// <returns></returns>
        public static DataSet ExecuteSqls(List<String> SQLStringList, List<SqlParameter[]> LstParas)
        {
            using (SqlConnection con = new SqlConnection(constr))
            {
                con.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                DataSet ds = new DataSet();
                try
                {
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        string strsql = SQLStringList[n];
                        SetCmdParam(con, cmd, CommandType.Text, strsql, null);
                        DataTable dt = new DataTable();
                        SqlDataAdapter sda = new SqlDataAdapter(cmd);
                        sda.Fill(dt);
                        ds.Tables.Add(dt);
                        cmd.Parameters.Clear();
                    }
                    return ds;
                }
                catch
                {
                    return null;
                }
            }
        }
        #endregion

        #region ExecuteSqlTran
        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">多条SQL语句</param>        
        /// <returns>执行结果</returns>
        public static int ExecuteSqlTran(List<String> SQLStringList)
        {
            return ExecuteSqlTran(SQLStringList, null);
        }
        /// <summary>
        /// 执行多条SQL语句，实现数据库事务,每条sql语句都要有同索引对应的参数，没有参数传NULL。
        /// </summary>
        /// <param name="SQLStringList">多条SQL语句</param>        
        /// <param name="LstParas">每条sql对应的参数组</param>        
        /// <returns>执行结果</returns>
        public static int ExecuteSqlTran(List<String> SQLStringList, List<SqlParameter[]> LstParas)
        {
            using (SqlConnection con = new SqlConnection(constr))
            {
                con.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                SqlTransaction tx = con.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    int count = 0;
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        string strsql = SQLStringList[n];
                        if (strsql.Trim().Length > 1)
                        {
                            if (LstParas == null)
                                SetCmdParam(con, cmd, strsql);
                            else
                                SetCmdParam(con, cmd, strsql, LstParas[n]);
                            count += cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();
                        }
                    }
                    tx.Commit();
                    return count;
                }
                catch
                {
                    tx.Rollback();
                    return 0;
                }
            }
        }
        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">多条SQL语句</param>        
        /// <param name="returnValue">异常信息</param>        
        /// <returns>异常信息</returns>
        public static int ExecuteSqlTran(List<String> SQLStringList, out string returnValue)
        {
            return ExecuteSqlTran(SQLStringList, null, out returnValue);
        }
        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">多条SQL语句</param>        
        /// <param name="LstParas">每条sql对应的参数组</param>        
        /// <param name="returnValue">异常信息</param>        
        /// <returns>异常信息</returns>
        public static int ExecuteSqlTran(List<String> SQLStringList, List<SqlParameter[]> LstParas, out string returnValue)
        {
            returnValue = string.Empty;
            using (SqlConnection conn = new SqlConnection(constr))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                SqlTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    int count = 0;
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        string strsql = SQLStringList[n];
                        if (strsql.Trim().Length > 1)
                        {
                            SetCmdParam(conn, cmd, strsql, LstParas[n]);
                            count += cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();
                        }
                    }
                    tx.Commit();
                    return count;
                }
                catch (Exception ex)
                {
                    returnValue = ex.Message;
                    tx.Rollback();
                    return 0;
                }
            }
        }
        #endregion

        #region ExecuteScalar
        /// <summary>
        /// 执行sql语句，返回第一行第一列的值
        /// </summary>
        /// <param name="safeSql">sql语句</param>
        /// <returns></returns>
        public static object ExecuteScalar(string safeSql)
        {
            return ExecuteScalar(safeSql, null);
        }
        /// <summary>
        /// 执行sql语句，返回第一行第一列的值
        /// </summary>
        /// <param name="safeSql">sql语句</param>
        /// <param name="paras">参数</param>
        /// <returns></returns>
        public static object ExecuteScalar(string safeSql, params SqlParameter[] paras)
        {
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        con.Open();
                        SetCmdParam(con, cmd, CommandType.Text, safeSql, paras);
                        object obj = cmd.ExecuteScalar();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                            return null;
                        else
                            return obj;
                    }
                    catch (SqlException e)
                    {
                        cmd.Dispose();
                        con.Close();
                        throw e;
                    }
                }
            }
        }
        #endregion

        #region ExecuteProcedure

        #region 受影响行数
        /// <summary>
        /// 执行存储过程，返回影响的行数        
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="rowsAffected">影响的行数</param>
        /// <returns></returns>
        public static int ExecuteProcedure(string storedProcName, out int rowsAffected)
        {
            return ExecuteProcedure(storedProcName, out rowsAffected, null);
        }
        /// <summary>
        /// 执行存储过程，返回影响的行数        
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="rowsAffected">影响的行数</param>
        /// <param name="paras">存储过程参数</param>
        /// <returns></returns>
        public static int ExecuteProcedure(string storedProcName, out int rowsAffected, params SqlParameter[] paras)
        {
            using (SqlConnection con = new SqlConnection(constr))
            {
                try
                {
                    int result;
                    con.Open();
                    SqlCommand cmd = BuildIntCommand(con, storedProcName, paras);
                    rowsAffected = cmd.ExecuteNonQuery();
                    result = (int)cmd.Parameters["ReturnValue"].Value;
                    return result;
                }
                catch (SqlException e)
                {
                    con.Close();
                    throw e;
                }
            }
        }
        #endregion

        #region 返回查询数据表
        /// <summary>
        /// 执行sql语句，返回查询数据
        /// </summary>
        /// <param name="storedProcName"></param>
        /// <returns></returns>
        public static DataTable ExecuteProcedure(string storedProcName)
        {
            return ExecuteProcedure(storedProcName, null);
        }
        /// <summary>
        /// 执行sql语句，返回查询数据
        /// </summary>
        /// <param name="storedProcName"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static DataTable ExecuteProcedure(string storedProcName, params SqlParameter[] paras)
        {
            using (SqlConnection con = new SqlConnection(constr))
            {
                DataTable dt = new DataTable();
                con.Open();
                SqlDataAdapter sda = new SqlDataAdapter();
                sda.SelectCommand = BuildQueryCommand(con, storedProcName, paras);
                sda.Fill(dt);
                con.Close();
                return dt;
            }
        }
        #endregion

        #endregion

        #region GetDataTable
        /// <summary>
        /// 通过SQL语句查询多条数据库信息
        /// </summary>
        /// <returns>DataTable对象</returns>
        public static DataTable GetDataTable(string safeSql)
        {
            return GetDataTable(safeSql, null);
        }
        /// <summary>
        /// 通过SQL语句查询多条数据库信息
        /// </summary>
        /// <returns>DataTable对象</returns>
        public static DataTable GetDataTable(string safeSql, params SqlParameter[] paras)
        {
            return GetDataTable(safeSql, CommandType.Text, paras);
        }

        /// <summary>
        /// 获取DataTable数据
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="cmdText">SQL语句</param>
        /// <param name="cmdType">SQL类型</param>
        /// <param name="paras">参数</param>
        /// <returns>DataTable</returns>
        public static DataTable GetDataTable(string cmdText, CommandType cmdType, params SqlParameter[] paras)
        {
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        con.Open();
                        SetCmdParam(con, cmd, cmdType, cmdText, paras);
                        DataTable dt = new DataTable();
                        SqlDataAdapter sda = new SqlDataAdapter(cmd);
                        sda.Fill(dt);
                        return dt;
                    }
                    catch (SqlException e)
                    {
                        cmd.Dispose();
                        con.Close();
                        throw e;
                    }
                }
            }
        }

        #endregion

        #region 构建cmd参数

        #region SetCmdParam

        /// <summary>
        /// 构建cmd参数
        /// </summary>
        /// <param name="con"></param>
        /// <param name="cmd"></param>
        /// <param name="cmdText">文本</param>
        /// <param name="paras">参数</param>
        private static void SetCmdParam(SqlConnection con, SqlCommand cmd, string cmdText)
        {
            SetCmdParam(con, cmd, CommandType.Text, cmdText, null);
        }
        /// <summary>
        /// 构建cmd参数
        /// </summary>
        /// <param name="con"></param>
        /// <param name="cmd"></param>
        /// <param name="cmdText">文本</param>
        /// <param name="paras">参数</param>
        private static void SetCmdParam(SqlConnection con, SqlCommand cmd, string cmdText, SqlParameter[] paras)
        {
            SetCmdParam(con, cmd, CommandType.Text, cmdText, paras);
        }
        /// <summary>
        /// 构建cmd参数
        /// </summary>
        /// <param name="con"></param>
        /// <param name="cmd"></param>
        /// <param name="cmdType">类型</param>
        /// <param name="cmdText">文本</param>
        /// <param name="paras">参数</param>
        private static void SetCmdParam(SqlConnection con, SqlCommand cmd, CommandType cmdType, string cmdText, params SqlParameter[] paras)
        {
            if (con.State == ConnectionState.Closed)
                con.Open();
            cmd.Connection = con;
            cmd.CommandText = cmdText;
            cmd.CommandType = cmdType;
            if (paras == null)
                return;
            foreach (SqlParameter para in paras)
            {
                if ((para.Direction == ParameterDirection.InputOutput || para.Direction == ParameterDirection.Input) && para.Value == DBNull.Value)
                    para.Value = DBNull.Value;
                cmd.Parameters.Add(para);
            }
        }
        #endregion

        #region BuildIntCommand

        /// <summary>
        /// 创建 SqlCommand 对象实例(用来返回一个整数值)    
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlCommand 对象实例</returns>
        private static SqlCommand BuildIntCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
            command.Parameters.Add(new SqlParameter("ReturnValue",
                SqlDbType.Int, 4, ParameterDirection.ReturnValue,
                false, 0, 0, string.Empty, DataRowVersion.Default, null));
            return command;
        }

        /// <summary>
        /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
        /// </summary>
        /// <param name="con">数据库连接</param>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="paras">存储过程参数</param>
        /// <returns>SqlCommand</returns>
        private static SqlCommand BuildQueryCommand(SqlConnection con, string storedProcName, IDataParameter[] paras)
        {
            if (con.State == ConnectionState.Closed)
                con.Open();
            SqlCommand cmd = new SqlCommand(storedProcName, con);
            cmd.CommandType = CommandType.StoredProcedure;
            if (paras == null || paras.Length == 0)
                return cmd;
            foreach (SqlParameter para in paras)
            {
                if (para != null)
                {
                    // 检查未分配值的输出参数,将其分配以DBNull.Value.
                    if ((para.Direction == ParameterDirection.InputOutput || para.Direction == ParameterDirection.Input) &&
                        (para.Value == null))
                    {
                        para.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(para);
                }
            }
            return cmd;
        }
        #endregion

        #endregion
    }