SQL存储过程 数据访问类

 

//数据访问类 using System; using System.ComponentModel; using System.Collections; using System.Web.Security; using System.Diagnostics; using System.Data; using System.Data.SqlClient; using System.Configuration; using System.Text.RegularExpressions; namespace WebApp.Components { /// <summary> /// 通用的数据库处理类，通过ado.net与数据库连接 /// </summary> public class Database : IDisposable { // 连接数据源 private SqlConnection con; #region 执行不带参数的存储过程，返回存储过程返回值 /// <summary> /// 执行存储过程 /// </summary> /// <param name="procName">不带参数的存储过程的名称</param> /// <returns>返回存储过程返回值</returns> public int RunProc(string procName) { SqlCommand cmd = CreateCommand(procName, null); cmd.ExecuteNonQuery(); this.Close(); return (int)cmd.Parameters["ReturnValue"].Value; } #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; } #endregion #region 执行不带参数的存储过程，通过输出参数返回SqlDataReader对象 /// <summary> /// 执行存储过程 /// </summary> /// <param name="procName">不带参数的存储过程的名称</param> /// <param name="dataReader">通过输出参数返回SqlDataReader对象</param> public void RunProc(string procName, out SqlDataReader dataReader) { SqlCommand cmd = CreateCommand(procName, null); dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection); } #endregion #region 执行带参数的存储过程，通过输出参数返回SqlDataReader对象 /// <summary> /// 执行存储过程 /// </summary> /// <param name="procName">带参数的存储过程的名称</param> /// <param name="prams">存储过程所需参数</param> /// <param name="dataReader">通过输出参数返回SqlDataReader对象</param> public void RunProc(string procName, SqlParameter[] prams, out SqlDataReader dataReader) { SqlCommand cmd = CreateCommand(procName, prams); dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection); } #endregion #region 创建SqlCommand对象 /// <summary> /// 创建一个SqlCommand对象以此来执行存储过程 /// </summary> /// <param name="procName">存储过程的名称</param> /// <param name="prams">存储过程所需参数</param> /// <returns>返回SqlCommand对象</returns> private SqlCommand CreateCommand(string procName, SqlParameter[] prams) { // 确认打开连接 Open(); SqlCommand cmd = new SqlCommand(procName, con); cmd.CommandType = CommandType.StoredProcedure; // 依次把参数传入存储过程 if (prams != null) { foreach (SqlParameter parameter in prams) cmd.Parameters.Add(parameter); } // 加入返回参数 cmd.Parameters.Add( new SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null)); return cmd; } #endregion #region 打开数据库连接 /// <summary> /// 打开数据库连接. /// </summary> private void Open() { // 打开数据库连接 if (con == null) { con = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]); } if(con.State == System.Data.ConnectionState.Closed) con.Open(); } #endregion #region 关闭数据库连接 /// <summary> /// 关闭数据库连接 /// </summary> public void Close() { if (con != null) con.Close(); } #endregion #region 释放资源 /// <summary> /// 释放资源 /// </summary> public void Dispose() { // 确认连接是否已经关闭 if (con != null) { con.Dispose(); con = null; } } #endregion #region 传入输入参数 /// <summary> /// 传入输入参数 /// </summary> /// <param name="ParamName">存储过程名称</param> /// <param name="DbType">参数类型</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); } #endregion #region 传入输出参数 /// <summary> /// 传入输出参数 /// </summary> /// <param name="ParamName">存储过程名称</param> /// <param name="DbType">参数类型</param> /// <param name="Size">参数大小</param> /// <returns>新的 parameter 对象</returns> public SqlParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size) { return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, null); } #endregion #region 传入返回值参数 /// <summary> /// 传入返回值参数 /// </summary> /// <param name="ParamName">存储过程名称</param> /// <param name="DbType">参数类型</param> /// <param name="Size">参数大小</param> /// <returns>新的 parameter 对象</returns> public SqlParameter MakeReturnParam(string ParamName, SqlDbType DbType, int Size) { return MakeParam(ParamName, DbType, Size, ParameterDirection.ReturnValue, null); } #endregion #region 生成存储过程参数 /// <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> public SqlParameter MakeParam(string ParamName, SqlDbType DbType, Int32 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 (!(Direction == ParameterDirection.Output && Value == null)) param.Value = Value; return param; } #endregion #region 将DataReader 转为 DataTable /// <summary> /// 将DataReader 转为 DataTable /// </summary> /// <param name="DataReader">DataReader</param> public static DataTable ConvertDataReaderToDataTable(SqlDataReader reader) { try { DataTable objDataTable = new DataTable(); int intFieldCount = reader.FieldCount; for (int intCounter = 0; intCounter < intFieldCount; ++intCounter) { objDataTable.Columns.Add(reader.GetName(intCounter), reader.GetFieldType(intCounter)); } objDataTable.BeginLoadData(); object[] objValues = new object[intFieldCount]; while (reader.Read()) { reader.GetValues(objValues); objDataTable.LoadDataRow(objValues, true); } reader.Close(); objDataTable.EndLoadData(); return objDataTable; } catch(Exception ex) { throw new Exception("转换DataReader为DataTable出错!",ex); } } #endregion #region 数字判定 /// <summary> /// 数字判定 /// </summary> /// <param name="str">字符串参数</param> public static bool IsNumber(String strNumber) { Regex objNotNumberPattern = new Regex("[^0-9.-]"); Regex objTwoDotPattern = new Regex("[0-9]*[.][0-9]*[.][0-9]*"); Regex objTwoMinusPattern = new Regex("[0-9]*[-][0-9]*[-][0-9]*"); String strValidRealPattern = "^([-]|[.]|[-.]|[0-9])[0-9]*[.]*[0-9]+$"; String strValidIntegerPattern = "^([-]|[0-9])[0-9]*$"; Regex objNumberPattern = new Regex("(" + strValidRealPattern + ")|(" + strValidIntegerPattern + ")"); return !objNotNumberPattern.IsMatch(strNumber) && !objTwoDotPattern.IsMatch(strNumber) && !objTwoMinusPattern.IsMatch(strNumber) && objNumberPattern.IsMatch(strNumber); } #endregion } } ///使用 SqlDataReader dataReader = null; Database data = new Database(); SqlParameter[] prams = { data.MakeInParam("@LinkID",SqlDbType.Int, 4, LinkID), }; try { data.RunProc("存储过程", prams, out dataReader); return dataReader; } catch (Exception ex) { throw new Exception("友情链接读取出错!", ex); } //这些是关于数据访问的部分代码，关于存储过程，分三种：无返回查询，返回数据集，返回单值

转载于:https://www.cnblogs.com/zhangq723/archive/2010/04/08/1707225.html