代碼
<%@ CodeTemplate Language="C#" Inherits="CodeSmith.BaseTemplates.SqlCodeTemplate" TargetLanguage="T-SQL" Description="編輯用的存儲過程，包括添加、修改和刪除" Debug="False" ResponseEncoding="UTF-8"%>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Assembly Name="CodeSmith.BaseTemplates" %>
<%@ Assembly Name="System.Data" %>
<%@ Import Namespace="SchemaExplorer" %>
<%@ Import Namespace="System.Data" %>
<%@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema" Category="數據" Description="要進行操作的表" %>
<%@ Property Name="IncludeDrop" Type="System.Boolean" Default="False" Category="選項" Description="是否需要先Drop掉以前的存儲過程" %>
<%@ Property Name="IncludeInsert" Type="System.Boolean" Default="True" Category="選項" Description="插入數據" %>
<%@ Property Name="IncludeUpdate" Type="System.Boolean" Default="True" Category="選項" Description="更新數據" %>
<%
if(false)
{
%>
<% for (int i = 0; i < SourceTable.Columns.Count; i++) { %>
<%= GetSqlParameterStatement(SourceTable.Columns[i]) %> <% if ((i < SourceTable.Columns.Count - 1)) { %>,<% } %> <% if(SourceTable.Columns[i].Description.Length > 0) { %>--<%= SourceTable.Columns[i].Description %><% } %>
<%
}
%>
Update <%= GetTableOwner() %>[<%= SourceTable.Name %>] Set
<% for (int i = 0; i < SourceTable.Columns.Count; i++) { %>
[<%= SourceTable.Columns[i].Name %>] = @<%= SourceTable.Columns[i].Name %> <% if (i < SourceTable.Columns.Count - 1) { %>,<% } %> <% if(SourceTable.Columns[i].Description.Length > 0) { %>--<%= SourceTable.Columns[i].Description %><% } %>
<% } %>
Insert Into <%= GetTableOwner() %>[<%= SourceTable.Name %>]
(
<% for (int i = 0; i < SourceTable.Columns.Count; i++) { %>
[<%= SourceTable.Columns[i].Name %>]<% if (i < SourceTable.Columns.Count - 1) { %>,<% } %> <% if(SourceTable.Columns[i].Description.Length > 0) { %>--<%= SourceTable.Columns[i].Description %><% } %>
<% } %>
)
Values
(
<% for (int i = 0; i < SourceTable.Columns.Count; i++) { %>
@<%= SourceTable.Columns[i].Name %><% if (i < SourceTable.Columns.Count - 1) { %>,<% } %> <% if(SourceTable.Columns[i].Description.Length > 0) { %>--<%= SourceTable.Columns[i].Description %><% } %>
<% } %>
)
<%
return;
}
%>
<% if (SourceTable.PrimaryKeys == null) throw new ApplicationException("該表缺少主鍵。"); %>
<% if (IncludeDrop) { %>
<% if (IncludeInsert) { %>
/****** Object: Stored Procedure <%= GetTableOwner() %><%= SourceTable.Name %>_Insert Script Date: ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[<%= SourceTable.Name %>_Insert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure <%= GetTableOwner() %>[<%= SourceTable.Name %>_Insert]
GO
<% } %>
<% if (IncludeUpdate) { %>
/****** Object: Stored Procedure <%= GetTableOwner() %><%= SourceTable.Name %>_Update Script Date: ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[<%= SourceTable.Name %>_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure <%= GetTableOwner() %>[<%= SourceTable.Name %>_Update]
GO
<% } %>
<%}%>
<% if (IncludeInsert) { %>
/*
******************************************************************************************
過程名稱：<%= SourceTable.Name %>_Insert
設計時間：<%= System.DateTime.Now.ToString("yyyy年MM月dd日 hh:mm:ss") %>
******************************************************************************************
<% if (SourceTable.PrimaryKey.MemberColumns.Count == 1 && (SourceTable.PrimaryKey.MemberColumns[0].DataType == DbType.Guid || SourceTable.PrimaryKey.MemberColumns[0].DataType == DbType.Int16 || SourceTable.PrimaryKey.MemberColumns[0].DataType == DbType.Int32 || SourceTable.PrimaryKey.MemberColumns[0].DataType == DbType.Int64)) {
ColumnSchema primaryKey = SourceTable.PrimaryKey.MemberColumns[0];
%>
Create Procedure <%= GetTableOwner() %>[<%= SourceTable.Name %>_Insert]
<%= GetSqlParameterStatement(primaryKey, true) %>,
<% for (int i = 0; i < SourceTable.NonPrimaryKeyColumns.Count; i++) { %>
<%= GetSqlParameterStatement(SourceTable.NonPrimaryKeyColumns[i]) %><%if (i < SourceTable.NonPrimaryKeyColumns.Count - 1){%>,<%}%> <% if(SourceTable.NonPrimaryKeyColumns[i].Description.Length > 0) { %>--<%= SourceTable.NonPrimaryKeyColumns[i].Description %><% } %>
<% } %>
AS
<% if (primaryKey.DataType == DbType.Guid) { %>
Set @<%= primaryKey.Name %> = NEWID()
<% } %>
Insert Into <%= GetTableOwner() %>[<%= SourceTable.Name %>]
(
<% if (primaryKey.DataType == DbType.Guid) { %>
[<%= primaryKey.Name %>],
<% } %>
<% for (int i = 0; i < SourceTable.NonPrimaryKeyColumns.Count; i++) { %>
[<%= SourceTable.NonPrimaryKeyColumns[i].Name %>]<% if (i < SourceTable.NonPrimaryKeyColumns.Count - 1) { %>,<% } %>
<% } %>
)
Values
(
<% if (primaryKey.DataType == DbType.Guid) { %>
@<%= primaryKey.Name %>,
<% } %>
<% for (int i = 0; i < SourceTable.NonPrimaryKeyColumns.Count; i++) { %>
@<%= SourceTable.NonPrimaryKeyColumns[i].Name %><% if (i < SourceTable.NonPrimaryKeyColumns.Count - 1) { %>,<% } %>
<% } %>
)
<% if (primaryKey.DataType == DbType.Int16 || primaryKey.DataType == DbType.Int32 || primaryKey.DataType == DbType.Int64) { %>
Set @<%= primaryKey.Name %> = @@IDENTITY
<% } %>
Return @@IDENTITY
<% } else { %>
Create Procedure <%= GetTableOwner() %>[Insert_<%= SourceTable.Name %>]
<% for (int i = 0; i < SourceTable.Columns.Count; i++) { %>
<%= GetSqlParameterStatement(SourceTable.Columns[i]) %><% if (i < SourceTable.Columns.Count - 1) { %>,<% } %>
<% } %>
AS
Insert Into <%= GetTableOwner() %>[<%= SourceTable.Name %>]
(
<% for (int i = 0; i < SourceTable.Columns.Count; i++) { %>
[<%= SourceTable.Columns[i].Name %>]<% if (i < SourceTable.Columns.Count - 1) { %>,<% } %>
<% } %>
)
Values
(
<% for (int i = 0; i < SourceTable.Columns.Count; i++) { %>
@<%= SourceTable.Columns[i].Name %><% if (i < SourceTable.Columns.Count - 1) { %>,<% } %>
<% } %>
)
Return @@IDENTITY
<% } %>
GO
<% } %>
<% if (IncludeUpdate) { %>
/*
******************************************************************************************
過程名稱：<%= SourceTable.Name %>_Update
設計時間：<%= System.DateTime.Now.ToString("yyyy年MM月dd日 hh:mm:ss") %>
******************************************************************************************
Create Procedure <%= GetTableOwner() %>[<%= SourceTable.Name %>_Update]
<% for (int i = 0; i < SourceTable.Columns.Count; i++) { %>
<%= GetSqlParameterStatement(SourceTable.Columns[i]) %><% if (i < SourceTable.Columns.Count - 1) { %>,<% } %> <% if (SourceTable.Columns[i].Description.Length > 0) { %>-- <%= SourceTable.Columns[i].Description %><% } %>
<% } %>
AS
Update <%= GetTableOwner() %>[<%= 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 %>
<% } %>
Return @@ROWCOUNT
GO
<% } %>
<script runat="template">
// My methods here.
public string GetTableOwner()
{
return GetTableOwner(true);
}
public string GetTableOwner(bool includeDot)
{
if (SourceTable.Owner.Length > 0)
{
return "[" + SourceTable.Owner + "].";
}
else
{
return "";
}
}
</script>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Assembly Name="CodeSmith.BaseTemplates" %>
<%@ Assembly Name="System.Data" %>
<%@ Import Namespace="SchemaExplorer" %>
<%@ Import Namespace="System.Data" %>
<%@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema" Category="數據" Description="要進行操作的表" %>
<%@ Property Name="IncludeDrop" Type="System.Boolean" Default="False" Category="選項" Description="是否需要先Drop掉以前的存儲過程" %>
<%@ Property Name="IncludeInsert" Type="System.Boolean" Default="True" Category="選項" Description="插入數據" %>
<%@ Property Name="IncludeUpdate" Type="System.Boolean" Default="True" Category="選項" Description="更新數據" %>
<%
if(false)
{
%>
<% for (int i = 0; i < SourceTable.Columns.Count; i++) { %>
<%= GetSqlParameterStatement(SourceTable.Columns[i]) %> <% if ((i < SourceTable.Columns.Count - 1)) { %>,<% } %> <% if(SourceTable.Columns[i].Description.Length > 0) { %>--<%= SourceTable.Columns[i].Description %><% } %>
<%
}
%>
Update <%= GetTableOwner() %>[<%= SourceTable.Name %>] Set
<% for (int i = 0; i < SourceTable.Columns.Count; i++) { %>
[<%= SourceTable.Columns[i].Name %>] = @<%= SourceTable.Columns[i].Name %> <% if (i < SourceTable.Columns.Count - 1) { %>,<% } %> <% if(SourceTable.Columns[i].Description.Length > 0) { %>--<%= SourceTable.Columns[i].Description %><% } %>
<% } %>
Insert Into <%= GetTableOwner() %>[<%= SourceTable.Name %>]
(
<% for (int i = 0; i < SourceTable.Columns.Count; i++) { %>
[<%= SourceTable.Columns[i].Name %>]<% if (i < SourceTable.Columns.Count - 1) { %>,<% } %> <% if(SourceTable.Columns[i].Description.Length > 0) { %>--<%= SourceTable.Columns[i].Description %><% } %>
<% } %>
)
Values
(
<% for (int i = 0; i < SourceTable.Columns.Count; i++) { %>
@<%= SourceTable.Columns[i].Name %><% if (i < SourceTable.Columns.Count - 1) { %>,<% } %> <% if(SourceTable.Columns[i].Description.Length > 0) { %>--<%= SourceTable.Columns[i].Description %><% } %>
<% } %>
)
<%
return;
}
%>
<% if (SourceTable.PrimaryKeys == null) throw new ApplicationException("該表缺少主鍵。"); %>
<% if (IncludeDrop) { %>
<% if (IncludeInsert) { %>
/****** Object: Stored Procedure <%= GetTableOwner() %><%= SourceTable.Name %>_Insert Script Date: ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[<%= SourceTable.Name %>_Insert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure <%= GetTableOwner() %>[<%= SourceTable.Name %>_Insert]
GO
<% } %>
<% if (IncludeUpdate) { %>
/****** Object: Stored Procedure <%= GetTableOwner() %><%= SourceTable.Name %>_Update Script Date: ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[<%= SourceTable.Name %>_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure <%= GetTableOwner() %>[<%= SourceTable.Name %>_Update]
GO
<% } %>
<%}%>
<% if (IncludeInsert) { %>
/*
******************************************************************************************
過程名稱：<%= SourceTable.Name %>_Insert
設計時間：<%= System.DateTime.Now.ToString("yyyy年MM月dd日 hh:mm:ss") %>
******************************************************************************************
<% if (SourceTable.PrimaryKey.MemberColumns.Count == 1 && (SourceTable.PrimaryKey.MemberColumns[0].DataType == DbType.Guid || SourceTable.PrimaryKey.MemberColumns[0].DataType == DbType.Int16 || SourceTable.PrimaryKey.MemberColumns[0].DataType == DbType.Int32 || SourceTable.PrimaryKey.MemberColumns[0].DataType == DbType.Int64)) {
ColumnSchema primaryKey = SourceTable.PrimaryKey.MemberColumns[0];
%>
Create Procedure <%= GetTableOwner() %>[<%= SourceTable.Name %>_Insert]
<%= GetSqlParameterStatement(primaryKey, true) %>,
<% for (int i = 0; i < SourceTable.NonPrimaryKeyColumns.Count; i++) { %>
<%= GetSqlParameterStatement(SourceTable.NonPrimaryKeyColumns[i]) %><%if (i < SourceTable.NonPrimaryKeyColumns.Count - 1){%>,<%}%> <% if(SourceTable.NonPrimaryKeyColumns[i].Description.Length > 0) { %>--<%= SourceTable.NonPrimaryKeyColumns[i].Description %><% } %>
<% } %>
AS
<% if (primaryKey.DataType == DbType.Guid) { %>
Set @<%= primaryKey.Name %> = NEWID()
<% } %>
Insert Into <%= GetTableOwner() %>[<%= SourceTable.Name %>]
(
<% if (primaryKey.DataType == DbType.Guid) { %>
[<%= primaryKey.Name %>],
<% } %>
<% for (int i = 0; i < SourceTable.NonPrimaryKeyColumns.Count; i++) { %>
[<%= SourceTable.NonPrimaryKeyColumns[i].Name %>]<% if (i < SourceTable.NonPrimaryKeyColumns.Count - 1) { %>,<% } %>
<% } %>
)
Values
(
<% if (primaryKey.DataType == DbType.Guid) { %>
@<%= primaryKey.Name %>,
<% } %>
<% for (int i = 0; i < SourceTable.NonPrimaryKeyColumns.Count; i++) { %>
@<%= SourceTable.NonPrimaryKeyColumns[i].Name %><% if (i < SourceTable.NonPrimaryKeyColumns.Count - 1) { %>,<% } %>
<% } %>
)
<% if (primaryKey.DataType == DbType.Int16 || primaryKey.DataType == DbType.Int32 || primaryKey.DataType == DbType.Int64) { %>
Set @<%= primaryKey.Name %> = @@IDENTITY
<% } %>
Return @@IDENTITY
<% } else { %>
Create Procedure <%= GetTableOwner() %>[Insert_<%= SourceTable.Name %>]
<% for (int i = 0; i < SourceTable.Columns.Count; i++) { %>
<%= GetSqlParameterStatement(SourceTable.Columns[i]) %><% if (i < SourceTable.Columns.Count - 1) { %>,<% } %>
<% } %>
AS
Insert Into <%= GetTableOwner() %>[<%= SourceTable.Name %>]
(
<% for (int i = 0; i < SourceTable.Columns.Count; i++) { %>
[<%= SourceTable.Columns[i].Name %>]<% if (i < SourceTable.Columns.Count - 1) { %>,<% } %>
<% } %>
)
Values
(
<% for (int i = 0; i < SourceTable.Columns.Count; i++) { %>
@<%= SourceTable.Columns[i].Name %><% if (i < SourceTable.Columns.Count - 1) { %>,<% } %>
<% } %>
)
Return @@IDENTITY
<% } %>
GO
<% } %>
<% if (IncludeUpdate) { %>
/*
******************************************************************************************
過程名稱：<%= SourceTable.Name %>_Update
設計時間：<%= System.DateTime.Now.ToString("yyyy年MM月dd日 hh:mm:ss") %>
******************************************************************************************
Create Procedure <%= GetTableOwner() %>[<%= SourceTable.Name %>_Update]
<% for (int i = 0; i < SourceTable.Columns.Count; i++) { %>
<%= GetSqlParameterStatement(SourceTable.Columns[i]) %><% if (i < SourceTable.Columns.Count - 1) { %>,<% } %> <% if (SourceTable.Columns[i].Description.Length > 0) { %>-- <%= SourceTable.Columns[i].Description %><% } %>
<% } %>
AS
Update <%= GetTableOwner() %>[<%= 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 %>
<% } %>
Return @@ROWCOUNT
GO
<% } %>
<script runat="template">
// My methods here.
public string GetTableOwner()
{
return GetTableOwner(true);
}
public string GetTableOwner(bool includeDot)
{
if (SourceTable.Owner.Length > 0)
{
return "[" + SourceTable.Owner + "].";
}
else
{
return "";
}
}
</script>
浙公網安備 33010602011771號