Session 01
Session 01
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
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
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
Ver. 1.0
Session 1
Slide 4 of 25
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
Ver. 1.0
Session 1
Slide 6 of 25
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
Ver. 1.0
Session 1
Slide 8 of 25
Ver. 1.0
Session 1
Slide 9 of 25
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
Ver. 1.0
Session 1
Slide 11 of 25
Ver. 1.0
Session 1
Slide 12 of 25
Ver. 1.0
Session 1
Slide 13 of 25
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
Ver. 1.0
Session 1
Slide 15 of 25
Ver. 1.0
Session 1
Slide 16 of 25
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
Ver. 1.0
Session 1
Slide 18 of 25
Ver. 1.0
Session 1
Slide 19 of 25
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
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
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
Ver. 1.0
Session 1
Slide 23 of 25
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
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.
Connection pooling enables a data source to reuse connections for a particular user.
Ver. 1.0
Session 1
Slide 25 of 25