190 likes | 337 Views
E-DATA The Datasession. Venelina Jordanova Venelina@VandU.eu. What about Venelina & Uwe?. New company: V&U Ltd. www.VandU.eu Info@VandU.eu Managers: Venelina & Uwe Software projects with VFP Visual Extend High Quality for low prices Development in Varna, Bulgaria. Overview.
E N D
E-DATAThe Datasession Venelina Jordanova Venelina@VandU.eu
What aboutVenelina & Uwe? • New company: V&U Ltd. • www.VandU.eu • Info@VandU.eu • Managers: Venelina & Uwe • Software projects with VFP • Visual Extend • High Quality for low prices • Development in Varna, Bulgaria
Overview Data retrieval control Using parameters Overcoming VFP limits The place of CursorAdapter class in the application architecture.
Accessing data • How to reach the data • Do not use VFP CA Builder • Where do you want to connect today? • http://connectionstrings.com/
ODBC or ADO? • ODBC SQLSTRINGCONNECT() or SQLCONNECT() • ADODB loConnDataSource = CREATEOBJECT('ADODB.Connection') loConnDataSource.ConnectionString = [Provider=SQLOLEDB.1...] loConnDataSource.Open() This.DataSource = CREATEOBJECT('ADODB.RecordSet') This.DataSource.ActiveConnection = loConnDataSource
Accessing data • Controlling data retrieval • CursorFill() and CursorRefresh() methods • BeforeCursorFill() and BeforeCursorRefresh()events • Make sure to pass all the editable parameters by reference • UseCursorSchema and NoDataOnLoad properties
Handling data access errors By default CursorAdapter class does not raise error events Why does VFP browse the cursor after select? Considering remote data engine specifics ANSI SQL functions Accessing data
The power of parameters • Reducing the traffic • Using parameterized select command • make sure all the parameters exist and have correct value at the time when object initializes and every time when CursorFill or CursorRefresh is executed • Prefix parameter variables with “?” when working with ODBC provider • ADOCommand parameters when using ADODB provider
The power of parameters • Complicated parameterized select command
The power of parameters • Data security • Narrow data range • User access rights • Record level security
Dancingdata • Working with a large table • Split table into two (or more) cursors • Use one of the cursors as main alias • Manage relation between cursors • Save data from all the cursors • Update using same ID • Saving new record • Insert only once • Deleting a record • Ensure data consistency • Use transactions
Dancingdata • Error: Statement too long. • SYS(3055) • = SYS(3055, 8 * MIN(40, FCOUNT( ))) • WhereType property • 4 or DB_KEYANDTIMESTAMP (from FOXPRO.H) • Why not using it always? • Lost update conflicts management
Dancingdata • Joined tables • Properties • Tables • UpdateNameList • UpdatableFieldList • Delete a record is deleting in all the joined tables • DELETE commands are generated for all the tables that CursorAdapter object updates
CursorAdapter in N-Tier architecture • Data layer? • Data transformations • Keeping data integrity • Handling retrieve and update errors • Business rules layer? • Record level validation • BeforeCursorUpdate(), BeforeInsert(), BeforeUpdate(), and BeforeDelete() events
Auto-generated ID field values • VFP • GETAUTOINCVALUE() Function • SQL • @@IDENTITY system variable • MS SQL functions SCOPE_IDENTITY() and IDENT_CURRENT( 'table_name' )
Keeping data integrity • Transactions • UseTransactions property • Data engine feedback • properties InsertCmdRefreshKeyFieldList InsertCmdRefreshFieldListInsertCmdRefreshCmd • Only when BatchUpdateCount = 1
Close data all • Data access • Optimized • Error free • Stable • Detailed control • Data retrieval • Saving data
Thank you! Please remember to fill out your evaluation.