command//---------------------------------------------------
//日期： 2002.1.10
//作者： raxzhang
//版权： raxzhang
//环境： Microsoft Visual Studio.Net 7.0
//语言： Visual C#
//类型： 类文件，编译后为.dll文件
//描述： 这是作为对数据操作的最常用的属性和方法类。
//       是一个基础类。可以被继承和扩展。
//注意： 使用这个类的条件是-1.dbo用户。2.存储过程的
//       参数名与表的字段名相差一个
//---------------------------------------------------
using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;   

namespace zyq.DBMapping
{
    /// <summary>
    /// 对SQL server进行操作
    /// </summary>
    public class DataAccessSQL
    {
        #region class variables
        private String connectionString;
        private int _tablecount=-1;
        private int _stroeprocedurecount=-1;
        private SqlConnection conn=null;
        #endregion
        #region properties  of class
        /// <summary>
        /// 属性:数据库存储过程的个数(stat>0)
        /// </summary>
        public int StroeProcedureCount
        {
            get
            {
                if (this._stroeprocedurecount !=-1)
                {
                    return this._stroeprocedurecount;
                }
                else
                {
                    return this.GetStroeProcedures().Count;
                }
            }
        }
        /// <summary>
        /// 属性:数据库用户表的个数
        /// </summary>
        public int TablesCount
        {
            get
            {
                if(this._tablecount !=-1)
                {
                    return this._tablecolscount;
                }
                else
                {
                    return this.GetTables().Count;
                }
             }

        }
        #endregion
        #region structure of class
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="ConnectionString">数据库连接字符串，string</param>
        public DataAccessSQL(string ConnectionString)
        {
            this.connectionString=ConnectionString;
            this.conn =new SqlConnection(this.connectionString);
        }
        #endregion
        #region Methods of class  
        /// <summary>
        /// 获得数据库的所有表对象
        /// </summary>
        /// <returns>System.Data.SqlClient.SqlDataReader</returns>
        public Hashtable GetTables()
        {
            try
            {
                Hashtable sptable=new Hashtable();
                //验证连接
                if(conn!=null && conn.State!=ConnectionState.Open)
                {
                    conn.Open();
                }
                else
                {
                    conn= new SqlConnection(this.connectionString);
                    conn.Open();
                }                                            
                string Query = " select name, Id from sysobjects  where (type='U') and (name <> 'dtproperties') order by name ";
                //获得指定数据库中的所有用户表的名称和ID
                SqlCommand comm= new SqlCommand(Query,conn);            
                SqlDataReader reader=comm.ExecuteReader(CommandBehavior.CloseConnection);
                //录制Hashtable
                while(reader.Read())
                {
                    sptable.Add(reader.GetInt32(1),reader.GetString(0));
                }
                this._tablecount =sptable.Count;
                return sptable;
            }
            catch(SqlException se)
            {
                throw(se);
            }
        }
        /// <summary>
        /// 获得数据库的存储过程的名称及ID列表
        /// </summary>
        /// <returns>HasTable</returns>
        public Hashtable GetStroeProcedures()
        {            
            try
            {    
                //验证连接            
                if(conn!=null && conn.State!=ConnectionState.Closed)
                {
                    conn.Open();
                }
                else
                {
                    conn= new SqlConnection(this.connectionString);
                    conn.Open();
                }
                Hashtable sptable=new Hashtable();
                string Query = " SELECT name, id FROM sysobjects WHERE (type = 'p') AND (status > 0) ";
                //获得指定数据库中的所有用户存储过程的名称和ID
                SqlCommand comm= new SqlCommand(Query,conn);
            
                SqlDataReader reader=comm.ExecuteReader(CommandBehavior.CloseConnection);
                //录制Hashtable
                while(reader.Read())
                {
                    sptable.Add(reader.GetInt32(1),reader.GetString(0));
                }
                this._stroeprocedurecount =sptable.Count;
                return sptable;
            }
            catch(SqlException se)
            {
                throw(se);
            }
            catch(Exception e)
            {
                throw(e);
            }
            finally
            {
                if(conn.State==ConnectionState.Open )
                    conn.Close();
            }
        }
        /// <summary>
        ///获得数据库的指定表的列对象定义
        /// </summary>
        /// <param name="spname">表名称</param>
        /// <returns>DataSet</returns>
        public DataSet getTableColumns(string spname)
        {
            
            try
            {
                Int32 spid=-1; //指定表的ID号初始
                //验证连接
                if(conn!=null && conn.State!=ConnectionState.Closed)
                {
                    conn.Open();
                }
                else
                {
                    conn= new SqlConnection(this.connectionString);
                    conn.Open();
                }
                //获取指定表名的ID号
                SqlCommand comm= new SqlCommand("SELECT id FROM dbo.sysobjects WHERE name = '"+spname,conn);
                SqlDataReader reader=comm.ExecuteReader(CommandBehavior.CloseConnection);
                while(reader.Read())
                {
                    spid = reader.GetInt32(0);  
                }
                reader.Close();
                //验证ID
                if(spid==0 ||spid==-1)
                    throw new Exception ("StroedProcedure is not existed!");
                //获得表的列定义
                return getTableColumns(spid);
            }
            catch(SqlException se)
            {
                throw(se);
            }
            finally
            {
                if(conn.State ==ConnectionState.Open)
                    conn.Close();
            }

        }
        /// <summary>
        /// 获得数据库的指定表的列对象定义的列数组
        /// </summary>
        /// <param name="spid">表名称</param>
        /// <returns>DataSet</returns>
        public DataSet getTableColumns(Int32 spid)
        {            
            try
            {                
                DataSet myDataSet=new DataSet();
                //验证连接
                if(conn!=null && conn.State!=ConnectionState.Closed)
                {
                    conn.Open();
                }
                else
                {
                    conn= new SqlConnection(this.connectionString);
                    conn.Open();
                }
                SqlDataAdapter comm= new SqlDataAdapter("SELECT dbo.syscolumns.name, dbo.systypes.name AS Type, dbo.syscolumns.length,dbo.syscolumns.isoutparam, dbo.syscolumns.isnullable FROM dbo.syscolumns INNER JOIN dbo.systypes ON dbo.syscolumns.xtype = dbo.systypes.xtype WHERE dbo.syscolumns.id ='"+spid+"'",conn);
                //获取指定表的列定义
                comm.Fill(myDataSet,"dbo.systypes");
                comm.Fill(myDataSet,"dbo.systypes");
                /*this._tablecolscount=myDataSet.Tables[0].Rows.Count;
                DataColumn[] dcols=new DataColumn[5];
                dcols[0]=myDataSet.Tables["dbo.syscolumns"].Columns["name"];
                dcols[1]=myDataSet.Tables["dbo.systypes"].Columns["Type"];
                dcols[2]=myDataSet.Tables["dbo.syscolumns"].Columns["length"];
                dcols[3]=myDataSet.Tables["dbo.syscolumns"].Columns["isoutparam"];
                dcols[3]=myDataSet.Tables["dbo.syscolumns"].Columns["isnullable"];*/
                return myDataSet;
            }
            catch(SqlException se)
            {
                throw(se);
            }
            finally
            {
                if(conn.State ==ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }
        /// <summary>
        /// 为传入SqlCommand对象建立存储过程的参数数组
        /// </summary>
        /// <remarks >参数只付值ParameterName,SqlDbType,IsNullable,Direction</remarks>
        /// <param name="sqlcommand">SqlCommand</param>        
        /// <returns>SqlCommand</returns>
        public SqlCommand getStroeProcedureParamsByName(SqlCommand sqlcommand)//, string spname)
        {
            try
            {
                Int32 spid=-1; //初始化存储过程的ID
                //验证连接
                if(conn!=null && conn.State!=ConnectionState.Open)
                {
                    conn.Open();
                }
                else
                {
                    conn= new SqlConnection(this.connectionString);
                    conn.Open();
                }
                //获取存储过程的名称
                string spname=sqlcommand.CommandText;
                //获取存储过程的ID号
                SqlCommand comm= new SqlCommand("SELECT id FROM dbo.sysobjects WHERE name = '"+spname+"'",conn);
                SqlDataReader reader=comm.ExecuteReader(CommandBehavior.CloseConnection);
                while(reader.Read())
                {
                    spid = reader.GetInt32(0);  
                }
                //验证ID号
                if(spid==0 ||spid==-1)
                    throw new Exception ("StroedProcedure is not existed!");
                //创建参数数组
                return getStroeProcedureParamsByID( sqlcommand ,spid);
            }
            catch(SqlException se)
            {
                throw(se);
            }
            finally
            {
                if(conn.State ==ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }
        /// <summary>
        ///为传入SqlCommand对象建立存储过程的参数数组
        /// </summary>
        /// <remarks >参数只付值ParameterName,SqlDbType,IsNullable,Direction</remarks>
        /// <param name="sqlcommand">SqlCommand</param>
        /// <param name="spid">存储过程ID</param>
        /// <returns>SqlCommand</returns>
        public SqlCommand getStroeProcedureParamsByID(SqlCommand sqlcommand, Int32 spid)
        {                    
            try
            {
                //获取存储过程相关表的isnullable定义
                Hashtable dependtble=this.SPDependonTable(spid);
                DataSet myDataSet=new DataSet();
                //验证连接
                if(conn!=null && conn.State!=ConnectionState.Open)
                {
                    conn.Open();
                }
                else
                {
                    conn= new SqlConnection(this.connectionString);
                    conn.Open();
                }
                //获取指定存储过程的参数内容
                SqlDataAdapter comm= new SqlDataAdapter("SELECT dbo.syscolumns.name, dbo.systypes.name AS Type, dbo.syscolumns.length,dbo.syscolumns.isoutparam FROM dbo.syscolumns INNER JOIN dbo.systypes ON dbo.syscolumns.xtype = dbo.systypes.xtype WHERE dbo.syscolumns.id ="+spid,conn);//, dbo.syscolumns.isnullable                
                comm.Fill(myDataSet,"dbo.syscolumns");
                comm.Fill(myDataSet,"dbo.systypes");                
                int paramcount = myDataSet.Tables[0].Rows.Count;                
                for(int i=0;i<paramcount;i++)
                {
                    //参数名称
                    string pname=myDataSet.Tables["dbo.syscolumns"].Rows[i]["name"].ToString();//.ToString();
                    //参数的SqlDBType类型定义
                    SqlDbType ptp=this.getSqlDbType(myDataSet.Tables["dbo.systypes"].Rows[i]["Type"].ToString());
                    //参数的DBType类型定义
                    //DbType dtp=this.getDbType(myDataSet.Tables["dbo.systypes"].Rows[i]["Type"].ToString());
                    //参数的长度定义
                    int flength=Convert.ToInt32(myDataSet.Tables["dbo.syscolumns"].Rows[i]["length"]);
                    //创建一个参数
                    sqlcommand.Parameters.Add(pname,ptp,flength);
                    //定义参数可否为空值，由相关表的isnullable定义得到                    
                    sqlcommand.Parameters[pname].IsNullable =(Boolean)dependtble[pname];
                    //sqlcommand.Parameters[pname].DbType =dtp;    
                    //定义参数的INPUT和OUTPUT                
                    if((int)(myDataSet.Tables["dbo.syscolumns"].Rows[i]["isoutparam"])==1)
                    {
                        sqlcommand.Parameters[pname].Direction =ParameterDirection.Output;
                    }
                    else
                    {
                        sqlcommand.Parameters[pname].Direction =ParameterDirection.Input;
                    }
                }
                this._spparamcount=paramcount;
                return sqlcommand;
            }
            catch(SqlException se)
            {
                throw(se);
            }
            finally
            {
                if(conn.State ==ConnectionState.Open)
                {
                    conn.Close();
                }
                
            }
        }