最近看了一下CodeSmith发现破玩意儿功能还真的强大的,尤其是自定义模板的功能,以后就不用重复的去写DAL层的那些代码了,怎么写也写不出什么新意,而且还很枯燥。
安装3.2版后发现里面有个ScriptTableData.cst模板,可以将SQL Server表中的数据导出到一个文件以Insert Table(col1,col2...coln)values(val1,val2...,val3),但是这个模板对Oracle数据库无效,因为PL-SQL和T-SQL语法毕竟有很多不同的。于是决定自己写一个类似的适用于Oracle的模板,其实基本和自带的那个模板差不多,只是有些语法有些不同而已。
CodeSmith默认没有Oracle的提供程序的,需要在CodeSmith的论坛下载之,网址是
http://community.codesmithtools.com/files/folders/schemaproviders/entry8631.aspx,不过下载需要注册的,如果你嫌注册太麻烦,可以发邮件给我,我发给你。有了提供程序就可以按照上面的说明文档配置,成功后就可以用CodeSmith连接到Oracle数据中的表,连接成功后进行一下的操作:
1、新建一个空模板。
2、在模板的开始加入下面的声明:
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
定义属性及引入命名空间
1![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
<%
@ CodeTemplate Language="C#" TargetLanguage="PL-SQL" Description="导出Oracle数据库表中的数据" %>
2![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
<%
@ Assembly Name="SchemaExplorer" %>
3![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
<%
@ Import Namespace="SchemaExplorer" %>
4![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
<%
@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema" Category="Context" Description="" %>
5![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
6
--Created <%=DateTime.Now %>
3、导出表数据的主要语句:
4、其他相关自定义函数
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
自定义函数
1
<script runat="template">
2
private DataTable _sourceTableData;
3
private string _allColumns;
4
private string _allData;
5![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
6![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
SourceTableData#region SourceTableData
7
private DataTable SourceTableData
8![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
{
9
get
10![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
11
if (_sourceTableData == null)
12![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
13
_sourceTableData = SourceTable.GetTableData();
14
}
15
16
return _sourceTableData;
17
}
18
}
19
#endregion
20![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
21![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
AllColumns#region AllColumns
22
private string AllColumns
23![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
{
24
get
25![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
26
if(_allColumns ==null)
27![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
28
string strCols = "";
29
for(int i=0;i<SourceTable.Columns.Count-1;i++)
30![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
31
strCols += SourceTable.Columns[i].Name + ",";
32
}
33
strCols+=SourceTable.Columns[SourceTable.Columns.Count-1].Name;
34
_allColumns = strCols;
35
}
36
return _allColumns;
37
38
}
39
}
40
#endregion
41![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
42![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
AllData#region AllData
43
private string AllDate(int intRow)
44![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
{
45
string strVal = "";
46
int intCol=0;
47
for(;intCol<SourceTable.Columns.Count-1;intCol++)
48![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
49
strVal += Formated(SourceTableData.Rows[intRow][intCol],intCol,intRow) + ",";
50
}
51
strVal += Formated(SourceTableData.Rows[intRow][intCol],intCol,intRow);
52
return strVal;
53
}
54
#endregion
55![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
56![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
FormatData#region FormatData
57
private string Formated(object obj,int intCol,int intRow)
58![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
{
59
string strVal = "";
60
switch(SourceTable.Columns[intCol].DataType)
61![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
62
case DbType.String:
63
strVal = "'" + obj + "'";
64
break;
65
case DbType.Int16:
66
case DbType.Int32:
67
case DbType.Int64:
68
strVal = obj.ToString();
69
break;
70
case DbType.DateTime:
71
strVal = "to_date('" + obj + "','yyyy-mm-dd HH24:MI:SS')";
72
break;
73
default:
74
strVal = obj.ToString();
75
break;
76
}
77
if (SourceTableData.Rows[intRow][intCol]==DBNull.Value)
78
strVal = "null";
79
return strVal;
80
}
81
#endregion
82![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
83
public string GetTableOwner()
84![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
{
85
return GetTableOwner(true);
86
}
87![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
88
public string GetTableOwner(bool includeDot)
89![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
{
90
if (SourceTable.Owner.Length > 0)
91![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
92
return SourceTable.Owner + ".";
93
}
94
else
95![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
96
return "";
97
}
98
}
99![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
100
</script>