|
一般有3种方法。 用 ADO.NET 直接执行普通 SQL 语句 SELECT * FROM TABLE ，然后根据返回的记录集的结构来获取结构，ColumnName 就是 select 时的别名。
用 SQL SERVER SMO 对象直接获取数据表的结构。
用 ADO.NET 执行获取 SQL SERVER 系统表的 SQL 语句，然后根据返回集中记录的数据来获取结构。 下面是草稿，直接摘录了 以下是一个 dbtoview.ASHX.cs 文件的源代码 using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Diagnostics; namespace demo_ASP4
{
/// <summary>
/// DbToView 的摘要说明
/// </summary>
public class DbToView : IHttpHandler
{ public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "text/plain";
//context.Response.Write("Hello World");
SqlConnection conn = new SqlConnection();
DataTable schemaTable;
DataRow schemaRow;
DataColumn schemaColumn;
conn.ConnectionString = "Data Source=localhost;Initial Catalog=KBData;User ID=sa;Password=q1W2e3R4";
conn.Open();
SqlCommand cmd = new SqlCommand()
{
CommandType = System.Data.CommandType.Text,
CommandText = "select * from t_topics",
Connection = conn };
SqlDataReader rd = cmd.ExecuteReader();
while (rd.Read()) {
context.Response.Write(rd["title"].ToString());
}
//rd.Close();
//获取结构
//rd = cmd.ExecuteReader(CommandBehavior.KeyInfo);
schemaTable = rd.GetSchemaTable();
for (int i=0;i<schemaTable.Rows.Count;i++)
{
schemaRow= schemaTable.Rows[i];
for (int i2 = 0; i2 < schemaTable.Columns.Count;i2++ )
{
schemaColumn = schemaTable.Columns[i2];
Debug.Print("FieldName:{0}, PropertyName:{1}, PropValue:{2}", "", schemaColumn.ColumnName, schemaRow[i2].ToString());
}
} //For each field in the table...
foreach (DataRow myField in schemaTable.Rows)
{
//For each property of the field...
foreach (DataColumn myProperty in schemaTable.Columns)
{
//Display the field name and value.
Debug.Print(myProperty.ColumnName + " = " + myField[myProperty].ToString());
//Console.WriteLine(myProperty.ColumnName + " = " + myField[myProperty].ToString());
}
Debug.Print("-------------------");
//Debug.Print("FieldName:{0},\tFieldDataType:{1},\tColumnSize:{2},\tIsKey:{3},\tAllowDBNull{4}",
// myField["ColumnName"].ToString(),
// myField["DataTypeName"].ToString(),
// myField["ColumnSize"].ToString(),
// myField["IsKey"].ToString(),
// myField["AllowDBNull"].ToString());
} //For each field in the table...
foreach (DataRow myField in schemaTable.Rows)
{
Debug.Print("-------------------");
Debug.Print("Index:{0},\tFieldName:{1},\tFieldDataType:{2},\tColumnSize:{3},\tIsKey:{4},\tAllowDBNull:{5}",
myField["ColumnOrdinal"].ToString(),
myField["ColumnName"].ToString(),
myField["DataTypeName"].ToString(),
myField["ColumnSize"].ToString(),
myField["IsKey"].ToString(),
myField["AllowDBNull"].ToString());
} //按字段名直接过滤出某个字段的数据结构，并将数据结构放到 DataRow 对象中。
//由于字段名不会重复，所以这个DataRow[]数组对象的长度应该永远为1
DataRow[] selectRow = schemaTable.Select("ColumnName='CreateDate'");
Debug.Print("过滤出 CreateDate 相关资料 \t{0}", selectRow.Length.ToString());
if (selectRow.Length == 1) {
//通过这样，可以直接写个函数取出数据结构
Debug.Print("{0}\t{1}", selectRow[0]["ColumnName"].ToString(),
selectRow[0]["DataTypeName"].ToString());
}
rd.Close();
conn.Close();
} public bool IsReusable
{
get
{
return false;
}
}
}
}
| 
1701841
