Microsoft.NET

……………………………………………….Expertise in .NET Technologies

ADO.NET: Part 4 – ADO.NET Classes

Posted by Ravi Varma Thumati on November 6, 2009

Main Namespaces Used to Access ADO.NET Objects

Name Space Contains
System.Data ADO.NET base objects.
System.Data.OleDb Managed OLDDB data store objects
System.Data.SqlClient SQL Server-specific implementation

of the ADO.NET objects

System.Data.OracleClient Oracle specific implementation of the ADO.NET objects. 
System.Data.ODBC ODBC specific implementation of the ADO.NET objects

Before you can access any part of a database, you need to establish a connection to it. You can use either the OleDbConnection or SqlConnection object to represent that connection.

By creating an instance of a Connection object, and specifying data source-specific information in its properties, you can build a predefined link between the data consumer (your ASP.NET application) and the data provider (your database). Then, once you have established the connection to the data source, you can use the connection, in concert with other ADO.NET objects, to execute commands directly against the data source, to execute stored procedures, and to retrieve and manipulate data.

The Connection object also offers you the benefit of connection pooling—a mechanism that keeps connections pooled after you have explicitly closed them. Repeatedly opening and closing connections may consume Web server resources and can be a time-consuming process. Connection pooling is effectively a method of improving performance when a Web server is regularly accessed.

Creating a Connection

With ADO, ADO.NET’s predecessor, you, as the developer, could create Connection objects explicitly or through another object such as a Command object. That is no longer the case. With ADO.NET you must explicitly open your connections using one of its constructors. Some developers might be taken aback by this feature, however, we recommend that developers create their connection objects explicitly for two reasons: (1) the code is easier to maintain and (2) connection pooling can be utilized.

In order to use the OLEDB Managed Provider objects, such as OleDbConnection, OleDbCommand, and so on, you need to include the OleDb namespace in your ASP.NET page. To do this, you use the Import construct at the top of your ASP.NET page:

<%@ Import Namespace=”System.Data.OleDb” %>

For SQL

<%@ Import Namespace=”System.Data.SQLClient” %>

Now you’re ready to create your connection object. In the following code, we’ve used VB to create a connection object called oConn:

<%@ Import Namespace=”System.Data.OleDb” %>

<script language=”VB” runat=”server”>

Dim oConn As New OleDbConnection

</script>

The ADO.NET Connection objects, OleDb and Sql, have several constructors. A constructor is essentially the syntax you use to instantiate an object. Any given object can have several constructors, like the Connection objects, or no constructors, like the DataReader objects, which need to be instantiated or created by another object. In the previous code snippet, we demonstrated the use of the OleDbConnection’s default constructor that does not accept any parameters. The Connection objects have another constructor that accepts a connection string as its only parameter.

Here is a snippet of code that opens, using the OleDbConnection object’s Open method, a connection to a SQL Server database named “Music” that is located on my local machine:

Sub Page_Load(Sender As Object, E As EventArgs)

Dim oConn As OleDBConnection

Dim sConnString As String

sConnString = “Provider=SQLOLEDB;Data Source=(local);Initial

Catalog=Music;User;Password=music”

oConn = New OleDBConnection(sConnString)

oConn.Open()

oConn.Close()

End Sub

Opening a Connection

In order to open a connection to a data source, you need to know a little about the database. Kind of like making a phone call, you need to have a phone number. When opening a connection, you need to supply several pieces of information depending on the Relational Database Management System (RDBMS) you are using. Some of these pieces of information could be server name or IP address, database name, user name, and password. With this crucial information, you will construct a connection string that is effectively a collection of name/value pairs, separated by semicolons, which tell the Connection, object how to connect to your database. The information that you use to construct your connection string will vary depending on the type of database to which you are trying to connect. Table 17-2 lists some of the most common parameters you will use to build a connection string

Parameters Used to Construct a Connection String

Parameter Description
Provider The OLEDB provider used to access the database.
Data Source The IP address or name of the server on which the database resides.
Database The name of the database to be used once the connection is open.
User ID The user ID for the account used to access the database.
Password The password for the account used to access the database.

Building a Command

There are many ways of building, or constructing, a command object with ADO.NET. You can explicitly set the command object’s properties, pass parameters into the command object’s constructor, or a combination of the two. Following are several examples of how to initialize (or construct) an OleDbCommand object:

oCmd = New OleDbCommand()

oCmd = New OledbCommand(sSQL)

oCmd = New OledbCommand(sSQL, oConn)

In the previous listing, oConn is an OleDbConnection object and sSQL is a query command string.

sqlCmd = New SQLCommand()

sqlCmd = New SQLCommand (sSQL)

sqlCmd = New SQLCommand (sSQL, sqlConn)

In the previous listing, sqlConn is an SQLConnection object and sSQL is a query command string.

The OleDbCommand/SQLCommand object has several properties that you can explicitly set. Some of these properties are Connection, CommandText, CommandType, and CommandTimeout.

Connection property

The Connection property is used to set or get the connection against which to execute the command. You must pass a valid Connection object to the Connection property or you will receive an error.

 Below shows an example of how you might explicitly set the Connection property by passing it a valid Connection object.

Example:

Sub Page_Load(Sender As Object, E As EventArgs)

Dim oConn As OleDbConnection

Dim oCmd As OleDbCommand

Dim sSQL As String

Dim sConnString As String

sSQL = “SELECT * FROM t_bands”

sConnString = “Provider=SQLOLEDB;Data Source=(local);Initial

Catalog=Music;User;Password=music”

oConn = New OleDbConnection

With oConn

.ConnectionString = sConnString

.Open()

End With

oCmd = New OledbCommand(sSQL)

With oCmd

.Connection = oConn

End With

oConn.Close()

End Sub

CommandText property

The CommandText property gives you a means of holding your command (as a string) for later execution. It can contain a SQL statement, a stored procedure name, or a table name. For example, you can assign a simple SQL statement to the CommandText property as follows:

oCmd.CommandText = “SELECT band_id, band_title, music_type_id, record_company_id FROM

t_bands”

Alternatively, you could assign a stored procedure name to the CommandText property and tell the Command object you are using a stored procedure by setting the CommandType property accordingly:

oCmd.CommandText = “prGetBands”

oCmd.CommandType = CommandType.StoredProcedure

CommandType property

The CommandType property gets the CommandText or sets how it is interpreted. The possible values, or enumerations, of the CommandType property are

  • StoredProcedure
  • TableDirect
  • Text

When the CommandType property is set to StoredProcedure, the CommandText property is interpreted as a stored procedure. If the CommandType is set to TableDirect and the CommandText property is set to a valid table name, then all the rows and columns for the specified table are returned. This is generally not a good idea, for performance reasons, when executing the command against a large database. Finally, if the CommandType property is set to Text, then the CommandText is executed as a SQL text command.

Below is an example of how to execute a stored procedure called prCountBands.

Sub Page_Load(Sender As Object, E As EventArgs)

Dim oConn As OledbConnection

Dim oCmd As OledbCommand

Dim sSQL As String

Dim iBandCount As Integer

 

oConn = New OledbCOnnection(“Provider=SQLOLEDB;Data

Source=(local);Initial Catalog=Music;User;Password=music”)

oConn.Open()

oCmd = New OleDbCommand(sSQL, oConn)

oCmd.CommandType = CommandType.StoredProcedure

oCmd.CommandText = “prCountBands”

iBandCount = oCmd.ExecuteScalar()

oConn.Close()

lblBandCount.Text = iBandCount

End Sub

CommandTimeout property

The CommandTimeout property gets or sets the time, in seconds, to wait while executing the command before terminating the attempt and generating an error. The syntax for setting the CommandTimeout property follows:

oCmd.CommandTimeout = 60

The default value for the CommandTimeout property is 30 seconds. The CommandTimeout property is not inherited from the command’s Connection. The command object’s CommandTimeout property and the connection object’s CommandTimeout property are completely disparate properties. The Command object’s CommandTimeout property sets the maximum amount of time, in seconds, for a command to attempt to execute before returning an error. The Connection object’s ConnectionTimeout works the same way. The connection object attempts to open the connection for a designed amount of time before returning an error.

Setting the CommandTimeout property’s value to 0 indicates that the command will attempt to execute indefinitely. It is not a good practice to recommend this!

Appending parameters

The Command object supports a collection property named Parameters. The Parameters property is actually a ParameterCollection object that can contain more than one Parameter object. The Parameters property enables you to append parameters to the Command object. Parameters are generally attached to commands that are executing stored procedure that require input parameters. For example, you could write the following stored procedure to return a band’s title based on its band_id:

CREATE PROCEDURE prGetBandTitle

@iID AS INT

AS

SELECT band_title from t_bands WHERE band_id = @iID

RETURN

So how do you append parameters? First you create a Parameter object. A Parameter object can be constructed in several ways. For now, we’ll focus on constructing the Parameter objects by setting its Properties explicitly rather than passing them into the Parameter object constructor. The properties we’ll set are ParameterName, DBType, and Value as follows:

oParam = New OleDbParameter()

oParam.ParameterName = “@iID”

oParam.DBType = OleDbType.Integer

oParam.Value = 1

The OledbParameter object supports an Add () method that you can call to append the Parameter to your OLEDBCommand as shown in Listing.

Sub Page_Load(Sender As Object, E As EventArgs)

Dim oConn As OleDbConnection

Dim oCmd As OleDbCommand

Dim oParam As OleDbParameter

Dim sSQL As String

oConn = New OleDbConnection(“Provider=SQLOLEDB;Data

Source=(local);Initial Catalog=Music;User;Password=music”)

oConn.Open()

oCmd = New OleDbCommand(sSQL, oConn)

oCmd.CommandType = CommandType.StoredProcedure

oCmd.CommandText = “prGetBandTitle”

oParam = New OleDbParameter()

oParam.ParameterName = “@iID”

oParam.DBType = OleDbType.Integer

oParam.Value = 1

oCmd.Parameters.Add(oParam)

End Sub

You’ll notice in Listing that we first created the OleDbCommand, oCmd object. Next, we constructed the OleDbParameter oParam, object, and set its properties. Finally, we attached the OledbParameter to the OleDbCommand object using the Parameters collection’s Add () method with the following line of code:

oCmd.Parameters.Add(oParam)

The Parameter object supports many properties and methods and can become very complex.

Executing a Command

Now that you know how to construct an Command object, it is time that you ask it do something. The Command object has many useful methods, including the ExecuteReader(), ExecuteNonQuery(), ExecuteScalar(), and Prepare() methods.

ExecuteNonQuery method

The ExecuteReader() and ExecuteNonQuery() methods are similar in that they both execute commands against a data source. The main difference is the number of rows returned when the command is executed. As indicated by its name, the ExecuteNonQuery() method does not return any rows from the datasource; you probably won’t use this command when executing a SQL SELECT command. It could, however, be useful when executing INSERT, UPDATE or DELETE commands depending on your requirements. The ExecuteNonQuery() method does not require, or for that matter accept any parameters in its constructor. Here is a sample of calling the ExecuteNonQuery method:

oCmd = New OleDbCommand()

oCmd.Connection = oConn

oCmd.CommandType = CommandType.Text

oCmd.CommandText = “UPDATE t_bands SET band_title = ‘Hootie and The

Blowfish’ WHERE band_title = ‘Hootie & The Blowfish’”

oCmd.ExecuteNonQuery()

You’ll notice that we are executing a SQL UPDATE command so we probably don’t want any records returned. The ExecuteNonQuery() method does return the number of rows, as an integer, that were affected by the executed command. So if you wanted to determine how many records were affected by a command, you could use the following code:

Dim iAffected As Integer

iAffected = oCmd.ExecuteNonQuery()

ExecuteReader method

The ExecuteReader() method executes the CommandText against the command’s Connection and builds an object capable of forward-only data reads. This object is an OleDbDataReader. The syntax is

oDR = oCmd.ExecuteReader()

Where oDR is an OleDbDataReader object. Simple! Once you have populated the OleDbDataReader object by calling the OleDbCommand’s ExecuteReader() method, you have access to the data. We cover DataReader objects in detail in Session 19, “Using DataReaders.” Listing 18-5 demonstrates how to populate a datareader via the command object’s Execute method.

Sub Page_Load(Sender As Object, E As EventArgs)

Dim oConn As OleDbConnection

Dim oCmd As OleDbCommand

Dim oDR As OleDbDataReader

oConn = New OleDbConnection(“Provider=SQLOLEDB;Data

Source=(local);Initial Catalog=Music;User;Password=music”)

oConn.Open()

oCmd = New OleDbCommand()

With oCmd

.Connection = oConn

.CommandType = CommandType.StoredProcedure

.CommandText = “prGetBands”

oDR = oCmd.ExecuteReader()

End With

While oDR.Read()

lstBands.Items.Add(New

ListItem(oDR.Item(“band_title”),oDR.Item(“band_id”)))

End While

oDR.Close()

oConn.Close()

End Sub

we use the following line of code to populate the DataReader object with the results of the query:

oDR = oCmd.ExecureReader()

ExecuteScalar() Method

The ExecuteScalar() method executes the CommandText against the command’s Connection and returns a single value which is the first column of the first row of the resulting rowset. The syntax is

iBandCount = oCmd.ExecuteScalar()

Where iBandCount is a integer value returns the result set of query. And it fast and efficient in cases where a singleton value is required

Sub Page_Load(Sender As Object, E As EventArgs)

Dim oConn As OledbConnection

Dim oCmd As OledbCommand

Dim sSQL As String

Dim iBandCount As Integer

oConn = New OledbCOnnection(“Provider=SQLOLEDB;Data

Source=(local);Initial Catalog=Music;User;Password=music”)

oConn.Open()

oCmd = New OleDbCommand(sSQL, oConn)

oCmd.CommandType = CommandType.StoredProcedure

oCmd.CommandText = “prCountBands”

iBandCount = oCmd.ExecuteScalar()

oConn.Close()

lblBandCount.Text = iBandCount

End Sub

Prepare () method

The Prepare () method is used to create a prepared, or compiled, version of the command on the datasource. This method is generally used only when the CommandType property is set to Text; but it does improve performance when executing large SQL commands or dynamically generated SQL commands that contain parameters. The syntax for preparing a command is

oCmd.Prepare()


Introducing DataReader Class

So, what is a DataReader? A DataReader object is effectively a forward-only collection of records from your data source. The interesting thing about DataReader is that they do not have a public constructor per se. The DataReader is created via a Command object’s ExecuteReader method. Another interesting thing to note about DataReader objects is that, unlike many other ADO.NET objects, they can’t be disconnected—that is, they always need an active connection. Thus, you can’t, for example, pass them between business objects. The purpose of the DataReader is to provide data for display, that’s it. The DataReader objects are lightweight and very fast so they are ideal for this purpose.

When creating a DataReader, start by declaring a variable as follows:

Dim oDR As OleDbDataReader

The next thing you need to do is construct your Connection and Command objects. Next, initialize the DataReader object by calling the Command object’s ExecuteReader method as follows:

oDR = oCmd.Execute()

Now that is easy! Let’s bring it all together . . . The following example illustrates how to (1) construct and open a Connection, (2) construct a Command, and (3) call the Command’s ExecuteReader method and pass the result to a DataReader, as shown in Listing

Sub Page_Load(Sender As Object, E As EventArgs)

Dim oConn As OleDbConnection

Dim oCmd As OleDbCommand

Dim oDR As OleDbDataReader

oConn = New OleDbConnection(“Provider=SQLOLEDB;Data

Source=(local);Initial Catalog=Music;User;Password=music”)

oConn.Open()

oCmd = New OleDbCommand()

With oCmd

.Connection = oConn

.CommandType = CommandType.Text

.CommandText = “SELECT * FROM t_bands”

oDR = .ExecuteReader()         

End With

End Sub

Using DataReader Properties

OK, so now that you have your DataReader object, what can you do with it? Well, just like all other objects, the DataReader object has numerous properties and methods. We’ll start with the properties:

Item property

The Item property returns the value for a given column in its native format. In order to reference the value of a column, you need to pass a string representing the column name or an integer representing the column’s index. Take for example the following table called t_bands:

You could reference the band_title field in either of the following ways:

oDR.Items(“band_title”)

oDR.Items(1)

You’ll notice that we passed a one (1) to the DataReader object’s Items property. To clarify, the 1 is the column index or location of the column in the row from which we want to retrieve the data. We used 1 as the index, because the numbering of column indexes begins with 0.

FieldCount property

The FieldCount property, which is obviously read-only, returns the number fields, as an integer, in the current record. Here is some sample syntax for getting the FieldCount:

Dim iFCount As Integer

iFCount = oDR.FieldCount

One possible application of the FieldCount property is to iterate through the columns in a DataReader and write out the column’s value as shown in Listing

Sub Page_Load(Sender As Object, E As EventArgs)

Dim oConn As OleDbConnection

Dim oCmd As OleDbCommand

Dim oDR As OleDbDataReader

Dim iFieldCount As Integer

Dim x As Integer

oConn = New OleDbConnection(“Provider=SQLOLEDB;Data

Source=(local);Initial Catalog=Music;User;Password=music”)

oConn.Open()

oCmd = New OleDbCommand()

oCmd.Connection = oConn

oCmd.CommandType = CommandType.Text

oCmd.CommandText = “SELECT * FROM t_bands”

oDR = oCmd.ExecuteReader()

iFieldCount = oDR.FieldCount

While oDR.Read()

Dim oRow As New TableRow()

For x = 0 To (iFieldCount – 1)

Dim oCell As New TableCell()

oCell.Text = oDR.Item(x)

oRow.Cells.Add(oCell)

Next

tblExample.Rows.Add(oRow)

End While

oDR.Close

oConn.Close

End Sub

In Listing19-2, all we’ve done is open our OleDbDataReader object, obtain the number of fields in the DataReader using the FieldCount property, and iterate through the rows of the DataReader using the Read method. (We discuss the Read method later in this session.) For each row, we loop through the fields and create a table cell containing the columns’ value. Simple!

IsClosed property

The IsClosed method returns a Boolean value indicating whether the DataReader is closed. A value of true means that the DataReader is closed.

RecordsAffected property

The RecordsAffected property returns the number of rows that are changed, inserted, or deleted by the Command object that opens the DataReader. 0 is returned from the RecordsAffected property if no records were affected by the command object, and –1 is returned for SELECT commands. The RecordsAffected property is not set until the DataReader object is closed. The isClosed and RecordsAffected are the only DataReader properties that can be accessed after the DataReader has been closed.

Listing illustrates how you can use the isClosed and RecordsAffected properties to display information about the Command that was executed to create a DataReader object.

Sub Page_Load(Sender As Object, E As EventArgs)

Dim oConn As OleDbConnection

Dim oCmd As OleDbCommand

Dim oDR As OleDbDataReader

Dim oParam As OleDbParameter

Dim iBandID As Integer = 0

If Page.IsPostBack Then iBandID = lstBands.SelectedItem.Value

oConn = New OleDbConnection(“Provider=SQLOLEDB;Data

Source=(local);Initial Catalog=Music;User;Password=music”)

oConn.Open()

oCmd = New OleDbCommand()

With oCmd

.Connection = oConn

.CommandType = CommandType.StoredProcedure

.CommandText = “prBandDelete”

oParam = New OleDbParameter

With oParam

.ParameterName = “BandID”

.OleDbType = OleDbType.Integer

.Value = iBandID

End With

.Parameters.Add(oParam)

Try

oDR = .ExecuteReader()

lstBands.Items.Clear

lstBands.Items.Add(New ListItem(“”,0))

While oDR.Read()

lstBands.Items.Add(New

ListItem(oDR.Item(“band_title”),oDR.Item(“band_id”)))

End While

Catch err As Exception

Response.Write(“The following error occurred:<BR>” &

err.Message & “<BR>”)

End Try

End With

oDR.Close

oConn.Close

If oDR.isClosed Then

If oDR.RecordsAffected > 0 Then lblDeleted.Text = “You deleted “ &

oDR.RecordsAffected & “ bands from the database.”

End If

End Sub

You’ll notice that Listing 19-3 uses a ListBox Web Control, lstBands, to allow a user to select a band that he or she would like to delete from the t_bands table. When the Web Form containing lstBands is submitted, the id of the band is gathered and passed to OleDbCommand, oCmd, as an OleDbParameter, oParam. The prBandDelete stored procedure, which deletes the selected band and returns a recordset containing the remaining bands, is then executed by calling the Command object’s ExecuteReader method. Following is the code for the prBandDelete stored procedure.

CREATE PROCEDURE prBandDelete

@BandID INT = 0

AS

IF @iBandID > 0

BEGIN

DELETE FROM t_songs WHERE album_id IN (SELECT

album_id FROM t_albums WHERE band_id = @iBandID)

DELETE FROM t_albums WHERE band_id = @iBandID

DELETE FROM t_band_members WHERE band_id = @iBandID

DELETE FROM t_bands WHERE band_id = @iBandID

END

SELECT

band_id, band_title

OM

t_bands

DER BY and_title

When the ExecuteReader method is called, an OleDbDataReader, oDR, is constructed and then iterated through the data to repopulate the lstBands ListBox. After repopulating lstBands, we inspect to see that the DataReader has been closed, using the isClosed property. We then use the RecordsAffected property to display the number of records that were deleted from t_bands table.

Using DataReader Methods

Now that you know the properties you are likely to use most often, let’s move onto the DataReader’s methods. The DataReader objects provide a plethora of methods. Read method we’ve touched on the Read method in an earlier example (Listing 19-2). The Read method advances the DataReader object to the next record each time it is called. In the “Old World” of ADO you would have had to use a combination of several properties and methods, including EOF and MoveNext, to perform the same function as the DataReader’s Read method.

Since the DataReader provides for forward-only navigation, the Read method really minimizes the amount of code you need to write to get to your data. We do not provide a Read method example here as you can refer to several of the previous examples to see it in action.

GetValue method

The GetValue method returns the value of a specified field in its native format. You can effectively use the GetValue method in place of the Item property. The GetValue method accepts either an integer representing a column index or a string representing a column name. For example, if the first column (index of 0) of our table is called band_id, we can use the following statement to get its value:

iID = oDR.GetValue(0)

Or, we can use the following:

iID = oDR.GetValue(“band_id”)

Since band_id is set as an integer in our table, the value returned from the GetValue method will return an integer, its native format.

Get[Data Type] methods

The DataReader object provides a multitude of what we call the Get[Data Type] methods, including the GetString, GetInt32, and GetBoolean methods. The Get[Data Type] methods return the data in a column as the specified data type. For example, the GetString method will return the data in a column in the form of a string. However, no data type conversion is performed, so the data type of the column must be of the data type specified.

Unlike the GetValue method, the Get[Data Type] methods only accept a column index, also called an ordinal reference, as a parameter. The following statements demonstrate the GetString method:

Dim sBandName As String

sBandName = oDR.GetString(0)

GetOrdinal method

The GetOrdinal method returns a column’s ordinal reference value, or index, as an integer when passed a column name. For example, the following code returns 0 because band_id is the first column in the t_bands table:

Dim iOrdinal As Integer

iOrdinal = oDR.GetOrdinal(“band_id”)

GetName method

The GetName method is the exact opposite of the GetOrdinal method. It returns a column’s name as a string when passed its index. For example, the following code snippet will return band_id:

Dim sName As String

sName = oDR.GetName(0)

Close method

As the name implies, the Close method closes a DataReader object. Unlike other objects, closing the DataReader object is mandatory when you’re done using it. You will get an error if you don’t close your DataReader and then attempt to alter your Connection object. Closing a DataReader’s Connection object immediately closes the DataReader. The syntax is very simple:

oDR.Close()

That covers the DataReader’s major properties and methods.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: