How to get the structure information of a table
HBZXF (A Ho) http://www.cnblogs.com/hbzxf
Recent projects need to use ASP.NET to create custom query features, establish different query statements according to 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 need to display On the control, ADO.NET has provided the corresponding function to do this, the following is checked by the detailed code instance.
Since I use WebService to return the result of the data, the GetTableSchema (String SQLSTR) function is created, and the detailed code is as follows:
///
/// Name: getTablesChema /// Parameters: string SQLSTR, front query statement /// function: get the table structure /// return value: dataset ///
[WebMethod (EnableSession = true, description = "gets the table structure.")] Public DataSet Get
TableSchema (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 established, the front desk is displayed in a DROPDOWNLIST below. However, there is a problem to solve that the table field information I have to query uses an English field when I build a table, how to turn the English field of the table to the Chinese field, need a temporary Dataset to switch, in temporary A temporary table is established in the DataSet to store the Chinese information of the field and the English information of English information. Reasons for demand, we use Webcombo instead of developers' DropDownList). The detailed code is as follows:
// CheckBoxList CBL = new checkboxlist (); // check box display mode is longitudinal, each column reality 3 record CBL.RepeatColumns = 3; //cbl.repeator = horizontal; // Establish a new ListItem, save trial ListItem Li = new ListItem (); DataSet D = New DataSet (); DATATABLE T = New DataTable ("t"); // Establish 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.dattype = system.type.gettype ("System. "); C1.columnname =" c1 "; t.columns.add (c1); datacolumn c2 = new datacolumn (); c2.dattype = system.type.gettype (" system.string "); c2.columnname = "C2"; t.columns.add (c2); // r ["c"]: English name R ["C1"]: Column of Chinese Name R ["C2"]: Column Type DATAROW R; // By trying to operate the GetTablesChema function in WebService (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 (color) {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 column one by one to check box // CBL is a checkboxlist control, display table structure all fields Information, means that users can choose different fields and automatically generate column information for DataGrid, for users to browse, don't pay attention to 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);} / / Generate the table, and tied to the drop-down list // wcol to the 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.database ();
At this point, we have bind the field information of the table to the needed control, if you want to help DropDownList, everyone will try it.
Because the personal level is limited, the code is inevitable, I hope to understand!
