技术资料 > .Net专区 > Asp.Net开发 : 自动填充SqlCommand.Parameters的类（2）

自动填充SqlCommand.Parameters的类（2）

March 25,2004

/// <summary>
      /// 获取存储过程的相关表的列名和isnuable
      /// </summary>
      /// <param name="spid">存储过程名称</param>
      /// <returns>Hastable</returns>
      public Hashtable SPDependonTable(Int32 spid)
      {
         try
         {
            //验证连接
            if(conn!=null && conn.State!=ConnectionState.Open)
            {
                  conn.Open();
            }
            else
            {
                  conn= new SqlConnection(this.connectionString);
                  conn.Open();
            }
            string sqlstr="SELECT name, isnullable as allowNUll FROM dbo.syscolumns WHERE (id IN (SELECT DISTINCT id FROM dbo.sysobjects WHERE (id IN (SELECT DISTINCT dbo.sysdepends.depid FROM dbo.sysdepends WHERE (dbo.sysdepends.id ="+spid+")))))";
            //获取相关表的isnullable
            SqlDataAdapter comm= new SqlDataAdapter(sqlstr,conn );
            DataSet depds=new DataSet();
            comm.Fill(depds,"dbo.syscolumns");
            Hashtable dependtbl=new Hashtable();
            //建立Hashtble
            for(int n=0;n<depds.Tables[0].Rows.Count;n++)
            {
                  string keyname="@"+depds.Tables["dbo.syscolumns"].Rows[n]["name"];
                  Boolean isnullabled=Convert.ToBoolean( depds.Tables["dbo.syscolumns"].Rows[n]["allowNull"]);
                  dependtbl.Add(keyname,isnullabled);
            }

            return dependtbl;
         }
         catch(SqlException se)
         {
            throw(se);
         }
         catch(Exception e)
         {
            throw(e);
         }
         finally
         {
            if(conn.State ==ConnectionState.Open)
            {
                  conn.Close();
            }
         }
      }
      /// <summary>
      /// 获得指定表的所有字段对象
      /// </summary>
      /// <param name="TableName">表名</param>
      /// <returns>System.Data.DataTable</returns>
      public DataTable GetFields(String TableName)
      {
         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 * from "+TableName,conn);

            comm.FillSchema(myDataSet, SchemaType.Mapped,TableName);

            return myDataSet.Tables[0];
         }
         catch(SqlException se)
         {
            throw(se);
         }
      }
      /// <summary>
      ///私有: 获取数据类型(DbType)
      /// </summary>
      /// <param name="typename">数据类型名称</param>
      /// <returns>DbType</returns>
      private DbType getDbType(string typename)
      {
         //DbType t;
         #region switch datatype
         switch(typename)
         {
                  //int64
            case "bigint":
                  return DbType.Int64;
                  //break;
                  //int32
            case "int":
                  return DbType.Int32;
                  //break;
                  //int16
            case "smallint":
                  return DbType.Int16;
                  //break;
                  //byte
            case "binary":
                  return DbType.Byte;
                  //break;
            case "image":
                  return DbType.Byte;
                  //break;
            case "varbinary":
                  return DbType.Byte;
                  //break;
            case "tinyint":
                  return DbType.Byte;
                  //break;
                  //boolean
            case "bit":
                  return DbType.Boolean;
                  //break;
                  //string
            case "varchar":
                  return DbType.String;
                  //break;
            case "text":
                  return DbType.String;
                  //break;
            case "nvarchar":
                  return DbType.String;
                  //break;
            case "ntext":
                  return DbType.String;
                  //break;
            case "nchar":
                  return DbType.String;
                  //break;
            case "char":
                  return DbType.String ;
                  //break;
                  //DateTime
            case "datetime":
                  return DbType.DateTime;
                  //break;
            case "smalldatetime":
                  return DbType.DateTime;
                  //break;
            case "timestamp":
                  return DbType.DateTime;
                  //break;
                  //double
            case "Float":
                  return DbType.Double;
                  //break;
                  //decimal
            case "decimal":
                  return DbType.Decimal;
                  //break;
            case "money":
                  return DbType.Decimal ;
                  //break;
            case "smallmoney":
                  return DbType.Decimal;
                  //break;
            default:
                  return DbType.Object;
                  //break;
         }
                     #endregion

      }
      /// <summary>
      /// 获取数据类型(SqlDbType)
      /// </summary>
      /// <param name="typename">数据类型名称</param>
      /// <returns>SqlDbType</returns>
      public SqlDbType getSqlDbType(string typename)
      {
         //SqlDbType t;
         #region switch datatype
         switch(typename)
         {
                  //int64
            case "bigint":
                  return SqlDbType.BigInt;
                  //break;
                  //int32
            case "int":
                  return SqlDbType.Int;
                  //break;
                  //int16
            case "smallint":
                  return SqlDbType.SmallInt;
                  //break;
                  //byte
            case "binary":
                  return SqlDbType.Binary;
                  //break;
            case "image":
                  return SqlDbType.Image;
                  //break;
            case "varbinary":
                  return SqlDbType.VarBinary;
                  //break;
            case "tinyint":
                  return SqlDbType.TinyInt;
                  //break;
                  //boolean
            case "bit":
                  return SqlDbType.Bit;
                  //break;
                  //string
            case "varchar":
                  return SqlDbType.VarChar;
                  //break;
            case "text":
                  return SqlDbType.Text;
                  //break;
            case "nvarchar":
                  return SqlDbType.NVarChar;
                  //break;
            case "ntext":
                  return SqlDbType.NText ;
                  //break;
            case "nchar":
                  return SqlDbType.NChar;
                  //break;
            case "char":
                  return SqlDbType.Char;
                  //break;
                  //DateTime
            case "datetime":
                  return SqlDbType.DateTime;
                  //break;
            case "smalldatetime":
                  return SqlDbType.SmallDateTime;
                  //break;
            case "timestamp":
                  return SqlDbType.Timestamp;
                  //break;
                  //double
            case "Float":
                  return SqlDbType.Float;
                  //break;
                  //decimal
            case "decimal":
                  return SqlDbType.Decimal;
                  //break;
            case "money":
                  return SqlDbType.Money;
                  //break;
            case "smallmoney":
                  return SqlDbType.SmallMoney;
                  //break;
            default:
                  return SqlDbType.Variant;
                  //break;
         }
                     #endregion

      }
      #endregion
}
}