Client Server Data Client Server Data Access Techniques with Access Techniques with

Visual FoxProVisual FoxPro

E-CSE-CS

Mike FeltmanF1 Technologies

Who Am I?Who Am I?• mfeltman@f1tech.commfeltman@f1tech.com• F1 Technologies PartnerF1 Technologies Partner• Visual FoxExpress DeveloperVisual FoxExpress Developer• Former Fox Software EmployeeFormer Fox Software Employee• Application ProgrammerApplication Programmer

Our GoalsOur Goals• Provide you with an overview of each Provide you with an overview of each

of the data access techniquesof the data access techniques• Make you aware of the pros and cons Make you aware of the pros and cons

of the various techniquesof the various techniques• Equip you with the information Equip you with the information

necessary to make an informed necessary to make an informed choice of data access technique.choice of data access technique.

AgendaAgenda• Overview of Various Data Access Overview of Various Data Access

TechniquesTechniques• Evaluation CategoriesEvaluation Categories

– Ease of UseEase of Use– Performance and ScalabilityPerformance and Scalability– MaintainabilityMaintainability– FlexibilityFlexibility– Security Concerns/IssuesSecurity Concerns/Issues

• Mixing and Matching TechniquesMixing and Matching Techniques

The Data Access MethodsThe Data Access Methods• Remote ViewsRemote Views• Dynamic SQL Pass-ThroughDynamic SQL Pass-Through• Stored ProceduresStored Procedures• CursorAdapterCursorAdapter• ADOADO• XMLXML

Ease of UseEase of Use• UI and Tool Support UI and Tool Support • Data BindingData Binding• NormalcyNormalcy• CompletenessCompleteness• Plays Well With OthersPlays Well With Others• Distribution DifficultiesDistribution Difficulties

Remote ViewsRemote Views• UI & Tools Support UI & Tools Support

– View Builder, GenDBCView Builder, GenDBC– eView & View EditoreView & View Editor– xCasexCase– DBCXDBCX

• Data BindingData Binding– Full Support in Data EnvironmentFull Support in Data Environment

• NormalcyNormalcy– Most similar to tablesMost similar to tables

Remote ViewsRemote Views• CompletenessCompleteness

– Full Language Support in VFPFull Language Support in VFP– Limited Access to Back-end FeaturesLimited Access to Back-end Features

• Plays Well With Others Plays Well With Others – Very well, Can share connectionsVery well, Can share connections

• DistributionDistribution– DBC DistributionDBC Distribution

Dynamic SPT – Ease of UseDynamic SPT – Ease of Use• UI and Tool SupportUI and Tool Support

– None Native, Some 3None Native, Some 3rdrd Party Party• Data BindingData Binding

– Manual or TricksManual or Tricks• NormalcyNormalcy

– Requires More Code to work like a View Requires More Code to work like a View (CursorSetProp())(CursorSetProp())

Dynamic SPT – Ease of UseDynamic SPT – Ease of Use• CompletenessCompleteness

– Access to full back-endAccess to full back-end• Plays well with othersPlays well with others

– Very well except for ADOVery well except for ADO• Distribution IssuesDistribution Issues

– NoneNone

Stored Procedures – Ease of Stored Procedures – Ease of UseUse

• UI and Tool SupportUI and Tool Support– None in VFPNone in VFP– DataClasDataClas– Enterprise Manager and QueryAnalyzerEnterprise Manager and QueryAnalyzer– Mike Levy’s Classes from various Mike Levy’s Classes from various

conferencesconferences

Stored Procedures – Ease of Stored Procedures – Ease of UseUse

• CompletenessCompleteness– Access to everything the back-end has Access to everything the back-end has

to offerto offer– Great for Multiple and/or Complex Great for Multiple and/or Complex

StatementsStatements• Distribution DifficultiesDistribution Difficulties

– Must update stored proceduresMust update stored procedures– Code Conflict ResolutionCode Conflict Resolution

CursorAdapter with ODBC – CursorAdapter with ODBC – Ease of UseEase of Use

• Similar to Remote ViewsSimilar to Remote Views• UI and Tool SupportUI and Tool Support

– CursorAdapter & DE BuilderCursorAdapter & DE Builder– 33rdrd Party Tools Party Tools

• NormalcyNormalcy– Just like a Table or ViewJust like a Table or View

CursorAdapter with ODBC – CursorAdapter with ODBC – Ease of UseEase of Use

• CompletenessCompleteness– More code than remote views, less than More code than remote views, less than

other techniquesother techniques• Plays Well With OthersPlays Well With Others

– Easiest Easiest

CursorAdapter with CursorAdapter with ADO/OLEDB – Ease of UseADO/OLEDB – Ease of Use

• Similar to CursorAdapter with ODBCSimilar to CursorAdapter with ODBC• Plays Well With OthersPlays Well With Others

– Cannot share connection with other data Cannot share connection with other data access strategiesaccess strategies

• Distribution ConcernsDistribution Concerns– MDACMDAC

Performance and ScalabilityPerformance and Scalability• The differences between various The differences between various

techniques are generally minor.techniques are generally minor.• Server, Workstation & LAN/WAN Server, Workstation & LAN/WAN

performance and scalability must ALL be performance and scalability must ALL be taken into accounttaken into account

• Size (& Contents) MattersSize (& Contents) Matters• Query optimization in SQL Server is much Query optimization in SQL Server is much

different than in VFP. Syntax vs. Content-different than in VFP. Syntax vs. Content-based optimization. based optimization.

Performance and ScalabilityPerformance and Scalability• Results can vary widely for the same Results can vary widely for the same

tests.tests.• Those with much more experience than Those with much more experience than

me with large databases (million rows+) me with large databases (million rows+) all all swear Stored Procedures produce the swear Stored Procedures produce the best performance.best performance.

• Microsoft tends to get very angry at those Microsoft tends to get very angry at those who publish SQL Server benchmarks.who publish SQL Server benchmarks.

MaintainabilityMaintainability• Code RequirementsCode Requirements

– How much code?How much code?• Maintenance PointsMaintenance Points

– How many?How many?– Easy to find and work with?Easy to find and work with?

• ToolsTools– Are there any?Are there any?

• Schema ChangesSchema Changes– What happens when schema changes?What happens when schema changes?

Maintainability - Remote Maintainability - Remote ViewsViews

– Code RequirementsCode Requirements• LittleLittle

– Maintenance PointsMaintenance Points• Primarily the views themselvesPrimarily the views themselves• Custom classesCustom classes

Maintainability - Remote Maintainability - Remote ViewsViews

– ToolsTools• View Designer, eView, ViewEditor, xCase, View Designer, eView, ViewEditor, xCase,

GenDBCGenDBC– Schema ChangesSchema Changes

• Use xCaseUse xCase• Unit test remote viewsUnit test remote views

Maintainability - Dynamic Maintainability - Dynamic SPTSPT

– Code RequirementsCode Requirements• One good base classOne good base class• App specific subclassesApp specific subclasses

– Maintenance PointsMaintenance Points• Custom Class CodeCustom Class Code

Maintainability - Dynamic Maintainability - Dynamic SPTSPT

– ToolsTools• NoneNone

– Schema ChangesSchema Changes• Maybe do nothingMaybe do nothing• Unit test data access codeUnit test data access code

Maintainability - Stored Maintainability - Stored ProceduresProcedures

– Code RequirementsCode Requirements• VFP – One good set of classesVFP – One good set of classes• 4 or more Procedures for basically every 4 or more Procedures for basically every

tabletable– Maintenance PointsMaintenance Points

• Custom classesCustom classes• Actual Stored ProceduresActual Stored Procedures

Maintainability - Stored Maintainability - Stored ProceduresProcedures

– ToolsTools• None for VFPNone for VFP• DataClas, 3DataClas, 3rdrd Party Party

– Schema ChangesSchema Changes• All ProceduresAll Procedures• Unit Test all Stored ProceduresUnit Test all Stored Procedures

Maintainability - Maintainability - CursorAdaptersCursorAdapters

– Code RequirementsCode Requirements• One good base classOne good base class

– Maintenance PointsMaintenance Points• Individual subclassesIndividual subclasses

– ToolsTools• 33rdrd party, DE and CA Builder party, DE and CA Builder

– Schema ChangesSchema Changes• Modify CA propertiesModify CA properties• Unit test all cursor adaptersUnit test all cursor adapters

FlexibilityFlexibility• Remote ViewsRemote Views

– Inflexible without other techniquesInflexible without other techniques• Dynamic SPTDynamic SPT

– Highly flexible with custom classes, but Highly flexible with custom classes, but overly difficult to writeoverly difficult to write

FlexibilityFlexibility• Stored ProceduresStored Procedures

– Very FlexibleVery Flexible– Much like Dynamic SQL Pass-ThroughMuch like Dynamic SQL Pass-Through

• Cursor AdapterCursor Adapter– Most Flexible – mix & match with all Most Flexible – mix & match with all

techniquestechniques– Native class provides appropriate Native class provides appropriate

locations for customizationlocations for customization

SecuritySecurity• Stored Procedures – Execute Rights Stored Procedures – Execute Rights

on Stored Procedures Onlyon Stored Procedures Only• All Others – Provide direct rights to All Others – Provide direct rights to

update tables or viewsupdate tables or views• DSN Based Connection vs. DSN Based Connection vs.

Connection String is more importantConnection String is more important• Use Application RolesUse Application Roles

Mixing and MatchingMixing and Matching• CURSORSETPROP()CURSORSETPROP()

– Make SPT Cursor UpdatableMake SPT Cursor Updatable– Load data into remote viewLoad data into remote view

• USE … CONNSTRING (handle)USE … CONNSTRING (handle)• CursorAdapter.CursorAttachCursorAdapter.CursorAttach• SQLSTRINGCONNECT()SQLSTRINGCONNECT()

SummarySummary• Be Informed!Be Informed!• It is Not an “All or Nothing” SituationIt is Not an “All or Nothing” Situation• Choose the Best Approach for the Choose the Best Approach for the

ApplicationApplication• Mix and Match When Necessary.Mix and Match When Necessary.

Thank you!Thank you!