Database Programming

Chapter 6 Database Programming

Part A

Objectives:

1. Introduce ADO.Net, the data access technology built into the .NET framework.

2. Learn the classes that you need to use most often in your ASP.Net pages.

3. Learn how to retrieve records from a database table, insert new records, update records, and delete records.

6.1 Accessing Data From ASP.Net

* Classic ASP pages used ActiveX Data Objects (ADO) to access and modify database.

* ADO is a programming interface used to access data.

* Advantage: Efficient and fairly easy for developers to learn and implement.

* However, ADO suffered from a dated model for data access with many limitations, such as the inability to transmit data so it is easily and universally accessible.

* Coupled with the move from standard SQL databases (relational database) to more distributed types of databases (such as Ms SQL Server), Microsoft introduced ADO.NET - the next evolution of ADO.

* ADO.Net is a major revision of ADO that enables Asp.Net pages to present data in much more efficient and different ways.

* ASP.Net pages use ADO.Net to communicate with any type of data provider.

* Figure 1 depicts the model of data access with ADO.net & ASP.Net.

Figure 1

6.2 Data Access Classes

* The .Net framework contains several namespaces with dozens of classes devoted to database access.

* Microsoft has created separate namespaces that are optimized for working with different data providers (different types of databases).

* The following data provider specific namespaces are included with ADO.Net:

 System.Data.SqlClient:

Contains classes for connecting to Microsoft SQL Server version 7.0 or higher.

 System.Data.OleDb (our focus):

Contains classes for connecting to a data source that has an OLE DB provider (such as Ms Access).

 System.Data.Odbc:

Contains classes for connecting to a data source that has an ODBC driver.

 System.Data.OracleClient:

Contains classes for connecting to an Oracle database server.

* The System.Data.OleDb namespace include the following classes:

 OleDbConnection:

Represents an open database connection to a database. (Refer P.271 for more detail)

 OleDbCommand:

Represents a SQL statement or store procedure. (Refer p.271-272 for more detail)

 OleDbDataReader:

Represents the results from a database query. (Refer p. 278 - 281 for more detail)

6.3 Common Database Tasks

* In this section, you will learn to perform common database tasks using ADO.Net:

1. Create & open a database connection.

2. Retrieve & display database records.

3. Add new database records.

4. Update existing database records.

5. Delete database records.

* When working with Microsoft Access database system, you need to import the System.Data.OleDb namespace by adding the following page directive at the top of your ASP.Net page:

6.3.1 Create & Open A Database Connection

* To access a database, you need to use the OleDbConnection class to create a connection object/instance that will create and open a database connection.

* Prefix:con >> conXXX

* The basic step:

i. Create an instance of the OleDbConnection class.

ii. Initialize the instance with a connection string appropriate for Ms Access.

iii. Call the Open() method to establish the connection.

Example 1 : OleDbConnection.aspx

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

void Page_Load( object s, EventArgs e )

{

OleDbConnection conAuthors;

conAuthors = new OleDbConnection( "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA Source=c:\Authors.mdb" );

conAuthors.Open();

if (conAuthors.State == ConnectionState.Open)

Response.Write ("Succesfully open the database connection" + "");

conAuthors.Close();

...