?14? ADO.NET?????

1
?14? ADO.NET?????

• 14-1 ??????
• 14-2 ??ADO.NET?????
• 14-3 DataGrid????????
• 14-4 SQL??????
• 14-5 SQL??????

2
14-1 ??????

• 14-1-1 ??????
• 14-1-2 ??Access?????

3
14-1-1 ??????

• ????????????????(Database System)????,????????????
  (Database)??????????(Database Mangement
  System,DBMS)???,?????

4
14-1-2 ??Access?????
5
14-2 ADO.NET???

• 14-2-1 ???ADO.NET
• 14-2-2 ??DataReader???????

6
14-2-1 ???ADO.NET-??

• ADO.NET?????????,???ADO?(ActiveX Data
  Object)???????
• ADO.NET?????????????,????????????,???????????,????
  ???????,????????(? EXCEL?????Access?)???,?VB.NET??
  ????,??ADO.NET????????????

7
ADO.NET
DataReader
1
Command
1
1
????? (Data Binding)
Connection
2
2
DataAdapter
2
2
Data Source
DataSet
DataTable
8
ADO.NET ?????

• ?? Connection ?????????????,???? Command
  ???????????????,(??????? Command ? SQL
  ??),??????,?? DataReader ??????? DataSet ??,????
  Data Binging ?????? (? TextBox ? DataGrid )
  ??????????? Insert / Delete / Update ????????????

9
14-2-1 ???ADO.NET-Connection??

• Connection????????????????,??????Connection???????
  ???,Connection????????
• OLE DB??OleDbConnection??
• SQL Server??SqlConnection??

10
14-2-1 ???ADO.NET-Command??

• Command?????????????,???????????,????SQL??,???Conn
  ection??????????,?????Command????SQL???
• Command???????
• OLE DB??OleDbCommand??
• SQL Server??SqlCommand??

11
14-2-1 ???ADO.NET-DataReader??

• ADO.NET?DataReader???????????Command????SQL??,????
  ??????????????????DataReader???????????,??????????
  ?????,??????????
• DataReader??????
• OLE DB??OleDbDataReader??
• SQL Server??SqlDataReader??

12
14-2-1 ???ADO.NET-DataSet?DataTable??

• DataSet????DataTable?????,DataSet?????????????????
  ?,???DataTable????????????,???DataRow?????????
• ????Connection?????????,????DataAdapter???Fill????
  ???????DataSet??,???????DataTable???DataAdapter???
  ???
• OLE DB??OleDbDataAdapter??
• SQL Server??SqlDataAdapter??

13
14-2-2 ??DataReader???????-??

• VB.NET??????ADO.NET?????????????????DataReader???
• ???Access?????,??????ADO.NET?,????Imports?????OLE
  DB???????Namespace,????
• Imports System.Data.OleDb
• ??SQL Server???,????????,????
• Imports System.Data.SqlClient

14
14-2-2 ??DataReader???????-??1

• ??1??????????
• ????????Connection????????,????
• Dim objCon As OleDbConnection
• objCon New OleDbConnection(strDbCon)
• StrDbCon?????????????,????
• strDbCon "ProviderMicrosoft.Jet.OLEDB.4.0
  _
• Data
  Source..\..\Books.mdb"
• ???????
• objCon.open()

15
14-2-2 ??DataReader???????-??2

• ??2??Command??
• ?????????,?????Command??,????????SQL????,????
• Dim objCmd As OleDbCommand objCmd _
• New OleDbCommand("SELECT FROM Book", objCon)

16
14-2-2 ??DataReader???????-??3

• ??3??DataReader??
• ????Command???,???????ExecuteReader????SQL????Data
  Reader??,????
• Dim objDataReader As OleDbDataReader
• objDataReader objCmd.ExecuteReader()
• ???????DataReader??objDataReader?

17
14-2-2 ??DataReader???????-??3 Execute??
18
14-2-2 ??DataReader???????-??4

• ??4??????????????
• DataReader??????????,???SQL????DataReader???,?????
  ?????DataReader??,???????While?????????????,????
• While objDataReader.Read()
• out objDataReader.Item("BookID") " "
• out objDataReader.Item("BookTitle") " "
• out objDataReader.Item("BookPrice") _
• vbNewLine
• End While

19
DataReader ??

• ???????? objDataReader.Item(BookTitle)
• objDataReader.Item(3)? 0 ??
• FieldCount ?????
• IsClosed ??????,True ????

20
14-2-2 ??DataReader???????-??5

• ??5??DataReader??????
• ??????DataReader?????????Connection??,????
• objDataReader.Close()
• objCon.Close()
• ???????Close????DataReader?Connection???

21
14-3 DataSet???DataGrid???

• 14-3-1 ??????DataSet??
• 14-3-2 DataGrid????????

22
14-3-1 ??????DataSet??-??

• DataSet?????????????????,?????????DataTable???
• ???DataReader????????,????????????????DataSet??,??
  ????DataSet????????

23
14-3-1 ??????DataSet??-??1

• ??1??Connection??
• ??????DataReader??,????????Connection????????,????
  
• objCon New OleDbConnection(strDbCon)
• objCon.Open()
• ???????objCon???????,??????????

24
14-3-1 ??????DataSet??-??2

• ??2??DataAdapter??
• ?DataSet?????DataAdapter????????,??????DataSet?Con
  nection??????????,????????DataSet???
• DataAdapter?????????,SQL Server??SqlDataAdapter,??
  ??????OLE DB?OleDbDataAdapter,????
• Dim objDataAdapter As OleDbDataAdapter
• objDataAdapter New OleDbDataAdapter( _
• "SELECT FROM Book", objCon)

25
14-3-1 ??????DataSet??-??3

• ??3??DataSet????????
• ?????????DataSet??,??DataAdapter?SQL????,???????Da
  taSet??,????
• Dim objDataSet As DataSet New DataSet()
• objDataAdapter.Fill(objDataSet, "Books")

DataTable ????
26
14-3-1 ??????DataSet??-??4

• ??4??????????????
• ???DataAdapter??????????DataSet?,???For
  Each???????DataRow??,???????,????
• Dim objRow As DataRow
• For Each objRow in objDataSet.Tables("Books").Rows
  
• out objRow("BookID") vbNewLine
• out objRow("BookTitle") vbNewLine
• Next
• ???????????
• objRow objDataset.Tables(Books).Rows(0)
• objDataset.Tables(Books).Rows.Count ?????????

27
14-3-1 ??????DataSet??-??5

• ??5???????
• ???????????,??????Connection???objCon,????
• objCon.Close()

28
14-3-2 DataGrid????????-??

• DataGrid????????????????????,?????????????Excel??
  ????????????????

29
14-3-2 DataGrid????????-??
30
14-3-2 DataGrid????????-??
31
14-3-2 DataGrid????????-??????

• ?????DataGrid????,?????????????DataSet??,?????Data
  Grid????DataSource??,????
• dtgOutput.DataSource _
• objDataSet.Tables("Books")
• ??????DataGrid???????????DataSet????Books?DataTabl
  e???

32
14-4 SQL??????

• 14-4-1 SQL?????
• 14-4-2 SQL?????

33
14-4-1 SQL?????-??

• ?SQL?(Structured Query Language)??ANSI?(American
  National Standards Institute) ????????,SQL????????
  ???????????,??Access?SQL Server?Informix?Oracle?Sy
  base?????????ANSI?SQL???

34
14-4-1 SQL?????-??

• SQL?????????4???,?????
• ???????DDL?(Data Definition Language)????????????
  ?SQL???
• ???????DML?(Data Manipulation Language)??????????
  ????????
• ???????DQL?(Data Query Language)?????????,????SEL
  ECT???
• ???????DCL?(Data Control Language)???????????????
  ??

35
14-4-2 SQL?????-??

• ?SQL?????????????SELECT??,???????????
• SELECT ??1, ??2 FROM ??? WHERE conditions
• ??SELECT?????12??????,conditions?????,???????????
  ?????????WHERE???????,????1?2??

36
14-4-2 SQL?????-????

• ??""????????????,??????????????,????
• SELECT FROM Book
• ??????WHERE??,???????????????????

37
14-4-2 SQL?????-???????WHERE??

• SQL?????????,WHERE??????????,????
• ??????????,????
• SELECT FROM Book WHERE BookID'F8111A'
• ???????????????,????
• SELECT FROM Book WHERE BookPricegt550
• ??????????LIKE????????""????,????????????????????
  ??,????
• SELECT FROM Book WHERE BookID LIKE '1'
• ???????????ltgt?gt?lt?gt?lt???????????????????????????
  ???????

38
14-4-2 SQL?????-???????WHERE??

• WHERE???????????AND?OR???????,?????,????
• AND????????????????,????????,??????"3"????"??"??
  ??,????
• SELECT FROM Book WHERE BookID LIKE '3' AND
  BookTitle LIKE '??'
• OR?????????????????????,??????"3"????"??"????,??
  ??
• SELECT FROM Book WHERE BookID LIKE '3' OR
  BookTitle LIKE '??'

39
14-4-2 SQL?????-????

• SQL?????????????????,???????,???SELECT???????ORDER
  BY????,????
• SELECT FROM Book WHERE BookPricegt500 ORDER BY
  BookPrice
• ????????BookPrice????,????????ASC,??????????,????D
  ESC,????
• SELECT FROM Book WHERE BookPricegt500 ORDER BY
  BookPrice DESC

40
14-4-2 SQL?????-SQL?????
41
14-5 SQL??????

• 14-5-1 ??????SQL??
• 14-5-2 ????????

42
14-5-1 ??????SQL??-INSERT????1

• SQL???????????????????,INSERT???????,????
• INSERT INTO table (column1,column2,..) VALUES
  ('value1', 'value2 ', )
• INSERT???????,????
• ??????????,??????????
• INSERT??VALUES??,?????????,?????????,??/????""???
  ??
• INSERT?????????,????????????????????,???????????,?
  ???????????VALUES???????

43
14-5-1 ??????SQL??-INSERT????2

• ????Book?????????SQL??,????
• INSERT INTO Book (BookID,BookTitle,BookAuthor,Book
  Price,BookP
• ubDate) VALUES ('F8915','ASP????????','???',580,0
  6/06/2000)
• ??SQL???????????????,?????,??/????""???

44
14-5-1 ??????SQL??-DELETE????

• SQL????????????????????????,DELETE???????,????
• DELETE FROM table WHERE conditions
• DELETE???????,????
• WHERE???DELETE???????,????WHERE??,??????????????
• ????Book?????????SQL??,????
• DELETE FROM Book WHERE BookID'F8915'

45
14-5-1 ??????SQL??-UPDATE????1

• SQL???????????????????,?????????,UPDAT
  E???????,????
• UPDATE table SET column1 'value1' WHERE
  conditions
• ???????????,???????,?????,????
• UPDATE table SET column1 'value1' , column2
  'value2' WHERE conditions
• UPDATE???????,????
• WHERE???????,????WHERE??,???????????????
• ???????????????,???????????,??????????

46
14-5-1 ??????SQL??-UPDATE????2

• ????Book?????????SQL??,????
• UPDATE Book SET BookPrice650, BookPubDate6/15/2
  002 WHERE
• BookID'F8534A'
• ??SQL???WHERE???????BookID??,????SET?????????

47
14-5-2 ????????

• ?VB.NET?????????????????????Command???,?????Comman
  d???ExecuteNonQuery????SQL??,????
• Dim intRowsAffected As Integer
• intRowsAffected objCmd.ExecuteNonQuery()
• ????????intRowsAffected???????????,?????ExecuteNon
  Query????SQL??,???????DataReader?????

48
14-5-2 ????????

• ?? DataGrid ??? Data Binding ????? DataSource
  ??????,?????? DataSource ?????? Data Binding
  ??????????? ListBox?ComboBox ?????????????????
• ListBox ?? DataSource ??,????????
• DisplayMember ???????
• ValueMember ???????,?ListBox1.SelectedValue
  ????????

49
????? OleDbDataAdapter ?????? ????DataSet
????? OleDdDataAdapter
50
???????
51
???????????, ? ???? ?
52
???? ??? TabPage ?????????,Access ???
Microsoft Jet 4.0 OLE DB Provider,????
53
???? ?
54
????? ???? ? ?? ?,????????? (???) ?????????,?
??
55
???????? ????????? ???????????, ? ???
56
???? ???
57
????? SQL ??
58
???? ??
59
???? OldDbConnection1 ?? ? OleDbDataAdapter1 ??,??
???????