CodeSmith下载与生成存储过程的一个模板
<%@ CodeTemplate Language="C#" TargetLanguage="T-SQL" Description="生成存储过程" %>
<%@ Property Name="table" Type="SchemaExplorer.TableSchema" Category="Context" Description="数据表模式" %>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Import Namespace="SchemaExplorer" %>
<script runat="template">
public string GetProParam(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: <%= DateTime.Now.ToString() %>
-- By:<%="秦迷"%>
----增加的存储过程----
Creste Procedure dbo.Insert<%=table.Name%>
<%for(int i=0;i<table.Columns.Count;i++){%>
<%= GetProParam(table.Columns[i])%><% if(i<table.Columns.Count-1){%>,<% } %>
<% } %>
As
Insert Into <%=table.Name%>(<%for(int i=0;i<table.NonPrimaryKeyColumns.Count;i++){%><%=table.NonPrimaryKeyColumns[i].Name%><%if(i<table.NonPrimaryKeyColumns.Count-1){%>,<%} } %>)
Values(<%for(int i=0;i<table.NonPrimaryKeyColumns.Count;i++){%>@<%=table.NonPrimaryKeyColumns[i].Name%><%if(i<table.NonPrimaryKeyColumns.Count-1){%>,<%}}%>)
----更新的存储过程----
Create Procedure dbo.Update<%= table.Name %>
<% for (int i = 0; i < table.Columns.Count; i++) { %>
<%= GetProParam(table.Columns[i]) %><% if (i < table.Columns.Count - 1) { %>,<% }} %>
As
Update <%= table.Name %> Set
<% for (int i = 0; i < table.NonPrimaryKeyColumns.Count; i++) { %>
<%= table.NonPrimaryKeyColumns[i].Name %> = @<%= table.NonPrimaryKeyColumns[i].Name %><% if (i < table.NonPrimaryKeyColumns.Count - 1) { %>,<% } %>
<% } %>
Where
<% for (int i = 0; i < table.PrimaryKey.MemberColumns.Count; i++) { %>
<% if (i > 0) { %>AND <% } %><%= table.PrimaryKey.MemberColumns[i].Name %>=@<%= table.PrimaryKey.MemberColumns[i].Name %>
<% } %>
