Transcript Using ADO.NET

การเขียนโปรแกรมภาษาคอมพิวเตอร์ข้นั สูง 4123305
Using ADO.NET
โดย อ. นัฐพงศ์ ส่ งเนียม
สาขาวิชา เทคโนโลยีสารสนเทศ และ
สาขาวิชา วิทยาการคอมพิวเตอร์
คณะวิทยาศาสตร์ และเทคโนโลยี มหาวิทยาลัยราชภัฏพระนคร
http://www.siam2dev.com
[email protected]
[email protected]
[email protected]
Using ADO.NET
Disconnected Model
Disconnected Model
VB4
user id=sa;
password=123
Products
id name unit price
10
1 pen 25
3
2 pencil 3
5
DataSet
P1
Connection
DataAdapter
SelectCommand
Fill
Update
CommandBuilder
UpdateCommand
id name unit price
1 pen 25
10
3
2 pencil 3
5
ADO.NET Object Model
DataSet
System.Data
DataTable
DataReader
Command
CommandBuilder
DataAdapter
DataRow
Update
DataColumn
Fill
Constraint
Connection
DataRelation
Database
XML File
Importing Namespace
Oledb
'Connect to database via OLEDB
Imports System.Data.Oledb
SqlClient
'Connect to SQL Server database only
Imports System.Data.SqlClient
OledbConnection
Initial Object
Connect to Microsoft Access
Public CN as New OledbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=FileName.MDB")
Connect to Microsoft SQL Server
Public CN as New OledbConnection("Provider=SQLOLEDB;" & _
"Data Source=ServerName;Initial Catalog=DatabaseName;" & _
User ID=UserName;Password=Password;")
Connect to Database Using UDL
Public CN as New OledbConnection("File Name=FileName.UDL")
SqlConnection
Initial Object
Connect to Microsoft SQL Server
Public CN as New SQLConnection("Data Source=ServerName;" & _
Initial Catalog=DatabaseName;" & _
User ID=UserName;Password=Password;")
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.
DataSet and DataAdapter Objects
DataSet
DataAdapter
DataTable
Fill
Update
Data Source
SelectCommand
CommandBuilder
UpdateCommand
DataAdapter
DataTable
Fill
Update
SelectCommand
CommandBuilder
UpdateCommand
Connection
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
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")
Retriving data from DataSet
Table Name
Column Name
TextBox1.Text = DS.Tables("Customer").Rows(2)("Name")
Row number
DataSet
Customer
ID
Product
Name
Tel
ID
Name
100
Peter
123123121
P1
Coke
101
John
012040230
P2
Pepsi
102
David
402123242
P3
Fanta
103
Joseph
973534245
P4
7Up
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,"TableName")
Update DataSet to data source.
DA.Update(DS,"TableName")
DataRow
DataColumn
DataRow
DataTable
DataRelation
DataSet1.Tables("TableName").Rows({number})

Delete an existing row
DataSet1.Tables("TableName").Rows({number}).Delete
Insert and update data

Insert Data
Dim DR1 As DataRow = DataSet1.Tables("Person").NewRow()
DR1("ID") = "001"
DR1("Name") = "Peter"
DataSet1.Tables("Person").Rows.Add(DR1)

Update Data
DataSet1.Tables("Person").Rows(2)("ID") = "001"
DataSet1.Tables("Person").Rows(2)("Name") = "Peter"
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.DataSource=DS.Tables("Customer")
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)("StuTel")
Catch ex As Exception
' Message something
End Try
End Sub
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 >= 0 ) AndAlso ( i + Number < Count ) Then
i += Number
ShowData()
Else
' Message Something
End If
End Sub
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.Count()
i = Count - 1
ShowData()
End Sub