.netorm连接mysql_ORM映射框架总结--数据库操作库(精修版)

1 /**2 * 2010-2-263 *4 * 情 缘5 *6 * 当前类实现了IDbHelper接口,主要用于对实体进行7 * 增删改查操作。8 *9 **/10 usingSystem;11 usingSystem.Collections.Generic;12 usingSystem.Linq;13 usingSystem.Text;14 usingCommonData.Entity;15 usingSystem.Data;16 usingCommonData.Model.Core;17 18 namespaceCommonData.Data.Core.SQLCore19 {20 publicclassSqlHelper:IDbHelper21 {22 privateIBaseHelper baseHelper;23 ///24 ///数据库操作公共接口25 ///26 publicIBaseHelper BaseHelper27 {28 get29 {30 if(baseHelper==null)31 {32 baseHelper=newBaseHelper();33 }34 returnbaseHelper;35 }36 set37 {38 baseHelper=value;39 }40 }41 42 privateIDbFactory factory;43 ///44 ///sql语句构造工厂对象45 ///46 publicIDbFactory Factory47 {48 get49 {50 if(factory==null)51 {52 factory=newSqlFactory();53 }54 returnfactory;55 }56 set57 {58 factory=value;59 }60 }61 62 63 ///64 ///添加实体对象65 ///将实体数据信息映射为一条插入sql语句66 ///67 ///实体公共接口68 ///69 publicintAdd(IEntity entity)70 {71 IDataParameter[] param=null;72 stringsql=Factory.CreateInsertSql(entity,outparam);73 using(IDbProvider provider=newSqlProvider())74 {75 returnBaseHelper.ExecuteNonQuery(provider,sql,param);76 }77 }78 79 ///80 ///添加实体对象81 ///将泛型数据信息映射为一条插入sql语句82 ///该泛型类必须实现IEntity 接口83 ///84 ///实体泛型类型85 ///泛型实体值86 ///87 publicintAdd(T t)whereT : IEntity88 {89 IDataParameter[] param=null;90 stringsql=Factory.CreateInsertSql(t,outparam);91 using(IDbProvider provider=newSqlProvider())92 {93 returnBaseHelper.ExecuteNonQuery(provider, sql, param);94 }95 }96 97 ///98 ///添加实体对象99 ///value 的类型必须和type一致，这样才能保存值100 ///101 ///实体类型102 ///实体类型实例103 ///104 publicintAdd(Type type,objectvalue)105 {106 IDataParameter[] param=null;107 stringsql=Factory.CreateInsertSql(type,value,outparam);108 using(IDbProvider provider=newSqlProvider())109 {110 returnBaseHelper.ExecuteNonQuery(provider, sql, param);111 }112 }113 114 ///115 ///根据实体公共接口修改该实体信息116 ///该实体是根据主键修改117 ///118 ///实体公共接口119 ///120 publicintUpdate(IEntity entity)121 {122 IDataParameter[] param=null;123 stringsql=Factory.CreateUpdateSql(entity,outparam);124 using(IDbProvider provider=newSqlProvider())125 {126 returnBaseHelper.ExecuteNonQuery(provider, sql, param);127 }128 }129 130 ///131 ///根据实体的某个属性修改数据132 ///entity 中必须包含该属性，而且该属性的133 ///值不能为空134 ///135 ///实体公共接口136 ///实体属性名称137 ///138 publicintUpdate(IEntity entity,stringpropertyName)139 {140 IDataParameter[] param=null;141 stringsql=Factory.CreateUpdateSql(entity,outparam, propertyName);142 using(IDbProvider provider=newSqlProvider())143 {144 returnBaseHelper.ExecuteNonQuery(provider, sql, param);145 }146 }147 148 ///149 ///根据实体的多个属性修改数据150 ///数组中的属性名称必须存在.151 ///传递参数数组不能为null152 ///153 ///实体公共接口154 ///属性名称数组155 ///156 publicintUpdate(IEntity entity,string[] propertyNames)157 {158 IDataParameter[] param=null;159 stringsql=Factory.CreateUpdateSql(entity,outparam,propertyNames);160 using(IDbProvider provider=newSqlProvider())161 {162 returnBaseHelper.ExecuteNonQuery(provider, sql, param);163 }164 }165 166 ///167 ///修改实体信息168 ///该实体是根据主键修改169 ///170 ///实体泛型类型171 ///泛型实例172 ///173 publicintUpdate(T t)whereT : IEntity174 {175 IDataParameter[] param=null;176 stringsql=Factory.CreateUpdateSql(typeof(T),t,outparam);177 using(IDbProvider provider=newSqlProvider())178 {179 returnBaseHelper.ExecuteNonQuery(provider, sql, param);180 }181 }182 183 ///184 ///根据泛型类的某个实体属性来修改该数据。185 ///泛型实体实例中该属性不能为空186 ///187 ///泛型类188 ///泛型实例189 ///190 ///191 publicintUpdate(T t,stringpropertyName)whereT : IEntity192 {193 IDataParameter[] param=null;194 stringsql=Factory.CreateUpdateSql(typeof(T), t,outparam,propertyName);195 using(IDbProvider provider=newSqlProvider())196 {197 returnBaseHelper.ExecuteNonQuery(provider, sql, param);198 }199 }200 201 ///202 ///根据实体的多个属性修改数据203 ///数组中的属性名称在泛型类中必须存在，204 ///数组不能传递null值205 ///206 ///泛型类207 ///泛型实例208 ///属性名称数组209 ///210 publicintUpdate(T t,string[] propertyNames)whereT : IEntity211 {212 IDataParameter[] param=null;213 stringsql=Factory.CreateUpdateSql(typeof(T), t,outparam, propertyNames);214 using(IDbProvider provider=newSqlProvider())215 {216 returnBaseHelper.ExecuteNonQuery(provider, sql, param);217 }218 }219 220 ///221 ///修改实体信息222 ///该实体是根据主键修改223 ///224 ///实体类型225 ///实体对象实例226 ///227 publicintUpdate(Type type,objectvalue)228 {229 IDataParameter[] param=null;230 stringsql=Factory.CreateUpdateSql(type,value,outparam);231 using(IDbProvider provider=newSqlProvider())232 {233 returnBaseHelper.ExecuteNonQuery(provider, sql, param);234 }235 }236 237 ///238 ///根据实体的某个属性来修改数据信息239 ///该方法使用Type 来确定需要修改的实体对象240 ///value 实例中必须包含propertyName 这个属性241 ///而且propertyName 属性值不能为空242 ///243 ///实体类型244 ///实体实例245 ///属性名称246 ///247 publicintUpdate(Type type,objectvalue,stringpropertyName)248 {249 IDataParameter[] param=null;250 stringsql=Factory.CreateUpdateSql(type, value,outparam, propertyName);251 using(IDbProvider provider=newSqlProvider())252 {253 returnBaseHelper.ExecuteNonQuery(provider, sql, param);254 }255 }256 257 ///258 ///根据实体的多个属性来修改数据信息259 ///该方法使用Type 来确定需要修改的实体对象260 ///数组中的属性名称在泛型类中必须存在，261 ///而且数组传递参数不能为null262 ///263 ///实体类型264 ///实体实例265 ///属性名称数组266 ///267 publicintUpdate(Type type,objectvalue,string[] propertyNames)268 {269 IDataParameter[] param=null;270 stringsql=Factory.CreateUpdateSql(type, value,outparam, propertyNames);271 using(IDbProvider provider=newSqlProvider())272 {273 returnBaseHelper.ExecuteNonQuery(provider, sql, param);274 }275 }276 277 278 279 280 ///281 ///删除实体对象282 ///该方法是根据实体对象主键删除数据283 ///284 ///实体公共接口285 ///286 publicintDelete(IEntity entity)287 {288 IDataParameter[] param=null;289 stringsql=Factory.CreateDeleteSql(entity,outparam);290 using(IDbProvider provider=newSqlProvider())291 {292 returnBaseHelper.ExecuteNonQuery(provider, sql, param);293 }294 }295 296 ///297 ///根据某个实体属性删除数据298 ///该实体必须包含指定的属性名称299 ///而且实体属性值不能为空300 ///301 ///实体属性公共接口302 ///实体属性名称303 ///304 publicintDelete(IEntity entity,stringpropertyName)305 {306 IDataParameter[] param=null;307 stringsql=Factory.CreateDeleteSql(entity,outparam,propertyName);308 using(IDbProvider provider=newSqlProvider())309 {310 returnBaseHelper.ExecuteNonQuery(provider, sql, param);311 }312 }313 314 ///315 ///根据实体多个属性删除数据316 ///实体中必须包含该属性317 ///传递参数数组不能为空318 ///319 ///实体属性公共接口320 ///实体属性名称数组321 ///322 publicintDelete(IEntity entity,string[] propertyNames)323 {324 IDataParameter[] param=null;325 stringsql=Factory.CreateDeleteSql(entity,outparam, propertyNames);326 using(IDbProvider provider=newSqlProvider())327 {328 returnBaseHelper.ExecuteNonQuery(provider, sql, param);329 }330 }331 332 ///333 ///根据泛型类删除数据334 ///该方法是根据实体的主键删除的335 ///336 ///泛型类337 ///泛型实例338 ///339 publicintDelete(T t)whereT :class340 {341 IDataParameter[] param=null;342 stringsql=Factory.CreateDeleteSql(typeof(T), t,outparam);343 using(IDbProvider provider=newSqlProvider())344 {345 returnBaseHelper.ExecuteNonQuery(provider, sql, param);346 }347 }348 349 ///350 ///根据泛型类的某个属性删除数据351 ///泛型类中必须存在该属性，而且352 ///属性值不能为空353 ///354 ///泛型类型355 ///泛型类实例356 ///属性名称357 ///358 publicintDelete(T t,stringpropertyName)whereT :class359 {360 IDataParameter[] param=null;361 stringsql=Factory.CreateDeleteSql(typeof(T), t,outparam,propertyName);362 using(IDbProvider provider=newSqlProvider())363 {364 returnBaseHelper.ExecuteNonQuery(provider, sql, param);365 }366 }367 368 ///369 ///根据泛型类型的多个属性删除数据370 ///泛型类型中必须存在这些属性，传371 ///递参数的时候不能为null372 ///373 ///泛型类374 ///泛型实例375 ///属性名称数组376 ///377 publicintDelete(T t,string[] propertyNames)whereT :class378 {379 IDataParameter[] param=null;380 stringsql=Factory.CreateDeleteSql(typeof(T), t,outparam, propertyNames);381 using(IDbProvider provider=newSqlProvider())382 {383 returnBaseHelper.ExecuteNonQuery(provider, sql, param);384 }385 }386 387 ///388 ///根据实体的类型删除数据。389 ///value 中的类型由type确定390 ///391 ///实体类型392 ///实体对象实例393 ///394 publicintDelete(Type type,objectvalue)395 {396 IDataParameter[] param=null;397 stringsql=Factory.CreateDeleteSql(type,value,outparam);398 using(IDbProvider provider=newSqlProvider())399 {400 returnBaseHelper.ExecuteNonQuery(provider, sql, param);401 }402 }403 404 ///405 ///根据实体的类型的某个属性删除数据。406 ///value 中的类型由type确定407 ///propertyName 属性名称必须在value408 ///对象中存在409 ///410 ///实体类型411 ///实体对象实例412 ///属性名称413 ///414 publicintDelete(Type type,objectvalue,stringpropertyName)415 {416 IDataParameter[] param=null;417 stringsql=Factory.CreateDeleteSql(type, value,outparam,propertyName);418 using(IDbProvider provider=newSqlProvider())419 {420 returnBaseHelper.ExecuteNonQuery(provider, sql, param);421 }422 }423 424 ///425 ///根据实体的类型的某个属性删除数据。426 ///value 中的类型由type确定427 ///propertyName 属性名称必须在value428 ///对象中存在429 ///430 ///实体类型431 ///实体对象实例432 ///属性名称数组433 ///434 publicintDelete(Type type,objectvalue,string[] propertyNames)435 {436 IDataParameter[] param=null;437 stringsql=Factory.CreateDeleteSql(type, value,outparam, propertyNames);438 using(IDbProvider provider=newSqlProvider())439 {440 returnBaseHelper.ExecuteNonQuery(provider, sql, param);441 }442 }443 444 ///445 ///根据主键查询实体对象446 ///447 ///泛型类型448 ///主键值449 ///450 publicT GetEntity(objectpkPropertyValue)whereT :class451 {452 IDataParameter[] param=null;453 stringsql=Factory.CreateSingleSql(typeof(T), pkPropertyValue,outparam);454 using(IDbProvider provider=newSqlProvider())455 {456 returnBaseHelper.ConvertToEntity(BaseHelper.ExecuteDataReader(provider,sql,param));457 }458 }459 460 ///461 ///根据实体类的类型和主键值查询实体对象462 ///使用 type 确定实体，主键确定数据的唯463 ///一性464 ///465 ///实体类型466 ///主键值467 ///468 publicobjectGetEntity(Type type,objectpkPropertyValue)469 {470 IDataParameter[] param=null;471 stringsql=Factory.CreateSingleSql(type,pkPropertyValue,outparam);472 using(IDbProvider provider=newSqlProvider())473 {474 returnbaseHelper.ConvertToEntity(type,BaseHelper.ExecuteDataReader(provider,sql,param));475 }476 }477 478 ///479 ///根据某个实体的属性来查询实体对象480 ///该属性值能确定数据库唯一数据行481 ///482 ///实体泛型类483 ///属性名称484 ///属性值485 ///486 publicT GetEntity(stringpropertyName,objectpropertyValue)whereT :class487 {488 IDataParameter[] param=null;489 stringsql=Factory.CreateQueryByPropertySql(typeof(T),propertyName,propertyValue,outparam);490 using(IDbProvider provider=newSqlProvider())491 {492 returnBaseHelper.ConvertToEntity(BaseHelper.ExecuteDataReader(provider,sql,param));493 }494 }495 496 ///497 ///根据某个实体的属性来查询实体对象498 ///该属性值能确定数据库唯一数据行499 ///500 ///实体类型501 ///属性名称502 ///属性值503 ///504 publicobjectGetEntity(Type type,stringpropertyName,objectpropertyValue)505 {506 IDataParameter[] param=null;507 stringsql=Factory.CreateQueryByPropertySql(type, propertyName, propertyValue,outparam);508 using(IDbProvider provider=newSqlProvider())509 {510 returnBaseHelper.ConvertToEntity(type,BaseHelper.ExecuteDataReader(provider,sql,param));511 }512 }513 514 ///515 ///根据某个实体的多个属性来查询实体对象516 ///517 ///泛型类518 ///实体公共接口519 ///属性名称数组520 ///521 publicT GetEntity(IEntity entity,string[] propertyNames)whereT :class522 {523 IDataParameter[] param=null;524 stringsql=Factory.CreateSingleSql(entity,outparam,propertyNames);525 using(IDbProvider provider=newSqlProvider())526 {527 returnBaseHelper.ConvertToEntity(BaseHelper.ExecuteDataReader(provider,sql,param));528 }529 }530 531 ///532 ///根据某个实体的多个属性来查询实体对象533 ///实体根据type类型来确定534 ///535 ///实体类型536 ///实体公共接口537 ///属性名称数组538 ///539 publicobjectGetEntity(Type type, IEntity entity,string[] propertyNames)540 {541 IDataParameter[] param=null;542 stringsql=Factory.CreateSingleSql(type,entity,outparam,propertyNames);543 using(IDbProvider provider=newSqlProvider())544 {545 returnBaseHelper.ConvertToEntity(type,BaseHelper.ExecuteDataReader(provider,sql,param));546 }547 }548 549 550 ///551 ///查询该类型实体数据行数552 ///553 ///实体类型554 ///555 publicintGetCount(Type type)556 {557 stringsql=Factory.CreateConverageSql(type,Converage.Count);558 using(IDbProvider provider=newSqlProvider())559 {560 return(int)BaseHelper.ExecuteScalar(provider,sql);561 }562 }563 564 ///565 ///查询该类型实体数据行数566 ///567 ///泛型类型568 ///569 publicintGetCount()whereT :class570 {571 stringsql=Factory.CreateConverageSql(typeof(T), Converage.Count);572 using(IDbProvider provider=newSqlProvider())573 {574 return(int)BaseHelper.ExecuteScalar(provider, sql);575 }576 }577 578 ///579 ///根据条件查询实体数据行数580 ///581 ///实体类型582 ///实体属性键值对583 ///查询组件584 ///585 publicintGetCount(Type type, IDictionarydic, ConditionComponent component)586 {587 IDataParameter[] param=null;588 stringsql=Factory.CreateConverageSql(type,Converage.Count,"",dic,outparam,component);589 using(IDbProvider provider=newSqlProvider())590 {591 return(int)BaseHelper.ExecuteScalar(provider, sql, param);592 }593 }594 595 ///596 ///查询所有实体集合597 ///598 ///泛型类型599 ///600 publicIListGetList()whereT :class601 {602 stringsql=Factory.CreateQuerySql(typeof(T));603 using(IDbProvider provider=newSqlProvider())604 {605 returnBaseHelper.ConvertToList(BaseHelper.ExecuteDataReader(provider,sql));606 }607 }608 609 ///610 ///根据某个实体属性查询实体集合611 ///612 ///泛型类型613 ///属性名称614 ///属性值615 ///616 publicIListGetList(stringpropertyName,objectvalue)whereT :class617 {618 IDataParameter[] param=null;619 stringsql=Factory.CreateQueryByPropertySql(typeof(T),propertyName,value,outparam);620 using(IDbProvider provider=newSqlProvider())621 {622 returnBaseHelper.ConvertToList(BaseHelper.ExecuteDataReader(provider, sql,param));623 }624 }625 626 627 ///628 ///根据多个属性查询实体集合629 ///630 ///泛型类型631 ///实体公共接口632 ///属性名称633 ///634 publicIListGetList(IDictionarydic)whereT :class635 {636 IDataParameter[] param=null;637 stringsql=Factory.CreateQueryByPropertySql(typeof(T), dic,outparam);638 using(IDbProvider provider=newSqlProvider())639 {640 returnBaseHelper.ConvertToList(BaseHelper.ExecuteDataReader(provider, sql, param));641 }642 }643 644 ///645 ///根据多个属性查询实体集合646 ///该查询方式附加查询组建647 ///648 ///类型类649 ///属性键值对650 ///查询组件651 ///652 publicIListGetList(IDictionarydic, ConditionComponent component)whereT :class653 {654 IDataParameter[] param=null;655 stringsql=Factory.CreateQueryByPropertySql(typeof(T),dic,outparam,component);656 using(IDbProvider provider=newSqlProvider())657 {658 returnBaseHelper.ConvertToList(BaseHelper.ExecuteDataReader(provider, sql, param));659 }660 }661 662 ///663 ///释放对象内存664 ///665 publicvoidDispose()666 {667 GC.SuppressFinalize(this);668 }669 }670 }671