Vb .net Tutorial - 5

Working with ADO.NET Objectives In this lesson, you will learn to: ☛Identify the need for ADO.NET ☛Identify the features of ADO.NET ☛Identify the components of the ADO.NET object model ☛Connect to a database by creating a data adapter ☛Access a database through a dataset ☛Preview the data adapter result

©NIIT

Working with ADO.NET/Lesson 5/Slide 1 of 30

Working with ADO.NET ADO.NET ☛ Is a model used by Visual Basic .NET applications to communicate with a database for retrieving, accessing, and updating data. ☛ Uses a structured process flow to interact with a database.

©NIIT

Working with ADO.NET/Lesson 5/Slide 2 of 30

Working with ADO.NET Features of ADO.NET ☛ Disconnected data architecture — Applications connect to the database only while retrieving and updating data. ☛ Data cached in datasets — ADO.NET is based on a disconnected data structure. Therefore, the data is retrieved and stored in datasets. ☛ Data transfer in XML format — ADO.NET uses XML for transferring information from a database into a dataset and from the dataset to another component. ☛ Interaction with the database is done through data commands.

©NIIT

Working with ADO.NET/Lesson 5/Slide 3 of 30

Working with ADO.NET Just a Minute… List the features of ADO.NET.

©NIIT

Working with ADO.NET/Lesson 5/Slide 4 of 30

Working with ADO.NET ADO.NET Object Model

DATA PROVIDER

CONNECTION VISUAL BASIC .NET APPLICATION (WINDOWS/ WEB FORM)

Accessing retrieved data

DATA READER

COMMAND

Accessing retrieved data

DATASET

©NIIT

Filling dataset with data DATA ADAPTER

Establishes connection with the database Retrieves data in a read-only, forward only mode Executes a command to retrieve DATABASE data from the database Transfers data to the dataset and reflects the changes made in the dataset to the database

Working with ADO.NET/Lesson 5/Slide 5 of 30

Working with ADO.NET ADO.NET Object Model (Contd.) ☛Data from a database can be assessed through: ✓ A dataset ✓ A DataReader object

©NIIT

Working with ADO.NET/Lesson 5/Slide 6 of 30

Working with ADO.NET Key Components of the ADO.NET Model ☛Data Provider ✓ Is used for connecting to a database, retrieving data, and storing the data. ✓ Is of two types:

©NIIT

➤ OLE

DB data provider

➤ SQL

Server data provider

Working with ADO.NET/Lesson 5/Slide 7 of 30

Working with ADO.NET Just a Minute… Identify the two types of data providers and list the difference between them.

©NIIT

Working with ADO.NET/Lesson 5/Slide 8 of 30

Working with ADO.NET Components of a Data Provider ☛Connection ✓ Used to establish a connection with a data source ✓ Some commonly used properties and methods: ➤ ConnectionString

property

➤ Open()method ➤ Close()method ➤ State

©NIIT

property

Working with ADO.NET/Lesson 5/Slide 9 of 30

Working with ADO.NET Components of a Data Provider (Contd.) ☛Data adapter ✓ Creates a dataset and updates the database. ✓ Handles data transfer between the database and the dataset through its properties and methods. ✓ Displays the data through the process of table mapping. ✓ Are of two types: ➤ SqlDataAdapter ➤ OleDbDataAdapter

©NIIT

Working with ADO.NET/Lesson 5/Slide 10 of 30

Working with ADO.NET Just a Minute… What is the function of a data adapter?

©NIIT

Working with ADO.NET/Lesson 5/Slide 11 of 30

Working with ADO.NET Components of a Data Provider (Contd.) ☛Data command ✓ Is a SQL statement or a stored procedure that is used to retrieve, insert, delete, or modify data from a data source. ✓ Is an object of the OleDbCommand or SQLCommand class.

©NIIT

Working with ADO.NET/Lesson 5/Slide 12 of 30

Working with ADO.NET Components of a Data Provider (Contd.) ☛Data reader ✓ Is used to retrieve data from a data source in a read-only and forward-only mode. ✓ Stores a single row at a time in the memory. ✓ Commonly used methods: ➤ Read() ➤ Close() ➤ NextResult()

©NIIT

Working with ADO.NET/Lesson 5/Slide 13 of 30

Working with ADO.NET Dataset ☛Is a disconnected, cached set of records that are retrieved from a database. ☛Is present as a DataSet class in the System.Data namespace. ☛Has its own object model.

©NIIT

Working with ADO.NET/Lesson 5/Slide 14 of 30

Working with ADO.NET Dataset Object Model

DATASET

DataRelationCollection

DataRelation

DataRowCollection

DataRow

©NIIT

DataTableCollection

ExtendedProperties

DataTable

DataView

PrimaryKey

DataColumnCollection

DataColumn

Working with ADO.NET/Lesson 5/Slide 15 of 30

Working with ADO.NET Just a Minute… What are the various components of a dataset?

©NIIT

Working with ADO.NET/Lesson 5/Slide 16 of 30

Working with ADO.NET Problem Statement 5.D.1 The call center application needs to provide the facility of viewing the customer details for the Sales department.

©NIIT

Working with ADO.NET/Lesson 5/Slide 17 of 30

Working with ADO.NET Task List ☛Identify the data that needs to be displayed. ☛Identify the method for creating a data adapter. ☛Identify the type of dataset to be created. ☛Create a data adapter. ☛Create a dataset. ☛Preview the database records.

©NIIT

Working with ADO.NET/Lesson 5/Slide 18 of 30

Working with ADO.NET 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

Working with ADO.NET/Lesson 5/Slide 19 of 30

Working with ADO.NET Task 2: Identify the method for creating a data adapter. ☛There are three methods to create a data adapter: ✓ Manually ✓ Through a wizard ✓ Using the Server Explorer window Result: ☛In the given problem statement, you will create a data adapter by using Data Adapter Configuration Wizard since it lets you create a data adapter with minimum steps.

©NIIT

Working with ADO.NET/Lesson 5/Slide 20 of 30

Working with ADO.NET Just a Minute… What are the different methods of creating a data adapter?

©NIIT

Working with ADO.NET/Lesson 5/Slide 21 of 30

Working with ADO.NET Task 3: Identify the type of dataset to be created. ☛Datasets are of two types: ✓ Typed ✓ Untyped ☛Typed dataset ✓ Is derived from the DataSet class and has an associated XML schema, which is created at the time of the creation of the dataset. ✓ Can be customized after creation. ✓ Supports Intellisense and auto‑completion for the elements of the syntax while writing code. ©NIIT

Working with ADO.NET/Lesson 5/Slide 22 of 30

Working with ADO.NET Task 3: Identify the type of dataset to be created. (Contd.) ☛ Untyped dataset ✓ Does not have any associated XML schema, therefore, the structure of an untyped dataset is not known at the compile time. ✓ Represents tables and columns as collections. ✓ Does not support Intellisense and auto‑completion for the elements of the syntax. Result: ☛ Since the structure of the data is known at compile time, you will create a typed dataset for the given problem statement. ©NIIT

Working with ADO.NET/Lesson 5/Slide 23 of 30

Working with ADO.NET Just a Minute… What is the difference between typed and untyped dataset?

©NIIT

Working with ADO.NET/Lesson 5/Slide 24 of 30

Working with ADO.NET Task 4: Create a data adapter. Task 5: Create a dataset. Task 6: Preview the database records.

©NIIT

Working with ADO.NET/Lesson 5/Slide 25 of 30

Working with ADO.NET Problem Statement 5.P.1 An application needs to be created that allows the Diaz Telecommunications Sales Manager to preview the customer order details from the database. In addition, for each order, percentage of the cost of a product given as advance needs to be previewed.

©NIIT

Working with ADO.NET/Lesson 5/Slide 26 of 30

Working with ADO.NET Summary In this lesson, you learned that: ☛ADO.NET is a data access programming model for accessing the data stored in a database from a .NET application. ☛ADO.NET has the following features: ✓ A disconnected data architecture ✓ Data cached in datasets ✓ The use of XML format for data transfer ✓ Database operations done through data commands

©NIIT

Working with ADO.NET/Lesson 5/Slide 27 of 30

Working with ADO.NET Summary (Contd.) ☛ ADO.NET consists of the following components: ✓ Data Provider — Used for connecting to a database, retrieving data, storing the data in a dataset, and updating the database. ✓ Data Adapter — A data adapter is integral to the working of the ADO.NET model since data transfer to and from the database is done through a data adapter. ✓ Dataset — A dataset is a disconnected cached set of records that are retrieved from the database. ☛ A data adapter communicates with a database through data commands. These data commands use parameters for execution. ©NIIT

Working with ADO.NET/Lesson 5/Slide 28 of 30

Working with ADO.NET Summary (Contd.) ☛After a dataset has been created, a data adapter uses the process of table mapping to map the columns in the database table with the dataset columns. ☛A data adapter can be created by using any of the following methods: ✓ Manually ✓ Through a wizard ✓ By using Server Explorer

©NIIT

Working with ADO.NET/Lesson 5/Slide 29 of 30

Working with ADO.NET Summary (Contd.) ☛Datasets are of two types: ✓ Typed datasets – A typed dataset is derived from the DataSet class and has an associated schema. ✓ Untyped datasets – An untyped dataset does not have any associated XML schema. The tables and columns in an untyped dataset are represented as collections.

©NIIT

Working with ADO.NET/Lesson 5/Slide 30 of 30