ActiveX Data Object (ADO) By: U. Abd. Rohim Website : http://www.abdrohim.com mailto :
[email protected]
4/4/2005
Pemrograman Visual Basic & SQL Server
1
ADO n
n
n
n
Memungkinkan aplikasi untuk mengakses dan memanipulasi database melalui ADODB ADO mendukung pengembangan aplikasi ClientServer (aplikasi n-Tier) dan aplikasi berbasis Web Model ADO mendefinisikan koleksi dari objek yang mendukung konsep Component Object Model (COM) dan OLE (Object Linking and Embedding). model ini disebut sebagai teknologi OLE DB Pada VB untuk mengakses ADO buat referensi dengan menu Project | Refferences dan ceklist Microsoft ActiveX data Objects 2.0 Liblary
4/4/2005U. Abd. Rohim
[email protected]
Pemrograman Visual Basic & SQL Server ADO
2
1
Model ADO n
n
n
n
Connection Memungkinkan terjadinya pertukaran data antara VB dan dataserver Command berisi instruksi SQL Parameter Berisi parameter yang berkaiatan dengan instruksi SQL Recordset
Connection Error Property Command Parameters Property Connection
Digunakan untuk proses navigasi dan manipulasi data n
Field, Error, Property
4/4/2005U. Abd. Rohim
[email protected]
Fields Property Property
Pemrograman Visual Basic & SQL Server ADO
3
Membuka Connection nNama
Provider/Driver nNama Server nNama Database nUser ID dan Password Keberhasilan Connection dinyatakan dalam properti State; State = 1 (Connection OK) State = 0 (Connection Failed) 4/4/2005U. Abd. Rohim
[email protected]
Pemrograman Visual Basic & SQL Server ADO
4
2
Contoh Connection Dim conn as ADODB.Connection Set Conn = NEW ADODB.Connection conn.Provider = "SQLOLEDB" conn.ConnectionString = "SERVER=SERVER_DATA; UID=sa; PWD=xx; DATABASE=Northwind" conn.CommandTimeout = 30 conn.Open If Conn.State=1 then MsgBox “Connection OK” Else MsgBox “Connection Failed” End if 4/4/2005U. Abd. Rohim
[email protected]
Pemrograman Visual Basic & SQL Server ADO
5
Eksekusi Instruksi SQL n
n
n
ADO digunakan untuk mengakses ke database untuk proses; pencarian, editing, updating, insert dan manipulasi lainnya Instruksi SQL dapat diberikan lewat layer: Connection, Command dan Recordset Object Command q q q
ActiveConnection CommandText CommandType adCmdText, adCmdTable, adCmdTableDirect, adCmdStoreProc, adCmdUnknown, adCmdFile
q
Execute
4/4/2005U. Abd. Rohim
[email protected]
Pemrograman Visual Basic & SQL Server ADO
6
3
Contoh Command Dim conn As ADODB.Connection Dim cmd As ADODB.Command Dim rs As ADODB.Recordset Set conn = New ADODB.Connection Set cmd = New ADODB.Command Set rs = New ADODB.Recordset conn.Provider = "SQLOLEDB" conn.ConnectionString = "SERVER=SERVER_DATA; UID=sa; PWD=ar; DATABASE=Northwind" conn.CommandTimeout = 30 conn.Open cmd.ActiveConnection = conn cmd.CommandText = "Select * From Customers" cmd.CommandType = adCmdText Set rs = cmd.Execute 4/4/2005U. Abd. Rohim
[email protected]
Pemrograman Visual Basic & SQL Server ADO
7
RecordSet (1) n
n n
RecordSet merepresentasikan kumpulan record yang dihasilkan oleh query pada suatu database Cursor menujukan satu record yang berada dalam sebuah RecordSet Metoda RecordSet.Open (parameter) q q q q q
cmdSQL strConnect CursorType LockType cmdType
4/4/2005U. Abd. Rohim
[email protected]
Pemrograman Visual Basic & SQL Server ADO
8
4
RecordSet (2) n
CursorType q q q q
n
adOpenForwardOnly (default) adOpenDynamic adOpenKeySet adOpenStatic
TypeLock q q q q
adLockReadOnly (default) adLockPessimistic adLockOptimistic adLockBatchOptimistic
4/4/2005U. Abd. Rohim
[email protected]
Pemrograman Visual Basic & SQL Server ADO
9
RecordSet (3) n
n
n
Navigasi MoveFirst, MoveNext, MovePrevious, MoveLast, EOF, BOF Manipulasi Data AddNew, Update, Delete, CancelUpdate Akses Field recordset!namafield, recordset.fields(n), recordset.fields(n).value, recordset.fields(namafiled”)
4/4/2005U. Abd. Rohim
[email protected]
Pemrograman Visual Basic & SQL Server ADO
10
5
Contoh RecordSet Dim conn As ADODB.Connection Dim rs As ADODB.Recordset Dim strSql As String Set conn = New ADODB.Connection Set rs = New ADODB.Recordset conn.Provider = "SQLOLEDB" conn.ConnectionString = "SERVER=SERVER_DATA; UID=sa; PWD=ar; DATABASE=Northwind" conn.Open strSql = "Select * From Customers" rs.Open strSql, conn, adOpenStatic, adLockOptimistic rs.MoveFirst Do While Not rs.EOF 'lakukan sesuatu proses rs.MoveNext Loop 4/4/2005U. Abd. Rohim
[email protected]
Pemrograman Visual Basic & SQL Server ADO
11
6