CIS 338: Using ADO (ActiveX Data Objects)

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 • 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 • 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 • 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 • 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 • 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 • "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 • 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 • 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 • 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 • 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 • 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 • 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 • 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 • 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 • 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 • 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 • 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 • 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 • 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 • 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 • 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

Binding Other Controls - Code

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 • 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 • 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 • 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 '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 • displays database data on a form like in a spreadsheet • can use this layout to edit data in the database

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 • 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 • 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 • 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 • 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 • 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 • 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 • 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 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 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 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 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 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 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 '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 • 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) • 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!)

CIS 338: Using ADO (ActiveX Data Objects)