Week # 6 Introduction to Menus and Database Processing.

1. Week # 6Introduction to Menus andDatabase Processing. CSC 336Fall 2003 Jane Huang

2. Topics to cover this week: • Create a menu system for a form • Create event handlers for menus • Review loading a textfile into an array • Define a connection to a database • Use a DataAdapter to retrieve data from a database. • Use the dataset class • Use the ControlBindingsCollection • Navigate between records • Modify a database record

3. Create a menu system for a form Add a MainMenu object to the current Form A menu appears with a “Type Here” label. You can add submenus or additional high-level menu options.

4. Customizing your menu • Right click on the menu and select “Edit Names” to change the names of each menu item. (Give each option a meaningful name) • Use an ampersand (&) to create a hotkey. &Files will appear as Files and “F” is the hot key. • Add separators between items in sub-menus by right clicking and specifying “Add separator”. Use these to create meaningful groups. • Write menu click event handlers by double clicking on the menu item.

5. Topics to cover this week: • Create a menu system for a form • Create event handlers for menus • Review loading a textfile into an array • Define a connection to a database • Use a DataAdapter to retrieve data from a database. • Use the dataset class • Use the ControlBindingsCollection • Navigate between records • Modify a database record

6. Creating Event Handlers To add an event handler for the “File Open” menu option, just double click ‘mnuFileOpen’ If you want something to happen when the user selects a menu option – then place the code in this event handler.

7. Topics to cover this week: • Create a menu system for a form • Create event handlers for menus • Review loading a textfile into an array • Define a connection to a database • Use a DataAdapter to retrieve data from a database. • Use the dataset class • Use the ControlBindingsCollection • Navigate between records • Modify a database record

8. Loading Text into an Array • First question: “Where do we declare the arrays?” Answer: If we want them to be visible to ALL functions and procedures within the form, we must make them GLOBAL. • We therefore declare them outside of any function.

9. Loading Text into an Array • Second question: “Where do we write the code to read the data from the text file?” Answer: It depends on WHEN we want the file to be read. • Options: • When the form loads – place it in ‘new’ or ‘form_load’ event handlers. • When the user selects the ‘File Open’ option from the menu – place it in the menu click event handler.

10. Code to read the file: Next enter the loop to read each line, and store datafrom each line into the States and Capitals arrays.

11. Code to read the file: (cont..) Next display all the elements from the two arrays.

12. Code to read the file: (cont..) As a result, the states and capitals are all displayed in the listbox.

13. Sorting by State We sort the two arrays using the mstrStates array as the key. This means that states and their capitals maintain the same relative positions in each array – ordered by states.

14. Searching for an Element • We can use a BinarySearch method to locate an item in an array. • If the item is found, the function returns an integer value specifying the location of the item. • If the item is not found, the function returns a negative value. • IntegerResult = BinarySearch(Array, SearchItem) In our example, where do we put the code for the Binary Search?

15. Searching for an Element Answer: We place it in the textbox_validating event – so that we can perform the search after the user enters the search state.

16. Topics to cover this week: • Create a menu system for a form • Create event handlers for menus • Review loading a textfile into an array • Define a connection to a database • Use a DataAdapter to retrieve data from a database. • Use the dataset class • Use the ControlBindingsCollection • Navigate between records • Modify a database record

17. VB.NET Database Connection ADO.NETconnection Your solution Databaseprovider Sendscommands Database Getsdata

18. Establishing a Database Connection • The first step in retrieving data from a database is to establish a database connection. • ADO.NET connection class provides the necessary support for this connection between your solution and the data source, using the resources of a provider. • VB.NET supports two database connection controls: • OleDBConnection Control • SQLConnection – to connect only to SQL servers.

19. Establishing a Database Connection • Select the ‘OleDbDataConnection’ option from the ‘data’ toolbox. • Rename the instance to a meaningful name. • Click on the “ConnectionString” property.

20. Establishing a Database Connection From the “ConnectionString” – Provider property, select <New Connection>. Select “Microsoft Jet 4.0 OLE DB Provider. Select the Access table fromthe Connection tab. Click the ‘test connection’ button to make sure everything is OK.

21. OleDbConnection Class • Properties • ConnectionString – contains a string defining the provider, database name, and security information. • ConnectionTimeOut defines the number os second that the solution will wait to establish a connection. • DataSource – gets the location and filename of the database. • Provider – defines the provider used to connect to the database. • State – gets the current state of the connection{ open, closed, connecting, executing, fetching }

22. OleDbConnection Class • Methods • Close – closes an open connection with the database. • Open – uses the contents of the ConnectionString property to open a connection with a database. • Events • StateChanged – occurs whenever the state of the connection changes.

23. Topics to cover this week: • Create a menu system for a form • Create event handlers for menus • Review loading a textfile into an array • Define a connection to a database • Use a DataAdapter to retrieve data from a database. • Use the dataset class • Use the ControlBindingsCollection • Navigate between records • Modify a database record

24. Creating the DataAdapter • Data is sent and retrieved over a connection via a DataAdapter. • The DataAdapter class provides methods to support ‘send’ and ‘retrieve’ requests. • All requests use Structured Query Language (SQL). • SELECT – to select one or more rows from a database table. • INSERT to insert a row. • UPDATE to change the contents of a row • DELETE to remove one or more rows.

25. Creating the DataAdapter • Add an OleDBDataAdapter control to your form. • The Data Adapter Wizard appears. • Select a data connection (ie your database) • Specify SQL

26. Creating the DataAdapter Either enter your own SQL commands, or use the Query Builder.

27. Constructing an SQL statement Select the tablefrom the database. Use the “Queryby Example” tool to build the SQL statement.

28. Constructing an SQL statement The wizard will confirm that the adapter was successfully configured. Rename the OleDBAdapter Object to a meaningful name.

29. DataAdapter Class • Properties • SelectCommand – contains an SQL SELECT statement to retrieve records. • InsertCommand, UpdateCommand, and DeleteCommand – to modify data in the database. • Methods • Fill – uses an existing connection to fill rows in the dataset. • Update – sends data changed in the DataSet back to the underlying data source.

30. Topics to cover this week: • Create a menu system for a form • Create event handlers for menus • Review loading a textfile into an array • Define a connection to a database • Use a DataAdapter to retrieve data from a database. • Use the dataset class • Use the ControlBindingsCollection • Navigate between records • Modify a database record

31. Your solution Databaseprovider Data-Adapter sends command Connect-ion Data-base Data-Adapter creates dataset The role of the DataSet • DataAdapter sends SQL to the provider. • Data is sent back in the form of ‘rows of data’ and stored into a DataSet.

32. Creating a DataSet • Select an instance of the DataAdapterControl • From the VB.NET menu bar select Data / Generate Dataset • Select ‘New’ and type in a name for the new DataSet. • You should now have 3 objects in the ‘tray’

33. The DataSet Class • Properties • Tables property – gets the collection of tables stored as a dataset. • Methods • Clear – clears all data from the dataset (ie removes all rows from the table BUT does not change the original database. • AcceptChanges – removes deleted rows and marks changed rows as unchanged. • GetChanges – takes one dataset as an argument, and returns a dataset showing all changed rows. • HasChanges – returns Boolean {True if a dataset has been changed }

34. Write the code to fill the dataset. On loading the form, the dataset (DsCustomerBalance1) is filled by the Adapter (OleDBAdapCustomers) *** If you run the solution – you WON’T see anything yet. First we have to create control bindings.

35. Topics to cover this week: • Create a menu system for a form • Create event handlers for menus • Review loading a textfile into an array • Define a connection to a database • Use a DataAdapter to retrieve data from a database. • Use the dataset class • Use the ControlBindingsCollection • Navigate between records • Modify a database record

36. Data Binding • Data binding is when we associate a property of a control instance – such as a text property of a text box, to a field in a data source, such as a DataSet. • Bind a SINGLE property in a control instance with a SINGLE field. • Simple Binding – Example: A textbox displays data from only one row at a time. • Complex Binding – Example: A listbox, ComboBox, or Datagrid control display multiple rows.

37. Data Binding • Create a Binding Object. • Provide a property name - Example: Text • Supply a data source - Example: The data set name is DSCustomerBalance1 • Supply a navigation path.Example: The table name in the data set is “Customer Balance” and the field in the table is “Name” Data from the 1st row of the dataset is displayed.

38. Topics to cover this week: • Create a menu system for a form • Create event handlers for menus • Review loading a textfile into an array • Define a connection to a database • Use a DataAdapter to retrieve data from a database. • Use the dataset class • Use the ControlBindingsCollection • Navigate between records • Modify a database record

39. Navigate between records: • When you bind controls to a DataSet, VB.NET automatically creates two objects: • CurrencyManager PositionProperty tracks the current row in the dataset. • BindingContext An individual binding context is defined by two arguments: • Dataset • Fieldname The binding context ‘Position’ property can be incremented or Decremented in order to navigate to the next record or the previous record.

40. Navigate between records: • Moving to the NEXT record • Moving to the PREVIOUS record • Finding the FIRST record • Finding the LAST record

41. Navigate between records: • By changing the Position property, we navigate through the dataset. Where do we put the navigation code?

42. BindingManagerBase Class • Properties • Count – returns number of rows • Position – specifies which row is to be displayed. • Methods • AddNew – Adds a new item

43. Topics to cover this week: • Create a menu system for a form • Create event handlers for menus • Review loading a textfile into an array • Define a connection to a database • Use a DataAdapter to retrieve data from a database. • Use the dataset class • Use the ControlBindingsCollection • Navigate between records • Modify a database record

44. Adding a new record • To add a new record we need to perform the following steps: • Clear the textboxes on the screen. • Allow the user to enter the new data. • Allow the user to COMMIT or CANCEL the changes.