Session 4 From Access Forms to Windows Forms .NET

1. Session 4From Access Forms to Windows Forms .NET Adam Cogan Database Architect ssw.com.au

2. To From

3. Overview 1 2 3 4 ü ü ü Access 97 to Access 2003 Access to SQL Server Access to Reporting Services Access to Windows Forms .NET

4. Agenda • Current Problems • What’s New in .NET • Lab: Migrating from Access Forms to Windows Forms .NET Breezers Drink Receipt From Outback Oz

5. Assumptions • Backend in SQL Server 2000 • Using Enterprise Manager for data management • Access forms front-end • Reports using Reporting Services • Some VBA knowledge

10. Session Prerequisites (Current Problems) • “Our application stopped working on Mary’s machine when we installed the new version of Office” • “Our Access database got corrupted and we can’t open it” • “Our Access forms run too slowly - we want a small, fast EXE” • “Can we stop the continual polling of SQL Server so we can scale this application?” • “We want to integrate with other systems”

11. Enter .NET • No need for Access runtime or Office for end-user (1) • Produce an EXE which doesn’t get corrupted like an MDB (2) • The EXE is small and runs fast (3) • Only explicit updates to database (4) • Web Service Support are built in (use an open XML-based architecture) (5)

12. Problems with Bound Access Forms Advantage – Database Connections are Controllable In Access • Forms perform database queries in the background • Cannot be controlled In .NET • Full control over database connections and queries • Improved performance and scalability Not as smooth a ride

13. Problems with Bound Access Forms Advantage – Database Connections are Controllable In Access In .NET

14. Differences In Form Design Advantage – A Shallow Learning Curve for Access Developers (1 of 2)

15. Differences In Form Design Advantage – A Shallow Learning Curve for Access Developers (2 of 2)

16. Northwind .NET

17. Differences In Form Design New Feature – Anchoring Controls

18. Differences In Form Design New Feature – Docking Controls

19. Differences In Form Design Advantage – Powerful New Controls (1 of 2)

20. Differences In Form Design Advantage – Powerful New Controls (2 of 2)

21. Data Forms In Access • Get Data – Make Queries

22. Data Forms In Access 2. Bind – Set the One RecordSource

23. Data Forms In Access 3. Bind – Set the ControlSource for all bound controls

24. Data Forms In .NET • Get Data – Create Data Components (DataSets and DataAdapters)

25. Data Forms In .NET 2. Bind – Set the Many Referenced DataSets

26. Data Forms In .NET 3. Bind – Set the DataBindings for all bound controls

27. Differences In Form Design Disadvantage – Database Updates Are Not Done Automatically In Access • Changes made on forms are automatically saved In .NET: • Add a save button • Call DataAdapter.Update(DataSet) in the OnClick event

28. Differences in Application Design Advantage – All .NET Solution Items are Described in Plain Text In Access • Everything is in the MDB • If damaged, everything stops working In .NET • All solution items are in plain text • Forms described in XML • Low chance of corruption • Lightweight

29. Differences In Form Design Difference – Data Views

30. Navigation PrivateSub nextRecord_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles nextRecord.Click ' Check that the user isn't on the last record. IfNotMe._ordersManager.Position = Me._ordersManager.Count - 1 Then _movingRecords = True Me._ordersManager.Position += 1 _movingRecords = False EndIf EndSub PrivateSub previousRecord_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles previousRecord.Click ' Check that the user isn't on the first record. IfNotMe._ordersManager.Position = 0 Then _movingRecords = True Me._ordersManager.Position -= 1 _movingRecords = False EndIf EndSub

31. Add / Edit Line Items PrivateSub editItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles editItem.Click ' Check that there is a row to edit. IfNot BindingContext(ordersList, "Orders.OrdersOrder_Details").Position = -1 Then Dim cm As CurrencyManager = CType(BindingContext(ordersList, "Orders.OrdersOrder_Details"), CurrencyManager) ' Get the current row. Dim orderItem As OrdersDataSet.Order_DetailsRow = CType(CType(cm.Current, DataRowView).Row, OrdersDataSet.Order_DetailsRow) ' Pass the current row to the Order Details form. Dim orderDetailsForm AsNew OrderDetailsPopupForm(orderItem) ' Show the Order Details form and return the result. Dim result As DialogResult = orderDetailsForm.ShowDialog() ' Check if the user clicked "OK". If result = DialogResult.OK Then CalculateTotals() EndIf EndIf EndSub

32. Save Data PrivateSub btnOK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOK.Click Me._ordersManager.EndCurrentEdit() ' Check if changes were made If ordersList.HasChanges Then ' Update the Orders table _ordersDA.Adapter.Update(ordersList) ' Update the Order Details table _orderDetailsDA.Adapter.Update(ordersList) EndIf EndSub

33. Differences In Form Design Disadvantage – Multiple Columns Not Supported in Some Controls • Use .NET ListView instead of Access Listbox • Some programming required for multi-column ComboBox

34. Differences In Form Design Disadvantage – Multiple Columns Not Supported in Some Controls

35. Differences In Form Design Disadvantage – The Datasheet View Is Harder to Implement In Access • View – Datasheet In .NET: • Use DataGrid control • Manually bind to database • Hard to implement advanced controls (ComboBox etc.)

36. Differences In Form Design Disadvantage – Continuous Forms Are Harder to Implement (1 of 4) In Access • Form Property: Default View = Continuous Forms In .NET • Not supported in .NET • 2 options • Tiled user controls • Summary/Detail

37. Differences In Form Design Disadvantage – Continuous Forms Are Harder to Implement (2 of 4) • Tiled user controls

38. Differences In Form Design Disadvantage – Continuous Forms Are Harder to Implement (3 of 4) • Split into summary/detail for complex subforms

39. Differences In Form Design Disadvantage – Continuous Forms Are Harder to Implement (4 of 4) • Split into summary with popup window

40. Differences In Form Design Disadvantage – Subforms are Easier to Use than User Controls (1 of 2) In Access • Create parent and sub forms • Add subform/subreport to parent • Set linkages between parent/subform

41. Differences In Form Design Disadvantage – Subforms are Easier to Use than User Controls (2 of 2) In .NET • Create Orders form (parent) • Create user control for Orders Subform form • Add property to user control to link parent and subform (code) • Update the Orders Subform (user control) when the parent record changes (code) • Add the Orders Subform (user control) to the Orders form • Bind the Orders form to the Orders Subform (user control) Note: We take a different approach with our example (popup window)

42. Differences In Form Design Advantage – Form Inheritance • Add any common form controls and logic into a base form • Create new instances of (“inherit”) the parent form to ensure consistency • Make any required changes to logic and controls on child forms

43. Differences In Form Design Advantage – Use Windows XP Styles • Easy to implement in .NET

44. Differences In Form Design Disadvantage – Read-Only Textboxes are Grayed Out In Access • Set Locked = Yes In .NET • Set ReadOnly = True • Have to explicitly set the background colour

45. Differences In Form Design Disadvantage – Combo Boxes Cannot Be Locked In Access • Set Locked = Yes In .NET • No automatic way • Capture the SelectedIndexChanged event and reset the value

46. Differences In Form Design Advantage – Applications Are Stored As Binary Executables In Access • Use compact and repair • Manually compile as MDE In .NET • Application automatically compiled on run • Runs efficiently because it is in binary

47. Differences in Application Design Advantage – Application Deployment is Easier In Access: • License for Office Developer Edition (for runtime), or • Office installed on target machine In .NET you only need the free .NET Framework

48. Differences in Application Design Advantage – .NET Versions Can Be Run Side-by-Side In Access: • Upgrading Office can cause issues (as we have seen) • Can have multiple versions of Office installed – however MDB associations don’t know enough In .NET: • Framework versions can run side-by-side • Apps using 1.0 continue to work alongside 1.1

49. Differences in Application Design Advantage – Extending Your Forms to Mobile Devices

50. Differences in Application Design Difference – Security Model Integrates with Windows In Access • Maintain two sets of security for forms (Access) and backend (SQL)