操作MS SQL Server 存储过程的类(外加ASP.NET MessageBox类)

using System;
using System.Data.SqlClient;
using System.Collections;
using System.Data;
namespace XNet
{

    /**//**//**//// <summary>
    /// 自定义信息对话框
    /// </summary>
    public class MessageBox
    {
        /**//**//**//// <summary>
        /// 定义一个web页面，用来显示用户自定错误提示信息
        /// </summary>
        System.Web.UI.Page p;
        /**//**//**//// <summary>
        /// 实例时，参数为:this 如：MessageBox MB=new MessageBox(this);
        /// </summary>
        /// <param name="Page">此参数为：this</param>
        public  MessageBox(System.Web.UI.Page Page)
        {
            p=Page; //创建页面
        }
        /**//**//**//// <summary>
        /// 显示对话框
        /// </summary>
        /// <param name="message">提示信息</param>
        public void Show(string message)
        {
            string  script="<script> alert('"+ message +"')</script>";
            p.Response.Write(script);
        }
    }
    /**//**//**//// <summary>
    /// 该类简化了.net 中对Ms Sql Server 中存储过程的调用
    /// </summary>
    public class SqlOperation
    {
        private SqlCommand mySqlCommand;//执行数据语句
        private SqlConnection Conn;//数据库连结
        //        private string connString;//数据连结字符串
        private ArrayList  ParaValues= new ArrayList();//调用存储时， 按参数顺序赋值即：顺序方式。例如：Account('01',1000);
        private SortedList NameValues= new SortedList();//调用存储时，使用参数的名称赋值即：参数列表方式。例如：Account(@account_id='01',@account_despiste=1000)
        private SortedList outValue = new SortedList();//暂时保存存储过程输出参数的返回值列表
        private Boolean bol_ParaSerial;//叛断调用存储时，使用参数列表方式(useNoName=true)还是顺序方式(useNoName=false)。
        private Boolean bol_haveOutValue;//判断是否有返回值
        private DataSet DbSet = new DataSet();//存储过程返回的数据集
        private string myProName;//存储过程的名称
        /**//**//**//// <summary>
        /// 实例化类时提供数据连结字符串
        /// </summary>
        /// <param name="connString">数据连结字符串</param>
        public SqlOperation(string connString)
        {
            Conn= new SqlConnection(connString);            
        }
        /**//**//**//// <summary>
        /// 实例化类时不提供数据连结字符串
        /// </summary>
        public SqlOperation()
        {
            Conn= new SqlConnection(Conn=System.Configuration.ConfigurationSettings.AppSettings["ConStr"]) ;//必要时对连接字符串进行加密;
        }
        
        /**//**//**//// <summary>
        /// 获取一个Boolean值,叛断所执行的存储过程是否有返回值
        /// </summary>
        public Boolean bol_haveOutValue
        {
            get
            {
                return bol_haveOutValue;
            }
        }
        /**//**//**//// <summary>
        /// 在不知道存储过程参数的名称,但是知道参数的顺序可以使用此方法添加参数的值，必须按参数的顺序添加.
        /// 用多字符串形式添加所执行的存储过程的参数的值
        /// </summary>
        /// <param name="values">参数的值.添加方法:AddValueByStrings("a","b","c")</param>
        public void AddValueByStrings(params object[] values)
        {
            //            ParaValues.AddRange(values);
            for(int i=0;i<= values.Length-1;i++)
            {
                ParaValues.Add(values[i].ToString());
            }
            this.bol_ParaSerial=true;
        }        
        /**//**//**//// <summary>
        /// 在不知道存储过程参数的名称,但是知道参数的顺序可以使用此方法添加参数的值，必须按参数的顺序添加 
        /// 不可与 "AddValueByName" 共用,可与 "InsertValueByIndex"、"AddValueByString" 共用
        /// </summary>
        /// <param name="values">存储过程的值</param>
        public void AddValueByIndex(object values)
        {
            ParaValues.Add(values);
            this.bol_ParaSerial=true;
        }
        /**//**//**//// <summary>
        /// 在知道存储过程名称的情况下,可以通过此方法来添加所运行的存储过程的参数值.
        /// </summary>
        /// <param name="name">存储过程的名称,格式:"@name"必须以"@"开头</param>
        /// <param name="values">存储过程的值</param>
        public void AddValueByName(string name,object values)
        {
            this.NameValues.Add(name,values);
            this.bol_ParaSerial= false;
        }
        /**//**//**//// <summary>
        /// 在不知道存储过程参数的名称,但是知道参数的顺序可以使用此方法添加参数的值，必须按参数的顺序添加 
        /// 添加存储过程参数值到指的索引处,不可与 "AddValueByName" 共用,可与 "AddValueByIndex"、"AddValueByString" 共用
        /// </summary>
        /// <param name="index">在添加参数值的索引位置</param>
        /// <param name="values">要添加的参数值</param>
        public void InsertValueByIndex(int index,object values)
        {
            this.ParaValues.Insert(index,values);
            this.bol_ParaSerial = false;
        }
        /**//**//**//// <summary>
        /// 清除传递给存储过程参数的值
        /// </summary>
        public void ClearValue()
        {
            this.ParaValues.Clear();
            this.NameValues.Clear();
            this.DbSet.Clear();
        }
        /**//**//**//// <summary>
        /// 获取已经赋值的参数的个数
        /// </summary>
        public int ValueCount
        {
            get
            {
                return this.bol_ParaSerial== true? this.ParaValues.Count:this.NameValues.Count;
            }
        }
        /**//**//**//// <summary>
        /// 获取或设置当前要运行的存储过程的名称
        /// </summary>
        public string ProcedureName
        {
            get
            {
                return myProName;
            }
            set
            {
                myProName=value;
            }
        }
        /**//**//**//// <summary>
        /// 通过索引得到返回参数值的名称
        /// </summary>
        /// <param name="index">参数索引</param>
        /// <returns>返回 string 类型</returns>
        public string GetOutValuesNameByIndex(int index)
        {    
            return Convert.ToString(this.outValue.GetKey(index));
        }
        /**//**//**//// <summary>
        /// 通过索引得到返回参数的值
        /// </summary>
        /// <param name="index">参数索引</param>
        /// <returns>返回 object 类型</returns>
        public object GetOutValuesValueByIndex(int index)
        {
            return this.outValue.GetByIndex(index);
        }
        /**//**//**//// <summary>
        ///  通过参数的名称得到返回参数的值
        /// </summary>
        /// <param name="name">参数名称</param>
        /// <returns>返回 object 类型</returns>
        public object GetOutValuesValueByName(string name)
        {
            //            return this.outValue.GetByIndex(this.outValue.IndexOfKey(name));
            return this.outValue[name];
        }
        /**//**//**//// <summary>
        /// 获取储存过程返回值的数量
        /// </summary>
        public int OutValueCount
        {
            get
            {
                return this.outValue.Count;
            
            }
        
        }

        /**//**//**//// <summary>
        /// 运行存储过程
        /// </summary>
        /// <param name="proName"></param>
        private void goProc(string proName )
        {
            string selectProcInfo;
            selectProcInfo="select specific_name as ProcName,parameter_Name as ParaName,Data_Type as TypeName,ORDINAL_POSITION as Colid,parameter_Mode as Isoutparam from INFORMATION_SCHEMA.PARAMETERS where SPECIFIC_NAME='" + proName + "' order by ORDINAL_POSITION";
            this.mySqlCommand= new SqlCommand(selectProcInfo,this.Conn);
            SqlDataAdapter myDP = new SqlDataAdapter(this.mySqlCommand);
            myDP.Fill(this.DbSet,"ProcInfo");//等到当前存储过程的信息:参数,参数的顺序, 参数的类型,参数的传递
            this.mySqlCommand.CommandText= proName;
            this.mySqlCommand.CommandType= CommandType.StoredProcedure;
            if (this.DbSet.Tables["ProcInfo"].Rows.Count>0)//(this.NameValues.Count>0||this.ParaValues.Count>0)&&
            {
                for(int i=0;i<=this.DbSet.Tables["ProcInfo"].Rows.Count-1;i++)
                {
                    SqlDbType sqlDbType;
                    sqlDbType=SqlDbType.Int;
                    switch (Convert.ToString(this.DbSet.Tables["ProcInfo"].Rows[i]["TypeName"]))
                    {
                        case "image":
                        {
                            sqlDbType = SqlDbType.Image;
                        }
                            break;
                        case "text":
                        {
                            sqlDbType = SqlDbType.Text;
                        }
                            break;
                        case "uniqueidentifier":
                        {
                            sqlDbType = SqlDbType.UniqueIdentifier;
                        }
                            break;
                        case "tinyint":
                        {
                            sqlDbType = SqlDbType.TinyInt;
                        }
                            break;
                        case "smallint":
                        {
                            sqlDbType = SqlDbType.SmallInt;
                        }
                            break;
                        case "int":
                        {
                            sqlDbType = SqlDbType.Int;
                        }
                            break;
                        case "smalldatetime":
                        {
                            sqlDbType = SqlDbType.SmallDateTime;
                        }
                            break;
                        case "real":
                        {
                            sqlDbType= SqlDbType.Real;
                        }
                            break;
                        case "money":
                        {
                            sqlDbType = SqlDbType.Money;
                        }
                            break;
                        case "datetime":
                        {
                            sqlDbType = SqlDbType.DateTime;
                        }
                            break;                                    
                        case "float":
                        {
                            sqlDbType = SqlDbType.Float;
                        }
                            break;
                        case "ntext":
                        {
                            sqlDbType = SqlDbType.NText;
                        }
                            break;
                        case "bit":
                        {
                            sqlDbType = SqlDbType.Bit;
                        }
                            break;
                        case "decimal":
                        {
                            sqlDbType = SqlDbType.Decimal;
                        }
                            break;
                        case "smallmoney":
                        {
                            sqlDbType = SqlDbType.SmallMoney;
                        }
                            break;
                        case "BigInt":
                        {
                            sqlDbType = SqlDbType.BigInt;
                        }
                            break;
                        case "varbinary":
                        {
                            sqlDbType = SqlDbType.VarBinary;
                        }
                            break;
                        case "varchar":
                        {
                            sqlDbType = SqlDbType.VarChar;
                        }
                            break;
                        case "binary":
                        {
                            sqlDbType = SqlDbType.Binary;
                        }
                            break;
                        case "char":
                        {
                            sqlDbType = SqlDbType.Char;
                        }
                            break;
                        case "timestamp":
                        {
                            sqlDbType = SqlDbType.Timestamp;
                        }
                            break;
                        case "nvarchar":
                        {
                            sqlDbType = SqlDbType.NVarChar;
                        }
                            break;
                        case "nchar":
                        {
                            sqlDbType = SqlDbType.NChar;
                        }
                            break;
                        default:
                            break;
                    }

                    string parameterName= Convert.ToString(this.DbSet.Tables["ProcInfo"].Rows[i]["ParaName"]);
                    SqlParameter P = new SqlParameter(parameterName,sqlDbType);
                    if(Convert.ToString(this.DbSet.Tables["ProcInfo"].Rows[i]["Isoutparam"])=="INOUT")
                    {
                        P.Direction = ParameterDirection.Output;
                        this.bol_haveOutValue = true;
                    }
                    else
                    {
                        if (this.bol_ParaSerial == true)
                        {
                            P.Value = (sqlDbType==SqlDbType.Bit?Convert.ToByte(this.ParaValues[i]):this.ParaValues[i]);
                        }
                        else
                        {
                            P.Value = (sqlDbType==SqlDbType.Bit?Convert.ToByte(this.NameValues[parameterName]):this.NameValues[parameterName]);
                        
                        }
                    }
                    this.mySqlCommand.Parameters.Add(P);
                }
                
            }
            this.myProName= proName;
        }

        private void OutoutValue()
        {
            if (this.bol_haveOutValue== true)
            {
                for(int i=0;i<=this.DbSet.Tables["ProcInfo"].Rows.Count-1;i++)
                {
                    if (Convert.ToString(this.DbSet.Tables["ProcInfo"].Rows[i]["Isoutparam"])=="INOUT")
                    {
                        this.outValue.Add(Convert.ToString(this.DbSet.Tables["ProcInfo"].Rows[i]["ParaName"]),this.mySqlCommand.Parameters[Convert.ToString(this.DbSet.Tables["ProcInfo"].Rows[i]["ParaName"])].Value);
                    }
                }
            }
            this.ClearValue();
        }
        /**//**//**//// <summary>
        /// 通过存储过程的名称执行存储过程,返回 DataSet。
        /// </summary>
        /// <param name="procedureName">存储过程的名称</param>
        /// <returns>返回类型 DataSet </returns>
        public DataSet ProcReturnDataSet(string procedureName)
        {
            this.goProc(procedureName);
            DataSet DBS= new DataSet();
            SqlDataAdapter ap = new SqlDataAdapter(this.mySqlCommand);
            ap.Fill(DBS);
            this.OutoutValue();
            return DBS;
        }        
        /**//**//**//// <summary>
        /// 通过存储过程的名称执行存储过程,返回 DataTable。
        /// </summary>
        /// <param name="procedureName">存储过程的名称</param>
        /// <param name="tableName">指定所返回的DataTable的TableName</param>
        /// <returns>返回类型 DataTable </returns>
        public DataTable ProcReturnDataTable(string procedureName,string tableName)
        {
            this.goProc(procedureName);
            DataTable dataTable= new DataTable();
            dataTable.TableName=tableName;
            SqlDataAdapter ap = new SqlDataAdapter(this.mySqlCommand);
            ap.Fill(dataTable);
            this.OutoutValue();
            return dataTable;
        }
        /**//**//**//// <summary>
        /// 通过存储过程的名称执行存储过程,返回 DataTable。
        /// </summary>
        /// <param name="procedureName">存储过程的名称</param>
        /// <returns>返回类型 DataTable </returns>
        public DataTable ProcReturnDataTable(string procedureName)
        {
            this.goProc(procedureName);
            DataTable dataTable= new DataTable();
            SqlDataAdapter ap = new SqlDataAdapter(this.mySqlCommand);
            ap.Fill(dataTable);
            this.OutoutValue();
            return dataTable;
        }
        /**//**//**//// <summary>
        /// 执行指定的存储过程。无返回类型。
        /// </summary>
        /// <param name="procedureName">存储过程名称</param>
        public void RunProcedure(string procedureName)
        {
            this.goProc(procedureName);
            this.Conn.Open();
            this.mySqlCommand.ExecuteNonQuery();
            this.OutoutValue();
            this.Conn.Close();
        }
                /**//**//**//// <summary>
        /// 执行SQL语句。返回 DataSet
        /// </summary>
        /// <param name="sql">所要执行的SQL语句</param>
        /// <returns>DataSet</returns>
        public DataSet RunSqlReturnDataSet(string sql,string tableName)
        {
            SqlCommand sqlCommand= new SqlCommand(sql,this.Conn);
            DataSet retrunDataSet = new DataSet();
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
            sqlDataAdapter.Fill(retrunDataSet,tableName);
            return retrunDataSet;
        }
        /**//**//**//// <summary>
        /// 执行SQL语句。返回 DataTable
        /// </summary>
        /// <param name="sql">所要执行的SQL语句</param>
        /// <param name="tableName">返回tableName的名称</param>
        /// <returns>DataTable</returns>
        public DataTable RunSqlReturnDataTable(string sql,string tableName)
        {
            SqlCommand sqlCommand= new SqlCommand(sql,this.Conn);
            DataTable retrunDataTable = new DataTable();
            retrunDataTable.TableName=tableName;
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
            sqlDataAdapter.Fill(retrunDataTable);
            return retrunDataTable;
        }
        /**//**//**//// <summary>
        /// 执行SQL语句。返回Boolean类型
        /// </summary>
        /// <param name="sql">所要执行的SQL语句</param>
        /// <returns>Boolean</returns>
        public Boolean  RunSql(string sql)
        {
            try
            {
                SqlCommand sqlCommand= new SqlCommand(sql,this.Conn);
                this.Conn.Open();
                sqlCommand.ExecuteNonQuery();
                this.Conn.Close();
                return true;
            }
            catch
            {
                return false;
            }
            
        }
    }    
}