我的代码生成器（更新中）
1
using System;
2using System.Collections.Generic;
3using System.Text;
4using System.Data;
5using System.Data.SqlClient;
6
7namespace JoeyCodeGenerator
8
{
9
public class CSharp : IProgrammingLanguage
10
{
11 public void GenerateBusinessLogicLayer(string projectName, string tableName, DataTable columns, DataTable pk, string folderPath)
12 {
13 string filePath = folderPath + "\\CSharp\\BusinessLogicLayer\\";
14 Routine.WriteFile(filePath + tableName + ".cs", GenerateClass(projectName, tableName, columns));
15 Routine.WriteFile(filePath + tableName + "Broker.cs", GenerateBroker(projectName, tableName));
16
}
17
18 public void GenerateDataAccessLayer(string projectName, string tableName, DataTable columns, DataTable pk, string folderPath)
19 {
20 string filePath = folderPath + "\\CSharp\\DataAccessLayer\\";
21 Routine.WriteFile(filePath + tableName + "DataBase.cs", GenerateDataBase(projectName, tableName, columns, pk));
22 }
23
24 private string GenerateBroker(string projectName, string tableName)
25 {
26 StringBuilder sb = new StringBuilder();
27 sb.AppendLine("using System;");
28 sb.AppendLine("using System.Collections.Generic;");
29 sb.AppendLine("using System.Text;");
30 sb.AppendLine();
31 sb.AppendLine("namespace " + projectName);
32 sb.AppendLine("{");
33 sb.AppendLine(" public class " + tableName + "Broker");
34 sb.AppendLine(" {");
35 sb.AppendLine(" #region \"Methods\"");
36 sb.AppendLine(" #region \"Insert\"");
37 sb.AppendLine(" public static void Insert(" + tableName + " instance)");
38 sb.AppendLine(" {");
39 sb.AppendLine(" //add business logic here");
40 sb.AppendLine(" " + tableName + "DataBase.Insert(instance);");
41 sb.AppendLine(" }");
42 sb.AppendLine(" #endregion");
43 sb.AppendLine();
44 sb.AppendLine(" #region \"Select\"");
45 sb.AppendLine(" public static void Select(" + tableName + " instance)");
46 sb.AppendLine(" {");
47 sb.AppendLine(" //add business logic here");
48 sb.AppendLine(" " + tableName + "DataBase.Select(instance);");
49 sb.AppendLine(" }");
50 sb.AppendLine(" #endregion");
51 sb.AppendLine();
52 sb.AppendLine(" #region \"Update\"");
53 sb.AppendLine(" public static void Update(" + tableName + " instance)");
54 sb.AppendLine(" {");
55 sb.AppendLine(" //add business logic here");
56 sb.AppendLine(" " + tableName + "DataBase.Update(instance);");
57 sb.AppendLine(" }");
58 sb.AppendLine(" #endregion");
59 sb.AppendLine();
60 sb.AppendLine(" #region \"Delete\"");
61 sb.AppendLine(" public static void Delete(" + tableName + " instance)");
62 sb.AppendLine(" {");
63 sb.AppendLine(" //add business logic here");
64 sb.AppendLine(" " + tableName + "DataBase.Delete(instance);");
65 sb.AppendLine(" }");
66 sb.AppendLine(" #endregion");
67 sb.AppendLine(" #endregion");
68 sb.AppendLine(" }");
69 sb.AppendLine("}");
70 return sb.ToString();
71 }
72
73 private string GenerateDataBase(string projectName, string tableName, DataTable columns, DataTable pk)
74 {
75 DataTable npk = Routine.GetNonePKColumns(columns, pk);
76 StringBuilder sb = new StringBuilder();
77 sb.AppendLine("using System;");
78 sb.AppendLine("using System.Collections.Generic;");
79 sb.AppendLine("using System.Text;");
80 sb.AppendLine("using System.Data;");
81 sb.AppendLine("using System.Data.SqlClient;");
82 sb.AppendLine("using System.Configuration;");
83 sb.AppendLine();
84 sb.AppendLine("namespace " + projectName);
85 sb.AppendLine("{");
86 sb.AppendLine(" public class " + tableName + "DataBase");
87 sb.AppendLine(" {");
88 sb.AppendLine(" #region \"Properties\"");
89 sb.AppendLine(" public const string CONNECTION_STRING = ConfigurationManager.ConnectionStrings[\"ConnectionString\"].ConnectionString;");
90 sb.AppendLine(" #endregion");
91 sb.AppendLine();
92 sb.AppendLine(" #region \"Methods\"");
93 sb.AppendLine(" #region \"Insert\"");
94 sb.AppendLine(" public static void Insert(" + tableName + " instance)");
95 sb.AppendLine(" {");
96 sb.AppendLine(" using (SqlConnection conn = new SqlConnection(CONNECTION_STRING))");
97 sb.AppendLine(" {");
98 sb.AppendLine(" using (SqlCommand comm = new SqlCommand())");
99 sb.AppendLine(" {");
100 sb.AppendLine(" comm.Connection = conn;");
101 sb.AppendLine(" comm.CommandType = CommandType.StoredProcedure;");
102 sb.AppendLine(" comm.CommandText = \"" + tableName + "_Insert\";");
103 foreach (DataRow dr in npk.Rows)
104 {
105 sb.AppendLine(" comm.Parameters.AddWithValue(\"@" + dr[0].ToString() + "\", instance." + dr[0].ToString() + ");");
106 }
107 sb.AppendLine(" SqlParameter sp;");
108 if (pk.Rows.Count == 1)//not composite primary key, retrieve the generated pk from database
109 {
110 sb.AppendLine(" sp = comm.Parameters.Add(\"@" + pk.Rows[0][0].ToString() + "\", " + Routine.ConvertFromDatabaseToSqlDbType(pk.Rows[0][1].ToString()) + ");");
111 sb.AppendLine(" sp.Direction = ParameterDirection.Output;");
112 }
113 else//composite primary key
114 {
115 foreach (DataRow dr in pk.Rows)
116 {
117 sb.AppendLine(" comm.Parameters.AddWithValue(\"@" + dr[0].ToString() + "\", instance." + dr[0].ToString() + ");");
118 }
119 }
120 sb.AppendLine(" sp = new SqlParameter();");
121 sb.AppendLine(" sp.Direction = ParameterDirection.ReturnValue;");
122 sb.AppendLine(" comm.Parameters.Add(sp);");
123 sb.AppendLine(" conn.Open();");
124 sb.AppendLine(" comm.ExecuteNonQuery();");
125 sb.AppendLine(" if ((int)sp.Value != 0)");
126 sb.AppendLine(" {");
127 sb.AppendLine(" throw new Exception(\"Errors happened when executing the stored procedure\");");
128 sb.AppendLine(" }");
129 if (pk.Rows.Count == 1)//not composite primary key, retrieve the generated pk from database
130 {
131 sb.AppendLine(" instance." + pk.Rows[0][0].ToString() + " = (" + Routine.ConvertFromDatabaseToLanguage(pk.Rows[0][1].ToString(), ProgrammingLanguageType.CSharp, DatabaseType.MSSQL) + ")comm.Parameters[\"@" + pk.Rows[0][0].ToString() + "\"].Value;");
132 }
133 sb.AppendLine(" conn.Close();");
134 sb.AppendLine(" }");
135 sb.AppendLine(" }");
136 sb.AppendLine(" }");
137 sb.AppendLine(" #endregion");
138 sb.AppendLine();
139 sb.AppendLine(" #region \"Select\"");
140 sb.AppendLine(" public static void Select(" + tableName + " instance)");
141 sb.AppendLine(" {");
142 sb.AppendLine(" using (SqlConnection conn = new SqlConnection(CONNECTION_STRING))");
143 sb.AppendLine(" {");
144 sb.AppendLine(" using (SqlCommand comm = new SqlCommand())");
145 sb.AppendLine(" {");
146 sb.AppendLine(" comm.Connection = conn;");
147 sb.AppendLine(" comm.CommandType = CommandType.StoredProcedure;");
148 sb.AppendLine(" comm.CommandText = \"" + tableName + "_Select\";");
149 foreach (DataRow dr in pk.Rows)
150 {
151 sb.AppendLine(" comm.Parameters.AddWithValue(\"@" + dr[0].ToString() + "\", instance." + dr[0].ToString() + ");");
152 }
153 sb.AppendLine(" SqlParameter sp;");
154 sb.AppendLine(" sp = new SqlParameter();");
155 sb.AppendLine(" sp.Direction = ParameterDirection.ReturnValue;");
156 sb.AppendLine(" comm.Parameters.Add(sp);");
157 sb.AppendLine(" conn.Open();");
158 sb.AppendLine(" using (SqlDataReader sdr = comm.ExecuteReader(CommandBehavior.CloseConnection))");
159 sb.AppendLine(" {");
160 sb.AppendLine(" if (sdr.Read())");
161 sb.AppendLine(" {");
162 foreach (DataRow dr in npk.Rows)
163 {
164 sb.AppendLine(" instance." + dr[0].ToString() + " = (" + Routine.ConvertFromDatabaseToLanguage(dr[1].ToString(), ProgrammingLanguageType.CSharp, DatabaseType.MSSQL) + ")sdr[\"" + dr[0].ToString() + "\"];");
165 }
166 sb.AppendLine(" }");
167 sb.AppendLine(" sdr.Close();");
168 sb.AppendLine(" }");
169 sb.AppendLine(" if ((int)sp.Value != 0)");
170 sb.AppendLine(" {");
171 sb.AppendLine(" throw new Exception(\"Errors happened when executing the stored procedure\");");
172 sb.AppendLine(" }");
173 sb.AppendLine(" }");
174 sb.AppendLine(" }");
175 sb.AppendLine(" }");
176 sb.AppendLine(" #endregion");
177 sb.AppendLine();
178 sb.AppendLine(" #region \"Update\"");
179 sb.AppendLine(" public static void Update(" + tableName + " instance)");
180 sb.AppendLine(" {");
181 sb.AppendLine(" using (SqlConnection conn = new SqlConnection(CONNECTION_STRING))");
182 sb.AppendLine(" {");
183 sb.AppendLine(" using (SqlCommand comm = new SqlCommand())");
184 sb.AppendLine(" {");
185 sb.AppendLine(" comm.Connection = conn;");
186 sb.AppendLine(" comm.CommandType = CommandType.StoredProcedure;");
187 sb.AppendLine(" comm.CommandText = \"" + tableName + "_Update\";");
188 foreach (DataRow dr in columns.Rows)
189 {
190 sb.AppendLine(" comm.Parameters.AddWithValue(\"@" + dr[0].ToString() + "\", instance." + dr[0].ToString() + ");");
191 }
192 sb.AppendLine(" SqlParameter sp;");
193 sb.AppendLine(" sp = new SqlParameter();");
194 sb.AppendLine(" sp.Direction = ParameterDirection.ReturnValue;");
195 sb.AppendLine(" comm.Parameters.Add(sp);");
196 sb.AppendLine(" conn.Open();");
197 sb.AppendLine(" comm.ExecuteNonQuery();");
198 sb.AppendLine(" if ((int)sp.Value != 0)");
199 sb.AppendLine(" {");
200 sb.AppendLine(" throw new Exception(\"Errors happened when executing the stored procedure\");");
201 sb.AppendLine(" }");
202 sb.AppendLine(" conn.Close();");
203 sb.AppendLine(" }");
204 sb.AppendLine(" }");
205 sb.AppendLine(" }");
206 sb.AppendLine(" #endregion");
207 sb.AppendLine();
208 sb.AppendLine(" #region \"Delete\"");
209 sb.AppendLine(" public static void Delete(" + tableName + " instance)");
210 sb.AppendLine(" {");
211 sb.AppendLine(" using (SqlConnection conn = new SqlConnection(CONNECTION_STRING))");
212 sb.AppendLine(" {");
213 sb.AppendLine(" using (SqlCommand comm = new SqlCommand())");
214 sb.AppendLine(" {");
215 sb.AppendLine(" comm.Connection = conn;");
216 sb.AppendLine(" comm.CommandType = CommandType.StoredProcedure;");
217 sb.AppendLine(" comm.CommandText = \"" + tableName + "_Delete\";");
218 foreach (DataRow dr in pk.Rows)
219 {
220 sb.AppendLine(" comm.Parameters.AddWithValue(\"@" + dr[0].ToString() + "\", instance." + dr[0].ToString() + ");");
221 }
222 sb.AppendLine(" SqlParameter sp;");
223 sb.AppendLine(" sp = new SqlParameter();");
224 sb.AppendLine(" sp.Direction = ParameterDirection.ReturnValue;");
225 sb.AppendLine(" comm.Parameters.Add(sp);");
226 sb.AppendLine(" conn.Open();");
227 sb.AppendLine(" comm.ExecuteNonQuery();");
228 sb.AppendLine(" if ((int)sp.Value != 0)");
229 sb.AppendLine(" {");
230 sb.AppendLine(" throw new Exception(\"Errors happened when executing the stored procedure\");");
231 sb.AppendLine(" }");
232 sb.AppendLine(" conn.Close();");
233 sb.AppendLine(" }");
234 sb.AppendLine(" }");
235 sb.AppendLine(" }");
236 sb.AppendLine(" #endregion");
237 sb.AppendLine(" #endregion");
238 sb.AppendLine(" }");
239 sb.AppendLine("}");
240 return sb.ToString();
241 }
242
243 private string GenerateClass(string projectName, string tableName, DataTable columns)
244 {
245 StringBuilder sb = new StringBuilder();
246 sb.AppendLine("using System;");
247 sb.AppendLine("using System.Collections.Generic;");
248 sb.AppendLine("using System.Text;");
249 sb.AppendLine();
250 sb.AppendLine("namespace " + projectName);
251 sb.AppendLine("{");
252 sb.AppendLine(" public class " + tableName);
253 sb.AppendLine(" {");
254 sb.AppendLine(" #region \"Properties\"");
255 foreach (DataRow dr in columns.Rows)
256 {
257 sb.AppendLine(" private " + Routine.ConvertFromDatabaseToLanguage(dr[1].ToString(), ProgrammingLanguageType.CSharp, DatabaseType.MSSQL) + " _" + dr[0].ToString() + ";");
258 }
259 foreach (DataRow dr in columns.Rows)
260 {
261 sb.AppendLine();
262 sb.AppendLine(" public " + Routine.ConvertFromDatabaseToLanguage(dr[1].ToString(), ProgrammingLanguageType.CSharp, DatabaseType.MSSQL) + " " + dr[0].ToString());
263 sb.AppendLine(" {");
264 sb.AppendLine(" get");
265 sb.AppendLine(" {");
266 sb.AppendLine(" return this._" + dr[0].ToString() + ";");
267 sb.AppendLine(" }");
268 sb.AppendLine(" set");
269 sb.AppendLine(" {");
270 sb.AppendLine(" this._" + dr[0].ToString() + " = value;");
271 sb.AppendLine(" }");
272 sb.AppendLine(" }");
273 }
274 sb.AppendLine(" #endregion");
275 sb.AppendLine(" }");
276 sb.AppendLine("}");
277 return sb.ToString();
278 }
279 }
280
}
281
using System;2
using System.Collections.Generic;3
using System.Text;4
using System.Data;5
using System.Data.SqlClient;6
7
namespace JoeyCodeGenerator8
{
9
public class CSharp : IProgrammingLanguage10
{11
public void GenerateBusinessLogicLayer(string projectName, string tableName, DataTable columns, DataTable pk, string folderPath)12
{13
string filePath = folderPath + "\\CSharp\\BusinessLogicLayer\\";14
Routine.WriteFile(filePath + tableName + ".cs", GenerateClass(projectName, tableName, columns));15
Routine.WriteFile(filePath + tableName + "Broker.cs", GenerateBroker(projectName, tableName));16
}17
18
public void GenerateDataAccessLayer(string projectName, string tableName, DataTable columns, DataTable pk, string folderPath)19
{20
string filePath = folderPath + "\\CSharp\\DataAccessLayer\\";21
Routine.WriteFile(filePath + tableName + "DataBase.cs", GenerateDataBase(projectName, tableName, columns, pk));22
}23
24
private string GenerateBroker(string projectName, string tableName)25
{26
StringBuilder sb = new StringBuilder();27
sb.AppendLine("using System;");28
sb.AppendLine("using System.Collections.Generic;");29
sb.AppendLine("using System.Text;");30
sb.AppendLine();31
sb.AppendLine("namespace " + projectName);32
sb.AppendLine("{");33
sb.AppendLine(" public class " + tableName + "Broker");34
sb.AppendLine(" {");35
sb.AppendLine(" #region \"Methods\"");36
sb.AppendLine(" #region \"Insert\"");37
sb.AppendLine(" public static void Insert(" + tableName + " instance)");38
sb.AppendLine(" {");39
sb.AppendLine(" //add business logic here");40
sb.AppendLine(" " + tableName + "DataBase.Insert(instance);");41
sb.AppendLine(" }");42
sb.AppendLine(" #endregion");43
sb.AppendLine();44
sb.AppendLine(" #region \"Select\"");45
sb.AppendLine(" public static void Select(" + tableName + " instance)");46
sb.AppendLine(" {");47
sb.AppendLine(" //add business logic here");48
sb.AppendLine(" " + tableName + "DataBase.Select(instance);");49
sb.AppendLine(" }");50
sb.AppendLine(" #endregion");51
sb.AppendLine();52
sb.AppendLine(" #region \"Update\"");53
sb.AppendLine(" public static void Update(" + tableName + " instance)");54
sb.AppendLine(" {");55
sb.AppendLine(" //add business logic here");56
sb.AppendLine(" " + tableName + "DataBase.Update(instance);");57
sb.AppendLine(" }");58
sb.AppendLine(" #endregion");59
sb.AppendLine();60
sb.AppendLine(" #region \"Delete\"");61
sb.AppendLine(" public static void Delete(" + tableName + " instance)");62
sb.AppendLine(" {");63
sb.AppendLine(" //add business logic here");64
sb.AppendLine(" " + tableName + "DataBase.Delete(instance);");65
sb.AppendLine(" }");66
sb.AppendLine(" #endregion");67
sb.AppendLine(" #endregion");68
sb.AppendLine(" }");69
sb.AppendLine("}");70
return sb.ToString();71
}72
73
private string GenerateDataBase(string projectName, string tableName, DataTable columns, DataTable pk)74
{75
DataTable npk = Routine.GetNonePKColumns(columns, pk);76
StringBuilder sb = new StringBuilder();77
sb.AppendLine("using System;");78
sb.AppendLine("using System.Collections.Generic;");79
sb.AppendLine("using System.Text;");80
sb.AppendLine("using System.Data;");81
sb.AppendLine("using System.Data.SqlClient;");82
sb.AppendLine("using System.Configuration;");83
sb.AppendLine();84
sb.AppendLine("namespace " + projectName);85
sb.AppendLine("{");86
sb.AppendLine(" public class " + tableName + "DataBase");87
sb.AppendLine(" {");88
sb.AppendLine(" #region \"Properties\"");89
sb.AppendLine(" public const string CONNECTION_STRING = ConfigurationManager.ConnectionStrings[\"ConnectionString\"].ConnectionString;");90
sb.AppendLine(" #endregion");91
sb.AppendLine();92
sb.AppendLine(" #region \"Methods\"");93
sb.AppendLine(" #region \"Insert\"");94
sb.AppendLine(" public static void Insert(" + tableName + " instance)");95
sb.AppendLine(" {");96
sb.AppendLine(" using (SqlConnection conn = new SqlConnection(CONNECTION_STRING))");97
sb.AppendLine(" {");98
sb.AppendLine(" using (SqlCommand comm = new SqlCommand())");99
sb.AppendLine(" {");100
sb.AppendLine(" comm.Connection = conn;");101
sb.AppendLine(" comm.CommandType = CommandType.StoredProcedure;");102
sb.AppendLine(" comm.CommandText = \"" + tableName + "_Insert\";");103
foreach (DataRow dr in npk.Rows)104
{105
sb.AppendLine(" comm.Parameters.AddWithValue(\"@" + dr[0].ToString() + "\", instance." + dr[0].ToString() + ");");106
}107
sb.AppendLine(" SqlParameter sp;");108
if (pk.Rows.Count == 1)//not composite primary key, retrieve the generated pk from database109
{110
sb.AppendLine(" sp = comm.Parameters.Add(\"@" + pk.Rows[0][0].ToString() + "\", " + Routine.ConvertFromDatabaseToSqlDbType(pk.Rows[0][1].ToString()) + ");");111
sb.AppendLine(" sp.Direction = ParameterDirection.Output;");112
}113
else//composite primary key114
{115
foreach (DataRow dr in pk.Rows)116
{117
sb.AppendLine(" comm.Parameters.AddWithValue(\"@" + dr[0].ToString() + "\", instance." + dr[0].ToString() + ");");118
}119
}120
sb.AppendLine(" sp = new SqlParameter();");121
sb.AppendLine(" sp.Direction = ParameterDirection.ReturnValue;");122
sb.AppendLine(" comm.Parameters.Add(sp);");123
sb.AppendLine(" conn.Open();");124
sb.AppendLine(" comm.ExecuteNonQuery();");125
sb.AppendLine(" if ((int)sp.Value != 0)");126
sb.AppendLine(" {");127
sb.AppendLine(" throw new Exception(\"Errors happened when executing the stored procedure\");");128
sb.AppendLine(" }");129
if (pk.Rows.Count == 1)//not composite primary key, retrieve the generated pk from database130
{131
sb.AppendLine(" instance." + pk.Rows[0][0].ToString() + " = (" + Routine.ConvertFromDatabaseToLanguage(pk.Rows[0][1].ToString(), ProgrammingLanguageType.CSharp, DatabaseType.MSSQL) + ")comm.Parameters[\"@" + pk.Rows[0][0].ToString() + "\"].Value;");132
}133
sb.AppendLine(" conn.Close();");134
sb.AppendLine(" }");135
sb.AppendLine(" }");136
sb.AppendLine(" }");137
sb.AppendLine(" #endregion");138
sb.AppendLine();139
sb.AppendLine(" #region \"Select\"");140
sb.AppendLine(" public static void Select(" + tableName + " instance)");141
sb.AppendLine(" {");142
sb.AppendLine(" using (SqlConnection conn = new SqlConnection(CONNECTION_STRING))");143
sb.AppendLine(" {");144
sb.AppendLine(" using (SqlCommand comm = new SqlCommand())");145
sb.AppendLine(" {");146
sb.AppendLine(" comm.Connection = conn;");147
sb.AppendLine(" comm.CommandType = CommandType.StoredProcedure;");148
sb.AppendLine(" comm.CommandText = \"" + tableName + "_Select\";");149
foreach (DataRow dr in pk.Rows)150
{151
sb.AppendLine(" comm.Parameters.AddWithValue(\"@" + dr[0].ToString() + "\", instance." + dr[0].ToString() + ");");152
}153
sb.AppendLine(" SqlParameter sp;");154
sb.AppendLine(" sp = new SqlParameter();");155
sb.AppendLine(" sp.Direction = ParameterDirection.ReturnValue;");156
sb.AppendLine(" comm.Parameters.Add(sp);");157
sb.AppendLine(" conn.Open();");158
sb.AppendLine(" using (SqlDataReader sdr = comm.ExecuteReader(CommandBehavior.CloseConnection))");159
sb.AppendLine(" {");160
sb.AppendLine(" if (sdr.Read())");161
sb.AppendLine(" {");162
foreach (DataRow dr in npk.Rows)163
{164
sb.AppendLine(" instance." + dr[0].ToString() + " = (" + Routine.ConvertFromDatabaseToLanguage(dr[1].ToString(), ProgrammingLanguageType.CSharp, DatabaseType.MSSQL) + ")sdr[\"" + dr[0].ToString() + "\"];");165
}166
sb.AppendLine(" }");167
sb.AppendLine(" sdr.Close();");168
sb.AppendLine(" }");169
sb.AppendLine(" if ((int)sp.Value != 0)");170
sb.AppendLine(" {");171
sb.AppendLine(" throw new Exception(\"Errors happened when executing the stored procedure\");");172
sb.AppendLine(" }");173
sb.AppendLine(" }");174
sb.AppendLine(" }");175
sb.AppendLine(" }");176
sb.AppendLine(" #endregion");177
sb.AppendLine();178
sb.AppendLine(" #region \"Update\"");179
sb.AppendLine(" public static void Update(" + tableName + " instance)");180
sb.AppendLine(" {");181
sb.AppendLine(" using (SqlConnection conn = new SqlConnection(CONNECTION_STRING))");182
sb.AppendLine(" {");183
sb.AppendLine(" using (SqlCommand comm = new SqlCommand())");184
sb.AppendLine(" {");185
sb.AppendLine(" comm.Connection = conn;");186
sb.AppendLine(" comm.CommandType = CommandType.StoredProcedure;");187
sb.AppendLine(" comm.CommandText = \"" + tableName + "_Update\";");188
foreach (DataRow dr in columns.Rows)189
{190
sb.AppendLine(" comm.Parameters.AddWithValue(\"@" + dr[0].ToString() + "\", instance." + dr[0].ToString() + ");");191
}192
sb.AppendLine(" SqlParameter sp;");193
sb.AppendLine(" sp = new SqlParameter();");194
sb.AppendLine(" sp.Direction = ParameterDirection.ReturnValue;");195
sb.AppendLine(" comm.Parameters.Add(sp);");196
sb.AppendLine(" conn.Open();");197
sb.AppendLine(" comm.ExecuteNonQuery();");198
sb.AppendLine(" if ((int)sp.Value != 0)");199
sb.AppendLine(" {");200
sb.AppendLine(" throw new Exception(\"Errors happened when executing the stored procedure\");");201
sb.AppendLine(" }");202
sb.AppendLine(" conn.Close();");203
sb.AppendLine(" }");204
sb.AppendLine(" }");205
sb.AppendLine(" }");206
sb.AppendLine(" #endregion");207
sb.AppendLine();208
sb.AppendLine(" #region \"Delete\"");209
sb.AppendLine(" public static void Delete(" + tableName + " instance)");210
sb.AppendLine(" {");211
sb.AppendLine(" using (SqlConnection conn = new SqlConnection(CONNECTION_STRING))");212
sb.AppendLine(" {");213
sb.AppendLine(" using (SqlCommand comm = new SqlCommand())");214
sb.AppendLine(" {");215
sb.AppendLine(" comm.Connection = conn;");216
sb.AppendLine(" comm.CommandType = CommandType.StoredProcedure;");217
sb.AppendLine(" comm.CommandText = \"" + tableName + "_Delete\";");218
foreach (DataRow dr in pk.Rows)219
{220
sb.AppendLine(" comm.Parameters.AddWithValue(\"@" + dr[0].ToString() + "\", instance." + dr[0].ToString() + ");");221
}222
sb.AppendLine(" SqlParameter sp;");223
sb.AppendLine(" sp = new SqlParameter();");224
sb.AppendLine(" sp.Direction = ParameterDirection.ReturnValue;");225
sb.AppendLine(" comm.Parameters.Add(sp);");226
sb.AppendLine(" conn.Open();");227
sb.AppendLine(" comm.ExecuteNonQuery();");228
sb.AppendLine(" if ((int)sp.Value != 0)");229
sb.AppendLine(" {");230
sb.AppendLine(" throw new Exception(\"Errors happened when executing the stored procedure\");");231
sb.AppendLine(" }");232
sb.AppendLine(" conn.Close();");233
sb.AppendLine(" }");234
sb.AppendLine(" }");235
sb.AppendLine(" }");236
sb.AppendLine(" #endregion");237
sb.AppendLine(" #endregion");238
sb.AppendLine(" }");239
sb.AppendLine("}");240
return sb.ToString();241
}242
243
private string GenerateClass(string projectName, string tableName, DataTable columns)244
{245
StringBuilder sb = new StringBuilder();246
sb.AppendLine("using System;");247
sb.AppendLine("using System.Collections.Generic;");248
sb.AppendLine("using System.Text;");249
sb.AppendLine();250
sb.AppendLine("namespace " + projectName);251
sb.AppendLine("{");252
sb.AppendLine(" public class " + tableName);253
sb.AppendLine(" {");254
sb.AppendLine(" #region \"Properties\"");255
foreach (DataRow dr in columns.Rows)256
{257
sb.AppendLine(" private " + Routine.ConvertFromDatabaseToLanguage(dr[1].ToString(), ProgrammingLanguageType.CSharp, DatabaseType.MSSQL) + " _" + dr[0].ToString() + ";");258
}259
foreach (DataRow dr in columns.Rows)260
{261
sb.AppendLine();262
sb.AppendLine(" public " + Routine.ConvertFromDatabaseToLanguage(dr[1].ToString(), ProgrammingLanguageType.CSharp, DatabaseType.MSSQL) + " " + dr[0].ToString());263
sb.AppendLine(" {");264
sb.AppendLine(" get");265
sb.AppendLine(" {");266
sb.AppendLine(" return this._" + dr[0].ToString() + ";");267
sb.AppendLine(" }");268
sb.AppendLine(" set");269
sb.AppendLine(" {");270
sb.AppendLine(" this._" + dr[0].ToString() + " = value;");271
sb.AppendLine(" }");272
sb.AppendLine(" }");273
}274
sb.AppendLine(" #endregion");275
sb.AppendLine(" }");276
sb.AppendLine("}");277
return sb.ToString();278
}279
}280
}281
1using System;
2
3namespace JoeyCodeGenerator
4{
5 [Flags]
6 public enum DatabaseType
7 {
8 Default = 0,
9 MSSQL = 1,
10 Oracle = 2,
11 MySQL = 3,
12 Access = 4
13 }
14}
using System;2
3
namespace JoeyCodeGenerator4
{5
[Flags]6
public enum DatabaseType7
{8
Default = 0,9
MSSQL = 1,10
Oracle = 2,11
MySQL = 3,12
Access = 413
}14
} 1using System;
2using System.Collections.Generic;
3using System.ComponentModel;
4using System.Data;
5using System.Drawing;
6using System.Text;
7using System.Windows.Forms;
8using System.IO;
9
10namespace JoeyCodeGenerator
11{
12 public partial class Form1 : Form
13 {
14 public Form1()
15 {
16 InitializeComponent();
17 }
18
19 private void Form1_Load(object sender, EventArgs e)
20 {
21
22 }
23
24 private void button1_Click(object sender, EventArgs e)
25 {
26 Generator g = new Generator(this.textBox1.Text, this.textBox2.Text, this.textBox3.Text, DatabaseType.MSSQL, ProgrammingLanguageType.CSharp);
27 g.Generate();
28 }
29 }
30}
using System;2
using System.Collections.Generic;3
using System.ComponentModel;4
using System.Data;5
using System.Drawing;6
using System.Text;7
using System.Windows.Forms;8
using System.IO;9
10
namespace JoeyCodeGenerator11
{12
public partial class Form1 : Form13
{14
public Form1()15
{16
InitializeComponent();17
}18
19
private void Form1_Load(object sender, EventArgs e)20
{21
22
}23
24
private void button1_Click(object sender, EventArgs e)25
{26
Generator g = new Generator(this.textBox1.Text, this.textBox2.Text, this.textBox3.Text, DatabaseType.MSSQL, ProgrammingLanguageType.CSharp);27
g.Generate();28
}29
}30
} 1using System;
2using System.Collections.Generic;
3using System.Text;
4using System.Data.SqlClient;
5using System.Data;
6using System.IO;
7
8namespace JoeyCodeGenerator
9{
10 public class Generator
11 {
12
private properties
21
22 public properties
107
108 constructors
126
127 public methods
142 }
143}
using System;2
using System.Collections.Generic;3
using System.Text;4
using System.Data.SqlClient;5
using System.Data;6
using System.IO;7
8
namespace JoeyCodeGenerator9
{10
public class Generator11
{12
private properties21
22
public properties107
108
constructors126
127
public methods142
}143
} 1using System;
2using System.Collections.Generic;
3using System.Text;
4using System.Data;
5
6namespace JoeyCodeGenerator
7{
8 interface IDataBase
9 {
10 DataTable GetColumnsFromTable(string tableName);
11 DataTable GetPKFromTable(string tableName);
12 List<string> GetTableNames();
13 void GenerateStoredProcedures(string tableName, DataTable columns, DataTable pk, string folderPath);
14 }
15}
16
using System;2
using System.Collections.Generic;3
using System.Text;4
using System.Data;5
6
namespace JoeyCodeGenerator7
{8
interface IDataBase9
{10
DataTable GetColumnsFromTable(string tableName);11
DataTable GetPKFromTable(string tableName);12
List<string> GetTableNames();13
void GenerateStoredProcedures(string tableName, DataTable columns, DataTable pk, string folderPath);14
}15
}16
1using System;
2using System.Collections.Generic;
3using System.Text;
4using System.Data;
5
6namespace JoeyCodeGenerator
7{
8 interface IProgrammingLanguage
9 {
10 void GenerateBusinessLogicLayer(string projectName, string tableName, DataTable columns, DataTable pk, string folderPath);
11 void GenerateDataAccessLayer(string projectName, string tableName, DataTable columns, DataTable pk, string folderPath);
12 }
13}
14
using System;2
using System.Collections.Generic;3
using System.Text;4
using System.Data;5
6
namespace JoeyCodeGenerator7
{8
interface IProgrammingLanguage9
{10
void GenerateBusinessLogicLayer(string projectName, string tableName, DataTable columns, DataTable pk, string folderPath);11
void GenerateDataAccessLayer(string projectName, string tableName, DataTable columns, DataTable pk, string folderPath);12
}13
}14
1using System;
2using System.Collections.Generic;
3using System.Text;
4using System.Data;
5using System.Data.SqlClient;
6
7namespace JoeyCodeGenerator
8{
9 public class MSSQL : IDataBase
10 {
11 private string _connectionString;
12
13 public MSSQL(String connectionString)
14 {
15 this.ConnectionString = connectionString;
16 }
17
18 public string ConnectionString
19 {
20 get
21 {
22 return this._connectionString;
23 }
24 set
25 {
26 this._connectionString = value;
27 }
28 }
29
30 public DataTable GetColumnsFromTable(string tableName)
31 {
32 StringBuilder sb = new StringBuilder();
33 sb.AppendLine("SELECT columnTable.name,");
34 sb.AppendLine(" typeTable.name,");
35 sb.AppendLine(" REPLACE(CAST(COLUMNPROPERTY(columnTable.id, columnTable.name, 'PRECISION') AS nvarchar),'-1','4000')");
36 sb.AppendLine(" FROM syscolumns columnTable");
37 sb.AppendLine(" JOIN");
38 sb.AppendLine(" systypes typeTable");
39 sb.AppendLine(" ON columnTable.xtype = typeTable.xtype");
40 sb.AppendLine(" WHERE columnTable.id = object_id(N'" + tableName + "')");
41 sb.AppendLine(" AND typeTable.name <> 'sysname'");
42 DataTable dt = new DataTable();
43 using (SqlConnection conn = new SqlConnection(this.ConnectionString))
44 {
45 using (SqlCommand comm = new SqlCommand())
46 {
47 comm.CommandText = sb.ToString();
48 comm.Connection = conn;
49 conn.Open();
50 using (SqlDataAdapter sda = new SqlDataAdapter())
51 {
52 sda.SelectCommand = comm;
53 sda.Fill(dt);
54 }
55 }
56 }
57 return dt;
58 }
59
60 public DataTable GetPKFromTable(string tableName)
61 {
62 StringBuilder sb = new StringBuilder();
63 sb.AppendLine("SELECT PrimaryKey = columnTable.name,");
64 sb.AppendLine(" KeyType = typeTable.name,");
65 sb.AppendLine(" REPLACE(CAST(COLUMNPROPERTY(columnTable.id, columnTable.name, 'PRECISION') AS nvarchar),'-1','4000')");
66 sb.AppendLine(" FROM syscolumns columnTable");
67 sb.AppendLine(" JOIN systypes typeTable");
68 sb.AppendLine(" ON columnTable.xtype = typeTable.xtype");
69 sb.AppendLine(" JOIN sysobjects objectTable");
70 sb.AppendLine(" ON columnTable.id = objectTable.id");
71 sb.AppendLine(" AND objectTable.xtype = 'U'");
72 sb.AppendLine(" AND objectTable.name <> 'dtproperties'");
73 sb.AppendLine(" WHERE EXISTS");
74 sb.AppendLine(" (");
75 sb.AppendLine(" SELECT 1");
76 sb.AppendLine(" FROM sysobjects");
77 sb.AppendLine(" WHERE xtype='PK'");
78 sb.AppendLine(" AND name IN");
79 sb.AppendLine(" (");
80 sb.AppendLine(" SELECT name");
81 sb.AppendLine(" FROM sysindexes");
82 sb.AppendLine(" WHERE indid IN");
83 sb.AppendLine(" (");
84 sb.AppendLine(" SELECT indid");
85 sb.AppendLine(" FROM sysindexkeys");
86 sb.AppendLine(" WHERE id = columnTable.id");
87 sb.AppendLine(" AND colid = columnTable.colid");
88 sb.AppendLine(" )");
89 sb.AppendLine(" )");
90 sb.AppendLine(" )");
91 sb.AppendLine(" AND objectTable.name='" + tableName + "'");
92 sb.AppendLine(" AND typeTable.name <> 'sysname'");
93 DataTable dt = new DataTable();
94 using (SqlConnection conn = new SqlConnection(this.ConnectionString))
95 {
96 using (SqlCommand comm = new SqlCommand())
97 {
98 comm.CommandText = sb.ToString();
99 comm.Connection = conn;
100 conn.Open();
101 using (SqlDataAdapter sda = new SqlDataAdapter())
102 {
103 sda.SelectCommand = comm;
104 sda.Fill(dt);
105 }
106 }
107 }
108 return dt;
109 }
110
111 public List<string> GetTableNames()
112 {
113 List<string> tableNames = new List<string>();
114 using (SqlConnection conn = new SqlConnection(this.ConnectionString))
115 {
116 using (SqlCommand comm = new SqlCommand())
117 {
118 comm.CommandText = "SELECT table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE'";
119 comm.Connection = conn;
120 conn.Open();
121 using (SqlDataReader sdr = comm.ExecuteReader(CommandBehavior.CloseConnection))
122 {
123 while (sdr.Read())
124 {
125 tableNames.Add(sdr[0].ToString());
126 }
127 }
128 }
129 }
130 return tableNames;
131 }
132
133 public void GenerateStoredProcedures(string tableName, DataTable columns, DataTable pk, string folderPath)
134 {
135 string filePath = folderPath + "\\MSSQL\\";
136 string content;
137 content = GenerateInsert(tableName, columns, pk);
138 if (!string.IsNullOrEmpty(content))
139 {
140 Routine.WriteFile(filePath + tableName + "_Insert.sp", content);
141 }
142 content = GenerateSelect(tableName, columns, pk);
143 if (!string.IsNullOrEmpty(content))
144 {
145 Routine.WriteFile(filePath + tableName + "_Select.sp", content);
146 }
147 content = GenerateUpdate(tableName, columns, pk);
148 if (!string.IsNullOrEmpty(content))
149 {
150 Routine.WriteFile(filePath + tableName + "_Update.sp", content);
151 }
152 content = GenerateDelete(tableName, pk);
153 if (!string.IsNullOrEmpty(content))
154 {
155 Routine.WriteFile(filePath + tableName + "_Delete.sp", content);
156 }
157 }
158
159 private string GenerateDelete(string tableName, DataTable pk)
160 {
161 StringBuilder sb = new StringBuilder();
162 sb.AppendLine("SET ANSI_NULLS ON");
163 sb.AppendLine("SET QUOTED_IDENTIFIER OFF");
164 sb.AppendLine("GO");
165 sb.AppendLine("CREATE PROCEDURE " + tableName + "_Delete");
166 for (int i = 0; i < pk.Rows.Count - 1; i++)
167 {
168 if (pk.Rows[i][1].ToString() != "nvarchar" && pk.Rows[i][1].ToString() != "nchar")
169 {
170 sb.AppendLine(" @" + pk.Rows[i][0].ToString() + GenerateProperSpaces(pk.Rows[i][0].ToString(), 18) + pk.Rows[i][1].ToString() + ",");
171 }
172 else
173 {
174 sb.AppendLine(" @" + pk.Rows[i][0].ToString() + GenerateProperSpaces(pk.Rows[i][0].ToString(), 18) + pk.Rows[i][1].ToString() + "(" + pk.Rows[i][2].ToString() + ")" + ",");
175 }
176 }
177 if (pk.Rows.Count != 0)
178 {
179 if (pk.Rows[pk.Rows.Count - 1][1].ToString() != "nvarchar" && pk.Rows[pk.Rows.Count - 1][1].ToString() != "nchar")
180 {
181 sb.AppendLine(" @" + pk.Rows[pk.Rows.Count - 1][0].ToString() + GenerateProperSpaces(pk.Rows[pk.Rows.Count - 1][0].ToString(), 18) + pk.Rows[pk.Rows.Count - 1][1].ToString());
182 }
183 else
184 {
185 sb.AppendLine(" @" + pk.Rows[pk.Rows.Count - 1][0].ToString() + GenerateProperSpaces(pk.Rows[pk.Rows.Count - 1][0].ToString(), 18) + pk.Rows[pk.Rows.Count - 1][1].ToString() + "(" + pk.Rows[pk.Rows.Count - 1][2].ToString() + ")");
186 }
187 }
188 sb.AppendLine("AS");
189 sb.AppendLine("BEGIN");
190 sb.AppendLine(" DECLARE @ErrorCode int");
191 sb.AppendLine(" SELECT @ErrorCode = 0");
192 sb.AppendLine();
193 sb.AppendLine(" DECLARE @TranStarted bit");
194 sb.AppendLine(" SELECT @TranStarted = 0");
195 sb.AppendLine();
196 sb.AppendLine(" IF(@@TRANCOUNT = 0)");
197 sb.AppendLine(" BEGIN");
198 sb.AppendLine(" BEGIN TRANSACTION");
199 sb.AppendLine(" SELECT @TranStarted = 1");
200 sb.AppendLine(" END");
201 sb.AppendLine(" ELSE");
202 sb.AppendLine(" SELECT @TranStarted = 0");
203 sb.AppendLine();
204 sb.AppendLine(" DELETE FROM [" + tableName + "]");
205 if (pk.Rows.Count == 1)
206 {
207 sb.AppendLine(" WHERE [" + pk.Rows[0][0].ToString() + "] = @" + pk.Rows[0][0].ToString());
208 }
209 else
210 {
211 sb.AppendLine(" WHERE [" + pk.Rows[0][0].ToString() + "] = @" + pk.Rows[0][0].ToString());
212 for (int i = 1; i < pk.Rows.Count; i++)
213 {
214 sb.AppendLine(" AND [" + pk.Rows[i][0].ToString() + "] = @" + pk.Rows[i][0].ToString());
215 }
216 }
217 sb.AppendLine();
218 sb.AppendLine(" IF(@@ERROR <> 0)");
219 sb.AppendLine(" BEGIN");
220 sb.AppendLine(" SELECT @ErrorCode = -1");
221 sb.AppendLine(" GOTO Cleanup");
222 sb.AppendLine(" END");
223 sb.AppendLine();
224 sb.AppendLine(" IF(@TranStarted = 1)");
225 sb.AppendLine(" BEGIN");
226 sb.AppendLine(" SELECT @TranStarted = 0");
227 sb.AppendLine(" COMMIT TRANSACTION");
228 sb.AppendLine(" END");
229 sb.AppendLine();
230 sb.AppendLine("Cleanup:");
231 sb.AppendLine();
232 sb.AppendLine(" IF(@TranStarted = 1)");
233 sb.AppendLine(" BEGIN");
234 sb.AppendLine(" SET @TranStarted = 0");
235 sb.AppendLine(" ROLLBACK TRANSACTION");
236 sb.AppendLine(" END");
237 sb.AppendLine();
238 sb.AppendLine(" RETURN @ErrorCode");
239 sb.AppendLine();
240 sb.AppendLine("END");
241 return sb.ToString();
242 }
243
244 private string GenerateSelect(string tableName, DataTable columns, DataTable pk)
245 {
246 StringBuilder sb = new StringBuilder();
247 sb.AppendLine("SET ANSI_NULLS ON");
248 sb.AppendLine("SET QUOTED_IDENTIFIER OFF");
249 sb.AppendLine("GO");
250 sb.AppendLine("CREATE PROCEDURE " + tableName + "_Select");
251 for (int i = 0; i < pk.Rows.Count - 1; i++)
252 {
253 if (pk.Rows[i][1].ToString() != "nvarchar" && pk.Rows[i][1].ToString() != "nchar")
254 {
255 sb.AppendLine(" @" + pk.Rows[i][0].ToString() + GenerateProperSpaces(pk.Rows[i][0].ToString(), 18) + pk.Rows[i][1].ToString() + ",");
256 }
257 else
258 {
259 sb.AppendLine(" @" + pk.Rows[i][0].ToString() + GenerateProperSpaces(pk.Rows[i][0].ToString(), 18) + pk.Rows[i][1].ToString() + "(" + pk.Rows[i][2].ToString() + ")" + ",");
260 }
261 }
262 if (pk.Rows.Count != 0)
263 {
264 if (pk.Rows[pk.Rows.Count - 1][1].ToString() != "nvarchar" && pk.Rows[pk.Rows.Count - 1][1].ToString() != "nchar")
265 {
266 sb.AppendLine(" @" + pk.Rows[pk.Rows.Count - 1][0].ToString() + GenerateProperSpaces(pk.Rows[pk.Rows.Count - 1][0].ToString(), 18) + pk.Rows[pk.Rows.Count - 1][1].ToString());
267 }
268 else
269 {
270 sb.AppendLine(" @" + pk.Rows[pk.Rows.Count - 1][0].ToString() + GenerateProperSpaces(pk.Rows[pk.Rows.Count - 1][0].ToString(), 18) + pk.Rows[pk.Rows.Count - 1][1].ToString() + "(" + pk.Rows[pk.Rows.Count - 1][2].ToString() + ")");
271 }
272 }
273 sb.AppendLine("AS");
274 sb.AppendLine("BEGIN");
275 sb.AppendLine(" DECLARE @ErrorCode int");
276 sb.AppendLine(" SELECT @ErrorCode = 0");
277 sb.AppendLine();
278 sb.AppendLine(" DECLARE @TranStarted bit");
279 sb.AppendLine(" SELECT @TranStarted = 0");
280 sb.AppendLine();
281 sb.AppendLine(" IF(@@TRANCOUNT = 0)");
282 sb.AppendLine(" BEGIN");
283 sb.AppendLine(" BEGIN TRANSACTION");
284 sb.AppendLine(" SELECT @TranStarted = 1");
285 sb.AppendLine(" END");
286 sb.AppendLine(" ELSE");
287 sb.AppendLine(" SELECT @TranStarted = 0");
288 sb.AppendLine();
289 sb.Append(" SELECT ");
290 if (columns.Rows.Count == 1)
291 {
292 sb.AppendLine(" @" + columns.Rows[0][0].ToString() + " = [" + columns.Rows[0][0].ToString() + "]");
293 }
294 else
295 {
296 if (columns.Rows.Count == 2)
297 {
298 sb.AppendLine(" [" + columns.Rows[0][0].ToString() + "],");
299 sb.AppendLine(" [" + columns.Rows[1][0].ToString() + "]");
300 }
301 else
302 {
303 sb.AppendLine(" [" + columns.Rows[0][0].ToString() + "],");
304 for (int i = 1; i < columns.Rows.Count - 1; i++)
305 {
306 sb.AppendLine(" [" + columns.Rows[i][0].ToString() + "],");
307 }
308 sb.AppendLine(" [" + columns.Rows[columns.Rows.Count - 1][0].ToString() + "]");
309 }
310 }
311 sb.AppendLine(" FROM [" + tableName + "]");
312 if (pk.Rows.Count == 1)
313 {
314 sb.AppendLine(" WHERE [" + pk.Rows[0][0].ToString() + "] = @" + pk.Rows[0][0].ToString());
315 }
316 else
317 {
318 sb.AppendLine(" WHERE [" + pk.Rows[0][0].ToString() + "] = @" + pk.Rows[0][0].ToString());
319 for (int i = 1; i < pk.Rows.Count; i++)
320 {
321 sb.AppendLine(" AND [" + pk.Rows[i][0].ToString() + "] = @" + pk.Rows[i][0].ToString());
322 }
323 }
324 sb.AppendLine();
325 sb.AppendLine(" IF(@@ERROR <> 0)");
326 sb.AppendLine(" BEGIN");
327 sb.AppendLine(" SELECT @ErrorCode = -1");
328 sb.AppendLine(" GOTO Cleanup");
329 sb.AppendLine(" END");
330 sb.AppendLine();
331 sb.AppendLine(" IF(@TranStarted = 1)");
332 sb.AppendLine(" BEGIN");
333 sb.AppendLine(" SELECT @TranStarted = 0");
334 sb.AppendLine(" COMMIT TRANSACTION");
335 sb.AppendLine(" END");
336 sb.AppendLine();
337 sb.AppendLine("Cleanup:");
338 sb.AppendLine();
339 sb.AppendLine(" IF(@TranStarted = 1)");
340 sb.AppendLine(" BEGIN");
341 sb.AppendLine(" SET @TranStarted = 0");
342 sb.AppendLine(" ROLLBACK TRANSACTION");
343 sb.AppendLine(" END");
344 sb.AppendLine();
345 sb.AppendLine(" RETURN @ErrorCode");
346 sb.AppendLine();
347 sb.AppendLine("END");
348 return sb.ToString();
349 }
350
351 private string GenerateInsert(string tableName, DataTable columns, DataTable pk)
352 {
353 DataTable npk = Routine.GetNonePKColumns(columns, pk);
354 StringBuilder sb = new StringBuilder();
355 sb.AppendLine("SET ANSI_NULLS ON");
356 sb.AppendLine("SET QUOTED_IDENTIFIER OFF");
357 sb.AppendLine("GO");
358 sb.AppendLine("CREATE PROCEDURE " + tableName + "_Insert");
359 if (pk.Rows.Count == 1)//need to return id inserted
360 {
361 foreach (DataRow dr in npk.Rows)
362 {
363 if (dr[1].ToString() != "nvarchar" && dr[1].ToString() != "nchar")
364 {
365 sb.AppendLine(" @" + dr[0].ToString() + GenerateProperSpaces(dr[0].ToString(), 20) + dr[1].ToString() + ",");
366 }
367 else
368 {
369 sb.AppendLine(" @" + dr[0].ToString() + GenerateProperSpaces(dr[0].ToString(), 20) + dr[1].ToString() + "(" + dr[2].ToString() + ")" + ",");
370 }
371 }
372 if (pk.Rows[0][2].ToString() != "nvarchar" && pk.Rows[0][2].ToString() != "nchar")
373 {
374 sb.AppendLine(" @" + pk.Rows[0][0].ToString() + GenerateProperSpaces(pk.Rows[0][0].ToString(), 20) + pk.Rows[0][1].ToString() + " OUTPUT");
375 }
376 else
377 {
378 sb.AppendLine(" @" + pk.Rows[0][0].ToString() + GenerateProperSpaces(pk.Rows[0][0].ToString(), 20) + pk.Rows[0][1].ToString() + "(" + pk.Rows[0][2].ToString() + ")" + " OUTPUT");
379 }
380 }
381 else//no need to return id inserted
382 {
383 for (int i = 0; i < columns.Rows.Count - 1; i++)
384 {
385 if (columns.Rows[i][1].ToString() != "nvarchar" && columns.Rows[i][1].ToString() != "nchar")
386 {
387 sb.AppendLine(" @" + columns.Rows[i][0].ToString() + GenerateProperSpaces(columns.Rows[i][0].ToString(), 20) + columns.Rows[i][1].ToString() + ",");
388 }
389 else
390 {
391 sb.AppendLine(" @" + columns.Rows[i][0].ToString() + GenerateProperSpaces(columns.Rows[i][0].ToString(), 20) + columns.Rows[i][1].ToString() + "(" + columns.Rows[i][2].ToString() + ")" + ",");
392 }
393
394 }
395 if (columns.Rows.Count != 0)
396 {
397 if (columns.Rows[columns.Rows.Count - 1][1].ToString() != "nvarchar" && columns.Rows[columns.Rows.Count - 1][1].ToString() != "nchar")
398 {
399 sb.AppendLine(" @" + columns.Rows[columns.Rows.Count - 1][0].ToString() + GenerateProperSpaces(columns.Rows[columns.Rows.Count - 1][0].ToString(), 20) + columns.Rows[columns.Rows.Count - 1][1].ToString());
400 }
401 else
402 {
403 sb.AppendLine(" @" + columns.Rows[columns.Rows.Count - 1][0].ToString() + GenerateProperSpaces(columns.Rows[columns.Rows.Count - 1][0].ToString(), 20) + columns.Rows[columns.Rows.Count - 1][1].ToString() + "(" + columns.Rows[columns.Rows.Count - 1][2].ToString() + ")");
404 }
405 }
406 }
407 sb.AppendLine("AS");
408 sb.AppendLine("BEGIN");
409 sb.AppendLine(" DECLARE @ErrorCode int");
410 sb.AppendLine(" SELECT @ErrorCode = 0");
411 sb.AppendLine();
412 sb.AppendLine(" DECLARE @TranStarted bit");
413 sb.AppendLine(" SELECT @TranStarted = 0");
414 sb.AppendLine();
415 sb.AppendLine(" IF(@@TRANCOUNT = 0)");
416 sb.AppendLine(" BEGIN");
417 sb.AppendLine(" BEGIN TRANSACTION");
418 sb.AppendLine(" SELECT @TranStarted = 1");
419 sb.AppendLine(" END");
420 sb.AppendLine(" ELSE");
421 sb.AppendLine(" SELECT @TranStarted = 0");
422 sb.AppendLine();
423 sb.AppendLine(" INSERT INTO [" + tableName + "]");
424 sb.AppendLine(" (");
425 if (pk.Rows.Count == 1)//need to return id inserted
426 {
427 for (int i = 0; i < npk.Rows.Count - 1; i++)
428 {
429 sb.AppendLine(" [" + npk.Rows[i][0].ToString() + "],");
430 }
431 if (npk.Rows.Count != 0)
432 {
433 sb.AppendLine(" [" + npk.Rows[npk.Rows.Count - 1][0].ToString() + "]");
434 }
435 }
436 else
437 {
438 for (int i = 0; i < columns.Rows.Count - 1; i++)
439 {
440 sb.AppendLine(" [" + columns.Rows[i][0].ToString() + "],");
441 }
442 sb.AppendLine(" [" + columns.Rows[columns.Rows.Count - 1][0].ToString() + "]");
443 }
444 sb.AppendLine(" )");
445 sb.AppendLine(" VALUES");
446 sb.AppendLine(" (");
447 if (pk.Rows.Count == 1)//need to return id inserted
448 {
449 for (int i = 0; i < npk.Rows.Count - 1; i++)
450 {
451 sb.AppendLine(" @" + npk.Rows[i][0].ToString() + ",");
452 }
453 if (npk.Rows.Count != 0)
454 {
455 sb.AppendLine(" @" + npk.Rows[npk.Rows.Count - 1][0].ToString());
456 }
457 }
458 else
459 {
460 for (int i = 0; i < columns.Rows.Count - 1; i++)
461 {
462 sb.AppendLine(" @" + columns.Rows[i][0].ToString() + ",");
463 }
464 if (columns.Rows.Count != 0)
465 {
466 sb.AppendLine(" @" + columns.Rows[columns.Rows.Count - 1][0].ToString());
467 }
468 }
469 sb.AppendLine(" )");
470 sb.AppendLine();
471 sb.AppendLine(" IF(@@ERROR <> 0)");
472 sb.AppendLine(" BEGIN");
473 sb.AppendLine(" SELECT @ErrorCode = -1");
474 sb.AppendLine(" GOTO Cleanup");
475 sb.AppendLine(" END");
476 sb.AppendLine();
477 if (pk.Rows.Count == 1)//need to return id inserted
478 {
479 sb.AppendLine(" SELECT @" + pk.Rows[0][0].ToString() + " = @@identity");
480 sb.AppendLine();
481 }
482 sb.AppendLine(" IF(@TranStarted = 1)");
483 sb.AppendLine(" BEGIN");
484 sb.AppendLine(" SELECT @TranStarted = 0");
485 sb.AppendLine(" COMMIT TRANSACTION");
486 sb.AppendLine(" END");
487 sb.AppendLine();
488 sb.AppendLine("Cleanup:");
489 sb.AppendLine();
490 sb.AppendLine(" IF(@TranStarted = 1)");
491 sb.AppendLine(" BEGIN");
492 sb.AppendLine(" SET @TranStarted = 0");
493 sb.AppendLine(" ROLLBACK TRANSACTION");
494 sb.AppendLine(" END");
495 sb.AppendLine();
496 sb.AppendLine(" RETURN @ErrorCode");
497 sb.AppendLine();
498 sb.AppendLine("END");
499 return sb.ToString();
500 }
501
502 private string GenerateUpdate(string tableName, DataTable columns, DataTable pk)
503 {
504 DataTable npk = Routine.GetNonePKColumns(columns, pk);
505 if (npk.Rows.Count == 0)
506 {
507 return string.Empty;
508 }
509 StringBuilder sb = new StringBuilder();
510 sb.AppendLine("SET ANSI_NULLS ON");
511 sb.AppendLine("SET QUOTED_IDENTIFIER OFF");
512 sb.AppendLine("GO");
513 sb.AppendLine("CREATE PROCEDURE " + tableName + "_Update");
514 for (int i = 0; i < columns.Rows.Count - 1; i++)
515 {
516 if (columns.Rows[i][1].ToString() != "nvarchar" && columns.Rows[i][1].ToString() != "nchar")
517 {
518 sb.AppendLine(" @" + columns.Rows[i][0].ToString() + GenerateProperSpaces(columns.Rows[i][0].ToString(), 20) + columns.Rows[i][1].ToString() + ",");
519 }
520 else
521 {
522 sb.AppendLine(" @" + columns.Rows[i][0].ToString() + GenerateProperSpaces(columns.Rows[i][0].ToString(), 20) + columns.Rows[i][1].ToString() + "(" + columns.Rows[i][2].ToString() + ")" + ",");
523 }
524 }
525 if (columns.Rows.Count != 0)
526 {
527 if (columns.Rows[columns.Rows.Count - 1][1].ToString() != "nvarchar" && columns.Rows[columns.Rows.Count - 1][1].ToString() != "nchar")
528 {
529 sb.AppendLine(" @" + columns.Rows[columns.Rows.Count - 1][0].ToString() + GenerateProperSpaces(columns.Rows[columns.Rows.Count - 1][0].ToString(), 20) + columns.Rows[columns.Rows.Count - 1][1].ToString());
530 }
531 else
532 {
533 sb.AppendLine(" @" + columns.Rows[columns.Rows.Count - 1][0].ToString() + GenerateProperSpaces(columns.Rows[columns.Rows.Count - 1][0].ToString(), 20) + columns.Rows[columns.Rows.Count - 1][1].ToString() + "(" + columns.Rows[columns.Rows.Count - 1][2].ToString() + ")");
534 }
535 }
536 sb.AppendLine("AS");
537 sb.AppendLine("BEGIN");
538 sb.AppendLine(" DECLARE @ErrorCode int");
539 sb.AppendLine(" SELECT @ErrorCode = 0");
540 sb.AppendLine();
541 sb.AppendLine(" DECLARE @TranStarted bit");
542 sb.AppendLine(" SELECT @TranStarted = 0");
543 sb.AppendLine();
544 sb.AppendLine(" IF(@@TRANCOUNT = 0)");
545 sb.AppendLine(" BEGIN");
546 sb.AppendLine(" BEGIN TRANSACTION");
547 sb.AppendLine(" SELECT @TranStarted = 1");
548 sb.AppendLine(" END");
549 sb.AppendLine(" ELSE");
550 sb.AppendLine(" SELECT @TranStarted = 0");
551 sb.AppendLine();
552 sb.AppendLine(" UPDATE [" + tableName + "]");
553 if (npk.Rows.Count == 1)
554 {
555 sb.AppendLine(" SET [" + npk.Rows[0][0].ToString() + "] = @" + npk.Rows[0][0].ToString());
556 }
557 else if (npk.Rows.Count > 1)
558 {
559 sb.AppendLine(" SET [" + npk.Rows[0][0].ToString() + "] = @" + npk.Rows[0][0].ToString() + ",");
560 if (npk.Rows.Count > 2)
561 {
562 for (int i = 1; i < npk.Rows.Count - 1; i++)
563 {
564 sb.AppendLine(" [" + npk.Rows[i][0].ToString() + "] = @" + npk.Rows[i][0].ToString() + ",");
565 }
566 sb.AppendLine(" [" + npk.Rows[npk.Rows.Count - 1][0].ToString() + "] = @" + npk.Rows[npk.Rows.Count - 1][0].ToString());
567 }
568 else
569 {
570 sb.AppendLine(" [" + npk.Rows[1][0].ToString() + "] = @" + npk.Rows[1][0].ToString());
571 }
572 }
573 if (pk.Rows.Count == 1)
574 {
575 sb.AppendLine(" WHERE [" + pk.Rows[0][0].ToString() + "] = @" + pk.Rows[0][0].ToString());
576 }
577 else
578 {
579 sb.AppendLine(" WHERE [" + pk.Rows[0][0].ToString() + "] = @" + pk.Rows[0][0].ToString());
580 for (int i = 1; i < pk.Rows.Count; i++)
581 {
582 sb.AppendLine(" AND [" + pk.Rows[i][0].ToString() + "] = @" + pk.Rows[i][0].ToString());
583 }
584 }
585 sb.AppendLine();
586 sb.AppendLine(" IF(@@ERROR <> 0)");
587 sb.AppendLine(" BEGIN");
588 sb.AppendLine(" SELECT @ErrorCode = -1");
589 sb.AppendLine(" GOTO Cleanup");
590 sb.AppendLine(" END");
591 sb.AppendLine();
592 sb.AppendLine(" IF(@TranStarted = 1)");
593 sb.AppendLine(" BEGIN");
594 sb.AppendLine(" SELECT @TranStarted = 0");
595 sb.AppendLine(" COMMIT TRANSACTION");
596 sb.AppendLine(" END");
597 sb.AppendLine();
598 sb.AppendLine("Cleanup:");
599 sb.AppendLine();
600 sb.AppendLine(" IF(@TranStarted = 1)");
601 sb.AppendLine(" BEGIN");
602 sb.AppendLine(" SET @TranStarted = 0");
603 sb.AppendLine(" ROLLBACK TRANSACTION");
604 sb.AppendLine(" END");
605 sb.AppendLine();
606 sb.AppendLine(" RETURN @ErrorCode");
607 sb.AppendLine();
608 sb.AppendLine("END");
609 return sb.ToString();
610 }
611
612 private string GenerateProperSpaces(string input, int totalSpaces)
613 {
614 if (input.Length >= totalSpaces)
615 {
616 return " ";
617 }
618 StringBuilder sb = new StringBuilder();
619 for (int i = 0; i < totalSpaces - input.Length; i++)
620 {
621 sb.Append(" ");
622 }
623 return sb.ToString();
624 }
625 }
626}
627
using System;2
using System.Collections.Generic;3
using System.Text;4
using System.Data;5
using System.Data.SqlClient;6
7
namespace JoeyCodeGenerator8
{9
public class MSSQL : IDataBase10
{11
private string _connectionString;12
13
public MSSQL(String connectionString)14
{15
this.ConnectionString = connectionString;16
}17
18
public string ConnectionString19
{20
get21
{22
return this._connectionString;23
}24
set25
{26
this._connectionString = value;27
}28
}29
30
public DataTable GetColumnsFromTable(string tableName)31
{32
StringBuilder sb = new StringBuilder();33
sb.AppendLine("SELECT columnTable.name,");34
sb.AppendLine(" typeTable.name,");35
sb.AppendLine(" REPLACE(CAST(COLUMNPROPERTY(columnTable.id, columnTable.name, 'PRECISION') AS nvarchar),'-1','4000')");36
sb.AppendLine(" FROM syscolumns columnTable");37
sb.AppendLine(" JOIN");38
sb.AppendLine(" systypes typeTable");39
sb.AppendLine(" ON columnTable.xtype = typeTable.xtype");40
sb.AppendLine(" WHERE columnTable.id = object_id(N'" + tableName + "')");41
sb.AppendLine(" AND typeTable.name <> 'sysname'");42
DataTable dt = new DataTable();43
using (SqlConnection conn = new SqlConnection(this.ConnectionString))44
{45
using (SqlCommand comm = new SqlCommand())46
{47
comm.CommandText = sb.ToString();48
comm.Connection = conn;49
conn.Open();50
using (SqlDataAdapter sda = new SqlDataAdapter())51
{52
sda.SelectCommand = comm;53
sda.Fill(dt);54
}55
}56
}57
return dt;58
}59
60
public DataTable GetPKFromTable(string tableName)61
{62
StringBuilder sb = new StringBuilder();63
sb.AppendLine("SELECT PrimaryKey = columnTable.name,");64
sb.AppendLine(" KeyType = typeTable.name,");65
sb.AppendLine(" REPLACE(CAST(COLUMNPROPERTY(columnTable.id, columnTable.name, 'PRECISION') AS nvarchar),'-1','4000')");66
sb.AppendLine(" FROM syscolumns columnTable");67
sb.AppendLine(" JOIN systypes typeTable");68
sb.AppendLine(" ON columnTable.xtype = typeTable.xtype");69
sb.AppendLine(" JOIN sysobjects objectTable");70
sb.AppendLine(" ON columnTable.id = objectTable.id");71
sb.AppendLine(" AND objectTable.xtype = 'U'");72
sb.AppendLine(" AND objectTable.name <> 'dtproperties'");73
sb.AppendLine(" WHERE EXISTS");74
sb.AppendLine(" (");75
sb.AppendLine(" SELECT 1");76
sb.AppendLine(" FROM sysobjects");77
sb.AppendLine(" WHERE xtype='PK'");78
sb.AppendLine(" AND name IN");79
sb.AppendLine(" (");80
sb.AppendLine(" SELECT name");81
sb.AppendLine(" FROM sysindexes");82
sb.AppendLine(" WHERE indid IN");83
sb.AppendLine(" (");84
sb.AppendLine(" SELECT indid");85
sb.AppendLine(" FROM sysindexkeys");86
sb.AppendLine(" WHERE id = columnTable.id");87
sb.AppendLine(" AND colid = columnTable.colid");88
sb.AppendLine(" )");89
sb.AppendLine(" )");90
sb.AppendLine(" )");91
sb.AppendLine(" AND objectTable.name='" + tableName + "'");92
sb.AppendLine(" AND typeTable.name <> 'sysname'");93
DataTable dt = new DataTable();94
using (SqlConnection conn = new SqlConnection(this.ConnectionString))95
{96
using (SqlCommand comm = new SqlCommand())97
{98
comm.CommandText = sb.ToString();99
comm.Connection = conn;100
conn.Open();101
using (SqlDataAdapter sda = new SqlDataAdapter())102
{103
sda.SelectCommand = comm;104
sda.Fill(dt);105
}106
}107
}108
return dt;109
}110
111
public List<string> GetTableNames()112
{113
List<string> tableNames = new List<string>();114
using (SqlConnection conn = new SqlConnection(this.ConnectionString))115
{116
using (SqlCommand comm = new SqlCommand())117
{118
comm.CommandText = "SELECT table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE'";119
comm.Connection = conn;120
conn.Open();121
using (SqlDataReader sdr = comm.ExecuteReader(CommandBehavior.CloseConnection))122
{123
while (sdr.Read())124
{125
tableNames.Add(sdr[0].ToString());126
}127
}128
}129
}130
return tableNames;131
}132
133
public void GenerateStoredProcedures(string tableName, DataTable columns, DataTable pk, string folderPath)134
{135
string filePath = folderPath + "\\MSSQL\\";136
string content;137
content = GenerateInsert(tableName, columns, pk);138
if (!string.IsNullOrEmpty(content))139
{140
Routine.WriteFile(filePath + tableName + "_Insert.sp", content);141
}142
content = GenerateSelect(tableName, columns, pk);143
if (!string.IsNullOrEmpty(content))144
{145
Routine.WriteFile(filePath + tableName + "_Select.sp", content);146
}147
content = GenerateUpdate(tableName, columns, pk);148
if (!string.IsNullOrEmpty(content))149
{150
Routine.WriteFile(filePath + tableName + "_Update.sp", content);151
}152
content = GenerateDelete(tableName, pk);153
if (!string.IsNullOrEmpty(content))154
{155
Routine.WriteFile(filePath + tableName + "_Delete.sp", content);156
}157
}158
159
private string GenerateDelete(string tableName, DataTable pk)160
{161
StringBuilder sb = new StringBuilder();162
sb.AppendLine("SET ANSI_NULLS ON");163
sb.AppendLine("SET QUOTED_IDENTIFIER OFF");164
sb.AppendLine("GO");165
sb.AppendLine("CREATE PROCEDURE " + tableName + "_Delete");166
for (int i = 0; i < pk.Rows.Count - 1; i++)167
{168
if (pk.Rows[i][1].ToString() != "nvarchar" && pk.Rows[i][1].ToString() != "nchar")169
{170
sb.AppendLine(" @" + pk.Rows[i][0].ToString() + GenerateProperSpaces(pk.Rows[i][0].ToString(), 18) + pk.Rows[i][1].ToString() + ",");171
}172
else173
{174
sb.AppendLine(" @" + pk.Rows[i][0].ToString() + GenerateProperSpaces(pk.Rows[i][0].ToString(), 18) + pk.Rows[i][1].ToString() + "(" + pk.Rows[i][2].ToString() + ")" + ",");175
}176
}177
if (pk.Rows.Count != 0)178
{179
if (pk.Rows[pk.Rows.Count - 1][1].ToString() != "nvarchar" && pk.Rows[pk.Rows.Count - 1][1].ToString() != "nchar")180
{181
sb.AppendLine(" @" + pk.Rows[pk.Rows.Count - 1][0].ToString() + GenerateProperSpaces(pk.Rows[pk.Rows.Count - 1][0].ToString(), 18) + pk.Rows[pk.Rows.Count - 1][1].ToString());182
}183
else184
{185
sb.AppendLine(" @" + pk.Rows[pk.Rows.Count - 1][0].ToString() + GenerateProperSpaces(pk.Rows[pk.Rows.Count - 1][0].ToString(), 18) + pk.Rows[pk.Rows.Count - 1][1].ToString() + "(" + pk.Rows[pk.Rows.Count - 1][2].ToString() + ")");186
}187
}188
sb.AppendLine("AS");189
sb.AppendLine("BEGIN");190
sb.AppendLine(" DECLARE @ErrorCode int");191
sb.AppendLine(" SELECT @ErrorCode = 0");192
sb.AppendLine();193
sb.AppendLine(" DECLARE @TranStarted bit");194
sb.AppendLine(" SELECT @TranStarted = 0");195
sb.AppendLine();196
sb.AppendLine(" IF(@@TRANCOUNT = 0)");197
sb.AppendLine(" BEGIN");198
sb.AppendLine(" BEGIN TRANSACTION");199
sb.AppendLine(" SELECT @TranStarted = 1");200
sb.AppendLine(" END");201
sb.AppendLine(" ELSE");202
sb.AppendLine(" SELECT @TranStarted = 0");203
sb.AppendLine();204
sb.AppendLine(" DELETE FROM [" + tableName + "]");205
if (pk.Rows.Count == 1)206
{207
sb.AppendLine(" WHERE [" + pk.Rows[0][0].ToString() + "] = @" + pk.Rows[0][0].ToString());208
}209
else210
{211
sb.AppendLine(" WHERE [" + pk.Rows[0][0].ToString() + "] = @" + pk.Rows[0][0].ToString());212
for (int i = 1; i < pk.Rows.Count; i++)213
{214
sb.AppendLine(" AND [" + pk.Rows[i][0].ToString() + "] = @" + pk.Rows[i][0].ToString());215
}216
}217
sb.AppendLine();218
sb.AppendLine(" IF(@@ERROR <> 0)");219
sb.AppendLine(" BEGIN");220
sb.AppendLine(" SELECT @ErrorCode = -1");221
sb.AppendLine(" GOTO Cleanup");222
sb.AppendLine(" END");223
sb.AppendLine();224
sb.AppendLine(" IF(@TranStarted = 1)");225
sb.AppendLine(" BEGIN");226
sb.AppendLine(" SELECT @TranStarted = 0");227
sb.AppendLine(" COMMIT TRANSACTION");228
sb.AppendLine(" END");229
sb.AppendLine();230
sb.AppendLine("Cleanup:");231
sb.AppendLine();232
sb.AppendLine(" IF(@TranStarted = 1)");233
sb.AppendLine(" BEGIN");234
sb.AppendLine(" SET @TranStarted = 0");235
sb.AppendLine(" ROLLBACK TRANSACTION");236
sb.AppendLine(" END");237
sb.AppendLine();238
sb.AppendLine(" RETURN @ErrorCode");239
sb.AppendLine();240
sb.AppendLine("END");241
return sb.ToString();242
}243
244
private string GenerateSelect(string tableName, DataTable columns, DataTable pk)245
{246
StringBuilder sb = new StringBuilder();247
sb.AppendLine("SET ANSI_NULLS ON");248
sb.AppendLine("SET QUOTED_IDENTIFIER OFF");249
sb.AppendLine("GO");250
sb.AppendLine("CREATE PROCEDURE " + tableName + "_Select");251
for (int i = 0; i < pk.Rows.Count - 1; i++)252
{253
if (pk.Rows[i][1].ToString() != "nvarchar" && pk.Rows[i][1].ToString() != "nchar")254
{255
sb.AppendLine(" @" + pk.Rows[i][0].ToString() + GenerateProperSpaces(pk.Rows[i][0].ToString(), 18) + pk.Rows[i][1].ToString() + ",");256
}257
else258
{259
sb.AppendLine(" @" + pk.Rows[i][0].ToString() + GenerateProperSpaces(pk.Rows[i][0].ToString(), 18) + pk.Rows[i][1].ToString() + "(" + pk.Rows[i][2].ToString() + ")" + ",");260
}261
}262
if (pk.Rows.Count != 0)263
{264
if (pk.Rows[pk.Rows.Count - 1][1].ToString() != "nvarchar" && pk.Rows[pk.Rows.Count - 1][1].ToString() != "nchar")265
{266
sb.AppendLine(" @" + pk.Rows[pk.Rows.Count - 1][0].ToString() + GenerateProperSpaces(pk.Rows[pk.Rows.Count - 1][0].ToString(), 18) + pk.Rows[pk.Rows.Count - 1][1].ToString());267
}268
else269
{270
sb.AppendLine(" @" + pk.Rows[pk.Rows.Count - 1][0].ToString() + GenerateProperSpaces(pk.Rows[pk.Rows.Count - 1][0].ToString(), 18) + pk.Rows[pk.Rows.Count - 1][1].ToString() + "(" + pk.Rows[pk.Rows.Count - 1][2].ToString() + ")");271
}272
}273
sb.AppendLine("AS");274
sb.AppendLine("BEGIN");275
sb.AppendLine(" DECLARE @ErrorCode int");276
sb.AppendLine(" SELECT @ErrorCode = 0");277
sb.AppendLine();278
sb.AppendLine(" DECLARE @TranStarted bit");279
sb.AppendLine(" SELECT @TranStarted = 0");280
sb.AppendLine();281
sb.AppendLine(" IF(@@TRANCOUNT = 0)");282
sb.AppendLine(" BEGIN");283
sb.AppendLine(" BEGIN TRANSACTION");284
sb.AppendLine(" SELECT @TranStarted = 1");285
sb.AppendLine(" END");286
sb.AppendLine(" ELSE");287
sb.AppendLine(" SELECT @TranStarted = 0");288
sb.AppendLine();289
sb.Append(" SELECT ");290
if (columns.Rows.Count == 1)291
{292
sb.AppendLine(" @" + columns.Rows[0][0].ToString() + " = [" + columns.Rows[0][0].ToString() + "]");293
}294
else295
{296
if (columns.Rows.Count == 2)297
{298
sb.AppendLine(" [" + columns.Rows[0][0].ToString() + "],");299
sb.AppendLine(" [" + columns.Rows[1][0].ToString() + "]");300
}301
else302
{303
sb.AppendLine(" [" + columns.Rows[0][0].ToString() + "],");304
for (int i = 1; i < columns.Rows.Count - 1; i++)305
{306
sb.AppendLine(" [" + columns.Rows[i][0].ToString() + "],");307
}308
sb.AppendLine(" [" + columns.Rows[columns.Rows.Count - 1][0].ToString() + "]");309
}310
}311
sb.AppendLine(" FROM [" + tableName + "]");312
if (pk.Rows.Count == 1)313
{314
sb.AppendLine(" WHERE [" + pk.Rows[0][0].ToString() + "] = @" + pk.Rows[0][0].ToString());315
}316
else317
{318
sb.AppendLine(" WHERE [" + pk.Rows[0][0].ToString() + "] = @" + pk.Rows[0][0].ToString());319
for (int i = 1; i < pk.Rows.Count; i++)320
{321
sb.AppendLine(" AND [" + pk.Rows[i][0].ToString() + "] = @" + pk.Rows[i][0].ToString());322
}323
}324
sb.AppendLine();325
sb.AppendLine(" IF(@@ERROR <> 0)");326
sb.AppendLine(" BEGIN");327
sb.AppendLine(" SELECT @ErrorCode = -1");328
sb.AppendLine(" GOTO Cleanup");329
sb.AppendLine(" END");330
sb.AppendLine();331
sb.AppendLine(" IF(@TranStarted = 1)");332
sb.AppendLine(" BEGIN");333
sb.AppendLine(" SELECT @TranStarted = 0");334
sb.AppendLine(" COMMIT TRANSACTION");335
sb.AppendLine(" END");336
sb.AppendLine();337
sb.AppendLine("Cleanup:");338
sb.AppendLine();339
sb.AppendLine(" IF(@TranStarted = 1)");340
sb.AppendLine(" BEGIN");341
sb.AppendLine(" SET @TranStarted = 0");342
sb.AppendLine(" ROLLBACK TRANSACTION");343
sb.AppendLine(" END");344
sb.AppendLine();345
sb.AppendLine(" RETURN @ErrorCode");346
sb.AppendLine();347
sb.AppendLine("END");348
return sb.ToString();349
}350
351
private string GenerateInsert(string tableName, DataTable columns, DataTable pk)352
{353
DataTable npk = Routine.GetNonePKColumns(columns, pk);354
StringBuilder sb = new StringBuilder();355
sb.AppendLine("SET ANSI_NULLS ON");356
sb.AppendLine("SET QUOTED_IDENTIFIER OFF");357
sb.AppendLine("GO");358
sb.AppendLine("CREATE PROCEDURE " + tableName + "_Insert");359
if (pk.Rows.Count == 1)//need to return id inserted360
{361
foreach (DataRow dr in npk.Rows)362
{363
if (dr[1].ToString() != "nvarchar" && dr[1].ToString() != "nchar")364
{365
sb.AppendLine(" @" + dr[0].ToString() + GenerateProperSpaces(dr[0].ToString(), 20) + dr[1].ToString() + ",");366
}367
else368
{369
sb.AppendLine(" @" + dr[0].ToString() + GenerateProperSpaces(dr[0].ToString(), 20) + dr[1].ToString() + "(" + dr[2].ToString() + ")" + ",");370
}371
}372
if (pk.Rows[0][2].ToString() != "nvarchar" && pk.Rows[0][2].ToString() != "nchar")373
{374
sb.AppendLine(" @" + pk.Rows[0][0].ToString() + GenerateProperSpaces(pk.Rows[0][0].ToString(), 20) + pk.Rows[0][1].ToString() + " OUTPUT");375
}376
else377
{378
sb.AppendLine(" @" + pk.Rows[0][0].ToString() + GenerateProperSpaces(pk.Rows[0][0].ToString(), 20) + pk.Rows[0][1].ToString() + "(" + pk.Rows[0][2].ToString() + ")" + " OUTPUT");379
}380
}381
else//no need to return id inserted382
{383
for (int i = 0; i < columns.Rows.Count - 1; i++)384
{385
if (columns.Rows[i][1].ToString() != "nvarchar" && columns.Rows[i][1].ToString() != "nchar")386
{387
sb.AppendLine(" @" + columns.Rows[i][0].ToString() + GenerateProperSpaces(columns.Rows[i][0].ToString(), 20) + columns.Rows[i][1].ToString() + ",");388
}389
else390
{391
sb.AppendLine(" @" + columns.Rows[i][0].ToString() + GenerateProperSpaces(columns.Rows[i][0].ToString(), 20) + columns.Rows[i][1].ToString() + "(" + columns.Rows[i][2].ToString() + ")" + ",");392
}393
394
}395
if (columns.Rows.Count != 0)396
{397
if (columns.Rows[columns.Rows.Count - 1][1].ToString() != "nvarchar" && columns.Rows[columns.Rows.Count - 1][1].ToString() != "nchar")398
{399
sb.AppendLine(" @" + columns.Rows[columns.Rows.Count - 1][0].ToString() + GenerateProperSpaces(columns.Rows[columns.Rows.Count - 1][0].ToString(), 20) + columns.Rows[columns.Rows.Count - 1][1].ToString());400
}401
else402
{403
sb.AppendLine(" @" + columns.Rows[columns.Rows.Count - 1][0].ToString() + GenerateProperSpaces(columns.Rows[columns.Rows.Count - 1][0].ToString(), 20) + columns.Rows[columns.Rows.Count - 1][1].ToString() + "(" + columns.Rows[columns.Rows.Count - 1][2].ToString() + ")");404
}405
}406
}407
sb.AppendLine("AS");408
sb.AppendLine("BEGIN");409
sb.AppendLine(" DECLARE @ErrorCode int");410
sb.AppendLine(" SELECT @ErrorCode = 0");411
sb.AppendLine();412
sb.AppendLine(" DECLARE @TranStarted bit");413
sb.AppendLine(" SELECT @TranStarted = 0");414
sb.AppendLine();415
sb.AppendLine(" IF(@@TRANCOUNT = 0)");416
sb.AppendLine(" BEGIN");417
sb.AppendLine(" BEGIN TRANSACTION");418
sb.AppendLine(" SELECT @TranStarted = 1");419
sb.AppendLine(" END");420
sb.AppendLine(" ELSE");421
sb.AppendLine(" SELECT @TranStarted = 0");422
sb.AppendLine();423
sb.AppendLine(" INSERT INTO [" + tableName + "]");424
sb.AppendLine(" (");425
if (pk.Rows.Count == 1)//need to return id inserted426
{427
for (int i = 0; i < npk.Rows.Count - 1; i++)428
{429
sb.AppendLine(" [" + npk.Rows[i][0].ToString() + "],");430
}431
if (npk.Rows.Count != 0)432
{433
sb.AppendLine(" [" + npk.Rows[npk.Rows.Count - 1][0].ToString() + "]");434
}435
}436
else437
{438
for (int i = 0; i < columns.Rows.Count - 1; i++)439
{440
sb.AppendLine(" [" + columns.Rows[i][0].ToString() + "],");441
}442
sb.AppendLine(" [" + columns.Rows[columns.Rows.Count - 1][0].ToString() + "]");443
}444
sb.AppendLine(" )");445
sb.AppendLine(" VALUES");446
sb.AppendLine(" (");447
if (pk.Rows.Count == 1)//need to return id inserted448
{449
for (int i = 0; i < npk.Rows.Count - 1; i++)450
{451
sb.AppendLine(" @" + npk.Rows[i][0].ToString() + ",");452
}453
if (npk.Rows.Count != 0)454
{455
sb.AppendLine(" @" + npk.Rows[npk.Rows.Count - 1][0].ToString());456
}457
}458
else459
{460
for (int i = 0; i < columns.Rows.Count - 1; i++)461
{462
sb.AppendLine(" @" + columns.Rows[i][0].ToString() + ",");463
}464
if (columns.Rows.Count != 0)465
{466
sb.AppendLine(" @" + columns.Rows[columns.Rows.Count - 1][0].ToString());467
}468
}469
sb.AppendLine(" )");470
sb.AppendLine();471
sb.AppendLine(" IF(@@ERROR <> 0)");472
sb.AppendLine(" BEGIN");473
sb.AppendLine(" SELECT @ErrorCode = -1");474
sb.AppendLine(" GOTO Cleanup");475
sb.AppendLine(" END");476
sb.AppendLine();477
if (pk.Rows.Count == 1)//need to return id inserted478
{479
sb.AppendLine(" SELECT @" + pk.Rows[0][0].ToString() + " = @@identity");480
sb.AppendLine();481
}482
sb.AppendLine(" IF(@TranStarted = 1)");483
sb.AppendLine(" BEGIN");484
sb.AppendLine(" SELECT @TranStarted = 0");485
sb.AppendLine(" COMMIT TRANSACTION");486
sb.AppendLine(" END");487
sb.AppendLine();488
sb.AppendLine("Cleanup:");489
sb.AppendLine();490
sb.AppendLine(" IF(@TranStarted = 1)");491
sb.AppendLine(" BEGIN");492
sb.AppendLine(" SET @TranStarted = 0");493
sb.AppendLine(" ROLLBACK TRANSACTION");494
sb.AppendLine(" END");495
sb.AppendLine();496
sb.AppendLine(" RETURN @ErrorCode");497
sb.AppendLine();498
sb.AppendLine("END");499
return sb.ToString();500
}501
502
private string GenerateUpdate(string tableName, DataTable columns, DataTable pk)503
{504
DataTable npk = Routine.GetNonePKColumns(columns, pk);505
if (npk.Rows.Count == 0)506
{507
return string.Empty;508
}509
StringBuilder sb = new StringBuilder();510
sb.AppendLine("SET ANSI_NULLS ON");511
sb.AppendLine("SET QUOTED_IDENTIFIER OFF");512
sb.AppendLine("GO");513
sb.AppendLine("CREATE PROCEDURE " + tableName + "_Update");514
for (int i = 0; i < columns.Rows.Count - 1; i++)515
{516
if (columns.Rows[i][1].ToString() != "nvarchar" && columns.Rows[i][1].ToString() != "nchar")517
{518
sb.AppendLine(" @" + columns.Rows[i][0].ToString() + GenerateProperSpaces(columns.Rows[i][0].ToString(), 20) + columns.Rows[i][1].ToString() + ",");519
}520
else521
{522
sb.AppendLine(" @" + columns.Rows[i][0].ToString() + GenerateProperSpaces(columns.Rows[i][0].ToString(), 20) + columns.Rows[i][1].ToString() + "(" + columns.Rows[i][2].ToString() + ")" + ",");523
}524
}525
if (columns.Rows.Count != 0)526
{527
if (columns.Rows[columns.Rows.Count - 1][1].ToString() != "nvarchar" && columns.Rows[columns.Rows.Count - 1][1].ToString() != "nchar")528
{529
sb.AppendLine(" @" + columns.Rows[columns.Rows.Count - 1][0].ToString() + GenerateProperSpaces(columns.Rows[columns.Rows.Count - 1][0].ToString(), 20) + columns.Rows[columns.Rows.Count - 1][1].ToString());530
}531
else532
{533
sb.AppendLine(" @" + columns.Rows[columns.Rows.Count - 1][0].ToString() + GenerateProperSpaces(columns.Rows[columns.Rows.Count - 1][0].ToString(), 20) + columns.Rows[columns.Rows.Count - 1][1].ToString() + "(" + columns.Rows[columns.Rows.Count - 1][2].ToString() + ")");534
}535
}536
sb.AppendLine("AS");537
sb.AppendLine("BEGIN");538
sb.AppendLine(" DECLARE @ErrorCode int");539
sb.AppendLine(" SELECT @ErrorCode = 0");540
sb.AppendLine();541
sb.AppendLine(" DECLARE @TranStarted bit");542
sb.AppendLine(" SELECT @TranStarted = 0");543
sb.AppendLine();544
sb.AppendLine(" IF(@@TRANCOUNT = 0)");545
sb.AppendLine(" BEGIN");546
sb.AppendLine(" BEGIN TRANSACTION");547
sb.AppendLine(" SELECT @TranStarted = 1");548
sb.AppendLine(" END");549
sb.AppendLine(" ELSE");550
sb.AppendLine(" SELECT @TranStarted = 0");551
sb.AppendLine();552
sb.AppendLine(" UPDATE [" + tableName + "]");553
if (npk.Rows.Count == 1)554
{555
sb.AppendLine(" SET [" + npk.Rows[0][0].ToString() + "] = @" + npk.Rows[0][0].ToString());556
}557
else if (npk.Rows.Count > 1)558
{559
sb.AppendLine(" SET [" + npk.Rows[0][0].ToString() + "] = @" + npk.Rows[0][0].ToString() + ",");560
if (npk.Rows.Count > 2)561
{562
for (int i = 1; i < npk.Rows.Count - 1; i++)563
{564
sb.AppendLine(" [" + npk.Rows[i][0].ToString() + "] = @" + npk.Rows[i][0].ToString() + ",");565
}566
sb.AppendLine(" [" + npk.Rows[npk.Rows.Count - 1][0].ToString() + "] = @" + npk.Rows[npk.Rows.Count - 1][0].ToString());567
}568
else569
{570
sb.AppendLine(" [" + npk.Rows[1][0].ToString() + "] = @" + npk.Rows[1][0].ToString());571
}572
}573
if (pk.Rows.Count == 1)574
{575
sb.AppendLine(" WHERE [" + pk.Rows[0][0].ToString() + "] = @" + pk.Rows[0][0].ToString());576
}577
else578
{579
sb.AppendLine(" WHERE [" + pk.Rows[0][0].ToString() + "] = @" + pk.Rows[0][0].ToString());580
for (int i = 1; i < pk.Rows.Count; i++)581
{582
sb.AppendLine(" AND [" + pk.Rows[i][0].ToString() + "] = @" + pk.Rows[i][0].ToString());583
}584
}585
sb.AppendLine();586
sb.AppendLine(" IF(@@ERROR <> 0)");587
sb.AppendLine(" BEGIN");588
sb.AppendLine(" SELECT @ErrorCode = -1");589
sb.AppendLine(" GOTO Cleanup");590
sb.AppendLine(" END");591
sb.AppendLine();592
sb.AppendLine(" IF(@TranStarted = 1)");593
sb.AppendLine(" BEGIN");594
sb.AppendLine(" SELECT @TranStarted = 0");595
sb.AppendLine(" COMMIT TRANSACTION");596
sb.AppendLine(" END");597
sb.AppendLine();598
sb.AppendLine("Cleanup:");599
sb.AppendLine();600
sb.AppendLine(" IF(@TranStarted = 1)");601
sb.AppendLine(" BEGIN");602
sb.AppendLine(" SET @TranStarted = 0");603
sb.AppendLine(" ROLLBACK TRANSACTION");604
sb.AppendLine(" END");605
sb.AppendLine();606
sb.AppendLine(" RETURN @ErrorCode");607
sb.AppendLine();608
sb.AppendLine("END");609
return sb.ToString();610
}611
612
private string GenerateProperSpaces(string input, int totalSpaces)613
{614
if (input.Length >= totalSpaces)615
{616
return " ";617
}618
StringBuilder sb = new StringBuilder();619
for (int i = 0; i < totalSpaces - input.Length; i++)620
{621
sb.Append(" ");622
}623
return sb.ToString();624
}625
}626
}627
1using System;
2
3namespace JoeyCodeGenerator
4{
5 [Flags]
6 public enum ProgrammingLanguageType
7 {
8 Default = 0,
9 CSharp = 1,
10 VBDotNet = 2,
11 Java = 3
12 }
13}
using System;2
3
namespace JoeyCodeGenerator4
{5
[Flags]6
public enum ProgrammingLanguageType7
{8
Default = 0,9
CSharp = 1,10
VBDotNet = 2,11
Java = 312
}13
} 1using System;
2using System.Collections.Generic;
3using System.Text;
4using System.Data;
5using System.IO;
6
7namespace JoeyCodeGenerator
8{
9 public static class Routine
10 {
11 public static void WriteFile(string filePath, string content)
12 {
13 if (!Directory.Exists(Path.GetDirectoryName(filePath)))
14 {
15 Directory.CreateDirectory(Path.GetDirectoryName(filePath));
16 }
17 using (StreamWriter sw = new StreamWriter(filePath))
18 {
19 sw.Write(content);
20 }
21 }
22 public static string ConvertFromDatabaseToSqlDbType(string columnType)
23 {
24 string sdt = string.Empty;
25 //Bigint
26 if (columnType.ToLower() == "Bigint".ToLower())
27 {
28 sdt = "SqlDbType.BigInt";
29 }
30 else if (columnType.ToLower() == "bit".ToLower())
31 {//bit
32 sdt = "SqlDbType.Bit";
33 }
34 else if (columnType.ToLower() == "datetime".ToLower())
35 {//datetime
36 sdt = "SqlDbType.DateTime";
37 }
38 else if (columnType.ToLower() == "decimal".ToLower())
39 {//decimal
40 sdt = "SqlDbType.Decimal";
41 }
42 else if (columnType.ToLower() == "Float".ToLower())
43 {//Float
44 sdt = "SqlDbType.Float";
45 }
46 else if (columnType.ToLower() == "image".ToLower())
47 {//image
48 sdt = "SqlDbType.Image";
49 }
50 else if (columnType.ToLower() == "Int".ToLower())
51 {//Int
52 sdt = "SqlDbType.Int";
53 }
54 else if (columnType.ToLower() == "Ntext".ToLower())
55 {//Ntext
56 sdt = "SqlDbType.NText";
57 }
58 else if (columnType.ToLower() == "Real".ToLower())
59 {//Real
60 sdt = "SqlDbType.Real";
61 }
62 else if (columnType.ToLower() == "smallint".ToLower())
63 {//smallint
64 sdt = "SqlDbType.SmallInt";
65 }
66 else if (columnType.ToLower() == "tinyint".ToLower())
67 {//tinyint
68 sdt = "SqlDbType.TinyInt";
69 }
70 else if (columnType.ToLower() == "smallmoney".ToLower())
71 {//
72 return "SqlDbType.SmallMoney";
73 }
74 else if (columnType.ToLower() == "text".ToLower())
75 {//text
76 return "SqlDbType.Text";
77 }
78 else if (columnType.ToLower() == "timestamp".ToLower())
79 {//
80 return "SqlDbType.Timestamp";
81 }
82 else if (columnType.ToLower() == "binary".ToLower())
83 {//binary
84 return "SqlDbType.Binary";
85 }
86 else if (columnType.ToLower() == "uniqueidentifier".ToLower())
87 {//uniqueidentifier
88 return "SqlDbType.UniqueIdentifier";
89 }
90 else if (columnType.ToLower() == "varbinary".ToLower())
91 {//varbinary
92 return "SqlDbType.VarBinary";
93 }
94 else if (columnType.ToLower() == "varchar".ToLower())
95 {//varchar
96 return "SqlDbType.VarChar";
97 }
98 else if (columnType.ToLower() == "sql_variant".ToLower())
99 {//sql_variant
100 return "SqlDbType.Variant";
101 }
102 else if (columnType.ToLower() == "Money".ToLower())
103 {//Money
104 return "SqlDbType.Money";
105 }
106 else if (columnType.ToLower() == "nchar".ToLower())
107 {//nchar
108 return "SqlDbType.NChar";
109 }
110 else if (columnType.ToLower() == "nvarchar".ToLower())
111 {//nvarchar
112 return "SqlDbType.NVarChar";
113 }
114 else if (columnType.ToLower() == "smalldatetime".ToLower())
115 {//smalldatetime
116 return "SqlDbType.SmallDateTime";
117 }
118 else if (columnType.ToLower() == "Char".ToLower())
119 {//Char
120 return "SqlDbType.Char";
121 }
122 else
123 {//sql_variant
124 sdt = "SqlDbType.Variant";
125 }
126 return sdt;
127 }
128 public static string ConvertFromDatabaseToLanguage(string input, ProgrammingLanguageType plt, DatabaseType dt)
129 {
130 if (dt == DatabaseType.MSSQL && plt == ProgrammingLanguageType.CSharp)
131 {
132 string type = string.Empty;
133 switch (input.ToLower())
134 {
135 case "int":
136 type = "Int32";
137 break;
138 case "text":
139 type = "String";
140 break;
141 case "bigint":
142 type = "Int64";
143 break;
144 case "binary":
145 type = "System.Byte[]";
146 break;
147 case "bit":
148 type = "Boolean";
149 break;
150 case "char":
151 type = "String";
152 break;
153 case "datetime":
154 type = "System.DateTime";
155 break;
156 case "decimal":
157 type = "System.Decimal";
158 break;
159 case "float":
160 type = "System.Double";
161 break;
162 case "image":
163 type = "System.Byte[]";
164 break;
165 case "money":
166 type = "System.Decimal";
167 break;
168 case "nchar":
169 type = "String";
170 break;
171 case "ntext":
172 type = "String";
173 break;
174 case "numeric":
175 type = "System.Decimal";
176 break;
177 case "nvarchar":
178 type = "String";
179 break;
180 case "real":
181 type = "System.Single";
182 break;
183 case "smalldatetime":
184 type = "System.DateTime";
185 break;
186 case "smallint":
187 type = "Int16";
188 break;
189 case "smallmoney":
190 type = "System.Decimal";
191 break;
192 case "timestamp":
193 type = "System.DateTime";
194 break;
195 case "tinyint":
196 type = "System.Byte";
197 break;
198 case "uniqueidentifier":
199 type = "System.Guid";
200 break;
201 case "varbinary":
202 type = "System.Byte[]";
203 break;
204 case "varchar":
205 type = "String";
206 break;
207 case "variant":
208 type = "Object";
209 break;
210 default:
211 break;
212 }
213 return type;
214 }
215 else
216 {
217 return string.Empty;
218 }
219 }
220 public static DataTable GetNonePKColumns(DataTable columns, DataTable pk)
221 {
222 DataTable dt = columns.Clone();
223 foreach (DataRow dr in columns.Rows)
224 {
225 if (!IsInPKTable(dr, pk))
226 {
227
228 dt.Rows.Add(dr.ItemArray);
229 }
230 }
231 return dt;
232 }
233 private static bool IsInPKTable(DataRow dr, DataTable dt)
234 {
235 foreach (DataRow tdr in dt.Rows)
236 {
237 if (dr[0].ToString() == tdr[0].ToString() && dr[1].ToString() == tdr[1].ToString())
238 {
239 return true;
240 }
241 }
242 return false;
243 }
244 }
245}
246
using System;2
using System.Collections.Generic;3
using System.Text;4
using System.Data;5
using System.IO;6
7
namespace JoeyCodeGenerator8
{9
public static class Routine10
{11
public static void WriteFile(string filePath, string content)12
{13
if (!Directory.Exists(Path.GetDirectoryName(filePath)))14
{15
Directory.CreateDirectory(Path.GetDirectoryName(filePath));16
}17
using (StreamWriter sw = new StreamWriter(filePath))18
{19
sw.Write(content);20
}21
}22
public static string ConvertFromDatabaseToSqlDbType(string columnType)23
{24
string sdt = string.Empty;25
//Bigint26
if (columnType.ToLower() == "Bigint".ToLower())27
{28
sdt = "SqlDbType.BigInt";29
}30
else if (columnType.ToLower() == "bit".ToLower())31
{//bit32
sdt = "SqlDbType.Bit";33
}34
else if (columnType.ToLower() == "datetime".ToLower())35
{//datetime 36
sdt = "SqlDbType.DateTime";37
}38
else if (columnType.ToLower() == "decimal".ToLower())39
{//decimal40
sdt = "SqlDbType.Decimal";41
}42
else if (columnType.ToLower() == "Float".ToLower())43
{//Float44
sdt = "SqlDbType.Float";45
}46
else if (columnType.ToLower() == "image".ToLower())47
{//image48
sdt = "SqlDbType.Image";49
}50
else if (columnType.ToLower() == "Int".ToLower())51
{//Int52
sdt = "SqlDbType.Int";53
}54
else if (columnType.ToLower() == "Ntext".ToLower())55
{//Ntext 56
sdt = "SqlDbType.NText";57
}58
else if (columnType.ToLower() == "Real".ToLower())59
{//Real60
sdt = "SqlDbType.Real";61
}62
else if (columnType.ToLower() == "smallint".ToLower())63
{//smallint64
sdt = "SqlDbType.SmallInt";65
}66
else if (columnType.ToLower() == "tinyint".ToLower())67
{//tinyint68
sdt = "SqlDbType.TinyInt";69
}70
else if (columnType.ToLower() == "smallmoney".ToLower())71
{//72
return "SqlDbType.SmallMoney";73
}74
else if (columnType.ToLower() == "text".ToLower())75
{//text 76
return "SqlDbType.Text";77
}78
else if (columnType.ToLower() == "timestamp".ToLower())79
{//80
return "SqlDbType.Timestamp";81
}82
else if (columnType.ToLower() == "binary".ToLower())83
{//binary84
return "SqlDbType.Binary";85
}86
else if (columnType.ToLower() == "uniqueidentifier".ToLower())87
{//uniqueidentifier 88
return "SqlDbType.UniqueIdentifier";89
}90
else if (columnType.ToLower() == "varbinary".ToLower())91
{//varbinary 92
return "SqlDbType.VarBinary";93
}94
else if (columnType.ToLower() == "varchar".ToLower())95
{//varchar96
return "SqlDbType.VarChar";97
}98
else if (columnType.ToLower() == "sql_variant".ToLower())99
{//sql_variant100
return "SqlDbType.Variant";101
}102
else if (columnType.ToLower() == "Money".ToLower())103
{//Money104
return "SqlDbType.Money";105
}106
else if (columnType.ToLower() == "nchar".ToLower())107
{//nchar108
return "SqlDbType.NChar";109
}110
else if (columnType.ToLower() == "nvarchar".ToLower())111
{//nvarchar112
return "SqlDbType.NVarChar";113
}114
else if (columnType.ToLower() == "smalldatetime".ToLower())115
{//smalldatetime116
return "SqlDbType.SmallDateTime";117
}118
else if (columnType.ToLower() == "Char".ToLower())119
{//Char120
return "SqlDbType.Char";121
}122
else123
{//sql_variant124
sdt = "SqlDbType.Variant";125
}126
return sdt;127
}128
public static string ConvertFromDatabaseToLanguage(string input, ProgrammingLanguageType plt, DatabaseType dt)129
{130
if (dt == DatabaseType.MSSQL && plt == ProgrammingLanguageType.CSharp)131
{132
string type = string.Empty;133
switch (input.ToLower())134
{135
case "int":136
type = "Int32";137
break;138
case "text":139
type = "String";140
break;141
case "bigint":142
type = "Int64";143
break;144
case "binary":145
type = "System.Byte[]";146
break;147
case "bit":148
type = "Boolean";149
break;150
case "char":151
type = "String";152
break;153
case "datetime":154
type = "System.DateTime";155
break;156
case "decimal":157
type = "System.Decimal";158
break;159
case "float":160
type = "System.Double";161
break;162
case "image":163
type = "System.Byte[]";164
break;165
case "money":166
type = "System.Decimal";167
break;168
case "nchar":169
type = "String";170
break;171
case "ntext":172
type = "String";173
break;174
case "numeric":175
type = "System.Decimal";176
break;177
case "nvarchar":178
type = "String";179
break;180
case "real":181
type = "System.Single";182
break;183
case "smalldatetime":184
type = "System.DateTime";185
break;186
case "smallint":187
type = "Int16";188
break;189
case "smallmoney":190
type = "System.Decimal";191
break;192
case "timestamp":193
type = "System.DateTime";194
break;195
case "tinyint":196
type = "System.Byte";197
break;198
case "uniqueidentifier":199
type = "System.Guid";200
break;201
case "varbinary":202
type = "System.Byte[]";203
break;204
case "varchar":205
type = "String";206
break;207
case "variant":208
type = "Object";209
break;210
default:211
break;212
}213
return type;214
}215
else216
{217
return string.Empty;218
}219
}220
public static DataTable GetNonePKColumns(DataTable columns, DataTable pk)221
{222
DataTable dt = columns.Clone();223
foreach (DataRow dr in columns.Rows)224
{225
if (!IsInPKTable(dr, pk))226
{227
228
dt.Rows.Add(dr.ItemArray);229
}230
}231
return dt;232
}233
private static bool IsInPKTable(DataRow dr, DataTable dt)234
{235
foreach (DataRow tdr in dt.Rows)236
{237
if (dr[0].ToString() == tdr[0].ToString() && dr[1].ToString() == tdr[1].ToString())238
{239
return true;240
}241
}242
return false;243
}244
}245
}246
