Vb .net Tutorial - 6

Accessing and Manipulating Data Objectives In this lesson, you will learn to: ☛ Bind and display data in a Windows Form ☛ Filter data ☛ Sort data ☛ Display data from multiple tables in a single Windows Form

©NIIT

Accessing and Manipulating Data/Lesson 6/Slide 1 of 33

Accessing and Manipulating Data Problem Statement 6.D.1 An application needs to be created that allows the Marketing Manager of Diaz Telecommunications to view the customer details in a customized format since the new product details are to be sent to all the customers. The customer details are located in the database at a remote call center.

©NIIT

Accessing and Manipulating Data/Lesson 6/Slide 2 of 33

Accessing and Manipulating Data Task List ☛Identify the data that needs to be displayed. ☛Identify the mechanism to display data in a customized format. ☛Design a Windows Form to display the data. ☛Connect to the database. ☛Bind the data to a Windows Form control. ☛View the data.

©NIIT

Accessing and Manipulating Data/Lesson 6/Slide 3 of 33

Accessing and Manipulating Data Task 1: Identify the data that needs to be displayed. Result: ☛As per the given problem statement, the data to be displayed is as follows: ✓ CustID ✓ FName ✓ LName ✓ Address ✓ Phone ✓ email

©NIIT

Accessing and Manipulating Data/Lesson 6/Slide 4 of 33

Accessing and Manipulating Data Task 2: Identify the mechanism to display data in a customized format. ☛Data binding is the process of binding the retrieved data to a control on a Windows Form to be displayed in a customized format. ☛Data binding is of two types: ✓ Simple Data Binding ✓ Complex Data Binding Result: ☛For the given problem statement, you will use complex data binding to display the data.

©NIIT

Accessing and Manipulating Data/Lesson 6/Slide 5 of 33

Accessing and Manipulating Data Just a Minute… What is the difference between simple data binding and complex data binding? What are the controls used in each type of binding?

©NIIT

Accessing and Manipulating Data/Lesson 6/Slide 6 of 33

Accessing and Manipulating Data Task 3: Design a Windows Form to display the data. Task 4: Connect to the database. Task 5: Bind the data to a Windows Form control. Task 6: View the data.

©NIIT

Accessing and Manipulating Data/Lesson 6/Slide 7 of 33

Accessing and Manipulating Data Problem Statement 6.D.2 An application needs to be created that allows the Marketing Manager of Diaz Telecommunications to view customer details since the new product details are to be sent to all the customers. In addition, the Marketing Manager should be able to navigate through the customer details. The customer details are located in the database at a remote call center.

©NIIT

Accessing and Manipulating Data/Lesson 6/Slide 8 of 33

Accessing and Manipulating Data Task List ☛Identify the data that needs to be displayed. ☛Identify the mechanism to display data in a customized format. ☛Identify the mechanism to navigate through the data. ☛Design a Windows Form to display the data. ☛Connect to the database. ☛Write the code to navigate through the data. ☛Bind the data to a Windows Form control. ☛View the data.

©NIIT

Accessing and Manipulating Data/Lesson 6/Slide 9 of 33

Accessing and Manipulating Data Task 1: Identify the data that needs to be displayed. Result: ☛As per the given problem statement, the data to be displayed is as follows: ✓ CustID ✓ Fname ✓ Lname ✓ Address ✓ Phone ✓ email

©NIIT

Accessing and Manipulating Data/Lesson 6/Slide 10 of 33

Accessing and Manipulating Data Task 2: Identify the mechanism to data in a customized format. Result: ☛For the given problem statement, you will use simple data binding to bind and display the data.

©NIIT

Accessing and Manipulating Data/Lesson 6/Slide 11 of 33

Accessing and Manipulating Data Task 3: Identify the mechanism to navigate through the data. ☛The CurrencyManager class ✓ Exists for every data source that is bound to a Windows Form. ✓ Object handles the binding to the data source by keeping a pointer to the current item in the record list. ✓ Is derived from the BindingManagerBase class. ☛A BindingContext object, which is a Windows Form object, is used to keep track of the existing CurrencyManager objects in a form.

©NIIT

Accessing and Manipulating Data/Lesson 6/Slide 12 of 33

Accessing and Manipulating Data Task 3: Identify the mechanism to navigate through the data. (Contd.) ☛Relationship between the BindingContext class, CurrencyManager class, and Windows Form: Windows Form Control 1 Control 2 Control 3

BindingContext object

©NIIT

CurrencyManager 1

DATA SOURCE 1

CurrencyManager 1

DATA SOURCE 1

CurrencyManager 1

DATA SOURCE 1

Accessing and Manipulating Data/Lesson 6/Slide 13 of 33

Accessing and Manipulating Data Task 3: Identify the mechanism to navigate through the data. (Contd.) Result: ☛You will use the BindingManagerBase class and the BindingContext() method to navigate through the records.

Task 4: Design a Windows Form to display the data. Task 5: Connect to the database. Task 6: Write the code to navigate through the data. Task 7: Bind the data to a Windows Form control. Task 8: View the data. ©NIIT

Accessing and Manipulating Data/Lesson 6/Slide 14 of 33

Accessing and Manipulating Data Problem Statement 6.D.3 An application needs to be created for the Marketing Manager of Diaz Telecommunications to view the order details for a specific invoice to verify the monthly sales report.

©NIIT

Accessing and Manipulating Data/Lesson 6/Slide 15 of 33

Accessing and Manipulating Data Task List ☛Identify the data that needs to be displayed. ☛Identify the method to filter and sort the data. ☛Identify the method to navigate through the data. ☛Design a Windows Form to display the data. ☛Connect to the database, and filter and sort the data. ☛Bind the data to a Windows Form control. ☛View the data.

©NIIT

Accessing and Manipulating Data/Lesson 6/Slide 16 of 33

Accessing and Manipulating Data Task 1: Identify the data that needs to be displayed. Result: ☛As per the given problem statement, the data to be displayed is as follows: ✓ Inv ✓ Date ✓ CustID ✓ ProdID ✓ Cost ✓ Advance

©NIIT

Accessing and Manipulating Data/Lesson 6/Slide 17 of 33

Accessing and Manipulating Data Task 2: Identify the method to filter and sort the data. ☛There are two methods to display filtered data: ✓ Parameterized queries ➤Filter data based on the criterion entered by a user at run time. ➤Are created while configuring data adapters. ✓ Filtering a dataset ➤After the data is retrieved in a dataset, the data can be filtered by: ➤ Using

the Select() method

➤ Filtering ©NIIT

a data view Accessing and Manipulating Data/Lesson 6/Slide 18 of 33

Accessing and Manipulating Data Task 2: Identify the method to filter and sort the data. (Contd.) ☛The Select() method ✓ Used to filter data without changing the order of the records in the table based on the parameter passed.

©NIIT

Accessing and Manipulating Data/Lesson 6/Slide 19 of 33

Accessing and Manipulating Data Just a Minute… The records of customers having monthly income of $4200 needs to be displayed in ascending order of their names from the table Employees. Write the code to retrieve filtered records and store the retrieved records.

©NIIT

Accessing and Manipulating Data/Lesson 6/Slide 20 of 33

Accessing and Manipulating Data Task 2: Identify the method to filter and sort the data. (Contd.) ☛Filtering a data view ✓ A DataView object creates a fixed customized view of a given DataTable object. ✓ By default, every table in a dataset has a data view attached to it. ✓ Multiple DataView objects can also be explicitly created for displaying different views of the same underlying data.

©NIIT

Accessing and Manipulating Data/Lesson 6/Slide 21 of 33

Accessing and Manipulating Data Task 2: Identify the method to filter and sort the data. (Contd.) ✓ Some commonly used properties of the DataView object: ➤Table ➤Sort ➤RowFilter ➤RowStateFilter ➤AllowNew, AllowDelete, AllowEdit

©NIIT

Accessing and Manipulating Data/Lesson 6/Slide 22 of 33

Accessing and Manipulating Data Task 2: Identify the method to filter and sort the data. (Contd.) Result: ☛You will use a parameterized query to filter and sort the data, as it requires you to write minimum code.

©NIIT

Accessing and Manipulating Data/Lesson 6/Slide 23 of 33

Accessing and Manipulating Data Just a Minute… The name, date of joining, and department of the employees with salary greater than $2000 need to be retrieved and displayed in a DataGrid control. Write the code to filter the data and display the filtered data.

©NIIT

Accessing and Manipulating Data/Lesson 6/Slide 24 of 33

Accessing and Manipulating Data Task 3: Identify the method to navigate through the data. Result: ☛You will use the BindingManagerBase class and the BindingContext() method to navigate through the data.

Task 4: Design a Windows Form to display the data. Task 5: Connect to the database, and filter and sort the data. Task 6: Bind the data to a Windows Form control. Task 7: View the data.

©NIIT

Accessing and Manipulating Data/Lesson 6/Slide 25 of 33

Accessing and Manipulating Data Problem Statement 6.D.4 An application needs to be created for the Diaz Telecommunications Marketing Manager to view customer details, such as the name and address, and order details for a specific customer for analyzing customer feedbacks. The customer details and the order details are present in separate tables.

©NIIT

Accessing and Manipulating Data/Lesson 6/Slide 26 of 33

Accessing and Manipulating Data Task List ☛Identify the data that needs to be displayed. ☛Identify the mechanism to display data from multiple tables in a single Windows Form. ☛Design a Windows Form to display the data. ☛Connect to the database. ☛Create a relationship between the tables. ☛Bind the data to the Windows Form controls. ☛View the data from multiple tables in a single Windows Form.

©NIIT

Accessing and Manipulating Data/Lesson 6/Slide 27 of 33

Accessing and Manipulating Data Task 1: Identify the data that needs to be displayed. Result: ☛As per the given problem statement, the data to be displayed is as follows: ✓ FName ✓ Address ✓ ProdID ✓ Inv ✓ Date ✓ CustID

©NIIT

Accessing and Manipulating Data/Lesson 6/Slide 28 of 33

Accessing and Manipulating Data Task 2: Identify the mechanism to display data from multiple tables in a single Windows Form. ☛Tables are stored in a dataset as discrete entities but a relationship can be created between the stored tables to display data from the related tables. ☛The column that is common in both the tables is used to create the relationship. Result: ☛You will create a relationship between CustomerTracking and CustOrder tables to display the data from both the tables.

©NIIT

Accessing and Manipulating Data/Lesson 6/Slide 29 of 33

Accessing and Manipulating Data Task 3: Design a Windows Form to display the data. Task 4: Connect to the database. Task 5: Create a relationship between the tables. Task 6: Bind the data to the Windows Form controls. Task 7: View the data.

©NIIT

Accessing and Manipulating Data/Lesson 6/Slide 30 of 33

Accessing and Manipulating Data Summary In this lesson, you learned that: ☛After the data is retrieved from the database, it has to be bound to a Windows Form control to be displayed in a customized format. ☛Data binding is of two types: ✓ Simple data binding ✓ Complex data binding ☛Using parameterized queries, the data can be filtered based on the criterion entered by a user.

©NIIT

Accessing and Manipulating Data/Lesson 6/Slide 31 of 33

Accessing and Manipulating Data Summary (Contd.) ☛After the data is retrieved in a dataset, you can filter the data stored in the dataset by: ✓ Using the Select() method ✓ Using DataView objects ☛A DataView object allows a fixed customized view of a given DataTable object. ☛A data view manager is a collection of data views and is useful while working with related tables.

©NIIT

Accessing and Manipulating Data/Lesson 6/Slide 32 of 33

Accessing and Manipulating Data Summary (Contd.) ☛A CurrencyManager object handles the binding to the data source by keeping a pointer to the current item in the record list. ☛A BindingContext object is used to keep track of the existing CurrencyManager objects on a form. ☛Tables are stored in a dataset as discrete entities, but a relationship can be created between the stored tables so that the dataset displays data from the related tables.

©NIIT

Accessing and Manipulating Data/Lesson 6/Slide 33 of 33