databaseinfo.cs

本程序演示了在ASP.NET中使用XML/XSLT

				strName = ( ( System.Data.SqlClient.SqlConnection ) myConn ).DataSource ;
			strName = strName + " - " + myConn.Database ;

			string strSQL = null;
			strSQL = @"
select
	sysobjects.name ,
	syscolumns.name  ,
	systypes.name ,
	syscolumns.length , 
	syscolumns.isnullable ,
	sysobjects.type
from 
	syscolumns,
	sysobjects,
	systypes 
where 
	syscolumns.id=sysobjects.id 
	and syscolumns.xusertype=systypes.xusertype 
	and (sysobjects.type='U' or sysobjects.type='V' )
	and systypes.name <>'_default_' 
	and systypes.name<>'sysname' 
order by 
	sysobjects.name,
	syscolumns.name";
			myTables.Clear();

			using( System.Data.IDbCommand myCmd = myConn.CreateCommand())
			{
				myCmd.CommandText = strSQL ;
				IDataReader myReader = myCmd.ExecuteReader( CommandBehavior.SingleResult );
				TableInfo LastTable = null;
				while( myReader.Read())
				{
					string TableName = myReader.GetString(0).Trim();
					if( LastTable == null || LastTable.Name != TableName )
					{
						LastTable = new TableInfo();
						myTables.Add( LastTable );
						LastTable.Name = TableName ;
						LastTable.Tag = Convert.ToString( myReader.GetValue( 5 ));
					}
					FieldInfo NewField = new FieldInfo();
					LastTable.Fields.Add( NewField );
					NewField.Name = myReader.GetString(1);
					NewField.FieldType = myReader.GetString(2);
					NewField.FieldWidth = myReader[3].ToString();
					if( myReader.IsDBNull( 4 ) == false)
						NewField.Nullable = (myReader.GetInt32(4) == 1);
					RecordCount ++ ;
				}//while
				myReader.Close();
				// 加载主键信息
				for( int iCount = myTables.Count - 1 ; iCount >= 0 ; iCount -- )
				{
					TableInfo myTable = myTables[ iCount ] ;
					if( string.Compare( ( string ) myTable.Tag , "U" , true ) == 0 )
					{
						try
						{
							myCmd.CommandText = "sp_helpindex \"" + myTable.Name + "\"" ;
							//myCmd.CommandType = System.Data.CommandType.Text ;
							myReader = myCmd.ExecuteReader( );
							while( myReader.Read())
							{
								string strKeyName = myReader.GetString(0);
								string strDesc = myReader.GetString(1);
								string strFields = myReader.GetString(2);
								bool bolPrimary = ( strDesc.ToLower().IndexOf("primary") >= 0 );
								foreach( string strField in strFields.Split(','))
								{
									FieldInfo myField = myTable.Fields[ strField.Trim()];
									if( myField != null)
									{
										myField.Indexed = true;
										myField.PrimaryKey = bolPrimary ;
									}
								}//foreach
							}//while
							myReader.Close();
						}
						catch( Exception ext )
						{
							//this.List.Remove( myTable );
							myTable.Name = myTable.Name + " " + ext.Message ;
						}
					}
				}//foreach
			}//using
			return RecordCount ;
		}//public int LoadFromSQLServer( System.Data.IDbConnection myConn )

		#endregion

		/// <summary>
		/// 复制对象
		/// </summary>
		/// <returns>复制品</returns>
		public object Clone()
		{
			DataBaseInfo info = new DataBaseInfo();
			info.intFillStyle = this.intFillStyle ;
			info.strDescription = this.strDescription ;
			info.Name = this.strName ;
			foreach( TableInfo table in myTables )
			{
				TableInfo NewTable = new TableInfo();
				NewTable.Name = table.Name ;
				NewTable.Remark = table.Remark ;
				NewTable.Description = table.Description ;
				NewTable.Tag = table.Tag ;
				info.myTables.Add( NewTable );
				foreach( FieldInfo field in table.Fields )
				{
					FieldInfo NewField = new FieldInfo();
					NewField.Name = field.Name ;
					NewField.Remark = field.Remark ;
					NewField.Description = field.Description ;
					NewField.FieldType = field.FieldType ;
					NewField.FieldWidth = field.FieldWidth ;
					NewField.Indexed = field.Indexed ;
					NewField.Nullable = field.Nullable ;
					NewField.PrimaryKey = field.PrimaryKey ;
					NewTable.Fields.Add( NewField );
				}
			}
			return info ;
		}
	}//public class DBStructInformation

	/// <summary>
	/// 数据表信息对象
	/// </summary>
	[System.Serializable()]
	[System.Xml.Serialization.XmlType("Table")]
	public class TableInfo
	{
		/// <summary>
		/// 初始化对象
		/// </summary>
		public TableInfo( )
		{
			myFields.myOwnerTable = this ;
		}
		private string strName = null;
		/// <summary>
		/// 对象名称
		/// </summary>
		public string Name
		{
			get{ return strName ;}
			set{ strName = value;}
		}
		private string strRemark = null;
		/// <summary>
		/// 对象说明,一般可以为对象中文名
		/// </summary>
		public string Remark 
		{
			get{ return strRemark ;}
			set{ strRemark = value;}
		}
		private string strDescription = null;
		/// <summary>
		/// 对象说明
		/// </summary>
		public string Description
		{
			get{ return strDescription ;}
			set{ strDescription = value;}
		}

		private object objTag = null;
		/// <summary>
		/// 对象附加数据
		/// </summary>
		public object Tag
		{
			get{ return objTag ;}
			set{ objTag = value;}
		}
		private FieldInfoCollection myFields = new FieldInfoCollection();
		/// <summary>
		/// 字段对象列表
		/// </summary>
		public FieldInfoCollection Fields
		{
			get{ return myFields ;}
		}

		/// <summary>
		/// 字段对象列表类型
		/// </summary>
		public class FieldInfoCollection : System.Collections.CollectionBase 
		{
			internal TableInfo myOwnerTable = null;
			/// <summary>
			/// 返回指定序号的字段信息
			/// </summary>
			public FieldInfo this[ int index ]
			{
				get{ return ( FieldInfo) this.InnerList[ index ] ;}
			}
			/// <summary>
			/// 返回指定名称的字段信息
			/// </summary>
			public FieldInfo this[ string strFieldName ]
			{
				get
				{
					foreach( FieldInfo f in this.InnerList )
					{
						if( string.Compare( f.Name , strFieldName , true ) == 0 )
							return f ;
					}
					return null;
				}
			}
			/// <summary>
			/// 添加字段信息对象
			/// </summary>
			/// <param name="info">字段信息对象</param>
			/// <returns>字段对象在列表中的序号</returns>
			public int Add( FieldInfo info )
			{
				info.OwnerTable = myOwnerTable ;
				return this.List.Add( info );
			}
			public void Remove( FieldInfo info )
			{
				this.List.Remove( info );
			}
		}
	}//public class TableInfo

	/// <summary>
	/// 字段信息对象
	/// </summary>
	[System.Serializable()]
	[System.Xml.Serialization.XmlType("Field")]
	public class FieldInfo
	{
		private string strName = null;
		/// <summary>
		/// 字段名称
		/// </summary>
		public string Name
		{
			get{ return strName ;}
			set{ strName = value;}
		}
		private string strRemark = null;
		/// <summary>
		/// 字段说明,一般可以为字段中文名
		/// </summary>
		public string Remark 
		{
			get{ return strRemark ;}
			set{ strRemark = value;}
		}
		private string strDescription = null;
		/// <summary>
		/// 字段说明
		/// </summary>
		public string Description
		{
			get{ return strDescription ;}
			set{ strDescription = value;}
		}

		private string strFieldType = null;
		/// <summary>
		/// 字段类型
		/// </summary>
		public string FieldType
		{
			get{ return strFieldType ;}
			set{ strFieldType = value;}
		}

		/// <summary>
		/// 判断字段是否是字符串字段
		/// </summary>
		public bool IsString
		{
			get
			{
				return TypeContainStrings( new string[]{ "char" , "text" });
			}
			set{}
		}
		
		/// <summary>
		/// 判断字段是否是整数字段
		/// </summary>
		public bool IsInteger
		{
			get
			{
				return TypeContainStrings( new string[]{ "int" , "bit" });
			}
			set{}
		}

		/// <summary>
		/// 判断字段是否是布尔类型
		/// </summary>
		public bool IsBoolean
		{
			get
			{
				return TypeContainStrings( new string[]{ "bit" });
			}
			set{}
		}

		/// <summary>
		/// 字段是否是数值的字段
		/// </summary>
		public bool IsNumberic
		{
			get
			{
				return TypeContainStrings( new string[]{ "number", "decimal" , "numberic" , "float" , "real" , "double" });
			}
			set{}
		}
		/// <summary>
		/// 是否是日期类型的字段
		/// </summary>
		public bool IsDateTime
		{
			get
			{
				return TypeContainStrings( new string[]{"date" , "datetime"});
			}
			set{}
		}

		/// <summary>
		/// 是否是二进制类型的字段
		/// </summary>
		public bool IsBinary
		{
			get
			{
				return TypeContainStrings( new string[]{"binary" , "long" , "image" });
			}
			set{}
		}
	
		private bool TypeContainStrings( string[] items )
		{
			string type = this.strFieldType ;
			if( type != null )
			{
				type = type.ToLower();
				foreach( string item in items )
				{
					if( type.IndexOf( item ) >= 0 )
						return true ;
				}
			}
			return false;
		}

		
		/// <summary>
		/// 字段对应的数据类型
		/// </summary>
		[System.Xml.Serialization.XmlIgnore()]
		public Type ValueType
		{
			get
			{
				if( this.IsBoolean )
					return typeof( bool );
				if( this.IsInteger )
					return typeof( int );
				if( this.IsBinary )
					return typeof( byte[] );
				if( this.IsDateTime )
					return typeof( DateTime );
				if( this.IsNumberic )
					return typeof( double );
				return typeof( string);
			}
		}

		/// <summary>
		/// 字段对应的数据类型名称
		/// </summary>
		public string ValueTypeName
		{
			get
			{
				return this.ValueType.FullName ;
			}
			set{}
		}

		private string strFieldWidth = "";
		/// <summary>
		/// 字段宽度
		/// </summary>
		public string FieldWidth
		{ 
			get{ return strFieldWidth ;}
			set{ strFieldWidth = value;}
		}

		private bool bolNullable = true ;
		/// <summary>
		/// 字段可否为空
		/// </summary>
		//[System.ComponentModel.DefaultValue( true )]
		public bool Nullable
		{
			get{ return bolNullable ;}
			set{ bolNullable = value;}
		}

		private bool bolPrimaryKey = false;
		/// <summary>
		/// 是否主键
		/// </summary>
		public bool PrimaryKey
		{
			get{ return bolPrimaryKey ;}
			set{ bolPrimaryKey = value;}
		}

		private bool bolIndexed = false;
		/// <summary>
		/// 是否索引
		/// </summary>
		public bool Indexed
		{
			get{ return bolIndexed ;}
			set{ bolIndexed = value;}
		}

		/// <summary>
		/// 字段全名
		/// </summary>
		public string FullName
		{
			get
			{
				if( myOwnerTable == null )
					return strName ;
				else
					return myOwnerTable.Name + "." + strName ;
			}
		}
		/// <summary>
		/// 返回表示对象的字符串
		/// </summary>
		/// <returns>字符串</returns>
		public override string ToString()
		{
			return FullName ;
		}

		private TableInfo myOwnerTable = null;
		/// <summary>
		/// 字段所在的数据表对象
		/// </summary>
		[System.Xml.Serialization.XmlIgnore()]
		public TableInfo OwnerTable
		{
			get{ return myOwnerTable ;}
			set{ myOwnerTable = value;}
		}
	}//public class FieldInfo
}