????????????????????????????????????? 4123305 Using ADO.NET

1
????????????????????????????????????? 4123305
Using ADO.NET

• ??? ?. ??????? ????????
• ???????? ????????????????? ???
• ???????? ???????????????????
• ?????????????????????????? ??????????????????????
  ?
• http//www.siam2dev.com
• nattapong_at_siam2dev.com
• xnattapong_at_hotmail.com
• xnattapong2002_at_yahoo.com

2
Using ADO.NET

• Disconnected Model

3
Disconnected Model

Products Products Products Products
id name unit price
1 pen 5 10
2 pencil 3 5
Products Products Products Products
id name unit price
1 pen 2 3
2 pencil 3 5
DataSet
P1 P1 P1 P1
id name unit price
1 pen 2 3
2 pencil 3 5
P1 P1 P1 P1
id name unit price
1 pen 5 10
2 pencil 3 5
Connection
SelectCommand
CommandBuilder
4
ADO.NET Object Model
System.Data
DataSet
DataTable
DataReader
CommandBuilder
DataRow
Update
DataColumn
DataAdapter
Command
Constraint
Fill
Connection
DataRelation
Database
XML File
5
Importing Namespace
Oledb
'Connect to database via OLEDB
Imports System.Data.Oledb
SqlClient
'Connect to SQL Server database only
Imports System.Data.SqlClient
6
OledbConnection
Initial Object
Connect to Microsoft Access
Public CN as New OledbConnection("ProviderMicroso
ft.Jet.OLEDB.4.0" _ "Data SourceFileName.MDB"
)
Connect to Microsoft SQL Server
Public CN as New OledbConnection("ProviderSQLOLED
B" _ "Data SourceServerNameInitial
CatalogDatabaseName" _ User
IDUserNamePasswordPassword")
Connect to Database Using UDL
Public CN as New OledbConnection("File
NameFileName.UDL")
7
SqlConnection
Initial Object
Connect to Microsoft SQL Server
Public CN as New SQLConnection("Data
SourceServerName" _ Initial
CatalogDatabaseName" _ User
IDUserNamePasswordPassword")
8
Connection Methods

• Open
• Open a database connection with the property
  settings specified by the ConnectionString.
• Close
• Close the connection to the data source. )
• Dispose
• Release the resources used by the Component.

9
DataSet and DataAdapter Objects
DataSet
DataAdapter
Data Source
DataTable
SelectCommand
Fill




CommandBuilder
Update
Connection
UpdateCommand
DataAdapter
DataTable
SelectCommand
Fill




CommandBuilder
Update
UpdateCommand
10
DataSet

• Datasets can include multiple DataTables
• Relationships between tables are represented
  using DataRelations
• Constraints enforce primary and foreign keys
• Use the DataRow and DataColumn to access values
  in Tables

DataColumn








DataRow
DataTable
DataRelation
11
DataAdapter

• Represents a set of data commands and a database
  connection that are used to fill the DataSet and
  update the data source.

Fill data from data source to DataSet
SQL "Select Sataement" Dim DS as New
DataSet("Name") Dim DA as New OledbDataAdapter(SQL
,CN) DA.Fill(DS,"TableName")
12
Retriving data from DataSet
Table Name
Column Name
TextBox1.Text DS.Tables("Customer").Rows(2)("Nam
e")
Row number
DataSet
Customer
Product
ID Name Tel
100 Peter 123123121
101 John 012040230
102 David 402123242
103 Joseph 973534245
ID Name
P1 Coke
P2 Pepsi
P3 Fanta
P4 7Up
13
CommandBuilder
Automatically generating single-table commands
used to reconcile changes made to a DataSet with
the associated database.
Fill data from data source to DataSet (Using
CommandBuilder)
SQL "Select Sataement" Dim DS as New
DataSet Dim DA as New OledbDataAdapter(SQL,CN) Dim
CB as New OledbCommandBuilder(DA) DA.Fill(DS,"Tab
leName")
Update DataSet to data source.
DA.Update(DS,"TableName")
14
DataRow
DataColumn

• Delete an existing row

DataRow
DataTable
DataRelation
DataSet1.Tables("TableName").Rows(number)
DataSet1.Tables("TableName").Rows(number).Delete
15
Insert and update data

• Insert Data
• Update Data

Dim DR1 As DataRow DataSet1.Tables("Person").New
Row() DR1("ID") "001" DR1("Name")
"Peter" DataSet1.Tables("Person").Rows.Add(DR1)
DataSet1.Tables("Person").Rows(2)("ID")
"001" DataSet1.Tables("Person").Rows(2)("Name")
"Peter"
16
DataGrid Control

• Show data from DataTable on user interfaces.
• Display tabular data and allowing for updates to
  the data source.
• Can be used to display either a single table or
  the hierarchical relationships between a set of
  tables.

Seting DataSource
DataGrid1.DataSourceDS.Tables("Customer")
17
Method ShowData

• Used for retrieving data from DataSet and showing
  on TextBox.

Private i As Integer 0 Private Sub ShowData(
) Try TextBox1.Text DS.Tables("Student"
).Rows(i)("StuID") TextBox2.Text
DS.Tables("Student").Rows(i)("StuName")
TextBox3.Text DS.Tables("Student").Rows(i)("StuT
el") Catch ex As Exception ' Message
something End Try End Sub
18
Method MoveData

• Used for moving the cursor.

Private Sub MoveData(Number As Integer) Dim
Count As Integer DS.Tables("Student").Rows.Count
() If ( i Number gt 0 ) AndAlso ( i Number
lt Count ) Then i Number
ShowData() Else ' Message Something
End If End Sub
19
Method MoveFirst and MoveLast

• Used for moving the cursor to the first row or
  the last row of the table.

Private Sub MoveFirst( ) i 0
ShowData() End Sub Private Sub MoveLast( )
Dim Count As Integer DS.Tables("Student").Rows.C
ount() i Count - 1 ShowData() End Sub