[CODE Smith]生成Update存储过程
2008-02-19 10:22 Jaypei 阅读(351) 评论(0) 编辑 收藏 举报
加载使用访问数据库的组件SchemaExplorer,并声明使用其命名空间
<%@ CodeTemplate Language="C#" TargetLanguage="T-SQL" Description="Generate 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." %>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Import Namespace="SchemaExplorer" %>
<%@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema" Category="Context" Description="Table that the stored procedures should be based on." %>
定义一个生成参数格式的函数GetSqlParameterStatement
public string GetSqlParameterStatement(ColumnSchema column)
{
string param = "@" + column.Name + " " +column.NativeType;
switch(column.DataType)
{
case DbType.Decimal:
{
param += "(" + column.Precision + "," + column.Scale + ")";
break;
}
case DbType.AnsiString:
case DbType.AnsiStringFixedLength:
case DbType.String:
case DbType.StringFixedLength:
{
param += "(" + (column.Size==-1 ? "MAX" : column.Size.ToString()) + ")";
break;
}
default:
{
break;
}
}
return param;
}
{
string param = "@" + column.Name + " " +column.NativeType;
switch(column.DataType)
{
case DbType.Decimal:
{
param += "(" + column.Precision + "," + column.Scale + ")";
break;
}
case DbType.AnsiString:
case DbType.AnsiStringFixedLength:
case DbType.String:
case DbType.StringFixedLength:
{
param += "(" + (column.Size==-1 ? "MAX" : column.Size.ToString()) + ")";
break;
}
default:
{
break;
}
}
return param;
}
编写存储过程
CREATE PROCEDURE dbo.Update<%= SourceTable.Name %>
<% for (int i=0; i<SourceTable.Columns.Count; i++) {%>
<%= GetSqlParameterStatement(SourceTable.Columns[i]) %> <% if (i<SourceTable.Columns.Count-1) {%>,<% } %>
<% }%>
AS
BEGIN
UPDATE [<%= SourceTable.Name %>] SET
<% for(int i=0; i<SourceTable.NonKeyColumns.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++) { %>
[<%= SourceTable.PrimaryKey.MemberColumns[i].Name %>] = <%= SourceTable.PrimaryKey.MemberColumns[i].Name %>
<% } %>
END
<% for (int i=0; i<SourceTable.Columns.Count; i++) {%>
<%= GetSqlParameterStatement(SourceTable.Columns[i]) %> <% if (i<SourceTable.Columns.Count-1) {%>,<% } %>
<% }%>
AS
BEGIN
UPDATE [<%= SourceTable.Name %>] SET
<% for(int i=0; i<SourceTable.NonKeyColumns.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++) { %>
[<%= SourceTable.PrimaryKey.MemberColumns[i].Name %>] = <%= SourceTable.PrimaryKey.MemberColumns[i].Name %>
<% } %>
END
参考TerryLee的《CodeSmith开发资料.pdf》