Cis 338 using ado activex data objects
Download
1 / 46

CIS 338: Using ADO ActiveX Data Objects - PowerPoint PPT Presentation


  • 115 Views
  • Updated On :

CIS 338: Using ADO (ActiveX Data Objects). [largely replaced by adonet.vb] Dr. Ralph D. Westfall April, 2003. Universal Data Access. ADO.NET is part of Microsoft's universal data access strategy Universal Data Access FAQs ODBC: open database connectivity

Related searches for CIS 338: Using ADO ActiveX Data Objects

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'CIS 338: Using ADO ActiveX Data Objects' - ostinmannual


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Cis 338 using ado activex data objects l.jpg

CIS 338: Using ADO (ActiveX Data Objects)

[largely replaced by adonet.vb]

Dr. Ralph D. Westfall

April, 2003


Universal data access l.jpg
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


Universal data access 2 l.jpg
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)


Oledb concepts l.jpg
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


Ado net l.jpg
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


Oledb with ado net l.jpg
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


Dataset object data tables l.jpg
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)


Data provider objects l.jpg
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


Ado net xml data transfer l.jpg
ADO.NET: XML Data Transfer

  • industry standard for transferring data

  • like HTML, but can create other tags

    • HTML: <title>[some text]</title>

    • XML: <price>[value for price]</price>

  • like HTML, can have tags within others

    • HTML: <html><body></body></html>

    • XML: <name><first></first></name>


Xml schema l.jpg
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


Exercise l.jpg
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


Database system options l.jpg
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


Creating an msde database l.jpg
Creating an MSDE Database

  • note: may have problems on network

  • start Access

  • File>New, New>Project (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


Connecting ado to access l.jpg
Connecting ADO to Access

  • start a new .NET Windows application

  • View>Server Explorer

  • right click Data Connections>Add Connection

  • click Provider tab>Microsoft Jet 4.0 OLE DB Provider>Next button

  • browse … for Access database>OK


Using data connection l.jpg
Using Data Connection

  • viewing data in a table

  • in Server Explorer, expand Data Connections, Access, Tables icons

  • right click on a Table>Retrieve Data from Table

  • close this preview window


Create a dataadapter l.jpg
Create a DataAdapter

  • in Server Explorer, click and drag a Table onto the form

    • note 2 new components in tray below

  • right click DataAdaptor>Generate Dataset


Datagrid with dataadapter l.jpg
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


List combobox with dataset l.jpg
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)


Dataview list combobox l.jpg
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


Dataview list combobox 2 l.jpg
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)


Sql as source of data l.jpg
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


Binding other controls to data l.jpg
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



Setting up ado with a dsn l.jpg
Setting Up ADO with a DSN

  • Windows (not VB): Start>Settings>Control Panel[>Administrative Tools>Data 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


Using ado data control l.jpg
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 String>Build etc. with latest Microsoft Jet [=Access] Provider)


Using ado data control 2 l.jpg
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


Using ado data control 3 l.jpg
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


Using code adodc control l.jpg
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 <> "" Then sSQL = sSQL & _

" WHERE State = '" & sState & "'" 'space

Adodc1.CommandType = adCmdText

Adodc1.RecordSource = sSQL

Adodc1.Refresh


Data grid control l.jpg
Data Grid Control

  • displays database data on a form like in a spreadsheet

  • can use this layout to edit data in the database


Using datagrid control l.jpg
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)


Using datagrid control 2 l.jpg
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


Using datagrid control 3 l.jpg
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


Using datagrid control 4 l.jpg
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)


Editing a datagrid l.jpg
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


Using activex data objects l.jpg
Using ActiveX Data Objects

  • Project>References> 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


Set up a data source in code l.jpg
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="DSN=[name]"

    cn.Open


Setting up a data source 2 l.jpg
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 = _

    "Driver=Microsoft Access Driver

    (*.mdb);DBQ=C:\[path]\[file].MDB;"

    'need just one space before (*.mdb)

    'NO spaces!! in Driver=Microsoft...

    cn.Open


Using recordsets in code l.jpg
Using Recordsets in Code

Dim cn As ADODB.Connection

Dim rs[name] As ADODB.Recordset

'add to (General Declarations)

Set cn = New ADODB.Connection

cn.ConnectionString = _

"Driver=Microsoft Access Driver

(*.mdb);DBQ=C:\[path]\[file].MDB;"

cn.Open


Using recordsets in code 2 l.jpg
Using Recordsets in Code - 2

Const sSQL = "SELECT * FROM [table]"

Set rs[name] = New ADODB.Recordset

rs[name].ActiveConnection = cn

rs[name].Source = sSQL

rs[name].Open

rs[name].MoveFirst

Print "[ ]= " & _

rs[name].Fields("[field]")


Multiple ways to use objects l.jpg
Multiple Ways to Use Objects

Set rs[name] = New ADODB.Recordset

rs[name].ActiveConnection = cn

rs[name].Source = sSQL

rs[name].Open

  • OR

    Set rs[name] = cn.Execute(sSQL)


Recordset lock types l.jpg
Recordset Lock Types

rs[name].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


Recordset cursor types l.jpg
Recordset Cursor Types

rs[name].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


Updating databases with recordsets in ado l.jpg
Updating Databases with Recordsets in ADO

Set rs[name] = New ADODB.Recordset

rs[name].CursorType = [ ]

rs[name].LockType = [ ]

rs[name].Source = [table, SQL]

rs[name].ActiveConnection = cn

rs[name].Open


Updating databases with recordsets in ado 2 l.jpg
Updating Databases with Recordsets in ADO - 2

'changing field values

rs[name].Fields("[field]") = [ ]

rs[name].Update '1 command

'need 2 commands to change in DAO

'adding new records (2 commands)

rs[name].AddNew '1st command

rs[name].Fields("[field]") = [ ]

rs[name].Update '2nd command


Disposing of objects l.jpg
Disposing of Objects

  • to conserve resources, get rid of objects when finished with them

    rs[name].Close

    'disconnects from database

    Set rs[name] = Nothing

    'removes from memory

    cn.Close

    Set cn = Nothing


Data source name dsn l.jpg
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!)


ad