ASP.NET DataBase
//数据库操作 public class DataBase { private SqlConnection conn;//数据库连接对象 #region 打开数据库连接 private void Open() { if (conn == null) { conn = new SqlConnection(); //conn.ConnectionString = ConfigurationSettings.AppSettings["connString"].ToString(); conn.ConnectionString = ConfigurationManager.ConnectionStrings["connString"].ToString(); conn.Open(); } else { if (conn.State == System.Data.ConnectionState.Closed) { conn.Open(); } } } #endregion #region 关闭数据库连接 private void Close() { if (conn != null && conn.State == System.Data.ConnectionState.Open) { conn.Close(); } } #endregion #region 释放连接资源 public void Dispose() { if (conn != null) { conn.Dispose(); conn = null; } } #endregion #region 传入参数并转换为SqlParameter类型 /// <summary> /// 转换参数 /// </summary> /// <param name="ParamName">存储过程名称或命令文本</param> /// <param name="DbType">参数类型</param></param> /// <param name="Size">参数大小</param> /// <param name="Value">参数值</param> /// <returns>新的 parameter 对象</returns> public SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, Object Value) { return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value); } /// <summary> /// 初始化参数值 /// </summary> /// <param name="ParamName">存储过程名称或命令文本</param> /// <param name="DbType">参数类型</param> /// <param name="Size">参数大小</param> /// <param name="Direction">参数方向</param> /// <param name="Value">参数值</param> /// <returns>新的 parameter 对象</returns> private SqlParameter MakeParam(string ParamName, SqlDbType DbType, int Size, ParameterDirection Direction, object Value) { SqlParameter param; if (Size>0) { param = new SqlParameter(ParamName, DbType, Size); } else { param = new SqlParameter(ParamName, DbType); } param.Direction = Direction; if (!(param.Direction == ParameterDirection.Output && Value == null)) { param.Value = Value; } return param; } #endregion #region 执行参数命令文本(无数据库中数据返回) /// <summary> /// 执行命令 /// </summary> /// <param name="procName">命令文本</param> /// <param name="prams">参数对象</param> /// <returns></returns> public int RunProc(string procName, SqlParameter[] prams) { SqlCommand cmd = CreateCommand(procName, prams); cmd.ExecuteNonQuery(); this.Close(); //得到成功执行的返回值 return (int)cmd.Parameters["ReturnValue"].Value; } /// <summary> /// 直接执行SQL语句 /// </summary> /// <param name="procName">命令文本</param> /// <returns></returns> public int RunProc(string procName) { this.Open(); SqlCommand cmd = new SqlCommand(procName, conn); int num = cmd.ExecuteNonQuery(); this.Close(); return num; } #endregion #region 执行参数命令文本(有返回值) /// <summary> /// 执行查询命令文本,并且返回DataSet数据集 /// </summary> /// <param name="procName">命令文本</param> /// <param name="prams">参数对象</param> /// <param name="tbName">数据表名称</param> /// <returns></returns> public DataSet RunProcReturn(string procName, SqlParameter[] prams, string tbName) { SqlDataAdapter dap = CreateDataAdapter(procName, prams); DataSet ds = new DataSet(); dap.Fill(ds, tbName); this.Close(); return ds; } /// <summary> /// 执行命令文本,并且返回DataSet数据集 /// </summary> /// <param name="procName">命令文本</param> /// <param name="tbName">数据表名称</param> /// <returns>DataSet</returns> public DataSet RunProcReturn(string procName, string tbName) { SqlDataAdapter dap = CreateDataAdapter(procName, null); DataSet ds = new DataSet(); dap.Fill(ds, tbName); return ds; } #endregion #region 将命令文本添加到SqlDataAdapter /// <summary> /// 创建一个SqlDataAdapter对象以此来执行命令文本 /// </summary> /// <param name="procName">命令文本</param> /// <param name="prams">参数对象</param> /// <returns></returns> private SqlDataAdapter CreateDataAdapter(string procName, SqlParameter[] prams) { this.Open(); SqlDataAdapter dap = new SqlDataAdapter(procName, conn); dap.SelectCommand.CommandType = CommandType.Text;// 执行类型:命令文本 if (prams != null) { foreach (SqlParameter param in prams) { dap.SelectCommand.Parameters.Add(param);// 为查询语句插入参数 } } //加入返回值 dap.SelectCommand.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, String.Empty, DataRowVersion.Default, null)); return dap; } #endregion #region 将命令文本添加到SqlCommand /// <summary> /// 创建一个SqlCommand对象以此来执行命令文本 /// </summary> /// <param name="procName">命令文本</param> /// <param name="prams"命令文本所需参数</param> /// <returns>返回SqlCommand对象</returns> private SqlCommand CreateCommand(string procName, SqlParameter[] prams) { this.Open(); SqlCommand cmd = new SqlCommand(procName, conn); cmd.CommandType = CommandType.Text;// 执行类型:命令文本 //一次把参数传入命令文本 if (prams != null) { foreach (SqlParameter param in prams) { cmd.Parameters.Add(param); } } //加入返回参数 cmd.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null)); return cmd; } #endregion }