At the time of web development. It is often necessary to establish the corresponding input control based on the fields in the database table to obtain the input data. Every time you need to hit the field, it's obviously too inefficient. and easy error. Here is a SQL script that generates a method for the corresponding 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 field with description, use column_name as Label +cast (case when Len (cast (REMARK as NVARCHAR)) <=0 and column_name when Len (CAST (ISNULL (REMARK, ") as NVARCHAR ( ))) <=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 ' descriptive narrative ', (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. Specified according to the actual situation.
2. 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 number of @width is the width of the control, as specified in the actual situation.
This is 200.
4.TOKEN generates the appropriate markup based on the field type. Actually in use. It is possible that the corresponding tag is not very accurate. Please make adjustments according to the usage situation.
5.EXTEND_STR is a special attribute tag string.
This is in the Numberbox control. Limit the number of decimal digits to int and smallint to 0. That is, decimalprecision= "0".
6. The label in the control, with the descriptive description of the field as the first choice, assuming there is no field descriptive narrative. The field name is used.
The resulting markup, such as
Web pages are used for example to
Generated for example with the
Reprint please specify the source http://blog.csdn.net/xxdddail/article/details/37877691
Fineui using SQL scripts to generate corresponding input controls from a database table