Ado.net C# Interview Questions

Ado.Net is the component that allows us to access data from database in most scalable way, when we think of data access performance, Ado.Net is the first choice over entity framework. Here are some latest Ado.net interview question answers for beginners.

1. What is ADO.NET?

   ADO stands for Active Data Object and ADO.NET is a set of .NET libraries for ADO

   ADO.NET is a collection of managed libraries used by .NET applications for data source communication using a driver or provider.

   ADO.NET has two types of architectures to work with datasource:
   Connected Architecture.
   Disconnected Architecture.
2. What are the key features of ADO.NET?
   • Disconnected Data Architecture.
   • DataSet.
   • DataAdapter
   • Bulk data Update to datasource using dataset
   • Scalability
   • Data transfer in XML Format.
   • DataReader for first reading
3. What is a Dataset?
   Dataset is an adonet object where data can be stored and manipulated without having any connection with actual data source. Dataset is designed in a way to work in disconnected architecture.

   • Dataset works in disconnected architecture
   • A DataSet object supports multiple tables from various databases
   • A DataSet object is slower to read data compare to DataReader
   • We can create relations in a dataset
   • A DataSet communicates with the Data Adapter only.
   • A Dataset supports reading and writing in XML

   
4. What is DataAdapter?
   Data Adapters is the bridge between a data source and a dataset.

   A Data Adapter contains a set of data commands and a database connection to fill the dataset and update a SQL Server database.

   There are four type of DataAdapters

   • SqlDataAdapter for SQL Server
   • OledbDataAdapter for OLE DB provider (like Excel)
   • OdbcDataAdapter for ODBC driver
   • OracleDataAdapter for Oracle

   DataAdapter supports mainly the following two methods
   Fill ():
   Update():

   DataSet ds=new DataSet()
                   
   SqlDataAdapter da=new SqlDataAdapter("Select * from  tbEmployee", con); 
                   
   da.Fill(ds,"Emp");

   What are DataReaders?
   DataReader is used to read data from data source, this is very first compare to DataSet We cannot store data in DataReader and do not maintain relation

   SqlConnection con = new SqlConnection("database connection string"); 
   con.Open(); 
         
   SqlCommand cmd = new SqlCommand("select * from tbStudent", con); 
   SqlDataReader dr = cmd.ExecuteReader(); 
         
   grid.DataSource = dr;       
   grid.DataBind(); 

5. What is Connection Pooling in ADO.NET?
   Connection pooling is the ability of reusing existing connection to the database.

   ADO.NET uses a technique called connection pooling, which minimizes the cost of repeatedly opening and closing connections. Connection pooling reuses existing active connections with the same connection string instead of creating new instance connection object to connect the database.

   <add name="sqlConnectionString" connectionString="Data Source=mySQLServer;Initial Catalog=myDatabase;Integrated Security=True;Connection Timeout=15;Connection Lifetime=0;Min Pool Size=0;Max Pool Size=100;Pooling=true;" />
   

   Here are the key attribute related to connection pooling

   • Connection Lifetime: Length of time in seconds after creation after which a connection is destroyed. The default is 0, indicating that connection will have the maximum timeout.
   • Max Pool Size: Maximum number of connections allowed in the pool. The default is 100.
   • Min Pool Size: Minimum number of connections maintained in the pool. The default is 0.
   • Pooling: When true, the connection is drawn from the appropriate pool, or if necessary, created and added to the appropriate pool. The default is true.
6. Explain SqlCommand Object in Ado.net
   SqlCommand carries the SQL statement to be executed on the database.
   Three important methods in SqlCommand object are:

   • ExecuteScalar
     is useful for returning a single value from the database
   • ExecuteNonQuery
     is used for executing (any DML statements) data manipulation on the database
   • ExecuteReader
     is used when we need to retrieve rows and columns of data using the SQL select statements.
7. How to implement transaction in ADO.Net ?
   In ADO.NET we can have two type of transaction support

   • Single-database transactions
     Single-database transaction is implemented using the .NET managed providers for Transaction and Connection classes from the System.Data namespace.
   • Distributed transactions.
     Distributed transaction model is implemented using classes in the namespace System.Transactions.
8. What are the differences between DataReader and DataSet?

   DataSet	DataReader
   1. Used in a disconnected architecture.	1. Work in a connected architecture only
   2. lower performance than DataReader	2. Provides better performance
   3. DataSet object has read/write access	3. DataReader has read-only access
   4. DataSet can object supports multiple tables from various databases.	4. DataReader object supports a single table based on a single SQL query of one database
   5. We can create relations in a dataset.	5. We can't create a relation in a data reader.
   6. We can modify data in DataSet.	6. We cannot modify data in DataReader.

9. What are the methods of DataSet?
   • Copy(): It copies the whole records with structure of DataSet.
   • Clone(): It copy only schema not full records of DataSet
   • Clear(): This method clears (removes) all rows from DataSet.
   • AcceptChanges(): This method saves changes which are made with records in a DataSet.
   • RejectChanges(): This method discard any changes made in DataSet and set the DataSet to previous stage
   • HasChanges(): Indicates if there is any changes made in dataset, This method return boolean value
   • GetChanges(): This method keep copy of those record, which is changed or modified.
10. What is ExecuteScalar method in ADO.NET?
    The ExecuteScalar method of the SqlCommand object is used for retrieving a single value from the database.
11. What is ExecuteNonQuery Method?
    ExecuteNonQuery method cannot be used to return the result set.
    
    ExecuteNonQuery method is used to execute the command and return the number of rows affected.

    Take a look at examples of ExecuteNonQuery in dbcommand

12. What is DataRelation class?
    It represents a relationship between database tables and correlates tables on the basis of matching column.

    DataRelation objDrel = new DataRelation("All", ds.Tables[0].Columns[0], ds.Tables[1].Columns[0]);