ado.net questions and answers for interview

ado.net questions and answers for interview

1.What are the major components of a Data Provider, and what function does each fulfill?

An ADO.NET Data Provider is a suite of components designed to facilitate data access. Every Data Provider minimally includes a Connection object that provides the actual connection to the data source, a Command object that represents a direct command to the data source, a DataReader object that provides connected, forward-only, read-only access to a database, and a DataAdapter that facilitates disconnected data access.

 

2.Briefly contrast connected and disconnected data access in ADO.NET.

In ADO.NET, connected data access is available through the DataReader, which is a lightweight class designed to provide very fast and efficient data access. It is severely limited, however, in that it can only provide forward-only data access, it does not allow editing, and it requires the exclusive use of a Connection object. In contrast, disconnected data access is facilitated by a DataAdapter, which manages the commands required for selecting and updating data. The DataAdapter executes a SELECT command against a database, opening a data connection just long enough to retrieve the data, and loads the data into a DataSet, which is an in-memory copy of the data. When the data is ready to be updated, the Data Provider manages the updates in the same way, generating the appropriate commands to update the database and keeping the connection open just long enough to execute those commands.
 

3.What are the three possible settings for the CommandType property of a SqlCommand object or an OleDbCommand object, and what does each mean?

A Command object can have a CommandType property setting of Text, StoredProcedure, or TableDirect. When set to Text, the command executes the SQL string that is stored in the Command object’s CommandText property. When set to StoredProcedure, the command accesses a procedure stored on the database and returns the results. A CommandText setting of TableDirect indicates that the command should return the entire contents of the table indicated by the CommandText property.

 

4.How could you execute DDL commands, such as ALTER or CREATE TABLE, against a database with ADO.NET?

You must use a Command object to execute DDL commands. You can set the CommandType property to Text and enter the appropriate DDL command in the CommandText property. Then call Command.ExecuteNonQuery to execute the command.
 

5.Briefly discuss the advantages and disadvantages of using typed DataSet objects.

Typed DataSet objects allow you to work with data that is represented as members of the .NET common type system. This allows your applications to be aware of the types of data returned in a DataSet and serves to eliminate errors resulting from invalid casts, as any type mismatches are caught at compile time. Untyped DataSet objects, however, are useful if you do not know the structure of your data, and can be used with any data source.
 

6.How can you manage data currency on a form with several bound controls?

Every data source on a form has an associated CurrencyManager object that keeps that of the “current” record with respect to bound controls. For convenience, all of the CurrencyManager objects represented on a form are exposed through the form’s BindingContext property. The Position of the CurrencyManager can be changed, allowing navigation through the records.
 

7.Describe how to use a DataView to filter or sort data.

You can apply sort criteria to a DataView by setting the Sort property to the name of a column or columns to be sorted by. The data represented in a DataView object can be filtered by setting the RowFilter property to a valid filter expression.
 

8.Briefly describe an XmlDataDocument and how it relates to a DataSet.

An XmlDataDocument is an in-memory representation of data in a hierarchical XML format. Each XmlDataDocument is synchronized with a DataSet. Whenever changes are made to one object, the other is instantly updated. Thus, you can use the XmlDataDocument to perform XML manipulations on a DataSet.
 

9.What are the four major parts of a SQL SELECT statement? Briefly describe each one.

The four major parts of a SELECT statement are SELECT, FROM, WHERE, and ORDER BY. SELECT specifies the fields to be retrieved. FROM specifies the table from which the records are to be retrieved. WHERE allows you to specify filter criteria for the records to be retrieved, and ORDER BY allows you to specify a sort order for the records.
 

10.In Visual Basic .NET or Visual C# programming, when would you use Structured Query Language (SQL)? How are they executed?

ADO.NET handles most of the database communication for you behind-the-scenes. You would only use SQL statements when generating ad-hoc queries for the database. You execute SQL statements by using a DataCommand object. Statements the return records, such as SELECT statements, are executed using the ExecuteQuery method. You can also return a single value with a SELECT statement by using the ExecuteScalar method. To execute non-value returning statements, such as DELETE, INSERT INTO, or UPDATE statements, use the ExecuteNonQuery method.
 

11.What is meant by a SQL injection attack? How can you prevent them from occurring in your application?

SQL injection attacks occur when a malicious user attempts to execute SQL code by passing a SQL string to the application through user input. You can guard against SQL injection attacks by validating the format of all strings derived from user input that are used to form ad hoc SQL statements.
 

12.How can you read XML data into a dataset? How would you write data in a dataset to an XML file? How would you retrieve a string representation of the XML contained within a dataset? Describe each in general terms.

To read data from an XML file into a dataset, you can use the ReadXML method of the dataset, specifying the stream or file that contains the XML data. To write data to a file, you can use the WriteXML method of the dataset, again specifying either the file or the stream that represents the file. The GetXML method of the dataset can be used to retrieve a string representation of the XML data contained by a dataset.
 

13.Describe ADO.NET Data Architecture?

Data access in ADO.NET relies on two entities: the DataSet, which stores data on the local machine, and the Data Provider, a set of components that mediates interaction between the program and the database.
 

14.What is Dataset?

The DataSet is a disconnected, in-memory representation of data. It can be thought of as a local copy of the relevant portions of a database. Data can be loaded into a DataSet from any valid data source, such as a SQL Server database, a Microsoft Access database, or an XML file. The DataSet persists in memory, and the data therein can be manipulated and updated independent of the database.
 

15.What is ADO.NET?

ADO.NET The data access architecture for the Microsoft .NET Framework. ADO.NET is built around a disconnected data access model that uses a set of classes called a Data Provider to retrieve data from a data source.
 

16.What is Transaction?

transaction A group of commands (treated as a single unit) that change the data stored in a database. The transaction ensures that the commands are handled in an all-or-nothing fashion—if one of the commands fails, all of the commands fail, and any data that was written to the database by the commands is backed out. In this way, transactions maintain the integrity of data in a database.
 

17.What is XML Schema?

XML Schema A description of the data elements contained in an XML file. The XML Schema provides the names of the elements, their types, whether or not they are key fields, and other information.