GO
/****** 对象: StoredProcedure [dbo].[pro_GenerateCheckInput_webControl] 脚本日期: 08/13/2012 10:10:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*****************************************************
** DECRIPTION: 生成（c#检查输入数据、web界面控件）代码的存储过程
** 大于两个字符，并以cd或id结尾的，用Combobox控件
** VERSION AUTH DATE Defect No DESC
** -------- ------------ ------------ ----------------- ------------------------------
** V000.0.1 pukuimin 08/04/2012 新建程序
** -------- ------------ ------------ ----------------- -------------------------------
*******************************************************/
ALTER procedure [dbo].[pro_GenerateCheckInput_webControl](
@ProName NVARCHAR(200), ---存储过程名
@TableName NVARCHAR(200) ---表名
)
--WITH ENCRYPTION ---加锁
as
begin
declare @tempProperty varchar(200)--临时字段
declare @DATA_TYPE varchar(200)--临时数据类型
declare @ckinput varchar(8000) ----输入检查
declare @tempValue varchar(200) --从控件取值
declare @webControls varchar(8000) ----web页面控件代码
declare @tempcomment varchar(100)
SELECT @tempProperty='',@DATA_TYPE='',@ckinput='',@tempValue='',@webControls='',@tempcomment=''
if isnull(@ProName,'')='' or isnull(@TableName,'')=''
begin
print '存储过程名或表名不能为空！'
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类型
@tempProperty=dbo.fun_get_UpperFirst(replace([name],'@','')), --参数
@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+'不能为空！\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)+
dbo.fun_get_tabspace(4)+'strErr+="'+@tempcomment+'格式不正确！\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)+
dbo.fun_get_tabspace(4)+'strErr+="'+@tempcomment+'格式不正确！\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)+
dbo.fun_get_tabspace(4)+'strErr+="'+@tempcomment+'格式不正确！\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 '没有此存储过程！'
return 0
end
set @webControls=@webControls+'</table>'
print dbo.fun_get_tabspace(3)+'#region 检查输入数据正确性'
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 '<!--输入控件 -->'
print @webControls
end
/*
exec [pro_GenerateCheckInput_webControl] 'pro_set_Stuinfo','stuinfo'
*/