Fineui using SQL scripts to generate the appropriate input control from a database table

In web development, it is often necessary to create the appropriate input controls based on the fields in the database table to obtain the input data. Every time you need to hit the field, it is obviously too inefficient and prone to error. This provides a way for a SQL script to generate the appropriate input control.

Use Dbdemodeclare @TEMP_TABLE_NAME NVARCHAR (DECLARE) @WIDTH NVARCHAR Set @TEMP_TABLE_NAME = ' Stuff ' Set @WIDTH = ' <f ' SELECT ': ' +token+ ' runat= "server" id= "' +token+ ' _ ' +column_name+ '" label= "'--no fields described, use column_name as Label + Cast (case when Len (cast (REMARK as NVARCHAR)) <=0 then column_name when Len (CAST (ISNULL (REMARK, ") as NVARCHAR (150 ))) <=0 then Column_nameelse REMARK END as NVARCHAR () + ' "' + ' width=" ' [email protected]+ ' "' + ' ></f: ' +token+ ' > ' as DATA--tag string--, table_name,column_name,data_type,token,extend_str,remarkfrom (SELECT temp_schema_ COLUMNS. table_name,--as ' table name ', Temp_schema_columns. column_name,--as ' column name ', Temp_schema_columns. Data_type,--as ' type ', temp_extended_properties.value as REMARK,--as ' description ' (case when temp_schema_columns. data_type= ' int ' then ' Numberbox ' when temp_schema_columns. Data_type= ' numeric ' then ' Numberbox ' when temp_schema_columns. Data_type= ' smallint ' then ' Numberbox ' when temp_schema_columns. Data_type= ' daTetime ' Then ' DatePicker ' when temp_schema_columns. Data_type= ' bit ' then ' CheckBox ' ELSE ' TextBox ' END ' as token,--tag (case when Temp_schema_columns. data_type= ' int ' then ' decimalprecision= "0" when Temp_schema_columns. Data_type= ' smallint ' then ' decimalprecision= "0" ' ELSE ' END) as EXTEND_STR--Special attribute tag string from ((SELECT temp_tables.name as TABLE_NAME, temp_columns.name as column_name, temp_columns.id as table_id, Temp_columns.colid as column_id from (SELE CT * from sysobjects WHERE type= ' U ') as Temp_tablesinner JOIN (SELECT * from syscolumns) as Temp_columns ontemp_tables.i d=temp_columns.id) as Temp_table_columnsinner JOIN (select Table_name,column_name,data_type from Information_ Schema.columns) as Temp_schema_columnson temp_schema_columns. Table_name=temp_table_columns.table_name and Temp_schema_columns. Column_name=temp_table_columns.column_namefull JOIN (SELECT value,major_id,minor_id from sys.extended_properties) as Temp_extended_propertieson Temp_table_columns.table_id=temp_extended_properties.major_id and temp_table_columns.column_id=temp_extended_properties.minor_id) WHERE [email  Protected]_table_name) as Temp_table

Description

1.DBDemo is the database name, as specified in the actual situation.
2. The parameter @temp_table_name the name of the database table for which the input control is generated, as specified in the actual situation. This is stuff.
3. The parameter @width is the width of the control, specified according to the actual situation. This is 200.
4.TOKEN generates the corresponding tag based on the field type. In actual use, may be the corresponding mark is not very accurate, please adjust according to the use situation.
5.EXTEND_STR is a special attribute tag string. Here, for the Numberbox control, the number of decimal digits for int and smallint is 0. That is, decimalprecision= "0".
6. The label in the control, with the description of the field as the preferred, if no field is described, the field name is used.

The resulting markup, such as

Use the Web page as

Generated as

Reprint please specify the source http://blog.csdn.net/xxdddail/article/details/37877691