1. What are the main objects in ADO.NET used for data access?
The four main objects of a data provider:-
1) Connection (This object used to connect a data to a Command object.)
2) Command object (This object used to connect connection object to Datareader or dataset.)
3) Data Adapter (This object acts as a bridge between datastore and dataset).
4) Datareader (This object reads data from data store in forward only mode)
2. What is Dataset?
The DataSet provides the basis for disconnected storage and manipulation of relational data. We fill it from a data store, work with it while disconnected from that data store, then reconnect and flush changes back to the data store if required.
3. How can we save all data from Dataset?
Dataset has “AcceptChanges()” method which commits all the changes since last time “AcceptChanges()” has been executed.
4. If we want to revert or abandon all changes since the dataset was loaded, what method should use?
By Using “RejectChanges()” Method.
5. What is the difference between Dataset Clone() and Dataset Copy()?
Clone: It only copies structure, does not copy data.
Copy: Copies both structure and data
6. What is Command Object?
Command objects are used to execute commands to a database across a data connection. They are used to connect connection object to Datareader or dataset. The Command objects can be used to execute stored procedures on the database, SQL commands, or return complete tables directly.
7. What are the methods that Command objects provides to execute commands on the database?
Following are the methods provided by command object:-
1). ExecuteNonQuery:- Executes the command defined in the CommandText property against the connection defined in the Connection property for a query that does not return any rows (an UPDATE, DELETE or INSERT). Returns an Integer indicating the number of rows affected by the query.
2). ExecuteReader:- Executes the command defined in the CommandText property against the connection defined in the Connection property. Returns a "reader“ object that is connected to the resulting rowset within the database, allowing the rows to be retrieved.
3). ExecuteScalar:- Executes the command defined in the CommandText property against the connection defined in the Connection property. Returns only a single value (effectively the first column of the first row of the resulting rowset). Any other returned columns and rows are discarded.
8. What is DataReader?
A DataReader is a lightweight object that provides read-only, forward-only data in a very fast and efficient way. Using a DataReader is efficient than using a DataAdapter but it is limited. Data access with DataReader is read-only meaning; we cannot make any changes (update) to data and forward-only, which means we cannot go back to the previous record which was accessed. A DataReader requires the exclusive use of an active connection for the entire time it is in existence.
9. When the DataReader is first returned where is it positioned?
DataReader is first returned it is positioned before the first record of the result set.
10. Which method should call to know record available in DataReader?
We need to call the “Read” method.
11. What happens if we read two or more DataReader in same connection on same time?
If we start to read another datareader without close first. A run time error occur and message show “There is already an open DataReader associated with this Connection which must be closed first.”
12. How can we force the connection object to close after datareader is closed?
Command method Executereader takes a parameter called as CommandBehavior where in we can specify saying close connection automatically after the Datareader is close. Such as-
13. How can we force the datareader to return only schema of the datastore rather than data?
If we want to force the datareader to return only schema of the datastore rather than data then we write in such way-:
pobjDataReader = pobjCommand.ExecuteReader(CommandBehavior.SchemaOnly)
14. What is the use of Connection Object?
The Connection object which provides a connection to the database through command object.
15. Which connection objects ADO.Net provide for database connectivity?
These connection objects are used to connect a data to a Command object.
1). An OleDbConnection object is used with an OLE-DB provider
2). A SqlConnection object uses Tabular Data Services (TDS) with MS SQL Server.
16. What is DataAdapter?
DataAdapter connect one or more Command objects to a Dataset object. They provide logic that gets the data from the data store and populates the tables in the DataSet, or pushes the changes in the DataSet back into the data store.
17. To create an empty table in the Dataset object with constraints then which DataAdapter's method should use?
We should use “FillSchema()” method that uses the SelectCommand to extract just the schema for a table from the data source, and creates an empty table in the Dataset object with all the corresponding constraints.
18. Can we perform Exception Handling in ADO.NET?
Yes, Exception handling is an in built mechanism in .NET framework to detect and handle run time errors.
19. If write try-catch-finally block and a run time error come then catch block execute after that statements written in finally block execute or not?
Statements written in finally block always get executed in spite of whether runtime error occurs or not
20. What is DataView?
DataView represents a complete table or small section of rows of DataTable depending on some criteria. It's best used for sorting and finding data within DataTable.
21. What method used to add new row to DataView?
AddNew() method adds a new row to the DataView object.
22. What is DataTable?
DataTable Represents one table of in-memory data and DataTable is a central object in the ADO.NET library. Other objects that use the DataTable include the DataSet and the DataView.
23. What method provided by DataRowCollection object to remove a DataRow object from DataTable depending on index position of the DataTable?
“RemoveAt()” method removes a DataRow object from DataTable depending on index position of the DataTable.
24. What is DataRelation?
Relations can be added between DataTable object’s using the DataRelation object
25. How can we add relations between tables in a DataSet?
In this sample code trying to build a relationship between “Customer” and “Addresses” “Datatable” using “CustomerAddresses” “DataRelation” object
Dim objRelation As DataRelation
Dim objDataSet As DataSet
26. What is DataRow?
DataRow Represents a row of data in a DataTable and DataRow object and its properties and methods to retrieve and evaluate; and insert, delete, and update the values in the DataTable
27. How can we know the current state of the row relative to its parent DataTable?
“RowState” property to determine the state of the row relative to its parent DataTable.
How the dataset’s update works internally?
By using Data adapter only, we update the database.
28. What is the namespace in which .NET have data functionality classes?
Following are the namespaces provided by .NET for data management:-
1) System.data: This contains the basic objects used for accessing and storing relational data, such as DataSet,DataTable and DataRelation. Each of these is independent of the type of data source and the way we connect to it.
2) System.Data.OleDB: This contains the objects that we use to connect to a data source via an OLE-DB provider, such as OleDbConnection, OleDbCommand, etc. These objects inherit from the common base classes, and so have the same properties, methods, and events as the SqlClient equivalents.
3) System.Data.SqlClient: This Contains the objects that we use to connect to a data source via the Tabular Data Stream (TDS) interface of Microsoft SQL Server (only). This can generally provide better performance as it removes some of the intermediate layers required by an OLE-DB connection.
4) System.XML: This Contains the basic objects required to create, read, store, write, and manipulate XML documents according to W3C recommendations.
29. What is difference between DataReader and DataSet?
Dataset is a disconnected architecture while datareader is connected architecture.
DataReader provides forward-only and read-only access to data, while the DataSet object can hold more than one table from the same data source as well as the relationships between them.
30. Which one is slower in DataSet and DataReader?
But one of the biggest drawbacks of DataSet is speed. As DataSet carry considerable overhead because of relations, multiple tables, speed is slower than DataReader. Always try to use DataReader wherever possible. as it’s meant specially for speed performance.
31. Which one persist contents in DataSet and DataReader?
Dataset can persists contents while datareader cannot persist contents, they are forward only.
32. What is DataProvider?
In ADO.NET, Data Provider provides access to datasource .it provides object to achieve functionalities like opening and closing connection, retrieve data and update data.