动软MySQL存储过程模板
<#@ template language="c#" HostSpecific="True" #>
<#@ output
extension= ".cs" #>
<#
TableHost host =
(TableHost)(Host);
host.Fieldlist.Sort(CodeCommon.CompareByintOrder);
int
columnCount=host.Fieldlist.Count;
string IdentityStr="id";
string
specStr="datetime";
foreach (ColumnInfo c in host.Fieldlist)
{
if(c.IsIdentity)
{
IdentityStr=c.ColumnName.ToString().ToLower();
break;
}
}
#>
DROP procedure IF EXISTS <#= host.GetDALClass(host.TableName)
#>_GetMaxId;
CREATE PROCEDURE <#= host.GetDALClass(host.TableName)
#>_GetMaxId()
BEGIN
SELECT MAX(Id) FROM <#=
host.GetDALClass(host.TableName) #>;
END;
DROP procedure IF EXISTS <#= host.GetDALClass(host.TableName)
#>_Exists;
CREATE PROCEDURE <#= host.GetDALClass(host.TableName)
#>_Exists
($_Id int)
BEGIN
SELECT count(1) FROM <#=
host.GetDALClass(host.TableName) #> WHERE Id=$_Id ;
END;
DROP procedure IF EXISTS <#= host.GetDALClass(host.TableName)
#>_ADD;
CREATE PROCEDURE <#= host.GetDALClass(host.TableName)
#>_ADD(
<#int updateI=1; foreach (ColumnInfo c in
host.Fieldlist)
{ if(c.IsIdentity) { #>OUT $_<#=
c.ColumnName.ToString().ToLower()#> <#=c.TypeName#>
(<#=c.Length#>)<#if (updateI!=columnCount){#>,<#}#><# }
else{ #><#if(c.TypeName==specStr){#>$_<#=
c.ColumnName.ToString().ToLower()#>
<#=c.TypeName#><#}else{#>$_<#=
c.ColumnName.ToString().ToLower()#>
<#=c.TypeName#>(<#=c.Length#>)<#}#><#if
(updateI!=columnCount){#>,<#}#><#} updateI++;} #>)
BEGIN
INSERT INTO <#= host.GetDALClass(host.TableName) #>(<#updateI=1;
foreach (ColumnInfo c in host.Fieldlist)
{ if(c.IsIdentity) {
updateI++;continue;} #><#= c.ColumnName.ToString().ToLower()#><#if
(updateI!=columnCount){#>,<#}#>
<# updateI++;}
#>
)VALUES(
<# updateI=1; foreach (ColumnInfo c in
host.Fieldlist)
{ if(c.IsIdentity) { updateI++;continue;} #>
$_<#= c.ColumnName.ToString().ToLower()#><#if
(updateI!=columnCount){#>,<#}#>
<# updateI++;}
#>
);
set $_<#=IdentityStr#>=last_insert_id();
END;
DROP procedure IF EXISTS <#= host.GetDALClass(host.TableName)
#>_Update;
CREATE PROCEDURE <#= host.GetDALClass(host.TableName)
#>_Update(
<#updateI=1; foreach (ColumnInfo c in
host.Fieldlist)
{ #>
<#if(c.TypeName==specStr){#>
$_<#= c.ColumnName.ToString().ToLower()#>
<#=c.TypeName#>
<#}else{#>
$_<#=
c.ColumnName.ToString().ToLower()#>
<#=c.TypeName#>(<#=c.Length#>)
<#}#>
<#if (updateI!=columnCount){#>,<#}#>
<# updateI++;}
#>)
BEGIN
UPDATE <#= host.GetDALClass(host.TableName) #> SET
<#updateI=1; foreach (ColumnInfo c in host.Fieldlist)
{
if(c.IsIdentity) { updateI++;continue;}#>
<#=
c.ColumnName.ToString().ToLower()#>=$_<#=
c.ColumnName.ToString().ToLower()#><#if
(updateI!=columnCount){#>,<#}#>
<#updateI++; } #>
WHERE
Id=$_Id ;
END;
DROP procedure IF EXISTS <#= host.GetDALClass(host.TableName)
#>_Delete;
CREATE PROCEDURE <#= host.GetDALClass(host.TableName)
#>_Delete(
$_Id int)
BEGIN
DELETE from <#=
host.GetDALClass(host.TableName) #>
WHERE Id=$_Id ;
END;
DROP procedure IF EXISTS <#= host.GetDALClass(host.TableName)
#>_GetModel;
CREATE PROCEDURE <#= host.GetDALClass(host.TableName)
#>_GetModel(
$_Id int)
BEGIN
SELECT
<#
updateI=1;foreach (ColumnInfo c in host.Fieldlist)
{ #>
<#= c.ColumnName.ToString().ToLower()#><#if
(updateI!=columnCount){#>,<#}#>
<#updateI++; } #>
FROM
<#= host.GetDALClass(host.TableName) #>
WHERE Id=$_Id ;
END;
DROP procedure IF EXISTS <#= host.GetDALClass(host.TableName)
#>_GetColumnInfo;
CREATE PROCEDURE <#= host.GetDALClass(host.TableName)
#>_GetColumnInfo(
$_Id int,
$_ColumnName varchar(20))
BEGIN
set @sqlStr=CONCAT(‘SELECT ‘,$_ColumnName , ‘ FROM <#=
host.GetDALClass(host.TableName) #> WHERE Id= ‘,$_Id);
PREPARE count_stmt
FROM @sqlStr;
EXECUTE count_stmt;
END;
DROP procedure IF EXISTS <#= host.GetDALClass(host.TableName)
#>_GetList;
CREATE PROCEDURE <#= host.GetDALClass(host.TableName)
#>_GetList()
BEGIN
SELECT
<# updateI=1;foreach
(ColumnInfo c in host.Fieldlist)
{ #>
<#=
c.ColumnName.ToString().ToLower()#><#if
(updateI!=columnCount){#>,<#}#>
<#updateI++; } #>
FROM
<#= host.GetDALClass(host.TableName) #>;
END;
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。