Deborah's Developer MindScape






         Tips and Techniques for Web and .NET developers.

July 7, 2009

DAL: Data Access Layer

Filed under: C#,Data,VB.NET @ 4:39 pm

One of the common ways to access data in a .NET application is to use the drag and drop TableAdapter tools or the new Entity Framework tools. But what if you want to write your data access code yourself?

There are many ways to access data in an application without using the drag and drop tools:

SQL Server

  • Retrieve a DataTable using a stored procedure (example)
     
  • Retrieve a DataTable using a SQL string (example)
     
  • Access a DataReader using a stored procedure (example)
     
  • Access a DataReader using a SQL string (example)
     
  • Retrieve a populated business object using a DataContext
     
  • Save data using a stored procedure (example)
     
  • Save data using a SQL string (example)
  • … 
     

Microsoft Access

  • Retrieve a DataTable using Microsoft Access (example)
     
  • Update a Microsoft Access database (example)

Data Provider Factory

  • Retrieve a DataTable using any database (example)

Regardless of the technique you choose, you should consider writing the code in a separate project, often referred to as a data access component (DAC) or a data access layer (DAL). Placing this code in a separate project allows you to more readily reuse the code in any future solution. This separation is also good programming practice because it encapsulates all of your data access code in one place.

If you are a beginner with VB or C#, one of the most common practices for adding data access code in an application is to put the code directly in the Form Load or Selected Index Changed or Button Clicked events. The application then ends up with data access code and SQL statements sprinkled throughout the application. This creates a lot of repeated code and makes maintenance more difficult.

Even if you don’t want to create a separate project for your data access code, you should minimally put it in its own function so it can be handled in one place in your application.

Enjoy!

2 Comments

  1.   Troopers — October 16, 2009 @ 4:29 am    Reply

    Could you give us an example for the method :
    Retrieve a populated business object using a DataContext

    I wish bind a business object on data and I wish my methods return some List

  2.   Troopers — October 19, 2009 @ 3:34 am    Reply

    Hi,

    I create this function to return a List<mybusinnessobject>

    public static List<T> ExecuteAndBindToObject<T>(Param pParam)
    {
       // Create the return list of T
       List<T> lList = new List<T>();
       // Get the type T and the properties
       Type tType = typeof(T);
       PropertyInfo[] piProperties = tType.GetProperties();
       // Execute the stored procedure
       DataTable dtTable = ExecuteDataTable(pParam);
       // If we found some records
       if(dtTable != null && dtTable.Rows.Count > 1)
       {
          // For each record
          for(int iRow = 0; iRow < dtTable.Rows.Count; iRow++)
          {
             // Create an object of type T
             T oCurrentObject = (T)Activator.CreateInstance(tType);

             // For each property of T
             foreach(PropertyInfo piProperty in piProperties)
             {
                // Get the binding info attribute
                object[] oAttributes = piProperty.GetCustomAttributes(typeof(BindingInfo), true);
                if(oAttributes.Length != 0)
                {
                   BindingInfo biInfo = (BindingInfo)oAttributes[0];
                   // Get the value in the datasource
                   object oValue = dtTable.Rows[iRow][biInfo.SourceFieldName];
                   // Set the property value of the current object
                   piProperty.SetValue(oCurrentObject, oValue.GetType() ==
                                             typeof(DBNull) ? null : oValue, null);
                }
             }
             // Add the current object
             lList.Add(oCurrentObject);
          }
       }
       // Return the list
       return lList;
    }

    [AttributeUsage(AttributeTargets.Property, AllowMultiple=false)]
    public class BindingInfo:System.Attribute
    {
       private string sSourceFieldName;
       public BindingInfo(string sSourceFieldName)
       {
          this.sSourceFieldName = sSourceFieldName;
       }
       public string SourceFieldName
       {
          get { return sSourceFieldName; }
       }
    }

    NOTE: I reformatted the code – DJK

     

RSS feed for comments on this post. TrackBack URI

Leave a comment

© 2019 Deborah's Developer MindScape   Provided by WPMU DEV -The WordPress Experts   Hosted by Microsoft MVPs