C#语言|根据表名获取表字段信息 C#语言|随笔

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

/// /// 创建SqlParameter /// 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; } } /// 【C#语言|根据表名获取表字段信息】指定表名，如果为空，则查询所有表 /// 数据库连接串 /// public static List TC(string specifyTable, string connStr) { List p = new List(); 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 = https://www.it610.com/article/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 建立一个参数 /// /// 根据表列信息集合和表对应实体类所属某个属性返回一个SQL参数 /// /// 表列信息集合 /// 属性 /// 属性对应值 /// 参数方向 /// 一个SQL参数，已经初始化 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 = https://www.it610.com/article/v; } return sp; } /// /// 根据表列信息集合和表对应实体类所属某个属性返回一个SQL参数 /// /// 表列信息集合 /// 属性 /// 属性对应值 /// 一个SQL参数，已经初始化 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 = https://www.it610.com/article/v; } return sp; } #endregion }

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