SOOPRO Pathshala provides ADO.NET Interview Questions and Answers
ADO.NET stands for ActiveX Data Object, it is a part of the .NET Framework by Microsoft. ADO.NET framework provides a set of classes that are used to handle data communication with data sources such as XML files and databases (such as SQL, Oracle, MySQL, MS Access, etc.).
In simple terms, it is used to provide connection between frontend and backend.
The DataSet is a collection of database tables(row and column format) that contain the data.
ADO-It is Component Object Modelling(COM) based.
ADO.NET-It is Common Language Runtime(CLR) based.
ADO-It uses the RecordSet object to access and store data from the data sources.
ADO.NET-It uses a DataSet object to access and store data from the data sources.
A DataAdapter is used to access data from a data source by functioning as a bridge between DataSet and a data source.
1. Typed DataSet: A typed DataSet is derived from the DataSet base class and can be created by selecting the DataSet option provided by Visual Studio.
2. Untyped DataSet: Untyped DataSet does not have an associated XML schema with it. Users are supposed to add columns, tables, and other elements to it. Properties can be set during design time or can add them during run time.
DataTable consists of a single database table that is placed within a memory.
DataSet consists of a collection of multiple database tables which is placed within a memory.
It has a row and column collection.
It has a database table collection.
Object pooling is a repository of the objects in memory that can be reused later without creating them. This object pooling reduces the burden of creating objects when it is required.
In ADO.NET, transactions are used when you want to bind several tasks together and execute them in the form of a single unit. Two types of transactions supported by ADO.NET are as follows:
Local Transaction: A local transaction is a single-phase transaction that is directly handled by the database. Every .NET Framework data provider has its own Transaction object for bringing out local transactions.
Distributed Transaction: A distributed transaction is coordinated by a transaction monitor and will make use of fail-safe mechanisms like two-phase commit for transaction resolution. This transaction will affect multiple resources.
Data binding in ADO.NET is the process through which user interface (UI) controls of a client application are configured to update or fetch data from data sources like a database or XML document. Using data binding, the user will be able to bind values to the particular control.
There are two types of data binding based on the type of binding offered:
Simple data binding: It is the process of binding the control with only one value in the dataset. The controls such as label, text box will be made bound to the control using the control properties.
Complex data binding: It is the method of binding the component with the Database. The controls can be a Dropdown list, GridView, or combo box. One or more than one value can be displayed from the dataset using the complex data binding.
Connection pooling allows you to reuse existing and active database connections, whenever there is a need, and thus increases the application performance.
ADO.NET is based on an Object Model where data residing in the database is accessed using a data provider. It is a technology of data access given by the Microsoft .Net Framework, which helps to communicate between relational and non-relational systems using a common group of components.
The components of ADO.NET architecture are:
Data Provider: It provides data to all the applications that perform the database updates. The application can access data through the DataSet or DataReader object. A data provider is a having group of components such as Command, Connection, DataReader, and DataAdapter objects. Command and Connection objects are the necessary components irrespective of the operations like Insert, Delete, Select, and Update.
Connection: The connection object is needed to connect with the database such as SQL Server, MySQL, Oracle, etc. To create a connection object, you must know about where the database is located(Ex: IP address or machine name, etc.) and the security credentials(Ex: user name and password-based authentication or windows authentication).
Command: The command object is the component where you will write the SQL queries. Then by using the command object, execute the queries over the connection. By using the command object and SQL queries, you will be able to fetch the data or send the data to the database.
DataReader: DataReader is a connected read-only RecordSet that is helpful in reading the records in the forward-only mode.
DataAdapter: The DataAdapter acts as a bridge between the dataset and command object. It receives the data from the command object and puts it into the data set.
DataSet: The DataSet is a disconnected RecordSet that can be browsed in both forward and backward directions. We can also update the data using the dataset. DataSet is filled by using DataAdapter.
DataView Class: A DataView allows you to create various views of data from DataTable, which can be used for data-binding applications. Using this, you can display the table with different order of sorting or you can filter the data based on a filter expression or by row state, etc.
XML: It is possible to create an XML representation of a dataset. In the dataset’s XML representation, data is represented in XML format and the database schema is represented in XML Schema Definition(XSD) language.
Connected Architecture:
In connected architecture, the connection must be kept open for accessing the data retrieved from the database. Connected architecture is based on Connection, DataReader, Command, and Transaction classes.
Disconnected Architecture:
In disconnected architecture, even if the database connection is closed, data retrieved from the database can be accessed. Disconnected architecture is based on classes connection, CommandBuilder, DataAdapter, DataSet, and DataView.
DataSet is a Disconnected Architecture because all records are brought at once and holding the database connection alive is not necessary.
A single value from the first row and first column of the ResultSet will be returned by ExecuteScalar() method on query execution.
Two types of authentication techniques are:
Windows Authentication: This default authentication is provided only through Windows domain accounts. This SQL Server security model is strongly integrated with Windows, so it is also referred to as integrated security.
SQL Server and Windows Authentication Mode(Mixed-mode): Authentication will be provided with the help of the Windows and SQL Server Authentication combination. User name and password pair will be maintained within SQL Server.
Disconnected Oriented Architecture can hold the data of multiple tables using dataset and single table data using Datatable.
Connected Oriented Architecture gives faster performance when dealing with smaller applications, with Select SQL queries and smaller data.
LINQ(Language Integrated Query) is a structured query syntax that helps the programmers and testers to retrieve data from various data sources such as Collections, XML Docs, ADO.NET DataSet, web service, MS SQL Server, etc.
It is integrated with C# or VB.NET and it eliminates the mismatch between different programming languages and databases. It provides a single querying interface for various data source types.
The method Clone() copies only the DataSet structure. The copied structure will have all the constraints, relations, as well as DataTable schemas used by the DataSet. It does not copy the data stored in the DataSet.
The Copy() method copies the DataSet structure along with the data in the DataSet. The original data will not be affected.
Serialization is the method of converting an object into a byte stream which can be stored as well as transmitted over the network. The advantage of serialization is that data can be transmitted in a cross-platform environment across the network and also it can be saved in a storage medium like persistent or non-persistent.
A DataView allows you to generate many data visualizations in a DataTable, which is useful in data-binding applications.
Different execute() methods supported by SqlCommandObject in ADO.NET is given below:
ExecuteScalar(): This method returns only a single value from the first row and first column of the ResultSet after the execution of the query. Even if ResultSet is having more than one row or column, all those rows and columns will be ignored. If the ResultSet is empty, it will return NULL.
ExecuteNonQuery(): This method returns the number of rows affected by the execution of a query. This method is not useful to return the ResultSet.
ExecuteReader(): This method returns an object of DataReader which is a read-only and forward-only ResultSet. It needs a live connection with the Data Source. We cannot directly instantiate the DataReader object. A valid DataReader object can be created with the help of the ExecuteReader() method.
ExecuteXmlReader(): This method builds an object of the XmlReader class and will return the ResultSet in the form of an XML document. This method is made available in SQL Server 2000 or later.
The two essential objects of ADO.Net are:
DataReader
DataSet
No. It is read-only and forward-only control, so it is not possible to edit data in a repeater control.
SqlDataReader object.
Data Grid: Data grid has advanced features and facilitates you to do many things like paging and to sort your data without much effort. Data grid can hold text data, but not linked or embedded objects.
Data Repeater: A data repeater doesn't have the paging feature, but it can be done by coding. A data repeater can hold other controls and can embed objects. A data repeater can embed a data grid within it but vice versa not possible.
Datatable is a collection of rows, columns, and constraints. A Datatable is able to perform disconnected data access.
A linked server enables the SQL server to implement commands against OLE DB data sources on remote servers.
The default timeout for SqlCommand.CommandTimeout property is 30 seconds.
No, it is not possible to modify data in the Repeater control.
The OLEDB provider helps access any database and offers flexibility of modifying the database at any time. SQLClient provider helps to access only the SQL Server database.
Command executes all kinds of queries such as DML and DDL. DML are the Insert, Update and Delete commands. DDL is like Create and Drop tables.
Command Builder object is used to create and execute DDL queries such as Create and Drop Tables.
Yes, we can load different tables in a single dataset.
The ExecuteNonQuery method executes the command and returns the number of rows affected.
The DataRelation is a class of disconnected architecture in the .NET framework. It is found in the System.Data namespace.
For a code-first concept, the user creates the entities and builds the database around them. The approach is useful to create smaller databases where there is more control over the process.
For a data-first concept, the user makes the database first and develops the entity models around it. I once used this method as an existing database or to make large ones.'
LINQ, which stands for language-integrated query, exists in the Microsoft.NET framework, and it enables users to address several query functions. To .NET languages, it adds some query expressions that enable programmers to build tools that accept data without delay and also without the use of a separate query language. The three LINQ technologies that ADO.NET use are DataSet, LINQ to Entities and LINQ to SQL, and they make available query capabilities in various ways.