Read the most frequently asked 65 top ADO.Net interview questions and answers for freshers and experienced job interview questions pdf
1. What is ADO.NET?
ADO.NET is a part of the Microsoft .NET Framework. This framework provides the set of classes that deal with data communication between various layers of the software architecture and the database. It provides a continuous access to different data source types such as SQL Server versions 7, 2000, 2005. It also provides connectivity options to data sources through OLE DB and XML. Connectivity may be established with other databases like Oracle, MySQL etc. as well.
ADO.NET has the ability to separate data access mechanisms, data manipulation mechanisms and data connectivity mechanisms.
ADO.NET introduces along with it the disconnected architecture. In a disconnected architecture, data may be stored in a DataSet. It contains providers for connecting to databases, commands for execution and retrieval of results.
The classes for ADO.NET are stored in the DLL System.Data.dll.
2. can we connect two dataadapters to same data source using single connection at same time?
yes,we can connect two dataadapters to same datasource using single connection at same time.
There is a technology in ado.net 2.0 called MARS usinng Mars in connection string we can do it.
for eg:
cn.ConnectionString = “server=(local); database=employee; integrated security=sspi; MultipleActiveResultSets=True”;
3. Can we do database operations without using any of the ADO.net objects?
No its not at all possible.
4. If we are not returning any records from the database, which method is to be used?
There is a method called Execute Non Query. This method executes the Update, Delete etc. This does not return any rows but will give the number of rows affected.
5. how can i retrieve two tables of data at a time by using data reader?
Data reader read and forward only, how is it possible to get 2 tables of data at a time?
yes this is possible
If we execute 2 select command either in stored procedure or in select command and then executereader method fired of command object. it return 2 tables in datareader.
like :
string str=”Select * from a;select * from b”;
cmd.commandtext=str;
dr=cmd.executereader();
Now it return 2 tables in datareader (dr).
6. Explain ExecuteNonQuery?
// Summary:
// Executes a Transact-SQL statement against the connection and returns the number of rows affected.
// Returns:
// The number of rows affected.
7. What is the ExecuteScalar method?
// Summary:
Executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.
// Returns:
The first column of the first row in the result set, or a null reference (Nothing in Visual Basic) if the result set is empty.
8. Which one of the following objects is a high-level abstraction of the Connection and Command objects in ADO.NET?
DataReader DataSet DataTable DataView DataAdapter
Answer: DataAdapter
9. How can we load multiple tables in to Dataset?
DataSet ds=new DataSet();
SqlDataAdapter dap=new SqlDataAdapter(Select * from ,);
dap.Fill(ds,”TableOne”);
SqlDataAdapter dap1=new SqlDataAdapter(Select * from ,);
dap1.Fill(ds,”tableTwo”);
10. What is connection String?
connection String – a string which contains address of the database we want to connect to.
11. What is Delegate?
Delegate is an important element of C# and is extensively used in every type of .NET application. A delegate is a class whose object (delegate object) can store a set of references to methods.
12. How do you update a Dataset in ADO.Net and How do you update database through Dataset?
a. Update a dataset;
Dataset ds = new dataset();
SqlDataAdapter adp = new SqlDataAdapter(Query,connection);
Adp.fill(ds);
Again you can add/update Dataset as below
SqlDataAdapter adp1 = new SqlDataAdapter(Query1,connection);
Adp1.fill(ds);
b. Update database through dataset.
SqlCommandBuilder mySqlCommandBuilder = new SqlCommandBuilder(mySqlDataAdapter);
Foreach(datarow dr in ds.table[0].rows)
{
Dr[“column Name”] = “value”;
mySqlDataAdapter.Update(ds);
}
13. What are the steps to connect to a database?
1. Create a connection. This requires a connection string, which can be given declaratively or put in a well defined place like the .config files. Advantage of keeping in .config files is that it enables use of Connection Pooling by .Net framework, else even one small change in connection string will cause CLR to think it’s not the same connection and will instantiate new connection for other request.
2. Open the connection and keep it open until done, typically done as using (con) { //use }
3. If using connected data model, create a SqlCommand object, decorate it with desired command, command type (stored procedure for eg), add any parameters and their values to the command, and then consume the command by using ExcuteReader or ExecuteScalar. In case of ExecuteReader, we will get back a handle to a fast-forward, read only pointer to the recordset. We can also decorate Command object with multiple recordsets in 2.0 and execute one by one (MARS – Multiple Active Record Sets)
4. If using disconnected data model, create a DataAdapter object, decorate it with desired SELECT, INSERT, UPDATE, DELETE commands, add parameters as necessary and then fill up a DataSet or DataTable using the DataAdapter. Subsequent SQL can be executed using insert, update, delete commands on the dataset.
14. How do you connect to SQL Server Database without using sqlclient?
you can connect sql using oledbname space .
15. What is Partial class?
A Partial class is a class that can be split into two or more classes. This means that a class can be physically separated into other parts of the class within the same namespace. All the parts must use the partial keyword. All the other classes should also have the same access modifier. At the compile time, all the partial classes will be treated as a single class. Let us list some advantages of having partial classes.
16. what are the advantages and disadvantages of using datalist?
Adv: the DataList’s display is defined via templates,DataList allows for much more customization of the rendered HTML markup , by which it is more user-friendly displays of data.
DisAdv: adding such functionality with the DataList takes more development time than with the DataGrid, as,
1. The Edit/Update/Cancel buttons that can be created in a DataGrid via the EditCommandColumn column type, must be manually added to the DataList, and
2. The DataGrid BoundColumn column types automatically use a TextBox Web control for the editing interface, whereas with the DataList you must explicitly specify the editing interface for the item being edited via the EditItemTemplate.
3. we can’t do the paging and sorting with datalist controls.
17. What is the difference between data reader and data adapter?
DateReader is an forward only and read only cursor type if you are accessing data through DataRead it shows the data on the web form/control but you can not perform the paging feature on that record(because it’s forward only type). Reader is best fit to show the Data (where no need to work on data)
DataAdapter is not only connect with the Databse(through Command object) it provide four types of command (InsertCommand, UpdateCommand, DeleteCommand, SelectCommand), It supports to the disconnected Architecture of .NET show we can populate the records to the DataSet. where as Dataadapter is best fit to work on data.
18. What is the difference between data reader and data set?
1) DataSet is disconnected object type. It uses XML to store data.
2) It fetches all data from the data source at a time
3) Modifications can be updated to the actual database
4) It will reduce the application performance.
19. Does SQLClient and OLEdb class share the same functionality?
No, each have its own functionality,
ex : for sql client , there is SqlConnection object
and for oledb client , there is OleDBConnection
20. why edit is not possible in repeater?
It has no such feature.
21. Difference between SqlCommand and SqlCommandBuilder?
a) SQLCommand is used to execute all kind of SQL queries like DML(Insert, update,Delete) & DDL like(Create table, drop table etc)
b)SQLCommandBuilder object is used to build & execute SQL (DML) queries like select, insert, update & delete.
22. Why ca not we use Multiple inheritance and garbage collector paralelly in .net?
.net doesn’t support the mutiple inheritance, perhaps you may talk about multi-level inheritance.
In the later case, if a class is inherited from another class, at the time of creating instance, it will obviously give a call to its base class constructor (ie bottom – top approach). Like wise the constructor execution is takes place in top down approach (ie. base class constructor is executed and the derived class constructor is executed).
So for GC, it will collect only when an object does not have any reference. As we see previously, the derived is constructed based on base class. There is a reference is set to be. Obviously GC cannot be collected.
23. How to find the given query is optimized one or not?
First Excute Sql Quries in Query Analzer,see How much time 2 take Excute , if Less then the ur desired Time, then it will Optimize query
24. How to copy the contents from one table to another table and how to delete the source table in ado.net?
it is possible
DataSet ds;
sqlAdap.Fill(ds);
Datatable dt = ds.Tables[0].copy();
//now the structure and data are copied into ‘dt’
ds.Tables.remove(ds.Table[0]);
//now the source is removed from the ‘ds’
25. How to call the SQL commands asynchronously in ADO.NET version 2.0?
executescalar()
executereader()
executenonquery()
these comes with Begin and End like Beginexecutescalr() Endexecutescalar()…….
by using these command we can achieve asynch comm in ado.net
26. what is typed and untyped dataset?
A DataSet can be Typed or Untyped. The difference between the two lies in the fact that a Typed DataSet has a schema and an Untyped DataSet does not have one. It should be noted that the Typed Datasets have more support in Visual studio.
27. I loaded the dataset with a table of 10 records. One of the records is deleted from the backend, How do you check whether all the 10 records were present while updating the
data(Which event and steps) and throw the exception.
By Using the Transactions we can check the Exact Numbers of the rows to be updated and if the updation fails then the Transaction will be rollbacked.
28. Can dataReader hold data from multiple tables?
data reader can hold data from multiple tables and datareader can hold more than one table.
string query=”select * from employee; select * from student”;
sqlcommand cmd=new sqlcommand(query, connection);
sqldatareader dr=cmd.executeReader();
if(dr.hasrows)
{
dr.read();
gridview1.DataSource=dr;
gridview1.Databind();
if(dr.nextresult)
{
gridview2.datasource=dr;
gridview2.databind();
}
}
dr.colse();
connection.close();
29. What is different between SqlCommand object and Command Behavior Object?
DO.NET Command Object – The Command object is similar to the old ADO command object.
It is used to store SQL statements that need to be executed against a data source.
The Command object can execute SELECT statements, INSERT, UPDATE, or DELETE statements, stored procedures, or any other statement understood by the database.
30. what is bubbled event can u pls explain?
all heavy controls like grid view,datagrid or datalist,repeater controls cantains the chield controls like button or link button, when we click this button then the event will be raised, that events are handled by parant controls,that is called event bubbling,means event is bubbled from bottom(chield)to up(parant).
31. If a table contains 20000 records . In a page at each time 100 records to be displayed.
What are the steps u will take to improve performance? will you use dataset or datareader?
we have to use a dataset because on using datareader forward only paging can be achieved.
Suppose if you are at 1000 page and you want to go back to 999th page, if you use datareader it cannot be achieved, since it does not support backward navigation.
Dataset supports forward and backward navigation
32. What is data access layer?
Data Access layer is actually a part of Architecture layer. It has 2 tier,3 tier or N tier Layer. Generally we use 3 tier Layer 1) Presentation layer,Business Logic layer and then Data Access Layer. Data Access layer is a medium to talk between database and Business Logic layer.
It helps to maintain flexibility,resuablity and even secuity also. In security SQL Injection can be stopped with 3 iter Archietcture.
33. What are the different row versions available in table?
There are four types of Rowversions.
Current:
The current values for the row. This row version does not exist for rows with a RowState of Deleted.
Default :
The row the default version for the current DataRowState. For a DataRowState value of Added,
Modified or Current, the default version is Current. For a DataRowState of Deleted, the version is Original.
For a DataRowState value of Detached, the version is Proposed.
Original:
The row contains its original values.
Proposed:
The proposed values for the row. This row version exists during an edit operation on a row, or for a row that is not part of a DataRowCollection.
34. What are the two fundamental objects in ADO.NET?
Datareader and Dataset are the two fundamental objects in ADO.NET
35. What we do with the object of ado.net dataset after using it?Can we dispose it or can we set it nothing?Is it must or not?
we use dispose
36. What provider ADO.net use by default?
Ado.net uses no Dataprovider by default and this is absulotely correct answere. there is no other choice for this question
37. What is the provider and namespaces being used to access oracle database?
The provider name is oledb and the namespace is system.data.oledb
38. Explain acid properties?
The term ACID conveys the role transactions play in mission-critical applications. Coined by transaction processing pioneers, ACID stands for atomicity, consistency, isolation, and durability.
These properties ensure predictable behavior, reinforcing the role of transactions as all-or-none propositions designed to reduce the management load when there are many variables.
39. What is Atomicity?
A transaction is a unit of work in which a series of operations occur between the BEGIN TRANSACTION and END TRANSACTION statements of an application. A transaction executes exactly once and is atomic ? all the work is done or none of it is.
Operations associated with a transaction usually share a common intent and are interdependent.
By performing only a subset of these operations, the system could compromise the overall intent of the transaction. Atomicity eliminates the chance of processing a subset of operations.
40. What is Isolation?
A transaction is a unit of isolation ? allowing concurrent transactions to behave as though each were the only transaction running in the system.
Isolation requires that each transaction appear to be the only transaction manipulating the data store, even though other transactions may be running at the same time. A transaction should never see the intermediate stages of another transaction.
Transactions attain the highest level of isolation when they are serializable. At this level, the results obtained from a set of concurrent transactions are identical to the results obtained by running each transaction serially.
Because a high degree of isolation can limit the number of concurrent transactions, some applications reduce the isolation level in exchange for better throughput.
41. what is data Adapter?
Data adapter is bridge between Connection and DataSet , Data adapter in passing the sql query and fill in dataset
42. How to find the count of records in a dataset?
DS.Tables[“tabname”].Rows.Count;
we can get count of the records.
43. Difference between ADO and ADO.NET
ADO:
1.It is a COM based library.
2.Classic ADO requires active connection with the data store.
3.Locking feature is available.
4.Data is stored in binary format.
5.XML integration is not possible.
6.It uses the object named Recordset to reference data from the data store.
7.Using Classic ADO, we can obtain information from one table or set of tables through join. We cannot fetch records from multiple tables independently.
8.Firewall might prevent execution of Classic ADO.
9.Classic ADO architecture includes client side cursor and server side cursor.
10.We cannot send multiple transactions using a single connection instance.
ADO.NET:
1.It is a CLR based library.
2.ADO.NET architecture works while the data store is disconnected.
3.Locking feature is not available.
4.Data is stored in XML.
5.XML integration is possible.
6.It uses Dataset Object for data access and representation.
7.Dataset object of ADO.NET includes collection of DataTables wherein each DataTable will contain records fetched from a particular table. Hence multiple table records are maintained independently.
8.ADO.NET has firewall proof and its execution will never be interrupted.
9.ADO.NET architecture doesn’t include such cursors.
10.We can send multiple transactions using a single connection instance.
44. Briefly explain some of the components of ADO.NET Data providers:
The following are some of the most important components of ADO.NET data providers:
Connection: This is the object responsible for establishing a connection to the database.
Command: The object responsible for executing commands and stored procedures.
ExecuteReader: Returns a complete set of results
ExecuteScaler: Returns only a single value
ExecuteNonQuery: Execute a command on a database but it doesn’t return any value from the database. Used mostly for insertion and update.
DataReader: Provides read-only and forward-only access to a database.
45. Elaborate on some of the advantages of using stored procedures in ADO.NET.
The following are some of the benefits of implementing ADO.NET stored procedures:
Stored procedures reside in databases in compiled form. This results in extremely fast performance.
Stored procedures require less network traffic and bandwidth to execute on remote databases since they do not contain any extra information but only compiled data.
Stored procedures are secure and invulnerable to any outside threat data might result in loss or leakage of data.
46. Do ADO.NET supports stored procedures?
Yes, ADO.NET contains a complete framework for executing stored procedures on a data set. Stored procedures in ADO.NET are used mostly for common repetitive functions.
47. Differentiate between Boxing and Unboxing
The process of storing a value type into a reference type variable or object is known as boxing. Unboxing is the opposite of boxing and is referred to as the process of storing a reference type into a value type variable. In .NET, boxing is done implicitly, while a casting mechanism is required to achieve unboxing.
48. Briefly mention some of the benefits of ADO.NET data access technology.
The following are some of the important benefits of using ADO.NET for data access:
ADO.NET objects are highly programmable and are flexible enough to be tweaked and twisted to achieve the desired functionality.
Code written in ADO.NET is highly maintainable and new data access features can be added to existing application imperceptibly.
ADO.NET objects are simple .NET objects that translate into MSIL code. Therefore, ADO.NET code written in one language can be seamlessly used with other .NET languages as well.
ADO.NET supports data scalability and is independent of the amount of data stored in the underlying database. Therefore, if data source expands or shrinks, the impact on the ADO.NET classes is minimal.
49. What is ADO.NET DataAdapter?
To perform communication between ADO.NET data sources such as databases and XML files and a DataSet, the DataAdapter object is used. All CRUD (create, read, update and delete) queries are performed using this DataAdapter.
50. What is Object Pooling in ADO.NET?
In .NET, whenever functionality has to be performed, the Class that contains that functionality is instantiated. The object of the class is created, desired functionality is performed and then that object is destroyed. This process of creating and destroying objects is expensive.
In ADO.NET objects are consistently created and destroyed as the application is connected to database, fetches records from database and disconnects from database. Therefore, the objects that are frequently used are placed in a pool from where they can be accessed whenever a related functionality needs to be performed. This phenomenon is known as object pooling.
51. What is an ADO.NET DataReader?
The DataReader object in ADO.NET is used to sequentially read data from an underlying data source. The DataReader object provides read-only access to data and cannot be used for DDL and DML operations.
52. List the 4 common ADO.NET Namespaces?
System.Data : Contains Classes, types, and services for creating and accessing data sets and their subordinate objects
System.Data.SqlClient : Contains Classes and types for accessing Microsoft SQL Server databases
System.Data.OracleClient : Contains Classes and types for accessing Oracle databases (Microsoft .NET Framework version 1.1 and later)
System.Data.OleDb : Contains Classes and types for accessing other databases
53. Why will you usually create an ASPNET user account in the Database for an ASP.NET web application?
Web applications run using the ASPNET user account. The SQL database administrator will have to set up this account and grant it permissions before your Web application will have access to a SQL database. For file-based databases, such as Microsoft Access, you must grant permissions on the database file to the ASPNET user account using Windows file security settings.
54. Can you inherit from SqlConnection Class?
No, you cannot inheirt from SqlConnection Class. SqlConnection Class is a sealed class. It is a compile time error.
55. Will the connection be closed, if the SqlConnection object goes out of scope?
No, If the SqlConnection goes out of scope, it won’t be closed. Therefore, you must explicitly close the connection by calling Close or Dispose.
56. What happens if connection pooling is enabled?
If connection pooling is enabled and when you call Close or Dispose methods, then the connection is returned to the connection pool. This connection can then be resused.If connection pooling is disabled and when you call Close or Dispose methods, the underlying connection to the server is actually closed.
57. How do you ensure that the database connections are always closed?
To ensure that the database connections are always closed, open the connection inside of a using block, as shown in the following code fragment. Doing so ensures that the connection is automatically closed when the code exits the block.
using (SqlConnection ConnectionObject = new SqlConnection())
{
ConnectionObject.Open();
//The database connection will be closed when the control exits the using code block
}
58. How do you read an XML file into a DataSet?
Using the DataSet object’s ReadXML method.
59. When do you use ExecuteReader, ExecuteNonQuery, ExecuteScalar methods?
If the command or stored procedure that is being executed returns a set of rows, then we use ExecuteReader method.
If the command or stored procedure that is being executed returns a single value then we use ExecuteScalar method.
If the command or stored procedure performs INSERT, DELETE or UPDATE operations, then we use ExecuteNonQuery method. ExecuteNonQuery method returns an integer specifying the number of rows inserted, deleted or updated.
60. Can your class inherit from SqlCommand Class?
No, you cannot inheirt from SqlCommand Class. SqlCommand Class is a sealed class. It is a compile time error.
61. Give an example that shows how to execute a stored procedure in ADO.NET?
using (SqlConnection ConnectionObject = new SqlConnection())
{
//Specify the name of the stored procedure to execute and the Connection Object to use
SqlCommand CommandObject = new SqlCommand(“StoredProcedureName”, ConnectionObject);
//Specify the SQL Command type is a stored procedure
CommandObject.CommandType = CommandType.StoredProcedure;
//Open the connection
ConnectionObject.Open();
//Execute the Stored Procedure
int RecordsAffected = CommandObject.ExecuteNonQuery();
}
62. Can you reuse a SqlCommand object?
Yes, you can reset the CommandText property and reuse the SqlCommand object.
63. What are the methods that can ensure asynchronous execution of the Transact-SQL statement or stored procedure?
BeginExecuteNonQuery
BeginExecuteReader
64. What is SqlCommand.CommandTimeout Property used for?
CommandTimeout Property is used to Get or set the wait time before terminating the attempt to execute a command and generating an error.
//Specify the CommandTimeout property value
SqlCommand CommandObject = new SqlCommand(“StoredProcedureName”, ConnectionObject);
//Wait for 10 seconds to execute the Stored procedure
CommandObject.CommandTimeout = 10;
The time is in seconds. The default is 30 seconds.
65. What is the use of connection object?
ADO.Net Connection object is used to establish a connection between application and the data source. SQL Commands can be executed once this connection has been established. It is mandatory to close the connection object once data base activities are completed.