MSSQL存储过程生成模板-优快云博客

本文介绍了一个使用CodeSmith模板语言生成SQL存储过程的方法，包括插入、更新及获取数据等操作。通过C#代码模板实现了根据不同数据库表结构自动生成对应的T-SQL存储过程。

Insert:

<%@ 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="" %>
<script runat="template">
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>
-----------------------------------------------------------------
-- Date Created: <%= DateTime.Now.ToString() %>
-- Created By: Generated by CodeSmith
-----------------------------------------------------------------

CREATE PROCEDURE dbo.Insert<%= SourceTable.Name %>
<% for (int i = 0; i < SourceTable.NonPrimaryKeyColumns.Count; i++) { %>
<%= GetSqlParameterStatement(SourceTable.Columns[i]) %><% if(i < SourceTable.Columns.Count - 1) { %>,<% } %>
<% } %>
AS

insert [<%= SourceTable.Name %>]
(
<% for (int i = 0; i < SourceTable.NonPrimaryKeyColumns.Count; i++) { %>
<%= SourceTable.NonPrimaryKeyColumns[i].Name %><% if (i < SourceTable.NonPrimaryKeyColumns.Count - 1) { %>,<% } %>
<% } %>
)
values
(
<% for (int i = 0; i < SourceTable.NonPrimaryKeyColumns.Count; i++) { %>
@<%= SourceTable.NonPrimaryKeyColumns[i].Name %><% if (i < SourceTable.NonPrimaryKeyColumns.Count - 1) { %>,<% } %>
<% } %>
)

select @@identity

Update:

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

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 %>
<% } %>

Get:

CREATE PROCEDURE dbo.Get<%= SourceTable.Name %>
<% for (int i = 0; i < SourceTable.PrimaryKey.MemberColumns.Count; i++) { %>
<%= GetSqlParameterStatement(SourceTable.PrimaryKey.MemberColumns[i]) %><% if(i < SourceTable.Columns.Count - 1) { %>,<% } %>
<% } %>
AS

select
<% for (int i = 0; i < SourceTable.Columns.Count; i++) { %>
<%= SourceTable.Columns[i].Name %><% if (i < SourceTable.Columns.Count - 1) { %>,<% } %>
<% } %>
from [<%= SourceTable.Name %>]
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 %>
<% } %>

ListAllBYPage:

<%@ CodeTemplate Language="C#" TargetLanguage="T-SQL" Description="Generates a update stored procedure." %>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Assembly Name="CodeSmith.CustomProperties"%>
<%@ Import Namespace="SchemaExplorer" %>
<%@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema" Category="Context" Description="" %>
<%@ Property Name="ColumnList" Type="CodeSmith.CustomProperties.StringCollection" Category="Custom" Description=""%>
<script runat="template">
public string FindKey()
{
return SourceTable.PrimaryKey.MemberColumns[0].Name;
}
public string ColumnShow()
{
string re="";
for(int i=0;i<ColumnList.Count;i++)
{
re+=ColumnList[i];
if(i<ColumnList.Count-1)re+=",";
}
return re;
}
</script>

-----------------------------------------------------------------
-- Date Created: <%= DateTime.Now.ToString() %>
-- Created By: Generated by CodeSmith
-----------------------------------------------------------------

CREATE PROCEDURE List<%= SourceTable.Name %>
@PageSize int,
@PageNum int,
AS
  declare @BeginID bigint
  declare @EndID bigint
  declare @RecordCount bigint
  declare @PageCount int
  declare @RowCount int

set nocount on

select @RecordCount=count(*) from [<%= SourceTable.Name %>]
if(@RecordCount=0)
return 0

if ((@PageNum * @PageSize) > @RecordCount)
return (-1)

set @RowCount = @PageNum * @PageSize+1

set rowcount @RowCount
select @BeginID = <%=FindKey()%> from [<%= SourceTable.Name %>] order by <%=FindKey()%> desc

set @RowCount = (@PageNum+1) * @PageSize

set rowcount @RowCount
select @EndID = <%=FindKey()%> from [<%= SourceTable.Name %>] order by <%=FindKey()%> desc

set rowcount 0
set nocount off
select <%=ColumnShow()%> from [<%= SourceTable.Name %>] where <%=FindKey()%> between @EndId and @BeginId order by <%=FindKey()%> desc
GO

转载于:https://www.cnblogs.com/erik168/archive/2006/11/16/562248.html