Using the Database Object
Whenever you work with the Data Access Block, you'll have to deal with the Database class first. The Database class represents the database itself, and provides methods (see Table 1) that you can use to perform CRUD (Create, Read, Update and Delete) operations against the database.
Table 1: The table lists Database class methods that you use to perform CRUD operations against the database.
||Executes a SQL query or stored procedure and returns the results in the form of a DataSet object.
||Executes a SQL query or stored procedure and returns the number of records affected.
||Executes a SQL query or stored procedure and returns the results in the form of an IDataReader object.
||Executes a SQL query or stored procedure and returns the first column of the first row in the result set.
||Executes a command and adds a new DataTable to the existing DataSet.
||Synchronizes the DataSet contents with the database by executing appropriate INSERT, UPDATE, and DELETE statements.
Now that you have a basic overview of the methods of the Database class, the next few sections provide examples that demonstrate how you use the class.
Execute a Query Returning a DbDataReader
I'll start with a simple example. You'll execute a simple SQL statement against the AdventureWorks database and retrieve the results in the form of a DbDataReader object, which you'll then bind to a GridView control in an ASP.NET page. Listing 1
contains the full page code. Here's the Page_Load
void Page_Load(object sender, EventArgs e)
Database db = DatabaseFactory.CreateDatabase();
string sqlCommand = "Select " + "EmployeeID,
NationalIDNumber," + "LoginID, Title from " +
DbCommand dbCommand = db.GetSqlStringCommand
using (IDataReader reader = db.ExecuteReader(
gridEmployees.DataSource = reader;
|Figure 1. Binding a DbDataReader: The page shows the result of binding the DbDataReader object returned by the ExecuteReader() method to a GridView control.|
The preceding code first invokes the CreateDatabase()
method of the DatabaseFactory class to obtain an instance of the Database object. As the name suggests, the DatabaseFactory is a class containing factory methods that create Database objects. The CreateDatabase()
method is overloaded and returns a default database when you invoke the parameter-less version of the method as shown above. To set the default database, assign the defaultDatabase
attribute value to the appropriate database configuration key in the Web.config
file as shown below.
After obtaining an instance of the Database object, you then invoke the GetSqlStringCommand()
method to construct an instance of the ADO.NET 2.0 DbCommand object. After that, you execute the SQL through the Database.ExecuteReader()
method passing in the DbCommand as an argument. Navigate to the page using a browser. Because the method binds the returned DbDataReader to a GridView control, you'll see output similar to Figure 1