How to obtain the structure information of a table

How to obtain the structure information of a table

HBZXF (ah Good)
Http://www.cnblogs.com/HBZXF

Recent projects need to use ASP.net to make custom query functionality, depending on the field name and condition of the table, you need to know how to display the structure of a table through ado.net and bind to the controls that need to be displayed, in fact Ado.net has provided the appropriate function to do this, the following details Code instance to see the results.

Since I used webservice to return the results of the data, I produced the GetTableSchema (string sqlstr) function, the detailed code is as follows:

<summary>
Name: GetTableSchema
Parameters: String sqlstr, foreground query statement
Function: Get table structure
return value: DataSet
</summary>
[WebMethod (enablesession=true,description = "gets the table structure.) ")]
Public DataSet GetTableSchema (string sqlstr)
{
DataSet ds = new DataSet ();
OleDbCommand cmd = new OleDbCommand (sqlstr,conn);
Conn. Open ();
OleDbDataReader read = cmd. ExecuteReader ();
DataTable TB = read. GetSchemaTable ()/Pay attention to this sentence
Ds. Tables.add (TB);
Read. Close ();
Conn. Close ();
return DS;
}


When the webservice is set up, the following needs to be displayed in the foreground to display the corresponding table structure field information to a
In DropDownList. But there is a problem to be solved is that I want to query the table field information in the form of the use of the English text paragraph, how to change the English field of the table into the Chinese text segment will need a temporary dataset to convert, In the temporary dataset to create a temporary table to store the Chinese information of the field and the comparison of English information, through the entire temporary table information to take out the corresponding text segment information, and echo back to the foreground Webcombo (note: Due to the user demand for this project, We used Webcombo instead of the developer's DropDownList. The detailed code is as follows:

CheckBoxList CBL = new CheckBoxList ();
check box is displayed in longitudinal shape, 3 records per column reality
Cbl. RepeatColumns = 3;
Cbl. RepeatDirection = horizontal;
Creates a new ListItem that holds the value of the column in the attempted table
ListItem li = new ListItem ();

DataSet d = new DataSet ();
DataTable T = new DataTable ("T");//CREATE TABLE T
DataColumn C = new DataColumn ();
C.datatype = System.Type.GetType ("System.String");
C.columnname = "C";
T.columns.add (c);
DataColumn C1 = new DataColumn ();
C1. DataType = System.Type.GetType ("System.String");
C1. ColumnName = "C1";
T.columns.add (C1);
DataColumn C2 = new DataColumn ();
C2. DataType = System.Type.GetType ("System.String");
C2. ColumnName = "C2";
T.columns.add (C2);
R["C": the English name of the column r["C1"]: The Chinese name of the column r["C2"]: Type of column
DataRow R;
Returns the structure of a table by attempting to manipulate the GetTableSchema function in WebService
foreach (DataRow row in us. GetTableSchema ("SELECT * from ZC_VIEW_KPB"). Tables[0]. Rows)
{
R = T.newrow ();
R["C"] = row["ColumnName"]. ToString ();
String Colname= "";
ColName = row["ColumnName"]. ToString ();
Switch (colname)
{
Case "Kpid":
r["C1"] = "card number";
Break
Case "ZJRQ":
r["C1"] = "Depreciation Date";
Break
Case "Zcmlid":
r["C1"] = "Asset catalogue number";
Break
Default
Break
}
Add the name of the column to the check box individually
CBL is a CheckBoxList control that displays all field information in the table structure, meaning that users can select different field contents and automatically generate column information for the DataGrid to be browsed by users.
Cbl. Items.Add (New ListItem (r["C1"). ToString (), colname));
The content stored in r["C2" is the type of field + English name
r["C2"] = row["ProviderType"]. ToString () +colname;
T.rows.add (R);
}

Make a table and tie it to a drop-down list
Wcol as Webcombo control name
D.tables.add (t);
This. Wcol.datasource = D;
This. Wcol.datamember = d.tables["T"]. ToString ();
This. Wcol.datatextfield = "C1";
This. Wcol.datavaluefield = "C2";
This. Wcol.databind ();


In this case, we have to bind the table field information to the desired control, if you want to help set to DropDownList is the same reason, you may wish to try.

Because the personal level is limited, the code unavoidably is disorderly, hope understanding!