.NET 中数据访问用的 DBHelper（Sql Server） 类

{ private static string DBConnectString = "Data Source=.;Initial Catalog=Roseonly;Integrated Security=True"; private static SqlConnection conn; public static SqlDataAdapter dap; private static SqlCommand cmd; private static SqlCommandBuilder builder; private static DBHelper dBHelper; public static DataSet ds = new DataSet(); public DBHelper() { conn = new SqlConnection(DBConnectString); } /// <summary> /// 实例化DBHelper对象 /// </summary> /// <returns></returns> public static DBHelper Instance() { if (dBHelper == null) { dBHelper = new DBHelper(); } return dBHelper; } /// <summary> /// 打开数据库连接 /// </summary> void DBOpen() { if (conn.State == ConnectionState.Closed) { conn.Open(); } } /// <summary> /// 关闭数据库连接 /// </summary> void DBClose() { if (conn.State == ConnectionState.Open) { conn.Close(); } } /// <summary> /// 执行SQL语句获取数据集 /// </summary> /// <param name="sql">sql语句</param> /// <returns>DataTable数据集</returns> public DataTable GetDataTableBySql(string sql) { DBOpen(); DataTable dt = new DataTable(); dap = new SqlDataAdapter(sql, conn); try { dap.Fill(dt); return dt; } catch { return null; } finally { DBClose(); } } /// <summary> /// 执行SQL语句 /// </summary> /// <param name="sql">SQL语句</param> /// <returns>是否执行成功</returns> public bool ExcuteSql(string sql) { DBOpen(); cmd = new SqlCommand(sql, conn); try { cmd.ExecuteNonQuery(); return true; } catch { return false; } finally { DBClose(); } } /// <summary> /// 执行存储过程 /// </summary> /// <param name="proName">存储过程名称</param> /// <param name="paras">存储过程参数</param> /// <returns>是否执行成功</returns> public bool ExcuteProcedure(string proName, SqlParameter[] paras) { DBOpen(); cmd = new SqlCommand(proName, conn); cmd.CommandType = CommandType.StoredProcedure; for (int i = 0; i < paras.Length; i++) { cmd.Parameters.Add(paras[i]); } try { cmd.ExecuteNonQuery(); return true; } catch { return false; } finally { DBClose(); } } /// <summary> /// 执行存储过程获得数据集 /// </summary> /// <param name="proName">存储过程名</param> /// <param name="paras">存储过程参数</param> /// <returns>DataTable数据集</returns> public DataTable GetDataTableByProcedure(string proName, SqlParameter[] paras) { DBOpen(); cmd = new SqlCommand(proName, conn); cmd.CommandType = CommandType.StoredProcedure; dap = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); for (int i = 0; i < paras.Length; i++) { cmd.Parameters.Add(paras[i]); } try { dap.Fill(dt); return dt; } catch { return null; } finally { DBClose(); } } /// <summary> /// /// </summary> /// <param name="sql"></param> /// <returns></returns> public DataSet GetDataSetBySql(string sql) { DBOpen(); dap = new SqlDataAdapter(sql, conn); try { builder = new SqlCommandBuilder(dap); return ds; } catch { return null; } finally { DBClose(); } } }