ADO .NET????????

1
????

• ADO .NET????????

2
17.1 ????ADO .NET????

• ????????,? .NET Framework????????????(??)?????SQL
  .NET Data Provider??OLE DB .NET Data
  Provider?????? .NET Data Provider?????????(Namespa
  ce),??,????Imports???????ADO .NET?????,??????????,
  ???????ADO .NET???????????ADO .NET?????????????,??
  ???????(V)/?????(J)????

3
(No Transcript)
4

• ????????ADO .NET???????????
• 1. System.Data
• ?ADO .NET???????,???????ADO .NET?????????????????M
  anaged????????????????????Tables?Rows?Columns?Cons
  traints? DataSet???????????DataSet?????System.Data
  ?????,?????????????DataSet??,???????????Imports???
  ???????,?????

5

• 2. System.Data.OleDb
• ???????????????OLE DB??????????? SQL?????Fill
  ???????DataSet??????????Access?Excel?SQL Server
  7.0????????????????????????,???????????Imports???
  ??????,?????
• ?OLE DB .NET Data Provider?,????ADO
  .NET???????????OleDb,?OleDbConnection?OleDbComman
  d?OleDbDataReader??

6

• 3. System.Data.SqlClient
• ????????????SQL Server 7.0(?)????????,??????????SQ
  L Server?????????????????,???????????Imports??????
  ???,?????
• ?SQL .NET Data Provider?,????ADO
  .NET???????????Sql,?SqlConnection?SqlCommand?SqlD
  ataReader??

7

• ????ADO .NET?????????????????

8
17.2 ??Connection?????????????

• ADO .NET????Connection??,??????????????????Connect
  ion????????????
• 1.Open??????????????
• 2.Close??????????????
• ????ADO .NET???,????ADO .NET??????????????????????
  ??????

9

• Case 1 ??System.Data.OleDb????(??Access?Excel???
  ?)
• ? ??????Imports System.DataImports
  System.Data.OleDb
• ? ??OleDbConnection????Dim myConnection As
  OleDbConnection
• ? ???????connStr?????,????????????,?????????????
  Dim connStr As String _ "ProviderMicrosoft.Jet
  .OLEDB.4.0Data Source" ???????

10

• ? ??OleDbConnection??????????????myConnection
  New OleDbConnection( connStr )
• ? ??Open???????????myConnection.Open()
• ? ???????????Close???????????myConnection.Close(
  )
• ??????????

11

• ??3-5????????????

12

• Case 2 ??System.Data.SqlClient????(??SQL Server
  7.0????????)
• ????? Case 1 ??,???SqlConnection??,???SqlConnectio
  n????????????,????Open??????????????Close?????????
  ???SqlConnection?OleDbConnection????????????????

13

• ???SqlConnection???????????
• server?????????????IP???localhost(????)?
• databaseSQL Server???????
• uid???????,sa??SQL???????????
• pwd????????
• ?uid?pwd???,????????????Windows?????SQL
  Server,???ASP .NET??(Web Form)?,?????ASP.NET??????
  ??????????????

14
17.3 ??DataReader?????????

• ?????????????????????,?????????????????,????ADO
  .NET????Command?DataReader?????????????
• 17.3.1 Command??
• Command????????SQL?????,???????????????(Stotred
  Procedure)???????????????,Command????SQL??????????
  ??,????????????????????????????????????

15

• 1.ExecuteReader????Command??????SQL
  ???Select??,????DataReader???????????
• 2.ExecuteNonQuery????Command???ExecuteNonQuer
  y??,?????????????????,??????SQL????Insert?Delete?U
  pdate???

16

• 17.3.2 DataReader??
• ??DataReader???????????(Forward-only)????????????,
  ??????????????????????,?????????????????????,?????
  ???????????????DataReader????????????Connection???
  ?????,???Command???ExecuteReader????SQL
  Select?????????????,???DataReader????????????,????
  ????????????????????????,???????????????????????Da
  taReader????,????????????,??Connection???DataReade
  r??,????DataReader???,??????Connection??????????Da
  taReader?????????????

17
(No Transcript)
18

• ??????????DataReader??,?????
• Case 1 ??System.Data.OleDb????(??Access?Excel???
  ?)
• ? ??myDataReader?myCommand????OleDbDataReader?OleD
  bCommand???????Dim myCommand As OleDbCommand
  Dim myDataReader As OleDbDataReader
• ? ??myCommand??OleDbCommand??,????????SQL???????(
  ??SQL?????17.5?)myCommand New OleDbCommand
  (SQL??, myConnection)
• ? ??OleDbCommand???ExecuteReader??,??????SQL??????
  myDataReader??,????myDataReader???????????????????
  ??????myDataReader myCommand.ExecuteReader()

19
(No Transcript)
20

• Case 2 ??System.Data.SqlClient????(??SQL Server
  7.0????????)

21

• 17.3.3 DataReader????????
• ??Command???ExecuteReader????SQL?????????DataReade
  r??,DataReader?????????????????,??????DataReader??
  ??????????????????????????

22
(No Transcript)
23
(No Transcript)
24

• 17.3.4 ????DataReader??????
• ?????DataReader???????????????????,???????????????
  ??????EOF??????,?????????EOF???????,??????DataRead
  er?????????,???????????????????????????????EOF???
  ???????,?????????

25

• 1. ????DataReader???????(??)?????
• ??FieldCount????????,???????1??????????,???ForNex
  t??GetName????????????,????????????TextBox1???????
  ???

26

• 2. ????DataReader????????????????
• ???????,???????????????????????,??????????????????
  ?
• ? ??Do WhileLoop????????????EOF????????????????EO
  F,?????????,????ForNext????Item??,????????????(??
  )?????,??????TextBox1??????

27

• ? ??Do WhileLoop?Read????????????EOF(??????),????
  ?EOF,??????????????????????DataReader???,???Item??
  ?????????,????TextBox1???????

28
(No Transcript)
29

• ??????
• ?????ch17?????DataReader1???????????,??DataReader?
  ??????????????,???? DataReader1\bin\Debug\DataRea
  der1.exe ???,????????

30

• Step1.??VB 2005???????,????Windows??????????????Da
  taReader1?,??????????
• Step2.??????ch17?????ScoreDb.mdb ?????????????
  DataReader1\bin\Debug ????,????ScoreDb.mdb??????
  ??
• Step3.?????????

31

• Step4.??ADO .NET?????
• ?????????(V)/???(C)????????????,?????????????(Impo
  rts)???ADO .NET???????????Access???,????????????(I
  mports)System.Data.OleDb,?????????????????

32

• Step5.????????

33
(No Transcript)
34

• ????
• 1.?DataReader1??????System.Data.OleDb????,???????
  ADO .NET???????????OleDb?
• 2.?6???????????????????????
• 3.?7?????SCORE????????SQL?????selectcmd?????
• 4.?8???OleDbConnection???conn??,??OleDbDataReade
  r???reader??,??OleDbCommand???cmd???
• 5.?9???conn?????connStr?????
• 6.?10???conn???Open????????????

35

• 7.?11???cmd??,????????SQL???conn???
• 8.?12???cmd???ExecuteReader????SQL?????????re
  ader???
• 9.?13-15???ForNext????SCORE???????????txtSho
  w???????
• 10.?17-22???????????reader???FieldCount?Item???
  ???SCORE????????,???txtShow???????
• 11.?23???conn???Close???????????
• 12.Item???????????,???ch17\DataReader2?????????
  

36

• ????Item??GetValue,????GetVaue????????????????????
  ch17\DataRedaer3?????????

37
17.4 ??DataSet?????????

• 17.4.1 DataSet??
• DataSet?????ADO .NET???,?????????????,????????????
  ????????????SQL Server??????????????,?????????SQL
  Server??????????,SQL Server??????????????????????
  ????????????(DataSet)?,?????????????DataSet???????
  ??,????SQL Server???????,??????????SQL
  Server???????DataSet?????,???????????,????????????
  ??????DataSet??????????DataTable??,DataTable??????
  ???????????

38

• 17.4.2 DataAdapter??
• DataAdapter???????DataSet????????DataAdapter????Co
  mmand????SQL??,?????????????DataSet,??????DataTabl
  e????????,?DataSet?????????????????????DataAdapter
  ????

39
(No Transcript)
40

• 17.4.3 ????DataAdapter??
• ??????????????????????????DataTable???
• Step1.?????myDataSet ?DataSet??????myAdapter???Dat
  aAdapter???
• Step2.??myAdapter??????????SQL????????????

41

• Step3.??DataAdapter???Fill??,??????????DataSet????
  ??DataSet?????????DataTable??,?DataTable??????????
  ?????????,??????DataTable??????SQL?????????

42

• ????????

43

• ?????DataSet????????????????????DataTable?????????
  DataSet??????Tables????(?DataTable?????),?????????
  DataTable???
• ???????????????DataTable??,Tables???????????0?

44

• ?????????DataTable????DataTable???
• ?DataTable?????DataGridView????DataSource??,??????
  DataGridView????????DataTable????????,????

45

• ????DataSet???DataTable????????

46
(No Transcript)
47

• ??????
• ?????ch17?????DataSet1???????????,??DataSet1??????
  ???????????
• ?? DataSet1\bin\Debug\DataSet1.exe
  ?????????,????????????????????DataTable???????Comb
  oBox????,DataGridView???????????DataTable????????C
  omboBox????DataTable????,????
  ?,??DataGridView???????????DataTalbe????????

48
(No Transcript)
49

• ?DB.mdb??????BOOK?SCORE?STOCK??????

50

• Step1.??VB 2005???????,????Windows??????????????Da
  taSet1?,??????????????????ch17?????DB.mdb???????Da
  taSet1???bin\Debug????,????DB.mdb???????
• Step2.?????????

51

• Step3.???????

52
(No Transcript)
53

• ????
• 1.?????OleDbDataAdapter???myAdapter??,myAdapter??
  ?????SQL ???Select??,???myAdapter???Fill??????????
  ?myDataSet??(DataSet),?????DataTable??,???????????
  ??????????????
• 2.?5????????????????????????????????????????Data
  Table??,???????myDataSet???????
• 3.?10???OleDbConnection???conn??,??OleDbDataAdap
  ter???myAdapter???
• 4.?14???myAdapter??,???SQL???conn????????
• 5.?13-21???myAdapter???Fill??????????myDataSet??
  ????DataTable???

54

• 6.?22-24???????????myDataSet???DataTable???????c
  boTable???????
• 7.?26?DataGridView1????DataSource????DataTable??
  ?????DataTable??,?DataTable????????????,????????Da
  taGridView1????????????????
• 8.?31???cboTable???????????DataTable??,??????Dat
  aGridView1????DataSource??,?DataGridView1??cboTabl
  e????????DataTable???
• 9.???????Tables?????????????,?????22-24??????????
  (?????????? ch17\ DataSet2 ??)

55

• 17.4.4 ????DataTable???????
• ??????DataTable???????????????,????????,???????myD
  ataTable?DataTable??????
• 1.??DataTable?????myDataTable.Columns.Count
• 2.??DataTable??j?????? (??????0)myDataTable.Colu
  mns(j).ColumnName
• 3.??DataTable??????myDataTable.Rows.Count
• 4.??DataTable??i???????????myDataTable.Rows(i).I
  tem(????)
• 5.??DataTable??i??j??????,??????0myDataTable.Row
  s(i).Item(j)

56

• DataTable??????????

57

• 17.4.5 ????DataTable???????????
• ??????????,?????DataTable???????
• 1.??DataTable???????,??????????Columns.Count?1?
  ?????????,?????ForNext??ColumnName????i??????,???
  txtShow??????

58

• 2.??DataTable???????,???????
• ??? ???????????DataTable???i??j????,???txtS
  how?????,?????

59

• ??? ???????????DataTable???i?????????,???txtS
  how???????????

60
(No Transcript)
61
(No Transcript)
62

• Step1.??VB 2005?????,????Windows????,???????DataTa
  ble1?,??????ch17?????SCOREDB.mdb????????DataTable1
  ??bin\Debug????,????SCOREDB.mdb????????
• Step2.?????????

63

• Step3.???????

64
(No Transcript)
65

• ????
• 1.?7-14???conn????SCOREDB.mdb???,??myAdapter????
  SCORE???,??SCORE?????????myDataSet?DataTable???
• 2.?15???DataTable,??????myDataTable?
• 3.?16-18???For?????????(DataTable??)?????,????tx
  tShow?????
• 4.?20-25?????For?????????(DataTable??)??????Item
  ???????????,??????20-25??????????????,????????ch17
  \DataTable2?????????????

66
(No Transcript)
67
17.5 SQL???Command??

• SQL(Structured Query Language ???????)????????????
  ????,????????????????????,?????? SQL Server???
  (???? Microsoft SQL Server 2000) ??? SQL??,??
  Access ???????? SQL ???????? SQL
  ?????????????????????????????SQL?????

68

• 17.5.1 Select????
• SQL????Select?????Where?????????,????????????????,
  ?????
• ????
• 1.????????????,??????SQL Server?????????????????
  ?????,??????????
• 2.Select????????1,????2,.????N??????????????????
  ?,??????????????????????????SCROE?????name?chi?en
  g?????????Select name ,chi , eng From SCORE

69

• 3.From????????????,??????????????STOCK??????????
  ???????,????Select From STOCK
• 4.?????STOCK?????????????????,?????Select Top
  5 From STOCK

70

• 5.?????????????,?????Where?????Where?????????????
  ,?????????????????????????(?lt?gt?ltgt)????????????,
  ?????????????????????(And?Or?Not)
  ???????????Where??,????????????Select????????MEMB
  ER??????id_no???????? A001 ??pass????????
  6036 Select From MEMBER Where id_no
  A001 And pass 6036
• 6.Order By????????????????,???Desc???????????????
  ??????Desc??,??????????????????,???Order
  By??????????PRODUCT?????id_no????????????,?????
  Select From PRODUCT Order By id_no Desc

71

• 17.5.2 ??SQL???????
• ????Command?DataAdapter?????SQL???,???????????????
  (?????txtName)?,???????????,???????

72

• ????
• 1.????txtName.Text??????????txtName??????,???????
  ??????????(?????????SQL??????,???????????????????)
  ????txtName.Text??????(??),?SQL????????Select
  From Member Where name txtName.Text
  ????????VB???,?????????Select From Member
  Where name ??? Select From Member
  Where name ??? ????????SQL????????????????

73
(No Transcript)
74

• 2.???SQL????????,???????????SQL????,???????????VB?
  ???,??????????????????????VB???,??????SQL???

75

• ??????
• 1.?????ch17?????Command1???????????,??Command1???
  ???????????,???? Command1\ bin\Debug\Command1.exe
  ???,?????????

76
(No Transcript)
77

• 2.? ???????,?
  ????????????,??????????????????? ????

78

• 3.???????????,??????????????????????????????????
  ?????????!?????

79

• Step1.??VB 2005?????,????Windows????,???????Comman
  d1?,??????ch17?????SCOREDB.mdb????????Command1??bi
  n\Debug????,????SCOREDB.mdb????????
• Step2.?????????

80

• Step3.???????

81
(No Transcript)
82

• ????
• 1.?8???InputBox??????????????,????selectName???
• 2.?9?????SQL???????selectName??????
• 3.?11-15???OleDbDataReader?????,??????myReader?
• 4.?15???cmd?ExecuteReader()?????????myReader???
• 5.?16-24???DataReader????????????(EOF)??????????
  ???,????17-21?,????????txtShow??????????23?,?txtS
  how?????????????????!?????

83

• ??
• ???Command1.sln???????????(???)???,?????
  ?,?????????????????????Jackwu??????????

84
(No Transcript)
85

• ??????jackwu???????????,????????????SQL??????????
  ????,?????????,??VB 2005??????,????SQL??
• ???????VB 2005????????????

86

• ??SQL?????,??Jack????(??Jack??????),?wu??????????
  ?????????,????????????SQL????????????????????,?V
  B 2005??????????????????,???????String?????Repla
  ce????????????????????????Command1.sln??9???
• ?????????,????Command2.sln?????????
• ???????????????selectName,?????????????????,??????
  ?,????????????,?????????

87

• 17.5.3 Insert????
• ???????????????,????????????,???SQL???Insert
  Into???????????????????,?????

88

• ????
• ??SQL???????????????,??????????1??????1??????
  2??????2 ????N??????N?????,??????????????
  ??????????????,?PRODUCT??????????,?prod_id??(?????
  ???)???id_no???prod_name??(????????)???name??(??)?
  prod_price??(?????????)???price??

89

• 17.5.4 Delete????
• SQL???Delete???????????????,?????
• ????????,????Where???????????????????PRODUCT????id
  _no????? A123 ???

90

• 17.5.5 Update????
• ???????????????????????,????SQL????UpDate?????????
  ???,?????
• ????
• 1.Set?????????????
• 2.Where?????????,??????????????????PRODUCT???????
  id_no(??)?? B123 (??????????)???,??????name(??)
  ?????????price????????4000??????

91

• 17.5.6 ????Command???SQL???????
• ??????(Insert)???(Delete)???(Update)???SQL??,?????
  ??Command???ExecuteNonQuery?????????????????

92
(No Transcript)
93

• ??????
• 1.?????ch17?????Command3???????????,??Command3???
  ????????????,???? Command3\bin\Debug\Command3.exe
  ???,????????,???DataGridView?????PERSON?????????

94

• 2.?????????????????????? ? ?
  ?,????????????????????????????
• Step1.??VB 2005?????,????Windows????,???????Comman
  d3?,??????ch17?????SCOREDB.mdb????????Command3??bi
  n\Debug????,????SCOREDB.mdb????????
• Step2.?????????

95
(No Transcript)
96

• Step3.???????

97
(No Transcript)
98
(No Transcript)
99
(No Transcript)
100
(No Transcript)
101

• ????
• 1.?5-16??ShowPerson?????????DataGridView1?????P
  ERSON????????
• 2.?61-63?GetSqlStr??????????????????????????,
  ??????????
• ????? GetSqlStr(?????????)
• 3.?19???ShowPerson??,?DataGridView1?????Person??
  ??????
• 4.?25???????SQL?????insertCmd????????SQL?????Get
  SqlStr??????????????????
• 5.?29???Command???cmd??,???insertCmd????(?????SQ
  L??)?conn????????

102

• 6.?30???cmd???ExecuteNonQuery?????????
• 7.?38???????SQL?????updateCmd?
• 8.?51???????SQL?????delCmd?
• 9.PERSON???????????????????,????????????,????????
  ????

103

• ??
• ? ?????????????????????????????,??????????????????
  ???????????,?????????????
• ? ????Access????????????????????,?????????????????
  ??????

104

• 17.5.7 ?????????SQL??
• ??????????????SQL????????????SQL?????????SQL?????,
  ?????????????????????????????????SQL?????
• Step1.????????SQL??,??????????_at_????????SQL????????
  ???_at_name, _at_tel, _at_position, _at_salary?

105

• Step2.??Command?????????????????????SQL???Connecti
  on???
• Step3.??????????????,??????_at_name, _at_position,
  _at_tel?????????,_at_salary?????????

106

• Step4.???????????????,? ??? ???_at_name,????
  ???_at_position,04-12345678 ???_at_tel,50000???_at_salary
  ?
• Step5.??Command???ExecuteNonQuery?????????????SQL?
  ?,??????

107

• ????,????????

108

• ??????3-6???,?????Parameters???????,??????System.D
  ata.SqlClient?System.Data.OleDb?Parameters????????
  ??

109

• ???????Command4?????????Command4??????????Command3
  ??,??????????Command4?Command3?????,??????????????
  ???????????SQL??,??????????????????????????

110
(No Transcript)
111
(No Transcript)
112
(No Transcript)
113
(No Transcript)
114
(No Transcript)