1 / 61

Active Data Object

Active Data Object. Yen-Cheng Chen Department of Information Management Ming Chuan University Dec. 1999. Note: Based on Dr. J.L.Wang’s Presentation. Outlines. ADO Overview ADO Object Model ADO Objects Connection Command & Parameters Recordset & Fields. ADO Overview.

tyrone-bean
Download Presentation

Active Data Object

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Active Data Object Yen-Cheng Chen Department of Information Management Ming Chuan University Dec. 1999 Note: Based on Dr. J.L.Wang’s Presentation

  2. Outlines • ADO Overview • ADO Object Model • ADO Objects • Connection • Command & Parameters • Recordset & Fields

  3. ADO Overview • ADO is Microsoft's strategic, high-level interface to all kinds of data. • ADO provides consistent, high-performance access to data, whether you're creating a front-end database client or middle-tier business object using an application, tool, language, or even an Internet browser. • ADO is the single data interface you need to know for 1- to n-tier client/server and Web-based data-driven solution development.

  4. ADO Overview (Cont.) • ADO is designed as an easy-to-use application level interface to Microsoft's newest and most powerful data access paradigm, OLE DB. • OLE DB provides high-performance access to any data source, including relational and non-relational databases, email and file systems, text and graphics, custom business objects, and more.

  5. Microsoft UDA 應用程式, Active Server Page ADO OLE DB ODBC Database Data Storage Database Universal Data Access

  6. ADO 物件(Objects) • Connection啟動資料交換。 • Command具體化一個 SQL 陳述式。 • Parameter具體化一個 SQL 陳述式的參數。 • Recordset啟動資料瀏覽與操作。 • Field具體化一個 Recordset物件的資料欄。 • Error具體化在一個連線上的錯誤。 • Property具體化一個 ADO 物件的特性。

  7. ADO 集合物件 (Collections) • Errors所有在對連線上單一失敗回應所建立的Error 物件。 • Parameters所有關聯於Command 物件的Parameter 物件。 • Fields所有關聯於 Recordset物件的Field 物件。 • Properties所有關聯於Connection、 Command、Recordset或Field 的 Property 物件。

  8. ADO Objects

  9. ADO Object Model

  10. ADO 物件程式設計模式 • 連接至資料來源 (Connection)。您可以選擇性地開始交易。 • 您可以選擇性地建立物件，以代表 SQL 指令 (Command)。 • 您可以選擇性地以 SQL 指令的變數參數指定資料欄、表格，與數值 (Parameter)。 • 執行指令 (Command、Connection，或 Recordset)。 • 若指令傳回整列資料，將資料列儲存在儲存物件 (Recordset)。

  11. ADO 物件程式設計模式 (續) • 您可以選擇性地建立儲存物件的檢視，以便排序、篩選，與巡覽資料 (Recordset)。 • 新增、刪除，或改變行列的方式來編輯資料 (Recordset)。 • 若合適時，以儲存物件中的變更來更新資料 (Recordset)。 • 若使用交易，則須接受或拒絕交易期間所做的變更。結束交易 (Connection)。

  12. ADO Objects • Connection • Establish an active connection that allows us to gain access to data stored in a database • Command • Obtain records, execute SQL queries, or manipulate the data

  13. ADO Objects (Cont.) • Recordset • Access the data that is returned from executing an SQL query

  14. Connection Object Recordset Object Field collection Property collection Command object Parameter collection Property collection Property collection Error collection

  15. Connection Object 1/2 • Connection • Represent the physical link between applications and the remote database server • All communications between Recordset or Commands and the back-end database is negotiated through the connection

  16. Connection Object 2/2 • Transaction • Make the interaction with the database bulletproof • A series changes can be grouped together to look like a single, all-or-nothing change

  17. Connection Object: Basic Flow • Create an instance of the Connection object • Open a connection: Data Source Name (DSN) • Execute commands: SQL Command • Close the connection • Release the object resource

  18. Connection Object: Basic Commands • Create an Instance of Connection Object • VB: Dim conn As New ADODB.Connection • VBScript (ASP): Set conn=Server.CreateObject(“ADODB.Conneciton”) • Open a connection • conn.Open “DSN”, “username”, “password” • Execute an execution, the result, if any, is stored in a recordset • rs = conn.Execute “SQL COMMAND” • Close the connection • conn.Close • Free the object resource • Set conn = Nothing

  19. Data Source Name (DSN)

  20. VB Example: Connection Sub main() Dim conn As New ADODB.Connection conn.Open "dsnNW", "nw", "nw123" Set rs = conn.Execute("select * from 產品資料") While Not rs.EOF Debug.Print rs(1) & ":" & rs("單價") rs.MoveNext Wend rs.Close conn.Close Set rs = Nothing Set conn = Nothing End Sub

  21. Connection.Execute Method • 對於非 row-returning 指令字串： • connection.ExecuteCommandText, RecordsAffected, Options • 對於 row-returning 指令字串： • Set recordset = connection.Execute(CommandText, RecordsAffected, Options)

  22. Connection.Execute method • CommandText • SQL command • Stored procedure • A command or procedure that already exists in the source database system • RecordAffected • ADO will set it to the number of affected records

  23. Connection Object: Execute method • Options • AdCmdUnknown 0 • Unknown (default) • AdCmdText 1 • A text definition of a command (SQL) • adCmdTable 2 • The name of a table (create a recordset) • Set rs = conn.Execute(“產品資料”, adCmdTable) • adCmdStoreProc 3 • A stored procedure, or query(in the data source )

  24. Transaction • Begins a new transaction • conn.BeginTrans • Saves any changes and ends the transaction • conn.CommitTrans • Cancel any changes and ends the transaction • conn.RollbackTrans

  25. Connection Transaction • Perform a series of updates on a data source • Get the system to store up all the changes, and then commit them in one go • Before actually commit the change, the changes can be rolling back

  26. Set ObjCon=Server.CreateObject(“ADODB.Conneciton”) ObjCon.Open “DSN” ObjCon.BeginTrans ObjCon.Execute “SQL COMMAND” If Conditions Then ObjCon.CommitTrans // Serve any chanegs Else ObjConn.RollbackTrans // Cancel any changes End If ObjCon.Close Set ObjCon = Nothing

  27. Command Object • A Command object is a definition of a specific command that you intend to execute against a data source. • Provide methods and properties to manipulate individual commands • Collections • Parameters, Properties • Methods • CreateParameter, Execute • Properties • ActiveConnection, CommandText, CommandTimeout, CommandType, Name, Prepared, State

  28. Command Object • Methods • CreateParameter: Create a new Parameter object that can be appended to the Parameters collections • Execute: Execute the SQL statement or stored procedure • Property • ActiveConnection: Active one connection to be used by command object (DSN) • CommandText: Text of a command to be execute • CommandTimeout: No. of second for finishing a command • CommandType: adCmdText(1), adCmdTable(2), adCmdStoreProc(3),adCmdUnknown(4) • Prepared: Whether to create a prepared statement before execution (a command could be executed for multiple times)

  29. Command: Basic Commands • Create an instance of the Command object • Dim ObjCmd As New ADODB.Command • Create an active connection • ObjCmd.ActiveConnection = “DSN” • Execute a query • ObjCmd.CommandText = “SQL Command” • ObjCmd.CommandType = adCmdText ’SQL query • ObjCmd.Prepared = True ‘Compile the statement • ObjCmd.Execute • Release the resource used • ObjCmd.ActiveConnection = Nothing • set ObjCmd = Nothing

  30. Command Object: Execute Method ObjCmd.Execute [RecordAffected,] Parameters, Options • Execute the query specified by the CommandText property • RecordAffected and Options (same as Connection ) • Parameters part specify an array of parameters that are to be used while executing the query • Non-record-producing queries:Update, Insert, Delete • SQL select statement, table name, stroed procedured that returns records: A recordset is returned

  31. Parameter Object • A Parameter object represents a parameter or argument associated with a Command object based on a parameterized query or stored procedure. • Collections • Properties • Methods • AppendChunk • Properties • Attributes, Direction, Name, NumericScale, Precision, Size, Type, Value

  32. Command Object: Execute Example Dim ObjCmd As New ADODB.Command ObjCmd.ActiveConnection = “DSN” ObjCmd.CommandText = “StoredProc” ObjCmd.CommandType = AdCmdStoredProc ObjCmd.Execute Array(“tablename”, “State”) ObjCmd.ActiveConnection = Nothing • Execute the stored procedure, where the table name and the state are specified in the parameter list

  33. Command Object:Parameters Collection • The Command object contains an collection of Parameter objects ( Name: Parameters ) • Each query can take one or more parameters • Properties • Count: Indicate the total number of parameters in the Parameters collection • Methods • Parameters.Appendparameter • Append the new created Parameter object to the Parameters collection • Parameters.Deleteindex ‘Remove a parameter object from the collection • index: The name or ordinal index of the Parameter • Set para = Parameters.Item(index) 'Retrieve a parameter • Parameters.Refresh • Enforce the Parameters collection to read the schema information from the CommandText ( create parameters )

  34. Ordered Group of Parameter objects Command Object Parameters Collection Parameter Object Parameter Object Parameter Object

  35. Without Parameters ln="李" fn="大同" Set cmd = “Select * from employee where” Set cmd = cmd & “lname=“ & ln Set cmd = cmd & “And fname=“ & fn Set cm.CommandText = cmd cm.execute

  36. With Parameters Set cmd. CommandText = “Select * from employee where lname=? And fname=?” cm.parameters.refresh cm(0) = "李" cm(1) = "大同" cm.execute cm(0) = "王" cm.execute

  37. With Parameters Set cmd. CommandText = “Select * from employee where lname=? And fname=?” cm.parameters.refresh cm(0) = lname cm(1) = fname cm.execute cm(0) = lname2 cm.execute

  38. Recordset Object • Assign the query results to a Recordset object • Like a table in memory • Can create recordsets containing the data returned from that query • Can even create a recordset directly, without having to open a connection or execute a command first

  39. Recordset Fundamentals • Open the recordset Dim ObjRS As New ADODB.Recordset ObjRS.Open “SQL Command”, “dataSourceName” • Access the data field firstname = ObjRS(“fieldName”) ' Get the field with field name "fieldName" firstname = ObjRS.Fields(“fieldname”) ' the same as above n = ObjRS.Fields.Count ‘ get the number of fields secondField= ObjRS(2) ' get the 2nd Field of the record • Navigate the records while not ObjRS.EOF ‘do something with the data ObjRS.MoveNext 'Move the cursor to the next record Wend

  40. Recordset: Properties • AbsolutePage: Page of current position • AbsolutePosition: The original position of the current record • ActiveConnection: Active connection object • BOF: Before of first record ( True or False ) • Bookmark: Return/set a bookmark • CacheSize: Number of records cached • CursorLocation: Server, client, or client batch • CursorType: Forwarde, static, dynamic, keyset • EditMode: The editing status ( backward compatible with DAO) • EOF: End of file ( True or False ) • Filter: Hide types of records • LockType: Record locking for edits or updates • MaxRecords: Maximum records retrieved • PageSize: Number of pages total • RecordCount: Number of total records • Source: Source command • Status: Status of the last action

  41. CursorType • Dynamic: adOpenDynamic • Fully updateable recordset • All actions made by other users while the recordset is open are visible • All types of movement ( up and down ) • Keyset: adOpenKeyset • Updateable recordset • It prevents access to records that other users add after it was created • All types of movement • Static: adOpenStatic • Static non-updateable recordset ( retrieve data ) • Changes made by other users while the recordset is open aren’t visible • All types of movement • Forward-only: adOpenForwardOnly (default) • Static non-updateable recordset • Only Scroll forward through the records (MoveNext, GetRows) Actions: Insert, Update & Delete

  42. LockType • adLockReadOnly • Cannot alter the data ( no updates, inserts, or deletions ) • adLockPessimistic ( better at the database’s integrity ) • Record lock during editing • Lock out everyone else from the record you’re editing • Lock from the time of first change until call the Update method • adLockOptimistic • No lock during editing • Lock the record only during the call to Update • adLockBatchOptimistic • No lock during editing ( modify, insert, delete ) • Batch update: Lock the records only during the call to UpdateBatch

  43. Recordset: Method • AddNew: Create a new record in an updateable recordset • CancelBatch: Cancels a pending batch update • CancelUpdate: Cancel any changes made to the current or a new record • Clone: Create identical Recordset • Close: Close an open recordset • Delete: Delete the current record • GetRows: Get multiple records • Move: Move the position of the current record • MoveFirst, MoveLast, MoveNext, MovePrevious • NextRecordset: Move to the next set in multi-set query • Open: Establish a connection and execute the query • Requery: Refresh the data ( re-execute the original query ) • Resync: Synchronize data with server • Supports: Determine supported features • Update: Save any changes made to the current record • UpdateBatch: Write all pending batch updates to disk

  44. AddNew • ObjRS.AddNew [ Fields, Values ] • Fields: single or array of field names • Values: single or array of values • With no parameters • Add a blank record • With parameters • Add a completely defined new record in one statement • Update: To truly add the new record to the database

  45. Example Dim fields(2) Dim values(2) fields(0)=“Name” fields(1)=“Age” values(0)=“Wang” values(1)=1 ObjRS.AddNw fields, values

  46. Delete • ObjRS.Delete affect • adAffectCurrent (1) • Delete the current record (default ) • adAffectGroup (2) • Delete all records matching the current Filter property

  47. Recordset: Moving • ObjRS.Move n: Moving -n : move backward n records n: forward ( integer ) • ObjRS.AbsolutePosition n n: the current record number • ObjRS.MoveFirst • ObjRS.MoveLast • ObjRS.MoveNext • ObjRS.MovePrevious

  48. Update • Alter the field values of the record at the current position • ObjRS.Update [fields, values] • Update can be omitted for changes made to the current record • Most operations that change current record position cause the equivalent of a call to Update

  49. Recordset: Check for Empty • Forward-only cursor The current record position is set to the first record • Other types of recordset ObjRS.MoveFirst • Check the ObjRS.BOF and ObjRS.EOF properties

More Related