<%@ CodeTemplate Language="C#" TargetLanguage="T-SQL" Description="Generates a update stored procedure." %>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Import Namespace="SchemaExplorer" %>
<%@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema" Category="Context" Description="Table that the stored procedures should be based on." %>
CREATE PROCEDURE dbo.Update<%= SourceTable.Name%> <%--CREATE PROCEDURE dbo.UpdateMY_Test --%>
<% for (int i=0;i<SourceTable.Columns.Count;i++) {%>
<%= GetSqlParameterStatement(SourceTable.Columns[i])%><% if(i<SourceTable.Columns.Count-1){ %>,<% } %>
<% }%> <%--获得所有参数 --%>
as
UPDATE [<%= SourceTable.Name%>] SET
<% for(int i=0;i<SourceTable.NonPrimaryKeyColumns.Count;i++) {%> <%--查找非主键的列--%>
[<%= SourceTable.NonPrimaryKeyColumns[i].Name %>] = @<%= SourceTable.NonPrimaryKeyColumns[i].Name %><% if (i<SourceTable.NonPrimaryKeyColumns.Count-1) {%>,<% } %>
<% } %>
WHERE
<% for(int i=0;i<SourceTable.PrimaryKey.MemberColumns.Count;i++) { %>
<% if(i>0) {%>AND <%}%>
[<%= SourceTable.PrimaryKey.MemberColumns[i].Name %>] = @<%= SourceTable.PrimaryKey.MemberColumns[i].Name%>
<% } %>
<script runat="template">
// My methods here.
public string GetSqlParameterStatement(ColumnSchema column)
{
string param="@" +column.Name +" "+ column.NativeType;
switch(column.DataType)
{
case DbType.Decimal:
{
param+="(" +column.Precision+ ", " +column.Scale+ ")";
break;
}
default:
{
if(column.Size>0)
{
param+="(" +column.Size+ ")";
}
break;
}
}
return param;
}
</script>
--得到的结果
CREATE PROCEDURE dbo.UpdateMY_Test
@UserID int,
@UserName varchar(50),
@UserPass varchar(50)
as
UPDATE [MY_Test] SET
[UserName] = @UserName,
[UserPass] = @UserPass
WHERE
[UserID] = @UserID
select * from [MY_Test]