根据表名获取表字段信息

实际应用中不免需要调取数据库表字段的相关信息，特此将代码贴出，以作备用，调取的信息： 字段名、字段类型、字段长度、是否主键、说明

 /// <summary>    /// 创建SqlParameter    /// </summary>    public class CP    {        public class ColumnInfo        {            public string ColName { get; set; }            public SqlDbType ColType { get; set; }            public int ColLength { get; set; }            public bool IsPrimary { get; set; }            public string Description { get; set; }        }        /// <param name="specifyTable">指定表名，如果为空，则查询所有表</param>        /// <param name="connStr">数据库连接串</param>        /// <returns></returns>        public static List<ColumnInfo> TC(string specifyTable, string connStr)        {                List<ColumnInfo> p = new List<ColumnInfo>();                using (SqlConnection conn = new SqlConnection(connStr))                {                    StringBuilder sb = new StringBuilder(1000);                    sb.Append("SELECT a.name,(case when (SELECT count(*) FROM sysobjects WHERE (name in (SELECT name FROM sysindexes WHERE (id = a.id) AND (indid in (SELECT indid FROM sysindexkeys WHERE (id = a.id) AND (colid in (SELECT colid FROM syscolumns WHERE (id = a.id) AND (name = a.name))))))) AND (xtype = 'PK'))>0 then '1' else '0' end) isprimary,b.name [type],COLUMNPROPERTY(a.id,a.name,'PRECISION') as [length],isnull(g.value,'') as [description] FROM syscolumns a ");                    sb.Append("left join systypes b on a.xtype=b.xusertype inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'");                    sb.Append("left join syscomments e on a.cdefault=e.id left join sys.extended_properties g on a.id=g.major_id AND a.colid = g.minor_id where d.name =@tname order by a.id,a.colorder");                    conn.Open();                    SqlCommand cmd = new SqlCommand(sb.ToString(), conn);                    cmd.Parameters.Add(new SqlParameter("@tname", SqlDbType.VarChar, 30));                    cmd.Parameters[0].Value = specifyTable;                    SqlDataAdapter sda = new SqlDataAdapter(cmd);                    DataSet ds = new DataSet();                    sda.Fill(ds);                    DataTable dt = ds.Tables[0];                    int count = dt.Rows.Count;                    if (count > 0)                    {                        for (int i = 0; i < count; i++)                        {                            ColumnInfo item = new ColumnInfo() { ColName = dt.Rows[i]["name"].ToString(), ColLength = int.Parse(dt.Rows[i]["length"].ToString()), ColType = StringToSqlType(dt.Rows[i]["type"].ToString()), IsPrimary = (dt.Rows[i]["isprimary"].ToString() == "1" ? true : false), Description = dt.Rows[i]["description"].ToString() };                            p.Add(item);                        }                    }                }                return p;            }        }        //SqlServer数据库类型转换方法        public static SqlDbType StringToSqlType(string String)        {            SqlDbType dbType = SqlDbType.Variant;//默认为Object            switch (String)            {                case "int":                    dbType = SqlDbType.Int;                    break;                case "varchar":                    dbType = SqlDbType.VarChar;                    break;                case "bit":                    dbType = SqlDbType.Bit;                    break;                case "datetime":                    dbType = SqlDbType.DateTime;                    break;                case "decimal":                    dbType = SqlDbType.Decimal;                    break;                case "float":                    dbType = SqlDbType.Float;                    break;                case "image":                    dbType = SqlDbType.Image;                    break;                case "money":                    dbType = SqlDbType.Money;                    break;                case "ntext":                    dbType = SqlDbType.NText;                    break;                case "nvarchar":                    dbType = SqlDbType.NVarChar;                    break;                case "smalldatetime":                    dbType = SqlDbType.SmallDateTime;                    break;                case "smallint":                    dbType = SqlDbType.SmallInt;                    break;                case "text":                    dbType = SqlDbType.Text;                    break;                case "bigint":                    dbType = SqlDbType.BigInt;                    break;                case "binary":                    dbType = SqlDbType.Binary;                    break;                case "char":                    dbType = SqlDbType.Char;                    break;                case "nchar":                    dbType = SqlDbType.NChar;                    break;                case "numeric":                    dbType = SqlDbType.Decimal;                    break;                case "real":                    dbType = SqlDbType.Real;                    break;                case "smallmoney":                    dbType = SqlDbType.SmallMoney;                    break;                case "sql_variant":                    dbType = SqlDbType.Variant;                    break;                case "timestamp":                    dbType = SqlDbType.Timestamp;                    break;                case "tinyint":                    dbType = SqlDbType.TinyInt;                    break;                case "uniqueidentifier":                    dbType = SqlDbType.UniqueIdentifier;                    break;                case "varbinary":                    dbType = SqlDbType.VarBinary;                    break;                case "xml":                    dbType = SqlDbType.Xml;                    break;            }            return dbType;        }        #region 建立一个参数        /// <summary>        /// 根据表列信息集合和表对应实体类所属某个属性返回一个SQL参数        /// </summary>        /// <typeparam name="item">表列信息集合</typeparam>        /// <param name="property">属性</param>        /// <param name="v">属性对应值</param>        /// <param name="pd">参数方向</param>        /// <returns>一个SQL参数，已经初始化</returns>        public static SqlParameter cPa(ColumnInfo item, object v, ParameterDirection pd)        {            if (item == null)            {                return null;            }            SqlParameter sp = null;            if (item != null)            {                sp = new SqlParameter("@" + item.ColName, item.ColType, item.ColLength);                sp.Direction = pd;                sp.Value = v;            }            return sp;        }        /// <summary>        /// 根据表列信息集合和表对应实体类所属某个属性返回一个SQL参数        /// </summary>        /// <typeparam name="item">表列信息集合</typeparam>        /// <param name="property">属性</param>        /// <param name="v">属性对应值</param>        /// <returns>一个SQL参数，已经初始化</returns>        public static SqlParameter cPa(ColumnInfo item, object v)        {            if (item == null)            {                return null;            }            SqlParameter sp = null;            if (item != null)            {                sp = new SqlParameter("@" + item.ColName, item.ColType, item.ColLength);                sp.Direction = ParameterDirection.Input;                sp.Value = v;            }            return sp;        }        #endregion    }

可以根据实际应用对代码进行删节和改进