以下是两种数据库访问的公用方法：
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 }