Frequently Asked Interview Question-Answer of ADO.NET

What is a DataSet?          The Dataset is the central object in ADO.NET. DataSet is a logical container of data. It represents a set of data tables referenced as one unit in the application. With this object you can get all the data you need from each table quickly, examine and change it while you are disconnected from the server and then update the server with changes in an efficient operation.

How to fill DataSet with data?          To fill DataSet with data we have to use Fill() method of DataAdapter object. Fill() has several overloads. But the simple one is Fill(DataSet, DataTable)

Explain about ADO.NET and its features?          ADO.NET features are included in the Microsoft.NET framework. It has software components which help in data access and services.This can be used to make changes to RDBMS and non relational data base systems. This technology is considered to be the forefront of Active X data objects.

Explain about Active X data objects?          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.

What are the different languages which implement ADO?          There are several languages which implement ADO technology out of which these are some of the basic they are VB script in ASP, Visual Basic, Delphi, C++, and Microsoft environments. ADO.NET is a new addition to ADO..

State some of the basic steps required to access and manipulate data?          Some of the basic steps which should be taken to access and manipulate data are as follows. * Creating connection object which helps to connect to a database. * Record set object should be created which facilitates connection to receive data. * Opening of the connection * Passing the desired name for your table and performing necessary functions on the fetched data. * Changes can be done by using the commands update and update batch.

Name some of the top level objects which ADO consists?          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.

Explain about RDO?          A remote data object is also called as RDO in short. This represents data access application programming interface used in operating systems of windows. This was primarily designed for visual basic applications. This feature allows data base developers to interact directly with Open data base connectivity.

Explain about Data access objects or DAO?          DAO is used for database access on windows platform. It creates a work space object in which applications or operations are performed. There are two types of database engines they are Jet database engine and ODBC direct database engine. Explain about the command Data reader?          This feature should be used when you have a long list of results and you need to process one at a time. It provides only two features which is read and forward only. Records should be accessed sequentially. Previously accessed records cannot be accessed again. Explain about LINQ?          Language integrated query or LINQ provides developers to query data. It uses strongly typed queries and results. Developer productivity increases by the usage of compile time error checking and intellisense. This feature is common on number of data types. Explain about Data services?          ADO.NET has number of combinations of patterns and resources of libraries. This creates a feature which integrates data services with web. It contains formats which represent data such as JSON and plain XML. This form of interaction enables developers to interact with normal http commands.. Explain LINQ to Entities?          This feature allows LINQ query capabilities and it specifically forms a part of the .NET framework. This feature allows developers to program in ADO.NET and ORM. This feature also allows access to third party database. This provides new features to entity data. list all the classes those are used for database connections between sql server and asp.net.          1.SqlClientPermission - Enables the .NET Framework Data Provider for SQL Server to help ensure that a user has a security level adequate to access a data source. 2.SqlClientPermissionAttribute - Associates a security action with a custom security attribute. 3. SqlCommand - Represents a Transact-SQL statement or stored procedure to execute against a SQL Server database. This class cannot be inherited. 4.SqlCommandBuilder - Automatically generates single-table commands used to reconcile changes made to a DataSet with the associated SQL Server database. This class cannot be inherited. 5. SqlConnection - Represents an open connection to a SQL Server database. This class cannot be inherited. 6.SqlDataAdapter - Represents a set of data commands and a database connection that are used to fill the DataSet and update a SQL Server database. This class cannot be inherited. 7.SqlDataReader - Provides a means of reading a forward-only stream of rows from a SQL Server database. This class cannot be inherited. 8.SqlError - Collects information relevant to a warning or error returned by SQL Server. This class cannot be inherited. 9.SqlErrorCollection - Collects all errors generated by the .NET Framework Data Provider for SQL Server. This class cannot be inherited. 10.SqlException - The exception that is thrown when SQL Server returns a warning or error. This class cannot be inherited. 11.SqlInfoMessageEventArgs - Provides data for the InfoMessage event. This class cannot be inherited. 12.SqlParameter - Represents a parameter to a SqlCommand, and optionally, its mapping to DataSet columns. This class cannot be inherited. 13.SqlParameterCollection - Represents a collection of parameters relevant to a SqlCommand as well as their respective mappings to columns in a DataSet. This class cannot be inherited. 14.SqlRowUpdatedEventArgs - Provides data for the RowUpdated event. This class cannot be inherited. 15.SqlRowUpdatingEventArgs- Provides data for the RowUpdating event. This class cannot be inherited. 16. SqlTransaction - Represents a Transact-SQL transaction to be made in a SQL Server database. This class cannot be inherited.   What is main difference between ado and ado.net          ADO and ADO.NET are different in several ways: ? ADO works with connected data. This means that when you access data, such as viewing and updating data, it is real-time, with a connection being used all the time. This is barring, of course, you programming special routines to pull all your data into temporary tables. ADO.NET uses data in a disconnected fashion. When you access data, ADO.NET makes a copy of the data using XML. ADO.NET only holds the connection open long enough to either pull down the data or to make any requested updates. This makes ADO.NET efficient to use for Web applications. It is also decent for desktop applications. ? ADO has one main object that is used to reference data, called the Recordset object. This object basically gives you a single table view of your data, although you can join tables to create a new set of records. With ADO.NET, you have various objects that allow you to access data in various ways. The DataSet object will actually allow you to store the relational model of your database. This allows you to pull up customers and their orders, accessing/updating the data in each related table individually. ? ADO allows you to create client-side cursors only, whereas ADO.NET gives you the choice of either using client-side or server-side cursors. In ADO.NET, classes actually handle the work of cursors. This allows the developer to decide which is best. For Internet development, this is crucial in creating efficient applications. ? Whereas ADO allows you to persist records in XML format, ADO.NET allows you to manipulate your data using XML as the primary means. This is nice when you are working with other business applications and also helps when you are working with firewalls because data is passed as HTML and XML. Can we do database operations without using any of the ADO.net objects?          No its not at all possible. Can we do database operations without using any of the ADO.net objects?           can we connect two dataadapters to same data source using single connection at same time? 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. 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 How can we load multiple tables in to Dataset?          DataSet ds=new DataSet(); SqlDataAdapter dap=new SqlDataAdapter(Select * from <tablename>,<connection1>); dap.Fill(ds,"TableOne"); SqlDataAdapter dap1=new SqlDataAdapter(Select * from <tablename>,<connection1>); dap1.Fill(ds,"tableTwo"); What is connection String?          connection String - a string which contains address of the database we want to connect to. 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); } 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 is 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. Explain about command builder class feature?          Implementation of INSERT, DELETE and UPDATE commands is automatic and this class provides the necessary automation. You cannot extend or change the command builder while keeping the base algorithm intact to create action statements. Explain about Db enumerator?          This base class is added to ADO.NET 2.0. This class facilitates the process of obtaining a list of data sources. SQL server returns instances that are available on the network. Programmers do have choice of tools in their hands with this feature. How can you find a problem in the data stack?          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. Compare and explain about ADO.NET and Visual Stuidio.NET?          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. Explain about the relationship of XML and ADO.NET?          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. What are the Advantages of ADO.Net?          Ado.net uses dataset for accessing the data , since the dataset is created in client side there is no need of connection at the time of working on dataset ,to the server(where the database is present), so this makes the data accessing faster mainly in the net environment.

Explain about data provider?          ADO.NET has two primary parts in its architecture out of which one is data provider. It has its own set of objects and it consists of its own classes. Some of them are connection, parameter, data reader, command and data adapter. This provides access to data sources such as Oracle, SQL server, etc