用Codesmith写的一个的从SQL Server向Oracle导数据的模板
CodeSmith从4.1版直接支持Oracle Schema Provider了,最近在做一个SQL Server向Oracle迁移的项目,于是写了一个模板来导数据,感觉比较方便,放出来让大家评评,有不足的地方,请高手之处以便改进。
Code
1<%@ CodeTemplate Language="C#" TargetLanguage="T-SQL" Description="Export sql" Debug="true" %>
2<%@ Assembly Name="SchemaExplorer" %>
3<%@ Import Namespace="SchemaExplorer" %>
4<%@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema" Category="Context" Description="" %>
5<%@ Property Name="SortBy" Type="String" Default="" Optional="True" Category="SQL" Description="" OnChanged="" Editor="" EditorBase="" Serializer="" %>
6<%@ Property Name="Filter" Type="String" Default="" Optional="True" Category="" Description="" OnChanged="" Editor="" EditorBase="" Serializer="" %>
7<%@ Property Name="IsOracle" Type="Boolean" Default="False" Optional="False" Category="" Description="Script target database is oracle" OnChanged="" Editor="" EditorBase="" Serializer="" %>
8
9--Created <%=DateTime.Now %>
10-- written by wilson.fu
11<%for(int intRow=0;intRow<SourceTableData.Rows.Count;intRow++){ %>
12insert into <%=GetTableOwner() %><%=SourceTable.Name %>(<%=AllColumns %>)
13values (<%=AllDate(intRow) %>)
14<%if(!IsOracle){%>
15go
16<%}else{%>
17;
18<%}%>
19<%}%>
1<%@ CodeTemplate Language="C#" TargetLanguage="T-SQL" Description="Export sql" Debug="true" %>
2<%@ Assembly Name="SchemaExplorer" %>
3<%@ Import Namespace="SchemaExplorer" %>
4<%@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema" Category="Context" Description="" %>
5<%@ Property Name="SortBy" Type="String" Default="" Optional="True" Category="SQL" Description="" OnChanged="" Editor="" EditorBase="" Serializer="" %>
6<%@ Property Name="Filter" Type="String" Default="" Optional="True" Category="" Description="" OnChanged="" Editor="" EditorBase="" Serializer="" %>
7<%@ Property Name="IsOracle" Type="Boolean" Default="False" Optional="False" Category="" Description="Script target database is oracle" OnChanged="" Editor="" EditorBase="" Serializer="" %>
8
9--Created <%=DateTime.Now %>
10-- written by wilson.fu
11<%for(int intRow=0;intRow<SourceTableData.Rows.Count;intRow++){ %>
12insert into <%=GetTableOwner() %><%=SourceTable.Name %>(<%=AllColumns %>)
13values (<%=AllDate(intRow) %>)
14<%if(!IsOracle){%>
15go
16<%}else{%>
17;
18<%}%>
19<%}%>
Code
1<script runat="template">
2private DataTable _sourceTableData;
3private string _allColumns;
4private string _allData;
5
6SourceTableData#region SourceTableData
7private DataTable SourceTableData
8{
9 get
10 {
11 if (_sourceTableData == null)
12 {
13 _sourceTableData = SourceTable.GetTableData();
14 }
15
16 return _sourceTableData;
17 }
18 }
19 #endregion
20
21AllColumns#region AllColumns
22 private string AllColumns
23 {
24 get
25 {
26 if(_allColumns ==null)
27 {
28 string strCols = "";
29 for(int i=0;i<SourceTable.Columns.Count-1;i++)
30 {
31 if(IsOracle)
32 {
33 strCols += SourceTable.Columns[i].Name + ",";
34 }
35 else if(SourceTable.Columns[i].Name != "fid")
36 {
37 strCols += SourceTable.Columns[i].Name + ",";
38 }
39 }
40 strCols+=SourceTable.Columns[SourceTable.Columns.Count-1].Name;
41 _allColumns = strCols;
42 }
43 return _allColumns;
44
45 }
46 }
47 #endregion
48
49AllData#region AllData
50 private string AllDate(int intRow)
51 {
52 string strVal = "";
53 int intCol=0;
54 DataTable dt = SourceTable.GetTableData();
55 DataView dv = new DataView(dt);
56 if(SortBy!=string.Empty)
57 {
58 dv.Sort = SortBy;
59 }
60
61 if(Filter!=string.Empty)
62 {
63
64 dv.RowFilter = Filter;
65 }
66 dt = dv.ToTable();
67 for(;intCol<dt.Columns.Count-1;intCol++)
68 {
69 if(IsOracle)
70 {
71 strVal += Formated(dt.Rows[intRow][intCol],intCol,intRow) + ",";
72 }
73 else if(dt.Columns[intCol].ColumnName != "fid")
74 {
75 strVal += Formated(dt.Rows[intRow][intCol],intCol,intRow) + ",";
76 }
77 }
78 strVal += Formated(dt.Rows[intRow][intCol],intCol,intRow);
79 return strVal;
80 }
81 #endregion
82
83FormatData#region FormatData
84 private string Formated(object obj,int intCol,int intRow)
85 {
86 string strVal = "";
87 switch(SourceTable.Columns[intCol].DataType)
88 {
89 case DbType.String:
90 case DbType.Guid:
91 strVal = "'" + obj + "'";
92 break;
93 case DbType.Int16:
94 case DbType.Int32:
95 case DbType.Int64:
96 case DbType.Byte:
97
98 strVal = obj.ToString();
99 break;
100 case DbType.DateTime:
101 case DbType.Date:
102 if(!IsOracle)
103 {
104 strVal = "'" + obj + "'";
105 }
106 else
107 {
108 strVal = string.Format("to_date('{0}','yyyy-mm-dd hh24:mi:ss')"
109 ,Convert.ToDateTime(obj).ToString("yyyy-MM-dd HH:mm:ss"));
110 }
111 break;
112
113 case DbType.Boolean:
114 if(obj == Convert.DBNull)
115 {
116 strVal = "0";
117 }
118 else
119 {
120 if(Convert.ToBoolean(obj))
121 {
122 strVal = "1";
123 }
124 else
125 {
126 strVal = "0";
127 }
128 }
129 break;
130 /**//* case DbType.DateTime:
131 strVal = "('" + obj + "','yyyy-mm-dd HH24:MI:SS')";
132 break;*/
133 default:
134 strVal = "'" + obj + "'";
135 break;
136 }
137 if (SourceTableData.Rows[intRow][intCol]==DBNull.Value)
138 strVal = "null";
139 return strVal;
140 }
141 #endregion
142
143 public string GetTableOwner()
144 {
145 return GetTableOwner(true);
146 }
147
148 public string GetTableOwner(bool includeDot)
149 {
150 if (SourceTable.Owner.Length > 0&&!IsOracle)
151 {
152 return SourceTable.Owner + ".";
153 }
154 else
155 {
156 return "";
157 }
158 }
159
160</script>
漂泊雪狼,转载请注明出处,谢谢
1<script runat="template">
2private DataTable _sourceTableData;
3private string _allColumns;
4private string _allData;
5
6SourceTableData#region SourceTableData
7private DataTable SourceTableData
8{
9 get
10 {
11 if (_sourceTableData == null)
12 {
13 _sourceTableData = SourceTable.GetTableData();
14 }
15
16 return _sourceTableData;
17 }
18 }
19 #endregion
20
21AllColumns#region AllColumns
22 private string AllColumns
23 {
24 get
25 {
26 if(_allColumns ==null)
27 {
28 string strCols = "";
29 for(int i=0;i<SourceTable.Columns.Count-1;i++)
30 {
31 if(IsOracle)
32 {
33 strCols += SourceTable.Columns[i].Name + ",";
34 }
35 else if(SourceTable.Columns[i].Name != "fid")
36 {
37 strCols += SourceTable.Columns[i].Name + ",";
38 }
39 }
40 strCols+=SourceTable.Columns[SourceTable.Columns.Count-1].Name;
41 _allColumns = strCols;
42 }
43 return _allColumns;
44
45 }
46 }
47 #endregion
48
49AllData#region AllData
50 private string AllDate(int intRow)
51 {
52 string strVal = "";
53 int intCol=0;
54 DataTable dt = SourceTable.GetTableData();
55 DataView dv = new DataView(dt);
56 if(SortBy!=string.Empty)
57 {
58 dv.Sort = SortBy;
59 }
60
61 if(Filter!=string.Empty)
62 {
63
64 dv.RowFilter = Filter;
65 }
66 dt = dv.ToTable();
67 for(;intCol<dt.Columns.Count-1;intCol++)
68 {
69 if(IsOracle)
70 {
71 strVal += Formated(dt.Rows[intRow][intCol],intCol,intRow) + ",";
72 }
73 else if(dt.Columns[intCol].ColumnName != "fid")
74 {
75 strVal += Formated(dt.Rows[intRow][intCol],intCol,intRow) + ",";
76 }
77 }
78 strVal += Formated(dt.Rows[intRow][intCol],intCol,intRow);
79 return strVal;
80 }
81 #endregion
82
83FormatData#region FormatData
84 private string Formated(object obj,int intCol,int intRow)
85 {
86 string strVal = "";
87 switch(SourceTable.Columns[intCol].DataType)
88 {
89 case DbType.String:
90 case DbType.Guid:
91 strVal = "'" + obj + "'";
92 break;
93 case DbType.Int16:
94 case DbType.Int32:
95 case DbType.Int64:
96 case DbType.Byte:
97
98 strVal = obj.ToString();
99 break;
100 case DbType.DateTime:
101 case DbType.Date:
102 if(!IsOracle)
103 {
104 strVal = "'" + obj + "'";
105 }
106 else
107 {
108 strVal = string.Format("to_date('{0}','yyyy-mm-dd hh24:mi:ss')"
109 ,Convert.ToDateTime(obj).ToString("yyyy-MM-dd HH:mm:ss"));
110 }
111 break;
112
113 case DbType.Boolean:
114 if(obj == Convert.DBNull)
115 {
116 strVal = "0";
117 }
118 else
119 {
120 if(Convert.ToBoolean(obj))
121 {
122 strVal = "1";
123 }
124 else
125 {
126 strVal = "0";
127 }
128 }
129 break;
130 /**//* case DbType.DateTime:
131 strVal = "('" + obj + "','yyyy-mm-dd HH24:MI:SS')";
132 break;*/
133 default:
134 strVal = "'" + obj + "'";
135 break;
136 }
137 if (SourceTableData.Rows[intRow][intCol]==DBNull.Value)
138 strVal = "null";
139 return strVal;
140 }
141 #endregion
142
143 public string GetTableOwner()
144 {
145 return GetTableOwner(true);
146 }
147
148 public string GetTableOwner(bool includeDot)
149 {
150 if (SourceTable.Owner.Length > 0&&!IsOracle)
151 {
152 return SourceTable.Owner + ".";
153 }
154 else
155 {
156 return "";
157 }
158 }
159
160</script>