原文地址:http://www.cnblogs.com/luminji/archive/2011/06/10/2077696.html
本文目的是通过Silverlight ria service完成一次数据的读取过程,并且在此基础上建立测试项目。
Ria service借助于WCF和ADO.NET Entity Framework构建分布式开发框架。使用它可以快速构建自己的开发模式。
1:基础结构
首先,创建SL APP,如下:
然后,选择创建web:
可以,也可以不勾选enable wcf ria service,如果勾选了,在SL APP中会多几个DLL的引用,其它没有任何差别。如图:
2:创建一个示例数据库
这是一个小而轻型的数据库,在MDSN的课程中有使用到它。如下:
003 | SET QUOTED_IDENTIFIER ON |
009 | IF EXISTS ( SELECT * FROM sys.databases WHERE name = 'School' ) |
010 | DROP DATABASE School; |
014 | CREATE DATABASE School; |
027 | IF NOT EXISTS ( SELECT * FROM sys.objects |
028 | WHERE object_id = OBJECT_ID(N '[dbo].[Department]' ) |
031 | CREATE TABLE [dbo].[Department]( |
032 | [DepartmentID] [ int ] NOT NULL , |
033 | [ Name ] [nvarchar](50) NOT NULL , |
034 | [Budget] [money] NOT NULL , |
035 | [StartDate] [datetime] NOT NULL , |
036 | [Administrator] [ int ] NULL , |
037 | CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED |
040 | ) WITH (IGNORE_DUP_KEY = OFF ) ON [ PRIMARY ] |
046 | IF NOT EXISTS ( SELECT * FROM sys.objects |
047 | WHERE object_id = OBJECT_ID(N '[dbo].[Person]' ) |
050 | CREATE TABLE [dbo].[Person]( |
051 | [PersonID] [ int ] IDENTITY(1,1) NOT NULL , |
052 | [LastName] [nvarchar](50) NOT NULL , |
053 | [FirstName] [nvarchar](50) NOT NULL , |
054 | [HireDate] [datetime] NULL , |
055 | [EnrollmentDate] [datetime] NULL , |
056 | CONSTRAINT [PK_School.Student] PRIMARY KEY CLUSTERED |
059 | ) WITH (IGNORE_DUP_KEY = OFF ) ON [ PRIMARY ] |
065 | IF NOT EXISTS ( SELECT * FROM sys.objects |
066 | WHERE object_id = OBJECT_ID(N '[dbo].[OnsiteCourse]' ) |
069 | CREATE TABLE [dbo].[OnsiteCourse]( |
070 | [CourseID] [ int ] NOT NULL , |
071 | [Location] [nvarchar](50) NOT NULL , |
072 | [Days] [nvarchar](50) NOT NULL , |
073 | [ Time ] [smalldatetime] NOT NULL , |
074 | CONSTRAINT [PK_OnsiteCourse] PRIMARY KEY CLUSTERED |
077 | ) WITH (IGNORE_DUP_KEY = OFF ) ON [ PRIMARY ] |
083 | IF NOT EXISTS ( SELECT * FROM sys.objects |
084 | WHERE object_id = OBJECT_ID(N '[dbo].[OnlineCourse]' ) |
087 | CREATE TABLE [dbo].[OnlineCourse]( |
088 | [CourseID] [ int ] NOT NULL , |
089 | [URL] [nvarchar](100) NOT NULL , |
090 | CONSTRAINT [PK_OnlineCourse] PRIMARY KEY CLUSTERED |
093 | ) WITH (IGNORE_DUP_KEY = OFF ) ON [ PRIMARY ] |
099 | IF NOT EXISTS ( SELECT * FROM sys.objects |
100 | WHERE object_id = OBJECT_ID(N '[dbo].[StudentGrade]' ) |
103 | CREATE TABLE [dbo].[StudentGrade]( |
104 | [EnrollmentID] [ int ] IDENTITY(1,1) NOT NULL , |
105 | [CourseID] [ int ] NOT NULL , |
106 | [StudentID] [ int ] NOT NULL , |
107 | [Grade] [ decimal ](3, 2) NULL , |
108 | CONSTRAINT [PK_StudentGrade] PRIMARY KEY CLUSTERED |
111 | ) WITH (IGNORE_DUP_KEY = OFF ) ON [ PRIMARY ] |
117 | IF NOT EXISTS ( SELECT * FROM sys.objects |
118 | WHERE object_id = OBJECT_ID(N '[dbo].[CourseInstructor]' ) |
121 | CREATE TABLE [dbo].[CourseInstructor]( |
122 | [CourseID] [ int ] NOT NULL , |
123 | [PersonID] [ int ] NOT NULL , |
124 | CONSTRAINT [PK_CourseInstructor] PRIMARY KEY CLUSTERED |
128 | ) WITH (IGNORE_DUP_KEY = OFF ) ON [ PRIMARY ] |
134 | IF NOT EXISTS ( SELECT * FROM sys.objects |
135 | WHERE object_id = OBJECT_ID(N '[dbo].[Course]' ) |
138 | CREATE TABLE [dbo].[Course]( |
139 | [CourseID] [ int ] NOT NULL , |
140 | [Title] [nvarchar](100) NOT NULL , |
141 | [Credits] [ int ] NOT NULL , |
142 | [DepartmentID] [ int ] NOT NULL , |
143 | CONSTRAINT [PK_School.Course] PRIMARY KEY CLUSTERED |
146 | ) WITH (IGNORE_DUP_KEY = OFF ) ON [ PRIMARY ] |
152 | IF NOT EXISTS ( SELECT * FROM sys.objects |
153 | WHERE object_id = OBJECT_ID(N '[dbo].[OfficeAssignment]' ) |
156 | CREATE TABLE [dbo].[OfficeAssignment]( |
157 | [InstructorID] [ int ] NOT NULL , |
158 | [Location] [nvarchar](50) NOT NULL , |
159 | [ Timestamp ] [ timestamp ] NOT NULL , |
160 | CONSTRAINT [PK_OfficeAssignment] PRIMARY KEY CLUSTERED |
163 | ) WITH (IGNORE_DUP_KEY = OFF ) ON [ PRIMARY ] |
169 | IF NOT EXISTS ( SELECT * FROM sys.foreign_keys |
170 | WHERE object_id = OBJECT_ID(N '[dbo].[FK_OnsiteCourse_Course]' ) |
171 | AND parent_object_id = OBJECT_ID(N '[dbo].[OnsiteCourse]' )) |
172 | ALTER TABLE [dbo].[OnsiteCourse] WITH CHECK ADD |
173 | CONSTRAINT [FK_OnsiteCourse_Course] FOREIGN KEY ([CourseID]) |
174 | REFERENCES [dbo].[Course] ([CourseID]) |
176 | ALTER TABLE [dbo].[OnsiteCourse] CHECK |
177 | CONSTRAINT [FK_OnsiteCourse_Course] |
181 | IF NOT EXISTS ( SELECT * FROM sys.foreign_keys |
182 | WHERE object_id = OBJECT_ID(N '[dbo].[FK_OnlineCourse_Course]' ) |
183 | AND parent_object_id = OBJECT_ID(N '[dbo].[OnlineCourse]' )) |
184 | ALTER TABLE [dbo].[OnlineCourse] WITH CHECK ADD |
185 | CONSTRAINT [FK_OnlineCourse_Course] FOREIGN KEY ([CourseID]) |
186 | REFERENCES [dbo].[Course] ([CourseID]) |
188 | ALTER TABLE [dbo].[OnlineCourse] CHECK |
189 | CONSTRAINT [FK_OnlineCourse_Course] |
193 | IF NOT EXISTS ( SELECT * FROM sys.foreign_keys |
194 | WHERE object_id = OBJECT_ID(N '[dbo].[FK_StudentGrade_Course]' ) |
195 | AND parent_object_id = OBJECT_ID(N '[dbo].[StudentGrade]' )) |
196 | ALTER TABLE [dbo].[StudentGrade] WITH CHECK ADD |
197 | CONSTRAINT [FK_StudentGrade_Course] FOREIGN KEY ([CourseID]) |
198 | REFERENCES [dbo].[Course] ([CourseID]) |
200 | ALTER TABLE [dbo].[StudentGrade] CHECK |
201 | CONSTRAINT [FK_StudentGrade_Course] |
205 | IF NOT EXISTS ( SELECT * FROM sys.foreign_keys |
206 | WHERE object_id = OBJECT_ID(N '[dbo].[FK_StudentGrade_Student]' ) |
207 | AND parent_object_id = OBJECT_ID(N '[dbo].[StudentGrade]' )) |
208 | ALTER TABLE [dbo].[StudentGrade] WITH CHECK ADD |
209 | CONSTRAINT [FK_StudentGrade_Student] FOREIGN KEY ([StudentID]) |
210 | REFERENCES [dbo].[Person] ([PersonID]) |
212 | ALTER TABLE [dbo].[StudentGrade] CHECK |
213 | CONSTRAINT [FK_StudentGrade_Student] |
217 | IF NOT EXISTS ( SELECT * FROM sys.foreign_keys |
218 | WHERE object_id = OBJECT_ID(N '[dbo].[FK_CourseInstructor_Course]' ) |
219 | AND parent_object_id = OBJECT_ID(N '[dbo].[CourseInstructor]' )) |
220 | ALTER TABLE [dbo].[CourseInstructor] WITH CHECK ADD |
221 | CONSTRAINT [FK_CourseInstructor_Course] FOREIGN KEY ([CourseID]) |
222 | REFERENCES [dbo].[Course] ([CourseID]) |
224 | ALTER TABLE [dbo].[CourseInstructor] CHECK |
225 | CONSTRAINT [FK_CourseInstructor_Course] |
229 | IF NOT EXISTS ( SELECT * FROM sys.foreign_keys |
230 | WHERE object_id = OBJECT_ID(N '[dbo].[FK_CourseInstructor_Person]' ) |
231 | AND parent_object_id = OBJECT_ID(N '[dbo].[CourseInstructor]' )) |
232 | ALTER TABLE [dbo].[CourseInstructor] WITH CHECK ADD |
233 | CONSTRAINT [FK_CourseInstructor_Person] FOREIGN KEY ([PersonID]) |
234 | REFERENCES [dbo].[Person] ([PersonID]) |
236 | ALTER TABLE [dbo].[CourseInstructor] CHECK |
237 | CONSTRAINT [FK_CourseInstructor_Person] |
241 | IF NOT EXISTS ( SELECT * FROM sys.foreign_keys |
242 | WHERE object_id = OBJECT_ID(N '[dbo].[FK_Course_Department]' ) |
243 | AND parent_object_id = OBJECT_ID(N '[dbo].[Course]' )) |
244 | ALTER TABLE [dbo].[Course] WITH CHECK ADD |
245 | CONSTRAINT [FK_Course_Department] FOREIGN KEY ([DepartmentID]) |
246 | REFERENCES [dbo].[Department] ([DepartmentID]) |
248 | ALTER TABLE [dbo].[Course] CHECK CONSTRAINT [FK_Course_Department] |
252 | IF NOT EXISTS ( SELECT * FROM sys.foreign_keys |
253 | WHERE object_id = OBJECT_ID(N '[dbo].[FK_OfficeAssignment_Person]' ) |
254 | AND parent_object_id = OBJECT_ID(N '[dbo].[OfficeAssignment]' )) |
255 | ALTER TABLE [dbo].[OfficeAssignment] WITH CHECK ADD |
256 | CONSTRAINT [FK_OfficeAssignment_Person] FOREIGN KEY ([InstructorID]) |
257 | REFERENCES [dbo].[Person] ([PersonID]) |
259 | ALTER TABLE [dbo].[OfficeAssignment] CHECK |
260 | CONSTRAINT [FK_OfficeAssignment_Person] |
264 | IF NOT EXISTS ( SELECT * FROM sys.objects |
265 | WHERE object_id = OBJECT_ID(N '[dbo].[InsertOfficeAssignment]' ) |
266 | AND type in (N 'P' , N 'PC' )) |
268 | EXEC dbo.sp_executesql @statement = N ' |
269 | CREATE PROCEDURE [dbo].[InsertOfficeAssignment] |
271 | @Location nvarchar(50) |
273 | INSERT INTO dbo.OfficeAssignment (InstructorID, Location) |
274 | VALUES (@InstructorID, @Location); |
277 | SELECT [Timestamp] FROM OfficeAssignment |
278 | WHERE InstructorID=@InstructorID; |
285 | IF NOT EXISTS ( SELECT * FROM sys.objects |
286 | WHERE object_id = OBJECT_ID(N '[dbo].[UpdateOfficeAssignment]' ) |
287 | AND type in (N 'P' , N 'PC' )) |
289 | EXEC dbo.sp_executesql @statement = N ' |
290 | CREATE PROCEDURE [dbo].[UpdateOfficeAssignment] |
292 | @Location nvarchar(50), |
293 | @OrigTimestamp timestamp |
295 | UPDATE OfficeAssignment SET Location=@Location |
296 | WHERE InstructorID=@InstructorID AND [Timestamp]=@OrigTimestamp; |
299 | SELECT [Timestamp] FROM OfficeAssignment |
300 | WHERE InstructorID=@InstructorID; |
307 | IF NOT EXISTS ( SELECT * FROM sys.objects |
308 | WHERE object_id = OBJECT_ID(N '[dbo].[DeleteOfficeAssignment]' ) |
309 | AND type in (N 'P' , N 'PC' )) |
311 | EXEC dbo.sp_executesql @statement = N ' |
312 | CREATE PROCEDURE [dbo].[DeleteOfficeAssignment] |
315 | DELETE FROM OfficeAssignment |
316 | WHERE InstructorID=@InstructorID; |
322 | IF NOT EXISTS ( SELECT * FROM sys.objects |
323 | WHERE object_id = OBJECT_ID(N '[dbo].[DeletePerson]' ) |
324 | AND type in (N 'P' , N 'PC' )) |
326 | EXEC dbo.sp_executesql @statement = N ' |
327 | CREATE PROCEDURE [dbo].[DeletePerson] |
330 | DELETE FROM Person WHERE PersonID = @PersonID; |
336 | IF NOT EXISTS ( SELECT * FROM sys.objects |
337 | WHERE object_id = OBJECT_ID(N '[dbo].[UpdatePerson]' ) |
338 | AND type in (N 'P' , N 'PC' )) |
340 | EXEC dbo.sp_executesql @statement = N ' |
341 | CREATE PROCEDURE [dbo].[UpdatePerson] |
343 | @LastName nvarchar(50), |
344 | @FirstName nvarchar(50), |
346 | @EnrollmentDate datetime |
348 | UPDATE Person SET LastName=@LastName, |
349 | FirstName=@FirstName, |
351 | EnrollmentDate=@EnrollmentDate |
352 | WHERE PersonID=@PersonID; |
358 | IF NOT EXISTS ( SELECT * FROM sys.objects |
359 | WHERE object_id = OBJECT_ID(N '[dbo].[InsertPerson]' ) |
360 | AND type in (N 'P' , N 'PC' )) |
362 | EXEC dbo.sp_executesql @statement = N ' |
363 | CREATE PROCEDURE [dbo].[InsertPerson] |
364 | @LastName nvarchar(50), |
365 | @FirstName nvarchar(50), |
367 | @EnrollmentDate datetime |
369 | INSERT INTO dbo.Person (LastName, |
377 | SELECT SCOPE_IDENTITY() as NewPersonID; |
383 | IF NOT EXISTS ( SELECT * FROM sys.objects |
384 | WHERE object_id = OBJECT_ID(N '[dbo].[GetStudentGrades]' ) |
385 | AND type in (N 'P' , N 'PC' )) |
387 | EXEC dbo.sp_executesql @statement = N ' |
388 | CREATE PROCEDURE [dbo].[GetStudentGrades] |
391 | SELECT EnrollmentID, Grade, CourseID, StudentID FROM dbo.StudentGrade |
392 | WHERE StudentID = @StudentID |
398 | IF NOT EXISTS ( SELECT * FROM sys.objects |
399 | WHERE object_id = OBJECT_ID(N '[dbo].[GetDepartmentName]' ) |
400 | AND type in (N 'P' , N 'PC' )) |
402 | EXEC dbo.sp_executesql @statement = N ' |
403 | CREATE PROCEDURE [dbo].[GetDepartmentName] |
405 | @Name nvarchar(50) OUTPUT |
407 | SELECT @Name = Name FROM Department |
408 | WHERE DepartmentID = @ID |
416 | SET IDENTITY_INSERT dbo.Person ON |
418 | INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) |
419 | VALUES (1, 'Abercrombie' , 'Kim' , '1995-03-11' , null ); |
420 | INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) |
421 | VALUES (2, 'Barzdukas' , 'Gytis' , null , '2005-09-01' ); |
422 | INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) |
423 | VALUES (3, 'Justice' , 'Peggy' , null , '2001-09-01' ); |
424 | INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) |
425 | VALUES (4, 'Fakhouri' , 'Fadi' , '2002-08-06' , null ); |
426 | INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) |
427 | VALUES (5, 'Harui' , 'Roger' , '1998-07-01' , null ); |
428 | INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) |
429 | VALUES (6, 'Li' , 'Yan' , null , '2002-09-01' ); |
430 | INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) |
431 | VALUES (7, 'Norman' , 'Laura' , null , '2003-09-01' ); |
432 | INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) |
433 | VALUES (8, 'Olivotto' , 'Nino' , null , '2005-09-01' ); |
434 | INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) |
435 | VALUES (9, 'Tang' , 'Wayne' , null , '2005-09-01' ); |
436 | INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) |
437 | VALUES (10, 'Alonso' , 'Meredith' , null , '2002-09-01' ); |
438 | INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) |
439 | VALUES (11, 'Lopez' , 'Sophia' , null , '2004-09-01' ); |
440 | INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) |
441 | VALUES (12, 'Browning' , 'Meredith' , null , '2000-09-01' ); |
442 | INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) |
443 | VALUES (13, 'Anand' , 'Arturo' , null , '2003-09-01' ); |
444 | INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) |
445 | VALUES (14, 'Walker' , 'Alexandra' , null , '2000-09-01' ); |
446 | INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) |
447 | VALUES (15, 'Powell' , 'Carson' , null , '2004-09-01' ); |
448 | INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) |
449 | VALUES (16, 'Jai' , 'Damien' , null , '2001-09-01' ); |
450 | INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) |
451 | VALUES (17, 'Carlson' , 'Robyn' , null , '2005-09-01' ); |
452 | INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) |
453 | VALUES (18, 'Zheng' , 'Roger' , '2004-02-12' , null ); |
454 | INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) |
455 | VALUES (19, 'Bryant' , 'Carson' , null , '2001-09-01' ); |
456 | INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) |
457 | VALUES (20, 'Suarez' , 'Robyn' , null , '2004-09-01' ); |
458 | INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) |
459 | VALUES (21, 'Holt' , 'Roger' , null , '2004-09-01' ); |
460 | INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) |
461 | VALUES (22, 'Alexander' , 'Carson' , null , '2005-09-01' ); |
462 | INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) |
463 | VALUES (23, 'Morgan' , 'Isaiah' , null , '2001-09-01' ); |
464 | INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) |
465 | VALUES (24, 'Martin' , 'Randall' , null , '2005-09-01' ); |
466 | INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) |
467 | VALUES (25, 'Kapoor' , 'Candace' , '2001-01-15' , null ); |
468 | INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) |
469 | VALUES (26, 'Rogers' , 'Cody' , null , '2002-09-01' ); |
470 | INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) |
471 | VALUES (27, 'Serrano' , 'Stacy' , '1999-06-01' , null ); |
472 | INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) |
473 | VALUES (28, 'White' , 'Anthony' , null , '2001-09-01' ); |
474 | INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) |
475 | VALUES (29, 'Griffin' , 'Rachel' , null , '2004-09-01' ); |
476 | INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) |
477 | VALUES (30, 'Shan' , 'Alicia' , null , '2003-09-01' ); |
478 | INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) |
479 | VALUES (31, 'Stewart' , 'Jasmine' , '1997-10-12' , null ); |
480 | INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) |
481 | VALUES (32, 'Xu' , 'Kristen' , '2001-7-23' , null ); |
482 | INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) |
483 | VALUES (33, 'Gao' , 'Erica' , null , '2003-01-30' ); |
484 | INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) |
485 | VALUES (34, 'Van Houten' , 'Roger' , '2000-12-07' , null ); |
487 | SET IDENTITY_INSERT dbo.Person OFF |
491 | INSERT INTO dbo.Department (DepartmentID, [ Name ], Budget, StartDate, Administrator) |
492 | VALUES (1, 'Engineering' , 350000.00, '2007-09-01' , 2); |
493 | INSERT INTO dbo.Department (DepartmentID, [ Name ], Budget, StartDate, Administrator) |
494 | VALUES (2, 'English' , 120000.00, '2007-09-01' , 6); |
495 | INSERT INTO dbo.Department (DepartmentID, [ Name ], Budget, StartDate, Administrator) |
496 | VALUES (4, 'Economics' , 200000.00, '2007-09-01' , 4); |
497 | INSERT INTO dbo.Department (DepartmentID, [ Name ], Budget, StartDate, Administrator) |
498 | VALUES (7, 'Mathematics' , 250000.00, '2007-09-01' , 3); |
503 | INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID) |
504 | VALUES (1050, 'Chemistry' , 4, 1); |
505 | INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID) |
506 | VALUES (1061, 'Physics' , 4, 1); |
507 | INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID) |
508 | VALUES (1045, 'Calculus' , 4, 7); |
509 | INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID) |
510 | VALUES (2030, 'Poetry' , 2, 2); |
511 | INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID) |
512 | VALUES (2021, 'Composition' , 3, 2); |
513 | INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID) |
514 | VALUES (2042, 'Literature' , 4, 2); |
515 | INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID) |
516 | VALUES (4022, 'Microeconomics' , 3, 4); |
517 | INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID) |
518 | VALUES (4041, 'Macroeconomics' , 3, 4); |
519 | INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID) |
520 | VALUES (4061, 'Quantitative' , 2, 4); |
521 | INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID) |
522 | VALUES (3141, 'Trigonometry' , 4, 7); |
526 | INSERT INTO dbo.OnlineCourse (CourseID, URL) |
528 | INSERT INTO dbo.OnlineCourse (CourseID, URL) |
530 | INSERT INTO dbo.OnlineCourse (CourseID, URL) |
532 | INSERT INTO dbo.OnlineCourse (CourseID, URL) |
536 | INSERT INTO dbo.OnsiteCourse (CourseID, Location, Days, [ Time ]) |
537 | VALUES (1050, '123 Smith' , 'MTWH' , '11:30' ); |
538 | INSERT INTO dbo.OnsiteCourse (CourseID, Location, Days, [ Time ]) |
539 | VALUES (1061, '234 Smith' , 'TWHF' , '13:15' ); |
540 | INSERT INTO dbo.OnsiteCourse (CourseID, Location, Days, [ Time ]) |
541 | VALUES (1045, '121 Smith' , 'MWHF' , '15:30' ); |
542 | INSERT INTO dbo.OnsiteCourse (CourseID, Location, Days, [ Time ]) |
543 | VALUES (4061, '22 Williams' , 'TH' , '11:15' ); |
544 | INSERT INTO dbo.OnsiteCourse (CourseID, Location, Days, [ Time ]) |
545 | VALUES (2042, '225 Adams' , 'MTWH' , '11:00' ); |
546 | INSERT INTO dbo.OnsiteCourse (CourseID, Location, Days, [ Time ]) |
547 | VALUES (4022, '23 Williams' , 'MWF' , '9:00' ); |
550 | INSERT INTO dbo.CourseInstructor(CourseID, PersonID) |
552 | INSERT INTO dbo.CourseInstructor(CourseID, PersonID) |
554 | INSERT INTO dbo.CourseInstructor(CourseID, PersonID) |
556 | INSERT INTO dbo.CourseInstructor(CourseID, PersonID) |
558 | INSERT INTO dbo.CourseInstructor(CourseID, PersonID) |
560 | INSERT INTO dbo.CourseInstructor(CourseID, PersonID) |
562 | INSERT INTO dbo.CourseInstructor(CourseID, PersonID) |
564 | INSERT INTO dbo.CourseInstructor(CourseID, PersonID) |
566 | INSERT INTO dbo.CourseInstructor(CourseID, PersonID) |
571 | INSERT INTO dbo.OfficeAssignment(InstructorID, Location) |
572 | VALUES (1, '17 Smith' ); |
573 | INSERT INTO dbo.OfficeAssignment(InstructorID, Location) |
574 | VALUES (4, '29 Adams' ); |
575 | INSERT INTO dbo.OfficeAssignment(InstructorID, Location) |
576 | VALUES (5, '37 Williams' ); |
577 | INSERT INTO dbo.OfficeAssignment(InstructorID, Location) |
578 | VALUES (18, '143 Smith' ); |
579 | INSERT INTO dbo.OfficeAssignment(InstructorID, Location) |
580 | VALUES (25, '57 Adams' ); |
581 | INSERT INTO dbo.OfficeAssignment(InstructorID, Location) |
582 | VALUES (27, '271 Williams' ); |
583 | INSERT INTO dbo.OfficeAssignment(InstructorID, Location) |
584 | VALUES (31, '131 Smith' ); |
585 | INSERT INTO dbo.OfficeAssignment(InstructorID, Location) |
586 | VALUES (32, '203 Williams' ); |
587 | INSERT INTO dbo.OfficeAssignment(InstructorID, Location) |
588 | VALUES (34, '213 Smith' ); |
591 | INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) |
593 | INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) |
594 | VALUES (2030, 2, 3.5); |
595 | INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) |
597 | INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) |
599 | INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) |
600 | VALUES (2021, 6, 2.5); |
601 | INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) |
602 | VALUES (2042, 6, 3.5); |
603 | INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) |
604 | VALUES (2021, 7, 3.5); |
605 | INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) |
607 | INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) |
609 | INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) |
611 | INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) |
612 | VALUES (4041, 9, 3.5); |
613 | INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) |
614 | VALUES (4041, 10, null ); |
615 | INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) |
616 | VALUES (4041, 11, 2.5); |
617 | INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) |
618 | VALUES (4041, 12, null ); |
619 | INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) |
620 | VALUES (4061, 12, null ); |
621 | INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) |
623 | INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) |
625 | INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) |
627 | INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) |
629 | INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) |
630 | VALUES (4022, 15, 2.5); |
631 | INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) |
633 | INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) |
634 | VALUES (4022, 17, null ); |
635 | INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) |
636 | VALUES (4022, 19, 3.5); |
637 | INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) |
639 | INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) |
641 | INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) |
643 | INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) |
644 | VALUES (4041, 22, 3.5); |
645 | INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) |
646 | VALUES (4061, 22, 2.5); |
647 | INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) |
649 | INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) |
650 | VALUES (1045, 23, 1.5); |
651 | INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) |
653 | INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) |
655 | INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) |
656 | VALUES (1050, 26, 3.5); |
657 | INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) |
659 | INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) |
661 | INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) |
662 | VALUES (1045, 28, 2.5); |
663 | INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) |
664 | VALUES (1050, 28, 3.5); |
665 | INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) |
667 | INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) |
668 | VALUES (1050, 30, 3.5); |
670 | INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) |
3:RIA之ADO.NET ENTITY DATA MODEL
为web创建ADO.NET ENTITY DATA MODEL,如图:
注意,习惯命名规则:Model数据库名.edmx。
在下一步中,我们选择刚才创建的数据库:
选择新建连接,按照指示完成数据库连接配置。
进入下一步:
在这一步中,注意一定要选择第一个红框中的内容,否则默认不能生成联表查询。在下一步中,我们选择“Generate from database”,如下:
点击Finish后,VS为我们生成了一个以edmx为后缀的文件。OK,到此暂停,我们先来看看这个edmx文件是干什么用的,为我们完成了什么工作。
3.1:什么是EDM
ENTITY DATA MODEL,简写为EDM,中文为实体数据模型。它由三个概念组成。概念模型由概念架构定义语言文件 (.csdl)来定义,映射由映射规范语言文件 (.msl),存储模型(又称逻辑模型)由存储架构定义语言文件 (.ssdl)来定义。这三者合在一起就是EDM模型。EDM模型在项目中的表现形式就是扩展名为.edmx的文件。
Entity Framework实现了一套类似于ADO.NET2.0中的连接类来操作EDM完成持久化。EntityFramework中所有发往EDM的操作都是经过EntityClient,包括使用LINQ to Entity进行的操作。目前可用操作如下:
4:针对web EMD的测试
理解了EMD在开发中所处的作用,我们就可以针对EDM来写个测试项目。为了简便期间,我们直接在EMD中的SchoolEntities类型的构造方法中直接创建测试(严格意义来说,这不是个单元测试,这仅是测试)。
针对这个操作,会在测试项目中生成一个SchoolEntitiesTest的类型,同时,在这个类型中会生成一个SchoolEntitiesConstructorTest的方法,如下:
由于我们仅仅测试EDM,而跟WEB本身没有关系,所有我们注释掉了红框中的内容。同时我们写入真正的测试代码,如下:
这里的一个小细节是,连接字符串我们通过编码的方式传入到测试方法。EDM的连接字符串和ADO.NET的连接字符串有很大不同,在这里,可公开一下我们的生成EDM连接字符串的方法:
01 | public string GetConnectionString() |
03 | string providerName = "System.Data.SqlClient" ; |
04 | string serverName = "192.168.0.96" ; |
05 | string databaseName = "mysample" ; |
06 | SqlConnectionStringBuilder sqlBuilder = new SqlConnectionStringBuilder(); |
07 | sqlBuilder.DataSource = serverName; |
08 | sqlBuilder.InitialCatalog = databaseName; |
09 | sqlBuilder.IntegratedSecurity = false ; |
10 | sqlBuilder.UserID = "sa" ; |
11 | sqlBuilder.Password = "sasa" ; |
12 | string providerString = sqlBuilder.ToString(); |
13 | EntityConnectionStringBuilder entityBuilder = new EntityConnectionStringBuilder(); |
14 | entityBuilder.Provider = providerName; |
15 | entityBuilder.ProviderConnectionString = providerString; |
16 | entityBuilder.Provider = providerName; |
17 | entityBuilder.Metadata = "res://*/Modelmysample.csdl|res://*/Modelmysample.ssdl|res://*/Modelmysample.msl" ; |
18 | return entityBuilder.ToString(); |
调试我们的测试代码,最终我们会获取到数据库中的4条记录。
通过了以上的阐述,我们了解了EDM的作用,以及如何测试EDM。接下来,我们需要知道如何在SL APP中调用WEB中的EDM。
5:Domain Service Class
在SL APP中调用EDM是通过WEB的Domain Service Class来实现的。简单的说来,它是RIA SERVICE框架中的一个重要内容。
在WEB选择添加新项,选择Domain Service Class,命名,下一步会出现如下界面:
我们可以做出如上勾选。确定后,编译整个解决方法,会发现目前的解决方案结构会变成如下形式:
注意,第一个红框部分,是VS自动为我们在SL APP生成的。它是第二个红框在客户端的对应版本。我们需要将它包含到项目中去。它包含了所有服务端版本类中定义的方法、实体等,而可在客户端直接调用。
6:Ria Service数据流转
好了,经过以上的描述我们知道了:
EDM:直接操作数据库;
Domain Service Class:调用EDM,并把数据接口通过WCF的形式开放给客户端;
*.Web.g.cs:调用Domain Service Class,完成对数据的读取,并最终呈现给UI;
7:最终展示
在SL APP中的UI调用呈现数据。前台:
后台,直接使用*.Web.g.cs中的DomainServiceMySample 加载数据:
DomainServiceMySample context = new DomainServiceMySample(); |
context.Load(context.GetEmpQuery()); |
lb1.ItemsSource = context.emps; |
最后界面显示:
8:问题
该示例演示了使用Entity Framework和WCF Ria Services进行Silverlight开发。我们当然可以继续使用此模式完善功能,知道一个功能复杂的应用程序开发完毕。但是,当前,此示例起码存在如下几个问题。
1:实体模型被紧耦合在EDM中,同时它不能项目(模块)使用。随着每一次更新EDM,实体模型会被覆盖;
2:EDM和BLL紧耦合在一起;
3:没有提炼出数据接口,导致我们没有办法在此示例中进行单元测试。示例中虽然存在测试项目,但那是数据库相关的,达不到单元测试的要求;
下篇我们将继续重构该示例,以逐步解决这些问题。
本示例源码下载:SilverlightApplicationSchool20110612.zip