.net数据库操作类2

 

 using System;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using OSLeagueForumXP.Components;

namespace OSLeagueForumXP.Common
{
    /// <summary>
    /// 数据库操作通用类
    /// </summary>
    public class DataBase : IDisposable
    {
        /// <summary>
        /// 数据库连接源
        /// </summary>
        private SqlConnection SqlConn;
       
        /// <summary>
        /// 运行SQL Server储存过程
        /// </summary>
        /// <param name="ProcedureName">储存过程名称</param>
        /// <returns>储存过程返回的值</returns>
        public int RunProcedure(string ProcedureName)
        {
            SqlCommand Sqlcmd = CreateCommand(ProcedureName, null);
            Sqlcmd.ExecuteNonQuery();
            this.Close();
            return (int)Sqlcmd.Parameters["ReturnValue"].Value;
        }
       
        /// <summary>
        /// 运行SQL Server储存过程
        /// </summary>
        /// <param name="ProcedureName">储存过程名称</param>
        /// <param name="SqlPrams">储存过程参数</param>
        /// <returns>储存过程返回值</returns>
        public int RunProcedure(string ProcedureName, SqlParameter[] SqlPrams)
        {
            SqlCommand Sqlcmd = CreateCommand(ProcedureName, SqlPrams);
            Sqlcmd.ExecuteNonQuery();
            this.Close();
            return (int)Sqlcmd.Parameters["ReturnValue"].Value;
        }
       
        /// <summary>
        /// 运行SQL Server储存过程
        /// </summary>
        /// <param name="ProcedureName">储存过程名称</param>
        /// <param name="SqlReader">以SqlDataReader返回储存过程结果</param>
        public void RunProcedure(string ProcedureName, out SqlDataReader SqlReader)
        {
            SqlCommand Sqlcmd = CreateCommand(ProcedureName, null);
            SqlReader = Sqlcmd.ExecuteReader(CommandBehavior.CloseConnection);
        }
       
        /// <summary>
        /// 运行SQL Server储存过程
        /// </summary>
        /// <param name="ProcedureName">储存过程名称</param>
        /// <param name="SqlPrams">储存过程参数</param>
        /// <param name="SqlReader">以SqlDataReader返回储存过程结果</param>
        public void RunProcedure(string ProcedureName, SqlParameter[] SqlPrams, out SqlDataReader SqlReader)
        {
            SqlCommand Sqlcmd = CreateCommand(ProcedureName, SqlPrams);
            SqlReader = Sqlcmd.ExecuteReader(CommandBehavior.CloseConnection);
        }
       
        /// <summary>
        /// 运行Sql Server储存过程
        /// </summary>
        /// <param name="ProcedureName">储存过程名称</param>
        /// <param name="Table">数据表</param>
        /// <returns>DataSet对象</returns>
        public DataSet RunProcedure(string ProcedureName, string Table)
        {
            DataSet SqlDS = new DataSet();
            SqlDataAdapter SqlDA = CreateDataAdapter(ProcedureName,null);
            SqlDA.Fill(SqlDS,Table);
            return(SqlDS);
        }
       
        /// <summary>
        /// 运行SQL Server储存过程
        /// </summary>
        /// <param name="ProcedureName">储存过程名称</param>
        /// <param name="SqlPrams">Sql参数</param>
        /// <param name="Table">数据表</param>
        /// <returns>DataSet对象</returns>
        public DataSet RunProcedure(string ProcedureName, SqlParameter[] SqlPrams,string Table)
        {
            DataSet SqlDS = new DataSet();
            SqlDataAdapter SqlDA = CreateDataAdapter(ProcedureName,SqlPrams);
            SqlDA.Fill(SqlDS,Table);
            return(SqlDS);
        }
       
        /// <summary>
        /// 运行Sql Server储存过程
        /// </summary>
        /// <param name="ProcedureName">储存过程名称</param>
        /// <param name="Table">数据表</param>
        /// <param name="StartRecord">开始记录</param>
        /// <param name="MaxRecord">最大记录数</param>
        /// <returns>DataSet对象</returns>
        public DataSet RunProcedure(string ProcedureName, string Table,int StartRecord,int MaxRecord)
        {
            DataSet SqlDS = new DataSet();
            SqlDataAdapter SqlDA = CreateDataAdapter(ProcedureName,null);
            SqlDA.Fill(SqlDS,StartRecord,MaxRecord,Table);
            return(SqlDS);
        }
       
        /// <summary>
        /// 运行SQL Server储存过程
        /// </summary>
        /// <param name="ProcedureName">储存过程名称</param>
        /// <param name="SqlPrams">Sql参数</param>
        /// <param name="Table">数据表</param>
        /// <param name="StartRecord">开始记录</param>
        /// <param name="MaxRecord">最大记录数</param>
        /// <returns>DataSet对象</returns>
        public DataSet RunProcedure(string ProcedureName, SqlParameter[] SqlPrams,string Table,int StartRecord,int MaxRecord)
        {
            DataSet SqlDS = new DataSet();
            SqlDataAdapter SqlDA = CreateDataAdapter(ProcedureName,SqlPrams);
            SqlDA.Fill(SqlDS,StartRecord,MaxRecord,Table);
            return(SqlDS);
        }

        /// <summary>
        /// 创建SqlDataAdapter对象
        /// </summary>
        /// <param name="ProcedureName">储存过程</param>
        /// <param name="SqlPrams">Sql参数</param>
        /// <param name="Type">类型</param>
        /// <returns>SqlDataAdapter对象</returns>
        private SqlDataAdapter CreateDataAdapter(string ProcedureName, SqlParameter[] SqlPrams)
        {
            //打开数据库连接
            Open();
           
            SqlDataAdapter SqlDA = new SqlDataAdapter(ProcedureName, SqlConn);
           
            SqlDA.SelectCommand.CommandType = CommandType.StoredProcedure;
            // 添加储存过程参数
            if (SqlPrams != null)
            {
                foreach (SqlParameter Sqlparameter in SqlPrams)
                {
                    SqlDA.SelectCommand.Parameters.Add(Sqlparameter);
                }
            }
           
            // 返回参数
            SqlDA.SelectCommand.Parameters.Add(
                new SqlParameter("ReturnValue", SqlDbType.Int, 4,
                ParameterDirection.ReturnValue, false, 0, 0,
                string.Empty, DataRowVersion.Default, null));

            return SqlDA;
        }

        /// <summary>
        /// 创建一个SqlCommand对象来调用储存过程
        /// </summary>
        /// <param name="ProcedureName">储存过程名称</param>
        /// <param name="SqlPrams">储存过程参数</param>
        /// <returns>SqlCommand对象</returns>
        private SqlCommand CreateCommand(string ProcedureName, SqlParameter[] SqlPrams)
        {
            //打开数据库连接
            Open();
           
            SqlCommand Sqlcmd = new SqlCommand(ProcedureName, SqlConn);
            Sqlcmd.CommandType = CommandType.StoredProcedure;

            // 添加储存过程参数
            if (SqlPrams != null)
            {
                foreach (SqlParameter Sqlparameter in SqlPrams)
                {
                    Sqlcmd.Parameters.Add(Sqlparameter);
                }
            }
           
            // 返回参数
            Sqlcmd.Parameters.Add(
                new SqlParameter("ReturnValue", SqlDbType.Int, 4,
                ParameterDirection.ReturnValue, false, 0, 0,
                string.Empty, DataRowVersion.Default, null));

            return Sqlcmd;
        }
       
        /// <summary>
        /// 打开数据库连接
        /// </summary>
        public void Open()
        {
            INI clsini = new INI();
            string strGetKey = clsini.Read("Common","SqlConn","",HttpContext.Current.Server.MapPath("../config.aspx"));
            // 如果没有打开数据库，则打开数据库连接
            SqlConn = new SqlConnection(strGetKey);
            SqlConn.Open();
        }
       
        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        public void Close()
        {
            if (SqlConn != null)
                SqlConn.Close();
        }
       
        /// <summary>
        /// 释放资源
        /// </summary>
        public void Dispose()
        {
            // 确定以关闭数据库连接
            if (SqlConn != null)
            {
                SqlConn.Dispose();
                SqlConn = null;
            }               
        }
       
        /// <summary>
        /// 创建输入参数
        /// </summary>
        /// <param name="ParameterName">参数名称</param>
        /// <param name="DbType">参数类型</param>
        /// <param name="Size">参数大小</param>
        /// <param name="Value">参数值</param>
        /// <returns>新的参数</returns>
        public SqlParameter MakeInputParameter(string ParameterName, SqlDbType DbType, int Size, object Value)
        {
            return MakeParameter(ParameterName, DbType, Size, ParameterDirection.Input, Value);
        }
       
        /// <summary>
        /// 创建输出参数
        /// </summary>
        /// <param name="ParameterName">参数名称</param>
        /// <param name="DbType">参数类型</param>
        /// <param name="Size">参数大小</param>
        /// <returns>新的参数</returns>
        public SqlParameter MakeOutParameter(string ParameterName, SqlDbType DbType, int Size)
        {
            return MakeParameter(ParameterName, DbType, Size, ParameterDirection.Output, null);
        }
       
        /// <summary>
        /// 创建储存过程参数
        /// </summary>
        /// <param name="ParameterName">参数名称</param>
        /// <param name="DbType">参数类型</param>
        /// <param name="Size">参数大小</param>
        /// <param name="Direction">参数方法</param>
        /// <param name="Value">参数值</param>
        /// <returns>新的参数</returns>
        public SqlParameter MakeParameter(string ParameterName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value)
        {
            SqlParameter SqlParam;
           
            if(Size > 0)
            {
                SqlParam = new SqlParameter(ParameterName, DbType, Size);
            }
            else
            {
                SqlParam = new SqlParameter(ParameterName, DbType);
            }
           
            SqlParam.Direction = Direction;
            if (!(Direction == ParameterDirection.Output && Value == null))
            {
                SqlParam.Value = Value;
            }
           
            return SqlParam;
        }
    }
}