/ Assembly List / LJCDBDataAccess / DbDataAccess

Namespace - LJCDBDataAccess


Syntax

C#
public class DbDataAccess

Implements a Message Based data access class. (DE)

Remarks

The Message Data Access class contains methods for performing common database functions using DbRequest request objects and returning DbResult objects.

The single public method is Execute(). The Execute() method calls the supporting private data methods based on the DbRequest query request type. It returns the DbResult object created in the private data methods.

The DbResult object is created from the DataTable using the ResultCommon static GetResultColumns() and GetResultRecords() functions.

The private data methods create a SQL statement based on the data from the DbRequest object. The SQL statement is executed and the SQL result is transformed into a DbResult object.

The test code is found in project LJCDBServiceLib\TestDbDataAccess.

Public Methods
#ctor Initializes an object instance.
#ctor Initializes an object instance.
Execute Executes the specified DbRequest definition.
3 Methods

Private Methods
#ctor Initializes an object instance with the supplied values.
Add Inserts a record with the specified columns. (E)
CreateResult Creates the result object with common values.
Delete Deletes a record. (E)
ExecuteClientSql Executes a "Non-Query" client SQL statement. (E)
GetConfigValues Sets the DataConfig values.
Load Retrieves multiple data rows. (E)
LoadClientSql Executes a "Load" client SQL statement. (E)
Retrieve Retrieves the data row values. (E)
RetrieveClientSql Executes a "Retrieve" client SQL statement. (E)
SelectProcedure Retrieves the data row values from a Stored Procedure.
TableNames Retrieves the table names for the specified database.
Update Updates a database record. (E)
13 Methods

Properties
DatabaseName Gets or sets the Database name.
DbRequest Gets or sets the DbRequest object.

Example

The following example shows how to directly use the DbDataAccess object for database access. Applications should generally encapsulate much of this code in a table specific Manager class to make application programming easier and cleaner.

A Manager class is the client code that creates a table specific DbRequest object. It passes the DbRequest object to the DbDataAccess.Execute() method.

A common Manager class LJCDbClientLib.DataManager is available to simplify interacting with the data access objects.
It can directly call DbDataAccess or LJCDBServiceLib.DbService and returns data as DbResult objects.
It can also access a remote DbService object using a Service Host and a client proxy. The LJCDBServiceHost.DBWindowsService is a host that is provided to run as a windows service.

The test code is found in project LJCDBServiceLib\TestDbDataAccess.

C#
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

using System;
using System.Collections.Generic;
using System.Data.Common;
using LJCNet.Common;
using LJCDBMessage;
using LJCDBDataAccessLib;

// A Person Data Record class.
public class Person
{
  public Int32 ID { get; set; }
  public string Name { get; set; }
  public bool PrincipleFlag { get; set; }
}
public class Persons : List<Person> { }

// Test DbDataAccess
private static void TestDbDataAccess()
{
  DbConnectionStringBuilder connectionBuilder;
  DbDataAccess dbDataAccess;

  string databaseName = "DatabaseName";
  connectionBuilder = new DbConnectionStringBuilder()
  {
    { "Data Source", "DataServiceName" },
    { "Initial Catalog", databaseName },
    { "Integrated Security", "True" }
  };
  string connectionString = connectionBuilder.ConnectionString;
  string providerName = "System.Data.SqlClient";
  dbDataAccess = new DbDataAccess(connectionString, providerName
    , databaseName);

  // Get the table data definition.
  DbColumns dataDefinition = SchemaOnly(dbDataAccess);
  DbColumn dbColumn = dataDefinition.LJCSearchByName("Id");
  dbColumn.PropertyName = "ID";

  Add(dbDataAccess, dataDefinition);
  Person person = Retrieve(dbDataAccess, dataDefinition);
  Persons persons = Load(dbDataAccess, dataDefinition);
  Update(dbDataAccess, dataDefinition);
  persons = Load(dbDataAccess, dataDefinition);
  Delete(dbDataAccess, dataDefinition);
  persons = Load(dbDataAccess, dataDefinition);

  Add(dbDataAccess, dataDefinition);
  person = RetrieveClientSql(dbDataAccess, dataDefinition);
  persons = LoadClientSql(dbDataAccess, dataDefinition);
  ExecuteClientSql(dbDataAccess, dataDefinition);
  persons = LoadClientSql(dbDataAccess, dataDefinition);
  Delete(dbDataAccess, dataDefinition);
  persons = LoadClientSql(dbDataAccess, dataDefinition);
}

// Create the Data Definition from the table schema.
private static DbColumns SchemaOnly(DbDataAccess dbDataAccess)
{
  DbColumns retValue = null;

  DbRequest dbRequest = new DbRequest()
  {
    QueryType = QueryType.SchemaOnly.ToString(),
    TableName = "PersonTest"
  };
  DbResult dbResult = dbDataAccess.Execute(dbRequest);
  retValue = dbResult.DbRecordColumns;
  return retValue;
}

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