ADO is used to query the data, and the underlying database may be MySQL, SQL Server, Access, Oracle, or any other major RDBMS. Unlike many other articles, complete examples are presented, which may be copied into a file and run directly. Techniques for optimizing the output of tabular data are presented first, followed by techniques for optimizing more complex tabular data typical of reports. The appendix contains instructions for configuring the tests, links to references, and books suggestions.

In this chapter, we'll be discussing how to get at data from your C# programs using ADO.NET. Over the course of this chapter, we'll be covering the following areas:

Connecting to the database - how to utilize the new SqlConnection and OleDbConnection classes to connect to and disconnect from the database. Connections utilize the same form of connection strings as did OLEDB providers (and therefore ADO), and these are briefly discussed. We then go through a set of best practices for utilizing database connections, and show how to ensure that a connection is closed after use, which is one of the sources of poor application performance.
Executing Commands - ADO.NET has the concept of a command object, which may execute SQL directly, or may issue a stored procedure with return values. The various options on command objects are discussed in depth, with examples to show how commands can be used for each of the options presented by the Sql and OleDB classes.
Stored Procedures - How to call stored procedures using command objects, and how the results of those stored procedures may be integrated back into the data cached on the client.
The ADO.NET object model - this is significantly different from the objects available with ADO, and the DataSet, DataTable, DataRow, and DataColumn classes are all discussed. A DataSet can also include relationships between tables, and also constraints. These issues are also discussed.
Using XML and XML Schemas - ADO.NET is built upon an XML framework, so we'll examine how some of the support for XML has been added to the data classes.
We'll also present a guide to the naming conventions that preside in the world of ADO.NET and explain some of the reasoning behind them. First, though, let's take a brief tour of ADO.NET and see what's on offer

First we must send an SQL statement to database engine. Count of records will be first value of resultset.

In the tutorials so far we have learned from connecting to the database via ASP to inerting, updating and selecting data from the database.

The area we waste a lot of database time is when we show records to the user and in the mean time keep our database connection open. Following example illustrates this point :

In Active Server Pages, all strings are enclosed in double quotes ("). For example, if you wanted to pull in all orders from the database, the SQL string would look like this: