ADO.Net Frequently Asked Questions
ADO.NET stands for ActiveX Data Objects. It is a major component of .NET framework. It is designed to connect with different databases like SQL, MySQL, Oracle, and MS Access etc.
Following are the benefits of ADO.Net:
A .NET Framework data provider is used for connecting to a database, executing commands, and retrieving results.
The System.Data.SqlClient namespace.
DataReader and DataSet are the two fundamental objects in ADO.NET.
Datareader is an object of ADO.Net which provides access to data from a specified data source. It consists of classes which sequentially read data from a data source like Oracle, SQL or Access.
The DataSet is used to store data from a data source . It consists of a set of data tables. We can also specify the relation between the tables inside a dataset.
Datasets follow the disconnected data architecture.
Following are the Data Providers used in ADO.Net:.
- SQL server
OLEDB provider is used to access any database and provides flexibility of changing the database at any time. SQLClient provider is used to access only SQL Server database but it provides excellent performance than OLEDB provider while connecting with SQL Server database.
- Presentation Layer
- Business Logic Layer
- Database Access Layer
Connection object is used to connect data to a Command object.
- An OleDbConnection object is used with an OLE-DB provider.
- A SqlConnection object uses Tabular Data Services (TDS) with MS SQL Server.
You can connect SQL Server Database using oledb name space.
ADO.NET utilizes the power of XML by providing disconnected access to data. This is designed with the help of XML classes in .NET Framework which form the components of single architecture.
Yes, you can load multiple tables in a dataset.
Inside DataSet much like in Database, there are tables, columns, constraints, relationships, views and so forth.
Dataset is a connectionless data holder whereas RecordSet is connection oriented Data holder.
Data view is the representation of data in various formats and it can be requested by the users. Data can be exposed in different sort orders or filter on the user condition with the help of Data view. Data Customization is also possible through Data View.
The IsClosed property is used to check whether a DataReader is closed or opened. This property returns a true value if a Data Reader is closed, otherwise a false value is returned
There must be multiple processes to share the same connection describing the same parameters and security settings. The connection string must be identical.
Use Pooling = true in connection string if we want to enable connection pooling. To disable connection pooling set Pooling = false .
- Connect Timeout
- Max Pool Size
- Min Pool Size
OleDbDataAdapter is used to get the data from an Access database.
The ExecuteNonQuery() is one of the most frequently used method in SqlCommand Object, and is used for executing statements that do not return result sets.
The DataRelation object is used to add relationship between two DataTable objects.
The Clone() method copies only the structure of a DataSet. The copied structure includes all the relation, constraint, and DataTable schemas used by the DataSet. The Clone() method does not copy the data, which is stored in the DataSet.
The Copy() method copies the structure as well as the data stored in the DataSet.
Command is used to execute all kind of queries like DML and DDL. DML is nothing but Insert, Update and Delete. DDL are like Create and drop tables.
Command Builder object is used to build and execute DDL queries like Create and Drop Tables.
Yes, stored procedures are used in ADO.Net and it can be used for common repetitive functions.
The DataSource property and the DataMember property are used to bind a DataGridView control.
Params keyword is used to accept variable number of parameters.
The default timeout of Sqlcommand. CommandTimeout property is 30 Seconds.
A linked server is used to enable SQL server to execute commands against OLE DB data sources on remote servers.
In ADO Parameter object provides information about a single parameter used in a stored procedure or query.
- ExecuteScalar is typically used when your query returns a single value.
- ExecuteReader is used for any result set with multiple rows/columns
- ExecuteNonQuery() does not return data at all: only the number of rows affected by an insert, update, or delete.
You can use IsDBNull:
Filling a DataSet with multiple tables can be done by sending multiple requests to the database, or in a faster way: Multiple SELECT statements can be sent to the database server in a single request.
- GetChanges: gives the dataset that has changed since newly loaded or since Accept changes has been executed.
- HasChanges: this returns a status that tells if any changes have been made to the dataset since accept changes was executed.
We have the option of the fetching the starting record and the maximum record options.
It will retrieve the record from 5 to next 15 records.
- Pull model is used to take out the element/object from Database.
- Push model is used to insert the element at top position in table to database through object.