DataAccess Tutorial

/ Home / HowTo / DataAccess

This is an abbreviated tutorial showing how to use the LJCDataAccess library and DataAccess class to retrieve data from a database.

The Data Access Library

The Data Access library (LJCDataAccess.dll) contains classes that provide common functionality for program access to database tables.

DataAccess Class

This class implements an ADO.NET SQL Data Access Control layer.

The Data Access class encapsulates the repetitive code required to perform common database functions using ADO.NET. This reduces the amount of application code required to access this functionality and provides a standard location for implementing common best practices.

This class is capable of connecting using the SQL Server data provider, OLEDB driver, ODBC driver or MySQL data provider.

The following examples use the SQL Server table created with this TSQL script.

IF NOT EXISTS(SELECT* FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = N'Person')
BEGIN
CREATE TABLE[dbo].[Person]
(
  [ID][int] IDENTITY(1,1) NOT NULL,
  [Name] [nvarchar] (60) NULL,
  [PrincipleFlag] [bit] NOT NULL,
  CONSTRAINT[PK_Person]
  PRIMARY KEY CLUSTERED(
  [ID] ASC),
)
END
GO
insert into Person
  (Name, PrincipleFlag)
values
  ('John Doe', 1),
  ('Jane Doe', 0);
GO

Create a new Solution named "DataAccessTest" and a .NET Framework WinForms project named "PersonApp".
Add a new .NET Framework Class Library Project named "PersonDAL" to the DataAccessTest solution.
Create a folder named "External" under the project folder.
Copy the files LJCDataAccess.dll and LJCNetCommon.dll to the "External" folder.
Copy the file LJCNetCommon.dll to the PersonDAL\bin\Debug folder.
Add a reference to the External\LJCDataAccess.dll file for the PersonDAL project and set the reference "Copy Local" property to "False".

Creating the Custom DataManager Class

Create a Folder named "Manager" in the PersonDAL project.
Add a Class named "PersonManager" in the Manager folder. This class is customized for the "Person" result set.

GetDataTable() Method

This is one of the most commonly used DataAccess methods.

using System.Data;
using System.Text;
using LJCDataAccess;

namespace PersonDAL
{
  /// <summary>Provides Table specific data manipulation methods.</summary>
  public class PersonManager
  {
    #region Constructors

    /// <summary>Initializes an object instance.</summary>
    /// <param name="tableName">The Table name.</param>
    public PersonManager(string tableName = "Person")
    {
      TableName = tableName;
      var dataSource = "DBServiceName";
      var database = "DBName";
      DataAccess = DataCommon.GetDataAccess(dataSource, database);
    }
    #endregion

    #region Public Methods

    /// <summary>
    /// Executes the Select statement and retrieves the DataTable object.
    /// </summary>
    /// <returns>The DataTable object.</returns>
    public DataTable GetDataTable()
    {
      DataTable retValue;

      string sql = GetSql();

      // The DataColumn names are the same as the Data Source
      // Table Column Names or the SQL rename 'AS' name.
      retValue = DataAccess.GetDataTable(sql);
      return retValue;
    }
    #endregion

    #region Private Methods

    // Get the SQL string.
    private string GetSql()
    {
      StringBuilder builder;
      string retValue;

      builder = new StringBuilder(128);
      builder.AppendLine("select");
      builder.AppendLine(" ID,");
      builder.AppendLine(" Name,");
      builder.AppendLine(" PrincipleFlag ");
      builder.AppendLine($"from {TableName};");
      retValue = builder.ToString();
      return retValue;
    }
    #endregion

    #region Properties

    // Gets or sets the DataAccess object.
    private DataAccess DataAccess { get; set; }

    // Gets or sets the Table name.
    private string TableName { get; set; }
    #endregion
  }
}

/ Home / HowTo / DataAccess