Focus on Microsoft Technologies - Tutorials, Articles, Code Samples.

Wednesday, August 30, 2006

Introduction to Data Access application block

Data Access application block
The Data Access application block contains optimized code for data access. It encapsulates the System.Data.SqlClient namespace and may be used for accessing SQL Server data. You can download the Data Access application block here.

The Data Access application block is encapsulated in the Microsoft.ApplicationBlocks.Data namespace. First, you open the source code and build the assembly, adding a reference to the Microsoft.ApplicationBlocks.Data.Dll file. The Data Access block consists of two classes. A detailed view of the contents of the classes is shown in Figure B.

Figure B
Data Access block classes

The SqlHelper class has five static methods (shared in VB.NET) that are used for all common data access needs. Based on the situation and the return type needed, you can call the corresponding ExecuteXYZ( ) method of the SqlHelper class. For example, if you need a Dataset object returned, you can use the ExecuteDataset method.

Let's take a look at a VB.NET code snippet showing the SqlHelper class in use:
Dim dsProducts As DataSet
Dim sConnectionString As String = "Data Source=(local);Initial Catalog=Northwind;User Id=sa;Password="
dsProducts = SqlHelper.ExecuteDataset(sConnectionString, CommandType.Text, “SELECT * FROM Products”)

This returns a Dataset populated with the results from a query executed using the ExecuteDataset method. By making use of the CommandType parameter and setting it to CommandType.StoredProcedure, you can also call stored procedures. In this case, you can specify the stored procedure parameters.

You can make use of SqlHelper.ExecuteNonQuery whenever you make an INSERT, UPDATE, or DELETE statement. The ExecuteReader can be used when a stream of forward-only data is needed. The SqlHelper class also supports XML with the ExecuteXmlReader method, which can be used to retrieve data as XML. The ExecuteScalar method is used for retrieving a single value.

The SqlHelperParameterCache is a utility class that caches parameters used with the commands on SQL Server databases. The application blocks include complete source code in both VB.NET as well as C#.

Post a Comment