|
|
在上篇文章我們生成了具有明確含義標準的XML，有了這樣的XML要生成SQL就很容易。象我這樣構建出相關的類之后，只要有涉及到引用時只要繼承SQLRECORD這個類，并重寫它的GetSqlValue方法。而后根據名稱取得你對應的SQL生成器就行了。我相信即使還有許多要導入的EXCEL我也不怕了。那時我一個上午就完成了四張全都有引用表的導入工作，當然包括在EXCEL中設置伏筆等工作。記得那天我病得很重，頭天晚上還打針。  在這個導入設計中我還有些地方沒有做好。比如XML，SQL生成的管理器我就做得很粗糙。
1 using System;
2using System.Collections.Generic;
3using System.Text;
4using System.Xml;
5using System.IO;
6using Zxh.Pis.EntitiesTest.Xml;
7EntitiesTest.Sql
8   {
9 struct SqlColumn
10  {
11 private string _Name;
12 public string Name
13 {
14 get { return _Name; }
15 set { _Name = value; }
16 }
17 private string _Value;
18 public string Value
19 {
20 get { return _Value; }
21 set { _Value = value; }
22 }
23 private string _Type;
24 public string Type
25 {
26 get { return _Type; }
27 set { _Type = value; }
28 }
29 private string _Refrence;
30 public string Refrence
31 {
32 get { return _Refrence; }
33 set { _Refrence = value; }
34 }
35 private string _Restrict;
36 public string Restrict
37 {
38 get { return _Restrict; }
39 set { _Restrict = value; }
40 }
41 public SqlColumn(string name, string strValue)
42 : this(name, strValue,string.Empty)
43 {
44
45 }
46
47 public SqlColumn(string name, string strValue,string type)
48 : this(name, strValue, type,string.Empty)
49 {
50
51 }
52
53 public SqlColumn(string name, string strValue, string type,string refrence)
54 :this(name, strValue, type,refrence,string.Empty)
55 {
56
57 }
58
59 public SqlColumn(string name, string strValue, string type, string refrence,string restrict)
60 {
61 _Name = name;
62 _Value = strValue;
63 _Type = type;
64 _Refrence = refrence;
65 _Restrict = restrict;
66 }
67 }
68
69 class SqlConvrter
70 {
71 private XmlDocument _Doc;
72 public SqlConvrter(XmlDocument doc)
73 {
74 _Doc = doc;
75 }
76
77 public void ConvertToSql(string strFilePath)
78 {
79 TextWriter writer = new StreamWriter(strFilePath,false,Encoding.UTF8);
80 XmlNode root = _Doc.DocumentElement;
81 string strTableName = root.Attributes[XmlConstraint.NAME].Value;
82 SqlRecord sqlRecord = SqlRecord.GetSqlRecord(strTableName);
83 foreach (XmlNode node in _Doc.DocumentElement.ChildNodes)
84 {
85 sqlRecord.Clear();
86 foreach (XmlNode columnNode in node.ChildNodes)
87 {
88 string name = columnNode.Attributes[XmlConstraint.NAME].Value;
89 string strValue = columnNode.Attributes[XmlConstraint.VALUE].Value;
90 string type = string.Empty;
91 string refrence = string.Empty;
92 string restrict = string.Empty;
93 if (columnNode.Attributes[XmlConstraint.REFRENCE] != null)
94 {
95 refrence = columnNode.Attributes[XmlConstraint.REFRENCE].Value;
96 }
97 if (columnNode.Attributes[XmlConstraint.RESTRICT] != null)
98 {
99 restrict = columnNode.Attributes[XmlConstraint.RESTRICT].Value;
100 }
101 if (columnNode.Attributes[XmlConstraint.TYPE] != null)
102 {
103 type = columnNode.Attributes[XmlConstraint.TYPE].Value;
104 }
105 sqlRecord.AddColumn(new SqlColumn(name, strValue, type, refrence, restrict));
106 }
107 writer.WriteLine();
108 sqlRecord.ConvertToSql(writer);
109 writer.WriteLine();
110 writer.WriteLine("go");
111 }
112 writer.Dispose();
113 }
114 }
115
116 class SqlRecord
117 {
118 private string _TableName;
119 private string strInsert;
120 private int _Para = 0;
121
122 protected string GetNewParaName()
123 {
124 ++_Para;
125 return "@P" + _Para.ToString();
126 }
127 StringBuilder strColumns = new StringBuilder();
128 StringBuilder strValues = new StringBuilder();
129 List<SqlColumn> _Columns = new List<SqlColumn>();
130 public SqlRecord(string tableName)
131 {
132 _TableName = tableName;
133 strInsert = " insert into " + tableName;
134 }
135
136 public void AddColumn(SqlColumn column)
137 {
138 _Columns.Add(column);
139 }
140
141 public void Clear()
142 {
143 _Columns.Clear();
144 _Para = 0;
145 strColumns.Remove(0,strColumns.Length);
146 strValues.Remove(0, strValues.Length);
147 }
148
149 public void ConvertToSql(TextWriter writer)
150 {
151 StringBuilder sb = new StringBuilder();
152 foreach (SqlColumn sqlColumn in _Columns)
153 {
154 AddColumn(sqlColumn.Name);
155 string strFront = string.Empty;
156 string strValue = string.Empty;
157 GetSqlValue(sqlColumn,ref strFront,ref strValue);
158 sb.Append(strFront);
159 AddValue(strValue);
160 }
161 sb.Append(strInsert);
162 sb.Append("(");
163 sb.Append(strColumns);
164 sb.Append(") values(");
165 sb.Append(strValues);
166 sb.Append(") ");
167 writer.Write(sb.ToString());
168 }
169
170 protected virtual void GetSqlValue(SqlColumn sqlColumn,ref string strFront,ref string strValue)
171 {
172 strValue = GetValueString(sqlColumn);
173 }
174
175 protected string GetValueString(SqlColumn sqlColumn)
176 {
177 if (sqlColumn.Type == XmlConstraint.INT)
178 {
179 return sqlColumn.Value;
180 }
181 else
182 {
183 return "'" + sqlColumn.Value + "'";
184 }
185 }
186
187 protected void AddColumn(string strColumn)
188 {
189 if (strColumns.Length > 0)
190 {
191 strColumns.Append(',');
192 }
193 strColumns.Append(strColumn);
194 }
195
196 protected void AddValue(string strValue)
197 {
198 if (strValues.Length > 0)
199 {
200 strValues.Append(',');
201 }
202 strValues.Append(strValue);
203 }
204
205 public static SqlRecord GetSqlRecord(string name)
206 {
207 if ( name.ToLower()== "MemberOfCongress".ToLower())
208 {
209 return new RepresentativeSqlRecord(name);
210 }
211 else if (name.ToLower() == "proposal")
212 {
213 return new ProposalSqlRecord(name);
214 }
215 else if (name.ToLower() == "proposalmember")
216 {
217 return new ProposalMemberSqlRecord(name);
218 }
219 else if (name.ToLower() == "proposaldepartmentalism")
220 {
221 return new ProposalDepartmentalismSqlRecord(name);
222 }
223 else
224 {
225 return new SqlRecord(name);
226 }
227 }
228 }
229
230 class RepresentativeSqlRecord : SqlRecord
231 {
232 public RepresentativeSqlRecord(string strTableName)
233 :base(strTableName)
234 {
235 }
236
237 protected override void GetSqlValue(SqlColumn sqlColumn,ref string strFront,ref string strValue)
238 {
239 if (string.IsNullOrEmpty(sqlColumn.Refrence))
240 {
241 base.GetSqlValue(sqlColumn, ref strFront,ref strValue);
242 }
243 else
244 {
245 if (sqlColumn.Refrence.ToLower() == "entryitem")
246 {
247 string paraName = GetNewParaName();
248 string strFormat = "declare {0} int\n" +
249 "declare {0}_1 int\n" +
250 "select {0}_1=Id from entrybase where name='{2}'\n" +
251 "select {0}=id\n" +
252 "from entrybase \n" +
253 "where name='{1} '\n" +
254 " and id in (select id from entryitem where entrycategory={0}_1)\n" +
255 "if({0} is null)\n" +
256 "begin \n" +
257 " insert into entrybase(Name,fixed) values('{1}',1)\n" +
258 " select {0}=IDENT_CURRENT('entrybase')\n" +
259 " insert into entryitem(id,entrycategory) values({0},{0}_1)\n" +
260 "end\n";
261 strFront = string.Format(strFormat, paraName, sqlColumn.Value, sqlColumn.Restrict);
262 strValue = paraName;
263 }
264 else
265 {
266 if (sqlColumn.Refrence.ToLower() == "congress")
267 {
268 //if (sqlColumn.Value)
269 //{
270
271 //}
272 }
273 }
274 }
275 }
276 }
277
278 class ProposalSqlRecord : SqlRecord
279 {
280 public ProposalSqlRecord(string strTableName)
281 :base(strTableName)
282 {
283 }
284
285 protected override void GetSqlValue(SqlColumn sqlColumn,ref string strFront,ref string strValue)
286 {
287 if (string.IsNullOrEmpty(sqlColumn.Refrence))
288 {
289 base.GetSqlValue(sqlColumn, ref strFront,ref strValue);
290 }
291 else
292 {
293 if (sqlColumn.Refrence.ToLower() == "entryitem")
294 {
295 string paraName = GetNewParaName();
296 string strFormat = "declare {0} int\n" +
297 "declare {0}_1 int\n" +
298 "select {0}_1=Id from entrybase where name='{2}'\n" +
299 "select {0}=id\n" +
300 "from entrybase \n" +
301 "where name='{1} '\n" +
302 " and id in (select id from entryitem where entrycategory={0}_1)\n" +
303 "if({0} is null)\n" +
304 "begin \n" +
305 " insert into entrybase(Name,fixed) values('{1}',1)\n" +
306 " select {0}=IDENT_CURRENT('entrybase')\n" +
307 " insert into entryitem(id,entrycategory) values({0},{0}_1)\n" +
308 "end\n";
309 strFront = string.Format(strFormat, paraName, sqlColumn.Value, sqlColumn.Restrict);
310 strValue = paraName;
311 }
312 }
313 }
314 }
315
316 class ProposalMemberSqlRecord : SqlRecord
317 {
318 public ProposalMemberSqlRecord(string strTableName)
319 : base(strTableName)
320 {
321 }
322
323 protected override void GetSqlValue(SqlColumn sqlColumn, ref string strFront, ref string strValue)
324 {
325 if (string.IsNullOrEmpty(sqlColumn.Refrence))
326 {
327 base.GetSqlValue(sqlColumn, ref strFront, ref strValue);
328 }
329 else
330 {
331 if (sqlColumn.Refrence.ToLower() == "proposal")
332 {
333 string paraName = GetNewParaName();
334 string strFormat = "declare {0} int\n" +
335 "select {0}=id \n" +
336 "from proposal \n" +
337 "where ProposalNO='{1}'\n" +
338 "if {0} is null\n" +
339 "print '{1}'\n";
340 strFront = string.Format(strFormat, paraName, sqlColumn.Value);
341 strValue = paraName;
342 }
343 else if (sqlColumn.Refrence.ToLower() == "MemberOfCongress".ToLower())
344 {
345 string paraName = GetNewParaName();
346 string strFormat = "declare {0} int\n" +
347 "select {0}=id \n" +
348 "from memberofcongress \n" +
349 "where name='{1}'\n" +
350 "if {0} is null\n" +
351 "print '{1}'\n";
352 strFront = string.Format(strFormat, paraName, sqlColumn.Value);
353 strValue = paraName;
354 }
355 }
356 }
357 }
358
359 class ProposalDepartmentalismSqlRecord : SqlRecord
360 {
361 public ProposalDepartmentalismSqlRecord(string strTableName)
362 : base(strTableName)
363 {
364 }
365
366 protected override void GetSqlValue(SqlColumn sqlColumn, ref string strFront, ref string strValue)
367 {
368 if (string.IsNullOrEmpty(sqlColumn.Refrence))
369 {
370 base.GetSqlValue(sqlColumn, ref strFront, ref strValue);
371 }
372 else
373 {
374 if (sqlColumn.Refrence.ToLower() == "proposal")
375 {
376 string paraName = GetNewParaName();
377 string strFormat = "declare {0} int\n" +
378 "select {0}=id \n" +
379 "from proposal \n" +
380 "where ProposalNO='{1}'\n" +
381 "if {0} is null\n" +
382 "print '{1}'\n";
383 strFront = string.Format(strFormat, paraName, sqlColumn.Value);
384 strValue = paraName;
385 }
386 else if (sqlColumn.Refrence.ToLower() == "Departmentalism".ToLower())
387 {
388 string paraName = GetNewParaName();
389 string strFormat = "declare {0} int\n" +
390 "select {0}=id \n" +
391 "from Departmentalism \n" +
392 "where name like '{1}'+'%'\n" +
393 "if {0} is null\n" +
394 "print '{1}'\n";
395 strFront = string.Format(strFormat, paraName, sqlColumn.Value);
396 strValue = paraName;
397 }
398 else if (sqlColumn.Refrence.ToLower() == "entryitem")
399 {
400 string paraName = GetNewParaName();
401 string strFormat = "declare {0} int\n" +
402 "declare {0}_1 int\n" +
403 "select {0}_1=Id from entrybase where name='{2}'\n" +
404 "select {0}=id\n" +
405 "from entrybase \n" +
406 "where name='{1} '\n" +
407 " and id in (select id from entryitem where entrycategory={0}_1)\n" +
408 "if({0} is null)\n" +
409 "begin \n" +
410 " insert into entrybase(Name,fixed) values('{1}',1)\n" +
411 " select {0}=IDENT_CURRENT('entrybase')\n" +
412 " insert into entryitem(id,entrycategory) values({0},{0}_1)\n" +
413 "end\n";
414 strFront = string.Format(strFormat, paraName, sqlColumn.Value, sqlColumn.Restrict);
415 strValue = paraName;
416 }
417 }
418 }
419 }
420 }
421
|