今天需要使用“数据同步程序”将外网数据库的FundYield 数据重新同步到内网，上次成功的一次将50W数据查询了出来，但这次不行了。记得上次外网服务器剩余内存较多，SQLSERVER只占用了150M，这次占了500多M，程序无论如何也不能一次查询出50W数据来，老是查询超时，但这个数据着急要，只有想办法了。
系统使用每个表的最后修改日期（ZHXGRQ）字段作为更新的标记，检查了下数据，发现有51W多条数据都是 1999-1-1 ，除非程序将这51W条数据全部一次查询出来，否则只有另外想办法。看了下表结构，还有一个ID字段(bigint类型)，虽然不是主键，但不重复，这样我们可以使用这个字段作为“
分页”的依据了，每次查询个10-20W数据是没有问题，于是将原来的实体类修改为下面的样子：
namespace
WFT_DataSyncModel
{
[Serializable()]
public partial class FundYield : EntityBase, WcfMail.Interface.IDataSyncEntity
{
public FundYield()
{
TableName = " FundYield " ;
EntityMap = EntityMapType.SqlMap;
// IdentityName = "标识字段名";
// PrimaryKeys.Add("主键字段名");
PrimaryKeys.Add( " jjdm " );
PrimaryKeys.Add( " FSRQ " );
PropertyNames = new string [] { " ID " , " jjdm " , " jjmc " , " jjjc " , " dwjz " , " ljjz " , " FSRQ " , " QuarterYield " , " DayYield " , " WeekYield " , " WeekYieldPM " , " Month1Yield " , " Month1YieldPM " , " Month3Yield " , " Month3YieldPM " , " Month6Yield " , " YearYield " , " YearYieldPM " , " Year1Yield " , " Year1YieldPM " , " Year2Yield " , " Year3Yield " , " totalyield " , " bzc3 " , " bzc6 " , " bzc12 " , " bzc24 " , " BuyState " , " addtime " , " ZHXGRQ " , " DayYieldPM " , " Month6YieldPM " , " Year2YieldPM " , " Year3YieldPM " , " totalyieldPM " , " DayYieldCount " , " WeekYieldCount " , " Month1YieldCount " , " Month3YieldCount " , " Month6YieldCount " , " YearYieldCount " , " Year1YieldCount " , " Year2YieldCount " , " Year3YieldCount " , " totalYieldCount " };
PropertyValues = new object [PropertyNames.Length];
}
// ...实体属性在此省略
｝
{
[Serializable()]
public partial class FundYield : EntityBase, WcfMail.Interface.IDataSyncEntity
{
public FundYield()
{
TableName = " FundYield " ;
EntityMap = EntityMapType.SqlMap;
// IdentityName = "标识字段名";
// PrimaryKeys.Add("主键字段名");
PrimaryKeys.Add( " jjdm " );
PrimaryKeys.Add( " FSRQ " );
PropertyNames = new string [] { " ID " , " jjdm " , " jjmc " , " jjjc " , " dwjz " , " ljjz " , " FSRQ " , " QuarterYield " , " DayYield " , " WeekYield " , " WeekYieldPM " , " Month1Yield " , " Month1YieldPM " , " Month3Yield " , " Month3YieldPM " , " Month6Yield " , " YearYield " , " YearYieldPM " , " Year1Yield " , " Year1YieldPM " , " Year2Yield " , " Year3Yield " , " totalyield " , " bzc3 " , " bzc6 " , " bzc12 " , " bzc24 " , " BuyState " , " addtime " , " ZHXGRQ " , " DayYieldPM " , " Month6YieldPM " , " Year2YieldPM " , " Year3YieldPM " , " totalyieldPM " , " DayYieldCount " , " WeekYieldCount " , " Month1YieldCount " , " Month3YieldCount " , " Month6YieldCount " , " YearYieldCount " , " Year1YieldCount " , " Year2YieldCount " , " Year3YieldCount " , " totalYieldCount " };
PropertyValues = new object [PropertyNames.Length];
}
// ...实体属性在此省略
｝
在实体类 FundYield 中，有一个实体映射类型属性：
EntityMap=
EntityMapType.SqlMap;//映射为自定义SQL查询
默认情况下，应该是
EntityMap=EntityMapType.Table;//映射为表
数据更新实体类必须继承一个数据更新接口：
WcfMail.Interface.IDataSyncEntity
好了，实体类的修改仅此以处，实体类映射指定为SqlMap类型，必须建立一个SqlMap配置文件，文件名固定是 “EntitySqlMap.config” ，下面是文件内容：
代码
<?
xml version="1.0" encoding="utf-8"
?>
<!-- SQL-MAP 实体类自定义查询配置文件
SQL 语句不能使用 Select * from table 格式，必须指定跟实体类一致的字段定义，否则可能发生难以预测的错误。
要生成实体类,请使用PDF.NET 实体类工具。
有关PDF.NET,请了解 http://www.pwmis.com/sqlmap
power by dth,2010.12.8
-->
< configuration >
< Namespace name ="WFT_DataSyncModel" >
< Map name ="FundYield" >
< Sql >
<![CDATA[
SELECT
ID , jjdm , jjmc , jjjc , dwjz , ljjz , FSRQ , QuarterYield , DayYield , WeekYield , WeekYieldPM , Month1Yield , Month1YieldPM , Month3Yield , Month3YieldPM , Month6Yield , YearYield , YearYieldPM , Year1Yield , Year1YieldPM , Year2Yield , Year3Yield , totalyield , bzc3 , bzc6 , bzc12 , bzc24 , BuyState , addtime , ZHXGRQ , DayYieldPM , Month6YieldPM , Year2YieldPM , Year3YieldPM , totalyieldPM , DayYieldCount , WeekYieldCount , Month1YieldCount , Month3YieldCount , Month6YieldCount , YearYieldCount , Year1YieldCount , Year2YieldCount , Year3YieldCount , totalYieldCount
FROM FundYield where id < 400000
]]> </ Sql >
</ Map >
</ Namespace >
</ configuration >
<!-- SQL-MAP 实体类自定义查询配置文件
SQL 语句不能使用 Select * from table 格式，必须指定跟实体类一致的字段定义，否则可能发生难以预测的错误。
要生成实体类,请使用PDF.NET 实体类工具。
有关PDF.NET,请了解 http://www.pwmis.com/sqlmap
power by dth,2010.12.8
-->
< configuration >
< Namespace name ="WFT_DataSyncModel" >
< Map name ="FundYield" >
< Sql >
<![CDATA[
SELECT
ID , jjdm , jjmc , jjjc , dwjz , ljjz , FSRQ , QuarterYield , DayYield , WeekYield , WeekYieldPM , Month1Yield , Month1YieldPM , Month3Yield , Month3YieldPM , Month6Yield , YearYield , YearYieldPM , Year1Yield , Year1YieldPM , Year2Yield , Year3Yield , totalyield , bzc3 , bzc6 , bzc12 , bzc24 , BuyState , addtime , ZHXGRQ , DayYieldPM , Month6YieldPM , Year2YieldPM , Year3YieldPM , totalyieldPM , DayYieldCount , WeekYieldCount , Month1YieldCount , Month3YieldCount , Month6YieldCount , YearYieldCount , Year1YieldCount , Year2YieldCount , Year3YieldCount , totalYieldCount
FROM FundYield where id < 400000
]]> </ Sql >
</ Map >
</ Namespace >
</ configuration >
注意一下名称空间和映射名称必须和类的定义一致。
OK，所需的工作完成，我们只改了一下实体类的映射类型和编写了一个实体类查询文件，编译项目，重新发布，开始执行，剩下的只是每次修改一下配置文件的查询条件了，比如我现在正在使用的条件：
where ID>=600000 and ID<800000
最后的工作就是等待它执行完成，这个任务就OK了。
==================
总结：
使用面向对象的方法（OO）也可以很方便的处理“
纯数据问题”，
数据只是对象的一部分，我们将数据放到对象中去处理，使得我们对新问题的处理变得很容易，这就是OO的美妙之处！
本文转自深蓝医生博客园博客，原文链接：http://www.cnblogs.com/bluedoctor/archive/2010/12/08/1900598.html，如需转载请自行联系原作者
