ADO.NET

Question 1

What is ADO.NET?

Answer 1

ADO.NET is a set of classes (a framework) to interact with data
sources such as databases, XML files, text files or Excel files etc.

Question 2

What is ADO an acronym for?

Answer 2

ADO is the acronym for ActiveX Data Objects.

Question 3

What are the two types of connection architectures?

Answer 3

Connected and disconnected.

Question 4

What is Connected Architecture?

Answer 4

The application remains connected with the database throughout the processing.

Question 5

What is Disconnected Architecture?

Answer 5

The application automatically connects/disconnects during processing. On the application side, it uses temporary data called a DataSet.

Question 6

What is a connected environment?

Answer 6

A connected environment is one in which an application is constantly connected to a data source.

Question 7

What are the advantages of connected environments?

Answer 7

Advantages:
- Data concurrency issues are easily controlled.
– At a time only one user can update the data in database.
- Data is always updated.

Disadvantages:
- A constant network connection is required which may lead to network traffic logging
- Scalability and performance issues in application.
– If more than one user connected to database, query processing may be slow

Question 8

What is a disconnected environment?

Answer 8

A disconnected environment is one in which a user is not necessarily connected with a database. Connection is required only at the time of retrieval or update and after that connection is closed.

Question 9

What are the advantages and disadvantages of a disconnected environment?

Answer 9

Advantages:
- Multiple applications can simultaneously interact with the database.
- Improve scalability and performance of applications.

Disadvantages:
- Data is not always updated.
- Data concurrency issues can occur when multiple users are updating the data to the data source.

Question 10

What are the important classes in ADO.NET?

Answer 10

Connection, Command, DataReader, DateAdapter, DataSet

Question 11

What is the connection class?

Answer 11

Connection is an object that provides database connectivity and the entry point to a database.

When the connection of an object is instantiated, the constructor takes
a connection string that contains the information about the database
server, server type, database name, connection type, and database
user credentials.

Question 12

What is the command class?

Answer 12

The Command class provides methods for storing and executing SQL statements and Stored Procedures.

Question 13

What is the DataReader class?

Answer 13

The DataReader is used to retrieve data.

It is used in conjunction with the Command class to execute an SQL
SELECT statement and then access the returned rows.

Question 14

What is the DataAdapter class?

Answer 14

This class is used in the Disconnected Model of ADO.NET.

The purpose of the DataAdapter is embedded in its name – it performs the activities necessary to get the data from the data source on the server into the database that’s held in the DataSet.

Question 15

What are the six steps in the connected model?

Answer 15

1. Create the SqlConnection object.
   * You’ll need the connection string to create it.
2. Create the SqlCommand object.
   * You need the SQL query in string format.
   * And SqlConnection object created in the previous step.
3. Open the connection.
4. Execute the SqlCommand.
5. Display the result.
6. Close the connection.

Question 16

What is a connection pool?

Answer 16

A connection pool is a cache of database connections maintained so that connections can be reused when future requests to the database are required.

Question 17

What does the ‘using’ statement do?

Answer 17

The using statement ensures that the SqlConnection is closed and disposed of properly, even if an exception occurs within the block.

Question 18

How do you use parameters in SQL?

Answer 18

Avoid String Concatenation: Using string concatenation for building SQL queries can lead to SQL injection attacks.
Use Parameters: Use the parameter features of ADO.NET to safely include parameters in SQL queries.

Question 19

What are three of the various commands executed by the SqlCommand class?

Answer 19

ExecuteReader, ExecuteScalar, ExecuteNonQuery

Question 20

What are the five steps used in the disconnected model?

Answer 20

1. Create the SqlConnection object.
   * You’ll need the connection string to create it.
2. Create the SqlDataAdapter object.
   * You need the SQL SELECT query in string format.
   * And SqlConnection object created in the previous step.
3. Create DataSet object.
   * It’ll be used to store the local copy of the database.
4. Fill the DataSet object using SqlDataAdapter object.
   * adapterObj.Fill(dataSetObj);
5. Use the DataSet object to display the result.

Question 21

What does the SqlDataAdapter class do?

Answer 21

The SqlDataAdapter manages connections with the data source and gives us
the disconnected behaviour. The SqlDataAdapter opens a connection only when required and closes it as
soon as it has performed its task.

Question 22

What are the two useful methods the SqlAdapterClass has?

Answer 22

Fill:
* This method opens the connection, stores data from data source into DataSet
and then closes the connection.
Update:
* This method opens the connection, saves data from DataSet to the data
source and then closes the connection.