今天需要使用“數據同步程序”將外網數據庫的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];
}
//...實體屬性在此省略
}
在實體類 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>
注意一下名稱空間和映射名稱必須和類的定義一致。
OK,所需的工作完成,我們只改了一下實體類的映射類型和編寫了一個實體類查詢文件,編譯項目,重新發布,開始執行,剩下的只是每次修改一下配置文件的查詢條件了,比如我現在正在使用的條件:
where ID>=600000 and ID<800000
最後的工作就是等待它執行完成,這個任務就OK了。
==================
總結:
使用面向對象的方法(OO)也可以很方便的處理“
純數據問題”,
數據只是對象的一部分,我們將數據放到對象中去處理,使得我們對新問題的處理變得很容易,這就是OO的美妙之處!