代码
<%
@ 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>