How does ASP. NET obtain the structure information of a table?

How to obtain the structure information of a table

Hbzxf (Hao)
Http://www.cnblogs.com/HBZXF

ASP. net to create a custom query function, create different query statements based on the table's Field Names and conditions, this requires you to know how to use ADO. net to display the structure of a table and bind it to the control to be displayed. In fact, ADO. net has provided corresponding functions to achieve this, and the detailed code instance is used below to view the results.

Because I use WebService to return data results, I created the gettableschema (string sqlstr) function. The detailed code is as follows:

///

/// Name: Get

Tableschema
/// Parameter: String sqlstr, foreground query statement
/// Function: Obtain the table structure
/// Return value: Dataset
///
[Webmethod (enablesession = true, description = ") to obtain 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;
}

After a WebService is created, you need to display the corresponding table structure field information to
Dropdownlist. However, one problem is that the field information of the table I want to query uses English fields when creating the table, A temporary dataset is required to convert the English field of the table into a Chinese field, create a temporary table in the temporary dataset to store the Chinese information of the field and the contrast between the English information, and retrieve the corresponding Chinese field information through the whole temporary table information, and display it back to webcombo at the front end (Note: Due to the user requirements of this project, we use webcombo to replace the developer's dropdownlist ). The detailed code is as follows:

// Checkboxlist CBl = new checkboxlist ();
// The check box is vertically displayed, with three records in each column
CBl. repeatcolumns = 3;
// CBl. repeatdirection = horizontal;
// Create a new listitem to store the column values in the test chart.
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"]: English Column name R ["C1"]: Chinese column name R ["C2"]: column type
Datarow R;
// Try to operate the gettableschema function in WebService to return the table structure
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 directory number ";
Break;
Default:
Break;
}
// Add the column names one by one to the check box
// CBl is a checkboxlist control that displays information about all fields in the table structure. It enables users to select different fields and automatically generate column information of the DataGrid for users to browse.
CBl. Items. Add (New listitem (R ["C1"]. tostring (), colname ));
// The content stored in R ["C2"] is the field type + English name
R ["C2"] = row ["providertype"]. tostring () + colname;
T. Rows. Add (R );
}

// Generate a table and bind it to the drop-down list
// Wcol is the name of the webcombo Control
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 ();

At this point, we have bound the field information of the table to the required control. If you want to help set it to dropdownlist, you can try it.

Due to the limited individual level, the code is messy. Sorry!