LINQ Tieng Viet

2009

LINQ to SQL Tutorial From ScottGu blog

o Hi Nam

LINQ to SQL Tutorial

2


c dch t blog ScottGu - http://weblogs.asp.net/scottgu/default.aspx

Ngi dch: o Hi Nam http://www.daohainam.com


3

Mc lc Bi 1: S dng LINQ to SQL .............................................................................................................................. 6

1. LINQ to SQL l g? .................................................................................................................................. 6

2. M hnh ha CSDL dng LINQ to SQL: ................................................................................................. 6

3. Tm hiu lp DataContext ........................................................................................................................ 7

4. Cc v d LINQ to SQL ........................................................................................................................... 8

a. Ly cc Product t CSDL .................................................................................................................... 8

b. Cp nht mt sn phm trong CSDL ................................................................................................... 9

c. Chn thm mt phn loi mi v hai sn phm vo CSDL ................................................................. 9

d. Xa cc sn phm .............................................................................................................................. 10

e. Gi mt th tc .................................................................................................................................. 11

f. Ly cc sn phm v phn trang ........................................................................................................ 12

5. Tng kt ................................................................................................................................................. 12

Bi 2: nh ngha cc lp m hnh d liu ........................................................................................................ 13

1. To ra mt m hnh d liu LINQ to SQL ............................................................................................. 14

2. Cc lp thc th ..................................................................................................................................... 15

3. To cc lp thc th t CSDL ............................................................................................................... 15

4. Cch t tn v ng php s nhiu......................................................................................................... 17

5. Quan h gia cc thc th ...................................................................................................................... 18

6. Delay/Lazy Loading ............................................................................................................................... 19

7. Dng cc Stored Procedure .................................................................................................................... 19

Dng SPROCS cp nht/xa,thm d liu ............................................................................................ 21

8. Tng kt ................................................................................................................................................. 22

Bi 3: Truy vn C s d liu ............................................................................................................................ 23

1. M hnh ha CSDL Northwind dng LINQ to SQL.............................................................................. 23

2. Ly cc sn phm ................................................................................................................................... 23

3. Trc quan ha cc cu truy vn LINQ to SQL trong trnh g li .......................................................... 25

4. Gn ni cc cu truy vn LINQ to SQL vo cc control LINQ to SQL ................................................ 26

5. Data Sharping ......................................................................................................................................... 27

6. Phn trang kt qu truy vn.................................................................................................................... 32

7. Tng kt ................................................................................................................................................. 34

Bi 4: Cp nht c s d liu ............................................................................................................................. 35

1. CSDL Northwind c m hnh ha dng LINQ to SQL ..................................................................... 35

Change Tracking v DataContext.SubmitChanges() ................................................................................. 36

2. Cc v d Insert v Delete ...................................................................................................................... 37


4

a. Thm mt sn phm ........................................................................................................................... 38

b. Xa cc sn phm .............................................................................................................................. 38

3. Cp nht thng qua cc quan h ............................................................................................................. 38

4. Transactions ........................................................................................................................................... 40

5. Kim tra d liu v Business Logic ....................................................................................................... 41

6. H tr kim tra cc gi tr thuc tnh da trn schema ca CSDL ........................................................ 41

7. H tr ty bin vic kim tra gi tr cc thuc tnh ................................................................................ 41

8. H tr ty bin vic kim tra tnh hp l ca thc th ........................................................................... 42

9. Ty bin cc phng thc kim tra vic thm/xa/sa d liu ............................................................. 43

10. Nng cao: Xem danh sch thay i cho Transaction ......................................................................... 44

11. X l cc thay i ng thi vi Optimistic Concurrency: ............................................................... 45

12. Dng SPROCs hoc ty bin logic cc cu SQL: .............................................................................. 45

Bi 5: S dng asp:LinqDataSource .................................................................................................................. 47

1. ng dng mu m chng ta s xy dng: .............................................................................................. 47

2. l g v n gip g cho chng ta? ..................................................................... 48

Bc 1: nh ngha m hnh d liu .......................................................................................................... 49

Bc 2: To danh sch sn phm .............................................................................................................. 50

Bc 3: B cc ct khng cn thit ........................................................................................................... 54

Bc 4: Lc danh sch sn phm .............................................................................................................. 57

Bc 5: Thm cc quy tc kim tra logic .................................................................................................. 60

3. Tng kt ................................................................................................................................................. 63

Bi 6: Ly d liu dng Stored Procedure ......................................................................................................... 65

1. Dng SPROC hay khng SPROC? l mt vn . ..................................................................... 65

2. Cc bc nh x v gi SPROC dng LINQ to SQL ............................................................................ 66

3. Cch nh x mt SPROC vo mt DataContext ca LINQ ................................................................... 67

4. Cch gi SPROC mi c to ............................................................................................................. 68

5. nh x kiu tr v ca phng thc SPROC vo mt lp trong m hnh d liu ................................ 69

6. X l cc tham s th tc dng OUTPUT ............................................................................................. 72

7. X l cc th tc tr v nhiu kiu kt qu khc nhau........................................................................... 73

H tr cc hm do ngi dng t nh ngha (UDF) ................................................................................. 75

8. Tng kt ................................................................................................................................................. 77

Bi 7: Cp nht d liu dng Stored Procedure ................................................................................................. 78

9. Bc 1: To lp truy xut d liu (cha dng n cc th tc) ............................................................ 78

Thm cc quy tc kim tra d liu vo cc lp m hnh d liu ............................................................... 78

Thm phng thc GetCustomer() vo lp DataContext ......................................................................... 80


5

10. Bc 2: Dng lp truy cp d liu (cha s dng SPROC) .............................................................. 81

11. Ch mt giy - Ti ngh bi vit ny nh ni v vic dng SPROC c m ??? ............................... 82

12. Cch s dng SPROC thc hin Insert/Update/Delete ................................................................. 83

13. Bc 3: Thm mt Order bng cch dng SPROC ........................................................................... 83

14. Bc 4: Thc hin cp nht dng SPROC ........................................................................................ 87

15. Bc 5: Dng lp DAL ln na ......................................................................................................... 89

16. Mt s u im ca vic dng SPROC ............................................................................................. 89

Dng cc tham s dng output: .................................................................................................................. 89

S th no nu mt SPROC pht ra mt li? ............................................................................................. 90

Ti c th vit code thay v dung ORM designer gi SPROC? ............................................................ 90

17. Tng kt ............................................................................................................................................. 90

Bi 8: Thc thi cc biu thc SQL ty bin ....................................................................................................... 91

1. Dng cc cu truy vn SQL ty bin vi LINQ to SQL ........................................................................ 93

2. Dng ExecuteQuery ............................................................................................................................... 93

3. Ty bin cc biu thc SQL v theo vt (tracking) cc thao tc cp nht: ............................................ 94

4. Ty bin cc biu thc SQL vi cc lp ca bn .................................................................................. 95

5. Ty bin cc cu SQL cho Inserts/Updates/Deletes .............................................................................. 95

6. Tng kt ................................................................................................................................................. 96

Bi 9: Dng biu thc LINQ ty bin vi .................................................................... 97

1. Tm tt: dng vi mt mnh where c khai bo .................................... 97

2. Dng cc s kin Selecting vi ........................................................................ 98

3. Thc hin cc php chiu khi truy vn vi s kin Selecting .............................................................. 101

4. Tng kt ............................................................................................................................................... 103


6

Bi 1: S dng LINQ to SQL

y l bi vit u tin trong lot bi c ch LINQ to SQL, cc bi ny s cho bn mt ci nhn khi qut, gip bn lm quen vi LINQ, mt trong nhng cng ngh mi c trong .NET 3.5.

Lot bi ny c da trn lot Tutorial ca ScottGu (http://weblogs.asp.net/scottgu).

1. LINQ to SQL l g?

LINQ to SQL l mt phin bn hin thc ha ca O/RM (object relational mapping) c bn trong .NET Framework bn Orcas (nay l .NET 3.5), n cho php bn m hnh ha mt c s d liu dng cc lp .NET. Sau bn c th truy vn c s d liu (CSDL) dng LINQ, cng nh cp nht/thm/xa d liu t .

LINQ to SQL h tr y transaction, view v cc stored procedure (SP). N cng cung cp mt cch d dng thm kh nng kim tra tnh hp l ca d liu v cc quy tc vo trong m hnh d liu ca bn.

2. M hnh ha CSDL dng LINQ to SQL:

Visual Studio Orcas tch hp thm mt trnh thit k LINQ to SQL nh mt cng c d dng cho vic m hnh ha mt cch trc quan cc CSDL dng LINQ to SQL. Bi vit sau s i su hn vo cch dng trnh thit k ny (bn cng c th xem on video ny xem cch ti to mt m hnh LINQ to SQL).

Bng cch dng trnh thit k LINQ to SQL, ti c th d dng to mt m hnh cho CSDL mu Northwind ging nh di y:


7

M hnh LINQ to SQL trn nh ngha bn lp thc th: Product, Category, Order v OrderDetail. Cc thuc tnh ca mi lp nh x vo cc ct ca bng tng ng trong CSDL. Mi instance ca mt lp biu din mt dng trong bng d liu.

Cc mi tn gia bn lp thc th trn biu din quan h gia cc thc th khc nhau, chng c to ra da trn cc mi quan h primary-key/foreign-key trong CSDL. Hng ca mi tn ch ra mi quan h l mt mt hay mt nhiu. Cc thuc tnh tng ng s c thm vo cc lp thc th trong cc trng hp ny. Ly v d, lp Category trn c mt mi quan h mt nhiu vi lp Product, iu ny c ngha n s c mt thuc tnh Categories l mt tp hp cc i tng Product trong Category ny. Lp Product cng s c mt thuc tnh Category ch n i tng Category cha Product ny bn trong.

Bng cc phng thc bn tay phi bn trong trnh thit k LINQ to SQL trn cha mt danh sch cc SP tng tc vi m hnh d liu ca chng ta. Trong v d trn ti thm mt th tc c tn GetProductsByCategory. N nhn vo mt categoryID v tr v mt chui cc Product. Chng ta s xem bng cch no c th gi c th tc ny trong mt on code bn di.

3. Tm hiu lp DataContext

Khi bn bm nt Save bn trong mn hnh thit k LINQ to SQL, Visual Studio s lu cc lp .NET biu din cc thc th v quan h bn trong CSDL m chng ta va m hnh ha. C mi mt file LINQ to SQL chng ta thm vo solution, mt lp DataContext s c to ra, n s c dng khi cn truy vn hay cp nht li cc thay i. Lp DataContext c to s c cc thuc tnh biu din mi bng c m hnh ha t CSDL, cng nh cc phng thc cho mi SP m chng ta thm vo.


8

Ly v d, di y l lp NorthwindDataContext c sinh ra da trn m hnh chng ta to ra trn:

4. Cc v d LINQ to SQL

Mt khi m hnh ha CSDL dng trnh thit k LINQ to SQL, chng ta c th d dng vit cc on lnh lm vic vi n. Di y l mt vi v d v cc thao tc chung khi x l d liu:

a. Ly cc Product t CSDL

on lnh di y dng c php LINQ ly v mt tp IEnumerable cc i tng Product. Cc sn phm c ly ra phi thuc phn loi Beverages:

C#:

VB:


9

b. Cp nht mt sn phm trong CSDL

on lnh di y cho thy cch ly mt sn phm, cp nht li gi tin v lu li CSDL.

C#:

VB:

c. Chn thm mt phn loi mi v hai sn phm vo CSDL

on m di y biu din cch to mt phn loi mi, v to hai sn phm mi v a chng vo trong phn loi to. C ba sau s c a vo c s d liu.

Ch rng ti khng cn phi t qun l cc mi quan h primary key/foreign key, thay vo , ti ch n gin thm cc i tng Product vo tp hp Products ca i tng category, v ri thm i tng category vo tp hp Categories ca DataContext, LINQ to SQL s bit cch thit lp cc gi tr primary key/foreign key mt cch thch hp.

(Add c thay i bng InsertOnSubmit trong phin bn hin ti)

C#


10

VB:

d. Xa cc sn phm

on m sau s biu din cch xa tt c cc sn phm Toy khi CSDL:

(RemoveAll c thay i bng DeleteAllOnSubmit trong phin bn hin ti)

C#:


11

VB:

e. Gi mt th tc

on m di y biu din cch ly cc thc th Product m khng dng c php ca LINQ, m gi n th tc GetProductsByCategory chng ta thm vo trc y. Nh rng mt khi ly v kt qu, ti c th cp nht/xa v sau gi db.SubmitChanges() cp nht cc thay i tr li CSDL.

C#:

VB:


12

f. Ly cc sn phm v phn trang

on m di y biu din cch phn trang trn server nh mt phn ca cu truy vn LINQ. Bng cch dng cc ton t Skip() v Take(), chng ta s ch tr v 10 dng t CSDL bt u t dng 200.

C#:

VB:

5. Tng kt

LINQ to SQL cung cp mt cch hay, r rng m hnh ha lp d liu trong ng dng ca bn. Mt khi nh ngha m hinh d liu, bn c th d dng thc hin cc cu truy vn cng nh cp nht, xa, sa d liu mt cch hiu qu.

Hi vng nhng hng dn v v d mu trn gip bn lm quen vi LINQ. Ti s tip tc cc bi vit ny gip bn khm ph LINQ to SQL mt cch chi tit hn.


13

Bi 2: nh ngha cc lp m hnh d liu

Trong phn mt, ti tho lun v LINQ to SQL l g v cung cp mt ci nhn c bn v nhng trng hp chng ta c th s dng n.

Trong bi vit u tin, ti cng cung cp cc on code mu biu din cch x l d liu dng LINQ to SQL, bao gm:

Cch truy vn d liu Cc cp nht d liu Cch chn v to quan h cc dng trong mt CSDL Cch xa cc dng trong mt CSDL Cch gi mt th tc Cch ly d liu v phn trang trn server

Ti thc hin tt c cc thao tc d liu bng cch dng mt m hnh d liu LINQ to SQL ging nh di y:

Trong bi ny, ti s i vo chi tit cch to ra mt m hnh d liu LINQ to SQL ging nh trn.

LINQ to SQL, cng nh LINQ to SQL, v tt c cc tnh nng khc m ti ni n trong lot bi ny s c coi nh mt phn ca .NET 3.5 v Visual Studio Orcas (nay l Visual Studio 2008).

Bn c th lm theo tt c cc bc di y bng cch ti v hoc Visual Studio 2008 hoc Visual Web Developer Express. C hai u c th c ci t v dng ng thi vi Visual Studio 2005.


14

1. To ra mt m hnh d liu LINQ to SQL

Bn c th thm mt m hnh d liu LINQ to SQL v mt d n ASP.NET, Class Library hay Windows bng cch dng ty chn Add New Item bn trong Visual Studio v chn LINQ to SQL:

Vic chn mc LINQ to SQL s khi chy LINQ to SQL designer, v cho php bn m hnh ha cc lp m n biu din mt CSDL quan h. N cng s to ra mt lp kiu DataContext, trong c cc thuc tnh biu din mi bng m chng ta m hnh ha trong CSDL, cng nh cc phng thc cho mi Stored Procedure m chng ta m hnh ha. Nh ti m t trong phn 1 ca lot bi ny, lp DataContext l thnh phn trung tm ca m hnh, ton b cc thao tc truy vn hoc cp nht d liu u c thc hin thng qua lp ny.

Di y l nh chp mn hnh ca mt ca s thit k LINQ to SQL, v cng l ci m bn s thy ngay khi to ra mt m hnh d liu LINQ to SQL:


15

2. Cc lp thc th

LINQ to SQL cho php bn m hnh ha cc lp nh x vo CSDL. Cc lp ny thng c l Entity Class (lp thc th) v cc instance ca n thng c gi l Entity (thc th). Cc lp entity nh x vo cc bng bn trong mt CSDL. Cc thuc tnh ca cc lp thng thng nh x vo cc ct trong bng. Mi instance ca mt lp thc th biu din mt dng trong bng.

Cc lp thc th trong LINQ to SQL khng cn phi k tha t mt lp c bit no khc, iu cho php bn c th cho php chng tha k t bt c i tng no bn mun. Tt c cc lp c to ra dng LINQ to SQL designer u c nh ngha nh partial class c ngha l bn c th vit thm code thm vo cc thuc tnh, phng thc v s kin cho chng.

Khng ging nh chc nng DataSet/TableAdapter c trong VS 2005, khi dng LINQ to SQL designer, bn khng cn ch ra cu truy vn SQL c dng to ra m hnh v lp truy xut d liu.

Thay vo , bn tp trung ch yu vo vic nh ngha cc lp thc th, cch chng nh x vo CSDL, v mi quan h gia chng. Trnh LINQ to SQL c th m bn dng s m bo vic sinh ra cc lnh SQL thch hp vo lc chy khi bn tng tc v lm vic vi cc thc th d liu. Bn c th dng c php truy vn LINQ ch ra cch bn mun truy vn d liu.

3. To cc lp thc th t CSDL

Nu c cu trc cho CSDL, bn c th dng n to cc lp thc th LINQ to SQL mt cch nhanh chng.


16

Cc d dng nht lm iu ny l m CSDL trong ca s Server Explorer bn trong Visual Studio, chn cc table v view m bn mun m hnh ha, v ko th chng ln trn ca s LINQ to SQL designer.

Khi bn thm 2 bng (Categories and Products) v 1 view (Invoices) t CSDL Northwind vo ca s LINQ to SQL designer, bn s c thm 3 lp thc th c to ra mt cch t ng:


17

Dng cc lp m hnh ha d liu trn, bn c th chy tt c cc on lnh mu c mt t trong phn 1 ca lot bi ny. Ti khng cn thm bt k on code no hay cu hnh c th thc hin c cc thao tc query, insert, update, delete v phn trang.

4. Cch t tn v ng php s nhiu

Mt trong nhng th bn nghe nhc n khi dung LINQ to SQL l n c th t ng chuyn tn bng v ct thnh dng s nhiu khi to cc lp thc th. Ly v d: Bng Products trong v d ca chng ta to ra lp Product, cng nh bng Categories to ra lp Category. Cch t tn ny gip m hnh ca bn thng nht vi quy c t tn trong .NET.

Nu khng thch tn lp hay tn thuc tnh do trnh designer sinh ra, bn vn c th sa li thnh bt c tn no bn thch. Bn c th lm iu ny bng cch chnh sa tn thc th/thuc tnh bn trong trnh thit k hoc thng qua bng thuc tnh.

Kh nng t tn cho cc thc th/thuc tnh/quan h khc vi tn trong CSDL rt hu dng trong mt s trng hp, v d:


18

1. Khi tn bng/ct trong CSDL b thay i. Bi v m hnh thc th ca bn c th c tn khc vi tn trong CSDL, do vy bn c th ch cn cp nht li cc quy tc nh x m khng cn cp nht chng trnh hoc cc lnh truy vn c th dng c tn mi.

2. Khi cc thnh phn bn trong CSDL c t tn khng r rng. V d: thay v dng au_lname v au_fname cho cc tn thuc tnh ca mt lp thc th, bn c th t tn chng thnh LastName v FirstName trong lp thc th v vit cc lnh dng vi n (m khng cn i tn cc ct trong CSDL).

5. Quan h gia cc thc th

Khi bn ko th cc i tng t Server Explorer ln trn ca s LINQ to SQL designer, VS s t ng xc nh cc mi quan h primary key/foreign key gia cc i tng, v t ng to cc quan h mc nhin gia cc lp thc th khc nhau m n to. V d, khi bn thm c hai bng Products v Categories t Northwind ln trn ca s LINQ to SQL, bn c th thy mi mi quan h mt nhiu gia chng (c biu din bng mt mi tn trn ca s son tho):

Mi quan h trn s lm lp thc th Product c thm mt thuc tnh l Category, bn c th dng truy cp vo thc th Category ca mt Product. N cng lm lp Category c thm thuc tnh Products, y l mt tp hp cho php bn ly ra tt c cc Product c trong Category .


19

Nu bn khng thch cch m trnh thit k m hnh ha hoc t tn, bn hon ton c th chnh sa li. Ch cn click ln mi tn ch ra quan h trn ca s son tho v truy cp vo cc thuc tnh ca n thng qua bng thuc tnh i tn, chnh sa hoc thm ch xa n.

6. Delay/Lazy Loading

LINQ to SQL cho php ch ra cc thuc tnh ca mt thc th s c ly v trc(prefetch) hay ch c ly khi ngi dng ln u truy cp (gi l delay/lazy loading). Bn c th ty bin cc quy tc prefetch/lazy load cho cc thuc tnh trong thc th bng cch chn thuc tnh hay quan h , v t li gi tr cho thuc tnh Delay Loaded thnh true hoc false.

Ti c th cu hnh thuc tnh Picture n ch c np khi dng n bng cch t thuc tnh Delay Loaded thnh true:

Ghi ch: Thay v cu hnh prefetch/delay load trn cc thc th, bn cng c th t li thng qua cc lnh khi bn thc hin cc cu truy vn LINQ trn lp thc th (ti s hng dn cch lm iu ny trong bi vit sau ca lot bi ny).

7. Dng cc Stored Procedure

LINQ to SQL cho php bn c th m hnh ha cc th tc lu tr nh l cc phng thc trong lp DataContext. V d, cho rng chng ta nh ngha mt th tc n gin c tn SPROC nh di y ly v cc thng tin sn phm da trn mt CategoryID:


20

Ti c th dng Server Explorer trong VS ko/th th tc SPROC ln trn ca s son tho LINQ to SQL c th thm mt phng thc cho php goi SPROC. Nu ti th SPROC ln trn thc th Product, LINQ to SQL designer s khai bo SPROC tr v mt tp kt qu c kiu IEnumerable:

Sau ti c th dng c php LINQ to SQL hay gi thng phng thc trn ly v cc thc th t CSDL:


21

Dng SPROCS cp nht/xa,thm d liu

Mc nhin LINQ to SQL s t ng to ra cc biu thc SQL ph hp cho bn mi khi mun cp nht/xa/thm d liu. V d, nu bn vit m LINQ to SQL nh di y cp nht mt s gi tr trn mt thc th Product:

Mc nhin, LINQ to SQL s to v thc thi pht biu UPDATE tng ng khi bn xc nhn thay i (ti s ni thm v vn ny trong nhng bi vit khc).

Bn cng c th nh ngha v dng cc th tc INSERT, UPDATE, DELETE nu mun. cu hnh, click ln mt lp thc th trong ca s LINQ to SQL v trong bng thuc tnh, nhn chut ln trn nt trn cc gi tr Delete/Insert/Update, v chn SPROC m bn nh ngha.


22

C mt iu hay l nhng thay i trn hon ton c thc hin lp nh x LINQ to SQL c ngha l tt c nhng on lnh m ti vit trc y u c th tip tc lm vic m khng cn thay i bt k iu g. iu ny gip trnh phi thay i li code ngay c nu sau ny bn mun dng mt hm SPROC ti u hn sau ny.

8. Tng kt

LINQ to SQL cung cp mt cch thc n gin, sng sa m hnh ha lp d liu trong ng dng ca bn. Mt khi bn nh ngha m hnh d liu, bn c th thc hin cc cu truy vn, thm, cp nht v xa d liu mt cch d dng v hiu qu.

Dng trnh thit k LINQ to SQL c sn trong Visual Studio v Visual Web Developer Express, bn c th to v qun l m hnh d liu cc k nhanh. Trnh LINQ to SQL designer cng v cng mm do bn c th ty bin cc hnh vi mc nhin v ghi hoc m rng h thng sao cho ph hp vi nhng yu cu c th no .

Trong nhng bi tip theo ti s dng m hnh d liu chng ta to ra trong bi ny o sau hn vo vic truy vn, thm, cp nht v xa d liu. Trong cc bi vit v cp nht, thm, xa ti cng s tho lun v cch thm cc on lnh kim tra d liu cng nh cc quy tc vo cc lp thc th chng ta nh ngha trn.

Mike Taulty cng c mt s on video rt hay v LINQ to SQL m bn nn xem ti y. Chng cung cp mt cch tuyt vi hc bng cch xem nhng ngi khc tng bc s dng LINQ to SQL.


23

Bi 3: Truy vn C s d liu

Thng trc ti bt u vit lot bi v LINQ to SQL. LINQ to SQL l mt b khung (framework) c sn cho O/RM (object relational mapping) trong .NET 3.5, n cho php bn d dng m hnh ha cc CSDL quan h dng cc lp .NET. Bn c th dng cc biu thc LINQ truy vn CSDL, cng nh c th cp nht/thm/xa d liu t .

Trong bi vit ny, ti s i su hn vo cch chng ta dng m hnh d liu to trong phn 2, v cch dng n truy vn d liu bn trong mt d n ASP.NET.

1. M hnh ha CSDL Northwind dng LINQ to SQL

Trong phn 2 ca lot bi ny, ti i qua cc bc to mt m hnh cc lp LINQ to SQL bng cch dng trnh LINQ to SQL c sn trong VS 2008. Di y l mt hnh m ti to dng CSDL mu Northwind:

2. Ly cc sn phm

Mt khi nh ngha m hnh d liu nh trn, chng ta c th d dng truy vn v ly d liu t CSDL. LINQ to SQL cho php bn lm iu ny bng cch vit cc cu truy vn dng c php LINQ vi lp NorthwindDataContext m chng ta to dng trnh thit k LINQ to SQL designer trn.

V d, ly v duyt qua mt tp cc i tng Product, ti c th vit code nh di y:


24

Trong cu truy vn trn, ti dng mt mnh where trong c php LINQ ch tr v cc sn phm trong mt category cho trc. Ti hin ang dng CategoryID ca Product thc hin lc ra cc dng mong mun.

Mt trong nhng im hay l ti c rt nhiu la chn, rt nhiu cch ty bin cu lnh, v ti c th nm bt u im ca mi quan h gia cc thc th m ti to khi m hnh ha cc lp lm cho cu lnh phong ph v t nhin hn. V d, ti c th sa li cu truy vn lc ra cc dng theo CategoryName thay v CategoryID bng cch vit cu lnh LINQ nh sau:

Ch cch ti dng thuc tnh Category trn mi i tng Product lc theo CategoryName ca Category cha Product . Thuc tnh ny c t ng to ra bi LINQ to SQL v chng ta m hnh ha cc lp Category v Product nh mt mi quan h mt-nhiu.

Mt v d khc v cch dng quan h trong m hnh d liu bn trong cc cu truy vn, chng ta c th vit cu lnh LINQ nh di y ly v ch nhng Product c 5 hoc hn n t hng:

Ch cch chng ta dng tp hp OrderDetails m LINQ to SQL to trn mi lp Product (nh vo mi quan h mt-nhiu m chng ta m hnh ha trong trnh thit k LINQ to SQL).


25

3. Trc quan ha cc cu truy vn LINQ to SQL trong trnh g li

Cc trnh nh x O/R (Object relational mapper) nh LINQ to SQL to ra v thc thi cc cu lnh SQL mt cch t ng mi khi bn thc hin mt cu truy vn hay cp nht m hnh i tng ca n.

Mt trong nhng iu quan tm ln nht m cc lp trnh vin mi quen vi ORM l: Cu lnh SQL thc s c thc thi l g?. Mt iu thc s th v v LINQ to SQL l n cho php xem rt d dng cu lnh SQL c thc thi thc s khi bn chy ng dng trong ch g li.

Bt u t bn Beta2 ca VS 2008, bn c th dng mt LINQ to SQL visualizer plug-in xem mt cch d dng (v kim tra) bt k cu lnh truy vn LINQ to SQL no. Ch cn t mt breakpoint v di chut ln trn mt cu lnh LINQ to SQL, sau nhn vo biu tng chic knh lp xem gi tr ca cu lnh mt cch trc quan:

Mt ca s s hin ln cho php bn xem mt cch chnh xc cu lnh LINQ to SQL m LINQ to SQL s dng ly v cc i tng Product:

Nu bn nhn nt Execute trn ca s ny, n s cho php bn chy cu lnh SQL trc tip trong trnh debugger v xem mt cch chnh xc d liu c tr v:


26

iu ny r rng lm cho vic xem nhng g LINQ to SQL lm cho bn tr thnh cc k d dng. Nh rng bn c th d dng thay th cu SQL m LINQ to SQL thc thi nu mun - mc d trong 98% trng hp ti ngh bn s thy rng cu lnh m LINQ to SQL thc thi l thc s, thc s tt.

4. Gn ni cc cu truy vn LINQ to SQL vo cc control LINQ to SQL

Cc cu truy vn LINQ tr v kt qu m n s implement interrface IEnumerable y cng l interface m cc control ASP.NET dng h tr gn ni cc i tng. iu ny c ngha l bn c th gn ni kt qu ca bt k cu lnh LINQ, LINQ to SQL hay LINQ to XML vo bt k control ASP.NET no.

Ly v d, bn c th khai bo mt control trong mt trang .aspx ging nh sau:

Ti cng c th gn ni kt qu ca cu LINQ to SQL vit trc y vo GridView ging nh sau:


27

N s sinh ra mt trang trng nh sau:

5. Data Sharping

Hin ti, mi khi xc nh kt qu truy vn, chng ta ly ton b cc ct d liu cn thit cho cc i tng thuc lp Product:

V d, cu truy vn sau ly v cc sn phm:

V ton b kt qu c tr v:


28

Thng th chng ta ch mun tr v mt tp con ca d liu v mi sn phm. Chng ta c th dng tnh nng data shaping m LINQ v cc trnh dich C#, VB mi h tr ch ra rng chng ta ch mun mt tp con bng cch chnh sa li cu truy vn nh sau:

iu ny s tr v ch mt tp con d liu c tr v t CSDL:


29

Mt iu thc s th v v LINQ to SQL l ti c th tn dng tt c u im ca cc quan h trong m hnh d liu khi mun gt gia li d liu. N cho php ti biu din y v hiu qu cc cu truy vn. Ly v d, cu truy vn di y ly v ID v Name t thc th Product, tng s n hng c t cho sn phm , v ri ly tng gi tr ca tng n hng:

LINQ to SQL thng minh c th chuyn biu thc LINQ trn thnh cu SQL di y khi n c thc thi:


30

Cu SQL trn cho php tnh ton tt c cc gi tr ca NumOrders v Revenue t ngay trn SQL server, v tr v ch nhng d liu nh di y (lm cho vic thc thi c nhanh chng):

Chng ta c th gn ni tp kt qu vo control GridView to ra mt giao din p hn:


31

Bn cng c th c h tr y bi tnh nng intellisense bn trong VS 2008 khi vit cc cu truy vn LINQ:

Trong v d trn, ti ang s dng mt kiu v danh (anonymous type) v dng object initialization gt gia v nh ngha cu trc tr v. Mt iu thc s tuyt vi l VS 2008 cung cp intellisense y , kim tra lc dch v c refactoring khi lm vic c vi cc tp kt qu c kiu v danh:


32

6. Phn trang kt qu truy vn

Mt trong nhng yu cu chung khi vit cc trang web l bn phi c kh nng phn trang mt cch hiu qu. LINQ cung cp sn hai hm m rng cho php bn c th lm iu mt cch d dng v hiu qu hm Skip() v Take().

Bn c th dng Skip() v Take() nh di y ch ra rng bn ch mun ly v 10 i tng sn phm bt u t mt sn phm cho trc m chng ta chi ra trong tham s truyn vo:

Ch trn ti khng dng Skip() v Take() trong cu khai bo truy vn cc sn phm m ch dng ti khi gn kt d liu vo GridView. Mi ngi hay hi C phi lm nh vy th cu lnh u tin s ly ton b d liu t CSDL v lp gia, ri sau mi thc hin vic phn trang ?. Cu tr li l Khng. L do l v LINQ ch thc s thc thi cc cu truy vn khi bn ly kt qu t n m thi.


33

Mt trong nhng u im ca m hnh ny l n cho php bn c th vit cc cu lnh phc tp bng nhiu bc, thay v phi vit trong mt cu lnh n (gip d c hn). N cng cho php bn to ra cc cu truy vn t cc cu khc, gip bn c th xy dng cc cu truy vn rt phc tp cng nh c th dng li c cc cu truy vn khc.

Mt khi ti c phng thc BindProduct() nh ngha trn, ti c th vit lnh nh di y ly v ch s u t query string, v cho php danh sch sn phm c th c hin phn trang v hin th:

N s cho chng ta mt trang hin th cc sn phm c nhiu hn 5 n t hng, cng vi doanh thu tng ng, v c phn trang da trn tham s truyn vo qua query string:

Ghi ch: Khi lm vic vi SQL 2005, LINQ to SQL s dng hm ROW_NUMBER() thc hin vic phn trang logic trong CSDL. N m bo rng ch 10 dng d liu c tr v khi chng ta thc hin cc cu lnh trn:


34

N lm cho vic phn trang hiu qu v d dng hn, c bit l vi cc tp d liu ln.

7. Tng kt

Hi vng cc bc trn cung cp mt ci nhn y v nhng c tnh m LINQ to SQL cung cp, tm hiu thm v cc biu thc LINQ v c php mi c dng trong C# v VB.NET trong VS 2008, xin hy tham kho thm cc bi vit sau:

Automatic Properties, Object Initializer and Collection Initializers

Extension Methods

Lambda Expressions

Query Syntax

Anonymous Types

Trong bi vit tip theo trong lot bi ny, ti s cho thy cch thm cc php kim tra vo m hnh d liu ca chng ta, v biu din cch chng ta c th dng a logic chng trnh vo mi ln thc thi cc cu lnh update, insert, hay delete d liu. Ti cng s cho cc bn thy cc tnh nng cao cp hn ca lazy loading v eager loading, cch dng control mi h tr vic khai bo databinding trong ASP.NET, cch gii quyt xung t


35

Bi 4: Cp nht c s d liu

Trong bi hm nay, ti s ni r hn v cch chng ta dng CSDL c m hnh ha trc y, v dng n cp nht, chnh sa v xa d liu. Ti cng s cho cc bn thy cc chng ta c th thm cc quy tc (business rule sau ny tr i ti s nguyn t business rule, v t ny r ngha hn) v ty bin cch xc thc tnh hp l ca d liu.

1. CSDL Northwind c m hnh ha dng LINQ to SQL

Trong phn 2 ca lot bi ny, ti i qua cc bc to nn m hnh cc lp LINQ to SQL dng LINQ to SQL designer c trong VS 2008. Di y l s lp c to cho CSDL mu Northwind v cng s l m hnh c dng trong bi vit ny:

Khi chng ta nh ngha m hnh d liu dng LINQ to SQL designer nh trn, chng ta nh ngha ra 5 lp m hnh: Product, Category, Customer, Order and OrderDetail. Cc thuc tnh ca mi lp nh x vo cc ct tng ng trong bng d liu. Mi i tng thuc lp thc th s biu din mt dng trong bng CSDL.

Khi nh ngha m hnh d liu, LINQ to SQL designer cng to ra mt lp DataContext cung cp cc cch thc truy vn v cp nht li d liu. Trong m hnh mu chng ta nh ngha trn, lp ny c t tn l NorthwindDataContext. Lp NorthwindDataContext c cc thuc tnh biu


36

din cc bng chng ta nh ngha trong CSDL (Products, Categories, Customers, Orders, OrderDetails).

Nh chng ta xem trong phn 3, chng ta cng d dng dng cc biu thc LINQ truy vn v ly d liu t CSDL bng cch dng lp NorthwindDataContext. LINQ to SQL sau s t ng din dch cc biu thc thnh cc cu lnh SQL thch hp thc thi.

V d, chng ta c th vit biu thc LINQ nh di y ly v mt i tng Product n bng cch tm da trn tn sn phm:

Ti cng c th vit thm mt cu truy vn LINQ di y ly v tt c cc sn phm t CSDL m hin ti cha c n t hng, v gi tin nhiu hn $100:

Ch cch ti ang dng OrderDetails kt hp vi mi sn phm nh mt phn ca cu truy vn ch ly v cc sn phm khng c n t hng.

Change Tracking v DataContext.SubmitChanges()

When we perform queries and retrieve objects like the product instances above, LINQ to SQL will

by default keep track of any changes or updates we later make to these objects. We can make any

number of queries and changes we want using a LINQ to SQL DataContext, and these changes will

all be tracked together.

Khi chng ta thc hin cc cu truy vn v ly v cc i tng nh i tng product trn, LINQ to SQL s mc nhin lu li vt ca cc thao tc thay i hay cp nht m chng ta thc hin trn cc i tng (gi l change tracking). Chng ta c th thc hin bao nhiu cu truy vn v thay i m chng ta mun bng cch dng LINQ to SQL DataContext, v tt c cc thay i s c lu vt li.


37

Ghi ch: Vic lu vt LINQ to SQL xy ra bn pha chng trnh gi, v khng lin quan g n CSDL. C ngha l bn khng h dng ti nguyn trn CSDL, hoc bn khng cn ci t thm hay thay i bt k th g trn CSDL cho php lm iu ny.

Sau khi cp nht cc i tng chng ta ly t LINQ to SQL, chng ta c th gi phng thc SubmitChanges() trn lp DataContext cp nht li cc thay i ln CSDL. Vic gi phng thc ny s lm cho LINQ to SQL tnh ton ng v thc thi cc cu lnh SQL ph hp cp nht CSDL.

Ly v d, bn c th vit cu lnh di y cp nht li gi tin v s lng n v cn li ca sn phm Chai:

Khi ti gi northwind.SubmitChanges() nh trn, LINQ to SQL s xy dng v thc thi mt cu lnh SQL UPDATE m n s cp nht li hai thuc tnh ca sn phm m chng ta sa li nh trn.

Ti c th vit on lnh di y duyt qua danh sch cc sn phm t ph bin v gi cao, sau t li thuc tnh ReorderLevel = 0:

Khi ti gi northwind.SubmitChanges() nh trn, LINQ to SQL s tnh ton v thc thi mt tp thch hp cc pht biu UPDATE cp nht cc sn phm c thuc tnh ReorderLevel b thay i.

Hy nh l nu gi tr ca cc thuc tnh ca i tng Product khng b thay i bi cu lnh trn, c ngha l bn thn i tng khng b thay i, th LINQ to SQL cng s khng thc thi bt k cu lnh UPDATE no trn i tng . V d, nu n gi ca i tng Chai l 2 v s san phm cn li l 4, th vic gi SubmitChanges() s chng lm thc thi bt k cu SQL no. Cng vy, ch cc sn phm trong v d th hai c ReorderLevel khng bng 0 mi c cp nht khi gi SubmitChanges().

2. Cc v d Insert v Delete

Ngoi vic cp nht cc dng c trong CSDL, LINQ to SQL cn cho php bn thm v xa d liu. Bn c th lm c iu ny bng vic thm/bt cc i tng d liu t cc tp hp bng


38

trong lp DataContext, v sau gi SubmitChanges(). LINQ to SQL s lu vt li cc thao tc ny, v t ng thc thi cu lnh SQL INSERT hay DELETE ph hp khi phng thc SubmitChanges() c gi.

a. Thm mt sn phm

Bn c th thm mt sn phm mi vo CSDL bng vic to ra mt i tng thuc lp Product, gn cc gi tr thuc tnh, v sau thm n vo tp hp Products ca DataContext:

Khi gi SubmitChanges nh trn, mt dng mi s c thm vo bng Product.

b. Xa cc sn phm

Cng nh ti ni v vic thm mt sn phm mi bng cch i tng Product vo tp hp Products ca DataContext, ti cng c th lm mt cch ngc li khi mun xa mt sn phm t CSDL bng cch xa n khi tp hp ny:

(RemoveAll c thay i bng DeleteOnSubmit trong phin bn hin ti)

Ch cch ti ly mt tp hp cc sn phm khng cn c sn xut v cng khng c n t hng no bng cch dng mt cu truy vn LINQ, ri sau truyn n cho phng thc RemoveAll ca tp hp Products trong DataContext. Khi gi SubmitChanges(), tt c cc sn phm s b xa khi CSDL.

3. Cp nht thng qua cc quan h

iu lm cho cc trnh ORM nh LINQ to SQL cc k mm d l n cho php chng ta d dng m hnh ha mi quan h gia cc bng trong m hnh d liu. V d, ti c th m hnh ha


39

mi Product trong mt Category, mi Order cha cc OrderDetails cho tng mc, kt hp cc OrderDetail vi mt Product, v lm cho mi Customer kt hp vi mt tp cc Order. Ti biu din cch xy dng v m hnh ha cc mi quan h trong phn 2 ca lot bi ny.

LINQ to SQL cho php ti tn dng c u im ca cc mi quan h trong vic truy vn v cp nht d liu. V d, ti c th vit on lnh di y to mt Product mi v kt hp n vi mt category Beverages trong CSDL nh di y:


Hy ch cch ti thm mt i tng Product vo tp hp Products ca mt Category. N s ch ra rng c mt mi quan h gia hai i tng, v lm cho LINQ to SQL t ng duy tr mi quan h foreign-key/primary key gia c hai khi ti gi SubmitChanges.

Mt v d khc cho thy LINQ to SQL c th gip qun l quan h gia cc bng nh th no v gip cho vic lp trnh sng sa hn, hy xem mt v d di y khi ti to mt Order mi cho mt khch hng c. Sau khi t gi tr cho ngy chuyn hng v chi ph cho vic t hng, ti s to tip 2 mc chi tit trong n t hng ch n cc sn phm m khch hng ang mun mua. Sau , ti s kt hp n t hng vi khch hng, v cp nht cc thay i vo CSDL.


40


Nh bn thy, m hnh lp trnh trn cho php thc hin tt c cc cng vic ny mt cch cc k sng sa theo phong cch hng i tng.

4. Transactions

Mt transaction (giao dch) l mt dch v c cung cp bi mt CSDL (hoc mt trnh qun l ti nguyn khc) m bo rng mt tp cc thao tc c lp s c thc thi nh mt n v duy nht c ngha l hoc tt c cng thnh cng, hoc cng tht bi. V trong trng hp tht bi, tt c cc thao tc l lm s b hon tc trc khi bt k thao tc no khc c cho php thc hin.

Khi gi SubmitChanges() trn lp DataContext, cc lnh cp nht s lun c thc thi trong cng mt transaction. C ngha l CSDL ca bn s khng bao gi trong mt trng thi khng ton vn nu bn thc thi nhiu cu lnh hoc tt c cc thao tc bn lm s c lu li, hoc khng c bt k thay i no.

Nu khng c mt transaction ang din ra, DataContext ca LINQ to SQL s t ng bt u mt transaction bo v cc thao tc cp nht khi gi SubmitChanges(). Thm vo , LINQ to SQL cn cho php bn t nh ngha v dng i tng TransactionScope ca ring bn. iu ny lm cho vic tch hp cc lnh LINQ to SQL vo cc on m truy cp d liu c d dng hn. N cng c ngha l bn c th a c cc ti nguyn khng phi ca CSDL vo trong cng transaction. V d: bn c th gi i mt thng ip MSMQ, cp nht h thng file (s dng kh nng h tr transaction cho h thng file), v nhm tt c cc thao tc vo trong cng mt transaction m bn dng cp nht CSDL dng LINQ to SQL.


41

5. Kim tra d liu v Business Logic

Mt trong nhng iu quan trng m cc nh pht trin cn ngh n khi lm vic vi d liu l lm sao kt hp c cc php xc thc d liu v cc quy tc chng trnh (business logic). LINQ to SQL cng h tr nhiu cch cc nh pht trin c th d dng tch hp chng vo vi cc m hnh d liu ca h.

LINQ to SQL cho php bn thm kh nng xc thc d liu m khng ph thuc vo cch bn to ra m hnh d liu cng nh ngun d liu. iu ny cho php bn c th lp li cc php kim tra nhiu ch khc nhau, v lm cho m lnh sng sa v d bo tr hn rt nhiu.

6. H tr kim tra cc gi tr thuc tnh da trn schema ca CSDL

Khi nh ngha cc lp m hnh d liu dng LINQ to SQL designer trong VS 2008, chng s mc nhin c gn cc quy tc xc thc da trn cu trc nh ngha trong CSDL.

Kiu d liu ca thuc tnh trong cc lp m hnh d liu s khp vi cc kiu d liu tng ng trong CSDL. iu ny c ngha l bn s gp li bin dch nu c gng gn mt gi tr kiu boolean v cho mt thuc tnh decimal, hoc nu th p kiu d liu mt cch khng hp l.

Nu mt ct trong CSDL c nh du cho php mang gi tr NULL, khi thuc tnh tng ng trong m hnh d liu c to bi LINQ to SQL designer cng cho php NULL. Cc ct khng cho php NULL s t ng a ra cc exception nu bn c gng lu mt i tng c thuc tnh mang gi tr NULL. LINQ to SQL s m bo cc ct nh danh/duy nht khng b trng lp trong CSDL.

Bn c th dng LINQ to SQL designer ghi ln cc quy tc xc thc da trn schema nu mun, nhng cc quy tc ny s c to ra t ng v bn khng cn lm bt k iu g cho php chng. LINQ to SQL cng t ng x l cc chui escape, do vy bn khng cn lo lng v li SQL injection.

7. H tr ty bin vic kim tra gi tr cc thuc tnh

Vic kim tra d liu da trn cu trc nh ngha trong CSDL rt hu ch, nhng ch c coi nh mc c bn, trong thc t c th bn s gp phi nhng yu cu kim tra phc tp hn nhiu.

Hy xem mt v d trong CSDL Northwind, khi ti nh ngha thuc tnh Phone thuc lp Customer c kiu d liu l nvarchar. Cc nh pht trin dng LINQ to SQL c th vit code ging nh di y cp nht n vi mt s phone hp l:

Vn l on code trn c coi l hp l ng t gc kiu d liu SQL, v chui trn vn l mt chui nvarchar mc d c th n khng phi l mt s phone hp l:


42

trnh vic thm cc s phone kiu nh trn vo CSDL, chng ta c th thm mt quy tc kim tra tnh hp l vo lp Customer. Thm mt quy tc kim tra thc s n gin. Tt c nhng g chng ta cn lm l thm mt partial class vo v nh ngha phng thc nh di y:

on code trn tn dng u im ca 2 c tnh trong LINQ to SQL:

1) Tt c cc lp c to ra u l partial c ngha l nh pht trin c th d dng thm vo cc phng thc, thuc tnh v thm ch c cc s kin (v t chng trong mt file ring bit). iu ny lm cho vic thm cc quy tc xc thc v cc hm ph tr vo m hnh d liu v lp DataContext rt d dng. Bn khng cn cu hnh hay vit thm cc code no khc lm c iu ny.

2) LINQ to SQL to sn mt lot cc im m rng trong m hnh d liu v lp DataContext m bn c th dng thm vo cc php kim tra d liu trc v sau khi thc hin cc cng vic. Nhiu trong s ng dng mt c tnh ngn ng mi c gi l partial method c trong VB v C# c trong VS 2008 beta 2. Wes Dyer trong nhm C# c mt bi ni v cch cc partial method lm vic ti y.

Trong v d v vic kim tra tnh hp l d liu trn, ti dng phng thc OnPhoneChanging, y l mt phng thc s c thc thi bt k lc no ngi dng gn li gi tr cho thuc tnh Phone trn mt i tng Customer. Ti c th dng phng thc ny xc thc gi tr u vo theo bt k cch g ti mun (trong v d ny, ti dng mt biu thc chnh quy). Nu gi tr hp l, ti ch n gin return v khng lm g c, khi LINQ to SQL s cho l cc gi tr ny l gi tr hp l, ngc li ti c th pht ra mt Exception bn trong phng thc kim tra, v php gn khi s khng c thc hin.

8. H tr ty bin vic kim tra tnh hp l ca thc th

Vic kim tra trn tng thuc tnh nh trong cc v d trn rt hu dng khi bn mun kim tra gi tr ca tn thuc tnh ring l. Nhng i khi, bn s cn phi kim tra da trn nhiu gi tr ca cc thuc tnh khc nhau.


43

Hy xem v d sau, ti s t gi tr cho 2 thuc tnh OrderDate v RequiredDate:


on lnh trn l hp l nu ch n thun xt t gc ngn ng nhng s l khng c ngha khi bn li mun t ngy khch hng yu cu trc ngy t hng.

Tin vui l t bn LINQ to SQL beta 2, chng ta c th thm vo cc quy tc kim tra cho tng thc th trnh cc li kiu nh trn bng cch thm mt lp partial cho lp Order v hin thc ha hm OnValidate(), hm ny s c gi trc khi d liu c a vo CSDL. Bn trong phng thc ny, chng ta c th truy cp v kim tra tt c cc thuc tnh ca lp trong m hnh d liu.

Bn trong phng thc ny, bn c th kim tra gi tr bt k thuc tnh no, v thm ch c th truy cp (ch c) vo cc i tng lin quan, v c th pht ra mt exception nu c tn ti cc gi tr khng hp l. Bt k mt exception no c pht ra t phng thc OnValidate() s lm cho vic cp nht b hy b, v hy b cc thay i trong transaction.

9. Ty bin cc phng thc kim tra vic thm/xa/sa d liu

C nhiu lc bn mun thm cc php kim tra khi thm/xa/sa d liu. LINQ to SQL Beta2 cho php lm iu ny bng cch cho php bn thm vo mt lp partial m rng lp DataContext v sau hin thc ha cc phng thc ty bin cc thao tc thm/xa/sa cho cc thc th. Cc thc ny s c thc thi t ng khi bn gi SubmitChanges() trn lp DataContext.


44

Bn c th thm cc php kim tra thch hp vo bn trong cc phng thc v nu d liu hp l, LINQ to SQL s tip tc lu li cc thay i vo CSDL (bng cch gi phng thc ExecuteDynamicXYZ ca DataContext).

Mt trong nhng iu th v l cc phng thc ph hp s c gi t ng, khng ph thuc vo ng cnh m i tng c to/xa/sa. Hy xem v d sau, y ti mun to mt Order mi v kt hp n vi mt Customer c:


Khi ti gi northwind.SubmitChanges() trn, LINQ to SQL s xc nh l n cn lu li mt i tng Order, v phng thc InsertOrder s t ng c gi.

10. Nng cao: Xem danh sch thay i cho Transaction

i khi bn mun thm cc quy tc kim tra m khng th ch da trn tng thao tc thm/xa/sa ring l, thay vo bn phi c th duyt qua ton b cc thao tc thc hin trong transaction.


45

Bt u t bn Beta2 ca .NET 3.5, LINQ to SQL cho php bn truy cp vo danh sch ny bng cch gi phng thc DataContext.GetChangeList(). N s tr v mt i tng ChangeList cha cc tp hp cho cc thao tc thm/xa/sa c thc hin.

Mt cch tip cn l bn c th to mt lp tha k t lp DataContext v override phng thc SubmitChanges(). Khi bn c th ly ChangeList() cho thao tc cp nht v thc hin cc php kim tra cn thit trc khi thc thi:

11. X l cc thay i ng thi vi Optimistic Concurrency:

Mt trong nhng vn m cc nh pht trin phi ngh n trong mi trng a ngi dng l lm th no c th x l cc thao tc cp nht trn cc cng mt tp d liu. V d, cho l c hai ngi dng ang cng ly v mt i tng product bn trong mt ng dng, v mt ngi t li gi tr cho ReorderLevel l 0, trong khi ngi kia t li l 1. Nu c hai ngi dng u lu li cc thay i vo CSDL, nh pht trin cn cn nhc vic x l tranh chp d liu.

Mt cch tip cn n gin l let the last writer win (ngi cui cng l ngi chin thng) c ngha l nhng thay i bi ngi u tin s b thay i m khng bit. V iu ny thng c coi l mt tri nghim km ci (v khng ng) c ngha ngi dng s cm thy kh s dng.

Mt cch tip cn khc m LINQ to SQL h tr l dng m hnh optimistic concurrency khi LINQ to SQL s t ng xc nh xem gi tr gc trong CSDL b thay i b ngi dng khc hay cha. LINQ to SQL sau s cung cp mt danh sch cc gi tr b xung t ngi pht trin c th chn gii php x l hoc c th yu cu ngi dng chn mt thao tc no h mun.

Ti s ni v cch dng optimistic concurrency vi LINQ to SQL trong cc bi vit khc.

12. Dng SPROCs hoc ty bin logic cc cu SQL:

Mt trong nhng cu hi m cc nh pht trin (v c bit l cc DBA cc nh qun tr CSDL), nhng ngi tng vit cc th tc (SPROC) vi cc cu SQL ty bin thng hi khi nhn thy LINQ to SQL ln u tin l: lm sao ti c th kim sot hon ton cc cu lnh SQL c thc thi bn di ?


46

Mt tin tt l LINQ to SQL c mt m hnh cc k mm do, n cho php cc nh pht trin c th thay th cc cu lnh caLINQ to SQL bng cc th tc insert, update, delete m h t nh ngha.

iu thc s th v l bn c th bt u bng cch nh ngha m hnh d liu ca ring bn v LINQ to SQL t thc hin cc thao tc thm/sa/xa. Ri sau bn c th ty bin li m hnh d liu thc hin cc thao tc cp nht vi cc th tc hoc cc cu SQL ca bn m khng phi thay i bt k on lnh no dng m hnh d liu , v cng chng phi thay i bt k quy tc kim tra to trc . iu ny cung cp kh nng ty bin rt ln cho bn khi xy dng ng dng.

Ti cng s ni k hn v cch ty bin m hnh d liu dng cc th tc hay cu lnh SQL trong mt bi vit khc.


47

Bi 5: S dng asp:LinqDataSource

Trong bi vit ny, ti s khm ph control mi c trong ASP.NET thuc phin bn .NET 3.5. Control ny l mt datasource control mi cho ASP.NET (ging ObjectDataSource v SQLDataSource c trong ASP.NET 2.0) cho php bn khai bo vic gn kt d liu vo m hnh d liu ca LINQ to SQL cc k d dng.

1. ng dng mu m chng ta s xy dng:

Chng trnh web chnh sa d liu n gin m ti s xy dng qua cc bc c m t trong bi ny s l mt chng trnh cho php nhp/chnh sa d liu cho cc sn phm trong mt CSDL:

Chng trnh s h tr ngi dng cc tnh nng sau:

1. Cho php ngi dng lc sn phm theo phn loi. 2. Cho php ngi dng sp xp cc sn phm bng cch nhp chut ln tiu ct (Name, Price, Units

In Stock, ). 3. Cho php ngi dng phn trang cc sn phm (10 sn phm mi trang). 4. Cho php ngi dng chnh sa v cp nht cc chi tit sn phm ngay trn trang. 5. Cho php ngi dng xa cc sn phm trong danh sch.

ng dng web ny s c xy dng vi mt m hnh d liu hng i tng dng LINQ to SQL.


48

Tt c cc quy tc x l v kim tra d liu s c xy dng trong lp d liu m khng phi trong lp giao din. iu ny s m bo rng: 1) mt tp cc quy tc x l ng nht s c dng tt c mi ch trong ng dng, 2) chng ta s phi vit t code m khng cn lp li, v 3) c th d dng chnh sa/thay i cc quy tc x l sau ny m khng cn cp nht li chng nhiu ch khc nhau trong ng dng.

Chng ta cng s tn dng c u im ca vic phn trang/sp xp bn trong LINQ to SQL m bo rng cc c tnh khng c thc hin bn trong lp gia (middle-tier), m s c thc hin trong CSDL (c ngha l ch c 10 sn phm c ly ra trong CSDL ti mt thi im, chng ta s khng ly hng ngn dng ri mi thc hin phn trang hay sp xp trn web server).

2. l g v n gip g cho chng ta?

Control l mt ASP.NET control hin thc ha m hnh DataSourceControl c gii thiu trong ASP.NET 2.0. N tng t nh cc control ObjectDataSource v SqlDataSource, bn c th dng n khai bo vic gn ni d liu gia mt control ASP.NET vi mt ngun d liu. im khc bit l thay v n gn ni trc tip vo CSDL (nh SqlDataSource) hay vo mt lp (ObjectDataSource), c thit k gn vo mt m hnh d liu LINQ.

Mt trong nhng u im ca vic dng l n tn dng c tnh mm do ca cc trnh cung cp LINQ (LINQ provider: nh LINQ to SQL, LINQ to Object). Bn khng cn nh ngha cc phng thc query/insert/update/delete cho ngun d liu gi, thay vo bn c th tr n m hnh d liu ca bn, ch ra bn thc th no bn mun lm vic, ri gn ni n vo mt control Asp.NET v cho php chng lm vic vi nhau.

V d, xy dng mt danh sch c bn cc sn phm cho php lm vic vi cc thc th Product trong m hnh d liu LINQ to SQL, ti c th khai bo mt th trn trang v tr vo lp datacontext ca LINQ to SQL, v ch ra cc thc th (v d: Products) trong m hnh LINQ to SQL m ti mun gn ni. Ti c th cho mt GridView tr vo n (bng cch t thuc tnh DataSourceID) cho php xem cc Product theo dng li:

Khng cn lm thm bt k iu g, ti c th thc thi trang web v c mt danh sch cc Product vi kh nng phn trang cng nh sp xp c tch hp sn. Ti cng c th thm mt nt edit/delete v cho php ngi dng chnh sa d liu. Ti khng cn thm bt k phng thc, nh x cc tham s, hay thm ch vit bt k cu lnh no cho x l cc trng hp hin th v cp nht trn n c th lm vic vi m hnh LINQ to SQL m chng ta ch n v thc hin cc thao tc t ng. Khi cp nht, LINQ to SQL s m bo rng cc quy tc x l v kim tra d liu m ta thm vo m hnh LINQ to SQL (di dng cc phng thc partial) cng s c thc hin trc khi d liu c thc s cp nht vo CSDL.


49

Quan trng: Mt trong nhng im hay ca LINQ hay LINQ to SQL l n khng c thit k ch lm vic vi lp giao din, hay vi mt control c th no nh LinqDataSource. Nh bn thy trong cc bi vit trc ca cng lot bi ny, vit code dng LINQ to SQL cc k sng sa. Bn lun c th vit thm cc m lnh ty bin lm vic trc tip vi m hnh d liu LINQ to SQL nu mun, hay trong mt ng cnh no m khng ph hp dng.

Cc phn di y s m t tng bc to nn ng dng web ti ni trn bng cch dng LINQ to SQL v .

Bc 1: nh ngha m hnh d liu

Chng ta s bt u vic to ra ng dng bng cch nh ngha m hnh d liu biu din CSDL.

Ti ni v cch to mt m hnh d liu LINQ to SQL dng trnh son tho c trong VS 2008 trong bi 2. Di y l nh chp mn hnh cc lp d liu m ti c th nhanh chng to ra dng LINQ to SQl designer m hnh ha CSDL Northwind:

Ti s duyt li m hnh ny trong bc 5 khi ti thm mt s quy tc kim tra, nhng trong giai on u, ti s vn dng n (cha chnh sa) to giao din.


50

Bc 2: To danh sch sn phm

Chng ta s bt u phn giao din bng cch to mt trang ASP.NET vi mt control v dng CSS nh dng:

Chng ta c th vit code gn ni m hnh d liu vo gridview ny (ging nh ti lm trong phn 3), hoc ti c th lm cch khc l dng control mi gn ni gidview ny vi m hnh d liu.

VS 2008 includes build-in designer support to make it easy to connect up our GridView (or any other

ASP.NET server control) to LINQ data. To bind our grid above to the data model we created

earlier, we can switch into design-view, select the GridView, and then select the New Data Source option within the Choose Data Source: drop-down:

Trnh thit k trong VS 2008 c sn kh nng h tr lm iu ny mt cch d dng vi GridView (hay bt k control ASP.NET no khc) vo d liu LINQ. gn ni, chng ta c th chuyn sang ch thit k, chn GridView, v sau chn New Data Source bn trong dang sch Choose Data Source::

Mt hp thoi s hin ln, trong c danh sch cc loi datasource, chn LINQ trong hp thoi ny v t trn cho control m bn mun to:


51

Trnh thit k s hin th tip cc lp DataContext ca LINQ to SQL m ng dng ca bn c th dng c bao gm c trong cc th vin m bn ang tham chiu ti):

Chng ta mun chn m hnh d liu c to trc y vi trnh thit k LINQ to SQL. Chng ta cng s mun chn bng d liu bn trong m hnh d liu m chng ta s coi nh thc th chnh


52

lm vic vi . Trong v d ny chng ta s chn thc th Products. Chng ta cng s nhn vo nt Advanced v cho php vic cp nht cng nh xa d liu:

Khi nhn vo nt Finish trn, VS 2008 s khai bo mt trong trang .aspx, v cp nht tr n n (thng qua thuc tnh DataSourceID).N cng s t ng to ra cc ct trong Grid d trn cu trc ca thc th Products m chng ta chn:

Chng ta cng c th nhp vo hnh tam gic nh bt ln smart task ca GridView v ch ra chng ta mun cho php vic phn trang, sp xp, chnh sa v xa d liu:


53

Chng ta c th nhn F5 thc thi, v c mt trang hin th danh sch sn phm vi y kh nng phn trang cng nh sp xp cc ct:

Chng ta cng c th nhn v cc nt edit hoc delete cp nht li d liu:


54

Nu nhn vo m ngun ca trang, chng ta s thy cc th ca trang cha ni dung ging nh di y. Th ch n lp DataContext ca LINQ to SQL m ta to trc y, cng nh bng d liu m chng ta mun dng. GridView sau ch n (thng qua DataSourceID) v ch ra nhng ct no s c hin th, tiu ct, cng nh cch sp xp s c dng khi tiu ct c chn.

Gi chng ta c mt trang web c bn lm vic vi m hnh d liu LINQ to SQL, chng ta c th tip tc ty bin giao din v hnh vi.

Bc 3: B cc ct khng cn thit

GridView ca chng ta trn c rt nhiu ct c nh ngha sn, 2 trong s (SupplierID v CategoryID) l cc ct kha ngoi, v vic hin th cc ct ny c v nh khng phi l mt tng hay.

Xa bt cc ct khng cn thit

Chng ta c th bt u vic dn dp giao din bng cch xa i mt s ct khng cn thit. Ti c th lm iu ny bng cch sa m ngun, hay trong ch thit k (nhp chut ln ct mun xa v chn Remove). V d, bn c th b ct Quantity Per Unit di y v chy li ng dng ca chng ta c mt giao din sng sa hn:


55

Nu bn tng dng control trc y v truyn cc tham s cho cc phng thc cp nht, mt trong nhng th khn kh bn bit c l l vic thay i tham s ca cc phng thc cp nht trong TableAdapter khi cc thng s c nhn t lp giao din b thay i. V d: nu chng ta xa mt ct trong bng trn, chng ta li phi chnh sa li TableAdapter n h tr cc phng thc cp nht khng cn ti tham s b xa .

Mt iu hay l vi bn khng cn thc hin cc thay i kiu nh vy. Ch n gin l thm hoc xa mt ct v chy li chng trnh khng cn lm thm bt c iu g khc. iu ny lm cho vic thay i giao din web dng d hn nhiu.

Xa cc ct SupplierID v CategoryID

Hin ti, chng ta hin th cc gi tr kha ngoi n cc bng Supplier v Category trong GridView:

iu ny tuy cn thit ng t gc m hnh d liu, nhng li khng mang li gi tr g cho ngi dng. Th m chng ta lm l hin th CategoryName v SupplierName, v cung cp mt dang sch x xung trong ch Edit cho php ngi dng d dng chn cc gi tr cho SupplierID v CategoryID.

Ti c th thay i GridView hin th Supplier Name v Category Name thay v ID bng vic thay th vi mt . Trong TemplateField ny, ti c th thm bt k ni dung no ti mun ty bin li cch hin th ca ct d liu.


56

Trong on m di y, ti s tn dng cc thuc tnh Supplier v Category trn mi Product, nh ti c th d dng gn ni cc ct Supplier.CompanyName v Category.CategoryName v cc ct tng ng trong Grid.

V by gi khi chy ng dng, ti s c danh sch cc Category v Supplier theo tn:

to ra danh sch cho php ngi dng chn cc gi tr ca cc ct Supplier v Category trong ch Edit, u tin ti s thm hai control na vo trang. Ti s cu hnh chng gn ni vi Categories v Suppliers bn trong m hnh d liu LINQ to SQL m ta to trc y:


57

Ti c th quay tr li cc ct m chng ta to v ty bin giao din Edit ca chng (bng cch ch ra EditItemTemplate). Chng ta cng s ty bin mi ct c mt danh sch trong ch Edit, v cc gi tr s c ly t cc datasource CategoryDataSource v SupplierDataSource trn, v cc mt lin h ny s l 2 chiu:

V gi, khi ngi dng nhp chut ln Edit trn GridView, chng s c hin th nh mt danh sch Supplier m sn phm ang chn kt hp:

V khi bn bm nt Save, sn phm s c cp nht mt cch ph hp (GridView s dng gi tr ca dng c chn hin ti trong DropDownList a vo SupplierID).

Bc 4: Lc danh sch sn phm

Thay v hin th tt c cc sn phm trong CSDL, bn c th cp nht phn giao din n thm mt danh sch cho php ngi dng lc li cc sn phm theo mt phn loi no .


58

V chng ta thm control tham chiu n Categories vo trang web ny trc y, do vy gi nhng g cn lm ch l to mt dropdownlist trn u trang gn ni vi n. V d:

Khi ti chy trang web ny, ti s c trn u trang mt danh sch cho tt c cc mc phn loi:

Bc cui cng l cu hnh GridView n ch hin th cc sn phm trong phn loi c chn, cch d nht l chn Configure DataSource trong smart task ca GridView:

N s a ti quay tr li ca s thit k m ti dng trong phn u bi vit ny. Ti c th chn nt Where trong ca s ny thm mt b lc vo control datasource. Ti c th to ra nhiu b lc nu cn, v ko cc gi tr lc t mt vi ch khc nhau (v d: t querystring (trn web), t cc gi tr trn form, t cc control khc trn trang):


59

trn, ti s to b lc cc Products theo CategoryID, v sau ly gi tr ca CategoryID mun lc t danh sch m chng ta to trn trang:


60

Sau khi bm Finish, control trn trang ca chng ta s c cp nht s dng b lc ging nh sau:

V by gi nu thc thi trang web, ngi dng s c th chn mt trong cc phn loi c sn v sau phn trang, sp xp, chnh sa hay xa cc sn phm trong phn loi :

Control s t ng p dng cc b lc LINQ cn thit khi lm vic vi cc lp LINQ to SQL ca chng ta m bo rng ch c cc d liu cn thit c ly v t CSDL (v d: trong Grid trn, ch c 3 dng sn phm t trang th hai trong nhm cc sn phm Confection c ly v).

Bn c th s dng s kin Selecting trn nu mun ty bin cu truy vn LINQ trong on code.

Bc 5: Thm cc quy tc kim tra logic

Nh ti ni n trong phn 4 ca lot bi LINQ to SQL ny, khi chng ta nh ngha m hnh d liu LINQ to SQL, mc nhin chng ta s t ng c mt tp hp cc rng buc trong cc lp m hnh d liu, cc rng buc ny c sinh ra da trn nh ngha trong CSDL. iu ny c ngha l nu bn th nhp mt gi tr null vo cho mt ct mandatory, gn mt string vo cho mt ct s nguyn, hay t gi tr cho kha ngoi cho mt dng khng tn ti, m hnh LINQ to SQL ca chng ta s pht ra mt li v nh vy CSDL c ton vn.

Vic kim tra theo cch ny ch nhm m bo s ton vn mc c bn, d vy, n vn cho hu ht cc ng dng trong thc t. Chng ta cng c th mong mun thm vo cc quy tc logic mt mc cao hn, cho php kim tra cc quy tc business vo trong cc lp m hnh d liu. Xin cm n LINQ to SQL cho php lm iu ny tht d dng ( xem chi tit, xin c li phn 4).

Mt v d v cc quy tc logic

Ly v d, ngoi nhng quy tc logic c bn, chng ta cn mun m bo rng ngi dng s khng th ngng bn mt loi sn phm nu vn cn sn phm loi trong kho hng.


61

Nu mt ngi dng nhn nt Save dng trn, chng ta s khng cho php vic thay i c lu li v pht ra mt li bo cho ngi dng.

Thm mt quy tc kim tra m hnh d liu

Nu kim tra cc quy tc ny lp giao din th s l khng ph hp, v khi quy tc ny s ch c p dng cho chnh ni , v s khng t ng c p dng nu chng ta thm mt trang khc cng cho php cp nht Product vo ng dng. Vic phn tn cc quy tc kim tra logic/business vo lp giao din s lm cho vic bo tr tr nn kh khn khi ng dng tr nn ln v phc tp, v cc thay i/cp nht u cn p dng cc thao tc cn thit nhiu ch khc nhau.

Ni c coi l ph hp t cc quy tc kim tra ny l trong cc lp m hnh d liu LINQ to SQL m chng ta nh ngha trc y. Nh cp n trong phn 4, tt c cc lp c sinh ra bi LINQ to SQL designer u c nh ngha nh cc lp partial n cho php chng ta c th d dng thm vo cc phng thc/s kin/thuc tnh. Cc lp m hnh d liu LINQ to SQL s t ng gi cc phng thc kim tra m chng ta c th vit ra thc hin vic kim tra theo mong mun ring.

V d, ti c th thm mt lp partial vo ng dng hin thc phng thc OnValidate() m LINQ to SQL s gi trc khi lu mt i tng Product vo CSDL. Bn trong phng thc ny ti c th thm quy tc sau m bo rng cc sn phm khng th c mt ReOrder Level nu sn phm ngng bn:

Mt khi thm lp trn vo d n, quy tc business trn s c p dng bt k lc no ngi dng dng n m hnh d liu v chnh sa li CSDL. iu ny c p dng cho c vic thm mt sn phm mi, cng nh cp nht li mt sn phm c.

V m chng ta nh ngha trn lm vic vi cc lp m hnh d liu LINQ to SQL, do vy cc thao tc cp nht/xa/thm u phi qua c php kim tra trn trc khi c p dng vo CSDL. Chng ta khng cn lm thm bt k iu gi lp UI php kim tra


62

ny c thc hin n s t ng c dng bt k ni no cng nh bt k lc no m hnh LINQ to SQL c dng.

Thm phn kim sot li vo giao din

Mc nhin nu ngi dng nhp vo mt gi tr khng hp l cho UnitsOnOrder/Discontinued vo GridView, cc lp LINQ to SQL ca chng ta s sinh ra mt exception. n lt s bt li ny v cung cp mt s kin m ngi s dng c th dng x l li . Nu khng c trnh x l li no c cung cp, khi GridView (hoc mt control khc) gn ni vo s bt li ny v cung cp mt event ngi dng c th x l n. Nu li tip tc khng c ai x l li, khi n s c chuyn n cho Page, v chuyn n hm x l Application_Error() trong file Global.asax nu vn khng c trnh x l li. Cc nh pht trin c th chn bt k ch no trong chui x l ny cung cp mt cch tng tc hp l nht n ngi dng cui.

i vi ng dng ca chng ta, ni hp l nht n x l cc li cp nht d liu l bt s kin RowUpdatedtrn GridView. S kin ny s c pht ra mi khi mt lnh cp nht c thc hin trn datasource, v chng ta c th truy cp thng tin chi tit ca exception nu vic cp nht khng thnh cng, sau o hin th thng bo thch hp cho ngi dng.

rng trn ti khng h thm bt k hm kim tra no vo lp giao din. Thay v vy, ti s ly v chui thng bo li ca exception pht ra t phn business logic v hin th n cho ngi dng.

Ch l ti cng ch ra trn l ti mun GridView vn trong ch Edit khi li xy ra bng cch ngi dng s khng b mt i nhng thay i m h to ra, v c th chnh sa cc gi tr h nhp vo v click nt update mt ln na lu li. Chng ta cng c th thm mt control vi ID ErrorMessage bt k ch no m ta mun thng bo li hin ra:

V by gi chng ta s th cp nht Product vi cc gi tr kt hp khng hp l, chng ta s thy mt thng bo li, nh ngi dng s bit cch sa li cho ph hp:


63

Mt trong nhng u im khi lm theo cch trn l ti c th thm hay thay i cc quy tc trong m hnh d liu m khng cn chnh sa li code trong lp giao din c th hin th thng bo ph hp. Cc quy tc xc thc, v thng bo li tng ng, c th c vit mt ch trong lp m hnh d liu v s c p dng ph hp bt k khi no bn dng n.

3. Tng kt

Control cung cp mt cch d dng gn ni bt k control ASP.NET vo mt m hnh d liu LINQ to SQL. N cho php cc control dng hin th giao din c th va ly d liu v t LINQ to SQL, cng nh p dng cc thay i thm/xa/sa vo m hnh d liu.

Trong ng dng trn, chng ta dng LINQ to SQL designer to ra mt m hnh d liu r rng v hng i tng. Chng ta sau thm ba control ASP.NET vo trang (GridView, DropDownList, ErrorMessage Literal), v thm ba control gn ni d liu cho Product, Category, v Supplier.


64

Chng ta sau vit thm 5 dng kim tra d liu trong lp m hnh d liu, v 11 dng trong lp giao din x l li.

Kt qu cui cng l mt ng dng web n gin vi giao din c ty bin cho php ngi dng lc d liu ng theo phn loi, sp xp v phn trang mt cch hiu qu trn danh sch sn phm, chnh sa trc tip thng tin sn phm v cho php lu li cc thay i, v xa cc sn phm t h thng.

Trong bi vit tip theo ca lot bi ny, chng ta s khm ph thm LINQ to SQL bao gm kim sot truy xut ng thi, lazy loading, tha k cc nh x bng, cng nh cch dng cc th tc SQL ty bin.


65

Bi 6: Ly d liu dng Stored Procedure

Trong bi vit hm nay, ti s cho thy cch chng ta c th dng cc stored procedure (SPROCs) v cc hm do ngi dng nh ngha (UDFs) vi m hnh d liu LINQ to SQL. Bi vit ny s tp trung ch yu vo cch dng SPROCs truy vn v ly d liu v t CSDL. Trong bi vit k tip, ti s hin th cch bn c th dng cc SPROCs cp nht, thm, xa d liu t CSDL.

1. Dng SPROC hay khng SPROC? l mt vn .

Cu hi liu nn dng cc cu SQL ng c sinh ra bi trnh ORM hay dng Stored Procedure khi xy dng lp d liu l mt ch khng bao gi kt thc tranh ci gia cc nh pht trin, kin trc s phn mm v cc DBA. Rt nhiu ngi thng minh hn ti nhiu vit v ch ny, v vy ti s khng ni thm v vn ny y na.

LINQ to SQL i cng vi .NET 3.5 rt mm do, v c th c dng to cc lp m hnh d liu, trong cc i tng khng ph thuc vo cu trc CSDL pha di, v c th x l cc php kim tra logic cng nh xc thc tnh hp l ca d liu m khng ph thuc vo vic d liu s c lu np dng cc cu SQL ng hay thng qua cc SPROCs.

Trong bi Truy vn C s d liu (phn 3), ti tho lun cch bn c th vit cc biu thc truy vn LINQ cho mt m hnh d liu LINQ to SQL dng on m nh sau:

Khi bn vit cc biu thc LINQ kiu nh vy, LINQ to SQL s thc thi cc cu lnh SQL ng bn c th ly v cc i tng khp vi cu truy vn ca bn.

Nh bn c hc trong bi vit ny, bn cng c th dng cc SPROCs trong CSDL trong lp DataContext, n cung cp mt cch khc ly v cc i tng Products bng cch gi th tc tng ng:


66

Kh nng ny cho php bn dng c cc cu SQL ng v cc SPROCs vi mt m hnh d liu r rng, mnh m cng nh cung cp s mm do khi lm vic vi cc d n.

2. Cc bc nh x v gi SPROC dng LINQ to SQL

Trong phn 2, ti ni v cch dng LINQ to SQL designer to ra mt m hnh d liu LINQ to SQL nh di y:


67

ca s trn c cha 2 ca s con, ca s bn tri cho php chng ta nh ngha m hnh d liu s nh x vo CSDL, ca s bn phi cho php nh x cc th tc v hm vo i tng DataContext, iu ny cho php chng ta c th thay th cc cu SQL ng trong vic ly d liu v.

3. Cch nh x mt SPROC vo mt DataContext ca LINQ

nh x mt SPROC vo lp DataContext, trc tin hy m ca s Server Explorer trong VS 2008 v m danh sch cc SPROC trong CSDL:

Bn c th nhy p vo bt k th tc SPROC no trn m v chnh sa chng, v d nh CustOrderHist trong Northwind nh di y:

nh x vo SPROC trn vo DataContext, bn c th ko/th n t ca s Server Explorer ln trn ca s LINQ to SQL designer. Vic ny s lm t ng sinh ra mt th tc trong lp DataContext ca LINQ to SQL nh di y:


68

Mc nhin tn ca phng thc c to trong lp DataContext s chnh l tn ca SPROC, v kiu tr v ca phng thc s l mt kiu c to t ng vi cch t tn theo dng [SprocName]Result. V d: SPROC trn s tr vef mt dy cc i tng c kiu CustOrderHistResult. Chng ta c th i tn ca phng thc nu mun bng cch chn n ri dng Property Grid t li tn khc.

4. Cch gi SPROC mi c to

Khi hon thnh cc bc trn nh x mt SPROC vo lp DataContext ca chng ta, bn c th gi n mt cch d dng ly d liu v. Tt c nhng g chng ta cn lm l gi phng thc m chng ta nh x trong DataContext ly v mt chui cc i tng v t SPROC:

VB:

C#:


69

Thm na, thay v lp qua tp kt qu nh trn, ti cng c th gn ni n vo cho mt control hin th ra mn hnh, v d nh ti c th dng :

Khi danh sch cc sn phm c mua b khch hng s c hin th nh sau:

5. nh x kiu tr v ca phng thc SPROC vo mt lp trong m hnh d liu

Trong th tc CustOrderHist trn, th tc tr v mt danh sch d liu bao gm 2 ct: ProductName cha tn v TotalNumber cha s sn phm c t hng trong qu kh. LINQ to SQL designer s t ng to ra mt lp c tn CustOrderHistResult biu din kt qu ny.


70

Chng ta cng c th chn cch gn kiu tr v ca th tc cho mt lp c sn trong m hnh d liu, v d mt lp thc th Product hay Order.

V d, cho l chng ta c mt th tc tnGetProductsByCategory trong CSDL tr v thng tin sn phm ging nh sau:

Cng nh trc y, ta c th to mt phng thc GetProductsByCategory bn trong lp DataContext m n s gi th tc ny bng cch ko n vo ca s LINQ to SQL designer. Thay v th n vo mt v tr bt k, chng ta s th n ln trn lp Product m ta to ra sn trn sa s ny:

Vic ko mt SPROC v th ln trn mt lp Product s lm cho LINQ to SQL Designer to ra phng thc GetProductsByCategory tr v mt danh sch cc i tng c kiu Product:


71

Mt u im ca vic s dng lp Product nh kiu tr v l LINQ to SQL s t ng qun l cc thay i c to ra trn i tng c tr v ny, ging nh c lm vi cc i tng c tr v thng qua cc cu truy vn LINQ. Khi gi SubmitChanges() trn DataContext, nhng thay i ny cng s c cp nht tr li CSDL.

V d, bn c th vit on code ging nh di y (dng mt SPROC) v thay i gi ca cc sn phm bn trong mt Category no thnh 90% gi tr c:

Khi gi SubmitChanges, n s cp nht li gi ca tt c cc sn phm. hiu thm v cch qun l cc thay i v cch phng thc SubmitChanges() lm vic, cng nh cc thm cc phng thc xc thc logic d liu, xin mi c li bi 4 trong cng lot bi ny.

Trong bi vit tip theo ca lot bi v LINQ to SQL, ti s hng dn cc bn cch thay th cc cu lnh SQL ng cho vic INSERT/UPDATE/DELETE bng cc th tc SPROC. V khi thay th nh vy, bn hon ton khng phi thay i g trn cc on lnh trn vic thay i ny hon ton xy ra trn m hnh d liu v hon ton trong sut vi cc chng trnh dng n.


72

6. X l cc tham s th tc dng OUTPUT

LINQ to SQL nh x cc tham s dng OUTPUT ca cc SPROC thnh cc tham bin (dng t kha ref trong C# hoc ByRef trong VB.NET), v vi cc tham tr, LINQ to SQL dng cc bin kiu nullable (dng ? trong C# hay trong VB.NET).

V d, th tcGetCustomerDetails sau s nhn vo mtCustomerID nh tham s u vo, v tr v tn cng ty nh mt tham s dng OUTPUT v lch s giao dch nh kt qu truy vn:

Nu bn ko th tc trn th vo lp Order trong LINQ to SQL designer, chng ta c th vit lnh nh sau gi n:

VB:

C#:


73

Ch th tc trn va tr v mt tp cc i tng Order, ng thi tr v CompanyName thng qua mt tham s output.

7. X l cc th tc tr v nhiu kiu kt qu khc nhau

Khi mt th tc tr v nhiu kiu kt qu khc nhau, kiu tr v ca phng thc trn lp DataContext khng th c p v mt kiu c th no . V d, th tc di y c th tr v mt tp cc sm phm hay lnh t hng ty thuc vo tham s u vo:

LINQ to SQL h tr vic to cc phng thc tr gip cho php tr v Product hay Order bng cch thm mt lp partial NorthwindDataContext vo d n v nh ngha mt phng thc trong lp ny (trong v d ny chng ta gi l VariablesShapeSample) gi th tc v tr v mt i tng c kiu IMultipleResult nh trong v d sau:

VB:


74

C#:

Mt khi thm phng thc ny vo d n, bn c th gi v chuyn v kiu thch hp l Product hoc Order:

VB:


75

C#:

H tr cc hm do ngi dng t nh ngha (UDF)

Thm vo vic h tr cc cc th tc, LINQ to SQL cn h tr cc hm tr v cc gi tr v hng hoc cc bng kt qu. Mt khi c thm vo lp DataContext nh mt phng thc, bn c th dng cc hm UDF ny trong cu trong cc cu lnh LINQ.

V d, hy xem cc hm UDF n gin c tn MyUpperFunction sau y:


76

Chng ta c th ko v th n t ca s Server Explorer ln ca s LINQ to SQL Designer thm n vo lp DataContext nh mt phng thc.

Chng ta sau c th dng hm UDF ny ngay bn trong cc biu thc LINQ khi vit cc cu truy vn (ging nh chng ta ang dng trong biu thc Where nh di y):

VB:

C#:

Nu bn dng LINQ to SQL Debug Visualizer m ti vit ti y, bn c th thy cc LINQ to SQL chuyn i cu truy vn trn thnh cu lnh SQL thc thi hm UDF khi chy:


77

8. Tng kt

LINQ to SQL supports the ability to call Stored Procedures and UDFs within the database and nicely

integrate them into our data model. In this blog post I demonstrated how you can use SPROCs to

easily retrieve data and populate our data model classes. In my next blog post in this series Ill cover how you can also use SPROCs to override the update/insert/delete logic when you SubmitChanges()

on your DataContext to persist back to the database.

LINQ to SQL h tr kh nng gi cc th tc v hm trong CSDL v c kh nng tch hp d dng vo trong m hnh d liu. Trong bi vit ny ti trnh din cch dng cc th tc SPROC d dng truy xut cng nh cp nht cc lp m hnh d liu. Trong bi k tip ti s biu din cch dng SPROC thc hin vic cp nht/thm/xa khi gi SubmitChanges cp nht li d liu vo CSDL.


78

Bi 7: Cp nht d liu dng Stored Procedure

Trong phn 6 ti ni ti cch chng ta c th dng cc Stored Procedure (SPROC) v cc hm do ngi dng nh ngha (UDF) truy vn v ly d liu v dng m hnh d liu LINQ to SQL. Trong vit ny, ti s ni v cch dng cc th tc ny cp nht, thm hoc xa d liu.

c th minh ha cho iu ny, chng ta hy bt u t u v xy dng mt lp truy xut d liu cho CSDL mu Northwind:

9. Bc 1: To lp truy xut d liu (cha dng n cc th tc)

Trong phn 2, ti c ni v cch dng LINQ to SQL designer c trong VS 2008 to mt m hnh lp ging nh di y:

Thm cc quy tc kim tra d liu vo cc lp m hnh d liu

Sau khi nh ngha cc lp trong m hnh d liu v cc quan h gia chng, chng ta s tip tc thm vo cc quy tc kim tra tnh hp l ca d liu. Chng ta c th lm iu ny bng cch thm cc lp partial vo trong d n v thm cc quy tc kim tra vo cc lp m hnh d liu (ti ni n vn ny kh k trong bi 4).


79

V d, bn c th thm mt quy tc m bo rng s in thoi ca khch hng c nhp ng nh dng, v chng ta khng cho php thm mt n hng (Order) nu trng OrderDate ln hn RequiredDate. Mt khi c nh ngha nh di y, cc phng thc kim tra s t ng c thc thi bt k lc no chng ta cp nht li cc i tng trong h thng.

VB:

C#:


80

Thm phng thc GetCustomer() vo lp DataContext

Hin ti chng ta to cc lp m hnh d liu, v p dng cc phng thc kim tra trn chng, chng ta c th truy vn v tng tc vi d liu. Chng ta c th lm c iu ny bng cch vit cc cu lnh LINQ vi cc lp m hnh d liu truy vn v cp nht CSDL (ti c ni v iu ny trong bi 3). Thm na ti cng c th nh x cc SPROC vo lp DataContext v dng chng a d liu vo CSDL (bi 6).

Khi xy dng cc lp d liu LINQ to SQL, bn s thng c nhu cu a cc cu lnh LINQ thng dng vo cc phng thc tin ch trong lp DataContext. Bn c th lm c iu ny bng cch thm mt lp partial vo project. V d, banks c th thm mt phng thc c tn "GetCustomer()" cho php chng ta tm kim v ly v cc i tng Customer t CSDL da trn da tr ca CustomerID:

VB:


81

C#:

10. Bc 2: Dng lp truy cp d liu (cha s dng SPROC)

Hin ti chng ta c mt lp truy cp d liu (data access layer) biu din m hinh d liu, tch hp cc quy tc v cho php chng ta c th thc hin truy vn, cp nht, thm v xa d liu.

Hy xem mt trng hp n gin l khi chng ta ly v mt i tng khch hng c, cp nht li gi tr ca trng ContactName v PhoneNumber, sau to mi mt i tng Order v kt hp chng vi nhau. CHng ta c th vit on lnh di y lm tt c iu ny trong mt transaction. LINQ to SQL s m bo cc th tc kim tra s c thc thi v cho php trc khi d liu c th c cp nht mt cch thc s:

VB:


82

C#:

LINQ to SQL theo di cc thay i m chng ta to trn cc i tng c ly v t DataContext, v cng theo di c cc i tng m chng ta thm vo. Khi gi SubmitChanges(), LINQ to SQL s kim tra xem d liu c hp l hay khng, v c ng vi cc quy tc logic hay khng, nu ng th cc cu SQL ng s c sinh ra cp nht bn ghi Customer trn, v thm mt bn ghi mi vo bng Orders.

11. Ch mt giy - Ti ngh bi vit ny nh ni v vic dng SPROC c m ???

Nu vn ang c bi ny, bn c l s cm thy kh hiu v khng thy ni g v SPROC. Ti sao ti hng dn bn cch vit lnh lm vic vi cc i tng trong m hnh d liu, ri cho php cc cu lnh SQL ng c thc thi? Sao ti vn cha cho cc bn thy cch gi cc SPROC thc hin vic chm/sa/xa d liu ?


83

L do l v m hnh lp trnh ca LINQ to SQL lm vic vi cc i tng m hnh d liu bng SPROC cng hon ton tng t vi vic s dng cc cu SQL ng. Cch chng ta thm cc quy tc kim tra cng hon ton tng t (do vy cc quy tc m ta thm vo trc y s vn c hiu qu khi chng ta chuyn sang dng SPROC). on lnh trn ly v mt Customer, ri cp nht v thm mt Order s hon ton ging nhau, khng ph thuc vo vic chng ta dng cc cu SQL ng hay cc SPROC thc hin vic truy cp vo CSDL.

M hnh lp trnh ny rt mnh m theo c hai ngha: n khng bt bn phi hc hai cch dng khc nhau, v bn cng khng cn phi quyt nh ngay t u l dung SPROC hay khng. Ban u, bn c th dng cc cu SQL ng c cung cp bi LINQ to SQL cho tt c cc cu truy vn, chn, cp nht v xa d liu. Bn sau c th thm vo cc quy tc kim tra tnh hp l ca d liu, v ri sau na li c th thay i dng cc SPROC - hoc khng ty bn quyt nh. Cc on lnh v cc on test bn vit trc y s vn c s dng tip, khng ph thuc vo vic dng SQL hay SPROC.

Phn tip theo ca bi ny s biu din cch cp nht m hnh d liu m chng ta to ra dng SPROC trong vic thm/sa/xa d liu, chng ta vn tip tc dng cc quy tc xc thc, v vn tip tc lm vic vi cng cc on lnh vit trn.

12. Cch s dng SPROC thc hin Insert/Update/Delete

Chng ta c th sa li lp truy cp d liu c xy dng trc y x l cc thao tc cp nht, thay v dng cc cu SQL ng, theo mt trong 2 cch sau:

1) Dng LINQ to SQL designer cu hnh cc SPROC thc thi khi gp thao tc thm/xa/sa d liu trn cc lp m hnh d liu.

hoc:

2) Thm mt lp partial NorthwindDataContext vo d n, ri vit cc phng thc partial tng ng vi cc thao tc Insert/Update/Delete (v d: InsertOrder, UpdateOrder, DeleteOrder) m n s c gi khi chng ta thc hin Insert/Update/Delete trn cc i tng m hnh d liu. Cc phng thc partial s c truyn vo cc i tng d liu m ta mun cp nht, v chng ta c th thc thi cc th tc hay cu lnh SQL m chng ta mun dng lu i tng vo CSDL.

Khi dng cch 1) (dng LINQ to SQL designer) cu hnh cc SPROC gi, th thc ra n cng s to ra cch lnh tng t nh chng ta dng trong cch 2). Ni chng ti khuyn cc bn dng LINQ to SQL designer cu hnh cc SPROC trong 90% trng hp - v ch trong cc trng hp no bn cn ty bin li cch gi mt mc cao, bn mi nn vit cc lnh mt cch trc tip.

13. Bc 3: Thm mt Order bng cch dng SPROC

Chng ta s bt u chuyn m hnh d liu sang dng SPROC, bt u t i tng Object.

u tin, chng ta n ca s "Server Explorer" m rng nhnh Stored Procedures trong CSDL ca chng ta, v sau nhn phi chut v chn "Add New Stored Procedure":


84

Sau ta to thm mt th tc c tn "InsertOrder" c nhim v chn thm mt bn ghi mi vo bng Orders:

Hy ch cch SPROC nh ngha tham s OrderID nh mt tham s dng OUTPUT. l v ct OrderID trong CSDL l ct t tng mi khi thm mt bn ghi mi vo. Ngi gi s truyn gi tr NULL khi gi n - v th tucjnafy s tr v gi tr ca OrderID mi c to ra (bng cch gi hm SCOPE_IDENTITY() cui th tc).

Sau khi to ra SPROC, chng ta s m LINQ to SQL designer ca lp truy cp d liu. Nh ti ni trong bi 6, chng ta c th ko/th cc SPROC t Server Explorer ln trn mn hnh chnh ca trnh thit k. Chng ta cng s lm iu tng t vi th tc InsertOrder va c to:


85

Bc cui cng l cu hnh li lp truy cp d liu dng th tc SPROC khi chn cc i tng Order mi vo trong CSDL. Chng ta c th l iu ny bng cch chn lp Order trong ca sooe LINQ to SQL designer, v sau chuyn n bng thuc tnh v nhn nt 3 chm (...) mc Insert chn thao tc tng ng:

Khi nhn nt ny, ca s sau s hin ra c th ty bin hnh vi Insert:


86

trn, nu bn chn ch mc nhin ("Use Runtime") th LINQ to SQL s tnh ton v sinh ra cu lnh SQL ng thc hin cc thao tc tng ng. Chng ta c th thay i bng cch nhn chut vo Customize v chn th tc InsertOrder t danh sch cc SPROC:

LINQ to SQL s hin th cc tham s ca th tc m ta chn, v cho php nh x cc thuc tnh ca lp Order v cc tham s ca InsertOrder. Mc nhin, LINQ to cng t ng xc nh cc tham s tng ng theo tn, tuy nhin bn vn c th sa li nu mun.


87

Nhn vo nt Ok l xong. Gi y bt c khi no mt i tng Order c thm vo DataContext v phng thc SubmitChanges() c gi, th tc InsertOrder s c thc thi thay cho cu lnh SQL ng.

Quan trng: Mc d hin ti chng ta dng SPROC cp nht, phng thc "OnValidate" ca Order m chng ta to trc y (trong bc 1 ca bi vit ny) kim tra tnh hp l ca i tng Order s vn c thc thi trc khi bt k thay i no c thc hin. Do vy chng ta s c mt cch r rng x l v kim tra cc quy tc, v c th dng li mt cch d dng m khng ph thuc vo vic chng ta dng SQL ng hay dng SPROC.

14. Bc 4: Thc hin cp nht dng SPROC

Gi chng ta s sa li i tng Customer cho php cp nht bng cch dng SPROC.

Chng ta s bt u bng cch to mt SPROC tn "UpdateCustomer" nh di y:

Ch trn, ngoi vic truyn gi tr cho tham s CustomerID, ti cng truyn mt tham s khc c tn @Original_CustomerID. Ct CustomerID trong bng Customers khng phi l mt ct t tng, v n c th c chnh sa nh mt phn ca thao tc cp nht. Do vy chng ta s phi truyn c gi tr ca CustomerID c v CustomerID mi c th cp nht. Chng ta s xem cch nh x cc ct ngay sau y.

Bn s thy trn ti truyn mt tham s c tn @Version (c kiu timestamp) vo cho SPROC. y l mt ct ti thm vo bng Customers c th x l vic tranh chp khi cc thao tc cp nht c din ra ng thi (optimistic concurrency). Ti s ni chi tit hn v vic x l tranh chp ny trong bi vit sau ca lot bi LINQ to SQL, nhng ti cng ni lun l LINQ to SQL h tr y optimistic concurrency, v cho php bn c th chn dng version timestamp hay bng cch cung cp c gi tr c/mi cho SPROC c th xc nh c cc thay i c to ra bi ngi khc k t ln cui bn c d liu. Trong v d ny ti dng timestamp v n gip vit lnh r rng hn.

Mt khi to xong SPROC, bn c th ko/th n vo ca s LINQ to SQL designer thm n nh mt phng thc trong lp DataContext. Chng ta c th chn lp Customer trong ca s thit


88

k v nhn vo nt ... mc Update dng SPROC va to trong vic cp nht li d liu trong bng Customer:

Chng ta s chn "Customize" v chn dng UpdateCustomer:

Khi nh x cc thuc tnh ca i tng Customer vo cc tham s ca SPROC, bn s c nhc rng bn ang mun gn cc gi tr mi(Current) hay cc gi tr gc (Original) - l cc gi tr m bn ly v ln u t CSDL. V d, bn s cn gn gi tr thuc tnh Customer.CustomerID "mi" vo


89

cho tham s @CustomerID ca SPROC, v Customer.CustomerID "gc" vo cho @original_customerID.

Khi nhn "Ok" trn a s ny, bn hon thnh vic nh x cc tham s vo cc thuc tnh. T gi tr i, mi khi cp nht li gi tr cho i tng Customer v gi SubmitChanges(), th tc UpdateCustomer s c gi thay cho cu lnh SQL ng.

Quan trng: D rng hin ti bn dng SPROC cp nht, phng thc "OnPhoneChanging()" m chng ta to trc (trong bc 1 ca bi ny) xc thc s in thoi vn c thc thi trc khi bt k thay i no c lu li hay "UpdateCustomer" c gi. Chng ta c mt cch r rng, sng sa hin thc ha c quy tc x l cng nh xc thc d liu, v c th dng chng m khng ph thuc v vic chng ta ang dng cu lnh SQL ng hay SPROC.

15. Bc 5: Dng lp DAL ln na

Mt khi cp nht lp truy cp d liu (DAL) dng SPROC thay v cu lnh SQL ng, bn c th chy li cc cu lnh tng t cc cu lnh ta lm bc 2 lm vic vi cc lp m hnh d liu:

Gi y vic cp nht i tng Customer, v vic thm cc i tng Order s c thc thi thng qua th tc to thay v dng cc cu SQL ng. Cc quy tc kim tra cng c thc thi ht nh trc y, v cc cu lnh chng ta dng s dng cc lp m hnh d liu cng hon ton tng t.

16. Mt s u im ca vic dng SPROC

Sau y l mt vi nh c th c ch cho bn trong vic dng SPROC:

Dng cc tham s dng output:

Trong phn 3 trn, ti biu din cch chng ta c th tr v gi tr OrderID mi c to (y l mt ct t tng trong CSDL) bng cch dng mt tham s dng output. Bn s khng b gii hn trong vic tr v ch cc ct t tng - m tht s bn c th trarveef cc gi tr cho bt k tham s no


90

ca SPROC. Bn c th dng cch tip cn ny cho c trng hp Insert v Update. LINQ to SQL c th ly gi tr tr v v dng n cp nht gi tr ca cc thuc tnh ca cc i tng trong m hnh d liu m khng cn thc thi thm mt cu truy vn th 2 ly cc gi tr c to ra.

S th no nu mt SPROC pht ra mt li?

Nu mt SPROC pht ra mt li khi thc hin vic Insert/Update/Delete, LINQ to SQL s t ng hy v rollback ton b cc thay i to ra trong transaction kt hp vi li gi SubmitChanges(). iu ny m bo rng d liu ca bn s lun trong trng thi ng n.

Ti c th vit code thay v dung ORM designer gi SPROC?

Nh ni trong phn u bi vit ny, bn c th dng LINQ to SQL designer nh x cc thao tc thm/sa/xa vo cc SPROC, hoc bn cng c th thm cc phng thc partial vo lp DataContext v vit lnh gi chng. y l mt v d v cch vit cc phng thc trong lp partial ca NorthwindDataContext dng UpdateCustomer gi mt th tc:

on lnh trn thc ra chnh l ci c to ra khi bn dng LINQ to SQL designer nh x SPROC v kt hp n vi thao tc cp nht i tng Customer. Bn

LINQ Tieng Viet

Documents

Transcript of LINQ Tieng Viet