Stored Procedure for generating (c # Checking input data and web interface controls) Code

[SQL]
USE [test]
GO
/***** Object: StoredProcedure [dbo]. [pro_GenerateCheckInput_webControl] script Date: 08/13/2012 10:10:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*************************************** **************
** DECRIPTION: A Stored Procedure for generating code (c # Checking input data and web interface controls)
** If the name is greater than two characters and ends with a cd or id, use the Combobox control.
** Version auth date Defect No DESC
**-------------------------------------------------------------------------------
** V000.0.1 pukuimin 08/04/2012 create a program
**--------------------------------------------------------------------------------
**************************************** ***************/
ALTER procedure [dbo]. [pro_GenerateCheckInput_webControl] (
@ ProName NVARCHAR (200), --- Name of the stored procedure
@ TableName NVARCHAR (200) --- table name
)
-- With encryption --- lock
As
Begin
Declare @ tempProperty varchar (200) -- temporary field
Declare @ DATA_TYPE varchar (200) -- temporary data type
Declare @ ckinput varchar (8000) ---- input check
Declare @ tempValue varchar (200) -- value from the control
Declare @ webControls varchar (8000) ---- web page control code
Declare @ tempcomment varchar (100)
SELECT @ tempProperty = '', @ DATA_TYPE ='', @ ckinput = '', @ tempValue ='', @ webControls = '', @ tempcomment =''
 
If isnull (@ ProName, '') ='' or isnull (@ TableName, '') =''
Begin
Print 'stored procedure name or table name cannot be blank! '
Return 0
End
Set @ webControls = @ webControls + '<table cellSpacing = "0" cellPadding = "0" width = "100%" border = "0">' + CHAR (10)
 
If exists (select * from sys. all_parameters where object_id = object_id (@ ProName ))
Begin
Select
@ DATA_TYPE = type_name (user_type_id), -- SQL type
@ TempProperty = dbo. fun_get_UpperFirst (replace ([name], '@', ''), -- Parameter
@ Tempcomment = dbo. fun_get_comment (@ TableName, @ tempProperty ),
@ TempValue = (
Case
When Right ([name], 2) = 'id' or Right ([name], 2) = 'cd'
Then 'this. dropdownlist' + @ tempProperty + '. SelectedValue. Trim ()'
Else
'This. TextBox '+ @ tempProperty +'. Text. Trim ()'
End
),
@ Ckinput = @ ckinput +
(CASE
When [name] = '@ opr_typ' or [name] =' @ ret 'then''
WHEN @ DATA_TYPE = 'nvarchar 'OR @ DATA_TYPE = 'varchar' OR @ DATA_TYPE = 'Char 'OR @ DATA_TYPE = 'nchar' OR @ DATA_TYPE = 'ntext' OR @ DATA_TYPE =' text'
THEN dbo. fun_get_tabspace (3) + 'if ('+ @ tempValue +'. Length = 0) '+ CHAR (10) +
Dbo. fun_get_tabspace (3) + '{' + CHAR (10) +
Dbo. fun_get_tabspace (4) + 'strerr + = "'+ @ tempcomment +' cannot be blank! \ N "; '+ CHAR (10) +
Dbo. fun_get_tabspace (3) + '}' + CHAR (10)
WHEN @ DATA_TYPE = 'bigint'
THEN dbo. fun_get_tabspace (3) + 'if (! DataValidate. IsNumber ('+ @ tempValue +') '+ CHAR (10) +
Dbo. fun_get_tabspace (3) + '{' + CHAR (10) +
Invalid dbo. fun_get_tabspace (4) + 'strerr + = "'+ @ tempcomment +' format! \ N "; '+ CHAR (10) +
Dbo. fun_get_tabspace (3) + '}' + CHAR (10)
WHEN @ DATA_TYPE = 'int'
THEN dbo. fun_get_tabspace (3) + 'if (! DataValidate. IsInt ('+ @ tempValue +') '+ CHAR (10) +
Dbo. fun_get_tabspace (3) + '{' + CHAR (10) +
Invalid dbo. fun_get_tabspace (4) + 'strerr + = "'+ @ tempcomment +' format! \ N "; '+ CHAR (10) +
Dbo. fun_get_tabspace (3) + '}' + CHAR (10)
WHEN @ DATA_TYPE = 'numeric'
THEN dbo. fun_get_tabspace (3) + 'if (! DataValidate. IsDecimalSign ('+ @ tempValue +') '+ CHAR (10) +
Dbo. fun_get_tabspace (3) + '{' + CHAR (10) +
Invalid dbo. fun_get_tabspace (4) + 'strerr + = "'+ @ tempcomment +' format! \ N "; '+ CHAR (10) +
Dbo. fun_get_tabspace (3) + '}' + CHAR (10)
ELSE
''
END), ------ dbo. [fun_get_cssdt_by_sqldt] (@ DATA_TYPE) + '. MinValue)
@ WebControls = @ webControls +
(Case
When [name] = '@ opr_typ' or [name] =' @ ret 'then''
Else
Dbo. fun_get_tabspace (1) + '<tr>' +
Char (10) + dbo. fun_get_tabspace (1) + '<td height = "25" width = "30%" align = "right">' + @ tempcomment + ': </td>' +
Char (10) + dbo. fun_get_tabspace (1) + '<td height = "25" width = "*" align = "left">' + (
Case
When Right ([name], 2) = 'id' or Right ([name], 2) = 'cd'
Then char (10) + dbo. fun_get_tabspace (1) + '<asp: DropDownList ID = "DropDownList' + @ tempProperty + '" runat = "server" Width = "120px"> </asp: DropDownList>'
Else char (10) + dbo. fun_get_tabspace (1) + '<asp: TextBox id = "TextBox' + @ tempProperty + '" runat = "server" Width = "120px"> </asp: TextBox>'
End
) +
Char (10) + dbo. fun_get_tabspace (1) + '</td>' + char (10) + dbo. fun_get_tabspace (1) + '</tr>' + char (10)
End)
From sys. all_parameters where object_id = object_id (@ ProName)
 
End
Else
Begin
Print 'This stored procedure does not exist! '
Return 0
End
Set @ webControls = @ webControls + '</table>'
 
 
 
Print dbo. fun_get_tabspace (3) + '# region check input data correctness'
Print dbo. fun_get_tabspace (3) + 'string strErr = "";'
Print @ ckinput
Print dbo. fun_get_tabspace (3) + '# endregion'
Print char (10) + char (10) + char (10)
Print '<! -- Input control -->'
Print @ webControls
End
/*
 
Exec [pro_GenerateCheckInput_webControl] 'Pro _ set_Stuinfo ', 'stuinfo'
 
 
*/
Author: pukuimin1226