/ Assembly List / LJCDataAccess / DataAccess

Namespace - LJCDataAccess


Syntax

C#
public class DataAccess

Implements an ADO.NET SQL data access control layer (DE).

Remarks

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 most commonly used methods are GetDataTable(), ExecuteNonQuery() and GetSchemaOnly().

Data Access Class Diagram

Constructor
#ctor Initializes an object instance with the provided values.

Insert Update and Delete
ExecuteNonQuery Executes an Insert, Update or Delete statement. (E)

Select Methods
FillDataTable Executes a Select statement and fills the specified DataTable. (E)
GetDataReader Executes a Select statement and retrieves the DbDataReader object. (E)
GetDataSet Executes a Select statement and retrieves the DataSet object. (E)
GetDataTable Executes a Select statement and retrieves the DataTable object. (E)
GetSchemaOnly Retrieves the DataTable object with schema only. (E)

Script Methods
ExecuteScript Executes a DB script file. (E)
ExecuteScriptText Executes a DB script text string. (E)

Stored Procedure
GetProcedureDataTable Executes a Stored Procedure and retrieves the DataTable object. (E)

Other Public Methods
#ctor Initializes an object instance.
CloseConnection Closes the database connection.
GetColumnSQLTypes Get the column SQL types.
13 Methods

Other Private Methods
GetConnectionString Missing Summary
GetDataAccess Missing Summary
IsUseCommand Checks for the "use" command.
3 Methods

Properties
ConnectionString Gets or sets the ConnectionString value.
ProviderFactory Gets a reference to the LJCProviderFactory object.
ProviderName Gets the ProviderName value.
ScriptSQL Gets the ScriptSQL value.
TableName Gets the TableName value.

Example

This is main function sample code for testing. The code for the additional test functions is listed under the methods with the same names.

C#
using System;
using System.Data;
using System.Data.Common;
using System.IO;
using LJCDataAccess;
using LJCDataAccessConfig;

// Test DataAccess.
private static void TestDataAccess()
{
  DataAccess dataAccess;
  string connectionString;
  string providerName;

  // Create Data Configuration values.
  bool useInternal = false;
  if (useInternal)
  {
    // Use internal configuration.
    var connectionBuilder = new DbConnectionStringBuilder()
    {
      { "Data Source", "DataServiceName" },
      { "Initial Catalog", "DatabaseName" },
      { "Integrated Security", "True" }
    };
    connectionString = connectionBuilder.ConnectionString;
    providerName = "System.Data.SqlClient";
  }
  else
  {
    // Or use external configuration.
    DataConfigs dataConfigs = new DataConfigs();
    dataConfigs.LJCLoadData();
    dataConfig = dataConfigs.LJCGetByName("ConfigName");
    connectionString = dataConfig.GetConnectionString();
    providerName = dataConfig.GetProviderName();
  }

  // Create DataAccess object.
  dataAccess = new DataAccess(connectionString, providerName);

  // Method Tests.
  GetDataReader(dataAccess);
  GetDataTable(dataAccess);
  GetDataSet(dataAccess);
  GetProcedureDataTable(dataAccess);
  FillDataTable(dataAccess);
  GetSchemaOnly(dataAccess);
  ExecuteNonQuery(dataAccess);
  ExecuteScript(dataAccess);
  ExecuteScriptText(dataAccess);
}

IF NOT EXISTS(SELECT* FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = N'PersonTest')
BEGIN
CREATE TABLE[dbo].[PersonTest]
(
  [Id][int] IDENTITY(1,1) NOT NULL,
  [Name] [nvarchar] (60) NULL,
  [PrincipleFlag] [bit] NOT NULL,
  CONSTRAINT[PK_PersonTest]
  PRIMARY KEY CLUSTERED(
  [Id] ASC),
)
END
GO

Copyright © Lester J. Clark and Contributors.
Licensed under the MIT License.