0% found this document useful (0 votes)
48 views25 pages

Session 01

This document provides an overview of developing database applications using ADO.NET and XML. It discusses the rationale for using ADO.NET to manage data stored in relational databases and XML documents. The document outlines the prerequisites, case study, objectives, key concepts like the ADO.NET object model, features of ADO.NET, and how to create and manage database connections using ADO.NET. It also provides examples of executing SQL statements and handling connection events.

Uploaded by

Nandini Nandu
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPS, PDF, TXT or read online on Scribd
0% found this document useful (0 votes)
48 views25 pages

Session 01

This document provides an overview of developing database applications using ADO.NET and XML. It discusses the rationale for using ADO.NET to manage data stored in relational databases and XML documents. The document outlines the prerequisites, case study, objectives, key concepts like the ADO.NET object model, features of ADO.NET, and how to create and manage database connections using ADO.NET. It also provides examples of executing SQL statements and handling connection events.

Uploaded by

Nandini Nandu
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPS, PDF, TXT or read online on Scribd
You are on page 1/ 25

Developing Database Applications Using ADO.

NET and XML


Rationale

Business applications need to manage voluminous data. Data is generally stored in a relational database in the form of related tables or is stored in text format in XML documents. Most business applications allow users to retrieve the data stored in a database and present it in a user-friendly interface without writing the database commands. ADO.NET is a model used by .NET applications to communicate with a database for retrieving, accessing, and updating data. This module will provide the necessary skills to the students to work as a database application developer in the industry.

Ver. 1.0

Session 1

Slide 1 of 25

Developing Database Applications Using ADO.NET and XML


Prerequisites

A student registering for this module should be able to perform the following tasks:
Work with XML
Work with SQL queries

Ver. 1.0

Session 1

Slide 2 of 25

Developing Database Applications Using ADO.NET and XML


Case Study - Tebisco

Brief History
Tebisco is a leading producer and distributor of snacks in the U.S., as well as in most of the companys 23 international markets. In 1998, consumers spent $9.2 billion on Tebiscos snacks, $1.4 billion more than in the previous year. Tebisco started as a small bakery in Round Rock, Texas in 1978. In a short time, its gingersnaps, macaroons, shortbread and other cookies were popular all over the U.S. Three years ago, 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 system whereby the information about all the HR activities is maintained.

Ver. 1.0

Session 1

Slide 3 of 25

Developing Database Applications Using ADO.NET and XML


Objectives

In this session, you will learn to:


Understand the ADO.NET object model Create and manage connections

Ver. 1.0

Session 1

Slide 4 of 25

Developing Database Applications Using ADO.NET and XML


Understanding ADO.NET

Business applications allow users to retrieve data from a database by presenting data in a user-friendly interface. User need not remember the database commands for retrieving or updating data in the database. Microsoft has created a family of data access technologies to help programmers build efficient applications to access data, regardless of its source. The guidelines that can be followed for selecting an appropriate data access technology are:
Use ADO.NETODBC for writing a native code JDBC DB (OLE) for for writing a Microsoft code Microsoft writing a Java code targeting managed SQL Server. targeting the .NET Framework or C++. Windows by using C application, or based application, a VB 6 COMin Visual Basic, C#, and C++. a C++ application using COM.

Ver. 1.0

Session 1

Slide 5 of 25

Developing Database Applications Using ADO.NET and XML


Understanding ADO.NET (Contd.)

ADO.NET is a part of the .NET Framework architecture.

Ver. 1.0

Session 1

Slide 6 of 25

Developing Database Applications Using ADO.NET and XML


The ADO.NET Object Model

ADO.NET is based on an object model that is based on the standards laid down by W3C. The following figure shows the ADO.NET object model.

Ver. 1.0

Session 1

Slide 7 of 25

Developing Database Applications Using ADO.NET and XML


The ADO.NET Object Model (Contd.)

The two key components of ADO.NET Object model are:


Data provider Dataset 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

Ver. 1.0

Session 1

Slide 8 of 25

Developing Database Applications Using ADO.NET and XML


The ADO.NET Object Model (Contd.)

The two key components of ADO.NET Object model are:


Data provider Dataset Is a disconnected, cached set of records that are retrieved from a database. Is present in the Dataset class in the System.Data namespace. Has the following key components:
DataTableCollection DataRelationCollection DataTable DataRowCollection DataColoumnCollection

Ver. 1.0

Session 1

Slide 9 of 25

Developing Database Applications Using ADO.NET and XML


Just a minute

Which of the following components of a data provider is used to retrieve, insert, delete, or modify data in a data source?
1. 2. 3. 4. Connection Command DataReader DataAdapter

Answer:
2. Command

Ver. 1.0

Session 1

Slide 10 of 25

Developing Database Applications Using ADO.NET and XML


Features of ADO.NET

The key features of ADO.NET are:


Disconnected data architecture Data cached in datasets Scalability Data transfer in XML format Applications connect to the database only while The data is retrieved retrieving anddatasets. and storedoperations Database in updating data. You performedwiththe are can work on the XML is the fundamental Connection withofaon records insteadinthe datasetfor data transfer format stored database is closed, dataset as you work the database. in ADO.NET. oncereal data. is with result,dataset is As a the data Because a resources retrieved. and the Thesaved is are dataset XML stored in the Connection candatathe independentis meet database can format, you of re-established when of source anddemands increasing between transmit it you remain the data typesfrombe disconnected of the users more efficiently. different needs to updated. data source. applications.

Ver. 1.0

Session 1

Slide 11 of 25

Developing Database Applications Using ADO.NET and XML


Creating and Managing Connections

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.

Ver. 1.0

Session 1

Slide 12 of 25

Developing Database Applications Using ADO.NET and XML


Creating a Connection Object

Execute the following steps to create a connection to the database:


Create a SqlConnection object. SqlConnection connection = SqlConnection class is used to new SqlConnection(); connect to a SQL Server. connection.ConnectionString = The ConnectionString property provides information, such as the Name of the Server to be used "Data Source=SQLSERVER01; data source and database name, when a connection is open that is used to establish a Initial Catalog=HR; Name of the database connection with a database. User ID=sa; Used to specify the Server login account Password=password"; Login password for the Server account

Ver. 1.0

Session 1

Slide 13 of 25

Developing Database Applications Using ADO.NET and XML


Just a minute

Which of the following parameters of ConnectionString is used to specify the name of the database?
1. 2. 3. 4. Provider Initial Catalog Data source Database

Answer:
2. Initial Catalog

Ver. 1.0

Session 1

Slide 14 of 25

Developing Database Applications Using ADO.NET and XML


Creating a Command Object

Execute the following steps to create a command object:


SqlCommand cmd = new SqlCommand (SELECT * FROM monthlysalary ,connection);
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 to be executed and the SqlConnection object.

Ver. 1.0

Session 1

Slide 15 of 25

Developing Database Applications Using ADO.NET and XML


Opening the Connection Object

Execute the following steps to open a connection:


//SqlConnection connection connection.Open();
It opens a database connection with the property settings specified by the ConnectionString property.

Ver. 1.0

Session 1

Slide 16 of 25

Developing Database Applications Using ADO.NET and XML


Executing SQL Statements in the Command Object

To execute the query passed in the Command object, you can call one of the following methods:

// Creating a SqlConnection object SqlConnection connection = new SqlConnection(); // Creates a connection string to the HR database 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();
Session 1 Slide 17 of 25

Ver. 1.0

Developing Database Applications Using ADO.NET and XML


Closing the Connection Object

Execute the following steps to close a connection:


//SqlConnection connection connection.Close();
It closes the connection to the database.

Ver. 1.0

Session 1

Slide 18 of 25

Developing Database Applications Using ADO.NET and XML


Closing the Connection Object (Contd.)

Handling Connection Events:


The two key events for the SqlConnection class are:
StateChange event InfoMessage event This event occurs when the state of the connection changes. This event occurs when an It receives an message or type informational argument of StateChangeEventArgs. warning is returned from a data StateChangeEventArgs has the source. following properties: It receives an argument of type CurrentState SqlInfoMessageEventArgs. OriginalState SqlInfoMessageEventArgs has the following properties:
Errors Message Source

Ver. 1.0

Session 1

Slide 19 of 25

Developing Database Applications Using ADO.NET and XML


Implementing Connection Pooling

Connection pooling enables a data source to reuse connections for a particular user. Connection pooling is controlled by certain parameters that are placed into the connection string.
Connection timeout Min pool size Max pool size Pooling Connection reset Load balancing timeout, connection lifetime Enlist
It is the time in seconds to wait while a connection tothe data It is used to mention the source is attempted. The minimum to mention the It is used number of default valuemaintained in the seconds. connections is 15 of maximum number the When true, it causes pool. The default value the pool. connections allowed in is 0. to request for a new connection It indicates that the database The default value is 100. be drawn from the pool. connection the maximum time It specifies will be reset when the connection is removed from in seconds that a pooled the pool. should live. connection

When the value is true, the connection is automatically enlisted into the creation threads current transaction context.
Slide 20 of 25

Ver. 1.0

Session 1

Developing Database Applications Using ADO.NET and XML


Implementing Connection Pooling (Contd.)

A request for a connection is made by the application using the Open() method.

If the Pooling property is set to true, the pooler attempts to acquire a connection from the pool otherwise a new connection is created. Close the connection by calling the close() method.

Ver. 1.0

Session 1

Slide 21 of 25

Developing Database Applications Using ADO.NET and XML


Demo: Retrieving Data from a SQL Database

Problem Statement:
Tebisco is a leading producer and distributor of snacks in the United States. It is planning to start its annual appraisal process. Before starting up with the appraisal process, the senior management requires a list of all employees. The details include employee name, employee code, current position, designation, and joining date. As a member of the development team, you have been asked to develop an application that will display the employee details. Hint: You need to refer to the Employee table of the HR database.

Ver. 1.0

Session 1

Slide 22 of 25

Developing Database Applications Using ADO.NET and XML


Summary

In this session, you learned that:


ADO.NET is a data access programming model for accessing the 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, retrieving data, 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

Ver. 1.0

Session 1

Slide 23 of 25

Developing Database Applications Using ADO.NET and XML


Summary (Contd.)

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

Ver. 1.0

Session 1

Slide 24 of 25

Developing Database Applications Using ADO.NET and XML


Summary (Contd.)

In order to create and manage connection to the database, you need 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 reuse connections for a particular user.

Ver. 1.0

Session 1

Slide 25 of 25

You might also like