SqlStoredProcedures.cst 生成存储过程
1
<%--
2Name:
3Author: RS_WUBIYU
4Description:
5--%>
6<%@ CodeTemplate Language="C#" ResponseEncoding="UTF-8" TargetLanguage="Text" Src="Helper.cs" Inherits="Helper" Debug="False" Description="Template description here." %>
7<%@ Property Name="DataBase" Type="SchemaExplorer.DatabaseSchema" Category="2.数据库对象"
8 Description="数据库对象" %>
9<%@ Property Name="AutoExecuteScript" Type="System.Boolean" Default="False" Category="1. 是否自动执行生成后的脚本"
10 Description="是否自动执行生成后的脚本" %>
11<%@ Assembly Name="System.Data" %>
12<%@ Assembly Name="SchemaExplorer" %>
13<%@ Assembly Name="CodeSmith.BaseTemplates" %>
14
15<%@ Import Namespace="System.Data" %>
16<%@ Import Namespace="SchemaExplorer" %>
17<%@ Import Namespace="CodeSmith.BaseTemplates" %>
18<%
19foreach(TableSchema Table in DataBase.Database.Tables)
20
{
21
%>
22SET ANSI_NULLS ON
23GO
24SET QUOTED_IDENTIFIER ON
25GO
26IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[<%= GetTableName(Table)%>_Create]') AND type in (N'P', N'PC'))
27BEGIN
28DROP PROCEDURE [dbo].[<%= GetTableName(Table)%>_Create]
29END
30GO
31-- =============================================
32-- Create date: <%= System.DateTime.Now.ToString()%>
33-- Description: 创建<%= GetClassName(Table)%>
34-- =============================================
35CREATE PROCEDURE [dbo].[<%= GetTableName(Table)%>_Create]
36
37<%
38foreach(ColumnSchema column in Table.Columns)
39
{
40%>
41@<%= column.Name%> <%= GetTypeAndSize(column)%> <%= GetMorePrimaryOutput(Table,column)%><%=GetComma(column,Table.Columns)%>
42<%
43
}
44%>
45
46AS
47
48INSERT INTO [<%= GetTableName(Table)%>](
49<%
50foreach(ColumnSchema column in Table.Columns)
51{%> <%= GetInsertParam(Table,column)%><%= GetMorePrimaryComma(Table,column,Table.Columns)%>
52<%}%>
53 )
54VALUES(
55<%
56foreach(ColumnSchema column in Table.Columns)
57{
58%> <%= GetInsertInOrOutParam(Table,column)%><%= GetMorePrimaryComma(Table,column,Table.Columns)%>
59<%
60}%> )
61<%
62 if(!IsMorePrimary(Table))
63 {%>
64 set @<%= Table.PrimaryKey.MemberColumns[0].Name%> = SCOPE_IDENTITY()
65 <%
66 }
67%>
68GO
69SET ANSI_NULLS ON
70GO
71SET QUOTED_IDENTIFIER ON
72GO
73IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[<%= GetTableName(Table)%>_Update]') AND type in (N'P', N'PC'))
74BEGIN
75DROP PROCEDURE [dbo].[<%= GetTableName(Table)%>_Update]
76END
77GO
78-- =============================================
79-- Create date: <%= System.DateTime.Now.ToString()%>
80-- Description: 更新<%= GetTableName(Table)%>
81-- =============================================
82CREATE PROCEDURE [dbo].[<%= GetTableName(Table)%>_Update]
83<%
84foreach(ColumnSchema column in Table.Columns)
85{
86%>
87@<%= column.Name%> <%= GetTypeAndSize(column)%><%=GetComma(column,Table.Columns)%>
88<%
89}
90%>
91AS
92
93UPDATE [<%= GetTableName(Table)%>] set
94<%
95 foreach(ColumnSchema column in Table.Columns)
96 {
97%>
98 <%= GetInsertParam(Table,column)%> <%= GetMorePrimaryEqual(Table,column)%> <%= GetInsertInOrOutParam(Table,column)%><%= GetMorePrimaryComma(Table,column,Table.Columns)%>
99<%}%>
100WHERE <%
101if(!IsMorePrimary(Table))
102{%><%= Table.PrimaryKey.MemberColumns[0].Name%> = @<%= Table.PrimaryKey.MemberColumns[0].Name%>
103<%
104}
105else
106{
107 foreach(ColumnSchema column in Table.PrimaryKey.MemberColumns)
108 {%>[<%= column.Name%>] = @<%= column.Name%> <%= GetAnd(column,Table.PrimaryKey.MemberColumns)%> <%
109 }
110}
111%>
112GO
113SET ANSI_NULLS ON
114GO
115SET QUOTED_IDENTIFIER ON
116GO
117IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[<%= GetTableName(Table)%>_Get<%= GetClassName(Table)%>]') AND type in (N'P', N'PC'))
118BEGIN
119DROP PROCEDURE [dbo].[<%= GetTableName(Table)%>_Get<%= GetClassName(Table)%>]
120END
121GO
122-- =============================================
123-- Create date: <%= System.DateTime.Now.ToString()%>
124-- Description: 根据主键得到一个<%= GetClassName(Table)%>实体
125-- =============================================
126CREATE PROCEDURE [dbo].[<%= GetTableName(Table)%>_Get<%= GetClassName(Table)%>]
127<%
128foreach(ColumnSchema column in Table.PrimaryKey.MemberColumns)
129{%>
130 @<%= column.Name%> <%= GetTypeAndSize(column)%><%=GetComma(column,Table.PrimaryKey.MemberColumns)%>
131<%}%>
132AS
133
134SELECT * FROM [<%= GetTableName(Table)%>] WHERE <%
135foreach(ColumnSchema column in Table.PrimaryKey.MemberColumns)
136{%>[<%= column.Name%>] = @<%=column.Name%> <%= GetAnd(column,Table.PrimaryKey.MemberColumns)%> <%
137}
138%>
139GO
140SET ANSI_NULLS ON
141GO
142SET QUOTED_IDENTIFIER ON
143GO
144IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[<%= GetTableName(Table)%>_Delete]') AND type in (N'P', N'PC'))
145BEGIN
146DROP PROCEDURE [dbo].[<%= GetTableName(Table)%>_Delete]
147END
148GO
149-- =============================================
150-- Create date: <%= System.DateTime.Now.ToString()%>
151-- Description: 删除<%= GetTableName(Table)%>
152--==============================================
153CREATE PROCEDURE [dbo].[<%= GetTableName(Table)%>_Delete]
154<%
155foreach(ColumnSchema column in Table.PrimaryKey.MemberColumns)
156{%>
157 @<%= column.Name%> <%= GetTypeAndSize(column)%><%=GetComma(column,Table.PrimaryKey.MemberColumns)%>
158<%}%>
159AS
160
161DELETE FROM [<%= GetTableName(Table)%>] WHERE <%
162foreach(ColumnSchema column in Table.PrimaryKey.MemberColumns)
163{%>[<%= column.Name%>] = @<%=column.Name%> <%= GetAnd(column,Table.PrimaryKey.MemberColumns)%> <%
164}
165%>
166IF @@ERROR != 0
167BEGIN
168--50000
169RAISERROR (N'删除失败', -- Message text.
170 11, -- Severity,
171 3) -- State
172END
173GO
174
<%}%>
175<script runat="template">
176// My methods here.
177
178protected override void OnPostRender(string result)
179{
180
181 if (this.AutoExecuteScript)
182 {
183 // execute the output on the same database as the source table.
184 // 执行上面生成的脚本。
185 CodeSmith.BaseTemplates.ScriptResult scriptResult = CodeSmith.BaseTemplates.ScriptUtility.ExecuteScript(this.DataBase.Database.ConnectionString, result,
186 new System.Data.SqlClient.SqlInfoMessageEventHandler(cn_InfoMessage));
187 Trace.Write(scriptResult.ToString());
188 }
189 base.OnPostRender(result);
190}
191
192public void StordReader(string result)
193{
194 if (this.AutoExecuteScript)
195 {
196 // execute the output on the same database as the source table.
197 // 执行上面生成的脚本。
198 CodeSmith.BaseTemplates.ScriptResult scriptResult = CodeSmith.BaseTemplates.ScriptUtility.ExecuteScript(this.DataBase.Database.ConnectionString, result,
199 new System.Data.SqlClient.SqlInfoMessageEventHandler(cn_InfoMessage));
200 Trace.Write(scriptResult.ToString());
201 }
202}
203
204private void cn_InfoMessage(object sender, System.Data.SqlClient.SqlInfoMessageEventArgs e)
205{
206 //输出异常信息。
207 Trace.WriteLine(e.Message);
208}
209
210
211
212
213
214</script>
<%-- 2
Name:3
Author: RS_WUBIYU4
Description: 5
--%>6
<%@ CodeTemplate Language="C#" ResponseEncoding="UTF-8" TargetLanguage="Text" Src="Helper.cs" Inherits="Helper" Debug="False" Description="Template description here." %>7
<%@ Property Name="DataBase" Type="SchemaExplorer.DatabaseSchema" Category="2.数据库对象"8
Description="数据库对象" %>9
<%@ Property Name="AutoExecuteScript" Type="System.Boolean" Default="False" Category="1. 是否自动执行生成后的脚本"10
Description="是否自动执行生成后的脚本" %>11
<%@ Assembly Name="System.Data" %>12
<%@ Assembly Name="SchemaExplorer" %>13
<%@ Assembly Name="CodeSmith.BaseTemplates" %>14
15
<%@ Import Namespace="System.Data" %>16
<%@ Import Namespace="SchemaExplorer" %>17
<%@ Import Namespace="CodeSmith.BaseTemplates" %>18
<% 19
foreach(TableSchema Table in DataBase.Database.Tables)20
{
21
%>22
SET ANSI_NULLS ON23
GO24
SET QUOTED_IDENTIFIER ON25
GO26
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[<%= GetTableName(Table)%>_Create]') AND type in (N'P', N'PC'))27
BEGIN28
DROP PROCEDURE [dbo].[<%= GetTableName(Table)%>_Create]29
END30
GO31
-- =============================================32
-- Create date: <%= System.DateTime.Now.ToString()%>33
-- Description: 创建<%= GetClassName(Table)%>34
-- =============================================35
CREATE PROCEDURE [dbo].[<%= GetTableName(Table)%>_Create]36
37
<% 38
foreach(ColumnSchema column in Table.Columns)39
{
40
%>41
@<%= column.Name%> <%= GetTypeAndSize(column)%> <%= GetMorePrimaryOutput(Table,column)%><%=GetComma(column,Table.Columns)%>42
<%43
}44
%>45
46
AS47
48
INSERT INTO [<%= GetTableName(Table)%>](49
<%50
foreach(ColumnSchema column in Table.Columns)51
{%> <%= GetInsertParam(Table,column)%><%= GetMorePrimaryComma(Table,column,Table.Columns)%>52
<%}%>53
)54
VALUES(55
<%56
foreach(ColumnSchema column in Table.Columns)57
{58
%> <%= GetInsertInOrOutParam(Table,column)%><%= GetMorePrimaryComma(Table,column,Table.Columns)%>59
<% 60
}%> )61
<%62
if(!IsMorePrimary(Table))63
{%>64
set @<%= Table.PrimaryKey.MemberColumns[0].Name%> = SCOPE_IDENTITY()65
<%66
}67
%>68
GO69
SET ANSI_NULLS ON70
GO71
SET QUOTED_IDENTIFIER ON72
GO73
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[<%= GetTableName(Table)%>_Update]') AND type in (N'P', N'PC'))74
BEGIN75
DROP PROCEDURE [dbo].[<%= GetTableName(Table)%>_Update]76
END77
GO78
-- =============================================79
-- Create date: <%= System.DateTime.Now.ToString()%>80
-- Description: 更新<%= GetTableName(Table)%>81
-- =============================================82
CREATE PROCEDURE [dbo].[<%= GetTableName(Table)%>_Update]83
<%84
foreach(ColumnSchema column in Table.Columns)85
{86
%>87
@<%= column.Name%> <%= GetTypeAndSize(column)%><%=GetComma(column,Table.Columns)%>88
<%89
}90
%>91
AS92
93
UPDATE [<%= GetTableName(Table)%>] set94
<%95
foreach(ColumnSchema column in Table.Columns)96
{97
%>98
<%= GetInsertParam(Table,column)%> <%= GetMorePrimaryEqual(Table,column)%> <%= GetInsertInOrOutParam(Table,column)%><%= GetMorePrimaryComma(Table,column,Table.Columns)%>99
<%}%>100
WHERE <%101
if(!IsMorePrimary(Table))102
{%><%= Table.PrimaryKey.MemberColumns[0].Name%> = @<%= Table.PrimaryKey.MemberColumns[0].Name%>103
<%104
}105
else106
{107
foreach(ColumnSchema column in Table.PrimaryKey.MemberColumns)108
{%>[<%= column.Name%>] = @<%= column.Name%> <%= GetAnd(column,Table.PrimaryKey.MemberColumns)%> <% 109
}110
}111
%>112
GO113
SET ANSI_NULLS ON114
GO115
SET QUOTED_IDENTIFIER ON116
GO117
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[<%= GetTableName(Table)%>_Get<%= GetClassName(Table)%>]') AND type in (N'P', N'PC'))118
BEGIN119
DROP PROCEDURE [dbo].[<%= GetTableName(Table)%>_Get<%= GetClassName(Table)%>]120
END121
GO122
-- =============================================123
-- Create date: <%= System.DateTime.Now.ToString()%>124
-- Description: 根据主键得到一个<%= GetClassName(Table)%>实体125
-- =============================================126
CREATE PROCEDURE [dbo].[<%= GetTableName(Table)%>_Get<%= GetClassName(Table)%>]127
<%128
foreach(ColumnSchema column in Table.PrimaryKey.MemberColumns)129
{%>130
@<%= column.Name%> <%= GetTypeAndSize(column)%><%=GetComma(column,Table.PrimaryKey.MemberColumns)%>131
<%}%>132
AS133
134
SELECT * FROM [<%= GetTableName(Table)%>] WHERE <%135
foreach(ColumnSchema column in Table.PrimaryKey.MemberColumns)136
{%>[<%= column.Name%>] = @<%=column.Name%> <%= GetAnd(column,Table.PrimaryKey.MemberColumns)%> <%137
}138
%>139
GO140
SET ANSI_NULLS ON141
GO142
SET QUOTED_IDENTIFIER ON143
GO144
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[<%= GetTableName(Table)%>_Delete]') AND type in (N'P', N'PC'))145
BEGIN146
DROP PROCEDURE [dbo].[<%= GetTableName(Table)%>_Delete]147
END148
GO149
-- =============================================150
-- Create date: <%= System.DateTime.Now.ToString()%>151
-- Description: 删除<%= GetTableName(Table)%>152
--==============================================153
CREATE PROCEDURE [dbo].[<%= GetTableName(Table)%>_Delete]154
<%155
foreach(ColumnSchema column in Table.PrimaryKey.MemberColumns)156
{%>157
@<%= column.Name%> <%= GetTypeAndSize(column)%><%=GetComma(column,Table.PrimaryKey.MemberColumns)%>158
<%}%>159
AS160
161
DELETE FROM [<%= GetTableName(Table)%>] WHERE <%162
foreach(ColumnSchema column in Table.PrimaryKey.MemberColumns)163
{%>[<%= column.Name%>] = @<%=column.Name%> <%= GetAnd(column,Table.PrimaryKey.MemberColumns)%> <%164
}165
%>166
IF @@ERROR != 0167
BEGIN168
--50000169
RAISERROR (N'删除失败', -- Message text.170
11, -- Severity,171
3) -- State172
END173
GO174
<%}%>175
<script runat="template">176
// My methods here.177
178
protected override void OnPostRender(string result) 179
{180
181
if (this.AutoExecuteScript)182
{183
// execute the output on the same database as the source table.184
// 执行上面生成的脚本。185
CodeSmith.BaseTemplates.ScriptResult scriptResult = CodeSmith.BaseTemplates.ScriptUtility.ExecuteScript(this.DataBase.Database.ConnectionString, result, 186
new System.Data.SqlClient.SqlInfoMessageEventHandler(cn_InfoMessage)); 187
Trace.Write(scriptResult.ToString());188
}189
base.OnPostRender(result);190
}191
192
public void StordReader(string result)193
{194
if (this.AutoExecuteScript)195
{196
// execute the output on the same database as the source table.197
// 执行上面生成的脚本。198
CodeSmith.BaseTemplates.ScriptResult scriptResult = CodeSmith.BaseTemplates.ScriptUtility.ExecuteScript(this.DataBase.Database.ConnectionString, result, 199
new System.Data.SqlClient.SqlInfoMessageEventHandler(cn_InfoMessage)); 200
Trace.Write(scriptResult.ToString());201
}202
}203
204
private void cn_InfoMessage(object sender, System.Data.SqlClient.SqlInfoMessageEventArgs e)205
{206
//输出异常信息。207
Trace.WriteLine(e.Message);208
}209
210
211
212
213
214
</script>