Data Access Class Template for .NET

Occasionally, there is need to illustrate a point writing a full class. To avoid reproducing the boring parts of the class, I demonstrate here how to implement custom Data Access Class. Some private functions are not implemented, the same is about some of the functions implementations. However this example demonstrates the correct signatures and shows how to implement correctly public functions like:

• GetList
• Delete
• GetById
• Insert
• Update

This "OrderDataAccess" class uses Data Access Helper Class for low-level database Data Access. Order is supposed to be Business Entity.

public class OrderDataAccess
{
    public OrderDataAccess() { }


    public List GetOrderList() 
    {
        // declare objects
        SqlDataReader dataReader = null;
        List orderList = null;          
        DataAccessHelper dalcHelper = null;  
        
        try
        {
            // make database call
            dalcHelper = new DataAccessHelper();
            SqlParameter[] prams = { ... };
            dalcHelper.RunProcedure("GetOrderList", prams, out dataReader);
    
            // don't forget to implement GetOrderListFromDataReader 
            orderList = this.GetOrderListFromDataReader(dataReader);
        }
        catch(Exception ex)
        {
            throw;     
        }
        finally
        {
            dalcHelper.Dispose();
        }
         
        return orderList;
    }
      
      
    public void DeleteOrder(Order ord)
    {
        // declare objects
        DataAccessHelper dalcHelper = null;
        
        try
        {
            dalcHelper = new DataAccessHelper();
            SqlParameter[] prams = { ... };

            // run stored procedure
            dalcHelper.RunProcedure("DeleteOrder", prams);
        }
        catch(Exception ex)
        {
            throw;     
        }
        finally
        {
            dalcHelper.Dispose();
        }
    }


    public Order GetOrderById(int orderId)
    {
        // declare objects
        DataAccessHelper dalcHelper = null;
        Order ord = null;
        
        try
        {
            dalcHelper = new DataAccessHelper();
            SqlParameter[] prams = { ... };

            // run stored procedure
            dalcHelper.RunProcedure("GetOrderById", prams);
            
            
            ord = new Order();
            ord.OrderId = (int)prams[0].Value;
            
            // get other Order properties from SqlParameters array
            // (output and inputoutput)
        }
        catch(Exception ex)
        {
            throw;     
        }
        finally
        {
            dalcHelper.Dispose();
        }
        
        return ord;
    }


    public Order InsertOrder(Order ordIn)
    {
        // declare objects
        DataAccessHelper dalcHelper = null;
        Order ord = null;
        
        try
        {
            dalcHelper = new DataAccessHelper();
            SqlParameter[] prams = { ... };

            // run stored procedure
            dalcHelper.RunProcedure("InsertOrder", prams);
            
            
            ord = new Order();
            ord.OrderId = (int)prams[0].Value;
            
            // get other Order properties from SqlParameters array
            // (output and inputoutput)
        }
        catch(Exception ex)
        {
            throw;     
        }
        finally
        {
            dalcHelper.Dispose();
        }
        
        return ord;
    }


    public Order UpdateOrder(Order ordIn)
    {
        // declare objects
        DataAccessHelper dalcHelper = null;
        Order ord = null;
        
        try
        {
            dalcHelper = new DataAccessHelper();
            SqlParameter[] prams = { ... };

            // run stored procedure
            dalcHelper.RunProcedure("UpdateOrder", prams);
            
            
            ord = new Order();
            ord.OrderId = (int)prams[0].Value;
            
            // get other Order properties from SqlParameters array
            // (output and inputoutput)
        }
        catch(Exception ex)
        {
            throw;     
        }
        finally
        {
            dalcHelper.Dispose();
        }
        
        return ord;
    }
}

Notice that Update, Insert, Delete implementations are similar to each other and return Order. Delete, Update, Insert signatures have Order as their input parameter. Also please pay attention how exception is thrown to the upper layer. Very simple.

Related Posts:

• Data Access Helper Class Implementation
• Read XML Using XPathNavigator
• Data Access Approach to Implement Multilingual ASP.NET Application
• Asp.Net: Authentication based on HTTP Digest protocol. Module Enhancement.
• Asp.Net: Authentication based on HTTP Basic protocol. HttpModule.