An Intelligent Data Layer

• Download demo project - 39.7 KB
• Download source - 296.1 KB

Introduction

This intelligent data layer can communicate with a database and generate SQL queries on the fly. The real name of the intelligent data layer is CDataLayer Library. CDataLayer is a programmatic SQL generation library. The fundamental idea behind the intelligent data layer is to select, update, delete or insert data from a database without using .NET ADO objects (DataAdapters, DataCommandBuilders, etc.) or stored procedures. Objects that are using the intelligent data layer can update themselves by looking at their structure and the structure of the corresponding table in the database. Building projects with the intelligent data layer can be a miracle. On the other hand, you should keep in mind that easiness requires performance. Every time you create an object, you will send a select query like SELECT TOP 1 * FROM Categories to the database server. This query extracts the structure of the columns from the table. This information then can be used in generating SQL queries. CDatalayer Library can work with Microsoft SQL Server and Microsoft Office Access databases.

Using the Code

In order to use CDataLayer, you should add CDataLayer.dll as a reference. Alternatively, you can directly add the CDataLayer project into your solution. For the demo project, you need the SQLServer 2000 Northwind database sample. Change the column name ProductID in the Products table to ProductsID. In the CObjectInfoColllection class, primary columns are detected and not included in update queries.

// Primary Columns should not be updated. 
// In the update statements they generate errors.
// Detect Primary Columns by their names which have the 
// form of {TABLE_NAME}.ID
// Do not add primary columns into FieldValues collection.
if (iObjectInfo.Name == _memberName.Substring(
    _memberName.IndexOf(".")+1) + "ID")
continue;

Every table in your database should be presented with two classes in your code. For example, if you have a Customer table in your database design, you should write corresponding Customers and CustomersTable classes. It's crucial that every table in your database have a primary key. This key is used for selecting unique data from tables. Customers and CustomersTables are derived from the CMappedRow and CMappedTable classes, respectively. Both of these classes have abstract members:

public abstract string IdentityColumn {get;}
public abstract string ObjectName {get;}
public abstract DataBaseLanguage DBLanguage { get;}

So, you should implement them in your derived classes, as in the following:

// Overrides
public override string ObjectName
{
    get { return "Customers"; }
}
public override string IdentityColumn
{
    get { return "CustomerID"; }
}
public override DataBaseLanguage DBLanguage
{
    get { return CGlobal.DBLanguage; }
}

The CDataLayer.DataBaseLanguage enumeration contemporarily has two members: Turkish and USAEnglish. The intelligent data layer needs such information in order to correctly write date-time selections in SQL queries. ObjectName is the name of your table in the database and IdentityColumn is the name of the primary key column of the table. In the CGlobal.cs class, fill in appropriate values:

#region Private Members
    private static SqlConnection _SqlConnection;
    private static StringBuilder _SqlConnectionString;
    private static string _serverAddress = "localhost";
    private static string _databaseName = "Northwind";
    private static string _userID = "sa";
    private static string _password = "1234";
#endregion

If you want to work with a single row of data, you should create objects from {TABLENAME} classes. In order to work with data tables, create objects from {TABLENAMETable} classes. The intelligent data layer can work with both SqlConnection and OleDbConnection connection types. In the constructors of the CMappedRow and CMappedTable classes, you create an object from the CMappedRowFactory or CMappedTableFactory class. Both of these classes are derived from the CMappedBase class.

As you can see, these classes are rather complicated. You can obtain SQL query sentences for every select, insert, update and delete operation by calling GetQuery_{METHOD_NAME}. CMappedRow and CMappedTable have the following members:

Examples

Filling a data grid with the help of the intelligent data layer is very short in code.

ProductsTable _productsTable = new ProductsTable(CGlobal.MsSQLConnection);
grdProducts.DataSource = _productsTable.SelectFromTable(true);

If you want just the SQL query, you can call:

_productssTable.GetQuery_SelectFromTable(true);

If you have a list of primary key IDs, then you can retrieve related records of them. Also, you can select data and sort it with a list of specified columns and sorting directions, as in:

_MarkTable.SelectFromTable(new List<string>(new string[] { "Name" }), 
    new List<string>(new string[] { "ASC" }))

You can get a single record via the primary key by writing the following:

Products _product = new Products(CGlobal.MsSQLConnection, productID); 
// Create a product object from the datarow with specified productID
ucProducts.txtCategoryID.Text = _product.CategoryID.ToString(); 
// Get the CategoryID of the product
ucProducts.txtProductName.Text = _product.ProductName; 
// Get the ProductName of the product
ucProducts.txtQuantityPerUnit.Text = _product.QuantityPerUnit; 
// Get the QuantityPerUnit of the product

Selecting distinct records -- for example, in order to fill combo boxes -- can be done with the {TABLENAMETable}.SelectDistinctFromTable() method. While updating your record, the intelligent data layer looks at all of your object's properties and then writes the appropriate query.

While inserting a new record, only the properties that were filled are added into the SQL script. You can test this with the demo project. After inserting a new record into the database, another SQL query, Select @@IDENTITY;, is executed. We use this for immediately getting the primary key ID number of the recently added record.

You can generate search queries by just giving dummy values to the properties of an object. For example, you can write a search routine like the following:

private void SearchInTable()
{
    _productsLoading = true;
    _productsTable = new ProductsTable(CGlobal.MsSQLConnection);
    _productsTable.ProductName = txtProductNameSearch.Text.Trim();
    _productsTable.UnitPrice = 
        CUtility.ConvertToDecimal(txtUnitPrice.Text.Trim());
    grdProducts.DataSource = 
        _productsTable.SearchInTable(SqlSearchType.AND_LIKE);
    txtSQLQuery.Text = 
        _productsTable.GetQuery_SearchInTable(SqlSearchType.AND_LIKE);
    _productsLoading = false;
}

The corresponding SQL query with the ProductName search keyword "on" and UnitPrice "10" will be:

SELECT ProductName, SupplierID, CategoryID, 
    QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, 
ReorderLevel, Discontinued FROM Products WHERE 
(ProductName LIKE '%on%' OR ProductName IS NULL) AND (
    UnitPrice=10 OR UnitPrice IS NULL);

You can manipulate your search key with four logic cases. You can do this by specifying the SqlSearchType enumeration parameter.

SqlSearchType.AND_EXACT
SqlSearchType.AND_LIKE
SqlSearchType.OR_EXACT
SqlSearchType.OR_LIKE

In order to delete a single record, use the {TABLENAME}.DeleteRow() method. Rows with specified ID numbers can be deleted with the {TABLENAMETable}.DeleteFromTable({PrimaryID list}) method, as in:

ArrayList _Ids = new ArrayList();
_Ids.AddRange(new int[] { 1, 2 });
_productsTable.DeleteFromTable(_Ids);

The CUtility class in CDatalayer has a few useful functions and several data type conversion functions. The Cocnstans.cs class includes all enumerations. All SQL queries are constructed in the CSql class. The more you play with your {TABLENAME} and {TABLENAME}Table objects' methods, the more you'll understand CDataLayer and its capabilities.

Have a nice time with this intelligent data layer.

Future

I will probably not continue developing this intelligent data layer anymore. Any good improvements to my classes and demo project will be welcomed.

Note

I would like to be aware of the usage of my library. If you will use my library, please send me an informational email. I want this information just for motivating myself and getting statistical knowledge. My email is:

yeniferhat

yahoo.com

History

• 19 June, 2007 -- Original version posted
• 7 August, 2007 -- Article and downloads updated
• 9 August, 2007 -- Updated Introduction and Description sections of article