I have been using asp.net 2.0 and SQL Server 2000 for programming for about an year now, I have worked on 3-Tier architecture as well. I am little confused about handling databse transaction, that weather transactions should be handled at BLL(business logic layer) or at DAL(data access layer)

An example with source will be much appriciated.

Thank you.

The DAL handles access to the datasource.  The BLL shouldn't have any transactions with the datasource.  The DAL gives the raw data to the BLL.  The BLL is used to manage the data based upon business rules and then hands it over to the Presentation layer.  You can look at the BLL as the heart of the process.  It processes both the input of and output to the presentation layer.

The greatest benefit of this model is the fact that you can make changes on one layer without affecting another.  For example, you could change the datasource in the DAL without worrying about how it will effect the other layers.  You could go from accessing the data in SQL server to Oracle without changing any code in the BLL or presentation layer.

Example Code:

Presentation Layer:

List<MyCustomDataType> myData = ProcessRequest(string param1, string param2);

 foreach(MyCustomDataType row in myData)
{
  Response.write(row.Property1);
  Response.write(row.Property2); 
}

BLL

public List<MyCustomDataType> ProcessRequest(string param1, string param2)
{
      List<MyCustomDataType> myData = GetRawData(param1, param2);
      
      // Adjust the data according to the business rules
      foreach(MyCustomDataType row in myData)
      {
         // Process the data / filter it according to the business rules
      }

      return  myData;
}

DLL

public List<MyCustomDataType> GetRawData(string param1, string param2)
{
       List<MyCustomDataType> myData = new List<MyCustomDataType>();

      // Connect to your datasource
      // do whatever

      //  Store the data in a list
      while (<there is data in the datasource>)
      {
         MyCustomDataType row = new MyCustomDataType();
         row.Property1 = raw data column;
         row.Property2 = raw data column;
         myData.add(row);
      }

      return myData;

}