根据表名获取表字段信息

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

 /// <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
    }

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