//版权所有 osleague 2002-2003
//作者：blood
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;
        }
    }
}