a brief introduction to ado.net web forms
Post on 02-Jun-2018
219 Views
Preview:
TRANSCRIPT
-
8/10/2019 A Brief Introduction to ADO.NET Web Forms
1/40
1
.NET Web Forms
ADO .NET Introduction
K
http://www.apextgi.in/http://www.apextgi.in/http://www.apextgi.in/ -
8/10/2019 A Brief Introduction to ADO.NET Web Forms
2/40
2
ADO .NET Overview
ASP .NET CodeDatabase
Web Form
Data Controls
Data Connector(Establishes connection and
opens the database.)
Data Adapter(Translates commands and
exchanges data.)
SQL
Dataa
nd
Errors
Data Set (Collection of tables and relationships.)
CustomerID
LastName
FirstName
Data Table Data !ie" (Based on 1 table.)
O#DE# $% LastName
W&E#E '
Data #elations(ip
(Associations among tables.)
Fill
Data $ind
)a*or +uestion, W(at
is missin-Hint: it is
missing in AD .!ET"
not #$st the diagram.
Ans%er: sa&e data
-
8/10/2019 A Brief Introduction to ADO.NET Web Forms
3/40
3
Sample Project: Simple2
%ou s(ould create a ne" sample pro*ect to test /our s0ills.
T(e completed 1ersion s(ould loo0 somet(in- li0e, Simple2
-
8/10/2019 A Brief Introduction to ADO.NET Web Forms
4/40
4
Data Connector T(e Data Connector is usuall/ strai-(t3or"ard.
T(e "i4ard starts automaticall/ "(en /ou add a Data Adapter.
C(oose t(e t/pe o3 database 5t(at pic0s t(e pro1ider and t(e speci3icconnector6.
Enter t(e connection in3ormation7usuall/ location.
Access, Speci3ic p(/sical pat(.
Oracle, Oracle connection in3ormation
#e-istered name8ser name
8ser pass"ord
SQL Ser1er connection in3ormation 5but use t(e SQL Adapter6
Ser1er
8ser name8ser pass"ord
SQL Ser1er (as man/ s(ortcutsand tric0s "it(in !isual Studio
T(e connector simpl/ establis(es t(e communication lin0s to t(e
database.
-
8/10/2019 A Brief Introduction to ADO.NET Web Forms
5/40
5
Data Connector Callen!es ADO .NET di33iculties
Access problem is t(at it is (ard to c(an-e t(e location later.
It is also di33icult to c(an-e databases later 5SQL Ser1er to Oracle6.
Partial Solution 5but critical6
Store all D$ speci3ic code in one place.
Do notscatter database code across /our application and 3orms.
$e3ore buildin- an/ database 3orms9 create a ne" blan0 Component
3or t(e sole purpose o3 (oldin- all primar/ database de3initions.
Solution E:plorer ; Add ; Add Ne" Item ; Component Class, D$)ain
To create a database connection9 simpl/ dra- one onto t(e desi-n
screen o3 t(e ne" component9 and 3ill in t(e "i4ard properties.
Select a pro1iderAccess,
Oracle, Oracle Pro1ider 3or OLE D$ 5ma/be )icroso3t 1ersion6
SQL Ser1er, 8se t(e SQL Adapter instead?
#ename t(e connector, cnnOle
-
8/10/2019 A Brief Introduction to ADO.NET Web Forms
6/40
6
T"F: Tin!s "icroso#t For!ot
I de1eloped some standard routines to (andle se1eral common
issues. T(e/ are stored in a class called T)F. %ou "ill e1entuall/ end up addin- t(is class to most o3 /our 3orms.
First9 add t(e 3ile to /our pro*ect9 ri-(t@clic0 ; Add ; Add E:istin-,
T)F.1b 5/ou onl/ (a1e to do t(is once6.
Second9 3or an/ 3orm t(at /ou need t(e routines9 open t(e Code !ie"
5!ie" ; Code9 or ri-(t@clic06 add one line o3 code9 o3ten at t(e top9alon- "it( t(e control de3initions,
Dim tm3 As T)F B Ne" T)F56
Details o3 t(e routines "ill be e:plained in 3uture slides as t(e/ are
needed.
Notes and some documentation are "ritten as comments in t(e3ile.
-
8/10/2019 A Brief Introduction to ADO.NET Web Forms
7/40
7
Data Connector: Access Fle$ibilit%
One problem "it( t(e Access connector is t(at it relies on a 3i:ed
pat(name9 ma0in- it di33icult to mo1e t(e database. %ou need to add t(e special T)F 5T(in-s )icroso3t For-ot6 component to
/our pro*ect 5Add ; E:istin- File6.
In t(e D$)ain component9 e:pand t(e Component Desi-ner eneratedre-ion9 in t(e Public Sub Ne"569 a3ter Initiali4eComponent9 add
Dim tm3 As Ne" T)F56
Dim strCS As Strin-strCS B tm3.etCnnStrin-5cnnOleCareer6
I3 5strCS 6 T(en )e.cnnOleCareer.ConnectionStrin- B strCS
In t(e Web.con3i- 3ile9 ri-(t be3ore t(e end Gcon3i-uration9 addGmodi3/
appSettin-s
?@@ 8ser application and con3i-ured propert/ settin-s -o (ere.@@?@@ E:ample, add 0e/Bsettin-Name 1alueBsettin-!alueG @@
add 0e/BcnnOleCareer.ConnectionStrin- 1alueB G
add 0e/BcnnOleCareer.DataSource 1alueB..GDatabaseGcareer.mdb G
GappSettin-s
T(is approac( uses a relati1e pat(9 so /ou can mo1e t(e entire pro*ect.
-
8/10/2019 A Brief Introduction to ADO.NET Web Forms
8/40
8
Data A&apter Comments
Data Adapter
Is responsible 3or translatin- SQL re+uests to t(e c(osen t/pe o3database and returnin- data ro"s and messa-es.
E1er/ command or table t(at /ou "ant to access must be associated
"it( a data adapter.
Tec(nicall/9 a data adapter can (andle se1eral di33erent tables.
$ut9 unless t(e tables "ill be related 5parentGc(ild69 it seems easier tocreate a separate data adapter 3or eac( table.
$i--est dra"bac0 is t(at SQL Ser1er uses a di33erent data adapter
t(an AccessGOracle. So9 i3 /ou "ant to c(an-e D$)Ss later9 /ou (a1e
to replace all o3 t(e adapters?
Critical, Heep all data adapters in one 5ma/be t"o6 locations, t(e
D$)ain component.
Creatin- a data adapter
From t(e Data toolbo:9 dra- an adapter onto t(e main D$ component.
5Ole 3or Access or Oracle9 S+l 3or SQL Ser1er6.
Ans"er t(e "i4ard +uestions. To c(an-e later, Con3i-ure Data Adapter
-
8/10/2019 A Brief Introduction to ADO.NET Web Forms
9/40
9
Create Data A&apter: '(er% T%pe
First9 c(oose /our database
connection 5not s(o"n (ere6. Second9 c(oose t(e t/pe o3
+uer/ to use.
Access onl/ supports te:t
SQL.
SQL Ser1er and Oracle "ill-enerall/ run 3aster i3 /ou use
a stored procedure9 "(ic( is
a parameteri4ed 1ie" stored
and precompiled on t(e
database ser1er.
I3 /ou let t(e "i4ard build t(eprocedure9 /ou s(ould -o
bac0 to t(e database and
double@c(ec0 it.
-
8/10/2019 A Brief Introduction to ADO.NET Web Forms
10/40
10
Create Data A&apter: '(er% T%pe
Create t(e SQL statement to
retrie1e t(e data 3rom tablesor e:istin- 1ie"s.
8se t(e Quer/ $uilder to
minimi4e t/pin- errors.
Warnin-, i3 /ou pull data 3rom
more t(an one table 5or 3rom a1ie" t(at uses more t(an one
table69 .NET "ill not be able to
edit t(at data.
Quer/ $uilder 5ne:t slide6
Ad1anced Options 5slide6
-
8/10/2019 A Brief Introduction to ADO.NET Web Forms
11/40
11
Data A&apter: '(er% )(il&er
Add tables
Clic0 columns to add, Warnin-9t(e/ are displa/ed in t(e orderin "(ic( /ou add t(em. %oucan mo1e t(em later9 but it iseasier to do it ri-(t no".
Note t(at columns t(at arereser1ed "ords s(ould usebrac0ets. W(en in doubt9 usebrac0ets.
I3 /ou use more t(an one table9.NET "ill not be able to edit t(e
data. I3 /ou
-
8/10/2019 A Brief Introduction to ADO.NET Web Forms
12/40
12
Data A&apter: A&vance& Options I3 /ou onl/ need to retrie1e data 5not
c(an-e it69 unc(ec0 all o3 t(ese
options.
In some situations9 /ou "ill "ant to
s0ip t(e optimistic concurrenc/. I3
/ou unc(ec0 t(is bo:9 all c(an-es
"ill be "ritten to t(e database9 e1en
i3 someone else c(an-ed t(e data
immediatel/ be3ore /ou.
T(e t(ird option is onl/ a1ailable
"it( SQL Ser1er and Oracle
5databases t(at support +uer/
lan-ua-es t(at can do more t(anone t(in-6. 8se3ul i3 /ou insist on
usin- Identit/ 5AutoNumber6
columns and need to insert ro"s.
-
8/10/2019 A Brief Introduction to ADO.NET Web Forms
13/40
13
Data Set Overview
A Data Set is rou-(l/ a miniature in@memor/ cop/ o3 part o3 /our
database. %ou speci3/ "(ic( tables /ou "ant to include.
%ou can build relations(ips 5usuall/ parentGc(ild6.
%ou can create Data !ie"s9 but data 1ie"s are not+ueries. T(e/
cannot per3orm
-
8/10/2019 A Brief Introduction to ADO.NET Web Forms
14/40
14
Data Set: +enerate
Once t(e adapter is created9 it is
eas/ to -enerate a data set in t(emain D$ component.
#i-(t@clic0 on t(e adapter.
enerate Data set
Select Ne" and enter a uni+ue
data set name. )a0e sure t(e proper table is
c(ec0ed.
C(ec0 t(e bo: to add it to t(e
desi-ner.
I3 /ou need to delete a dataset9/ou "ill (a1e to delete it 3rom
t(e .:sd list o3 3iles 5and delete it
3rom t(e D$ code6.
-
8/10/2019 A Brief Introduction to ADO.NET Web Forms
15/40
15
Wat We ,ave So Far
A Data Connection t(at can open t(e database
A Data Adapter t(at can send SQL to t(e database and recei1edata ro"s.
A de3inition o3 a Data Set t(at can temporaril/ (old data t(at is
read 3rom one table 5or 1ie"6.
%ou can add ot(er adapters and datasets.
%ou can create relations(ips i3 t(ere are parentGc(ild tables9 but /ou"ill see t(at t(ese are not 1er/ use3ul.
T(in-s "e do not (a1e /et.
T(ere is no data in t(e data set.
T(e data set is not connected to t(e 3orm
-
8/10/2019 A Brief Introduction to ADO.NET Web Forms
16/40
16
Fillin! te Data Set
In t(e main D$ component 5!ie" ; Code6 add a subroutine,
Public Sub FillEmplo/ee5$/!al ds As DataSet6adpEmplo/ee.Fill5ds6
End Sub
At 3irst -lance9 t(e routine seems pointless9 but its purpose is to0eep t(e database@speci3ic code in one component. #emember
t(at t(e adapter is speci3ic to t(e database. T(is "a/9 /our 3orm
can use t(e dataset onl/9 "(ile t(e adapter sta/s in t(e one
component.
We "ill add code to /our data 3orm to call t(is routine.
-
8/10/2019 A Brief Introduction to ADO.NET Web Forms
17/40
17
Creatin! te Data Form
T(ree basic t/pes o3 3orms are commonl/ used in business
applications. Eac( (as sli-(tl/ di33erent considerations and setup.T(e/ "ill be co1ered in later lessons.
Consider a standard 3orm "it( one ro" o3 data.
To start9 add a ne" blan0 Web Form9 named Emplo/ee
Drop a Data Set onto t(e blan0 desi-n. Select t(e emplo/ee data
set /ou created earlier. T(en do !ie" ; Code to see t(e code. %ou need to tell t(e 3orm to -o to t(e database and 3ill t(e data set
"it( data. T(ere are t"o possible timesGplaces to do t(is,
Pa-eLoad or Pa-eInit. For no"9 Pa-eLoad is easier.
Near t(e top o3 t(e class9 add,
Protected cmp)ain as Ne" D$)ain56
Protected tm3 As Ne" T)F56
In t(e Sub Pa-eLoad9 add,
cmp)ain.FillEmplo/ee5DsEmplo/eeJ6
-
8/10/2019 A Brief Introduction to ADO.NET Web Forms
18/40
18
Startin! te Data Form Pa!e
Add a st/le. Open t(e st/le s(eet and add a class 3or Pa-eTitle
C(oose a 3ont 3amil/ 5e.-.9 Arial6 and 3ont si4e 5e.-.9 2 em6 In Te:t9 set t(e (ori4ontal ali-nment to Centered
Sa1e and close t(e st/le s(eet and drop it onto t(e data 3orm.
Add a Title. From t(e Web Forms toolbo:9 Drop a Label near t(e top o3 t(e 3orm
In t(e Te:t propert/9 enter t(e title, Emplo/ee
Set t(e CssClass, Pa-eTitle
C(an-e t(e ID, lblPa-eTitle 5not strictl/ re+uired9 but a use3ul (abit6
Lin0 t(e pa-e. Open t(e main 3orm 5rename it9 e.-.9 AAStart6Add t(e st/le s(eet and a title.
Add a &/perLin0 t(at opens t(e data 3orm.
Set, Te:t 5Emplo/ee Form69 ID 5ln0Emplo/ee69 Na1i-ate8#L5Emplo/ee.asp:6
Sa1e e1er/t(in- and build and bro"se to test it. I3 t(e D$ 3ormcras(es9 c(ec0 t(e securit/ settin-s 3irst.
-
8/10/2019 A Brief Introduction to ADO.NET Web Forms
19/40
19
A&&in! Data Controls
Desi-n 1ie" on t(e data 3orm.
Dra- and drop labels and te:t bo:es onto t(e 3orm. Heep a list (and/ 3or t(e data columns.
For labels9 set properties
ID, lblColumnlblEmplo/eeID
Te:t, Column Emplo/ee ID
For te:t bo:es9 set properties ID, t:tColumnt:tEmplo/eeID
Tab Inde: Number 3rom top to bottom 5> ' K6
Add a $utton9 Call it Sa1e 5IDBbtnSa1e6 5Do not "rite code 3or it /et6.
8se Format )enu to 1erticall/ space and ali-n e1er/t(in-.
Data bindin- t(e controls For eac( te:t bo:9 clic0 t(e Data $indin- propert/
C(oose simple bindin-9 and clic0 /our "a/ do"n t(e list until /ou can
select t(e matc(in- column. 5See ne:t slide6
-
8/10/2019 A Brief Introduction to ADO.NET Web Forms
20/40
20
Simple Data )in&in!
)a0e sure Te:t
propert/ is selected
on t(e le3t.
Clic0 /our "a/ do"n
t(e list on t(e ri-(tuntil /ou can select
t(e matc(in- data
column.
%ou can set a
3ormat9 3or e:ample
3or dates.
-
8/10/2019 A Brief Introduction to ADO.NET Web Forms
21/40
21
Activate Data )in&in!
T(e control properties simpl/ set a mappin- 3rom t(e dataset
table to t(e control. %ou still need to acti1ate t(e bindin- and trans3er t(e data.
Eac( control (as a Data$ind met(od t(at per3orms t(is one@"a/trans3er.
$ut9 it is easier to use t(e Pa-e.Data$ind met(od because it does t(e
trans3er 3or all o3 t(e controls. T(e problem,
Callin- Data$ind replaces t(e content o3 tar-et9 destro/in- "(ate1er"as t(ere be3ore.
Forms use a Post$ac0 approac(. W(en users clic0 a button9 t(e data
is sent bac0 to t(e same pa-e. %ou must a1oid destro/in- t(e datac(an-ed b/ t(e user.
In Pa-eLoad9 add,
Test it
I3 Not Pa-e.IsPost$ac0 T(en
Pa-e.Data$ind56
End I3
-
8/10/2019 A Brief Introduction to ADO.NET Web Forms
22/40
22
Savin! Data: Te Simple Approac W(en /ou created t(e data adapter9 b/ de3ault9 it created SQL
statements to 8pdate9 Insert9 and Delete.
I3 t(e data in t(e in@memor/ data set c(an-es9 ADO .Net canautomaticall/ appl/ t(e c(an-es 5includin- insert and delete6 bac0 to
t(e stored database.
First9 in t(e )ain database component9 add,
Public Sub 8pdateEmplo/ee5$/!al ds As DataSet6
adpEmplo/ee.8pdate5ds6End Sub
In t(e data 3orm9 "rite code 3or t(e Sa1e button to trans3er t(e 3orm
data to t(e data set. Double@clic0 t(e Sa1e button to start. Add,DsEmplo/eeJ.Tables5Emplo/ee6.#o"s5>6.$e-inEdit56
DsEmplo/eeJ.Tables5Emplo/ee6.#o"s5>65Emplo/eeID6 B t:tEmplo/eeID.Te:tDsEmplo/eeJ.Tables5Emplo/ee6.#o"s5>65LastName6 B t:tLastName.Te:t
' 5do all o3 t(e columns6
DsEmplo/eeJ.Tables5Emplo/ee6.#o"s5>6.EndEdit56
cmp)ain.8pdateEmplo/ee5DsEmplo/eeJ6
-
8/10/2019 A Brief Introduction to ADO.NET Web Forms
23/40
23
Problems wit te Simple Approac
We (a1e i-nored concurrenc/ issues.
It "ould actuall/ be better i3 /ou build t(e dataset "it(out testin- 3orconcurrenc/.
#i-(t no"9 i3 someone else c(an-es t(e data9 t(e update "ill cras(9 and
cannot be reco1ered unless /ou bac0 out o3 t(e 3orm and reload t(e data
3rom t(e database.
&o"e1er9 notice t(at e1er/ time t(e 3orm is loaded9 includin- durin- post
bac09 t(e pa-e -oes to t(e database and -ets t(e current 1ersion. So9 t(is
1ersion "ill actuall/ o1er"rite most c(an-es made b/ ot(er people "it( no
"arnin-. T(e onl/ concurrenc/ issue t(at "ill be cau-(t and cras(ed is on
t(e ser1er bet"een re@readin- t(e pa-e and "ritin- t(e ne" data "(ic( is
a relati1el/ minor c(ance.
It "ill be incredibl/ borin- to duplicate t(ese steps 3or e1er/ 3orm /ou"ill (a1e. It "ill also be pain3ul to return later and ma0e c(an-es.
Note t(at t(e dataset is not sa1ed across post bac0. W(ic( is probabl/
-ood9 since t(e s/stem is desi-ned to read t(e entire table. W(ic( is
cra4/9 since t(e 3orm onl/ uses one ro" at a time.
-
8/10/2019 A Brief Introduction to ADO.NET Web Forms
24/40
24
C(stomer Form: A )etter "eto& It is pointless and pain3ul to (a1e to "rite t(e same comple: update
code e1er/ time /ou need a 3orm. So9 I created some -eneric routines
to (elp. T(is e:ample relies (ea1il/ on t(em. )a0e sure /ou (a1e t(e T)F 3ile in /our pro*ect.
Cop/ t(e -eneric code 3rom t(e D$)ain in t(e sample pro*ect. 5especiall/
$atc(8pdate and )er-eConcurrenc/6
ettin- started
Create t(e Data Adapter in D$)ain as usual9 be sure to ta0e t(e de3ault to-enerate optimistic concurrenc/.
enerate t(e Data Set as usual. Heep a cop/ o3 t(e column names.
Add t"o routines to t(e D$)ain code,
Public Sub FillCustomer5$/!al ds As DataSet9 $/!al sID As Strin-6
Dim tm3 As T)F B Ne" T)F56 adpCustomer.SelectCommand.CommandTe:t B
tm3.AddW(ereToSelect5adpCustomer.SelectCommand.CommandTe:t9 CustomerIDB sID6
adpCustomer.Fill5ds6
End Sub
Public Function 8pdateCustomer5$/!al ds As DataSet9 $/!al sID As Strin-6 As Strin-
#eturn $atc(8pdate5ds9 adpCustomer9 Customer9 AddressO3 )e.FillCustomer9
sID6
End Function
-
8/10/2019 A Brief Introduction to ADO.NET Web Forms
25/40
25
C(stomer Form: Constr(ction
Create a ne" Web Form 3or Customer.
Add t(e st/le s(eet and Pa-e TitleAdd t(e data set
Add t(e labels and te:t bo:es9 set t(e tab inde:
8se Data$indin- to lin0 t(e te:t bo:es to t(e dataset.
Add t(e Sa1e button 5do not "rite code /et6
Add a blan0 Label at t(e top9 across t(e pa-e9 ID, lbl)essa-e
!ie" ; Code9 belo" t(e control de3initions 5Protected Wit(E1ents6
Protected cmp)ain As D$)ain B Ne" D$)ain56
Protected tm3 As tm3 B Ne" tm356
-
8/10/2019 A Brief Introduction to ADO.NET Web Forms
26/40
26
C(stomer Form: ar%Col(mns
T(e -eneric code needs a met(od to map t(e column names to
t(e controls. T(e easiest met(od is to create a special arra/ abo1et(e Pa-eLoad routine. For eac( column9 /ou enter
Column Name
Control ID
T/pe o3 control 5Te:t9 Label9 DDL9 C(ec06
W(et(er or not t(e column is part o3 t(e primar/ 0e/
Pri1ate ar/Columns56 As tm3.Sin-leEditItem B M
Ne" tm3.Sin-leEditItem5CustomerID9 t:tCustomerID9 Te:t9
True69
Ne" tm3.Sin-leEditItem5FirstName9 t:tFirstName9 Te:t9 False69
Ne" tm3.Sin-leEditItem5LastName9 t:tLastName9 Te:t9 False69
Ne" tm3.Sin-leEditItem5Address9 t:tAddress9 Te:t9 False69
' Do all o3 t(e columns '
Ne" tm3.Sin-leEditItem5E)ail9 t:tEmail9 Te:t9 False69
Ne" tm3.Sin-leEditItem58#L9 t:t8#L9 Te:t9 False6
-
8/10/2019 A Brief Introduction to ADO.NET Web Forms
27/40
27
C(stomer: Pa!e-oa&
T(e main c(an-e to Pa-eLoad is t(at it onl/ reads 3rom t(e
database t(e 3irst time t(e pa-e is loaded 5not durin- Post$ac069and t(e dataset (as to be sa1ed in a Session so "e can (an- onto
t(e ori-inal 1alues.
Also notice t(at t(e Fill routine (as been modi3ied to read onl/ one
ro" o3 data.
I3 Not Pa-e.IsPost$ac0 T(en
cmp)ain.FillCustomer5DsCustomerJ9 J6
Session5dsCustomerJ6 B DsCustomerJ
Pa-e.Data$ind56
Else
DsCustomerJ B CT/pe5Session5dsCustomerJ69 dsCustomer6
End I3
-
8/10/2019 A Brief Introduction to ADO.NET Web Forms
28/40
28
C(stomer: btnSave Sa1in- t(e data is no" (andled b/ t(e -eneric routines. Add t(e
3ollo"in- code to t(e btnSa1eClic0 routine,
T(e 3irst routine copies t(e data 3rom t(e 3orm to t(e dataset 5usin-
t(e ar/Columns mappin-6. T(e second routine does t(e update9 and c(ec0s 3or concurrenc/.
W(at (appens i3 t(ere is a concurrenc/ collision Tr/ it.
W(ere are t(e underl/in- 1alues stored T(e/ are 0ept as properties
"it(in t(e dataset table.
T(e routine re@reads t(e database9 and replaces t(e underl/in- 1alues"it( t(e ne" current 1alues. &o"e1er9 it lea1es t(is users c(an-es on t(e
3orm. So clic0in- Sa1e a second time "ill 3orce update t(e database i3
t(ere "as a concurrenc/ error.
%ou reall/ need to add some messa-e and probabl/ more c(oices to t(e
user. $ut t(at is a user inter3ace decision.
tm3.Cop/FormToDataSet5)e9 ar/Columns9 DsCustomerJ9 Customer9 >6
lbl)essa-e.Te:t B cmp)ain.8pdateCustomer5DsCustomerJ6
-
8/10/2019 A Brief Introduction to ADO.NET Web Forms
29/40
29
C(stomer: State DropDownist
To pro1ide a drop do"n list 5selection bo:6 3or states9 /ou 3irst need to
create a table o3 StateCodes9 "it( t(e state and t(e name. 8sers "ill seet(e names 5alp(abeti4ed69 and t(e t"o@letter state code "ill be insertedinto t(e database.
Second9 create a ne" data adapter and dataset. #etrei1e StateCode and Name9 sorted.
Deselect t(e build update.
Create t(e FillStateCodes routine.
Customer 3orm9 delete t(e e:istin- state te:t bo:Add t(e StateCodes data set9 t(en add a DDL and set its tab inde:
ID ddlState
DataSource DsStateCodesJ
Data)ember StateCodes DataTe:tField, Name
Data!alueField State
In t(e ar/Columns arra/9 c(an-e t(e control t/pe to DDL and ID to ddlState
In Pa-eLoad9 3ill t(e ne" dataset
cmp)ain.FillStateCodes5DsStateCodesJ6
-
8/10/2019 A Brief Introduction to ADO.NET Web Forms
30/40
30
DD: Tric/% T"F
)icroso3t did not bot(er to create a simple mec(anism to -et drop
do"n lists to displa/ t(e currentl/ assi-ned 1alue. For instance9 i3t(e database state 1alue is CT9 /ou "ant t(e bo: to start "it( a
displa/ o3 Connecticut.
So9 turn to t(e trust/ T)F class 3or (elp.
In properties 3or t(e DDL9 clic0 t(e Data $indin-s button.
)a0e sure /ou c(oose t(e SelectedInde: on t(e le3t side? C(oose custom bindin-9 and add, 5Its t(at eas/9 see ne:t slide6
tm3.SelectDe3aultDDL5sender9 DsStateCodesJ9 Data$inder.E1al5DsCustomerJ9
TablesRCustomer.De3ault!ie".R>.State66
Dataset name t(at (olds t(e DDL list
Dataset name 3or t(e o1erall 3orm
Column name 3rom t(e o1erall 3orm t(at (olds t(e de3ault 1alue
Table name 3or t(e o1erall 3orm
-
8/10/2019 A Brief Introduction to ADO.NET Web Forms
31/40
31
DD )in&in! #or Selecte&0n&e$
-
8/10/2019 A Brief Introduction to ADO.NET Web Forms
32/40
32
C(stomer Form
-
8/10/2019 A Brief Introduction to ADO.NET Web Forms
33/40
33
Te Form is Still not 1sable
T(e -eneric code can be used 3or an/ 3orm. T(e ar/Columns arra/
is t(e 0e/ to pro1idin- t(e in3ormation t(e subroutines need. %ou reall/ need to t(in0 about a user inter3ace to e:plain (o" to
(andle concurrenc/ collisions. T(e -rid code 5later6 s(o"s one
"a/ to (andle t(e problem. $ut 3eel 3ree to e:periment9 t(ere are
no -reat solutions /et.
Se1eral steps remain to ma0e t(is a use3ul 3orm. Selectin- a customerGro" 5tric0/9 depends on user inter3ace -oals6
Findin- a customer 5moderatel/ tric0/ code6
Addin- a ne" customer 5relati1el/ eas/6
Deletin- a customer 5eas/9 until /ou (a1e to s(o" t(e ne:t customer6
Securit/ 5relati1el/ eas/9 but re+uires more 3orms6 )ore
-
8/10/2019 A Brief Introduction to ADO.NET Web Forms
34/40
34
Conc(rrenc% &o" does t(e update command (andle optimistic concurrenc/
T(e SQL command uses t(e ori-inal 1alue 3or eac( column in its
W(ere clause. $ut "(ere does it -et 5and store6 t(e ori-inal 1alue9 "(ic( is t(e 1alue
t(at e:isted "(en t(e data ro" "as retrie1ed
Ans"er, t(e data ro" (olds multiple 1ersions o3 eac( data column.%ou can access t(em "it( Data#o"!ersion enumeration.
I3 t(ere is a concurrenc/ problem9 t(e $atc(8pdate routineretrie1es t(e current 1alue and places it into t(e Ori-inal 1alue. Itlea1es t(e current users 1alue as t(e primar/Gcurrent 1alue. %oucan compare t(ese 1alues in eac( column to see "(ere t(eproblem "as and s(o" bot( 1alues.
Set IDB t(e primar/ 0e/ 1alue o3 t(e ro" bein- edited
Dim ds#o" As Data#o" B Ds)ineJ.Tables5)/Table6.#o"s.Find5ID6
C(ec0 eac( columnGloop
I3 5ds#o"5*6 ds#o"5*9 Data#o"!ersion.Ori-inal66 T(en
Displa/ concurrenc/ errorG1alue
End I3
-
8/10/2019 A Brief Introduction to ADO.NET Web Forms
35/40
35
C(stomer: A&& Co&e
Public Function AddCustomer5$/!al ds As DataSet6 As Strin-
Dim cID As Inte-er B enHe/5Customer6
Dim str)s- As Strin-
I3 5cID >6 T(en
str)s- B 8nable to -enerate a ne" Customer ID.
End I3
Dim dr As Data#o" B ds.Tables5Customer6.Ne"#o"56
dr5CustomerID6 B cID ds.Tables5Customer6.#o"s.Add5dr6
str)s- B 8pdateCustomer5ds9 cID6
ds.Clear56 U Clear t(e data set and reload *ust t(e one ne" ro"
ds.AcceptC(an-es56 U Need to tell it not to delete t(e ori-inal ro"
FillCustomer5ds9 cID6
#eturn str)s-End Function
enHe/ is anot(er -eneric routine /ou need to add to /our
D$)ain. I3 /ou are runnin- under Oracle9 re"rite enHe/ to
use Se+uences.
-
8/10/2019 A Brief Introduction to ADO.NET Web Forms
36/40
36
C(stomer: Delete Co&e
T(e Delete code is muc( tric0ier9 because "e need to -uess "(ic(
CustomerID to displa/ ne:t9 and bad t(in-s (appen to t(e 3orm i3"e delete all o3 t(e ro"s in t(e dataset.
Public Function DeleteCustomer5$/!al ds As DataSet6 As Strin-
Dim sID As Strin- B ds.Tables5Customer6.#o"s5>65CustomerID6
Dim str)s- As Strin-
U T(e (ard part, W(at customer do I load ne:t U Tr/ 3indin- *ust t(e 3irst customer sorted b/ ID
Dim rdr As OleDb.OleDbData#eader
Dim cmd As OleDb.OleDbCommand B Ne" OleDb.OleDbCommand56
cmd.CommandTe:t B SELECT CustomerID F#O) Customer O#DE# $% CustomerID
cmd.Connection B )e.cnnOleSimple
cnnOleSimple.Open56
Dim cID As Inte-er
-
8/10/2019 A Brief Introduction to ADO.NET Web Forms
37/40
37
C(stomer: Delete Co&e 2
Tr/
rdr B cmd.E:ecute#eader5Command$e(a1ior.De3ault6 rdr.#ead56
cID B CT/pe5rdr5CustomerID69 Inte-er6
I3 5cID B CT/pe5sID9 Inte-er66 T(en U I3 delete Jst one9 tr/ to -et t(e ne:t ro"
rdr.#ead56
cID B rdr5CustomerID6
End I3 rdr.Close56
Catc( e2 As E:ception
cID B @J
str)s- B No customers remain. Please add at least one.
End Tr/
cnnOleSimple.Close56
-
8/10/2019 A Brief Introduction to ADO.NET Web Forms
38/40
38
C(stomer: Delete Co&e
Tr/
ds.Tables5Customer6.#o"s5>6.Delete56 I3 5cID @J6 T(en
8pdateCustomer5ds9 cID.ToStrin-6
Else
8pdateCustomer5ds9 Not(in-6
End I3
str)s- B Deleted customer sID
Catc( e As E:ception
str)s- B Error deletin- customer. e.)essa-e
End Tr/
I3 5cID B @J6 T(en U Dataset can ne1er be empt/ or 3orm "ill cras(
Dim dr As Data#o" B ds.Tables5Customer6.Ne"#o"
dr5CustomerID6 B @J ds.Tables5Customer6.#o"s.Add5dr6
End I3
#eturn str)s-
End Function
-
8/10/2019 A Brief Introduction to ADO.NET Web Forms
39/40
39
S(mmar%
T(e 3orm is -ettin- closer.
It still needs to be "it(in an application9 t(at can (andle t(in-s li0eselectin- a customer and securit/.
$ut9 buildin- se1eral 3orms o3 t(is t/pe 3or an application is still
-oin- to be pain3ul9 "it( lots o3 repetition.
8ltimatel/9 it "ould be nicer i3 a data "i4ard e:isted to build most o3
t(e -eneric code automaticall/. Lac0in- t(e time to "rite suc( a "i4ard9 t(e ne:t best step is to
create a set o3 -eneric templates9 t(at can be copied and used to
+uic0l/ build 3orms t(at (andle standard problems.
-
8/10/2019 A Brief Introduction to ADO.NET Web Forms
40/40
40
top related