SQL数据库抽像工厂类,抽象方法的实现
1 public class DAl : IData 2 { 3 readonly string InsertStr = "Insert Into {0}({1}) Values({2})"; 4 readonly string UpdateStr = "Update {0} Set {1} {2}"; 5 readonly string SelectStr = "Select {0} from {1} {2} {3}"; 6 readonly string DeleteStr = "Delete from {0} {1}"; 7 8 string _connstring; 9 public override string Connstring 10 { 11 get { return _connstring; } 12 set 13 { 14 _connstring = value; 15 } 16 } 17 18 public override int Insert<T>(T t) 19 { 20 SqlHelper.SqlConnectionString = Connstring; 21 Type ModelCs = t.GetType(); 22 YAO.Model.TableMap TMap = ModelAttributeTag<YAO.Model.TableMap>.GetTableMapAttribute(ModelCs); 23 string Table = TMap.TableName; 24 PropertyInfo[] Item = ModelCs.GetProperties(); 25 string Cols = string.Empty; 26 string Values = string.Empty; 27 bool SetState = true; 28 foreach (PropertyInfo P in Item) 29 { 30 YAO.Model.ColumnMap Map = AttributeProperty<YAO.Model.ColumnMap>.GetTableMapAttribute(P); 31 if (Map == null) 32 { 33 SetState = false; 34 break; 35 } 36 if (!Map.Mark) 37 { 38 object Value = P.GetValue(t, null); 39 if (Value != null) 40 { 41 if (Cols == string.Empty) 42 { 43 Cols = Map.ColumnName; 44 Values = "'" + Value + "'"; 45 } 46 else 47 { 48 Cols += "," + Map.ColumnName; 49 Values += ",'" + Value + "'"; 50 } 51 } 52 } 53 } 54 if (SetState) 55 { 56 string Sql = string.Format(InsertStr, Table, Cols, Values); 57 return SqlHelper.ExecuteNonQuery(Sql); 58 } 59 else 60 { 61 return 0; 62 } 63 } 64 65 public override int Insert(TableModel Data) 66 { 67 SqlHelper.SqlConnectionString = Connstring; 68 string Table = Data.TableName; 69 ColumnModel[] DmItem = Data.Columns; 70 if (Data.CmdType == DataCmdType.Insert) 71 { 72 string ColName = string.Empty; 73 string ColParams = string.Empty; 74 SqlParameter[] Parames = new SqlParameter[DmItem.Length]; 75 int i = 0; 76 foreach (ColumnModel Dm in DmItem) 77 { 78 if (ColName != string.Empty) 79 { 80 ColName += ","; 81 ColParams += ","; 82 } 83 ColName += Dm.ColumnName; 84 ColParams += "@" + Dm.ColumnName; 85 SqlParameter par = SqlHelper.SetDataParameter(Dm.ColumnName, (SqlDbType)Dm.DbType, Dm.ColumnLen, Dm.ColumnValue, Dm.Directioin); 86 Parames[i] = par; 87 i++; 88 } 89 string Sql = string.Format(InsertStr, Table, ColName, ColParams); 90 return SqlHelper.ExecuteNonQuery(CommandType.Text, Sql, Parames); 91 } 92 else 93 { 94 return 0; 95 } 96 } 97 98 public override int UpDate<T>(T t) 99 { 100 SqlHelper.SqlConnectionString = Connstring; 101 Type ModelCs = t.GetType(); 102 YAO.Model.TableMap TMap = ModelAttributeTag<YAO.Model.TableMap>.GetTableMapAttribute(ModelCs); 103 string Table = TMap.TableName; 104 PropertyInfo[] Item = ModelCs.GetProperties(); 105 string Cols = string.Empty; 106 string WhereStr = " where 1=1"; 107 bool SetState = true; 108 foreach (PropertyInfo P in Item) 109 { 110 ColumnMap Map = AttributeProperty<ColumnMap>.GetTableMapAttribute(P); 111 if (Map == null) 112 { 113 SetState = false; 114 break; 115 } 116 if (!Map.Mark) 117 { 118 object Value = P.GetValue(t, null); 119 if (Value != null) 120 { 121 if (Cols == string.Empty) 122 { 123 Cols = P.Name + "='" + Value + "'"; 124 } 125 else 126 { 127 Cols += "," + P.Name + "='" + Value + "'"; 128 } 129 } 130 } 131 else if(Map.PrimaryKey) 132 { 133 object Value = P.GetValue(t, null); 134 WhereStr += " and " + P.Name + "='" + Value + "'"; 135 } 136 137 } 138 if (SetState) 139 { 140 string Sql = string.Format(UpdateStr, Table, Cols, WhereStr); 141 return SqlHelper.ExecuteNonQuery(Sql); 142 } 143 else 144 { 145 return 0; 146 } 147 } 148 149 public override int UpDate<T>(T t, WhereColModel[] Dm) 150 { 151 SqlHelper.SqlConnectionString = Connstring; 152 Type ModelCs = t.GetType(); 153 YAO.Model.TableMap TMap = ModelAttributeTag<YAO.Model.TableMap>.GetTableMapAttribute(ModelCs); 154 string Table = TMap.TableName; 155 PropertyInfo[] Item = ModelCs.GetProperties(); 156 string Cols = string.Empty; 157 158 bool SetState = true; 159 foreach (PropertyInfo P in Item) 160 { 161 ColumnMap Map = AttributeProperty<ColumnMap>.GetTableMapAttribute(P); 162 if (Map == null) 163 { 164 SetState = false; 165 break; 166 } 167 if (!Map.Mark) 168 { 169 object Value = P.GetValue(t, null); 170 if (Value != null) 171 { 172 if (Cols == string.Empty) 173 { 174 Cols = P.Name + "='" + Value + "'"; 175 } 176 else 177 { 178 Cols += "," + P.Name + "='" + Value + "'"; 179 } 180 } 181 } 182 } 183 if (SetState) 184 { 185 string WhereStr = GetWhereStr(Dm); 186 string Sql = string.Format(UpdateStr, Table, Cols, WhereStr); 187 return SqlHelper.ExecuteNonQuery(Sql); 188 } 189 else 190 { 191 return 0; 192 } 193 } 194 195 public override int UpDate(TableModel Data) 196 { 197 SqlHelper.SqlConnectionString = Connstring; 198 string Table = Data.TableName; 199 WhereColModel[] Where = Data.Where; 200 string WhereStr = GetWhereStr(Where); 201 ColumnModel[] DmItem = Data.Columns; 202 if (Data.CmdType == DataCmdType.Update) 203 { 204 string Cols = string.Empty; 205 SqlParameter[] Parames = new SqlParameter[DmItem.Length]; 206 int i = 0; 207 foreach (ColumnModel Dm in DmItem) 208 { 209 if (Cols != string.Empty) 210 { 211 Cols += ","; 212 213 } 214 Cols += Dm.ColumnName + "=@" + Dm.ColumnName; 215 SqlParameter par = SqlHelper.SetDataParameter(Dm.ColumnName, (SqlDbType)Dm.DbType, Dm.ColumnLen, Dm.ColumnValue, Dm.Directioin); 216 Parames[i] = par; 217 i++; 218 } 219 string Sql = string.Format(UpdateStr, Table, Cols, WhereStr); 220 return SqlHelper.ExecuteNonQuery(CommandType.Text, Sql, Parames); 221 } 222 else 223 { 224 return 0; 225 } 226 } 227 228 public override int Delete(TableModel Data) 229 { 230 SqlHelper.SqlConnectionString = Connstring; 231 string Table = Data.TableName; 232 WhereColModel[] Where = Data.Where; 233 string WhereStr = GetWhereStr(Where); 234 if (Data.CmdType == DataCmdType.Delete) 235 { 236 string Sql = string.Format(DeleteStr, Table, WhereStr); 237 return SqlHelper.ExecuteNonQuery(Sql); 238 } 239 else 240 { 241 return 0; 242 } 243 } 244 245 public override int RunSqlCmd(TableModel Data) 246 { 247 if (Data.CmdType == DataCmdType.Delete) 248 { 249 return Delete(Data); 250 } 251 else if (Data.CmdType == DataCmdType.Update) 252 { 253 return UpDate(Data); 254 } 255 else if (Data.CmdType == DataCmdType.Insert) 256 { 257 return Insert(Data); 258 } 259 else 260 { 261 return 0; 262 } 263 } 264 265 public override T Row<T>(SelectModel Data) 266 { 267 SqlHelper.SqlConnectionString = Connstring; 268 OrderByType[] OrderBy = Data.OrderBy; 269 string Oby = string.Empty; 270 if (OrderBy != null) 271 { 272 for (int i = 0; i < OrderBy.Length; i++) 273 { 274 if (string.IsNullOrEmpty(Oby)) 275 { 276 Oby = " order by " + OrderBy[i].Cols + " " + OrderBy[i].Orderby; 277 } 278 else 279 { 280 Oby += "," + OrderBy[i].Cols + " " + OrderBy[i].Orderby; 281 } 282 } 283 } 284 WhereColModel[] WhereItem = Data.Where; 285 string WhereStr = GetWhereStr(WhereItem); 286 string Sql = string.Format(SelectStr, Data.Cols, Data.TableName, WhereStr, Oby); 287 DataRow Row = SqlHelper.ExecuteDataRow(Sql); 288 if (Row != null) 289 { 290 T t = default(T); 291 T tobj = Activator.CreateInstance<T>(); 292 Type ModelCs = tobj.GetType(); 293 if (ModelCs.IsClass) 294 { 295 PropertyInfo[] Item = ModelCs.GetProperties(); 296 Hashtable _TTable = new Hashtable(); 297 foreach (PropertyInfo P in Item) 298 { 299 YAO.Model.ColumnMap CMap = AttributeProperty<YAO.Model.ColumnMap>.GetTableMapAttribute(P); 300 if (CMap.IsValueTemp) 301 { 302 _TTable.Add(CMap.ValueTempName.ToLower(), P.PropertyType); 303 } 304 } 305 foreach (PropertyInfo P in Item) 306 { 307 string ColName = P.Name; 308 YAO.Model.ColumnMap Map = AttributeProperty<YAO.Model.ColumnMap>.GetTableMapAttribute(P); 309 310 if (Map == null || Map.IsSetValue) 311 { 312 if (Row.Table.Columns.Contains(ColName)) 313 { 314 object Values = Row[ColName]; 315 if (Values != DBNull.Value) 316 { 317 P.SetValue(tobj, Values, null); 318 } 319 } 320 } 321 else 322 { 323 if (!Map.IsValueTemp) 324 { 325 string[] keyItem = Map.ParentCol.Split('|'); 326 string[] CkeyItem = Map.KeyCol.Split('|'); 327 Hashtable KeyTable = new Hashtable(); 328 for (int Klen = 0; Klen < keyItem.Length; Klen++) 329 { 330 if (keyItem[Klen] != "") 331 { 332 if (Row.Table.Columns.Contains(keyItem[Klen])) 333 { 334 KeyTable.Add(CkeyItem[Klen], Row[keyItem[Klen]]); 335 } 336 else 337 { 338 KeyTable.Add(CkeyItem[Klen], keyItem[Klen]); 339 } 340 } 341 } 342 if (_TTable.Contains(P.Name.ToLower())) 343 { 344 345 Type _TempType = (Type)_TTable[P.Name.ToLower()]; 346 P.SetValue(tobj, ReadChdInfo(Map, P, KeyTable, _TempType), null); 347 } 348 else 349 { 350 P.SetValue(tobj, ReadChdInfo(Map, P, KeyTable), null); 351 } 352 } 353 } 354 } 355 t = tobj; 356 return t; 357 } 358 else 359 { 360 return (T)(object)Row; 361 } 362 } 363 else 364 { 365 return default(T); 366 } 367 } 368 369 public override object Scalar(SelectModel Data) 370 { 371 SqlHelper.SqlConnectionString = Connstring; 372 OrderByType[] OrderBy = Data.OrderBy; 373 string Oby = string.Empty; 374 if (OrderBy != null) 375 { 376 for (int i = 0; i < OrderBy.Length; i++) 377 { 378 if (string.IsNullOrEmpty(Oby)) 379 { 380 Oby = " order by " + OrderBy[i].Cols + " " + OrderBy[i].Orderby; 381 } 382 else 383 { 384 Oby += "," + OrderBy[i].Cols + " " + OrderBy[i].Orderby; 385 } 386 } 387 } 388 WhereColModel[] WhereItem = Data.Where; 389 string WhereStr = GetWhereStr(WhereItem); 390 string Sql = string.Format(SelectStr, Data.Cols, Data.TableName, WhereStr, Oby); 391 object Calar = SqlHelper.ExecuteScalar(Sql); 392 return Calar; 393 } 394 395 public override List<T> GetTable<T>(SelectModel Data) 396 { 397 SqlHelper.SqlConnectionString = Connstring; 398 OrderByType[] OrderBy = Data.OrderBy; 399 string Oby = string.Empty; 400 if (OrderBy != null) 401 { 402 foreach (OrderByType OB in OrderBy) 403 { 404 if (string.IsNullOrEmpty(Oby)) 405 { 406 Oby = " order by " + OB.Cols + " " + OB.Orderby; 407 } 408 else 409 { 410 Oby += "," + OB.Cols + " " + OB.Orderby; 411 } 412 } 413 } 414 WhereColModel[] WhereItem = Data.Where; 415 string WhereStr = GetWhereStr(WhereItem); 416 417 List<T> Li = new List<T>(); 418 string Sql = string.Format(SelectStr, Data.Cols, Data.TableName, WhereStr, Oby); 419 DataSet Ds = SqlHelper.ExecuteDataSet(Sql); 420 421 for (int n = 0; n < Ds.Tables[0].Rows.Count; n++) 422 { 423 T t = default(T); 424 T tobj = Activator.CreateInstance<T>(); 425 Type ModelCs = tobj.GetType(); 426 DataRow Row = Ds.Tables[0].Rows[n]; 427 PropertyInfo[] Item = ModelCs.GetProperties(); 428 Hashtable _TTable = new Hashtable(); 429 foreach (PropertyInfo P in Item) 430 { 431 YAO.Model.ColumnMap CMap = AttributeProperty<YAO.Model.ColumnMap>.GetTableMapAttribute(P); 432 if (CMap.IsValueTemp) 433 { 434 _TTable.Add(CMap.ValueTempName.ToLower(), P.PropertyType); 435 } 436 } 437 foreach (PropertyInfo P in Item) 438 { 439 YAO.Model.ColumnMap Map = AttributeProperty<YAO.Model.ColumnMap>.GetTableMapAttribute(P); 440 441 if (Map == null || Map.IsSetValue) 442 { 443 if (Row.Table.Columns.Contains(P.Name)) 444 { 445 if (Row[P.Name] != DBNull.Value) 446 { 447 P.SetValue(tobj, Row[P.Name], null); 448 } 449 } 450 } 451 else 452 { 453 if (!Map.IsValueTemp) 454 { 455 string[] keyItem = Map.ParentCol.Split('|'); 456 string[] CkeyItem = Map.KeyCol.Split('|'); 457 Hashtable KeyTable = new Hashtable(); 458 for (int Klen = 0; Klen < keyItem.Length; Klen++) 459 { 460 if (keyItem[Klen] != "") 461 { 462 if (Row.Table.Columns.Contains(keyItem[Klen])) 463 { 464 KeyTable.Add(CkeyItem[Klen], Row[keyItem[Klen]]); 465 } 466 else 467 { 468 KeyTable.Add(CkeyItem[Klen], keyItem[Klen]); 469 } 470 } 471 } 472 if (_TTable.Contains(P.Name.ToLower())) 473 { 474 475 Type _TempType = (Type)_TTable[P.Name.ToLower()]; 476 P.SetValue(tobj, ReadChdInfo(Map, P, KeyTable, _TempType), null); 477 } 478 else 479 { 480 P.SetValue(tobj, ReadChdInfo(Map, P, KeyTable), null); 481 } 482 } 483 } 484 } 485 t = tobj; 486 Li.Add(t); 487 } 488 return Li; 489 } 490 491 public override T Row<T>(SqlProModel Data) 492 { 493 SqlHelper.SqlConnectionString = Connstring; 494 ColumnModel[] DmItem = Data.DataParamer; 495 SqlParameter[] Parames = new SqlParameter[DmItem.Length]; 496 int n = 0; 497 if (DmItem.Length > 0) 498 { 499 foreach (ColumnModel Dm in DmItem) 500 { 501 if (Dm.ColumnValue != null) 502 { 503 SqlParameter par = SqlHelper.SetDataParameter(Dm.ColumnName, (SqlDbType)Dm.DbType, Dm.ColumnLen, Dm.ColumnValue, Dm.Directioin); 504 Parames[n] = par; 505 n++; 506 } 507 } 508 } 509 DataRow Row = SqlHelper.ExecuteDataRow(Data.ProName, CommandType.StoredProcedure, Parames); 510 511 if (Row != null) 512 { 513 T t = default(T); 514 T tobj = Activator.CreateInstance<T>(); 515 Type ModelCs = tobj.GetType(); 516 if (ModelCs.IsClass) 517 { 518 PropertyInfo[] Item = ModelCs.GetProperties(); 519 Hashtable _TTable = new Hashtable(); 520 foreach (PropertyInfo P in Item) 521 { 522 YAO.Model.ColumnMap CMap = AttributeProperty<YAO.Model.ColumnMap>.GetTableMapAttribute(P); 523 if (CMap.IsValueTemp) 524 { 525 _TTable.Add(CMap.ValueTempName.ToLower(), P.PropertyType); 526 } 527 } 528 foreach (PropertyInfo P in Item) 529 { 530 string ColName = P.Name; 531 YAO.Model.ColumnMap Map = AttributeProperty<YAO.Model.ColumnMap>.GetTableMapAttribute(P); 532 533 if (Map == null || Map.IsSetValue) 534 { 535 if (Row.Table.Columns.Contains(ColName)) 536 { 537 object Values = Row[ColName]; 538 if (Values != DBNull.Value) 539 { 540 P.SetValue(tobj, Values, null); 541 } 542 } 543 } 544 else 545 { 546 if (!Map.IsValueTemp) 547 { 548 string[] keyItem = Map.ParentCol.Split('|'); 549 string[] CkeyItem = Map.KeyCol.Split('|'); 550 Hashtable KeyTable = new Hashtable(); 551 for (int Klen = 0; Klen < keyItem.Length; Klen++) 552 { 553 if (keyItem[Klen] != "") 554 { 555 if (Row.Table.Columns.Contains(keyItem[Klen])) 556 { 557 KeyTable.Add(CkeyItem[Klen], Row[keyItem[Klen]]); 558 } 559 else 560 { 561 KeyTable.Add(CkeyItem[Klen], keyItem[Klen]); 562 } 563 } 564 } 565 if (_TTable.Contains(P.Name.ToLower())) 566 { 567 568 Type _TempType = (Type)_TTable[P.Name.ToLower()]; 569 P.SetValue(tobj, ReadChdInfo(Map, P, KeyTable, _TempType), null); 570 } 571 else 572 { 573 P.SetValue(tobj, ReadChdInfo(Map, P, KeyTable), null); 574 } 575 } 576 } 577 } 578 t = tobj; 579 return t; 580 } 581 else 582 { 583 return (T)(object)Row; 584 } 585 } 586 else 587 { 588 return default(T); 589 } 590 } 591 592 public override object Scalar(SqlProModel Data) 593 { 594 SqlHelper.SqlConnectionString = Connstring; 595 ColumnModel[] DmItem = Data.DataParamer; 596 SqlParameter[] Parames = new SqlParameter[DmItem.Length]; 597 int i = 0; 598 if (DmItem.Length > 0) 599 { 600 foreach (ColumnModel Dm in DmItem) 601 { 602 if (Dm.ColumnValue != null) 603 { 604 SqlParameter par = SqlHelper.SetDataParameter(Dm.ColumnName, (SqlDbType)Dm.DbType, Dm.ColumnLen, Dm.ColumnValue, Dm.Directioin); 605 Parames[i] = par; 606 i++; 607 } 608 } 609 } 610 object Calar = SqlHelper.ExecuteScalar(CommandType.StoredProcedure, Data.ProName, Parames); 611 return Calar; 612 } 613 614 public override List<T> GetTable<T>(SqlProModel Data) 615 { 616 SqlHelper.SqlConnectionString = Connstring; 617 List<T> Li = new List<T>(); 618 ColumnModel[] DmItem = Data.DataParamer; 619 SqlParameter[] Parames = new SqlParameter[DmItem.Length]; 620 int i = 0; 621 if (DmItem.Length > 0) 622 { 623 foreach (ColumnModel Dm in DmItem) 624 { 625 if (Dm.ColumnValue != null) 626 { 627 SqlParameter par = SqlHelper.SetDataParameter(Dm.ColumnName, (SqlDbType)Dm.DbType, Dm.ColumnLen, Dm.ColumnValue, Dm.Directioin); 628 Parames[i] = par; 629 i++; 630 } 631 } 632 } 633 DataSet Ds = SqlHelper.ExecuteDataSet(Data.ProName, Parames); 634 for (int n = 0; n < Ds.Tables[0].Rows.Count; n++) 635 { 636 T t = default(T); 637 T tobj = Activator.CreateInstance<T>(); 638 Type ModelCs = tobj.GetType(); 639 DataRow Row = Ds.Tables[0].Rows[n]; 640 PropertyInfo[] Item = ModelCs.GetProperties(); 641 Hashtable _TTable = new Hashtable(); 642 foreach (PropertyInfo P in Item) 643 { 644 YAO.Model.ColumnMap CMap = AttributeProperty<YAO.Model.ColumnMap>.GetTableMapAttribute(P); 645 if (CMap.IsValueTemp) 646 { 647 _TTable.Add(CMap.ValueTempName.ToLower(), P.PropertyType); 648 } 649 } 650 foreach (PropertyInfo P in Item) 651 { 652 YAO.Model.ColumnMap Map = AttributeProperty<YAO.Model.ColumnMap>.GetTableMapAttribute(P); 653 654 if (Map == null || Map.IsSetValue) 655 { 656 if (Row[P.Name] != DBNull.Value) 657 { 658 P.SetValue(tobj, Row[P.Name], null); 659 } 660 } 661 else 662 { 663 if (!Map.IsValueTemp) 664 { 665 if (_TTable.Contains(P.Name.ToLower())) 666 { 667 string[] keyItem = Map.ParentCol.Split('|'); 668 string[] CkeyItem = Map.KeyCol.Split('|'); 669 Hashtable KeyTable = new Hashtable(); 670 for (int Klen = 0; Klen < keyItem.Length; Klen++) 671 { 672 if (keyItem[Klen] != "") 673 { 674 if (Row.Table.Columns.Contains(keyItem[Klen])) 675 { 676 KeyTable.Add(CkeyItem[Klen], Row[keyItem[Klen]]); 677 } 678 else 679 { 680 KeyTable.Add(CkeyItem[Klen], keyItem[Klen]); 681 } 682 } 683 } 684 if (_TTable.Contains(P.Name.ToLower())) 685 { 686 687 Type _TempType = (Type)_TTable[P.Name.ToLower()]; 688 P.SetValue(tobj, ReadChdInfo(Map, P, KeyTable, _TempType), null); 689 } 690 else 691 { 692 P.SetValue(tobj, ReadChdInfo(Map, P, KeyTable), null); 693 } 694 } 695 } 696 } 697 } 698 t = tobj; 699 Li.Add(t); 700 } 701 return Li; 702 } 703 704 string GetWhereStr(WhereColModel[] WhereItem) 705 { 706 if (WhereItem != null) 707 { 708 ComSymbol Sbol; 709 Hashtable Symbol = Sbol.Symbol(); 710 string WhereStr = " where 1=1 "; 711 foreach (WhereColModel Where in WhereItem) 712 { 713 string Quotes = Where.Quotes ? "'" : ""; 714 string LeftQuotes = Where.leftQuotes ? "'" : ""; 715 string BeginBrackets = Where.BeginBrackets ? "(" : ""; 716 string EndBrackets = Where.EndBrackets ? ")" : ""; 717 string AndOr = Where.AndOr ? " and " : " or "; 718 string Sym = Symbol[Where.Com.ToString()].ToString(); 719 string WhereSyms = string.Format(Sym, LeftQuotes, Where.ColumnName, LeftQuotes, Quotes, Where.ColumnValue, Quotes); 720 WhereStr += AndOr + BeginBrackets + WhereSyms + EndBrackets; 721 } 722 return WhereStr; 723 } 724 else 725 { 726 return ""; 727 } 728 } 729 730 public override List<T> GetTable<T>(SqlProModel Data, ref Hashtable OutPut) 731 { 732 SqlHelper.SqlConnectionString = Connstring; 733 List<T> Li = new List<T>(); 734 ColumnModel[] DmItem = Data.DataParamer; 735 SqlParameter[] Parames = new SqlParameter[DmItem.Length]; 736 int i = 0; 737 if (DmItem.Length > 0) 738 { 739 foreach (ColumnModel Dm in DmItem) 740 { 741 if (Dm.ColumnValue != null) 742 { 743 SqlParameter par = SqlHelper.SetDataParameter(Dm.ColumnName, (SqlDbType)Dm.DbType, Dm.ColumnLen, Dm.ColumnValue, Dm.Directioin); 744 Parames[i] = par; 745 i++; 746 } 747 } 748 } 749 DataSet Ds = SqlHelper.ExecuteDataSet(ref OutPut, Data.ProName, Parames); 750 751 //Tb.PrimaryKey 752 for (int n = 0; n < Ds.Tables[0].Rows.Count; n++) 753 { 754 T t = default(T);//指定T一个初始值 755 T tobj = Activator.CreateInstance<T>(); 756 Type ModelCs = tobj.GetType(); 757 DataRow Row = Ds.Tables[0].Rows[n]; 758 759 PropertyInfo[] Item = ModelCs.GetProperties(); 760 Hashtable _TTable = new Hashtable(); 761 foreach (PropertyInfo P in Item) 762 { 763 YAO.Model.ColumnMap CMap = AttributeProperty<YAO.Model.ColumnMap>.GetTableMapAttribute(P); 764 if (CMap.IsValueTemp) 765 { 766 _TTable.Add(CMap.ValueTempName.ToLower(), P.PropertyType); 767 } 768 } 769 foreach (PropertyInfo P in Item) 770 { 771 YAO.Model.ColumnMap Map = AttributeProperty<YAO.Model.ColumnMap>.GetTableMapAttribute(P); 772 if (Map==null || Map.IsSetValue) 773 { 774 if (Row[P.Name] != DBNull.Value) 775 { 776 P.SetValue(tobj, Row[P.Name], null); 777 } 778 } 779 else 780 { 781 if (!Map.IsValueTemp) 782 { 783 string[] keyItem = Map.ParentCol.Split('|'); 784 string[] CkeyItem = Map.KeyCol.Split('|'); 785 Hashtable KeyTable = new Hashtable(); 786 for (int Klen = 0; Klen < keyItem.Length; Klen++) 787 { 788 if (keyItem[Klen] != "") 789 { 790 if (Row.Table.Columns.Contains(keyItem[Klen])) 791 { 792 KeyTable.Add(CkeyItem[Klen], Row[keyItem[Klen]]); 793 } 794 else 795 { 796 KeyTable.Add(CkeyItem[Klen], keyItem[Klen]); 797 } 798 } 799 } 800 801 if (_TTable.Contains(P.Name.ToLower())) 802 { 803 Type _TempType = (Type)_TTable[P.Name.ToLower()]; 804 P.SetValue(tobj, ReadChdInfo(Map, P, KeyTable, _TempType), null); 805 } 806 else 807 { 808 P.SetValue(tobj, ReadChdInfo(Map, P, KeyTable), null); 809 } 810 } 811 } 812 } 813 t = tobj; 814 Li.Add(t); 815 } 816 return Li; 817 } 818 819 public override string UpDateSql<T>(T t, WhereColModel[] Dm) 820 { 821 Type ModelCs = t.GetType(); 822 YAO.Model.TableMap TMap = ModelAttributeTag<YAO.Model.TableMap>.GetTableMapAttribute(ModelCs); 823 string Table = TMap.TableName; 824 PropertyInfo[] Item = ModelCs.GetProperties(); 825 string Cols = string.Empty; 826 827 bool SetState = true; 828 foreach (PropertyInfo P in Item) 829 { 830 ColumnMap Map = AttributeProperty<ColumnMap>.GetTableMapAttribute(P); 831 if (Map == null) 832 { 833 SetState = false; 834 break; 835 } 836 if (!Map.Mark) 837 { 838 object Value = P.GetValue(t, null); 839 if (Value != null) 840 { 841 if (Cols == string.Empty) 842 { 843 Cols = P.Name + "='" + Value + "'"; 844 } 845 else 846 { 847 Cols += "," + P.Name + "='" + Value + "'"; 848 } 849 } 850 } 851 } 852 if (SetState) 853 { 854 string WhereStr = GetWhereStr(Dm); 855 string Sql = string.Format(UpdateStr, Table, Cols, WhereStr); 856 return Sql; 857 } 858 else 859 { 860 return string.Empty; 861 } 862 } 863 864 public override int RunAnySql(string SqlStr) 865 { 866 SqlHelper.SqlConnectionString = Connstring; 867 return SqlHelper.ExecuteNonQuery(SqlStr); 868 } 869 870 public override string InsertSql<T>(T t) 871 { 872 Type ModelCs = t.GetType(); 873 YAO.Model.TableMap TMap = ModelAttributeTag<YAO.Model.TableMap>.GetTableMapAttribute(ModelCs); 874 string Table = TMap.TableName; 875 PropertyInfo[] Item = ModelCs.GetProperties(); 876 string Cols = string.Empty; 877 string Values = string.Empty; 878 bool SetState = true; 879 foreach (PropertyInfo P in Item) 880 { 881 ColumnMap Map = AttributeProperty<ColumnMap>.GetTableMapAttribute(P); 882 if (Map == null) 883 { 884 SetState = false; 885 break; 886 } 887 if (!Map.Mark) 888 { 889 object Value = P.GetValue(t, null); 890 if (Value != null) 891 { 892 if (Cols == string.Empty) 893 { 894 Cols = Map.ColumnName; 895 Values = "'" + Value + "'"; 896 } 897 else 898 { 899 Cols += "," + Map.ColumnName; 900 Values += ",'" + Value + "'"; 901 } 902 } 903 } 904 } 905 if (SetState) 906 { 907 string Sql = string.Format(InsertStr, Table, Cols, Values); 908 return Sql; 909 } 910 else 911 { 912 return string.Empty; 913 } 914 } 915 916 public override string DeleteSql(TableModel Data) 917 { 918 string Table = Data.TableName; 919 WhereColModel[] Where = Data.Where; 920 string WhereStr = GetWhereStr(Where); 921 if (Data.CmdType == DataCmdType.Delete) 922 { 923 string Sql = string.Format(DeleteStr, Table, WhereStr); 924 return Sql; 925 } 926 else 927 { 928 return string.Empty; 929 } 930 } 931 932 public override int SproCmd(SqlProModel Data) 933 { 934 Hashtable outParameters = null; 935 return SproCmd(Data, ref outParameters); 936 } 937 938 public override int SproCmd(SqlProModel Data, ref Hashtable OutPut) 939 { 940 SqlHelper.SqlConnectionString = Connstring; 941 ColumnModel[] DmItem = Data.DataParamer; 942 SqlParameter[] Parames = new SqlParameter[DmItem.Length]; 943 int i = 0; 944 if (DmItem.Length > 0) 945 { 946 foreach (ColumnModel Dm in DmItem) 947 { 948 if (Dm.ColumnValue != null) 949 { 950 SqlParameter par = SqlHelper.SetDataParameter(Dm.ColumnName, (SqlDbType)Dm.DbType, Dm.ColumnLen, Dm.ColumnValue, Dm.Directioin); 951 Parames[i] = par; 952 i++; 953 } 954 } 955 } 956 return SqlHelper.ExecuteNonQuery(ref OutPut, Data.ProName, Parames); 957 } 958 959 public override DataSet GetTable(SqlProModel Data) 960 { 961 SqlHelper.SqlConnectionString = Connstring; 962 ColumnModel[] DmItem = Data.DataParamer; 963 SqlParameter[] Parames = new SqlParameter[DmItem.Length]; 964 int i = 0; 965 if (DmItem.Length > 0) 966 { 967 foreach (ColumnModel Dm in DmItem) 968 { 969 if (Dm.ColumnValue != null) 970 { 971 SqlParameter par = SqlHelper.SetDataParameter(Dm.ColumnName, (SqlDbType)Dm.DbType, Dm.ColumnLen, Dm.ColumnValue, Dm.Directioin); 972 Parames[i] = par; 973 i++; 974 } 975 } 976 } 977 DataSet Ds = SqlHelper.ExecuteDataSet(Data.ProName, Parames); 978 return Ds; 979 } 980 981 public override DataSet GetTable(SqlProModel Data, ref Hashtable OutPut) 982 { 983 SqlHelper.SqlConnectionString = Connstring; 984 ColumnModel[] DmItem = Data.DataParamer; 985 SqlParameter[] Parames = new SqlParameter[DmItem.Length]; 986 int i = 0; 987 if (DmItem.Length > 0) 988 { 989 foreach (ColumnModel Dm in DmItem) 990 { 991 if (Dm.ColumnValue != null) 992 { 993 SqlParameter par = SqlHelper.SetDataParameter(Dm.ColumnName, (SqlDbType)Dm.DbType, Dm.ColumnLen, Dm.ColumnValue, Dm.Directioin); 994 Parames[i] = par; 995 i++; 996 } 997 } 998 } 999 DataSet Ds = SqlHelper.ExecuteDataSet(ref OutPut, Data.ProName, Parames); 1000 return Ds; 1001 } 1002 1003 1004 #region 子数据获取 1005 string ChdReadWhere(Hashtable CkeyCol) 1006 { 1007 string Where = ""; 1008 string WhereStr = ""; 1009 foreach (DictionaryEntry de in CkeyCol) 1010 { 1011 Where = " where 1=1"; 1012 WhereStr += " and " + de.Key + "='" + de.Value.ToString() + "'"; 1013 } 1014 Where += WhereStr; 1015 return Where; 1016 } 1017 List<object> ReadList(ColumnMap Map, Hashtable KeyValues, Type _ChdType, Type _TempTable) 1018 { 1019 PropertyInfo[] Item = _TempTable.GetProperties(); 1020 1021 List<object> Li = new List<object>(); 1022 string WhereStr = ChdReadWhere(KeyValues); 1023 string Oby = string.Empty; 1024 if (Map.IsOrderBy) 1025 { 1026 Oby = " order by " + Map.OrderByKey + " " + Map.OrderBy; 1027 } 1028 string Sql = string.Format(SelectStr, Map.ReadValueCols, Map.ValueTableName, WhereStr, Oby); 1029 DataSet Ds = SqlHelper.ExecuteDataSet(Sql); 1030 Hashtable _TTable = new Hashtable(); 1031 foreach (PropertyInfo P in Item) 1032 { 1033 YAO.Model.ColumnMap CMap = AttributeProperty<YAO.Model.ColumnMap>.GetTableMapAttribute(P); 1034 if (CMap.IsValueTemp) 1035 { 1036 _TTable.Add(CMap.ValueTempName.ToLower(), P.PropertyType); 1037 } 1038 } 1039 for (int n = 0; n < Ds.Tables[0].Rows.Count; n++) 1040 { 1041 DataRow Row = Ds.Tables[0].Rows[n]; 1042 dynamic TempTable = Activator.CreateInstance(_TempTable); 1043 foreach (PropertyInfo P in Item) 1044 { 1045 YAO.Model.ColumnMap CMap = AttributeProperty<YAO.Model.ColumnMap>.GetTableMapAttribute(P); 1046 1047 if (CMap == null || CMap.IsSetValue) 1048 { 1049 if (Row[P.Name] != DBNull.Value) 1050 { 1051 P.SetValue(TempTable, Row[P.Name], null); 1052 } 1053 } 1054 else 1055 { 1056 if (!CMap.IsValueTemp) 1057 { 1058 string[] keyItem = CMap.ParentCol.Split('|'); 1059 string[] CkeyItem = CMap.KeyCol.Split('|'); 1060 Hashtable KeyTable = new Hashtable(); 1061 for (int Klen = 0; Klen < keyItem.Length; Klen++) 1062 { 1063 if (keyItem[Klen] != "") 1064 { 1065 if (Row.Table.Columns.Contains(keyItem[Klen])) 1066 { 1067 KeyTable.Add(CkeyItem[Klen], Row[keyItem[Klen]]); 1068 } 1069 else 1070 { 1071 KeyTable.Add(CkeyItem[Klen], keyItem[Klen]); 1072 } 1073 } 1074 } 1075 if (_TTable.Contains(P.Name.ToLower())) 1076 { 1077 1078 Type _TempType = (Type)_TTable[P.Name.ToLower()]; 1079 P.SetValue(TempTable, ReadChdInfo(CMap, P, KeyTable, _TempType), null); 1080 } 1081 else 1082 { 1083 P.SetValue(TempTable, ReadChdInfo(CMap, P, KeyTable), null); 1084 } 1085 } 1086 } 1087 } 1088 1089 Li.Add(TempTable); 1090 } 1091 return Li; 1092 } 1093 object ReadRow(ColumnMap Map, Hashtable KeyValues, Type _ChdType) 1094 { 1095 PropertyInfo[] Item = _ChdType.GetProperties(); 1096 1097 List<object> Li = new List<object>(); 1098 string WhereStr = ChdReadWhere(KeyValues); 1099 string Oby = string.Empty; 1100 if (Map.IsOrderBy) 1101 { 1102 Oby = " order by " + Map.OrderByKey + " " + Map.OrderBy; 1103 } 1104 string Sql = string.Format(SelectStr, Map.ReadValueCols, Map.ValueTableName, WhereStr, Oby); 1105 DataRow Row = SqlHelper.ExecuteDataRow(Sql); 1106 if (Row != null) 1107 { 1108 Object Ctobj = Activator.CreateInstance(_ChdType); 1109 Hashtable _TTable = new Hashtable(); 1110 foreach (PropertyInfo P in Item) 1111 { 1112 YAO.Model.ColumnMap CMap = AttributeProperty<YAO.Model.ColumnMap>.GetTableMapAttribute(P); 1113 if (CMap.IsValueTemp) 1114 { 1115 _TTable.Add(CMap.ValueTempName.ToLower(), P.PropertyType); 1116 } 1117 } 1118 foreach (PropertyInfo P in Item) 1119 { 1120 YAO.Model.ColumnMap CMap = AttributeProperty<YAO.Model.ColumnMap>.GetTableMapAttribute(P); 1121 1122 if (CMap == null || CMap.IsSetValue) 1123 { 1124 if (Row[P.Name] != DBNull.Value) 1125 { 1126 P.SetValue(Ctobj, Row[P.Name], null); 1127 } 1128 } 1129 else 1130 { 1131 if (!CMap.IsValueTemp) 1132 { 1133 string[] keyItem = CMap.ParentCol.Split('|'); 1134 string[] CkeyItem = CMap.KeyCol.Split('|'); 1135 Hashtable KeyTable = new Hashtable(); 1136 for (int Klen = 0; Klen < keyItem.Length; Klen++) 1137 { 1138 if (keyItem[Klen] != "") 1139 { 1140 if (Row.Table.Columns.Contains(keyItem[Klen])) 1141 { 1142 KeyTable.Add(CkeyItem[Klen], Row[keyItem[Klen]]); 1143 } 1144 else 1145 { 1146 KeyTable.Add(CkeyItem[Klen], keyItem[Klen]); 1147 } 1148 } 1149 } 1150 if (_TTable.Contains(P.Name.ToLower())) 1151 { 1152 1153 Type _TempType = (Type)_TTable[P.Name.ToLower()]; 1154 P.SetValue(Ctobj, ReadChdInfo(CMap, P, KeyTable, _TempType), null); 1155 } 1156 else 1157 { 1158 P.SetValue(Ctobj, ReadChdInfo(CMap, P, KeyTable), null); 1159 } 1160 } 1161 } 1162 } 1163 return Ctobj; 1164 } 1165 else 1166 { 1167 return null; 1168 } 1169 } 1170 object ReadScalar(ColumnMap Map, Hashtable KeyValues, Type _ChdType) 1171 { 1172 Type genericArgTypes = _ChdType.GetGenericArguments()[0]; 1173 List<object> ll = new List<object>(); 1174 PropertyInfo[] Item = genericArgTypes.GetProperties(); 1175 1176 List<object> Li = new List<object>(); 1177 string WhereStr = ChdReadWhere(KeyValues); 1178 string Oby = string.Empty; 1179 if (Map.IsOrderBy) 1180 { 1181 Oby = " order by " + Map.OrderByKey + " " + Map.OrderBy; 1182 } 1183 string Sql = string.Format(SelectStr, Map.ReadValueCols, Map.ValueTableName, WhereStr, Oby); 1184 object ScalarValue = SqlHelper.ExecuteScalar(Sql); 1185 return ScalarValue; 1186 } 1187 object ReadChdInfo(ColumnMap Map, PropertyInfo P, Hashtable KeyValues, Type _TempTable) 1188 { 1189 Type ChdType = P.PropertyType; 1190 object ChdP = (object)Activator.CreateInstance(ChdType); 1191 Type _ChdType = ChdP.GetType(); 1192 object Result = null; 1193 switch (Map.ValueTp) 1194 { 1195 case Model.ValueType.List: 1196 Result = ReadList(Map, KeyValues, _ChdType, _TempTable); 1197 break; 1198 case Model.ValueType.Row: 1199 Result= ReadRow(Map, KeyValues, _ChdType); 1200 break; 1201 case Model.ValueType.Scalar: 1202 Result = ReadScalar(Map, KeyValues, _ChdType); 1203 break; 1204 } 1205 return Result; 1206 } 1207 object ReadChdInfo(ColumnMap Map, PropertyInfo P, Hashtable KeyValues) 1208 { 1209 Type ChdType = P.PropertyType; 1210 object ChdP = (object)Activator.CreateInstance(ChdType); 1211 Type _ChdType = ChdP.GetType(); 1212 object Result = null; 1213 switch (Map.ValueTp) 1214 { 1215 case Model.ValueType.Row: 1216 Result = ReadRow(Map, KeyValues, _ChdType); 1217 break; 1218 case Model.ValueType.Scalar: 1219 Result = ReadScalar(Map, KeyValues, _ChdType); 1220 break; 1221 } 1222 return Result; 1223 } 1224 #endregion 1225 }
补充：
出现类型错误提示，可采用下面的方法解决，泛型类型转换
object Values =null;
if (!property.PropertyType.IsGenericType)
{
//非泛型
property.SetValue(tobj, Values==null ? null : Convert.ChangeType(Values, property.PropertyType), null);
}
else
{
//泛型Nullable<>
Type genericTypeDefinition = property.PropertyType.GetGenericTypeDefinition();
if (genericTypeDefinition == typeof(Nullable<>))
{
property.SetValue(tobj, Values==null ? null : Convert.ChangeType(Values, Nullable.GetUnderlyingType(property.PropertyType)), null);
}
}
