Script-based Database Interaction for Your ASP.NET Website | WebReference

Script-based Database Interaction for Your ASP.NET Website

By Leidago Noabeb


[next]

In this article we will explore how ASP.NET interacts with databases by managing data in various scripts. We will manage the data by adding, modifying and deleting records.

First, we must create our database. Simply copy and paste the SQL below and execute it.

Overview: ADO vs. ADO.NET

So what is ADO? ActiveX Data Objects (ADO) is basically a data access API used by Microsoft in the days of homogeneous operating environments. Most of today's Internet applications are deployed in heterogeneous environments that consist of loosely coupled platforms. These loosely coupled platforms brought new challenges, particularly with regard to sharing common services and system scalability. Microsoft responded to these challenges by developing ADO.NET. The core element of the ADO model, the RecordSet object, was the universal data access object for COM-oriented environments. The key words here being COM-oriented environments as opposed to heterogeneous environments, which is what we operate in today.

Since the primary transmission protocol is HTTP, ADO, which was largely dependent on COM and more specifically on Advanced Data Table Gram (ADTG) as the native transmission format, had to be changed to adapt to the new reality. ADO.NET provides the needed evolution by natively supporting XML, a text-based format, as the transmission format, thus doing away with the otherwise required conversion of this binary format (ADTG) to XML in order to support robust transmission across HTTP. This native support of XML enables ADO.NET to communicate with almost all platforms and conform to open transmission standards. This is mainly because XML forms the basis for all heterogeneous environments because it can be interpreted by pretty much any programming language or application.

With ADO.NET you use the disconnected data set model to manipulate data without the need to stay connected to the RDBMS. Unlike classic ADO where you programmatically had to handle opening and closing database connections, ADO.NET does it automatically.

As mentioned earlier, ADO.NET natively supports XML. This support gives developers the ability to create what is referred to briefcase applications. Briefcase applications enable you, for example, to save a dataset in XML format and then work on it at home. You can then bring it back to work and then update your RDBMS. These are just a few of the benefits offered by ADO.NET.

Using ADO.NET

ADO.NET provides several methods for accessing a database. The first thing you must do is create a connection string. A connection string contains text that includes database access information such as the database name, username and password. You must explicitly open your connection using one of its constructors. This has two advantages for developers:

  • Easy utilization of the connection
  • Code maintenance

To use SQL Server-specific objects, you need to add the System.Data.SqlClient. To declare a namespace, you simply add the following line at the top of your .aspx page:

To use any other database server, you need to add the System.Data.OleDb namespace. This will give you access to the OLEDB managed provider objects, such as OleDbConnection, OleDbCommand, etc. In the example below, we are connecting to a database called CDdb that is contained in SQL Server 2008.

So what did we do here? We defined a connection string, and then we instantiated the connection object in the following line:

The above line sets the connection object. We then need to open the connection. In my code I use the try.. catch construct to catch any errors that may occur in the connection attempt, but that should not confuse you. It is simply a matter of best practice. We could just as easily have written the code like this:

[next]