关于Silverlight中DataGrid的数据加载
这个例子采用ADO.NET Entity Data Model 作为数据访问层,并使用Domain Service(WCF RIA) 实现客户端和服务端之间的通信。
实验环境如下:
在练习的过程中,我产生了以下疑问:
- 使用DataGrid控件,并与读取全部记录的数据源做绑定,实验步骤如下:
- 仅使用DataGrid控件,并与读取全部记录的数据源做绑定,编译程序
-
打开AnjLab Sql Profiler,新建一个Trace,并配置相关监控参数,如下图所示
配置General项
配置Events项,仅勾选SQLBatchCompleted、SQLBatchStarting、SQLStmtCompleted、SQLStmtStarting、SQLTransaction这几个Events就够了。
填写DatabaseID,可以打开数据库Sql Server Management Studio,找到master数据库中的表[master].[sys].[databases],在其中找到所用到的数据库的database_id。
- 点击Run按钮,然后执行Silverlight程序,观察AnjLab Sql Profiler窗口中的事件记录,如下所示:
2 [Extent1].[RequireID] AS [RequireID],
3 [Extent1].[RequireName] AS [RequireName],
4 [Extent1].[RequireType] AS [RequireType],
5 [Extent1].[RequireVersion] AS [RequireVersion],
6 [Extent1].[FunctionModule] AS [FunctionModule],
7 [Extent1].[RequireContent] AS [RequireContent],
8 [Extent1].[Solution] AS [Solution],
9 [Extent1].[Proposer] AS [Proposer],
10 [Extent1].[ProposedTime] AS [ProposedTime],
11 [Extent1].[RequireOwner] AS [RequireOwner],
12 [Extent1].[Attachment] AS [Attachment],
13 [Extent1].[RequireStatus] AS [RequireStatus],
14 [Extent1].[RequirePriority] AS [RequirePriority],
15 [Extent1].[RequireResult] AS [RequireResult],
16 [Extent1].[ResultStatus] AS [ResultStatus],
17 [Extent1].[PassTime] AS [PassTime],
18 [Extent1].[StandardID] AS [StandardID]
19 FROM ( SELECT [Extent1].[RequireID] AS [RequireID], [Extent1].[RequireName] AS [RequireName], [Extent1].[RequireType] AS [RequireType], [Extent1].[RequireVersion] AS [RequireVersion], [Extent1].[FunctionModule] AS [FunctionModule], [Extent1].[RequireContent] AS [RequireContent], [Extent1].[Solution] AS [Solution], [Extent1].[Proposer] AS [Proposer], [Extent1].[ProposedTime] AS [ProposedTime], [Extent1].[RequireOwner] AS [RequireOwner], [Extent1].[Attachment] AS [Attachment], [Extent1].[RequireStatus] AS [RequireStatus], [Extent1].[RequirePriority] AS [RequirePriority], [Extent1].[RequireResult] AS [RequireResult], [Extent1].[ResultStatus] AS [ResultStatus], [Extent1].[PassTime] AS [PassTime], [Extent1].[StandardID] AS [StandardID], row_number() OVER (ORDER BY [Extent1].[RequireID] ASC) AS [row_number]
20 FROM (SELECT
21 [Requirement].[RequireID] AS [RequireID],
22 [Requirement].[RequireName] AS [RequireName],
23 [Requirement].[RequireType] AS [RequireType],
24 [Requirement].[RequireVersion] AS [RequireVersion],
25 [Requirement].[FunctionModule] AS [FunctionModule],
26 [Requirement].[RequireContent] AS [RequireContent],
27 [Requirement].[Solution] AS [Solution],
28 [Requirement].[Proposer] AS [Proposer],
29 [Requirement].[ProposedTime] AS [ProposedTime],
30 [Requirement].[RequireOwner] AS [RequireOwner],
31 [Requirement].[Attachment] AS [Attachment],
32 [Requirement].[RequireStatus] AS [RequireStatus],
33 [Requirement].[RequirePriority] AS [RequirePriority],
34 [Requirement].[RequireResult] AS [RequireResult],
35 [Requirement].[ResultStatus] AS [ResultStatus],
36 [Requirement].[PassTime] AS [PassTime],
37 [Requirement].[StandardID] AS [StandardID]
38 FROM [dbo].[Requirement] AS [Requirement]) AS [Extent1]
39 ) AS [Extent1]
40 ORDER BY [Extent1].[RequireID] ASC
可以看到,SQL语句是执行了查询所有记录的命令,可以推断所有记录数据都是一次性传递到客户端并加载到DataGrid中的。另外也可以看到如下SQL语句:
2 [Extent1].[RequireID] AS [RequireID],
3 [Extent1].[RequireName] AS [RequireName],
4 [Extent1].[RequireType] AS [RequireType],
5 [Extent1].[RequireVersion] AS [RequireVersion],
6 [Extent1].[FunctionModule] AS [FunctionModule],
7 [Extent1].[RequireContent] AS [RequireContent],
8 [Extent1].[Solution] AS [Solution],
9 [Extent1].[Proposer] AS [Proposer],
10 [Extent1].[ProposedTime] AS [ProposedTime],
11 [Extent1].[RequireOwner] AS [RequireOwner],
12 [Extent1].[Attachment] AS [Attachment],
13 [Extent1].[RequireStatus] AS [RequireStatus],
14 [Extent1].[RequirePriority] AS [RequirePriority],
15 [Extent1].[RequireResult] AS [RequireResult],
16 [Extent1].[ResultStatus] AS [ResultStatus],
17 [Extent1].[PassTime] AS [PassTime],
18 [Extent1].[StandardID] AS [StandardID]
19 FROM (SELECT
20 [Requirement].[RequireID] AS [RequireID],
21 [Requirement].[RequireName] AS [RequireName],
22 [Requirement].[RequireType] AS [RequireType],
23 [Requirement].[RequireVersion] AS [RequireVersion],
24 [Requirement].[FunctionModule] AS [FunctionModule],
25 [Requirement].[RequireContent] AS [RequireContent],
26 [Requirement].[Solution] AS [Solution],
27 [Requirement].[Proposer] AS [Proposer],
28 [Requirement].[ProposedTime] AS [ProposedTime],
29 [Requirement].[RequireOwner] AS [RequireOwner],
30 [Requirement].[Attachment] AS [Attachment],
31 [Requirement].[RequireStatus] AS [RequireStatus],
32 [Requirement].[RequirePriority] AS [RequirePriority],
33 [Requirement].[RequireResult] AS [RequireResult],
34 [Requirement].[ResultStatus] AS [ResultStatus],
35 [Requirement].[PassTime] AS [PassTime],
36 [Requirement].[StandardID] AS [StandardID]
37 FROM [dbo].[Requirement] AS [Requirement]) AS [Extent1]
38 WHERE [Extent1].[RequireID] > 200
这是xaml文件中没有与DataGrid绑定的另外一个数据源riaControls:DomainDataSource的SQL语句,这说明Silverlight也将xaml中所有数据源riaControls:DomainDataSource的数据也一同加载进来(即使不将此数据源绑定到DataGrid等控件上)
- 使用DataGrid控件和DataPager控件,设定DataPager控件的PageSize为20,并与读取全部记录的数据源做绑定,实验步骤和上面一致,检测到的SQL语句如下:
2 [GroupBy1].[A1] AS [C1]
3 FROM ( SELECT
4 COUNT(1) AS [A1]
5 FROM (SELECT
6 [Requirement].[RequireID] AS [RequireID],
7 [Requirement].[RequireName] AS [RequireName],
8 [Requirement].[RequireType] AS [RequireType],
9 [Requirement].[RequireVersion] AS [RequireVersion],
10 [Requirement].[FunctionModule] AS [FunctionModule],
11 [Requirement].[RequireContent] AS [RequireContent],
12 [Requirement].[Solution] AS [Solution],
13 [Requirement].[Proposer] AS [Proposer],
14 [Requirement].[ProposedTime] AS [ProposedTime],
15 [Requirement].[RequireOwner] AS [RequireOwner],
16 [Requirement].[Attachment] AS [Attachment],
17 [Requirement].[RequireStatus] AS [RequireStatus],
18 [Requirement].[RequirePriority] AS [RequirePriority],
19 [Requirement].[RequireResult] AS [RequireResult],
20 [Requirement].[ResultStatus] AS [ResultStatus],
21 [Requirement].[PassTime] AS [PassTime],
22 [Requirement].[StandardID] AS [StandardID]
23 FROM [dbo].[Requirement] AS [Requirement]) AS [Extent1]
24 ) AS [GroupBy1]
以上可见是在计算此次SQL语句执行结果的记录总行数。未在Silverlight翻页之前的SQL语句如下:
2 [Extent1].[RequireID] AS [RequireID],
3 [Extent1].[RequireName] AS [RequireName],
4 [Extent1].[RequireType] AS [RequireType],
5 [Extent1].[RequireVersion] AS [RequireVersion],
6 [Extent1].[FunctionModule] AS [FunctionModule],
7 [Extent1].[RequireContent] AS [RequireContent],
8 [Extent1].[Solution] AS [Solution],
9 [Extent1].[Proposer] AS [Proposer],
10 [Extent1].[ProposedTime] AS [ProposedTime],
11 [Extent1].[RequireOwner] AS [RequireOwner],
12 [Extent1].[Attachment] AS [Attachment],
13 [Extent1].[RequireStatus] AS [RequireStatus],
14 [Extent1].[RequirePriority] AS [RequirePriority],
15 [Extent1].[RequireResult] AS [RequireResult],
16 [Extent1].[ResultStatus] AS [ResultStatus],
17 [Extent1].[PassTime] AS [PassTime],
18 [Extent1].[StandardID] AS [StandardID]
19 FROM (SELECT
20 [Requirement].[RequireID] AS [RequireID],
21 [Requirement].[RequireName] AS [RequireName],
22 [Requirement].[RequireType] AS [RequireType],
23 [Requirement].[RequireVersion] AS [RequireVersion],
24 [Requirement].[FunctionModule] AS [FunctionModule],
25 [Requirement].[RequireContent] AS [RequireContent],
26 [Requirement].[Solution] AS [Solution],
27 [Requirement].[Proposer] AS [Proposer],
28 [Requirement].[ProposedTime] AS [ProposedTime],
29 [Requirement].[RequireOwner] AS [RequireOwner],
30 [Requirement].[Attachment] AS [Attachment],
31 [Requirement].[RequireStatus] AS [RequireStatus],
32 [Requirement].[RequirePriority] AS [RequirePriority],
33 [Requirement].[RequireResult] AS [RequireResult],
34 [Requirement].[ResultStatus] AS [ResultStatus],
35 [Requirement].[PassTime] AS [PassTime],
36 [Requirement].[StandardID] AS [StandardID]
37 FROM [dbo].[Requirement] AS [Requirement]) AS [Extent1]
在silverlight中翻页后的SQL语句如下:
2 [Extent1].[RequireID] AS [RequireID],
3 [Extent1].[RequireName] AS [RequireName],
4 [Extent1].[RequireType] AS [RequireType],
5 [Extent1].[RequireVersion] AS [RequireVersion],
6 [Extent1].[FunctionModule] AS [FunctionModule],
7 [Extent1].[RequireContent] AS [RequireContent],
8 [Extent1].[Solution] AS [Solution],
9 [Extent1].[Proposer] AS [Proposer],
10 [Extent1].[ProposedTime] AS [ProposedTime],
11 [Extent1].[RequireOwner] AS [RequireOwner],
12 [Extent1].[Attachment] AS [Attachment],
13 [Extent1].[RequireStatus] AS [RequireStatus],
14 [Extent1].[RequirePriority] AS [RequirePriority],
15 [Extent1].[RequireResult] AS [RequireResult],
16 [Extent1].[ResultStatus] AS [ResultStatus],
17 [Extent1].[PassTime] AS [PassTime],
18 [Extent1].[StandardID] AS [StandardID]
19 FROM ( SELECT [Extent1].[RequireID] AS [RequireID], [Extent1].[RequireName] AS [RequireName], [Extent1].[RequireType] AS [RequireType], [Extent1].[RequireVersion] AS [RequireVersion], [Extent1].[FunctionModule] AS [FunctionModule], [Extent1].[RequireContent] AS [RequireContent], [Extent1].[Solution] AS [Solution], [Extent1].[Proposer] AS [Proposer], [Extent1].[ProposedTime] AS [ProposedTime], [Extent1].[RequireOwner] AS [RequireOwner], [Extent1].[Attachment] AS [Attachment], [Extent1].[RequireStatus] AS [RequireStatus], [Extent1].[RequirePriority] AS [RequirePriority], [Extent1].[RequireResult] AS [RequireResult], [Extent1].[ResultStatus] AS [ResultStatus], [Extent1].[PassTime] AS [PassTime], [Extent1].[StandardID] AS [StandardID], row_number() OVER (ORDER BY [Extent1].[RequireID] ASC) AS [row_number]
20 FROM (SELECT
21 [Requirement].[RequireID] AS [RequireID],
22 [Requirement].[RequireName] AS [RequireName],
23 [Requirement].[RequireType] AS [RequireType],
24 [Requirement].[RequireVersion] AS [RequireVersion],
25 [Requirement].[FunctionModule] AS [FunctionModule],
26 [Requirement].[RequireContent] AS [RequireContent],
27 [Requirement].[Solution] AS [Solution],
28 [Requirement].[Proposer] AS [Proposer],
29 [Requirement].[ProposedTime] AS [ProposedTime],
30 [Requirement].[RequireOwner] AS [RequireOwner],
31 [Requirement].[Attachment] AS [Attachment],
32 [Requirement].[RequireStatus] AS [RequireStatus],
33 [Requirement].[RequirePriority] AS [RequirePriority],
34 [Requirement].[RequireResult] AS [RequireResult],
35 [Requirement].[ResultStatus] AS [ResultStatus],
36 [Requirement].[PassTime] AS [PassTime],
37 [Requirement].[StandardID] AS [StandardID]
38 FROM [dbo].[Requirement] AS [Requirement]) AS [Extent1]
39 ) AS [Extent1]
40 WHERE [Extent1].[row_number] > 20
可以发现里面有如下代码:
1 WHERE [Extent1].[row_number] > 20
另外也可以看到如下SQL语句:
2 [Extent1].[RequireID] AS [RequireID],
3 [Extent1].[RequireName] AS [RequireName],
4 [Extent1].[RequireType] AS [RequireType],
5 [Extent1].[RequireVersion] AS [RequireVersion],
6 [Extent1].[FunctionModule] AS [FunctionModule],
7 [Extent1].[RequireContent] AS [RequireContent],
8 [Extent1].[Solution] AS [Solution],
9 [Extent1].[Proposer] AS [Proposer],
10 [Extent1].[ProposedTime] AS [ProposedTime],
11 [Extent1].[RequireOwner] AS [RequireOwner],
12 [Extent1].[Attachment] AS [Attachment],
13 [Extent1].[RequireStatus] AS [RequireStatus],
14 [Extent1].[RequirePriority] AS [RequirePriority],
15 [Extent1].[RequireResult] AS [RequireResult],
16 [Extent1].[ResultStatus] AS [ResultStatus],
17 [Extent1].[PassTime] AS [PassTime],
18 [Extent1].[StandardID] AS [StandardID]
19 FROM (SELECT
20 [Requirement].[RequireID] AS [RequireID],
21 [Requirement].[RequireName] AS [RequireName],
22 [Requirement].[RequireType] AS [RequireType],
23 [Requirement].[RequireVersion] AS [RequireVersion],
24 [Requirement].[FunctionModule] AS [FunctionModule],
25 [Requirement].[RequireContent] AS [RequireContent],
26 [Requirement].[Solution] AS [Solution],
27 [Requirement].[Proposer] AS [Proposer],
28 [Requirement].[ProposedTime] AS [ProposedTime],
29 [Requirement].[RequireOwner] AS [RequireOwner],
30 [Requirement].[Attachment] AS [Attachment],
31 [Requirement].[RequireStatus] AS [RequireStatus],
32 [Requirement].[RequirePriority] AS [RequirePriority],
33 [Requirement].[RequireResult] AS [RequireResult],
34 [Requirement].[ResultStatus] AS [ResultStatus],
35 [Requirement].[PassTime] AS [PassTime],
36 [Requirement].[StandardID] AS [StandardID]
37 FROM [dbo].[Requirement] AS [Requirement]) AS [Extent1]
38 WHERE [Extent1].[RequireID] > 200
这是xaml文件中没有与DataGrid绑定的另外一个数据源riaControls:DomainDataSource的SQL语句,这说明Silverlight也将xaml中所有数据源riaControls:DomainDataSource的数据也一同加载进来(即使不将此数据源绑定到DataGrid等控件上)
此次试验结论如下: