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.
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.
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: