ADO.NET Frequently Asked Question

MOST FREQUENTLY ASKED QUESTION ON ADO.NET

1. What steps would you follow and what objects would you use to quickly find the number of records in a database table?

There are two ways to accomplish this task:
(a) Use a database connection and a command object to execute a SQL command that returns the number of rows in the table.

(b) Use a database connection and data adapter object to create a data set for the table, and then get the number rows in the data set.

2. How do typed data sets differ from untyped data sets, and what are the advantages of typed data sets?

Typed data sets use explicit names and data types for their members, whereas untyped data sets use collections to refer to their members. The following examples show a typed reference vs. an untyped reference to a data item:' Typed reference to the Contacts table's HomePhone column.DataSet1.Contacts.HomePhoneColumn.Caption = "@Home"' Untyped reference to the Contacts table's HomePhone column.DataSet1.Tables ("Contacts"). Columns ("HomePhone").Caption = "@Home"
Typed data sets do error checking at design time. This error checking helps catch types and type mismatch errors, which would be detected only at run time with untyped data sets.

3. How do you call a stored procedure?

Create a command object, set the object’s Command Text property to the name of the stored procedure, and set the CommandType property to StoredProcedure. To execute the stored procedure, use the command object’s ExecuteNonQuery, ExcecuteScalar, ExecuteReader, or ExecutelXmlReader method. For example, the following code calls the Ten Most Expensive Products stored procedure on the Northwind Traders database:‘Create a command object to execute.Dim cmdTopTen As New SqlCommand(connNWind)‘Set command text.cmdTopTen.CommandText = "Ten Most Expensive Products"‘Set the command properties.cmdTopTen.CommandType = CommandType.StoredProcedure‘Create a data reader object to get the results.Dim drdTopTen As SqlDataReader‘Open the connection.connNWind.Open()‘Excecute the stored procedure.drdTopTen = cmdTopTen.ExecuteReader()

4. Explain the difference between handling transactions at the data set level and at the database level.

Data sets provide implicit transactions, because changes to the data set aren’t made permanent in the database until you call the Update method. To handle transactions in a data set, process the Update method and check for errors. If errors occur during an update, none of the changes from the data set is made in the database. You can try to correct the error and resubmit the update, or you can roll back the changes to the data set using the RejectChanges method.
Databases provide explicit transactions through the Transaction object. You create a Transaction object from a database connection and then assign that Transaction object to the commands you want to include in the transaction through the command object’s Transaction property. As you perform the commands on the database, you check for errors. If errors occur, you can either try to correct them and resubmit the command, or you can restore the state of the database using the Transaction object’s RollBack method. If no errors occur, you can make the changes permanent by calling the transaction object’s Commit method.

5. How do you declare an unmanaged procedure within .NET?

Use the DllImport attribute or a Visual Basic .NET Declare statement to declare an unmanaged procedure for use with a .NET assembly. The DllImport attribute is found in the System.Runtime.InteropServices namespace.

6. What is DataRelation?

DataRelation is a class representing a parent/child relationship between two DataTable objects. Before creating a relationship, DataRelation verifies whether or not a relationship can be established. It creates a relationship between the matching columns in the parent and child tables.

7. What is DataReader?

The DataReader object provides a forward-only, read-only, connected stream recordset from a database. Unlike other components of the Data Provider, DataReader objects cannot be directly instantiated. Using the DataReader can increase application performance and reduce system overhead because only one row at a time is ever in memory.

8. What is DataAdaptor?

The DataAdapter serves as a bridge between a DataSet and data source for retrieving and saving data. The DataAdapter provides this bridge by using Fill to load data from the data source into the DataSet and using Update to send changes made in the DataSet back to the data source.

9. What is DataSet?

DataSet is a disconnected, in-memory representation of data. It can be considered as a local copy of the relevant portions of the database. The DataSet is persisted in memory and the data in it can be manipulated and updated independent of the database. It represents related tables, constraints, and relationships among the tables. DataSet reads and writes data and schema as XML documents.

10. What is DataView?

DataView is a class that represents a databindable and customized view of DataTable. It allows data binding on Windows Forms and Web Forms. It is used to manage sorting, filtering, editing, searching and navigation on DataTable records.

11. What is Data Provider?

The Data Provider is responsible for providing and maintaining the connection to the database. A data provider is a set of related components that work together to provide data in an efficient and performance driven manner.

12. What is SqlConnection?

SqlConnection is a class representing an open connection to a Microsoft SQL Server. It cannot be inherited. It is used to represent a unique session to a Microsoft SQL Server 7.x database or later.

13. What is OleDbConnection?

OleDbConnection is a class representing an open connection to a data source. It cannot be inherited. It is used to represent a unique to an OLE DB data source or to a Microsoft Server 6.x database or earlier.

14. What is Web.config?

Web.config is an XML file that consists of a series of settings specific to an application. Whenever a new Web service project is created in Visual Studio .Net, the Web.config file is automatically created and added to the project. It contains a number of empty settings with text explaining each section and its use.

15. What is Machine.config?

Machine.config is a configuration file that includes settings specific to a computer, such as built-in remoting channels, machine-wide assembly binding, and ASP.NET configuration settings. The configuration system first searches for APIs and ASP.NET settings in Machine.config.
It then looks in the application configuration file.

16. Explain Validators?

The CustomValidator control is used to provide a user-defined validation function for an input control. It determines whether or not the value of the input control passes customized validation logic.

The CustomValidator can be used to create client-side as well as server-side validation functions. The ControlToValidate property of the CustomValidator control specifies the input control to be validated.

The RequiredFieldValidator control is used to ensure that users do not skip an entry.

The CompareValidator control is used to compare a user's entry against a constant value, against a property value of another control, or against a database value using a comparison operator. It cannot be used to implement customized validation logic.

The RegularExpressionValidator control is used to check that an entry matches a pattern defined by a regular expression. It cannot be used to implement customized validation logic

17. What is XML schema?

XML schema is a formalized model of objects and their relationships. It allows users to define rules for governing relations between elements and attributes. These rules are defined using standard XML and can be managed using XML applications. XML schema also supports namespaces, data types, range constraints etc.

18. What is OleDbDataAdapter?

OleDbDataAdapter is a class used to represent a database connection and a set of data commands that are used to fill the DataSet and update the data source. OleDbDataAdapter is used as a bridge between a dataset and a data source to save and retrieve data.

19. What is GAC?

Global assembly cache (GAC) is a machine-wide cache. It stores assemblies that are designed to be shared among multiple application on a computer. All assemblies stored in a global assembly cache must have strong names.

20. What permissions do Web applications run under by default?

By default, Web applications run as the ASPNET user, which has limited permissions equivalent to the Users group.

21. Why is the Machine.config file important to deployed Web applications?

The Machine.config file controls many aspects of how Web applications run, including how processes are recycled, what types of request queue limits are imposed, and what interval is used when checking if users are still connected.

22. How do you configure a setup project to install an application over the Web?

To configure a setup project to install an application over the Web, select the Web Bootstrapper option from the setup project’s properties. This setting allows the Windows Installer to be downloaded and installed over the Web.