|
在上篇文章我们生成了具有明确含义标准的XML,有了这样的XML要生成SQL就很容易。象我这样构建出相关的类之后,只要有涉及到引用时只要继承SQLRECORD这个类,并重写它的GetSqlValue方法。而后根据名称取得你对应的SQL生成器就行了。我相信即使还有许多要导入的EXCEL我也不怕了。那时我一个上午就完成了四张全都有引用表的导入工作,当然包括在EXCEL中设置伏笔等工作。记得那天我病得很重,头天晚上还打针。 ![](http://www.cppblog.com/CuteSoft_Client/CuteEditor/images/emsad.gif) 在这个导入设计中我还有些地方没有做好。比如XML,SQL生成的管理器我就做得很粗糙。
1 using System; 2 using System.Collections.Generic; 3 using System.Text; 4 using System.Xml; 5 using System.IO; 6 using Zxh.Pis.EntitiesTest.Xml; 7 EntitiesTest.Sql 8![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedBlockStart.gif) ![](http://www.cppblog.com/Images/OutliningIndicators/ContractedBlock.gif) { 9 struct SqlColumn 10![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 11 private string _Name; 12 public string Name 13![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 14![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) get { return _Name; } 15![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) set { _Name = value; } 16 } 17 private string _Value; 18 public string Value 19![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 20![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) get { return _Value; } 21![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) set { _Value = value; } 22 } 23 private string _Type; 24 public string Type 25![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 26![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) get { return _Type; } 27![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) set { _Type = value; } 28 } 29 private string _Refrence; 30 public string Refrence 31![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 32![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) get { return _Refrence; } 33![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) set { _Refrence = value; } 34 } 35 private string _Restrict; 36 public string Restrict 37![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 38![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) get { return _Restrict; } 39![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) set { _Restrict = value; } 40 } 41 public SqlColumn(string name, string strValue) 42 : this(name, strValue,string.Empty) 43![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 44![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif) 45 } 46![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif) 47 public SqlColumn(string name, string strValue,string type) 48 : this(name, strValue, type,string.Empty) 49![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 50![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif) 51 } 52![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif) 53 public SqlColumn(string name, string strValue, string type,string refrence) 54 :this(name, strValue, type,refrence,string.Empty) 55![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 56![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif) 57 } 58![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif) 59 public SqlColumn(string name, string strValue, string type, string refrence,string restrict) 60![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 61 _Name = name; 62 _Value = strValue; 63 _Type = type; 64 _Refrence = refrence; 65 _Restrict = restrict; 66 } 67 } 68![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif) 69 class SqlConvrter 70![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 71 private XmlDocument _Doc; 72 public SqlConvrter(XmlDocument doc) 73![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 74 _Doc = doc; 75 } 76![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif) 77 public void ConvertToSql(string strFilePath) 78![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 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![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 85 sqlRecord.Clear(); 86 foreach (XmlNode columnNode in node.ChildNodes) 87![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 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![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 95 refrence = columnNode.Attributes[XmlConstraint.REFRENCE].Value; 96 } 97 if (columnNode.Attributes[XmlConstraint.RESTRICT] != null) 98![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 99 restrict = columnNode.Attributes[XmlConstraint.RESTRICT].Value; 100 } 101 if (columnNode.Attributes[XmlConstraint.TYPE] != null) 102![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 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![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif) 116 class SqlRecord 117![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 118 private string _TableName; 119 private string strInsert; 120 private int _Para = 0; 121![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif) 122 protected string GetNewParaName() 123![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 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![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 132 _TableName = tableName; 133 strInsert = " insert into " + tableName; 134 } 135![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif) 136 public void AddColumn(SqlColumn column) 137![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 138 _Columns.Add(column); 139 } 140![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif) 141 public void Clear() 142![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 143 _Columns.Clear(); 144 _Para = 0; 145 strColumns.Remove(0,strColumns.Length); 146 strValues.Remove(0, strValues.Length); 147 } 148![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif) 149 public void ConvertToSql(TextWriter writer) 150![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 151 StringBuilder sb = new StringBuilder(); 152 foreach (SqlColumn sqlColumn in _Columns) 153![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 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![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif) 170 protected virtual void GetSqlValue(SqlColumn sqlColumn,ref string strFront,ref string strValue) 171![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 172 strValue = GetValueString(sqlColumn); 173 } 174![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif) 175 protected string GetValueString(SqlColumn sqlColumn) 176![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 177 if (sqlColumn.Type == XmlConstraint.INT) 178![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 179 return sqlColumn.Value; 180 } 181 else 182![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 183 return "'" + sqlColumn.Value + "'"; 184 } 185 } 186![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif) 187 protected void AddColumn(string strColumn) 188![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 189 if (strColumns.Length > 0) 190![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 191 strColumns.Append(','); 192 } 193 strColumns.Append(strColumn); 194 } 195![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif) 196 protected void AddValue(string strValue) 197![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 198 if (strValues.Length > 0) 199![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 200 strValues.Append(','); 201 } 202 strValues.Append(strValue); 203 } 204![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif) 205 public static SqlRecord GetSqlRecord(string name) 206![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 207 if ( name.ToLower()== "MemberOfCongress".ToLower()) 208![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 209 return new RepresentativeSqlRecord(name); 210 } 211 else if (name.ToLower() == "proposal") 212![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 213 return new ProposalSqlRecord(name); 214 } 215 else if (name.ToLower() == "proposalmember") 216![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 217 return new ProposalMemberSqlRecord(name); 218 } 219 else if (name.ToLower() == "proposaldepartmentalism") 220![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 221 return new ProposalDepartmentalismSqlRecord(name); 222 } 223 else 224![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 225 return new SqlRecord(name); 226 } 227 } 228 } 229![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif) 230 class RepresentativeSqlRecord : SqlRecord 231![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 232 public RepresentativeSqlRecord(string strTableName) 233 :base(strTableName) 234![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 235 } 236![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif) 237 protected override void GetSqlValue(SqlColumn sqlColumn,ref string strFront,ref string strValue) 238![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 239 if (string.IsNullOrEmpty(sqlColumn.Refrence)) 240![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 241 base.GetSqlValue(sqlColumn, ref strFront,ref strValue); 242 } 243 else 244![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 245 if (sqlColumn.Refrence.ToLower() == "entryitem") 246![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 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![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 266 if (sqlColumn.Refrence.ToLower() == "congress") 267![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 268 //if (sqlColumn.Value) 269 //{ 270![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif) 271 //} 272 } 273 } 274 } 275 } 276 } 277![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif) 278 class ProposalSqlRecord : SqlRecord 279![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 280 public ProposalSqlRecord(string strTableName) 281 :base(strTableName) 282![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 283 } 284![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif) 285 protected override void GetSqlValue(SqlColumn sqlColumn,ref string strFront,ref string strValue) 286![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 287 if (string.IsNullOrEmpty(sqlColumn.Refrence)) 288![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 289 base.GetSqlValue(sqlColumn, ref strFront,ref strValue); 290 } 291 else 292![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 293 if (sqlColumn.Refrence.ToLower() == "entryitem") 294![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 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![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif) 316 class ProposalMemberSqlRecord : SqlRecord 317![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 318 public ProposalMemberSqlRecord(string strTableName) 319 : base(strTableName) 320![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 321 } 322![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif) 323 protected override void GetSqlValue(SqlColumn sqlColumn, ref string strFront, ref string strValue) 324![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 325 if (string.IsNullOrEmpty(sqlColumn.Refrence)) 326![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 327 base.GetSqlValue(sqlColumn, ref strFront, ref strValue); 328 } 329 else 330![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 331 if (sqlColumn.Refrence.ToLower() == "proposal") 332![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 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![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 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![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif) 359 class ProposalDepartmentalismSqlRecord : SqlRecord 360![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 361 public ProposalDepartmentalismSqlRecord(string strTableName) 362 : base(strTableName) 363![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 364 } 365![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif) 366 protected override void GetSqlValue(SqlColumn sqlColumn, ref string strFront, ref string strValue) 367![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 368 if (string.IsNullOrEmpty(sqlColumn.Refrence)) 369![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 370 base.GetSqlValue(sqlColumn, ref strFront, ref strValue); 371 } 372 else 373![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 374 if (sqlColumn.Refrence.ToLower() == "proposal") 375![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 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![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 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![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 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![](http://www.cppblog.com/Images/OutliningIndicators/None.gif)
|