1
<%
2foreach (TableSchema table in SourceDatabase.Tables)
3
{
4
OutputSubTemplate(table);
5
}
6%>
7< runat="template">
8private CodeTemplate _mySubTemplate;
9
10[Browsable(false)]
11public CodeTemplate MySubTemplate
12{
13 get
14
{
15 if (_mySubTemplate == null)
16 {
17 CodeTemplateCompiler compiler = new CodeTemplateCompiler(this.CodeTemplateInfo.DirectoryName + "MySubTemplate.cst");
18 compiler.Compile();
19 if (compiler.Errors.Count == 0)
20 {
21 _mySubTemplate = compiler.CreateInstance();
22
}
23 else
24 {
25 for (int i = 0; i < compiler.Errors.Count; i++)
26 {
27 Response.WriteLine(compiler.Errors[ i].ToString());
28 }
29 }
30 }
31 return _mySubTemplate;
32 }
33}
34
35public void OutputSubTemplate(TableSchema table)
36{
37 MySubTemplate.SetProperty("SourceTable", table);
38 MySubTemplate.SetProperty("IncludeDrop", false);
39 MySubTemplate.SetProperty("InsertPrefix", "Insert");
40 MySubTemplate.Render(Response);
41}
42</>
FAQ中给出的例子为生成一个数据库中所有表的更新Update存储过程<% 2
foreach (TableSchema table in SourceDatabase.Tables) 3
{ 4
OutputSubTemplate(table); 5
} 6
%>7
< runat="template"> 8
private CodeTemplate _mySubTemplate;9
10
[Browsable(false)]11
public CodeTemplate MySubTemplate 12
{ 13
get 14
{ 15
if (_mySubTemplate == null) 16
{ 17
CodeTemplateCompiler compiler = new CodeTemplateCompiler(this.CodeTemplateInfo.DirectoryName + "MySubTemplate.cst"); 18
compiler.Compile(); 19
if (compiler.Errors.Count == 0) 20
{ 21
_mySubTemplate = compiler.CreateInstance(); 22
} 23
else 24
{ 25
for (int i = 0; i < compiler.Errors.Count; i++) 26
{27
Response.WriteLine(compiler.Errors[ i].ToString()); 28
} 29
} 30
} 31
return _mySubTemplate; 32
} 33
}34
35
public void OutputSubTemplate(TableSchema table) 36
{ 37
MySubTemplate.SetProperty("SourceTable", table); 38
MySubTemplate.SetProperty("IncludeDrop", false); 39
MySubTemplate.SetProperty("InsertPrefix", "Insert"); 40
MySubTemplate.Render(Response); 41
} 42
</>SubTemplatesExample.cst文件源代码
1<%@ CodeTemplate Language="C#" TargetLanguage="T-SQL"
2 Deion="Generates a update stored procedure." %>
3
4<%@ Property Name="SourceDatabase" Type="SchemaExplorer.DatabaseSchema"
5 Category="Context"
6 Deion="Database" %>
7
8<%@ Assembly Name="SchemaExplorer" %>
9
10<%@ Import Namespace="SchemaExplorer" %>
11
12<%
13foreach (TableSchema table in SourceDatabase.Tables)
14{
15 OutputSubTemplate(table);
16}
17%>
18
19< runat="template">
20private CodeTemplate _mySubTemplate;
21
22
23
24
25[Browsable(false)]
26public CodeTemplate MySubTemplate
27{
28 get
29 {
30 if (_mySubTemplate == null)
31 {
32 CodeTemplateCompiler compiler = new CodeTemplateCompiler(this.CodeTemplateInfo.DirectoryName + "MySubTemplate.cst");
33 compiler.Compile();
34
35 if (compiler.Errors.Count == 0)
36 {
37 _mySubTemplate = compiler.CreateInstance();
38 }
39 else
40 {
41 for (int i = 0; i < compiler.Errors.Count; i++)
42 {
43 Response.WriteLine(compiler.Errors[ i].ToString());
44 }
45 }
46 }
47
48 return _mySubTemplate;
49 }
50}
51
52public void OutputSubTemplate(TableSchema table)
53{
54 MySubTemplate.SetProperty("SourceTable", table);
55 MySubTemplate.SetProperty("IncludeDrop", false);
56 MySubTemplate.SetProperty("InsertPrefix", "Insert");
57
58 MySubTemplate.Render(Response);
59}
60</>
MySubTemplate.cst文件源代码<%@ CodeTemplate Language="C#" TargetLanguage="T-SQL"2
Deion="Generates a update stored procedure." %>3
4
<%@ Property Name="SourceDatabase" Type="SchemaExplorer.DatabaseSchema"5
Category="Context"6
Deion="Database" %>7
8
<%@ Assembly Name="SchemaExplorer" %>9
10
<%@ Import Namespace="SchemaExplorer" %>11
12
<% 13
foreach (TableSchema table in SourceDatabase.Tables) 14
{ 15
OutputSubTemplate(table); 16
} 17
%> 18
19
< runat="template"> 20
private CodeTemplate _mySubTemplate; 21
22
23
24
25
[Browsable(false)]26
public CodeTemplate MySubTemplate 27
{ 28
get 29
{ 30
if (_mySubTemplate == null) 31
{ 32
CodeTemplateCompiler compiler = new CodeTemplateCompiler(this.CodeTemplateInfo.DirectoryName + "MySubTemplate.cst"); 33
compiler.Compile(); 34
35
if (compiler.Errors.Count == 0) 36
{ 37
_mySubTemplate = compiler.CreateInstance(); 38
} 39
else 40
{ 41
for (int i = 0; i < compiler.Errors.Count; i++) 42
{43
Response.WriteLine(compiler.Errors[ i].ToString()); 44
} 45
} 46
} 47
48
return _mySubTemplate; 49
} 50
} 51
52
public void OutputSubTemplate(TableSchema table) 53
{ 54
MySubTemplate.SetProperty("SourceTable", table); 55
MySubTemplate.SetProperty("IncludeDrop", false); 56
MySubTemplate.SetProperty("InsertPrefix", "Insert"); 57
58
MySubTemplate.Render(Response); 59
} 60
</> 1<%@ CodeTemplate Language="C#" TargetLanguage="T-SQL"
2 Deion="Generates a update stored procedure." %>
3
4<%@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema"
5 Category="Context"
6 Deion="Table that the stored procedures should be based on." %>
7
8<%@ Assembly Name="SchemaExplorer" %>
9
10<%@ Import Namespace="SchemaExplorer" %>
11
12
13< runat="template">
14public string GetSqlParameterStatement(ColumnSchema column)
15{
16 string param = "@" + column.Name + " " + column.NativeType;
17
18 switch (column.DataType)
19 {
20 case DbType.Decimal:
21 {
22 param += "(" + column.Precision + ", " + column.Scale + ")";
23 break;
24 }
25 default:
26 {
27 if (column.Size > 0)
28 {
29 param += "(" + column.Size + ")";
30 }
31 break;
32 }
33 }
34
35 return param;
36}
37</>
38
39-----------------------------------------------------------------
40-- Date Created: <%= DateTime.Now.ToLongDateString() %>
41-- Created By: Generated by CodeSmith
42-----------------------------------------------------------------
43
44CREATE PROCEDURE dbo.Update<%= SourceTable.Name %>
45 <% for (int i = 0; i < SourceTable.Columns.Count; i++) { %>
46 <%= GetSqlParameterStatement(SourceTable.Columns[i]) %><% if (i < SourceTable.Columns.Count - 1) { %>,<% } %>
47 <% } %>
48AS
49
50UPDATE [<%= SourceTable.Name %>] SET
51 <% for (int i = 0; i < SourceTable.NonPrimaryKeyColumns.Count; i++) { %>
52 [<%= SourceTable.NonPrimaryKeyColumns[i].Name %>] = @<%= SourceTable.NonPrimaryKeyColumns[i].Name %><% if (i < SourceTable.NonPrimaryKeyColumns.Count - 1) { %>,<% } %>
53 <% } %>
54WHERE
55 <% for (int i = 0; i < SourceTable.PrimaryKey.MemberColumns.Count; i++) { %>
56 <% if (i > 0) { %>AND <% } %>
57 [<%= SourceTable.PrimaryKey.MemberColumns[i].Name %>] = @<%= SourceTable.PrimaryKey.MemberColumns[i].Name %>
58 <% } %>
<%@ CodeTemplate Language="C#" TargetLanguage="T-SQL"2
Deion="Generates a update stored procedure." %>3
4
<%@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema"5
Category="Context"6
Deion="Table that the stored procedures should be based on." %>7
8
<%@ Assembly Name="SchemaExplorer" %>9
10
<%@ Import Namespace="SchemaExplorer" %>11
12
13
< runat="template">14
public string GetSqlParameterStatement(ColumnSchema column)15
{16
string param = "@" + column.Name + " " + column.NativeType;17
18
switch (column.DataType)19
{20
case DbType.Decimal:21
{22
param += "(" + column.Precision + ", " + column.Scale + ")";23
break;24
}25
default:26
{27
if (column.Size > 0)28
{29
param += "(" + column.Size + ")";30
}31
break;32
}33
}34
35
return param;36
}37
</>38
39
-----------------------------------------------------------------40
-- Date Created: <%= DateTime.Now.ToLongDateString() %>41
-- Created By: Generated by CodeSmith42
-----------------------------------------------------------------43
44
CREATE PROCEDURE dbo.Update<%= SourceTable.Name %>45
<% for (int i = 0; i < SourceTable.Columns.Count; i++) { %>46
<%= GetSqlParameterStatement(SourceTable.Columns[i]) %><% if (i < SourceTable.Columns.Count - 1) { %>,<% } %>47
<% } %>48
AS49
50
UPDATE [<%= SourceTable.Name %>] SET51
<% for (int i = 0; i < SourceTable.NonPrimaryKeyColumns.Count; i++) { %>52
[<%= SourceTable.NonPrimaryKeyColumns[i].Name %>] = @<%= SourceTable.NonPrimaryKeyColumns[i].Name %><% if (i < SourceTable.NonPrimaryKeyColumns.Count - 1) { %>,<% } %>53
<% } %>54
WHERE55
<% for (int i = 0; i < SourceTable.PrimaryKey.MemberColumns.Count; i++) { %>56
<% if (i > 0) { %>AND <% } %>57
[<%= SourceTable.PrimaryKey.MemberColumns[i].Name %>] = @<%= SourceTable.PrimaryKey.MemberColumns[i].Name %>58
<% } %>
