获取数据库的字段类型

zsyok 2010-09-02 05:24:15

asp.net 获取数据库的字段类型

...全文

269 12 打赏收藏转发到动态举报

写回复

用AI写文章

12 条回复

切换为时间正序

请发表友善的回复…

发表回复

stpangpang 2010-09-03

打赏
举报

datacolumn.datatype

孟子E章 2010-09-03

打赏
举报



<%@ Page Language="C#" %>



<%@ Import Namespace="System.Data" %>

<%@ Import Namespace="System.Data.OleDb" %>

<%@ Import Namespace="System.Data.SqlClient" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"

 "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">



  protected void Page_Load(object sender, EventArgs e)

  {

    String connectionString = "Data Source=192.168.3.1;Initial Catalog=NorthWind;User Id=sa;Password=撒;";

    SqlConnection con = new SqlConnection(connectionString);

    con.Open();



    String cmd = "Select * From Employees";

    SqlDataAdapter adapter = new SqlDataAdapter();

    adapter.SelectCommand = new SqlCommand(cmd, con);

    DataSet ds = new DataSet();

    adapter.Fill(ds, "Employees");

    DataTable item = ds.Tables[0];



    foreach (DataColumn col in item.Columns)

    {

      Response.Write("<li>字段名字：" + col.ColumnName + " 类型：" + col.DataType);

    }





    con.Close();



    connectionString = "Provider=sqloledb;Data Source=192.168.3.1;Initial Catalog=NorthWind;User Id=sa;Password=sa";

    OleDbConnection cn = new OleDbConnection(connectionString);

    cn.Open();

    DataTable tables = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });



    DataTable cols = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns,

          new object[] { null, null, "Employees", null });



    Response.Write("<h2>Employees 的字段属性：</h2>");

    for (int i = 0; i < cols.Columns.Count; i++)

    {

      Response.Write("<li>" + cols.Columns[i].ColumnName + " = " + cols.Columns[i].DataType);

    }



    Response.Write("<h2>Employees 的字段名称和类型：</h2>");

    foreach (DataRow row in cols.Rows)

    {

      Response.Write("<li>" + row["COLUMN_NAME"] + " = " + (OleDbType)row["DATA_TYPE"]);

    }



    con.Close();

  }





</script>

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

  <title></title>

</head>

<body>

  <form id="form1" runat="server">

  </form>

</body>

</html>

yinglangziwsp 2010-09-03

打赏
举报

看5楼的

村长_乐 2010-09-03

打赏
举报

sql语句查询

generhappy 2010-09-03

打赏
举报

膜拜2楼！！

村长_乐 2010-09-03

打赏
举报

sql语句查询

wwfgu00ing 2010-09-03

打赏
举报

楼上的，可以的，验证过

wanghuaide 2010-09-02

打赏
举报



select   c.Name   as   TableName,b.name   as   ColName,a.name   as   Type,a.length  

from   syscolumns   a,systypes   b,sysobjects  

c   where   c.type= 'U '   and

a.xtype=b.xtype   and   a.id=c.id

studentliudong 2010-09-02

打赏
举报

http://www.cnblogs.com/zyh-nhy/archive/2007/11/24/970952.html

yuyizhao198 2010-09-02

打赏
举报

先取到dataset中可以获取数据类型

wuyq11 2010-09-02

打赏
举报

SELECT
表名 = case when a.colorder=1 then d.name else '' end,
表说明 = case when a.colorder=1 then isnull(f.value,'') else '' end,
字段序号 = a.colorder,
字段名 = a.name,
标识 = case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,
主键 = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id 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=1 then '√'else '' end,
默认值 = isnull(e.text,''),
字段说明 = isnull(g.[value],'')
FROM
syscolumns a
left join
systypes b
on
a.xusertype=b.xusertype
inner join
sysobjects d
on
a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join
syscomments e
on
a.cdefault=e.id
left join
sysproperties g
on
a.id=g.id and a.colid=g.smallid
left join
sysproperties f
on
d.id=f.id and f.smallid=0
where
d.name='要查询的表' order by
a.id,a.colorder