Interview Questions of ADO.NET 1.Explain about Active X data objects? A.Active X data objects are used to access data sources and it is based upon the Component object model. This technology allows data base developers to write program without the knowledge of how database is implemented. Knowledge of SQL is absolutely not necessary to write programs in ADO. 2.How XML is supported in ADO.Net? A.The dataset is represented in the memory as an XML document. You can fill the dataset by XML and can also get the result in the form of XML. Since XML is an international and widely accepted standard, you can read the data using the ADO.Net in the XML form and pass it to other applications using Web Service. These data consuming application need not be the essentially Dot Net based. They may be written with Java, C++ or any other programming language and running on any platform. 3.What is a dataset? A.A dataset is the local repository of the data used to store the tables and disconnected record set. When using disconnected architecture, all the updates are made locally to dataset and then the updates are performed to the database as a batch. dataset is class of using system.data;it is used to store the multiple table and is called disconnected architecture and used to fill the table in dataset using dataadapter. 4.What is Partial class? A.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. 5.What is a Data View in ADO.NET? A.Data View - Just like we have Views in SQL (in our backed), we have Data View object in ADO.NET. A data view object represents bind able, customized view of a Data Table object. Operations like Sorting, Filtering, Searching, Editing and Navigation may be performed on a Data View object. In scenarios like retrieval of a subset of data from a Data table, we may make use of Data Views to get this data. Note that the Default View property of a Data Table returns the Default data view for the Data Table. In case a custom view of a Data Table has to be created on a Data View, then the Row Filter property of the Data View is set to the Default View. A data view may also be used to sort data that resides in it in ascending or descending order. Below is code on how to sort data in a data view in ascending or descending order... Data View objdv = new Data View(); objdv.Sort("Column Name Asc|Desc"); 6.What's the difference between accessing data with dataset or data reader? A.The dataset is generally used when you like to employ the disconnected architecture of the ADO.Net. It reads the data into the local memory buffer and perform the data operations (update, insert, delete) locally to this buffer. The data reader, on the other hand, is directly connected to the database management system. It passes all the queries to the database management system, which executes them and returns the result back to the application. Since no memory buffer is maintained by the data reader, it takes up fewer resources and performs more efficiently with small number of data operations. The dataset, on the other hand is more efficient when large number of updates are to be made to the database. All the updates are done in the local memory and are updated to the database in a batch. Since database connection remains open for the short time, the database management system does not get flooded with the incoming requests. 7.On order to get assembly info which namespace we should import? A.System.Reflection Namespace 8.Explain ExecuteNonQuery? A. Summary: Executes a Transact-SQL statement against the connection and nreturns the number of rows affected. Returns: The number of rows affected. 9.Compare and explain about ADO.NET and Visual Stuidio.NET? A.If you are planning to create specialized sub classes of the data set then it is better to use visual studio as it has features supporting database schema. Access can be obtained to each field through strongly types properties. Intellisense is much helpful. 10.What are the standard dot net framework data providers that are shipped with the Dot Net Framework 1.1? A.The Dot Net Framework 1.1 is shipped with four different data providers: * Dot Net Framework data provider for Microsoft SQL Server DBMS * Dot Net Framework data provider for Oracle DBMS (available only in Framework 1.1) * Dot Net Framework data provider for OLEDB supporting DBMS * Dot Net Framework data provider for ODBC supporting data sources (available only in Framework 1.1) 11.Why edit is not possible in repeater? A.Repeater Control just reads the information from the database,so edit is not possible... 12.What is the execution procedure in oracle ? A.Type sql> execute Procedure_name(arg1,arg2,...,arg n); 13.What is connection String? A.connection String - a string which contains address of the database we want to connect to. 14.How can you find a problem in the data stack? A.Problem can arise from these following instances they are: - 1) Mismatch of schema between the client and database. 2) Network library problems or non existence of data. 3) Wrong SQL generated. 4) Wrong programming logic. 15.What are the different row versions available in table? A.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. 16.Why ca not we use Multiple inheritance and garbage collector paralelly in .net? A..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. 17.What does it mean by Dot Net Framework Data Provider? A.Dot Net Framework Data Provider is a set of classes that establishes the database communication between an application and the database management system based on the standards of ADO.Net framework. Different data providers provide specialized and optimized connectivity to particular database management system or to a particular class of DBMS. For example, the MS SQL Server data provider provides the optimized connectivity between dot net application and MS SQL Server DBMS while the OLEDB data provider provides the uniform connectivity between dot net application and the OLEDB databases. 18.What is the Dot Net Framework data provider for OLEDB? A.The dot net framework data provider for OLEDB provides connectivity with the OLEDB supported database management systems. It is the recommended middle tier for the SQL Server 6.5 or earlier and Microsoft Access Database. It is a general data provider. You can also use it to connect with the SQL Server or Oracle Database Management Systems. The classes for this provider are present in the System.Data.OleDBClient namespace. 19.Which one of the following objects is a high-level abstraction of the Connection and Command objects in ADO.NET? A.DataReader DataSet DataTable DataView DataAdapter Answer: DataAdapter 20.If we are not returning any records from the database, which method is to be used? A.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. 21.What are advantages and disadvantages of Microsoft- provided data provider classes in ADO.NET? A.SQLServer.NET data provider is high-speed and robust, but requires SQL Server license purchased from Microsoft. OLE- DB.NET is universal for accessing other sources, like Oracle, DB2, Microsoft Access and Informix, but it is a .NET layer on top of OLE layer, so not the fastest thing in the world. ODBC.NET is a deprecated layer provided for backward compatibility to ODBC engines. 22.Explain the three services model (three-tier application) A.Presentation (UI), business (logic and underlying code) and data (from storage or other sources). 23.How to find the given query is optimised one or not? A.Using the power of the SQL Profiler and running a simple trace to capture the performance of your stored procedures can easily obtain these query lists. that needs tuning. 24.How to find the given query is optimised one or not? A.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 25.What is data access layer? A.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. 26.How do I find the connection string for the database server? A.Usually the connection string options are provided in the documentation, you can also find the connection strings on the internet. A good website for the connections strings is http://www.connectionstrings.com In ASP.Net websites or web applications, connection strings are generally stored in web.config file within "<connectionstrings>" tag And in windows based applications connection strings are stored in app.config file within "<connectionstrings>" tag. 27.What are the two fundamental objects in ADO.NET? A.Datareader and Dataset are the two fundamental objects in ADO.NET 28.What is a data adapter? A.A data adapter is the component that exists between the local repository (dataset) and the physical database. It contains the four different commands (SELECT, INSERT, UPDATE and DELETE). It uses these commands to fetch the data from the DB and fill into the dataset and to perform updates done in the dataset to the physical database. It is the data adapter that is responsible for opening and closing the database connection and communicates with the dataset. 29.What is a data reader? A.The data reader is a component that reads the data from the database management system and provides it to the application. The data reader works in the connected manner; it reads a record from the DB, pass it to the application, then reads another and so on. 30.What are the two fundamental objects in ADO.NET? A.Datareader and Dataset are the two fundamental objects in ADO.NET 31. ADO.NET vs ADO? A. * ADO.NET is considered as evolution version of ADO. * ADO.NET works with both connected as well as disconnected fashion whereas ADO works with connected architecture. * ADO.NET provides disconnected access through Data Set while ADO provides disconnected access using record set. * ADO.NET use XML to transfer data between objects while ADO use binary format to transfer data. * ADO.NET is shipped with .Net Framework and implemented using .Net methodology whereas ADO relies on COM. 32.What is different between SqlCommand object and Command Behavior Object? A.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. 33.What is the role of the DataReader class in ADO.NET connections? A.It returns a read-only dataset from the data source when the command is executed. data reader is used for retrieving data from datasource when connection is open & command is executed. 34.Name some of the top level objects which ADO consists? A.Some of the several objects which ADO consists are as follows they are * Connection object is responsible for creating a connection to the database. * Record object represents data which is not from the database. * Parameter object represents a sql parameter * Stream object is responsible to represent data from a text page or web page. 35.Back to ADO.NET page Question : What is the difference between data reader and data set? A.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. 36.How do I create a command and supply the SQL query to ADO.Net? (Command object and command string)? A.First of all, you create a command object (SqlCommand, OracleCommand, OleDbCommand, OdbcCommand) using the connection object (SqlConnection, OracleConnection, OleDbConnection, OdbcConnection) and set its CommandText property to the SQL query to execute. C# Version OdbcCommand cmd = conn.CreateCommand(); cmd.CommandText = "select * from authors"; VB.Net Version Dim cmd As OdbcCommand cmd = conn.CreateCommand() cmd.CommandText = "select * from authors" 37.Define connected and disconnected data access in ADO.NET? A.You have connected data access through the DataReader objects of data provider. This object requires exclusive use of the connection object. It can provide fast and forward-only data access. It doesn't allow editing. Disconnected data access is achieved through the Data Adapter object. This object establishes connection, executes the command, load data in the Data Set. The data set works independent of database. It contains data in the memory and can edit the data. The changes in the data can be transmitted to the database using Update method of Data Adapter object. 38.What is a database connection? A.A database connection represents a communication channel between you application and database management system (DBMS). The application uses this connection to pass the commands and queries to the database and obtain the results of the operations from the database. 39.Why should one use a specialized data provider when the data can be accessed with general data providers? A.The specialized data providers (e.g., SQL Server and Oracle) are built specially for a particular kind of DBMS and works much more efficiently than the general data providers (e.g., OLEDB and ODBC). In practice, the specialized data providers are many times efficient than the general data providers. 40.What is a database command? A.A database command specifies which particular action you want to perform to the database. The commands are in the form of SQL (Structured Query Language). There are four basic SQL statements that can be passed to the database.