Designing and implementing a versatile data access tier for an ASP.NET application

• Download source files - 1Mb

Abstract

In this article, we will drill down deeper in to the design of a n-tier architecture and our focus will be on the data access tier (DAT) (Figure 0).Our goal is to design and implement an appropriate data access tier(DAT) for scalable, reliable and interoperable web application. I have divided this article in to two major parts. In the first part, we will discuss about architectural goals, typed DataSet, EventLogs and HttpModules, which are helpful to understand the second part of this article . We will build in the second part a small(but fine) n-tier application and our focus will be on design and implementation of data access tier. You need a SqlServer (database: Northwind) and VS.net to follow this article.

Contents:

1. Designing Goals of a data access tier(DAT)

1.1 Architectural Goals

1.2 Installation of the prototype

1.3 Typed DataSets

1.4 Event Logs

1.5 HttpModules

2.0 Implementing versatile data access tier(DAT)

2.1 Implemeting a super class for data access tier.

2.2 A performance test to evaluate SqlDataReader and DataSet objects in the business tier.

2.3 How can I extend this data access tier (DAT) to access multi-databases.

2.4 How can I apply database transactions with different isolation level?

2.5 How can I insert and retrieve images?

Figure 0 shows us a typical 3 Tier Application that is dissected in to three major disjunctive layers, which are data tier (DA),middle tier and presentation tier (PT).

If you are not familiar with n-tier architecture, I like to recommend following articles.

• http://www.c-sharpcorner.com/Tutorials/Building3TierAppPA.asp
• http://www.c-sharpcorner.com/Code/2002/June/AdoNetWinDNAPerspective.asp

1. Designing an appropriate data access tier (DAT)

1.0 Architectural Goals

The must requirements of an enterprise application can be expressed through the following buzzwords

• Interoperability and Extendibility
• Scalability and Performance
• Reliability and Manageability
• Security

Let us look some of the buzzwords closer, what do they really mean?

Interoperability deals with the ability of a system to communicate with other internal or external systems in a heterogeneous environment . This is a very important issue, because we need information to act just in time to meet needs of our customers and business partners. To achieve this important goal, our system must able to offer and consume web services.

To be agile in your business, sometimes you need to extend your application. If we couple a system so tightly to other systems, then it may trigger a “modify avalanche” (e.g. you pass data to a peer application via email, which represents data tables through comma separated columns. The recipient will open the email and parse it to gain information. Say, if we want add or change a column, then we have to modify other depending systems too because the order of columns have changed).In order to avoid “modify avalanches”, we must couple systems loosely using technologies such like Web services (preciously: XML supported technologies ).

Scalability is a measure which describes the ability of a system to increase performance by adding additional system resources .A system applies following strategies to be scalable.

1. It must able to allocate new resources to satisfy increasing user requests.
2. It must use algorithms and data structures which won’t occupy resource for long time.

Scalability is a very important issue, because you can invest in a system with the confidence that the system will grow with the business.

Reliability of a software system can be defined as the probability that a system will perform its task without failure . To achieve higher reliability, a system must provide utilities to track possible errors.

Figure 1 shows partial ER-Diagram of the database Northwind

So, we have now great goals. In order to achieve these goals, we have to lay a proper groundwork. Our data access tier (DAT) must be designed to accomplish the following tasks.

1. It must enable us to interact with set of data sources and to coordinate transactions among them
2. It must be flexible to use different data access technologies (SqlDataAdapter and DataSet), because this issue deals with scalability and performance of your system.
3. It must support presentation tier (PT) to display coherent data tables . Most of interrelated tables of a relational database are modeled according to master-detail (Parent-Child ) relationship . In our database example (see Fig 1), you can find out some these duos and they are: Customers-Orders . Orders-Order Details. There is always a need in the presentation tier(PT) to drill down the master table to expose details. The Figure 2 displays the master-detail relationship Customer-Order .
4. It must provide infrastructure to trace and analyze errors .

Figure 2 shows a screen shot of the demo web application DAPrototype

1.2 Installation of the prototype

Before we move further, I recommend you to install the sample application DAPrototype, so that you can refer it time to time. The solution DAPrototype.sln consists following class libraries (see Fig 3).

Figure 3

Please follow these instruction to install the sample application:

1. Download the file DAPrototype.zip
2. Create a new ASP.NET C# web application DAPrototype and replace it with the file which you have downloaded.

3. Open and execute the SQL Script DAPrototype/SqlScript/DAPrototype.sql, in order to create store procedures on the database Nortwind which are part of our data tier (DT)

4. In order to create Event Logs(Windows) for the presentation, business and data access tiers, open the C# project DAProtype/ELCreator/ELCreator.csproj separately (see 1.4). Please adjust the machine name there and execute it . Because of security reasons, We can’t integrate this creational functionality in our web application . The Window user ASPNET has no sufficient right to create windows resources.
5. Adjust configuration values for the keys in the DAPrototype/Web.config especially the keys localConnection, daMachine, blMachine and plMachine (See Fig 4).

   <appSettings>
    <!-- Database Connection -->
   <add key="LocalConnection" 
        value="server=localhost;database=Northwind;uid=sa;pwd=moses; 
   pooling=true; Max Pool Size=100;"/>
   <!-- Event Log for Data Access Tier -->
   <addkey="daMachine"value="ABRAHAM"/>
   <addkey="daLog"value="DALog"/>
   <addkey="daSource"value="DASource"/>
   <!-- Event Log for Business Tier -->
   <addkey="blMachine"value="ABRAHAM"/>
   <addkey="blLog"value="BLLog"/>
   <addkey="blSource"value="BLSource"/>
   <!-- Event Log for Prsentation Tier -->
   <addkey="plMachine"value="ABRAHAM"/>
   <addkey="plLog"value="PLLog"/>
   <addkey="plSource"value="PLSource"/>
              
    <!--- application constants-->
   <addkey="consumeTaxRate"value="0.16"/>
   </appSettings>

   Figure 4

6. Set the page DAPrototype/SerchCustomOrders.aspx as start page.

I hope now, that you can run the application.

1.3 Typed DataSets

A DataSet is a in-memory database and relies on relational data model. Datasets are predestinated for cross-tier communication. Like every relational data model, it has data table, foreign key constraints, triggers for table relations. DataSets are self sufficient, because they must be independent from feeding data sources. Further more, DataSets are also able to deal with hierarchal data structures such like XML DOM by treating XML elements as tables and XML attributes as table attributes. Let us look an example. (see Fig 5). The presented XML file lists some states and its cities of Germany.

<?xmlversion=”1.0”encoding=”utf-8”?>
<Germany>
    <StateName=”Bayern”>  
        <CityName=”Munic”/>  
        <CityName=”Nürenburg”/>
    </State>
    <StateName=”NRW”>
        <CityName=”Düsseldorf”/>  
        <CityName=”Bonn”/>
    </State>
</Germany>

XML File

XSD file for Corresponding DataSet

Figure 5

In this case, Dataset “ German”, needs two tables State and City and a data relation to represent this XML document. We don’t want to dig here further, because it is beyond our focus.

Let us look a typical data access scenario. Say, you using one of the overloaded method SqlDataAdapter.Fill to populate a DataSet (untyped) from a database . The SqlDataAdapter.Fill method takes a DataSet and a name of the DataTable as parameters. DataTables and DataColumns will be only created in the Dataset, if they don’t already exist in the DataSet. In this case, it will retrieve the required schema from the data source using the SqlDataAdapter.Schema method, after that DataTables and DataColumns will be created, which will fit the loading data.

DataSet (untyped) offers great deal of plasticity at runtime, but they are error-prone, because developers must anticipate the tables and its attributes, while they code. In worst case, developers must trace back from business layer (BL) to stored procedures to find out the content of a DataSet. Additionally, it is not always possible to follow back A such case is given, when you receive a DataSet from a third party via Webservice and it is not well documented The most errors(e.g. type mismatch errors) concerning DataSets can be trapped only during the runtime. You can wipe out this sort of problem using typed DataSets.

Typed DataSets are subclasses of the class DataSet and it has built-in and solid-stated (hard coded) DataTables and DataRows, which are specified to represent a certain relational data model. Metaphorically spoken, untyped DataSets are like T-Shirts and a typed DataSet can be compared to a made-to-measure suit.

Typed DataSets have in contrast to untyped DataSet following advantages.

1. It is easy read and write source code because hard-coded DataTables and DataRows exposes attributes and their types during the coding time. If you use untyped DataSet then you have to search them explicitly in the DataTable and DataRow collections and you must know their types.
2. Type mismatch errors can be discovered during the compile time.
3. Access to DataTables and DataRows of a typed Dataset is slightly faster because the access is determined at compile time and you don’t need to search DataTables and DataRows through accessing collections at runtime.

Now, we like show you, how create to a typed DataSet using of Visual Studio Intellisence. We like create a typed DataSet DSCustomer,in which we can store data from customers and their orders. We will use store procedures SPSelCustomers and SPSelOrders (See Fig 6) to fetch data from master-detail tables Customer and Orders

CREATE PROCEDURE  SPSelCustomers
(
  @CustomerID  varchar(5)
)
AS
SELECT 
  C.CustomerID,C.CompanyName,C.Address,C.City
FROM   Customers C
WHERE  C.CustomerID LIKE @CustomerID + '%'
GO

CREATE PROCEDURE SPSelOrders
(
   @CustomerID nchar(5)
)
AS
SELECT  O.OrderID,O.CustomerID, O.OrderDate
FROM   Orders O
WHERE O.CustomerID =  @CustomerID
ORDER BY O.OrderDate  DESC
GO

Figure 6

In order create the typed DataSet DSCustomer, we must first design a blueprint . A XSD schema serves this purpose(See Fig 7). Follow these steps to create such a schema.

(You can refer the file DAProtype/TDS/DSCustomer.xsd )

1. Create a new DataSet file DSCustomer.xsd .
   
   (Open the Solution Explorer-> right mouse click on the project -> choose
   Add -> Add New Item-> select DataSet and type there DSCustomer.xsd ).
   
2. Create XML elements that represent the built-in DataTables .(Launch the Server Explorer and navigate:
   
   Servers- > SqlServer -> Northwind -> Store Procedures, then drag SPSelCustomers and SPSelOrders and drop them on DSCustomer.xsd ).
   
3. Create a DataRelation (master-detail ) between the tables SPSelCustomers and SPSelOrders . 
   
   (Right mouse click on the table SpSelCustomers - > Add -> New Relation. See Fig 8).
   
4. Give friendly names to DataTables and DataRows without changing the underlying schema .
   
   (Click on the XML view and type the following statements on the file DSOrders.xsd, which are marked by an underline
   
   • <xs:schemaid=DSCustomerxmlns:codegen=urn:schemas-microsoft-com:xml-msprop.
   • <xs:elementname=SPSelCustomerscodegen:typedName=Customercodegen:typedPlural=Customers>
   • <xs:elementname=SPSelOrderscodegen:typedName=Ordercodegen:typedPlural=Orders>

   Now, we have denominated the tables SPSelOrders and SpSelOrders as Customers and Orders and their DataRows as Customer and Order )

5. Generate the class DSOrder (Swich back to DataSet view and right mouse click -> Generate DataSet)

Note: If you want use typed DataSets in Web service, then it is important to keep in mind that xml annotations to create friendly name won’t work with WSDL(web Service Description Language).

Figure 7 XSD schema for the typed DataSet “DSCustomer “

Figure 8

The following code snippet demonstrates that is very comfortable to write and read the source code with the typed DataSet “DSCustomer”, for it exposes its built-in DataTable and DataRows as properties .

foreach(DSCustomer.Customer drCustomer in dsCustomer.Customers)
{
    if( drCustomer.CustomerID ==ANTON)
    {

        DSCustomer.Order[] oField = drCustomer.GetOrders();
        foreach(DSCustomer.Order drOrder in oField)
        {
            // Do something genius

            //int j = drOrder.OrderID;

                                   
                 
        }
    }
}

1.4 Event Logs

Event Logs are centralized Windows resources to protocol important software and hardware events and it is indispensable to determine error sources. You can easily connect event logs of remote computers on your local computer, consequently event logs are suitable to analyze a n-tier application which is deployed on more than one machine.

You can use the EventLog class (namespace: System.Diagnostics) to deal with event logs and they allow you write different types of entries (e.g. information, warning, error etc) that helps us to express severity of occurred events. The following code snippet shows the basics of the class EventLog .

// Initialize the names for the logs and their sources

string strMachine = abraham;
string strLogBL = BLLog;
string strSourceBL = BLSource;

// Create EventLog for the Business Logic

if(!EventLog.SourceExists(strSourceBL,strMachine))
{
    EventLog.CreateEventSource(strSourceBL,strLogBL,strMachine);
    Console.WriteLine(New log and a source for BL is  created);
                                
}
else
{
    Console.WriteLine( source for BL exists already);
     // Delete EventLog.Delete(strLogBL,strMachine);

 }

// create an instance of EventLog of BL and enter some test entries

EventLog elBL= new EventLog(strLogBL,strMachine,strSourceBL);
elBL.WriteEntry(Test Error,EventLogEntryType.Error);
elBL.WriteEntry(Test Inforamtion,EventLogEntryType.Information);
elBL.WriteEntry(Test Warning,EventLogEntryType.Warning);

Of course, you can delete an existing EventLog using the event the EventLog.Delete method. Writing on Event Logs consumes resources(CPU time and disk space), therefore it is recommended to write only important events.

1.5 HttpModules

HttpModule is an useful instrument to customize ASP.NET applications. In other words, when CLR processes a web-request from a client, you can use HttpModules to take influence on every stage of html- render process. We will see later in details that request-processing in ASP.net relies on the pipeline process model., which enables parallel processing (Webgarten ) and enhance expendability and manageability of a web application .

Let us look for a moment, where we can apply HttpModules. In commercial application, there is always a need collect data for customer profiling, product marketing, forecasting business trends etc. You can use HttpModules in elegant way to collect data with out blotting the application code. It is also applicable to solve security issues( e.g. modules to prevent web attacks, authorization). Further more, HttpModules are very flexible(manageable), because you can switch on or off HttpModules on the web.config file. We are using HttpModules in our sample application to record the errors that occur in the presentation layer. We must understand first the ASP.NET pipeline process model in order to write HttpModules.

Figure 9 sketches ASP.NET pipeline

Let us look, what happens in a server, when it processes a request.

Say, a client submits a request for the page Test.aspx .

Step 1:

IIS will extract page name’s suffix, in our concrete example “ .aspx “, in order to load an appropriate ISAPI extension.. IIS will look in the metabase and load for the suffix “.aspx “ the extension aspnet_isapi.dll . (You can manage the extension- loading process by editing the Application Configuration dialog(See Fig 10). You can open this dialog by launching the Internet Services and expand -> right click on Default Web Site -> select the option “Properties” -> select the tab “Home Directory “-> push the button “Configuration” )

Figure 10

Afterwards, the HttpExtensionProc function,which is provided by the aspnet_isap.dll, will be invoked ; subsequently, the client request Test.aspx will be

hand over to the aspnet_wp.exe process. This process serves as unmanaged platform for the common language runtime (CLR), that’s why you add the aspnet_wp.exe process to the VS debugger, when you debug ASP.NET applications.

Step 2

Now, our request is now passed to a System.Web.HttpRuntime object, which marks the entry point of the process pipeline and the frontier of managed code . In fact, the HttpRuntime class is responsible for processing the requests, but it funnels incoming requests through the pipeline for further processing. The client request will be wrapped into an instance of the HttpContext class and delivers it to an appropriate instance of HttpApplication class . ASP.Net treats every virtual directory as a separate application. The HttpRuntime object will examine the client request and uses an instance of the HttpApplicationFactory class to create or find a proper instance of the HttpApplication class.

The HttpContext class serves as transport medium in the pipeline; therefore, it is accessible in every stage of the pipeline. The class HttpContext has instances of classes HttpRequest, HttpResponse, HttpSessionState, HttpServerUtility and so forth as intrinsic components . Thus makes this object self sufficient on its journey along the process pipeline to give information about request,user, session and to load the produced HTML code.

Step 3

The HttpApplication class is responsible for managing the entire life cycle of request execution process ; therefore, it raises events to mark pipeline stages(e.g . BeginRequest, PreRequestHandlerExcecute, PostReuestHandler, EndRequesthandler, Error …etc). You can now write handlers to subscribe events of the HttpApplication class in order to customize execution process . There are two major ways to implement it. You can use directly the Global class, which inherits the HttpApplication class (implemented in the file Global.asax.cs ), but if you hardcode the handlers in the instance Gloabal, then it will reduce manageability and flexibility of the application. HttpModules are designed to solve this problem in an elegant manner . Spoken in the language of design and pattern, HttpModules can be considered as observers of the subject HttpApplication, because they are designed to intercept events of the class HttpApplication . They implement the interface IHttpModule and complied in to a separate assembly .We will show you later in an concrete example, how to write a HttpModule. Let us move to next step.

Note :

A HttpApplication object can be used to process many requests, but it can process only one request at a time and ASP.Net doesn’t treat a HttpApplication object as singleton, particularly in webgarden. So, this class is not appropriate to hold properties which deals with whole application such like number of current users.

Step 4.

Now, we come to the climax of request processing. In this phase,contents of the server side controls are transformed into HTML code and saved into the instance of the HttpContext class and this scenario can be described through the pattern abstract factory . Let us look, how this procedure realized.

At first, the instance of the HttpApplication class selects an appropriate HttpHandlerFactory object, which implements the interface IHttpHandlerFactory . In our example Test.aspx, HttpApplication object will look on the file machine.config ( C:\WINNT\Microsoft.NET\Framework\v1.0.3705\CONFIG) under section <httpHandlers> and choose because of the suffix “.aspx “ the HttpHandlerFactory :System.Web.UI.PageHandlerFactory .

In order to get an appropriate instance of the System.Web.UI.Page class, which implements the interface IHttpHandler, HttpApplication object will invoke the method PageHanderFactory:GetHandler . PageHandlerFactory. HttpApplication object will look first for the requested url Test.aspx on the file web.config under the section <httpHandlers> to find out, if there any HttpHandler(Page) specified. If it finds a specification, then it will create the specified HttpHander and give it to the HttpApplication object. Otherwise, PageHandlerFactory will presume that a HttpHandler(Page) with the name Test exists and create the Test page and return it to the HttpApplication object.

Finally, HttpRuntime object will invoke the method IHttpHandler.

ProcessRequest (HttpContext context),in our example Test. ProcessRequest

(HttpContext context), to load the content of sever side controls in to the instance of the class HttpContext and it will go back through HttpModules, if they implement any handlers for the post request execution events.

Now, we look how to implement a HttpModule .As we mentioned before that HttpModules implement the interface IHttpModule . An instance of the class HttpApplication(more preciously : class Global ) is responsible for instantiating HttpModules. The IHttpInterface interface exposes two contract methods.

interface IHttpModule
{
   void  Init(HttpApplication  httpApplication);
   void  Dispose();
}

The method “void Init” is normally used subscribe events from HttpApplication and method “ void Dispose” is a potential candidate to release unmanaged resources .

We are using a HttpModule HttpTraceModule (see Fig 11) in our prototype DAPrototype to record errors that occurr in the presentation layer(PL), therefore we intercept the event HttpApplication.EndRequest and retrieve the initial error in the presentation layer(PL) using the property HttpContext.Error . To use this facility, you must add the thrown exception to the current HttpContext using the AddError(Exception e) method .

using System;
using System.Web;
using System.Diagnostics;
using System.Configuration;
using System.Text;


namespace HttpTraceModule
{
    ///<summary>

    /// It used to record errors on the presentation layer(PL)

    ///</summary>

    publicclass HttpTraceModule:IHttpModule
    {
         
        ///<summary>

        /// Event Log for the Presentation Layer

        ///</summary>

         EventLog elPL;
         ///<summary>

         ///

         ///</summary>

         public HttpTraceModule()
         {
             // Initialize  Trace Listener

             string strLog = ConfigurationSettings.AppSettings["plLog"];
             string strSource = ConfigurationSettings.AppSettings["plSource"];
             string strMachine = ConfigurationSettings.AppSettings["plMachine"];
             if(EventLog.SourceExists(strLog,strMachine))
             {
                 elPL = new EventLog(strLog,strMachine,strSource);
             }

         }

         ///<summary>

         /// interface contract method

         ///</summary>

         ///<param name="httpApplication"></param>

         publicvoid Init(HttpApplication httpApplication)
         {
             // Subscribe HttpApplication events

             httpApplication.BeginRequest += new EventHandler(Application_BeginRequest);
              httpApplication.EndRequest += new EventHandler(Application_EndRequest);
         }
         ///<summary>

         /// interface contract method

         ///</summary>

         publicvoid Dispose()
         {
                      

         }
         ///<summary>

         /// Writes errors on the log of the Presentation Layer

         ///</summary>

         ///<param name="oException"></param>

         ///<param name="oContext"></param>

         privatevoid WriteError(HttpContext oContext,Exception oException)
         {
             StringBuilder oBuilder = new StringBuilder();
             // Record the requested URL and the client

             oBuilder.Append("An Error took place, while attempting to request  the  URL: ");
             oBuilder.Append(oContext.Request.RawUrl);
             oBuilder.Append("**");
             oBuilder.Append("Client Address:");
             oBuilder.Append(oContext.Request.UserHostAddress);
             oBuilder.Append("**");
             //  Record the error message

             if( oException != null)
             {
                 oBuilder.Append( oException.Message);
             }
             if(elPL != null)
             {
                 elPL.WriteEntry(oBuilder.ToString(),EventLogEntryType.Error);
             }

         }


         ///<summary>

         /// Callback method  for the event HttpApplication.EndRequest

         /// It is used to record all errors on the log of the Presentation Layer

         ///</summary>

         ///<param name="source"></param>

         ///<param name="e"></param>

         privatevoid Application_EndRequest(object source, EventArgs e)
         {
             HttpApplication hApplication = (HttpApplication) source;
             HttpContext oContext = hApplication.Context;
             Exception oException = oContext.Error;
             if(oException != null)
             {
                 this.WriteError(oContext,oException);
             }
                        
         }

         ///<summary>

         /// Callback method  for the event HttpApplication.BeginRequest

         ///</summary>

         ///<param name="source"></param>

         ///<param name="e"></param>

         privatevoid Application_BeginRequest(object source, EventArgs e)
         {
             HttpApplication hApplication = (HttpApplication) source;
             HttpContext oContext = hApplication.Context;
                              
         }
    }
}

Figure 11

We have compiled this module in to the assembly HttpTraceModule.dll and

You must endorse the following entry on web.config file to activate this module.

 <httpModules>
 <!-- Module for Traceing -->
 <add  type=HttpTraceModule.HttpTraceModule,HttpTraceModule    name=HttpTraceModule/>
 </httpModules>

2.0 Implementing versatile data access tier (DAT)

Now, we are going to implement a data access tier that satisfies all expectations that we have outlined in the section 1.0 . First, we will design a super class.

2.1 Implementing a super class for data access tier (DAT)

We want now design a super class for our data access tier (DAT), which is capable enough to take over regular data access routines from its derived classes. We must foremost analyze diverse data access contexts, which can be categorized into

• Selecting DataRows using DataSets
• Retrieving data using SqlDataReader
• Updating and deleting tables directly using SqlCommand (Excecute –NonQuery)

Let us analyze these data access processes one after another:

Selecting DataRows using DataSet

Step 1:

Create objects for the database connection to the database and SqlCommand object for the stored procedure “strSP” .

SqlConnection  dbConnection_L = new SqlConnection(strLocalServer);
SqlCommand  dbCommand_L = new SqlCommand (strSP,dbConnection_L);
dbCommand_L.CommandType = CommandType.StoredProcedure;

Step 2:

Create an instance of SqlDataAdapter class and associate with the SqlCommand object: dbCommand_L.

SqlDataAdapter dbAdapter_L = new SqlDataAdapter();
dbAdapter_L.SelectCommand = dbCommand_L;

Step 3:

Add the query parameters to the SqlCommand object:

SqlParameter oParameterIn = new SqlParameter(“@ParaIn”,SqlDbType.Int,4);
oParameterIn.Direction = ParameterDirection.Input;
oParameterIn.Value = 7;
dbCommand_L.Parameters.Add(oParameterIn);

Step 4:

Open the database connection : dbConnection_L.Open(); We don’t need to open the connection explicitly here, because dbAdapter_L object will open the connection implicitly in the step 5.

Step 5:

Fill the Dataset:

DataSet   dsOut = new  Dataset();
string   strTable = Customer ;
dbAdapter_L.Fill(dsOut,strTable);

Step 6:

Close the database connection: dbConnection_L.Close();

Retrieving data using SqlDataReader

This type of data accessing differs little from previous one. We must leave out the step 2 and modify the step 5.

Step 1:

Create an object for the connection to the database and an instance of SqlCommand for the stored procedure “strSP “.

SqlConnection  dbConnection_L = new SqlConnection(strLocalServer);
SqlCommand  dbCommand_L = new SqlCommand (strSP,dbConnection_L);
dbCommand_L.CommandType = CommandType.StoredProcedure;

Step 2:

We don’t need the step 2 here.

Step 3:

Add the query parameters to the SqlCommand object:

SqlParameter oParameterIn = new SqlParameter(“@ParaIn”,SqlDbType.Int,4);
oParameterIn.Direction = ParameterDirection.Input;
oParameterIn.Value = 7;
dbCommand_L.Parameters.Add(oParameterIn);

Step 4:

Open the database connection : dbConnection_L.Open();

Step 5:

Receive the reader by executing the SqlCommand .ExcecuteReader method

SqlDataReader oReader = dbCommand_L.ExecuteReader(CommandBehavior.CloseConnection);

The enumerator “CommandBehavior .CloseConnection” is used in the dbCommand_L.ExecuteReader method to close database connection automatically after the retrieval of SqlDataReader object .

Step 6:

Because of the enumerator “ CommandBehavior CloseConnection” we don’t need the step 6 .

Updating and deleting tables (Executing non-queries)

This type of data accessing differ slightly from previous data access process. Let us look this type step by step.

Step 1:

Create a connection object to the database and an instance of SqlCommand class

for the stored procedure “strSP”.

SqlConnection  dbConnection_L = new SqlConnection(strLocalServer);
SqlCommand  dbCommand_L = new SqlCommand (strSP,dbConnection_L);
dbCommand_L.CommandType = CommandType.StoredProcedure;

Step 2:

We don’t require this step here, because we don’t need any instance of a class SqlDataAdapter in this process.

Step 3:

Add the in- parameters and out-parameters to the SqlCommand object:

SqlParameter oParameterIn = new SqlParameter(“@ParaIn “,SqlDbType.Int,4);
oParameterIn.Direction = ParameterDirection.Input;
oParameterIn.Value = 7;
dbCommand_L.Parameters.Add(oParameterIn);

SqlParameter oParameterOut = new SqlParameter(“@ParaOut”,SqlDbType.Int,4);
oParameterOut.Direction = ParameterDirection.Output;
dbCommand_L.Parameters.Add(oParameterOut);

Step 4:

Open the database connection explicitly : dbConnection_L.Open();

Step 5:

Execute the non-query and retrieve output-parameter value

int nAffected = dbCommand_L.ExecuteNonQuery();
int nOut = (int) oParameterOut.Value;

Step 6:

Close the database connection: dbConnection_L.Close();

As you can see, we have here ideal condition to design a super class .First of all, we must

capsule the important data access objects as protected elements in the basic class DABasis ( Please refer parallel the Figure 12 and source code on the file DAPrototype/DAT/DABaisis.cs )

// Objects for the local database access  

protected SqlConnection dbConnection_L;
protected SqlCommand  dbCommand_L;
protected SqlDataAdapter dbAdapter_L;
protected OleDbTransaction dbTransaction_L;

We use here the suffix “_L”, in order to express that these object deals with the local sql server. This notation will enhance clarity, if our DAT deals with many databases.

We are now going to implement functionalities of step 1 in the

DABasis.Perpair_L(string strSP) method . It will create a connection to the local database and an instance of the SqlCommand class for a certain store procedure with the name strSP .

///<summary>

/// This method initialize data access utilities for the local

 /// Sqlserver this method must be always called at first

///</summary>

///<param name=strSP>name of the stored procedure</param>

protectedvirtualvoid Prepair_L(string strSP)
{
    try
    {
        dbConnection_L = new SqlConnection(strLocalServer);
        dbCommand_L = new SqlCommand (strSP,dbConnection_L);
        dbCommand_L.CommandType = CommandType.StoredProcedure;

    }
    catch(Exception oException)
    {
        string strMessage = Occurred in Prepa_L() ;
        ErrorLog(strMessage,oException);
    }
                           
}

Figure 12 shows the class diagram of the data access tier(DAT)

We don’t implement functionalities of step 2 in the super class, because this step is not general for all data access methods.

The functionalities of Step 3 will be implemented in the derived class, but the super class supports this step with a parameter-factory . The parameter-factory is designed according to the pattern abstract factory and it will supply us with several type of SqlParameters with variations Input and Output direction, which are used set and retrieve values from the parameters . Further more, super class DABasis supplies a method virtualvoid AddParameter_L(object oParameter) to add an instance of SqlParameter class to the dbCommand_L object . Thus makes your code compact and neat.

The super class DABasis supports step 4 with the void Open_L() method to open the database to the local server and the complement step 6 is covered by the void Close_L() method .

The functionality of the step 5 is implemented in the methods void GetDataSet_L(DataSet dsOut,string strTable) and int ExcecuteNonQuery(). Now, it is time to look, how we can apply these methods in different database access contexts.

In the first practical example, we want to retrieve order details for an order using the DataSet object.The workflow for the implementation can be sketched through following steps.

• Write a store procedure “ SpSelOrderDetail” to access the data in the database(see figure 13).
• Create a typed DataSet “ DSOrderDetail for the store procedure(see how in §1.3)
• Write a data access method in the data access tier (DAT): void DAOrder.GetOrderDetail(int nOrderID,out DSOrderDetail dsOrderDetail)( See Figure 14).

CREATE PROCEDURE    SPSelOrderDetail
(
   @OrderID int
)
AS
SELECT   OD.ProductID, OD.UnitPrice,OD.Quantity, P.ProductName
FROM [Order Details]   OD INNER JOIN Products  P
 ON  OD.ProductID =  P.ProductID
WHERE OD.OrderID =@OrderID

GO

Figure 13

///<summary>

/// Retrieves  order details  for an OrderID  

///</summary>

///<param name=nOrderID>OrderID</param>

///<param name=dsOrderDetail>typed DataSet</param>

publicvoid GetOrderDetail(int nOrderID,out DSOrderDetail dsOrderDetail)
{
    // Create the typed Dataset for output

    dsOrderDetail = new DSOrderDetail();
    try
    {
        // lock the intrinsic DataAcces utilities

        Monitor.Enter(this);
        // intialize DataAcces utilities for the local SqlServer

        Prepair_L(“SPSelOrderDetail”);dbAdapter_L = new SqlDataAdapter();
        dbAdapter_L.SelectCommand = dbCommand_L;
        // Add query parameters to the command

        SqlParameter pmOrderID = pmFactory_In.GetPMInt4(“@OrderID”);pmOrderID.Value = nOrderID;
        AddParameter_L(pmOrderID);
        string strTable = dsOrderDetail.OrderDetails.TableName;
        // Retrive the typed DataSet

        GetDataSet_L(dsOrderDetail,strTable);
    }
    catch(Exception oException)
    {
        string strError;
        strError = An Error Occured in DAOrder:GetOrderDetailOrders;
        this.ErrorLog(strError,oException);

    }
    finally
    {
        this.Close_L();
        Monitor.Exit(this);

    }
}

Figure 14

Note: In order to protect intrinsic data –access objects from concurring threads, We are using the Monitor.Enter(this) method and it is complement

Monitor.Exit(this) in the publicvoid GetOrderDetail(int nOrderID,out DSOrderDetail dsOrderDetail)method . Moreover, we will use always try-catch-finally block in the data access tier (DAT) and we perform the kernel operation in the try-block, in the catch –block : we write possible errors in the Eventlogs, in the finally-block : we clean used -resources which we used in the try – block;

In the second practical example, we want to demonstrate the int DABais.ExcecuteNonQuery method, which can be used to update and insert databases directly. If you implement the presentation layer(PL) with ASP.NET, it is very efficient update directly against the database than using one of the SqlDataAdapter.Update method to update the DataSet object, because you must store the instance of a particular DataSet during the page-roundup in the session memory ; consequently, that will affect adversely the scalability and performance. In this example, we will update the attribute Quantity of the table Order Details (see Figure 15).

CREATE PROCEDURE SPUpOrderDetail
(
@OrderID int,
@ProductID int,
@Quantity int
)
AS
UPDATE [Order Details]
SET Quantity = @Quantity
WHERE ProductID=@ProductID AND OrderID=@OrderId

Figure 15

///<summary>

/// Updates  quantity of a product in an order deatail

///</summary>

///<param name=nOrderID>OrderID</param>

///<param name=nProductID>Product</param>

///<param name=nQuantity>Quantity</param>

///<returns> affected Rows</returns>

publicint UpdateOrderDetail(int nOrderID,int nProductID,int nQuantity)
{
    int nAffected = 0;
    try
    {
        // lock the intrinsic DataAcces utilities

        Monitor.Enter(this);
        // intialize DataAcces utilities for the local SqlServer

        Prepair_L(“SPUpOrderDetail”);// Add parameters to the command

        SqlParameter pmOrderID = pmFactory_In.GetPMInt4(“@OrderID”);pmOrderID.Value = nOrderID;
        AddParameter_L(pmOrderID);

        SqlParameter pmProductID = pmFactory_In.GetPMInt4(“@ProdID”);pmProductID.Value = nProductID;
        AddParameter_L(pmProductID);

        SqlParameter pmQuantity = pmFactory_In.GetPMInt4(“@Quantity”);pmQuantity.Value = nQuantity;
        AddParameter_L(pmQuantity);

        // Open the Connection and exequte

        this.Open_L();
        nAffected = this.ExcecuteNonQuery();

    }
    catch(Exception oException)
    {
        string strError;
        strError = An Error Occured in DAOrder:UpdateOrderDetail;
        this.ErrorLog(strError,oException);
    }
    finally
    {
        this.Close_L();
        Monitor.Exit(this);
    }
    return nAffected;
}

Figure 16

The store procedure “ SPUpOrderDetail” will act in the data tier (DT) to accomplish the update task.The int DAOrder.UpdateOrderDetail method (See Fig 16) will receive values from business logic tier (BLT) and it will create parameters for them, then created parameters will be added to the SqlCommand object with the help of inherited DABais.AddParameter_L method. Afterwards, we will use the DABais.ExcecuteNonQuery() method to update the database directly.

In the third example, we will use a SqlDataReader object to fetch data, which is an alternative to using DataSet objects. The functionality of this example is same as in example 1. The method public SqlDataReader DAOrder.GetOrderDetail(int nOrderID) retrieves order details of an Order and uses the store procedure “SpSelOrderDetail “ (Figure 13) in the data tier(DT).

///<summary>

/// retrieves  order details  for an OrderID  

///</summary>

///<param name=nOrderID>OrderID</param>

///<returns>SqlDataReader</returns>

public   SqlDataReader GetOrderDetail(int nOrderID)
{
    SqlDataReader oReader = null;
    try
    {
        // lock the intrinsic DataAcces utilities

        Monitor.Enter(this);
        // intialize DataAcces utilities for the local SqlServer

        Prepair_L(“SPSelOrderDetail”);// Add parameters to the command

        SqlParameter pmOrderID = pmFactory_In.GetPMInt4(“@OrderID”);pmOrderID.Value = nOrderID;
        AddParameter_L(pmOrderID);

        // Open the connection to the local server and  Excecutethe command

        this.Open_L();
        oReader = dbCommand_L.ExecuteReader(CommandBehavior.CloseConnection);
                       
    }
    catch(Exception oException)
    {
        this.Close_L();
        Monitor.Exit(this);
        string strError;
        strError = An Error Occured in DAOrder:GetOrderDetailOrders(DR);
        this.ErrorLog(strError,oException);
    }
    return oReader;
               
}

Figure 17

Unlike DataSets, a SqlDataReader holds the database connection open and it will retrieve data-rows one after another from the database in to the application. Afterwards, you must close the database connection explicitly . You can use SqlDataReader,when you need to read large amount of data quickly and there is no need for in-memory database. I prefer to use DataSets in multi-tier applications than SqlDataReaders, because there is always a need to calculate some business metrics in the middle tier before the retried data visualized in the presentation layer(PL).

In this case, SqlDataReaders are not faster than Datasets. Let me confirm the previous statement through a performance test.

2.2 A performance test to evaluate SqlDataReader and DataSet objects in the business tier.

In this experiment, we are going to implement a typical data retrieving scenario of a business application using SqlDataReader and DataSet,then we will put both implementations under the same well defined stress condition. We can simulate a multi-user requests using the Microsoft Web Application Stress Tool and measure metrics such like hits and requests per seconds.

I think that it is appropriate to consider the following data accessing scenario as an

average case in a commercial web application. We want to display an invoice list for an order. In sake of simplicity we will leave out bonus, consume -tax-rate depend on the product category and so forth from the calculation.. We will calculate the invoice list of an order using the simplified calculation formula

where

(See the table Order Details in the figure 1)

2.2.1 DataSet Implementation

We will use the DAOrder.GetOrderDetail(int nOrderID,out DSOrderDetail dsOrderDetail) method ( See Figure 14) to retrieve data from the database.

In order to calculate the total sum, we will alter the OrderDetail table in the DSOrderDetail DataSet with a decimal attribute: Price ( see in source code TDS/DSOrderDetail.xsd).

///<summary>

/// Retrieves  order details  for an OrderID  

///</summary>

///<param name=nOrderID>OrderID</param>

///<param name=dsOrderDetail>typed DataSet represent OrderDetail</param>

///<param name=dTotal>Total amount of an order</param>

public void GetOrderDetail(int nOrderID,out DSOrderDetail dsOrderDetail, outdecimal dTotal)
{
    dsOrderDetail = null;
    dTotal = 0;
    try
    {
        DAOrder daOrder = new DAOrder();
        daOrder.GetOrderDetail(nOrderID,out dsOrderDetail);

        // compute the total amount of order

        dsOrderDetail.OrderDetails.Columns[Price].Expression = “Quantity*UnitPrice”;
    object oSum = dsOrderDetail.OrderDetails.Compute(Sum(Price),Quantity>0);
        decimal dSum = Convert.ToDecimal(oSum);
        dTotal = dSum*dConsumeTaxRate;
                         
    }
    catch(Exception oException)
    {
        string strMessage = Error in BLOrderDetail:GetOrderDetail;
        ErrorLog(strMessage,oException);

    }
                     
}

Figure 18 (MethodBLOrderDetail. GetOrderDetail)

In the BLOrderDetail. GetOrderDetail method (Figure 18), we will first retrieve the typed DataSet “ DSOrderDetail” from database via data access tier, afterwards we will use the expression “Quantity* UnitPrice” to calculate the Price column. Now, we can determine the total of the invoice list by summing the Price column and

multiply this sum with the factor dConsumeTaxRate . We will then expose our invoice list on the DAPrototype/DSTest.aspx page.

2.2.1 SqlDataReader Implementation

We will use the store procedure SPSelOrderDetail (Figure 13) and the public SqlDataReader DAOrder.GetOrderDetail(int nOrderID) method (Figure 17) to retrieve necessary data from the database to the business logic tier(BLT). As you can see in the public SqlDataReader BLOrder.GetOrderDetail(int nOrderID,outdecimal dTotal) method(Figure 19), we must retrieve twice data from the database. We use the first data retrieval to calculate the business metric “total “ and the second data retrieval will be passed to presentation layer to expose invoice list.(DAPrototype/DRTest.aspx )

///<summary>

/// Retrieves  order details  for an OrderID 

///</summary>

///<param name=nOrderID>Id of an Order </param>

///<param name=dTotal> Total amount of invoice list </param>

///<returns> SqlDataReader </returns>

public SqlDataReader GetOrderDetail(int nOrderID,outdecimal dTotal)
{
    dTotal = 0;
    SqlDataReader oReader = null;
                     
    try
    {
        DAOrder daOrder = new DAOrder();
        // compute the total amount of order

        oReader = daOrder.GetOrderDetail(nOrderID);
        while(oReader.Read())
        {
            decimal dUnitPrice = Convert.ToDecimal(oReader[UnitPrice]);
            int nQuantity = Convert.ToInt16(oReader[Quantity]);
            dTotal += dUnitPrice * nQuantity;
        }
        dTotal*= dConsumeTaxRate;
        // Retrive for the presentation layer

        oReader =  daOrder.GetOrderDetail(nOrderID);

    }
    catch(Exception oException)
    {
        string strMessage = Error in BLOrderDetail:GetOrderDetail(DR);
        ErrorLog(strMessage,oException);
    }
    return oReader;

}

Figure 19

2.2.2

We can now start the performance test. I am using my home computer for this test

and it uses the Windows 2000 OS and has a single 800 MHz processor and 130 MB RAM. Sql Server 2000,.Net Framework and Web stress tool are installed in the same machine. We will keep the request stress level constant during the test, which will be 200 simultaneous browser connection. Web Stress Tool will send request for the Dataset implementation(DAPrototype/DSTest.aspx.) and the SqlDataReader implemetation (DAPrototype/DRTest.aspx.) for five minutes. Here are the results:

DataSet  implemetation
Total number of requests: 42.093
Average requests per second: 140,31
Average  database connection per second:31,90

SqlDataReader  implemetation
Total number of requests: 35.663
Average requests per second: 118,88
Average  database connection per second:173,27

Conclusion:

Dataset implementation is not only slightly faster than SqlDataReader implementation, but also it uses database connections efficiently. Furthermore, DataSets enable us to implement business-logic efficiently than DataReaders.

Now, let us discuss remaining aspects in the question and answer form.

2.3 How can I extend this data access tier (DAT) to access multi-databases.

It is very easy to extend it. Say, you want extend DAT, so that it can work with the database X. An extending workflow is given as follows.

1. Create protected attributes in the super class DABasis, in order to represent data access classes from the name space System.Data.SqlClient;

   protected SqlConnection dbConnection_X;
   protected SqlCommand dbCommand_X;
   protected SqlDataAdapter dbAdapter_X;
   protected SqlTransaction_X

2. Create methods which wraps these intrinsic data access objects. These methods can be listed as follows

PrePair_X,Open_X,BegeinTransaction_X,ExcequteScalar_X,ExcequteNonQuery_X,

GetDataSet_X,ReuseCommand_X. In order to establish database connection, you must add a key on the web.config file under the section <appSettings> and read and assign it to a protected variable in the super class DABasis. Folks, that’s all, now

our data access tier is ready to work with database X.

2.4 How can I apply database transactions with different isolation level?

.The DABasis class supports database transactions with the methods:

protected void BeginTransaction_L(IsolationLevel iLevel),
protected void Rollback_L(),protectedvoid Commit_L()(Figure 20).

The void BeginTransaction_L(IsolationLevel iLevel) method is the start point of the transaction and you can start transactions with different isolation level. The ANSI SQL standard defines four level of transaction isolation and SqlServer supports all of them.

Let us look all four isolation levels briefly:

1. Uncommitted read( dirty read): Lets a transaction read uncommitted and committed data from other transaction.
2. Commited read: Lets a transaction read only committed data from other transaction and SqlServer uses these isolation level as default.
3. Repeatable Read: This isolation level ensures that an another transaction won’t update your own transaction data until you have committed or roll backed your transaction. But you can’t prevent phantoms with this isolation level.(An another transaction can add a row to your own transaction data)
4. Serializable: This is the highest isolation level and it ensures that an another transaction won’t update, delete or insert your own transaction data.

///<summary>

/// Starts a transaction to the local server

/// with isolationlevel

///</summary>

protectedvoid BeginTransaction_L(IsolationLevel iLevel)
{
    try
    {
        // assign the OleDbtransaction to the SqlCommand

        this.dbTransaction_L = dbConnection_L.BeginTransaction(iLevel);
        this.dbCommand_L.Transaction = this.dbTransaction_L;

    }
    catch(Exception oException)
    {
        string strMessage = "Occured in BeginTransaction_L()";
        ErrorLog(strMessage,oException); 

    }
}

///<summary>

/// Rollbacks the transaction to the local server

///</summary>

protectedvoid Rollback_L()
{
    try
    {
        this.dbTransaction_L.Rollback();
    }
    catch(Exception oException)
    {
        string strMessage = "Occured in RollBack_L()";
        ErrorLog(strMessage,oException);

    }
}

///<summary>

/// Commits the local server

///</summary>

protectedvoid Commit_L()
{
    try
    {
        this.dbTransaction_L.Commit();
    }
    catch(Exception oException)
    {
        string strMessage = "Occured in Commit_L()";
        ErrorLog(strMessage,oException);
    }
}

Figure 20

Let us explain, how these methods work together through an example. In this example we going to delete an order and it’s details. Because of master-detail relationship constraints, we must delete first the order details and then the order itself. It is very efficient using a single store procedure “SPDelOrders” ( see Figure 21 and invoke it in an ExecutenonQuery method.

-- Used in DAPrototype

CREATE PROCEDURE SPDelOrders
(
@OrderID int
)
 AS
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION

DELETE [Order Details]  WHERE OrderID = @OrderID
DELETE Orders WHERE OrderID = @OrderID

IF @@error  > 0
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
GO

Figure 21

But in sake of demonstration, we delete an order and its order details in the data access tier. We can achieve this goal by modifying ExecuteNonQuery pattern. We will use store procedures “SPDelODDemo” and “SPDelOrdersDemo” to delete order details and the order respectively(Figure 22).

-- store  procedures delete order details and order

CREATE PROCEDURE SPDelODDemo
(
    @OrderID int
)
AS
    DELETE [Order Details]   WHERE OrderID  = @OrderID

/* Used in DAPrototype*/
CREATE PROCEDURE SPDelOrdersDemo
(
    @OrderID int
)
AS
DELETE Orders  WHERE  OrderID = @OrderID

Method of the DAOrder class to delete orders

///<summary>

/// Deletes an order and its corresponding entries in order details .

/// Its is used to demonstrate transaction

///</summary>

///<param name=nOrderID></param>

public  bool DeleteOrder(int nOrderID)
{  
    // At first, we will delete Order Details with the

    // foreign key value nOrderID and after we

    // will delete the corresponding Order. If this transaction fails,

    // then we will roll back first transaction

    bool bSuccess=false;
    try
    {
        Monitor.Enter(this);

        // Delete Order Detials with the OrderID

        Prepair_L(“SPDelODDemo”);// Add the parametrs

        SqlParameter pmOrderID = pmFactory_In.GetPMInt4(“@OrderID”);
    pmOrderID.Value = nOrderID;
        AddParameter_L(pmOrderID);
        // open the connection to the local server

        this.Open_L();
        this.BeginTransaction_L(System.Data.IsolationLevel.Serializable);
        int nOD = ExcecuteNonQuery_L();
        // write on the WaringLog

        StringBuilder strBuilder = new StringBuilder();
        strBuilder.Append(nOD);
    strBuilder.Append( Rows are deleted in [Order Details] with the OrderID );
        strBuilder.Append(nOrderID);
        this.WarningLog(strBuilder.ToString());
                         
        // delete Orders with the OrderID= nOrderID

        ReuseCommand_L(“SPDelOrdersDemo”);// Add the parametrs

        AddParameter_L(pmOrderID);
        nOD = ExcecuteNonQuery_L();
        // write on the WaringLog

        strBuilder = new StringBuilder();
        strBuilder.Append(an order was deleted with ID );
        strBuilder.Append(nOrderID);
        WarningLog(strBuilder.ToString());
                       
        // commit the transaction

        this.Commit_L();
        bSuccess=true;
                       
    }
    catch(Exception oException)
    {
        // Rollback the transaction

        bSuccess=false;
        this.Rollback_L();
        string strMessage = "An error occured in DAOrderDetail:DeleteOrder" ;
        ErrorLog(strMessage,oException);

    }
    finally
    {
        Close_L();
        Monitor.Exit(this);
    }
    return bSuccess;
}

Figure 22

Let us analyze the method publicbool DAOrder.DeleteOrder(int nOrderID)

step by step.

Step 1.

We initialize the data base connection and a SqlCommand object with the store procedure to delete Order details. Afterwards we will add query parameters to the instance of the SqlCommand class

Monitor.Enter(this);
// Delete Order Detials with the OrderID

Prepair_L(“SPDelODDemo”);// Add the parameters

SqlParameter pmOrderID = pmFactory_In.GetPMInt4(@OrderID);
pmOrderID.Value = nOrderID;
AddParameter_L(pmOrderID);

Step 2.

Open the database connection,start the transaction using the BeginTransaction_L(System.Data.IsolationLevel.Serializable) method

and execute the store procedure “ SPDelODDemo” .

// open the connection to the local server

this.Open_L();
this.BeginTransaction_L(System.Data.IsolationLevel.Serializable);
int nOD = ExecuteNonQuery_L();
// write on the WaringLog

StringBuilder strBuilder = new StringBuilder();
strBuilder.Append(nOD);
strBuilder.Append( Rows are deleted in [Order Details] with the OrderID );
strBuilder.Append(nOrderID);
this.WarningLog(strBuilder.ToString

Step 3

Reinitialize the SqlCommand object by clearing previous query parameters, set the store procedure “SPDelOrdersDemo” and execcute it.

// delete Orders with the OrderID= nOrderID

ReuseCommand_L(“SPDelOrdersDemo”);// Add the parametrs

AddParameter_L(pmOrderID);
nOD = ExecuteNonQuery_L();
// write on the WaringLogstrBuilder = new StringBuilder();

strBuilder.Append(an order was deleted with ID );
strBuilder.Append(nOrderID);
WarningLog(strBuilder.ToString());

Step 4

If there is no error exception in the try block, then the transaction will be committed, otherwise it rolled back in the exception block.

// commit the transaction

this.Commit_L();
bSuccess=true;
               
}
 catch(Exception oException)
 {
     // Rollback the transaction

     bSuccess=false;
     this.Rollback_L();
     string strMessage = An error occured in DAOrderDetail:DeleteOrder ;
     ErrorLog(strMessage,oException);

  }
 finally
 {
     Close_L();
     Monitor.Exit(this);
 }

2.5 How can I insert and retrieve images?

Say, we have a table “Images” with following attributes:

PId  char(5) 
Picture image

We can now use the store procedures “SPInImage” and “SPSelImage” to insert and retrieve images from the database.

CREATE PROCEDURE  SPInImage
(
 @PId  char(5),
 @Picture  image
)
AS
IF NOT EXISTS(SELECT * From Images WHERE PId=@PId)
BEGIN
    INSERT  INTO Images
    (PId,
    Picture
    )
 VALUES
 (
    @PId,
    @Picture
 )
 END
 GO
 CREATE PROCEDURE  SPSelImage
 (
    @PId char(15)
 )
 AS
    SELECT   Picture FROM  Images  WHERE  PId = @PId
 GO

Figure 23

Now, we can use the methods bool InsertImage(string strPId,byte[] imPicture) and byte[] GetImage(string strPId) to invoke these store procedures(SeeFigure 24)

///<summary>

/// Inserts a picture to the table

///</summary>

///<param name="strPId"></param>

///<param name="imPicture"></param>

///<returns></returns>

publicbool InsertImage(string strPId,byte[] imPicture)
{
    bool bInsert = false;

    try
    {
        // lock the intrinsic DataAccess utilities

        Monitor.Enter(this);
        // Initialise DataAccess utilities for the local server

        this.Prepair_L("SPInImage");

        // Add the parameters to SqlCommand

             
        SqlParameter pmPId = pmFactory_In.GetPMChar5("@PId");
        pmPId.Value = strPId;
        this.AddParameter_L(pmPId);


        SqlParameter pmPicture = pmFactory_In.GetPMImage("@Picture");
        pmPicture.Value = imPicture;
        this.AddParameter_L(pmPicture);

        // Excequte the query

        this.Open_L();
        int nExcequte = ExcecuteNonQuery_L();

        if(nExcequte>0)
        {
            bInsert = true;

        }

    }
    catch(Exception oException)
    {
        string strError = "An Error Occured in :InsertImage";
        ErrorLog(strError,oException);

    }
    finally
    {
        this.Close_L();
        Monitor.Exit(this);

    }
    return bInsert;

}
 
 
///<summary>

/// retrieves an image from the table “images”///</summary>///<param name="strPId"></param>

publicbyte[] GetImage(string strPId)
{
                     

     byte[] imPicture = null;

     try
     {
         // lock the intrinsic DataAccess utilities

         Monitor.Enter(this);
         // Initialise DataAccess utilities for the local server

         this.Prepair_L("SPSelImage");

         // Add the parameters to SqlCommand

         SqlParameter pmPId = pmFactory_In.GetPMChar15("@PId");
         pmPId.Value = strPId;
         this.AddParameter_L(pmPId);

         // Excequte the query

         this.Open_L();
         object oScalar = this.ExcecuteScalar_L();
         if(oScalar!=null)
         {
             imPicture = (byte[]) oScalar;

         }
     }
     catch(Exception oException)
     {
         string strError = "An Error Occured in :GetImage";
         ErrorLog(strError,oException);

     }
     finally
     {
         this.Close_L();
         Monitor.Exit(this);

     }
     return imPicture;
}

Figure 24