1 / 94

Accessing Data Sources with Visual Basic 6.0

Accessing Data Sources with Visual Basic 6.0. 12. William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer. William R. Vaughn. Microsoft Corporation Author: Hitchhiker’s Guide to Visual Basic and SQL Server Microsoft Press, 1996, 1997, 1998 billva@microsoft.com.

traugott-nigel
Download Presentation

Accessing Data Sources with Visual Basic 6.0

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. Accessing Data Sources with Visual Basic 6.0 12 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer

  2. William R. Vaughn Microsoft Corporation Author: Hitchhiker’s Guide to Visual Basic and SQL Server Microsoft Press, 1996, 1997, 1998 billva@microsoft.com

  3. Agenda Enterprise Development Strategies SQL Server 7.0 Microsoft Database Engine (MSDE) Making best use of Visual Basic 6.0 Tools DataEnvironment Designer Data Object Wizard Implementing ADO Solutions Effective Queries Processing Updates Managing Stored Procedures

  4. Enterprise Development Strategies:SQL Server 7.0 • Larger (8000 byte) page size • Longer Char, VarChar • Less need to record BLOBs in the DB • Unicode (DBCS) support • Nchar, NVarChar, Ntext • Better international support • New Substring functions • CONTAINS, FREETEXT • Better BLOB handling • Fetch starting at “nth” byte

  5. Enterprise Development Strategies:SQL Server 7.0 • Top [n] [Percent [n] ] • Preferred over RowCount • Can be used in subqueries • Enhanced local cursor support • New uniqueidentifier data type for storing a globally unique identifier (GUID) • Instead of Identity datatype • Great for disjoint server updates

  6. Enterprise Development Strategies:SQL Server 7.0 • Automatically manages admin functions • No need to: • Update statistics • Tune procedure cache • Run DBCC maintenance routines • Preallocate disk space (no disk inits) • Stretch TempDB, DB or Log space • Automatically runs backups • New GUI tools

  7. Enterprise Development Strategies:SQL Server 7.0 • Shares memory, disk space • Stretches or shrinks resources to match demand • Does not create TempDB SPs • Not needed to leverage existing query plans • Shares ad-hoc and stored procedures • Executing queries now re-entrant • Automatically senses reusable queries

  8. SQL Server 6.5 Cache management Stored Procedures SQL Server Procedure Cache 4 4 1st. instance is loaded 2nd. instance loaded 1st. instance finishes 3rd. instance re-uses first

  9. SQL Server 7.0 Cache management Stored Procedures SQL Server Managed Memory 4 4 1st. instance is loaded 2nd. instance shares first 1st. instance finishes 3rd. instance shares first Data Pages

  10. SQL Server 7.0 Ad hoc caching • If current SQL batch matches (exactly), SQL Server 7.0 uses cached plan. • Query #3 uses cached plan from #1, but #2 is recompiled… Query1: Insert MyTable values(1,0) Query2: Insert MyTable values(2,0) Query3: Insert MyTable values(1,0)

  11. Auto parameterizations • Engine guesses constants are parameters • Similar parameters share same plan • All three use the same cached plan created with Q1. Q1: Select * from employees where emp_id = 1000 Q2: Select * from employees where emp_id = 5 Q3: Select * from employees where emp_id = 1000

  12. Intelligent Caching • Tips: • Know what the parameters are? • Mark them (?) • Build parameters collection (in RDO/ADO) • Continue to use SPs • They just run faster • SPs are now re-entrant • Which makes them even faster

  13. Enterprise Development Strategies:SQL Server Desktop Edition • Microsoft SQL Server Desktop Edition • Runs on Windows 95, 98, 2000, NT 4.0 Workstation/Server • Duplicates functionality in coreNT SQL Server 7.0 (no OLAP, EQ, FTS etc.) • Performance “purposefully degraded” ~ 5 Users, • Supports (only) sockets (TCP/IP) • Supplied via • SQL Server 7.0 NT versions • Includes SQL Enterprise Manager tools • Same as those that ship for NT version

  14. Enterprise Development Strategies:MSDE • Microsoft Database Engine (MSDE) • Runs on Windows 95, 98, 2000, NT 4.0 WS/Svr • Duplicates functionality in coreNT SQL Server 7.0 (no OLAP, EQ, FTS etc.) • Performance “purposefully degraded” • Database limited to 2GB • Supplied through • Download/CD (now) • Office 2000

  15. Enterprise Development Strategies:MSDE • Visual Basic 6.0 “Plus” Pack • Includes SQL Server Developer Edition • To setup databases • To create permissions • Perform administrative functions • No “Setup.EXE” (requires batch operation) • No DB setup tools—SQLDMO samples • Get tools with SQL Server Desktop (on CD) • Download 45-Day “Trial” version of SQL Server Desktop Ed. • Get CreateDatabase tool from www.betav.com

  16. Enterprise Development Strategies:MSDE • Supports (only) sockets (TCP/IP)—no named pipes • Freely distributable (with a Visual Studio or Office 2000 app) • Does not replace Microsoft Jet Database Engine (Jet) • Requires SQL Server 7.0 to run in “per-seat” licensing mode. • See • www.msdn.microsoft.com/vstudio/msde • www.betav.com

  17. Enterprise Development Strategies:MSDE New standard “portable” SS7 DBMS No need to use DAO and RDO/ADO Same features, limited capacity Same tools, interfaces, programming model Easily scale up to SQL Server 7.0 NT Write once--run “anywhere” Replicated DB

  18. Making best use of Visual Basic 6.0 Tools: DED • Overview of DataEnvironment Technology • Introducing the Data View Window • Introducing the DataEnvironment Object • Solving problems with the DE

  19. Designers vs Wizards • Designers • RDO: UserConnection Designer • ADO: DataEnvironment Designer • Use dialogs to capture property settings • Create a binary .DSR file • Require a runtime • Construct data access COM objects at runtime • Wizards • Ask you a series of questions • Generate Visual Basic (editable) code

  20. DataEnvironment Technology • What is the “DE”? • Data Environment Designer created for VS 6.0 • GUI tool for ADO development • What does it do? • DE Creates layer over ADO object interface • How do I use it? • Exposed as programmable “data source” • Bindable to data-aware (ADO) controls • How can it help me? • Increases productivity • Builds team standard interface

  21. Introducing the Data View Window • What is the Data View Window? • Data Links persisted in Windows registry • Where is it exposed? • All Visual Studio tools • What is it used for? • Capture ADO Connection properties • Create DataEnvironment objects • View/create/modify: • schema, table(s), data, indexes, relationships, view(s), stored procedures, parameters… • Functionality based on provider

  22. Understanding the Data View Window New Data Link • Schema Diagrams New DE • Tables • Views • Stored Procedures • (Project) DE Connections

  23. Creating a Data Link • What’s a “Data Link”? • A persisted connection like a “DSN” • How do you create one? • Start Visual Basic 6.0 • Click “Add New Data Link” icon • Fill in Connection properties • How do I prevent extra dialogs? • Do not simply point to DSN for SQL Server or Oracle • Use ODBC or OLE DB provider

  24. Adding tables with a Data Link • Create, Open DB Diagram • Right-click to • add table • add related tables • arrange tables • Fill in or copy/paste column properties • Drag “foreign” key to “primary” key

  25. Introducing the DataEnvironment Object • DataEnvironment persisted with VB project • Saved as .DSR file • Loadable by other team members • Masks ADO object model • DE contains one or more Connections • Connections contain one or more Commands • Commands have parameters, properties, hierarchies • Managed via ADO properties, methods, events

  26. Understanding the DataEnvironment Object • Why should you use the DE? • To eliminate code–just like the UserConnection Designer • To generate queries and stored procedures • To “correctly” generate parameters • To expose complex queries to the entire team • Where should you use the DE? • On the client–not really suitable for the middle tier • Where you want to increase developer productivity • Acts as “data source” control • Other controls can bind to DE • Position current row via rsxxx “Move” methods

  27. Creating a DataEnvironment Object • Create new “Data” project • Use Data Link icon Next... • Right-click Project menu • Fill in Connection properties

  28. Setting DataEnvironment Connection Properties • Reference Properties - Connection window • Set properties not exposed in DE “wizard” • Command/ConnectionTimeout • Cursor location (server/client) • Passwords (design/runtime) • Prompt behavior • Run/DesignSaveAuthentication

  29. Using the Data Environment • Build Data Link with Data View Window • Create DataEnvironment object • Expand tables/views/stored procedures • Drag to DE • Set properties • Tune query • Drag to form • Provide navigation controls, code • Provide parameter controls, code

  30. Accessing ADO with the DataEnvironment Object DE object internals Exposed as ADO objects Drag tables/views/SPs from DE Connections lists

  31. DataEnvironment object ADO object DataEnvironment1 Connection Expenses “query” Expenses method/Command Recordset Field object Cost “field” Bindable result set rsExpenses Recordset Parameters Expenses.Parameters(.) DataEnvironment Object exposes ADO objects

  32. Binding to Data Aware Controls • Setting default controls by datatype • DataEnvironment “Options /Field Mapping” • Set control type for each datatype • Using the DOW to build UserControls

  33. Binding Options:DataGrid

  34. Binding Options:HierarchicalFlex Grid

  35. Binding Options:Bound Controls

  36. DataEnvironment QueriesTips and Techniques • Never use SELECT * • Always use WHERE clause • Choose the “right” (lightest weight) cursor • Limit rows to as few as possible • Never over 200, usually < 100 • Fetch More as needed • Update via stored procedure • Use asynchronous options

  37. Hierarchical Queries • In use everywhere • Customer-order-item • System-component-subsystem-part • State-Town-Street • DE uses ADO/OLE DB “Shape” provider SHAPE {SELECT * FROM "dbo"."AuthorsView"}AS AuthorsView APPEND ({SELECT Title, Year_Published, ISBN, CoverFilename FROM Titles} AS titles RELATE ) AS titles

  38. Hierarchical Queries • Observe “default” behavior with SQL Trace SELECT * FROM "dbo"."Authors" SELECT * FROM "dbo"."Title_Author" SELECT * FROM "dbo"."Titles" Be sure to limit focus of queries with views or focused queries Passing in parameters is tough... But getting easier with ADO 2.1

  39. Stored Procedures • Creating • Debugging • Installing into the DE • Passing parameters • Rebinding

  40. Creating Stored Procedures • Use Data View window • Create Stored Procedure • Design (against existing SP) • Refresh DE after changes • TSQL Debugger available to test • Data Object Wizard (DOW) • Use SQL Enterprise Manager … • Use Visual InterDev Data Tools • Code by hand

  41. Installing SPs into the DE • Drag from Data View window (DE explorer) • Insert Command under Connection • Set Command type • Point to SP • Set properties, but not parameter Value

  42. Passing Parameters to Stored Procedures • Don’t set in Data Environment Command properties... • Becomes a permanent part of query • Set while invoking Command as DE method • Recordset passed as new rsXXXXX object Set de = DataEnvironment1 de.AuthorsByYearBorn Text1.Text, Text2.text ShowADOData rsAuthorsByYearBorn

  43. Passing Parameters toStored Procedures • Set in WillExecute event • Parameter name depends on how query is parsed. • Note: this event called twice... Private Sub Connection1_WillExecute... pCommand("@YearLow") = Form1.Text1 pCommand("@YearHigh") = Form1.Text2 End Sub

  44. Passing Parameters to Stored Procedures (Subsequently) • No “Requery” available. • Must Close and re-execute query • … and rebind With DataEnvironment1 .rsAuthorsByYearBorn.Close .AuthorsByYearBorn Text1, Text2 End With RebindControls Me, "TextBox,", "AuthorsByYearBorn", DataEnvironment1

  45. Rebinding after Rebuilding Recordset • Recreating ADO Recordset requires rebinding to “simple” bound controls • “Complex” bound controls handled automatically Sub RebindControls(FormName, ControlType As String, CommandName, DE) Dim ctl As Control For Each ctl In FormName.Controls If InStr(ControlType & ",", TypeName(ctl) & ",") Then If ctl.DataMember = CommandName Then Set ctl.DataSource = DE End If End If Next End Sub

  46. Coding Move Methods • Default “simple” bound control binding does not include navigation controls • No drag-on control as in Visual InterDev Private Sub MoveNextButton_click() On Error Resume Next With DataEnvironment1.rsAuthorsByYearBorn .MoveNext If .EOF Then If Not .BOF Then .MoveLast End If End With End Sub

  47. Part II Making best use of Visual Basic 6.0 Wizards…

  48. Data Object Wizard • Building procedure-based applications • Constructing Procedures with Visual Basic version 6.0 • Building DOW-based Data classes • Building DOW-generated UserControls

  49. Building procedure-based applications • Why “procedure-based” designs? • Higher performance • Centralized management • Higher security

  50. Defining the problem • Data retrieval by key • Parameter-driven query • From any ADO data source • Resultsets displayed, manipulated by • Row • Grid • ComboBox • Updateable via procedure • Stored procedures • User-written ADO / DE Commands

More Related