一般有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; } } } }
|