首页 新闻 赞助 找找看

DataList显示数据库表的字段名

0
悬赏园豆:30 [待解决问题]

  /// <summary>
  /// 返回表名下所有字段
  /// </summary>
  /// <param name="SqlConnectionStr"></param>
  /// <returns></returns>
  public static void GetAllColumn(string source, string tableName,DataList dl)
  {
  List<string> list = new List<string>();
  //打开连接
  SqlConnection sqlcn = new SqlConnection(source);
  sqlcn.Open();
  SqlCommand sqlcmd = new SqlCommand("SELECT TOP 1 * FROM [" + tableName + "]", sqlcn);
  SqlDataReader dr = sqlcmd.ExecuteReader();
  for (int i = 0; i < dr.FieldCount; i++)
  {
  list.Add(dr.GetName(i));//数据库的字段名
  }
  dl.DataSource = list;
  dl.DataBind();
  sqlcn.Close();
  }

<asp:DataList ID="DataList2" runat="server" Width="97px">
  <ItemTemplate>
  <table border="1" cellpadding="0" cellspacing="0">
  <tr>
  <td style="width: 100px; height: 19px">
  <asp:CheckBox ID="CheckBox1" runat="server" /></td>
  <td style="width: 100px; height: 19px">
  <asp:Label ID="Label1" runat="server" Text='<%#Eval("怎么绑定") %>'></asp:Label></td>
  </tr>
  </table>
  </ItemTemplate>
  </asp:DataList>

我在DataList用Label显示数据库某一张表下的所有字段的名称(比如用户表中有用户账号(account)、密码(pwd)两个字段,要在页面上显示为account、pwd,而不是字段名对应的值。)。望各位朋友指点!
效果图为:

jenston的主页 jenston | 初学一级 | 园豆:110
提问于:2012-01-04 14:05
< >
分享
所有回答(2)
0

 SqlConnection sqlcn = new SqlConnection(Connecction);

DataTable dtSchema=sqlcn.GetSchema();

这样再根据表名得到你想的要的表的结构;

或用这个sql也可以

SELECT 字段名 =Rtrim(b.name)        ,主键 =CASE                WHEN h.id ISNOTNULLTHEN'PK'                ELSE''              END        ,字段类型 = Type_name(b.xusertype)                 +CASE                     WHEN b.colstat &1=1THEN'[ID('                                                  +CONVERT(VARCHAR,Ident_seed(a.name))                                                  +','                                                  +CONVERT(VARCHAR,Ident_incr(a.name))                                                  +')]'                     ELSE''                   END        ,长度 = b.length        ,允许空 =CASE b.isnullable                 WHEN0THEN'N'                 ELSE'Y'               END        ,默认值 =Isnull(e.TEXT,'')        ,字段说明 =Isnull(c.VALUE,'') FROM     sysobjects a          INNERJOIN  sys.all_objects aa            ON a.id=aa.object_id               AND  schema_name(schema_id)='dbo'          ,syscolumns b          LEFTOUTERJOIN sys.extended_properties c            ON b.id = c.major_id               AND b.colid = c.minor_id          LEFTOUTERJOIN syscomments e            ON b.cdefault = e.id          LEFTOUTERJOIN (SELECT g.id                                  ,g.colid                           FROM   sysindexes f                                  ,sysindexkeys g                           WHERE  (f.id = g.id)                                  AND (f.indid = g.indid)                                  AND (f.indid >0)                                  AND (f.indid <255)                                  AND (f.status &2048) <>0) h            ON (b.id = h.id)               AND (b.colid = h.colid) WHERE    (a.id = b.id)          AND (a.id =Object_id('表名'))  --要查询的表改成你要查询表的名称ORDERBY b.colid /************************************************************************************************************** b. **************************************************************************************************************/SELECT 表名 =CASE               WHEN a.colorder =1THEN d.name               ELSE''             END        ,表说明 =CASE                 WHEN a.colorder =1THENIsnull(f.VALUE,'')                 ELSE''               END        ,字段序号 = a.colorder        ,字段名 = a.name        ,标识 =CASE                WHENColumnproperty(a.id,a.name,'IsIdentity') =1THEN''                ELSE''              END        ,主键 =CASE                WHENEXISTS (SELECT1                             FROM   sysobjects                             WHERE  xtype ='PK'                                    AND name IN (SELECT name                                                 FROM   sysindexes                                                 WHERE  indid IN (SELECT indid                                                                  FROM   sysindexkeys                                                                  WHERE  id = a.id                                                                         AND colid = a.colid))) THEN''                ELSE''              END        ,类型 = b.name        ,字段长度 = a.length        ,占用字节数 =Columnproperty(a.id,a.name,'PRECISION')        ,小数位数 =Isnull(Columnproperty(a.id,a.name,'Scale'),0)        ,允许空 =CASE                 WHEN a.isnullable =1THEN''                 ELSE''               END        ,默认值 =Isnull(e.TEXT,'')        ,字段说明 =Isnull(g.[value],'') FROM     syscolumns a          LEFTJOIN systypes b            ON a.xusertype = b.xusertype          INNERJOIN sysobjects d            ON (a.id = d.id)               AND (d.xtype ='U')               AND (d.name <>'dtproperties')           INNERJOIN  sys.all_objects c             ON d.id=c.object_id                 AND  schema_name(schema_id)='dbo'          LEFTJOIN syscomments e            ON a.cdefault = e.id          LEFTJOIN sys.extended_properties g            ON (a.id = g.major_id)               AND (a.colid = g.minor_id)          LEFTJOIN sys.extended_properties f            ON (d.id = f.major_id)               AND (f.minor_id =0) --where d.name='要查询的表'         --如果只查询指定表,加上此条件ORDERBY a.id          ,a.colorder

画方软件 | 园豆:778 (小虾三级) | 2012-01-04 14:51
0

数据源不要用List<string>,你自己构造一个DataTable,然后定义一个列,然后将字段信息添加进去

DataTable dt = new DataTable();

dt.Columns.Add("columnName",typeof(string));

DataRow newRow = null;

for (int i = 0; i < dr.FieldCount; i++)
  {

  newRow = dt.NewRow();
     newRow["columnName"] = dr.GetName(i);//数据库的字段名

     dt.Rows.Add(newRow);

  }

dl.DataSource = dt ;
dl.DataBind();

然后页面绑定的时候Eval("columnName")就可以了

Eugene Wang | 园豆:239 (菜鸟二级) | 2012-01-05 13:07
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册