Download - Ddcauado.net Session01
-
7/30/2019 Ddcauado.net Session01
1/25
Slide 1 of 25Ver. 1.0
Developing Data-Centric Applications Using ADO.NET
Session 1
Business applications need to manage voluminous data.Data is generally stored in a relational database in the formof related tables or is stored in text format in XMLdocuments. Most business applications allow users toretrieve the data stored in a database and present it in auser-friendly interface without writing the databasecommands. ADO.NET is a model used by .NET applicationsto communicate with a database for retrieving, accessing,and updating data. This module will provide the necessaryskills to the students to work as a database application
developer in the industry.
Rationale
-
7/30/2019 Ddcauado.net Session01
2/25
Slide 2 of 25Ver. 1.0
Developing Data-Centric Applications Using ADO.NET
Session 1
A student registering for this module should be able toperform the following tasks:
Work with XML
Work with SQL queries
Prerequisites
-
7/30/2019 Ddcauado.net Session01
3/25
Slide 3 of 25Ver. 1.0
Developing Data-Centric Applications Using ADO.NET
Session 1
Brief History:
Tebisco is a leading producer and distributor of snacks in theU.S., as well as in most of the companys 23 internationalmarkets. In 1998, consumers spent $9.2 billion on Tebiscossnacks, $1.4 billion more than in the previous year.
Tebisco started as a small bakery in Round Rock, Texas in1978. In a short time, its gingersnaps, macaroons, shortbreadand other cookies were popular all over the U.S. Three yearsago, the management embarked on a rapid expansion plan.They set up offices in Asia and Europe, in addition to
strengthening their U.S. operations.Tebisco has got a centralized database management systemwhereby the information about all the HR activities ismaintained.
Case Study - Tebisco
-
7/30/2019 Ddcauado.net Session01
4/25
Slide 4 of 25Ver. 1.0
Developing Data-Centric Applications Using ADO.NET
Session 1
In this session, you will learn to:
Understand the ADO.NET object model
Create and manage connections
Objectives
-
7/30/2019 Ddcauado.net Session01
5/25
Slide 5 of 25Ver. 1.0
Developing Data-Centric Applications Using ADO.NET
Session 1
Business applications allow users to retrieve data from adatabase by presenting data in a user-friendly interface.
User need not remember the database commands forretrieving or updating data in the database.
Microsoft has created a family of data access technologiesto help programmers build efficient applications to accessdata, regardless of its source.
The guidelines that can be followed for selecting anappropriate data access technology are:
Understanding ADO.NET
Use ADO.NET for writing a managed codetargeting the .NET Framework in Visual Basic,C#, and C++.
Use Microsoft ODBC for writing a native codetargeting Windows by using C or C++.Use (OLE) DB for writing a Microsoftbased application, a VB 6 COM application, ora C++ application using COM.
Use JDBC for writing a Java code targetingSQL Server.
-
7/30/2019 Ddcauado.net Session01
6/25
Slide 6 of 25Ver. 1.0
Developing Data-Centric Applications Using ADO.NET
Session 1
ADO.NET is a part of the .NET Framework architecture.
Understanding ADO.NET (Contd.)
-
7/30/2019 Ddcauado.net Session01
7/25Slide 7 of 25Ver. 1.0
Developing Data-Centric Applications Using ADO.NET
Session 1
ADO.NET is based on an object model that is based on thestandards laid down by W3C.
The following figure shows the ADO.NET object model.
The ADO.NET Object Model
-
7/30/2019 Ddcauado.net Session01
8/25Slide 8 of 25Ver. 1.0
Developing Data-Centric Applications Using ADO.NET
Session 1
The two key components of ADO.NET Object model are:
Data provider
Dataset
The ADO.NET Object Model (Contd.)
Is required for:
Connecting to a database.
Retrieving data.
Storing the data in a dataset.
Reading the retrieved data.
Updating the database.
Has four key components:
Connection
Command
DataReader
DataAdapter
-
7/30/2019 Ddcauado.net Session01
9/25Slide 9 of 25Ver. 1.0
Developing Data-Centric Applications Using ADO.NET
Session 1
The two key components of ADO.NET Object model are:
Data provider
Dataset
The ADO.NET Object Model (Contd.)
Is a disconnected, cached set ofrecords that are retrieved from adatabase.
Is present in the Dataset class inthe System.Data namespace.
Has the following keycomponents:
DataTableCollection
DataRelationCollection
DataTable
DataRowCollection
DataColoumnCollection
-
7/30/2019 Ddcauado.net Session01
10/25Slide 10 of 25Ver. 1.0
Developing Data-Centric Applications Using ADO.NET
Session 1
Which of the following components of a data provider isused to retrieve, insert, delete, or modify data in a datasource?
1. Connection
2. Command
3. DataReader
4. DataAdapter
Just a minute
Answer:
2. Command
-
7/30/2019 Ddcauado.net Session01
11/25Slide 11 of 25Ver. 1.0
Developing Data-Centric Applications Using ADO.NET
Session 1
The key features of ADO.NET are:
Disconnected data architecture
Data cached in datasets
Scalability
Data transfer in XML format
Features of ADO.NET
Applications connect tothe database only whileretrieving and updatingdata.
Connection with thedatabase is closed,once the data isretrieved.
Connection is
re-established whenthe data needs to beupdated.
The data is retrievedand stored in datasets.
You can work with therecords stored in adataset as you workwith real data.
The dataset isindependent of data
source and you remaindisconnected from thedata source.
Database operationsare performed on thedataset instead of onthe database.
As a result, resourcesare saved and thedatabase can meet the
increasing demands ofusers more efficiently.
XML is the fundamentalformat for data transferin ADO.NET.
Because a dataset isstored in the XMLformat, you can
transmit it betweendifferent types ofapplications.
-
7/30/2019 Ddcauado.net Session01
12/25Slide 12 of 25Ver. 1.0
Developing Data-Centric Applications Using ADO.NET
Session 1
To create and manage connections, you need to:
Create a connection object.
Create a command object.
Open the connection object.
Execute the SQL statement in the command object.Close the connection object.
Creating and Managing Connections
-
7/30/2019 Ddcauado.net Session01
13/25Slide 13 of 25Ver. 1.0
Developing Data-Centric Applications Using ADO.NET
Session 1
SqlConnection connection =
new SqlConnection();
connection.ConnectionString =
"Data Source=SQLSERVER01;
Initial Catalog=HR;
User ID=sa;
Password=password";
Create a SqlConnection object.
SqlConnection class is used to
connect to a SQL Server.The ConnectionStringproperty
provides information, such as thedata source and database name,that is used to establish aconnection with a database.
Name of the Server to be usedwhen a connection is open.Name of the database.
Used to specify the Server loginaccount.Login password for the Serveraccount.
Creating a Connection Object
Execute the following steps to create a connection to thedatabase:
-
7/30/2019 Ddcauado.net Session01
14/25Slide 14 of 25Ver. 1.0
Developing Data-Centric Applications Using ADO.NET
Session 1
Which of the following parameters of ConnectionString isused to specify the name of the database?
1. Provider
2. Initial Catalog
3. Data source4. Database
Just a minute
Answer:
2. Initial Catalog
-
7/30/2019 Ddcauado.net Session01
15/25Slide 15 of 25Ver. 1.0
Developing Data-Centric Applications Using ADO.NET
Session 1
SqlCommand cmd = new
SqlCommand
(SELECT * FROM
monthlysalary,connection);
Creating a Command Object
To execute an SQL statement, you need to
create an instance of the SqlCommand
class.The two parameters that are passed to the
SqlCommnad object are, the SQL query tobe executed and the SqlConnection
object.
Execute the following steps to create a command object:
-
7/30/2019 Ddcauado.net Session01
16/25Slide 16 of 25Ver. 1.0
Developing Data-Centric Applications Using ADO.NET
Session 1
//SqlConnection connection
connection.Open();
Opening the Connection Object
It opens a database connectionwith the property settingsspecified by the
ConnectionString property.
Execute the following steps to open a connection:
-
7/30/2019 Ddcauado.net Session01
17/25Slide 17 of 25Ver. 1.0
Developing Data-Centric Applications Using ADO.NET
Session 1
To execute the query passed in the Command object, youcan call one of the following methods:
Executing SQL Statements in the Command Object
// Creating a SqlConnection object
SqlConnection connection = new
SqlConnection();
// Creates a connection string to the HRdatabase
connection.ConnectionString = "Data Source=
SQLSERVER01; Initial Catalog=HR; User
ID=sa; Password=niit#1234";
connection.Open();
// Creating a SqlCommand object
SqlCommand cmd = new SqlCommand("select *
from monthlysalary", connection);
// Creating SqlReader object
SqlDataReader myReader =
cmd.ExecuteReader();
-
7/30/2019 Ddcauado.net Session01
18/25Slide 18 of 25Ver. 1.0
Developing Data-Centric Applications Using ADO.NET
Session 1
//SqlConnection connection
connection.Close();
Closing the Connection Object
It closes the connection to thedatabase.
Execute the following steps toclose a connection:
-
7/30/2019 Ddcauado.net Session01
19/25Slide 19 of 25Ver. 1.0
Developing Data-Centric Applications Using ADO.NET
Session 1
Handling Connection Events:
The two key events for the SqlConnection class are:
StateChange event
InfoMessage event
Closing the Connection Object (Contd.)
This event occurs when the stateof the connection changes.
It receives an argument of type
StateChangeEventArgs.StateChangeEventArgs has the
following properties:CurrentState
OriginalState
This event occurs when aninformational message or
warning is returned from a datasource.
It receives an argument of typeSqlInfoMessageEventArgs.
SqlInfoMessageEventArgs
has the following properties:
Errors
Message
Source
-
7/30/2019 Ddcauado.net Session01
20/25Slide 20 of 25Ver. 1.0
Developing Data-Centric Applications Using ADO.NET
Session 1
Connection pooling enables a data source to reuseconnections for a particular user.
Connection pooling is controlled by certain parameters thatare placed into the connection string.
Connectiontimeout
Min poolsize
Max poolsize
Pooling
Connectionreset
Loadbalancingtimeout,connectionlifetime
Enlist
Implementing Connection Pooling
It is the time in seconds to waitwhile a connection to the datasource is attempted. Thedefault value is 15 seconds.
It is used to mention theminimum number ofconnections maintained in thepool. The default value is 0.
It is used to mention themaximum number ofconnections allowed in the pool.The default value is 100.
When true, it causes therequest for a new connection tobe drawn from the pool.It indicates that the databaseconnection will be reset when
the connection is removed from
the pool.It specifies the maximum timein seconds that a pooledconnection should live.
When the value is true, theconnection is automatically enlistedinto the creation threads current
transaction context.
-
7/30/2019 Ddcauado.net Session01
21/25Slide 21 of 25Ver. 1.0
Developing Data-Centric Applications Using ADO.NET
Session 1
A request for a connection ismade by the application usingthe Open() method.
If the Pooling property is set
to true, the pooler attempts toacquire a connection from thepool otherwise a newconnection is created.
Close the connection bycalling the close() method.
Implementing Connection Pooling (Contd.)
-
7/30/2019 Ddcauado.net Session01
22/25
Slide 22 of 25Ver. 1.0
Developing Data-Centric Applications Using ADO.NET
Session 1
Problem Statement:
Tebisco is a leading producer and distributor of snacks in theUnited States. It is planning to start its annual appraisalprocess. Before starting up with the appraisal process, thesenior management requires a list of all employees. The
details include employee name, employee code, currentposition, designation, and joining date.
As a member of the development team, you have been askedto develop an application that will display the employee details.
Hint: You need to refer to the Employee table of the HRdatabase.
Demo: Retrieving Data From a SQL Database
-
7/30/2019 Ddcauado.net Session01
23/25
Slide 23 of 25Ver. 1.0
Developing Data-Centric Applications Using ADO.NET
Session 1
In this session, you learned that:
ADO.NET is a data access programming model for accessingthe data stored in a database from a .NET application.
The ADO.NET object model consists of two main components,data provider and dataset.
A data provider is used for connecting to a database, retrievingdata, storing the data in a dataset, reading the retrieved data,and updating the database.
The various types of data providers are:
.NET Framework data provider for SQL Server
.NET Framework data provider for OLEDB
.NET Framework data provider for ODBC
.NET Framework data provider for Oracle
Summary
-
7/30/2019 Ddcauado.net Session01
24/25
Slide 24 of 25Ver. 1.0
Developing Data-Centric Applications Using ADO.NET
Session 1
The four key components of a data provider are:
Connection
Commnd
DataReader
DataAdapter
The dataset is memory-based relational representation of data.The main features of ADO.NET are:
Disconnected data architecture
Data cached in datasets
Scalability
Data transfer in XML format
Summary (Contd.)
-
7/30/2019 Ddcauado.net Session01
25/25
Developing Data-Centric Applications Using ADO.NET
In order to create and manage connection to the database, youneed to perform the following steps:
1. Create a connection object.
2. Create a command object.
3. Open the connection object.
4. Execute the SQL statement in the command object.5. Close the connection object.
The two key events for the SqlConnection class are:
StateChange event
InfoMessage event
Connection pooling enables a data source to reuseconnections for a particular user.
Summary (Contd.)