数据访问类 SQL存储过程

来源：互联网发布：网络转hdmi 编辑：程序博客网时间：2024/05/14 20:37
//////////////数据访问类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);            }//这些是关于数据访问的部分代码，关于存储过程，分三种：无返回查询，返回数据集，返回单值