4GuysFromRolla.com - Data Manipulation with ADO.NET

Senior Web Content Specialist
Aquent
US-NJ-Parsippany

Published: Friday, September 07, 2001

ASP.NET: Tips, Tutorials, and Code
Chapter 6: Data Manipulation with ADO.NET

2. Using ADO.NET to Accomplish Common Database Tasks

In this section, you'll learn how to use ADO.NET classes to perform common database tasks, such as retrieving database data, inserting new data, updating data, and deleting data. We'll focus on the ADO.NET classes for performing these tasks in this chapter and worry about constructing a friendly user interface for performing these tasks in the following chapter.

Whenever you need to work with database data in an ASP.NET page, you should import the proper namespaces so you can easily access the ADO.NET classes in your page. There are three namespaces that you'll use:

System.Data
System.Data.SqlClient
System.Data.OleDb

If you are accessing a SQL Server database (version 7.0 or greater), you'll need to include these two Import statements:

<%@ Import Namespace="System.Data" %>
<%@ Import NameSpace="System.Data.SqlClient" %>

If you are accessing a database other than Microsoft SQL Server 7.0 or greater (for example, Microsoft Access, Oracle, Microsoft SQL Server 6.5 or just about any data source that has an OLE DB Provider), you would use the following two Import statements:

<%@ Import Namespace="System.Data" %>
<%@ Import NameSpace="System.Data.OleDb" %>

Note
You can use the classes from the System.Data.OleDb namespace with Microsoft SQL Server. You might want to do this if you want your ASP.NET page to be compatible with any database. For example, you might want your page to work with both Microsoft SQL Server and Oracle. However, you will lose all the speed advantages of the SQL-specific classes if you use the System.Data.OleDb namespace.

Opening a Database Connection

The first thing you'll need to do in order to access a database is create and open a database connection. Once again, you'll create the connection in different ways, depending on the type of database that you want to access.

Listing 6.2.1 shows how you would create and open a connection for a Microsoft SQL Server database (this page is included on the CD as SQLConnection.aspx).

Listing 6.2.1 Opening a Connection to SQL Server

 1: <%@ Import Namespace="System.Data" %>
 2: <%@ Import NameSpace="System.Data.SqlClient" %>
 3: <%
 4: Dim myConnection As SqlConnection
 5: myConnection = New SqlConnection( "server=localhost;database=Pubs;uid=sa" )
 6:
 7: myConnection.Open()
 8: %>
 9: Connection Opened!

The first two lines in Listing 6.2.1 import the necessary namespaces for working with SQL Server. Next, in lines 4 and 5, an instance of the SqlConnection class is created named myConnection. The myConnection class is initialized by passing a connection string as a parameter to the constructor for the SqlConnection class. Finally, the connection is actually opened by calling the Open() method of the SqlConnection class.

The connection string contains all the necessary location and authentication information to connect to SQL Server. In the code in Listing 6.2.1, the connection string contains the name of the server, the name of the database, and the SQL Server login and password.

Note

Notice that you do not specify a provider parameter for the connection string when using the SqlConnection class. The classes in the System.Data.SqlClient namespace do not use an OLEDB provider, ADO, ODBC, or any other intermediate interface to SQL Server. The classes work directly with the TDS (Tabular Data Stream) protocol.

Furthermore, you cannot use a data source name (DSN) when opening a connection with the SqlConnection class. If you really want to use a DSN with SQL Server, you must use the classes in the System.Data.OleDb namespace instead.

You would use similar code to create a connection to a Microsoft Access database. In Listing 6.2.2, a database connection is created and opened for a Microsoft Access database named Authors (this page is included on the CD as OleDbConnection.aspx).

Listing 6.2.2 Opening a Connection to Microsoft Access

 1: <%@ Import Namespace="System.Data" %>
 2: <%@ Import NameSpace="System.Data.OleDb" %>
 3: <%
 4: Dim myConnection As OleDbConnection
 5: myConnection = New OleDbConnection( "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA
 Source=c:\authors.mdb" )
 6:
 7: myConnection.Open()
 8: %>
 9: Connection Opened!

Because we are creating a connection for Microsoft Access, we must import the System.Data.OleDb namespace rather than the System.Data.SqlClient namespace. Next, we must create an instance of the OleDbConnection class and initialize it with a connection string appropriate for Microsoft Access. Finally, calling the Open() method of the OleDbConnection class actually opens the database connection.

In the code contained in Listing 6.2.2, we pass the name of the OLEDB provider for Microsoft Access (Microsoft.Jet.OLEDB.4.0) and the path to the Access database on the server. If you wanted to connect to another type of database, you would need to specify a different provider. For example, to connect to an Oracle database, you would use the MSDAORA provider. (This provider is automatically installed as part of the Microsoft Data Access Components.)

Note

If you prefer, you can use a data source name (DSN) with the OleDbConnection class to open a database connection. For example, after you create a System DSN named myDSN, you can connect using this:
myConnection = New OleDbConnection( "DSN=myDSN" )
Realize, however, that opening a connection in this way forces you to use the OLEDB for ODBC provider rather than the native OLEDB provider for your database. Typically, but not always, this will result in slower performance.

By default, when you call the Open() method with either the SqlConnection or the OleDbConnection classes, the connection is given 15 seconds to open before timing out. You can override this default behavior by setting the ConnectionTimeout property before you open the database connection. For example, to allow up to 90 seconds to open a connection, you would use a statement like the following:

MyConnection.ConnectionTimeout = 90

Inserting Data Using ADO.NET

Now that you know how to create and open a database connection, we can use this connection to add new data to a database table. We'll add the new data with the SQL INSERT command. Here's the syntax for a basic INSERT command:

INSERT tablename ( column1, column2... ) VALUES ( value1, value2... )

You insert new records into a table by listing the table columns and listing the values that you want to insert into the columns. For example, imagine that you have a table named Authors that has both a FirstName and a LastName column. The following statement inserts a new author named Ralph Ellison:

INSERT Authors ( FirstName, LastName ) VALUES ( 'Ralph', 'Ellison' )

Three steps are required for executing a SQL INSERT command in an ASP.NET page:

Create and open a database connection.
Create a database command that represents the SQL INSERT statement to execute.
Execute the command.

The ASP.NET page in Listing 6.2.3 uses the classes from the System.Data.SqlClient namespace to insert a new record into a SQL Server database table (this page is included on the CD as SQLINSERTcommand.aspx).

Listing 6.2.3 Executing an `INSERT` Command

 1: <%@ Import Namespace="System.Data" %>
 2: <%@ Import NameSpace="System.Data.SqlClient" %>
 3:
 4: <%
 5: Dim myConnection As SqlConnection
 6: Dim myCommand As SqlCommand
 7:
 8: myConnection = New SqlConnection( "server=localhost;uid=sa;
 pwd=secret;database=myDataPubs" )
 9: myConnection.Open()
10: myCommand = New SqlCommand( "Insert testTable ( col1 ) 
Values ( 'Hello' )", myConnection )
11: myCommand.ExecuteNonQuery()
12: myConnection.Close()
13: %>
14: New Record Inserted!
15:

The first two statements in Listing 6.2.3 are used to import the necessary namespaces. Next, in lines 8 and 9, a connection to a SQL database is created. An instance of the SQLConnection class is initialized with a connection string for the myData database located on the local server. Next, the SqlConnection is opened with the Open() method.

In the statement that follows, an instance of the SqlCommand class is created. The SqlCommand class is initialized with two parameters: the command to execute and the connection to use for executing the command.

In line 10, a string containing an INSERT statement is passed to the instance of the SqlCommand class. The INSERT statement inserts a new record into a table named testTable with a single column named col1.

Finally, the command is executed by calling the ExecuteNonQuery() method of the SqlCommand class. The ExecuteNonQuery() method sends the SQL command to the database server. The method is called the ExecuteNonQuery() method because it is used to execute non-row-returning SQL commands.

The code in Listing 6.2.3 works only with Microsoft SQL Server (version 7.0 or greater). To work with other databases, you would need to modify the code to use the classes from the System.Data.OleDb namespace rather than the System.Data.SqlClient namespace. The ASP.NET page in Listing 6.2.4 demonstrates how you would add a new record to a Microsoft Access database table (this page is included on the CD as OleDbINSERTcommand.aspx).

Listing 6.2.4 Adding a Record to Access

 1: <%@ Import Namespace="System.Data" %>
 2: <%@ Import NameSpace="System.Data.OleDb" %>
 3:
 4: <%
 5: Dim myConnection As OleDbConnection
 6: Dim myCommand As OleDbCommand
 7:
 8: myConnection = New OleDbConnection( "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA 
 Source=c:authors.mdb" )
 9: myConnection.Open()
10: myCommand = New OleDbCommand( "Insert INTO Authors ( Author ) Values
 ( 'Simpson' )", myConnection )
11: myCommand.ExecuteNonQuery()
12: myConnection.Close()
13: %>
14: New Record Inserted!
15:

The code in Listing 6.2.4 executes an INSERT statement that adds a new record to a Microsoft Access table named authors.mdb. Notice that Microsoft Access requires you to use the keyword INTO with the INSERT statement (you use INSERT INTO Authors rather than INSERT Authors).

Note

The apostrophe character (') can cause problems when you're inserting data into a database table. For example, imagine that you want to add a new author named O'Leary to the Authors table. You might try to execute the following statement:
INSERT INTO Authors ( Author ) Values ( 'O'Leary' )
This statement will generate an error because the apostrophe in O'Leary will be interpreted as marking the end of the SQL string. To get around this problem, you'll need to double up your apostrophes. For example, use the following statement to add O'Leary to a database table:
INSERT INTO Authors ( Author ) Values ( 'O''Leary' )

Updating Data Using ADO.NET

To update existing records in a database table, you use the SQL UPDATE command. Here's the syntax for the basic UPDATE command:

UPDATE tablename SET column1 = value1, column2 = value2...
WHERE search condition

You update a table by setting certain columns to certain values where a certain search condition is true. For example, imagine that you have a database table named Authors that has a column named LastName. The following statement sets the value of the LastName column to 'Smith' wherever the column has a value of 'Bennet':

UPDATE Authors SET LastName = 'Smith'
WHERE LastName = 'Bennet'

You execute an UPDATE command within an ASP.NET page by completing the following steps:

Create and open a database connection.
Create a database command that represents the SQL UPDATE statement to execute.
Execute the command.

For example, the ASP.NET page contained in Listing 6.2.5 updates a record in a SQL Server database (this page is included on the CD as SQLUPDATEcommand.aspx).

Listing 6.2.5 Updating a SQL Database

 1: <%@ Import Namespace="System.Data" %>
 2: <%@ Import NameSpace="System.Data.SqlClient" %>
 3:
 4: <%
 5: Dim myConnection As SqlConnection
 6: Dim myCommand As SqlCommand
 7:
 8: myConnection = New SqlConnection( "server=localhost;uid=sa;
 pwd=secret;database=myDataPubs" )
 9: myConnection.Open()
10: myCommand = New SqlCommand( "UPDATE Authors SET LastName='Smith' 
WHERE LastName='Bennett'", myConnection )
11: myCommand.ExecuteNonQuery()
12: myConnection.Close()
13: %>
14: Record Updated!
15:

The first two statements in Listing 6.2.5 import the necessary namespaces for working with the SQL ADO.NET classes. Next, in lines 8 and 9, a database connection is created and opened for the local server. In the statement that follows, an instance of the SqlCommand class is created by passing a SQL command and SqlConnection to the constructor for the class.

The SQL UPDATE command is executed when the ExecuteNonQuery() method of the SqlCommand class is called. At this point, the UPDATE statement is transmitted to SQL Server and executed.

The code in Listing 6.2.5 will only work with Microsoft SQL Server (version 7.0 and greater). If you want to update a record in another type of database, you will need to use the ADO.NET classes from the System.Data.OleDb namespace rather than the System.Data.SqlClient namespace.

The ASP.NET page contained in Listing 6.2.6 modifies a record in a Microsoft Access database (the page is included on the CD as OleDbUPDATEcommand.aspx).

Listing 6.2.6 Updating an Access Database

 1: <%@ Import Namespace="System.Data" %>
 2: <%@ Import NameSpace="System.Data.OleDb" %>
 3:
 4: <%
 5: Dim myConnection As OleDbConnection
 6: Dim myCommand As OleDbCommand
 7:
 8: myConnection = New OleDbConnection( "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA
 Source=c:\authors.mdb" )
 9: myConnection.Open()
10: myCommand = New OleDbCommand( "UPDATE Authors SET Author='Bennett' 
 WHERE Author = 'Simpson'", myConnection )
11: myCommand.ExecuteNonQuery()
12: myConnection.Close
13: %>
14: Record Updated!
15:

One difference between the SQL UPDATE command and the SQL INSERT command is that the SQL UPDATE command might affect more than one record at a time. When you execute an UPDATE command, the command changes every record that satisfies the UPDATE command's WHERE clause.

You can determine the number of records affected by an UPDATE command within an ASP.NET page by grabbing the value returned by the ExecuteNonQuery() method. The page contained in Listing 6.2.7 illustrates this method (this page is included on the CD as SQLUPDATERecordsAffected.aspx).

Listing 6.2.7 Records Affected by the `UPDATE` Command

 1: <%@ Import Namespace="System.Data" %>
 2: <%@ Import NameSpace="System.Data.SqlClient" %>
 3:
 4: <%
 5: Dim myConnection As SqlConnection
 6: Dim myCommand As SqlCommand
 7: Dim recordsAffected As Integer
 8:
 9: myConnection = New SqlConnection( "server=localhost;uid=sa;
 pwd=secret;database=myDataPubs" )
10: myConnection.Open()
11: myCommand = New SqlCommand( "UPDATE testTable SET col1='hello' 
WHERE col1='fred'", myConnection )
12: recordsAffected = myCommand.ExecuteNonQuery()
13: Response.Write( "The UPDATE statement modified " & 
recordsAffected.toString() & " records!" )
14: myConnection.Close
15: %>
16:

Deleting Data Using ADO.NET

You can delete data from a database by using the SQL DELETE command. The syntax for a basic DELETE command takes the following form:

DELETE tablename WHERE search condition

For example, if you want to delete all the rows from a table named Authors in which the LastName column has the value 'Bennet', use the following statement:

DELETE Authors WHERE LastName = 'Bennet'

To execute a DELETE command from within an ASP.NET page, you must complete the following steps:

Create and open a database connection.
Create a database command that represents the SQL DELETE statement to execute.
Execute the command.

For example, the ASP.NET page in Listing 6.2.8 demonstrates how you can delete a record from a SQL Server database table (this page is included on the CD as SQLDELETEcommand.aspx).

Listing 6.2.8 Deleting a Record from SQL Server

 1: <%@ Import Namespace="System.Data" %>
 2: <%@ Import NameSpace="System.Data.SqlClient" %>
 3:
 4: <%
 5: Dim myConnection As SqlConnection
 6: Dim myCommand As SqlCommand
 7:
 8: myConnection = New SqlConnection( "server=localhost;uid=sa;
 pwd=secret;database=myDataPubs" )
 9: myConnection.Open()
10: myCommand = New SqlCommand( "DELETE testTable WHERE col1='fred'", myConnection )
11: myCommand.ExecuteNonQuery()
12: myConnection.Close()
13: %>
14: Record Deleted!
15:

The first two lines in Listing 6.2.8 are used to import the necessary namespaces to work with SQL Server. Next, a connection to the SQL Server running on the local machine is opened.

In line 10, the SqlCommand class is initialized with two parameters: a SQL DELETE command and the SqlConnection class. Next, the command is executed, and the connection is closed.

If you need to work with a database other than Microsoft SQL Server, you would use similar code. However, you must use the classes from the System.Data.OleDb namespace rather than the classes from System.Data.SqlClient.

The page contained in Listing 6.2.9 illustrates how you would delete a record from a Microsoft Access database table named Authors (this page is included on the CD as OleDbDELETEcommand.aspx).

Listing 6.2.9 Deleting a Record from an Access Database

 1: <%@ Import Namespace="System.Data" %>
 2: <%@ Import NameSpace="System.Data.OleDb" %>
 3:
 4: <%
 5: Dim myConnection As OleDbConnection
 6: Dim myCommand As OleDbCommand
 7:
 8: myConnection = New OleDbConnection( "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA
 Source=c:\authors.mdb" )
 9: myConnection.Open()
10: myCommand = New OleDbCommand( "DELETE FROM Authors 
WHERE Author = 'Simpson'", myConnection )
11: myCommand.ExecuteNonQuery()
12: myConnection.Close()
13: %>
14: Record Deleted!
15:
16:

Notice that you must use DELETE FROM rather than just DELETE when working with a Microsoft Access database.

A SQL DELETE command is similar to a SQL UPDATE command in that it might affect an unknown number of records. A SQL DELETE command deletes all the records that match the condition specified by the command's WHERE clause.

If you need to determine the number of records affected by a DELETE command, you can grab the value returned by the ExecuteNonQuery() method. The page contained in Listing 6.2.10 illustrates how you would do this (this page is included on the CD as SQLDELETERecordsAffected.aspx).

Listing 6.2.10 Records Affected by `DELETE`

 1: <%@ Import Namespace="System.Data" %>
 2: <%@ Import NameSpace="System.Data.SqlClient" %>
 3:
 4: <%
 5: Dim myConnection As SQLConnection
 6: Dim myCommand As SQLCommand
 7: Dim recordsAffected As Integer
 8:
 9: myConnection = New SqlConnection( "server=localhost;uid=sa;
 pwd=secret;database=myDataPubs" )
10: myConnection.Open()
11: myCommand = New SqlCommand( "DELETE Authors2 
WHERE LastName='Smith'", myConnection )
12: recordsAffected = myCommand.ExecuteNonQuery()
13: Response.Write( "The DELETE statement modified " 
& recordsAffected.toString() & " records!" )
14: myConnection.Close
15: %>
16:
17:

Querying Data Using ADO.NET

The SQL command that you will use most often in your ASP.NET pages is the SELECT command. The SELECT command enables you to retrieve records from a database table that match a certain condition. Here's the syntax for a basic SELECT command:

SELECT column1, column2...
FROM tablename1, tablname2...
WHERE search condition

For example, if you wanted to retrieve the FirstName and LastName columns from the Authors table, where the LastName column has the value 'Smith', you would use the following SELECT command:

SELECT FirstName, LastName
FROM Authors
WHERE LastName = 'Smith'

If you simply want to retrieve all the columns and all the rows from the Authors table, use the following SELECT statement:

SELECT * FROM Authors

The asterisk (*) is a wildcard character that represents all the columns. Without a WHERE clause, all the rows from the Authors table are automatically returned.

Note
For performance reasons, you should avoid using the * wildcard character in the SELECT statement.

Four steps are involved in executing a SELECT command in an ASP.NET page:

Create and open a database connection.
Create a database command that represents the SQL SELECT statement to execute.
Execute the command returning a DataReader.
Loop through the DataReader, displaying the results of the query.

When you execute a query using ADO.NET, the results of the query are returned in a DataReader. More accurately, the results of a query are represented by either the SqlDataReader or the OleDbDataReader, depending on the database from which you are retrieving the records.

A DataReader represents a forward-only stream of database records. This means that the DataReader only represents a single record at a time. To fetch the next record in the stream, you must call the Read() method. To display all the records returned from a query, you must call the Read() method again and again until you read the end of the stream.

Note
If you have used earlier versions of ADO, it may be helpful to think of a DataReader as a Recordset opened with a forward-only cursor.

For example, the ASP.NET page in Listing 6.2.11 displays all the records from a SQL Server database table named Authors;. (This page is included on the CD as SQLDataReader.aspx.)

Listing 6.2.11 Using the SQLDataReader

 1: <%@ Import Namespace="System.Data" %>
 2: <%@ Import NameSpace="System.Data.SqlClient" %>
 3:
 4: <%
 5: Dim myConnection As SqlConnection
 6: Dim myCommand As SqlCommand
 7: Dim myDataReader As SqlDataReader
 8:
 9: myConnection = New SqlConnection( "server=localhost;uid=sa;
 pwd=secret;database=Pubs" )
10: myConnection.Open()
11: myCommand = New SqlCommand( "Select * from Authors", myConnection )
12: myDataReader = myCommand.ExecuteReader()
13: While myDataReader.Read()
14:  Response.Write( myDataReader.Item( "au_lname" ) )
15: End While
16: myDataReader.Close()
17: myConnection.Close()
18: %>
19:

The first two lines in Listing 6.2.11 are used to import the necessary namespaces to use the ADO.NET classes for SQL Server. Next, in lines 9 and 10, a connection is created and opened for the database located on the local server named Pubs.

Next, a SqlCommand object is initialized with a SQL string that contains a SQL SELECT command. The SQL SELECT command retrieves all the records from a database table named Authors.

Next, the command is executed by calling the ExecuteReader() method of the SqlCommand class. In this case, we want to call the ExecuteReader() method rather than the ExecuteNonQuery() method because we need to retrieve the results of a query. The ExecuteReader() method returns a SQLDataReader that represents the results of executing the SQL SELECT statement.

Once we have a SQLDataReader, we need to loop through its contents to display all the records returned by the query. In Listing 6.2.11, this is accomplished with a WHILE...END WHILE loop. All the records returned by the SELECT command are displayed with the following block of code:

 While myDataReader.Read
  Response.Write( myDataReader.Item( "au_lname" ) )
 End While

The Read() method of the SqlDataReader class does two things whenever it is called. First, the method returns the value TRUE if there is another record; otherwise, it returns FALSE. Second, the method advances the DataReader to the next record if a next record exists. By combining these functions, the Read() method makes it very easy to quickly loop through the contents of a DataReader.

Caution
Remember to call the Read() method at least once before displaying a record with the DataReader. When a DataReader is first returned, the first record is not retrieved until you call the Read() method.

The value of a returned column is displayed by using the Item property of the DataReader. For example, in the code in Listing 6.2.11, the value of the au_lname column is displayed.

Note
The Item property will automatically convert the value returned to the proper .NET data type. The value of a SQL Varchar column is returned as a String, the value of a SQL Int column is returned as an Int32 value, the value of a SQL Money column is returned as a Decimal, and so on.

The page in Listing 6.2.11 was written using Visual Basic.NET. The page in Listing 6.2.12 does the same thing using the C# language (this page is included on the CD as SqlDataReader.aspx in the C# folder).

Listing 6.2.12 Using the SqlDataReader with C#

 1: <%@ Page Language="C#" %>
 2: <%@ Import Namespace="System.Data" %>
 3: <%@ Import NameSpace="System.Data.SqlClient" %>
 4:
 5: <%
 6: SqlDataReader myDataReader;
 7: SqlConnection myConnection = new 
SqlConnection( "server=localhost;uid=sa;
 pwd=secret;database=Pubs" );
 8: myConnection.Open();
 9: SqlCommand myCommand = new SqlCommand( "Select * from 
Authors", myConnection );
10: myDataReader = myCommand.ExecuteReader();
11: while ( myDataReader.Read() )
12: {
13:  Response.Write( myDataReader[ "au_lname" ].ToString() );
14: }
15: myDataReader.Close();
16: myConnection.Close();
17: %>
18:

The ASP.NET pages in Listing 6.2.11 and 6.2.12 will only work with Microsoft SQL Server. To use other databases, you must use the System.Data.OleDb classes rather than the System.Data.SqlClient classes.

The page in Listing 6.2.13 illustrates how you would execute a query against a Microsoft Access database (this page is included on the CD as OleDbDataReader.aspx).

Listing 6.2.13 Using the OleDbDataReader

 1: <%@ Import Namespace="System.Data" %>
 2: <%@ Import NameSpace="System.Data.OleDb" %>
 3:
 4: <%
 5: Dim myConnection As OleDbConnection
 6: Dim myCommand As OleDbCommand
 7: Dim myDataReader As OleDbDataReader
 8:
 9: myConnection = New OleDbConnection( "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA
 Source=c:\authors.mdb" )
10: myConnection.Open()
11: myCommand = New OleDbCommand( "Select * from Authors", myConnection )
12: myDataReader = myCommand.ExecuteReader()
13: While myDataReader.Read
14:  Response.Write( myDataReader.Item( "author" ) )
15: End While
16: myDataReader.Close()
17: myConnection.Close
18: %>
19:

After you finish displaying the records from a DataReader, it is important that you explicitly close it (using the Close() method). If you neglect to close the DataReader, you might encounter problems when attempting to reuse the conncetion later in the page.

Read Part 3