CIS 338: Using ADO (ActiveX Data Objects)

1
CIS 338 Using ADO (ActiveX Data Objects)

• largely replaced by adonet.vb
• Dr. Ralph D. Westfall
• April, 2003

2
Universal Data Access

• ADO.NET is part of Microsoft's universal data
  access strategy
• Universal Data Access FAQs
• ODBC open database connectivity
• API standard for using relational databases
• API application programming interface
• includes "drivers" for Access, dBase, DB2, Excel,
  text, etc.
• Microsoft is leading ODBC backer

3
Universal Data Access - 2

• OLEDB DB object linking embedding
• next step beyond ODBC
• accesses relational databases with ODBC
• provides access to other data also
• e-mail, video, voice, images, web pages, etc.,
  etc.
• data is accessed "in place" (no need to first
  move it into a database)

4
OLEDB Concepts

• data store "persistent" collection of data
• data provider gets data from data store
• OLEDB "driver" database, email data, etc.
• OLEDB "has nothing to do with Object Linking and
  Embedding" (Word, Excel)
• data consumer application that uses data
• ADO (ActiveX Data Object) interfaces to data
  store through OLEDB

5
ADO.NET

• replaceS DAO, RDO and previous ADO
• can use ADO.NET with VB in code via object
  interface
• more powerful than DAO and previous ADO
• numerous objects with useful properties and
  methods

6
OLEDB with ADO.NET

• dataset object
• copy of data in memory, rather than connected to
  the database
• can have 1 to many DataTable objects
• ADO.NET OLEDB data providers
• SQLClient driver for SQL Server (Microsoft)
• OleDBClient driver for other databases

7
DataSet Object Data Tables

• "collections" of table(s) information
• rows, columns of table
• relationships with other tables
• constraints to manage additions/deletions from
  tables that are related to other tables
• data "views" that allow data to be seen in
  different ways than it is stored in table(s)

8
Data Provider Objects

• connection (to file or server)
• command object runs SQL or stored procedures in
  the database
• data reader (if need high performance)
• read only, forward only
• data adaptor (more capabilities)
• add, change, delete, relate tables

9
ADO.NET XML Data Transfer

• industry standard for transferring data
• like HTML, but can create other tags
• HTML lttitlegtsome textlt/titlegt
• XML ltpricegtvalue for pricelt/pricegt
• like HTML, can have tags within others
• HTML lthtmlgtltbodygtlt/bodygtlt/htmlgt
• XML ltnamegtltfirstgtlt/firstgtlt/namegt

10
XML Schema

• separate file that describes data in an XML file
• type of data numeric, text, etc.
• occurrences 0 or 1, or either to many
• relationships e.g., name includes 1st and last
  names
• other characteristics e.g., primary key

11
Exercise

• create your own tags for something you are
  familiar with
• food, pets, motor vehicles, video games, ways to
  communicate, college courses, etc.
• make sure that tags are "nested"
• open and close inside other "boxes"
• then fill in with some sample data

12
Database System Options

• SQL Server
• high performance
• not well-suited for using on one computer
• Microsoft Data Engine (MSDE)
• chopped down version of SQL Server
• can work with SQL Server files
• but also can work with Microsoft Access

13
Creating an MSDE Database

• note may have problems on network
• start Access
• FilegtNew, NewgtProject (New Data)
• Access creates a MSDE database (.adp)
• data storage is different, but you can work with
  it like an Access database (.mdb)
• can edit data or upload files as with .mdb

14
Connecting ADO to Access

• start a new .NET Windows application
• ViewgtServer Explorer
• right click Data ConnectionsgtAdd Connection
• click Provider tabgtMicrosoft Jet 4.0 OLE DB
  ProvidergtNext button
• browse for Access databasegtOK

15
Using Data Connection

• viewing data in a table
• in Server Explorer, expand Data Connections,
  Access, Tables icons
• right click on a TablegtRetrieve Data from Table
• close this preview window

16
Create a DataAdapter

• in Server Explorer, click and drag a Table onto
  the form
• note 2 new components in tray below
• right click DataAdaptorgtGenerate Dataset

17
DataGrid with DataAdapter

• add a DataGrid
• set DataSource property to DataSet
• in Form_Load, use .Fill method of DataAdapter
  with DataSet as argument
• Click Start to view DataGrid

18
List/ComboBox with DataSet

• drag/drop ListBox onto form
• set DataSource to DataSet (drop down)
• set DisplayMember to a field in table
• add .Fill method of DataAdapter
• see previous slide if didn't do this already
• note that data is in same order as in database
  (not sorted)

19
DataView List/ComboBox

• DataView makes it possible to sort and do other
  things with database data
• drag/drop DataView object to form/tray
• set DataView Table property to a table from the
  DataSet (drop down)
• type in a field name for Sort property
• see field names in Server Explorer

20
DataView List/ComboBox - 2

• drag/drop ComboBox onto form
• set DataSource to DataSet (drop down)
• set DisplayMember to a field in table
• add .Fill method of DataAdapter
• see previous slide if didn't do this already
• can set .ListIndex property to -1 so that no item
  is selected at start (ListBox too)

21
SQL as Source of Data

• expand DataAdaptor Select Command property to see
  CommandText code
• can modify this SQL by changing the line, or by
  clicking to see Query Builder
• can (un)select fields in Output column
• can add a WHERE condition value
• ? in the Criteria column(s) prompts user

22
Binding Other Controls to Data

• draw TextBox or Label on form
• expand (DataBindings) property
• click Text, and select table/field from drop down
• add code to load DataSet with just the item input
  or selected in another control

23
Binding Other Controls - Code
24
Setting Up ADO with a DSN

• Windows (not VB) StartgtSettingsgtControl
  PanelgtAdministrative ToolsgtData Sources
• click Data Sources (32 bit) User DSN tab
• select MS Access Database, click Add
• select Microsoft Access Driver, click Finish
• click select, choose database (e.g., BIBLIO)
• type in Data Source Name (DSN) and description,
  then click OK
• put in what you want to call DSN, your description

25
Using ADO Data Control

• right click toolbox, select Components
• click Microsoft ADO Data Control 6.0, OK
• click Adodc control on toolbar, draw on form
• right click on control to see Properties page
• first set up Source of Connection (General tab)
• select Use ODBC Data Source Name and select the
  DSN you just created (or Use Connection
  StringgtBuild etc. with latest Microsoft Jet
  Access Provider)

26
Using ADO Data Control - 2

• select RecordSource tab
• select 1 of following Command Types
• adCmdText need to type in SQL query
• adCmdTable need to select the table name
• adCmdStoredProcedure need to select the
  procedure name
• enter SQL, or select Table or stored procedure
  (e.g., Publishers), click OK

27
Using ADO Data Control - 3

• add controls (e.g., 2 textboxes) to form
• bind controls to data fields (like with DAO
  e.g., Name, City)
• set DataSource properties name of data control
  from dropdown list
• set DataField properties to field names from
  dropdown list
• run project, scroll with ADO data control

28
Using Code ADODC Control

• 'command button event code (need a 3rd
• 'textbox to run this)
• Dim sState as String, sSQL as String
• sState Text3.Text '3rd TextBox
• sSQL "select from Publishers"
• If sState ltgt "" Then sSQL sSQL _
• " WHERE State '" sState "'" 'space
• Adodc1.CommandType adCmdText
• Adodc1.RecordSource sSQL
• Adodc1.Refresh

29
Data Grid Control

• displays database data on a form like in a
  spreadsheet
• can use this layout to edit data in the database

30
Using DataGrid Control

• right click toolbox, select Components
• click Microsoft ADO Data Control 6.0
• click Microsoft Data Grid Control 6.0, OK
• click Adodc control on toolbar, draw on form
• right click on Adodc control to see Properties
• 1st set Source of Connection (General tab)
• select Use ODBC Data Source Name and select DSN
  (BIBLIO), or use connect string
• select RecordSource tab
• select Command Type (adCmdTable), select Table
  name (Publishers)

31
Using DataGrid Control - 2

• click toolbar's DataGrid, draw on form
• using regular Properties window, set DataSource
  to connection being used
• then right click on DataGrid, select Retrieve
  fields

32
Using DataGrid Control - 3

• DataGrid properties
• use regular Properties window to set some
  properties
• e.g., Caption
• right click on grid for Properties Pages for
  others
• General tab
• ColumnHeaders on or off
• Enabled allows user to scroll, select, modify
• AllowAddNew, AllowDelete, or AllowUpdate user
  can add, delete, or change database contents

33
Using DataGrid Control - 4

• Properties Pages (continued)
• Keyboard tab
• allow use of arrow keys
• set TabAction tab key behavior (reference)
• other tabs
• Color, Font, Format (number, date/time, etc.
  like in Excel)

34
Editing a DataGrid

• right click grid, select Edit, right click again
• use commands to change grid
• cut, paste, delete, etc.
• can split a grid to get a new user window
• use Property Pages Layout tab to uncheck Visible
  for individual columns in different splits

35
Using ActiveX Data Objects

• ProjectgtReferencesgt and then check
• Microsoft ActiveX Data Objects 2.x Library
• e.g., x 2.7 if you have it
• provides ADOR (recordset) objects
• Recordset, Field and Property objects
• provides ADODB objects
• above objects plus Connection, Command, Parameter
  and Error objects

36
Set Up a Data Source in Code

• can use a connection object to access a database
  with a DSN
• Dim cn As ADODB.Connection
• Set cn New ADODB.Connection
• cn.ConnectionString"DSNname"
• cn.Open

37
Setting Up a Data Source - 2

• connecting without a DSN (p. 629)
• Dim cn As ADODB.Connection '(General)
• Set cn New ADODB.Connection
• cn.ConnectionString _
• "DriverMicrosoft Access Driver
• (.mdb)DBQC\path\file.MDB"
• 'need just one space before (.mdb)
• 'NO spaces!! in DriverMicrosoft...
• cn.Open

38
Using Recordsets in Code

• Dim cn As ADODB.Connection
• Dim rsname As ADODB.Recordset
• 'add to (General Declarations)
• Set cn New ADODB.Connection
• cn.ConnectionString _
• "DriverMicrosoft Access Driver
• (.mdb)DBQC\path\file.MDB"
• cn.Open

39
Using Recordsets in Code - 2

• Const sSQL "SELECT FROM table"
• Set rsname New ADODB.Recordset
• rsname.ActiveConnection cn
• rsname.Source sSQL
• rsname.Open
• rsname.MoveFirst
• Print " " _
• rsname.Fields("field")

40
Multiple Ways to Use Objects

• Set rsname New ADODB.Recordset
• rsname.ActiveConnection cn
• rsname.Source sSQL
• rsname.Open
• OR
• Set rsname cn.Execute(sSQL)

41
Recordset Lock Types

• rsname.LockType
• adLockReadOnly can't add, change, or delete
• adLockPessimistic record locked while working
  on it
• adLockOptimistic record locked when submitted
  to database (but rejected if another user already
  has a lock on it)
• adLockBatchOptimistic multiple records
  submitted, locked individually while being
  updated

42
Recordset Cursor Types

• rsname.CursorType
• adOpenForwardOnly fast, but one-way
• adOpenKeySet user can see changes by other
  users, but not new records or deletions
• adOpenDynamic slowest, but user can see all
  modifications by other users
• adOpenStatic can't see any modifications

43
Updating Databases with Recordsets in ADO

• Set rsname New ADODB.Recordset
• rsname.CursorType
• rsname.LockType
• rsname.Source table, SQL
• rsname.ActiveConnection cn
• rsname.Open

44
Updating Databases with Recordsets in ADO - 2

• 'changing field values
• rsname.Fields("field")
• rsname.Update '1 command
• 'need 2 commands to change in DAO
• 'adding new records (2 commands)
• rsname.AddNew '1st command
• rsname.Fields("field")
• rsname.Update '2nd command

45
Disposing of Objects

• to conserve resources, get rid of objects when
  finished with them
• rsname.Close
• 'disconnects from database
• Set rsname Nothing
• 'removes from memory
• cn.Close
• Set cn Nothing

46
Data Source Name (DSN)

• DSNs allow you to set up "virtual addresses" on a
  computer
• DSN "points to" the actual physical path
• can change physical path for a DSN (e.g., when
  move application to another computer) but use
  same DSN in code
• DSNs can make applications more portable
• but DO NOT use DSNs in your projects for CIS 338
  (they are not on my computer!)