Using Microsoft’s Data Access Application

The first step is to reference the data, SQLClient, and data access application block

namespaces. Add the following statements to the top of your data access client

source file:

<%@ Page Language="VB" ClassName="Recipe1001vb" %>

<%@ import Namespace="System.Data" %>

<%@ import Namespace="System.Data.SqlClient" %>

<%@ import Namespace="Microsoft.ApplicationBlocks.Data" %>

<script runat="server">

Next, you have to place the following code inside a method. Page_Load will do fine for

this example:

Sub Page_Load(sender As Object, e As EventArgs)

Dim dataSet As DataSet

'parameter value variables

Dim beginningDate As string = "1/1/1996"

Dim endingDate As string = "12/31/1996"

Try

Dim connectionString As string = _

"Integrated Security=yes;Initial Catalog=Northwind;Data Source=(local)"

Dim arParams(1) As SqlParameter

arParams(0) = New SqlParameter("@Beginning_Date", beginningDate)

arParams(1) = New SqlParameter("@Ending_Date", endingDate)

dataSet = SqlHelper.ExecuteDataset(connectionString, _

CommandType.StoredProcedure, "Employee Sales by Country", arParams)

'bind dataSet to a WebControl and call Page.DataBind()

Catch exception As Exception

'An error occurred - were your connection string,

'stored procedure name and parameters correct?

'you could display the error via a WebControl or throw an exception here

End Try

End Sub 'Page_Load

Comments

This is a simple example-it barely scratches the surface of data access application

block functionality.There's support for performing transactional updates and fetching

XML as well as data-readers and caching parameters for multiple calls to a given stored

procedure.

The SqlHelper object manages just about everything for you. In order to achieve the

same result in ADO.NET, you would need SqlConnection, SqlCommand, and

SqlDataAdapter objects in addition to what you've declared here. Connection management

is improved as well because the SqlConnection is created internally to the object.

There is no need you for to close the connection-the object handles closing the connection

as it goes out of scope.

Finally, take the opportunity to examine the source code for the application block.

There are many examples of best practices in the source code that you might want to

apply to your own projects!