ADataSet joins

In order to use joins you will be using an auxiliary class called AJoin which will be added to the ADataSet definition using the alJoins property. The joins are built using ATables and AForeignKey objects.

AJoin class

The AJoin class will be constructed using the ATable and AForeignKey. Also you will be able to specify join option such as "INNER JOIN", "RIGHT/LEFT OUTER JOIN", or "FULL JOIN".
For example if you want to create a dataset to bring customers and their orders you can use the ACustomer and AOrder.fkCust.


        Customer vCustomer = new Customer();
        Order vOrder = new Order();

        ADataSet vdsJoinTest = new ADataSet(vCustomer);
        vdsJoinTest.alJoins.Add(new AJoin(vOrder, vOrder.fkCust));
        vdsJoinTest.alAddCols(vCustomer);
        vdsJoinTest.alAddCols(vOrder);
        vdsJoinTest.alFill();

        foreach (DataRow vRow in vdsJoinTest.alGetCurrentRows())
        {
            vdsJoinTest.alGetValues(vRow, vCustomer, vOrder);
            Console.WriteLine(vCustomer.GetFormattedValues());
            Console.WriteLine(vOrder.GetFormattedValues());
        }

The AJoin requires the table and the foreign key to link to one of the tables in the dataset.

The sql for the example bellow is:

SELECT [CUSTOMER].[ID], [CUSTOMER].[NAME], [CUSTOMER].[MAXCREDIT] , [CUSTOMER].[ISACTIVE], [CUSTOMER].[CUSTSINCE],
[ORDER].[NUMBER], [ORDER].[CUSTID], [ORDER].[ORDDATE], [ORDER].[ADDRESS], [ORDER].[CITY], [ORDER].[STATE], [ORDER].[ZIP]
FROM [CUSTOMER]
INNER JOIN [ORDER] ON [ORDER].[CUSTID] = [CUSTOMER].[ID]

AJoin option

The join supports the following options:

INNER JOIN

LEFT OUTER JOIN

RIGHT OUTER JOIN

FULL OUTER JOIN

You can specify the option by passing a third parameter to the AJoin class. And you can use the predefined parameters in the AJoin class. The INNER JOIN is the default, which is the most common case.

Bellow is the example to use the LEFT OUTER JOIN that will bring all customers even if they don't have associated orders.


        vdsJoinTest.alJoins.Add(new AJoin(vOrder, vOrder.fkCust, AJoin.Option.LEFT_OUTER_JOIN));


That's it for joins, next we will take a look at how ADataSet handle DataRows.

ADataSet class

The ADataSet is one of the main classes of our sql framework, and the most used class after ATable. It encapsulates the DataSet class and have a couple of auxiliary methods to facilitate the access to the data, specially when dealing with joins.

ADataSet class

The framework class inherits directly from DataSet .net class, and we have a couple of methods starting with "al". Also we have internal variables to keep the dataset structure such as tables, joins, where, order by and columns.
Let's start with a simple dataset.

Simple ADataSet

Here we have an example to bring all customer records, and a line by line explanation:

        Customer vCustomer = new Customer();
        
        ADataSet vdsSimple = new ADataSet(vCustomer);
        vdsSimple.alAddCols(vCustomer);
        vdsSimple.alFill();

        foreach (DataRow vRow in vdsSimple.alGetCurrentRows())
        {
            vdsSimple.alGetValues(vRow, vCustomer);
            Console.WriteLine(vCustomer.GetFormattedValues());
        }

ADataSet vdsSimple = new ADataSet(vCustomer);

The vCustomer will be the main table, because we can have joins and those will be the secondary tables. It is just a convention.
The next step it is to add join tables, but for this sample we don't have any. Later we'll talk about joins. What we going to do now is to add the column defintion to our dataset.

vdsSimple.alAddCols(vCustomer);

We use the method alAddCols to add all customer columns, but we can just add the columns we need to bring from the database, alAddCols is just a shortcut. In this sample we are not using any "where" and "order by", so we just populate the dataset with our custom Fill command.

vdsSimple.alFill();

This method will do everything. Will create an internal DataTable object using the column definitions and also populate it building a sql command based on the structure. After that you going to use methods to add, change and delete DataRows. In order to update the database you have to use the ATable methods. The ADataSet is essentially readonly. We'll see later how to update the database after dataset changes.

foreach (DataRow vRow in vdsSimple.alGetCurrentRows())

Here we are using the method alGetCurrentRows to access the collection of DataRows that were previously populated by alFill. Now what we have to do is transfer that data to a ATable record. Of course you have to make sure you transfer to the correct ATable record.

vdsSimple.alGetValues(vRow, vCustomer);

The alGetValues method is responsible to move data from a DataRow to a ATable. It will use the ADataSet definition to map the DataColumns to the ACol objects.

This sample ADataSet is exactly the same as using the ATable.Select and ATable.NextRecord. But the advantage is that we can use joins, have control on changing the data, and many other features, specially when dealing with large amount of data.
Next we will take a look at joins.

AlSql - Select

Now we are going to take a look on how to retrieve db records.
Using the framework we can use one of the following methods:

ATable.Find: when reading one single record.
ATable.Select: when reading a group of records, about 50 records or less.
ADataSet class: when reading more than 50 records or when you need to join to other tables.

We will start with ATable.Select that is used internally for ATable.Find. The ATable will read the records using AWhere and AOrderBy. Both are optional. After selecting the records we can use the method ATable.NextRecord in a loop to process the records.

Here are some samples of the Select method:

using System;
using System.Collections.Generic;
using System.Text;
using AlSql;

namespace AlTest.Tests
{
    public class TestSelect
    {
        public static void Run()
        {
            Customer vCustomer = new Customer();

            // Select Sample 1: read all records
            vCustomer.Select(null, null);
            while (vCustomer.NextRecord())
            {
                Console.WriteLine("Customer: " + vCustomer.cId.Value + ", " + vCustomer.cName.Value);
            }

            // Select Sample 2: simple where clause
            vCustomer.Select(new AWhere(AWhere.Condition(vCustomer.cName, AWhere.EQ, "John Smith")), null);
            while (vCustomer.NextRecord())
            {
                Console.WriteLine("Customer: " + vCustomer.cId.Value + ", " + vCustomer.cName.Value);
            }

            // Select Sample 3: complex where expression
            AWhere vWhere = new AWhere();
            vWhere.Items.Add(AWhere.OpenParenthesis);
            vWhere.Items.Add(AWhere.Condition(vCustomer.cId, AWhere.EQ, 1));
            vWhere.Items.Add(AWhere.OR);
            vWhere.Items.Add(AWhere.Condition(vCustomer.cName, AWhere.LIKE, "N%"));
            vWhere.Items.Add(AWhere.CloseParenthesis);
            vWhere.Items.Add(AWhere.AND);
            vWhere.Items.Add(AWhere.IsNotNull(vCustomer.cName));

            vCustomer.Select(vWhere, null);
            while (vCustomer.NextRecord())
            {
                Console.WriteLine("Customer: " + vCustomer.cId.Value + ", " + vCustomer.cName.Value);
            }

            // Select Sample 4: one column Order By
            AWhere vWhere1 = new AWhere();
            vWhere1.Items.Add(AWhere.Condition(vCustomer.cMaxCredit, AWhere.GE, 100));
            vWhere1.Items.Add(AWhere.AND);
            vWhere1.Items.Add(AWhere.Condition(vCustomer.cMaxCredit, AWhere.LE, 200));

            vCustomer.Select(vWhere1, new AOrderBy(vCustomer.cName));
            while (vCustomer.NextRecord())
            {
                Console.WriteLine("Customer: " + vCustomer.cId.Value + ", " + vCustomer.cName.Value);
            }

            // Select Sample 5: order by with descending
            AOrderBy vOrderBy = new AOrderBy();
            vOrderBy.AddItem(vCustomer.cCustSince, false);
            vOrderBy.AddItem(vCustomer.cName);
            vCustomer.Select(vWhere1, vOrderBy);
            while (vCustomer.NextRecord())
            {
                Console.WriteLine("Customer: " + vCustomer.cId.Value + ", " + vCustomer.cName.Value);
            }
        }
    }
}

Select, NextRecord and CloseSelect methods
In the examples above you notice that NextRecord will return true while there's an available record, by using the loop. The ATable.Select uses an internal DataReader to keep track of the record selection. If you need to get out of the loop before last record, you have to close the DataReader by calling the method CloseSelect.
For example:

    Customer vCustomer = new Customer();
    vCustomer.Select(null, null);
    while (vCustomer.NextRecord())
    {
        Console.WriteLine("Customer: " + vCustomer.cId.Value + ", " + vCustomer.cName.Value);
        if (vCustomer.cId.IntValue == 8)
        {
            vCustomer.CloseSelect();
            break;
        }
    }


Find method
Find method is just an utility method when you know you will read just one record. It uses the AWhere class to indicate the selection. It will return an exception if there's more than one record. Another utility method we may create is FindFirst where it will return the first record for the selection.

ATable Select related methods

Bellow we have the code for the select methods in the table. Don't worry about the method StartChangeValueControl(). We will be explaining when we look at Update method.
In the next part we will be looking at ADataSet class.

        /// <summary>
        /// Internal DataReader to be used by Select/NextRecord methods.
        /// </summary>
        private IDataReader mSelectedRecords = null;
        
        /// <summary>
        /// Select records and start an internal DataReader to be accessed by NextRecord.
        /// Will execute the SQL SELECT command.
        /// </summary>
        public void Select(AWhere pWhere, AOrderBy pOrderBy)
        {
            StringBuilder vSql = new StringBuilder("SELECT ");

            String vDelimiter = "";
            foreach (ACol vCol in GetColumns())
            {
                vSql.Append(vDelimiter + vCol.SqlName());
                vDelimiter = ", ";
            }

            vSql.Append(" FROM [" + Name + "] WITH (NOLOCK)");

            if (pWhere != null)
                vSql.Append(" WHERE" + pWhere.SqlDef());

            if (pOrderBy != null)
                vSql.Append(pOrderBy.SqlDef());

            mSelectedRecords = DB.ExecuteQuery(vSql.ToString());
        }

        /// <summary>
        /// Go to next selected record. Need to execute the Select in order to create the record set.
        /// </summary>
        /// <returns>true - while there's a next record.</returns>
        public bool NextRecord()
        {
            ClearValues();

            if (mSelectedRecords == null)
                return false;

            if (mSelectedRecords.Read() == false)
            {
                CloseSelect();
                return false;
            }

            foreach (ACol vCol in GetColumns())
                vCol.GetValueFromDataReader(mSelectedRecords);

            StartChangeValueControl();

            return true;
        }

        /// <summary>
        /// Close the internal data reader created by Select.
        /// Is called automatically when NextRecord reaches the last record or can be called manually by user if necessary.
        /// </summary>
        public void CloseSelect()
        {
            mSelectedRecords.Close();
            mSelectedRecords = null;
        }
        
        /// <summary>
        /// Find one record.
        /// </summary>
        /// <param name="pWhere">Where clause to locate the record.</param>
        /// <returns>true if record is found</returns>
        public bool Find(AWhere pWhere)
        {
            Select(pWhere, null);
            if (NextRecord() == false)
                return false;
            if (mSelectedRecords.Read())
            {
                CloseSelect();
                throw new Exception("More than one record found for " + pWhere.SqlDef());
            }
            CloseSelect();
            return true;
        }

AlSql - AWhere and AOrderBy

Now we are going to take a look on how to retrieve db records using the framework classes.
But first, we need to look at the auxiliary classes AWhere and AOrderBy. Those classes will help to build the sql select commands.

AWhere

The AWhere class will represent the sql WHERE clause. Internally it will contains a list of items to build the WHERE clause. The items are the conditions you want for your query, such as "CUSTOMER.ID = 100".
You will be able to build complex queries by using the sql components like AND, OR, parenthesis, LIKE, IN, etc.
We have an internal class Item to represent each condition, and Operator to represent the operators =, <>, >= etc.
Also we have an auxiliary method called Condition, to help the creation of Item, and you will be using it most of the time. We need one condition method for each data type we support so we don't have to "cast" the values we will compare.
Bellow are some samples for the Customer table. Later will be using AWhere on the ATable.Select method.


            Customer vCustomer = new Customer();

            // example 1
            AWhere vWhere1 = new AWhere(AWhere.Condition(vCustomer.cName, AWhere.EQ, "John Smith"));
            Console.WriteLine(vWhere1.SqlDef());

            OUTPUT: [CUSTOMER].[NAME] = 'John Smith'

            // example 2
            AWhere vWhere2 = new AWhere();
            vWhere2.Items.Add(AWhere.Condition(vCustomer.cMaxCredit, AWhere.GE, 100));
            vWhere2.Items.Add(AWhere.AND); // this is optional.
            vWhere2.Items.Add(AWhere.Condition(vCustomer.cMaxCredit, AWhere.LE, 200));
            Console.WriteLine(vWhere2.SqlDef());

            OUTPUT: [CUSTOMER].[MAXCREDIT] >= 100 AND [CUSTOMER].[MAXCREDIT] <= 200

            // example 3
            AWhere vWhere3 = new AWhere();
            vWhere3.Items.Add(AWhere.OpenParenthesis);
            vWhere3.Items.Add(AWhere.Condition(vCustomer.cId, AWhere.EQ, 1));
            vWhere3.Items.Add(AWhere.OR);
            vWhere3.Items.Add(AWhere.Condition(vCustomer.cName, AWhere.LIKE, "N%"));
            vWhere3.Items.Add(AWhere.CloseParenthesis);
            vWhere3.Items.Add(AWhere.IsNotNull(vCustomer.cName));
            Console.WriteLine(vWhere3.SqlDef());

            OUTPUT: ([CUSTOMER].[ID] = 1 OR [CUSTOMER].[NAME] LIKE 'N%') AND [CUSTOMER].[NAME] IS NOT NULL

AOrderBy

The AOrderBy class will represent the sql ORDER BY to be used in the ATable.Select method and in the ADataSet class. You will add columns and you'll be able to use ASCENDING or DESCENDING options. Here is an example of creating the AOrderBy:


            AOrderBy o = new AOrderBy();
            o.AddItem(c.cCustSince, false);
            o.AddItem(c.cName);


See the complete classes bellow. Next on the list we'll take a look at ATable.Select.


//
// AWhere.cs
//
using System;
using System.Collections.Generic;
using System.Text;

namespace AlSql
{
    /// <summary>
    /// Represents the SQL WHERE clause.
    /// </summary>
    public class AWhere
    {
        private List<AWhere.Item> mItemList = new List<AWhere.Item>();

        public AWhere()
        {
        }

        public AWhere(AWhere.Item pItem)
        {
            Items.Add(pItem);
        }

        public List<AWhere.Item> Items
        {
            get
            {
                return mItemList;
            }
        }

        public string SqlDef()
        {
            StringBuilder vSql = new StringBuilder("");
            AWhere.Item vPrevItem = null;

            foreach (AWhere.Item vItem in mItemList)
            {
                if (vItem != AWhere.AND && vItem != AWhere.OR && vItem != AWhere.CloseParenthesis)
                {
                    if (vPrevItem != null && vPrevItem != AWhere.AND && vPrevItem != AWhere.OR && vPrevItem != AWhere.OpenParenthesis)
                        vSql.Append(" AND");
                }
                vSql.Append(" " + vItem.SqlDef());
                vPrevItem = vItem;
            }

            return vSql.ToString();
        }

        public class Item
        {
            private string mItem;

            public Item(string pItem)
            {
                mItem = pItem;
            }

            public string SqlDef()
            {
                return mItem;
            }
        }

        public class Operator
        {
            private string mOperator;

            public Operator(string pOperator)
            {
                mOperator = pOperator;
            }

            public string SqlDef()
            {
                return mOperator;
            }
        }

        public static AWhere.Item Condition(ACol pColumn, AWhere.Operator pOperator, string pValue)
        {
            return new AWhere.Item(pColumn.SqlName() + " " + pOperator.SqlDef() + " '" + pValue.Replace("'", "''") + "'");
        }
        public static AWhere.Item Condition(AChar pColumn, AWhere.Operator pOperator, string pValue)
        {
            return new AWhere.Item(pColumn.SqlName() + " " + pOperator.SqlDef() + " '" + pValue.Replace("'", "''") + "'");
        }
        public static AWhere.Item Condition(AInt pColumn, AWhere.Operator pOperator, int pValue)
        {
            return new AWhere.Item(pColumn.SqlName() + " " + pOperator.SqlDef() + " " + pValue);
        }
        public static AWhere.Item Condition(ADec pColumn, AWhere.Operator pOperator, decimal pValue)
        {
            return new AWhere.Item(pColumn.SqlName() + " " + pOperator.SqlDef() + " " + pValue);
        }
        public static AWhere.Item Condition(ADate pColumn, AWhere.Operator pOperator, DateTime pValue)
        {
            return new AWhere.Item(pColumn.SqlName() + " " + pOperator.SqlDef() + " " + pValue.ToString());
        }
        public static AWhere.Item Condition(ABool pColumn, AWhere.Operator pOperator, bool pValue)
        {
            return new AWhere.Item(pColumn.SqlName() + " " + pOperator.SqlDef() + " " + (pValue == true ? "1" : "0"));
        }
        public static AWhere.Item IsNull(ACol pColumn)
        {
            return new AWhere.Item(pColumn.SqlName() + " IS NULL");
        }
        public static AWhere.Item IsNotNull(ACol pColumn)
        {
            return new AWhere.Item(pColumn.SqlName() + " IS NOT NULL");
        }

        public static readonly AWhere.Item OpenParenthesis = new AWhere.Item("(");
        public static readonly AWhere.Item CloseParenthesis = new AWhere.Item(")");
        public static readonly AWhere.Item AND = new AWhere.Item("AND");
        public static readonly AWhere.Item OR = new AWhere.Item("OR");

        public static readonly AWhere.Operator EQ = new AWhere.Operator("=");
        public static readonly AWhere.Operator NE = new AWhere.Operator("<>");
        public static readonly AWhere.Operator GT = new AWhere.Operator(">");
        public static readonly AWhere.Operator LT = new AWhere.Operator("<");
        public static readonly AWhere.Operator GE = new AWhere.Operator(">=");
        public static readonly AWhere.Operator LE = new AWhere.Operator("<=");
        public static readonly AWhere.Operator LIKE = new AWhere.Operator("LIKE");

    }
}



//
// AOrderBy
//
using System;
using System.Collections.Generic;
using System.Text;

namespace AlSql
{
    public class AOrderBy
    {
        private List<string> mItems = new List<string>();

        public AOrderBy()
        {
        }

        public AOrderBy(ACol pCol)
        {
            AddItem(pCol, true);
        }

        public AOrderBy(ACol pCol, bool pAscending)
        {
            AddItem(pCol, pAscending);
        }

        public void AddItem(ACol pCol)
        {
            AddItem(pCol, true);
        }

        public void AddItem(ACol pCol, bool pAscending)
        {
            mItems.Add(pCol.SqlName() + (pAscending ? "" : " DESC"));
        }

        public int GetItemCount()
        {
            return mItems.Count;
        }
            
        public string SqlDef()
        {
            StringBuilder vSql = new StringBuilder(" ORDER BY ");
            string vDelimiter = "";
            foreach (string vItem in mItems)
            {
                vSql.Append(vDelimiter + vItem);
                vDelimiter = ", ";
            }

            return vSql.ToString();
        }

    }
}

AlSql - Sample tables with Foreign Key and Index

Bellow is the code for the application tables with foreign key and index declarations. Those are the complete tables used to show the framework.

Note that the foreign keys and indexes need to be initialized in the table's constructor. It would be nice to have the initialization together with the member variable declaration, but there is some compiler restriction due to column references. Anyway, at the constructor works fine.

Customer

using AlSql;

namespace AlTest
{
    public class Customer : ATable
    {
        public readonly AInt cId = new AInt("ID");
        public readonly AChar cName = new AChar("NAME", 50);
        public readonly ADec cMaxCredit = new ADec("MAXCREDIT", "###,###,##0.00");
        public readonly ABool cIsActive = new ABool("ISACTIVE");
        public readonly ADate cCustSince = new ADate("CUSTSINCE");

        public readonly AIndex iMain;
        public readonly AIndex iName;
        
        public Customer() : base(AppCfg.MainDB, "CUSTOMER")
        {
            // Indexes.
            iMain = new AIndex(this, "MAIN", true, true, cId);
            iName = new AIndex(this, "NAME", false, false, cName);
        }
    }
}

Category

using AlSql;

namespace AlTest
{
    public class Category : ATable
    {
        public readonly AInt cId = new AInt("ID");
        public readonly AChar cDesc = new AChar("DESCRIPT", 50);

        public readonly AIndex iMain;

        public Category() : base(AppCfg.MainDB, "CATEGORY")
        {
            iMain = new AIndex(this, "MAIN", true, true, cId);
        }
    }
}

Product

using AlSql;

namespace AlTest
{
    public class Product : ATable
    {
        public readonly AInt cId = new AInt("ID");
        public readonly AChar cDesc = new AChar("DESCRIPT", 50);
        public readonly AChar cUnitType = new AChar("UNITTYPE", 20);
        public readonly ADec cStockQty = new ADec("STOCKQTY");
        public readonly ABool cIsActive = new ABool("ISACTIVE");
        public readonly AInt cCategID = new AInt("CATEGID");

        public readonly AIndex iMain;
        public readonly AIndex iDesc;

        public readonly AForeignKey rCateg;

        public Product() : base(AppCfg.MainDB, "PRODUCT")
        {
            // Indexes.
            iMain = new AIndex(this, "MAIN", true, true, cId);
            iDesc = new AIndex(this, "DESC", false, false, cDesc);
            // Foreign Key
            rCateg = new Category().CreateForeignKey(this, AForeignKey.Option.NOCHECK, false, cCategID);
        }
    }
}

Order

using AlSql;

namespace AlTest
{
    public class Order : ATable
    {
        public readonly AInt cNumber = new AInt("NUMBER");
        public readonly AInt cCustId = new AInt("CUSTID");
        public readonly ADate cOrdDate = new ADate("ORDDATE");
        public readonly AChar cAddress = new AChar("ADDRESS", 100);
        public readonly AChar cCity = new AChar("CITY", 50);
        public readonly AChar cState = new AChar("STATE", 2);
        public readonly AChar cZIP = new AChar("ZIP", 10);

        public readonly AIndex iMain;
        public readonly AIndex iCust;

        public readonly AForeignKey fkCust;

        public Order() : base(AppCfg.MainDB, "ORDER")
        {
            iMain = new AIndex(this, "MAIN", true, true, cNumber);
            iCust = new AIndex(this, "CUST", false, true, cCustId, cNumber);
            fkCust = new Customer().CreateForeignKey(this, AForeignKey.Option.CHECK, false, cCustId);
        }

        public void Sample()
        {
            Order vOrder = new Order();
            Customer vCustomer = new Customer();
            AColPair[] vColPair = new AColPair[1] {new AColPair(vCustomer.cId, vOrder.cCustId)};
            AForeignKey vOrderCustomer = new AForeignKey(vCustomer, vOrder, AForeignKey.Option.CHECK, false, vColPair);
        }
    }
}

Order Line

using AlSql;

namespace AlTest
{
    public class OrderLine : ATable
    {
        public readonly AInt cOrderNumber = new AInt("ORDERNUMBER");
        public readonly AInt cSequence = new AInt("SEQUENCE");
        public readonly AInt cProdID = new AInt("PRODID");
        public readonly ADec cQty = new ADec("QTY");

        public readonly AIndex iMain;

        public readonly AForeignKey fkOrder;
        public readonly AForeignKey fkProduct;

        public OrderLine() : base(AppCfg.MainDB, "ORDERLINE")
        {
            iMain = new AIndex(this, "MAIN", true, true, cOrderNumber, cProdID);
            fkOrder = new Order().CreateForeignKey(this, AForeignKey.Option.CHECK, true, cOrderNumber);
            fkProduct = new Product().CreateForeignKey(this, AForeignKey.Option.CHECK, false, cProdID);
        }
    }
}

AlSql - Foreign Keys

The AForeignKey class will allow to establish relationships between tables and enforce relational integrity. For example, when we add a new record to the Order table, the customer record must exist. We are going to create a foreign key on the Order table using the customer id column. We can also have optional relationships, which will be useful for join queries.
The table relationships will also be used to bring associated records when using the ADataSet class.

Parent and child tables
The foreign key will always be added to the child table. For example, in the Order Customer association, the Customer is the parent table and the Order is the child table. The Order will have an AForeignKey member to make the association to the Customer.

AForeignKey class
This class will allow to create foreign keys by using the following sql command:
ALTER TABLE [ChildTable] WITH NOCHECK/CHECK
ADD CONSTRAINT [FullName]
FOREIGN KEY([ChildColumns])
REFERENCES [ParentTable] ([ParentColumn])

Here is an example to create the association between Order and customers:

            Order vOrder = new Order();
            Customer vCustomer = new Customer();
            AColPair[] vColPair = new AColPair[1] {new AColPair(vCustomer.cId, vOrder.cCustId)};
            AForeignKey vOrderCustomer = new AForeignKey(vCustomer, vOrder, AForeignKey.Option.CHECK, false, vColPair);


After declaration we can use the method GetSqlForeignKey to get the sql command in order to create it on database. Later we will show how to use the foreign key in datasets to execute join queries. Next we will show the complete table declarations and start using the other sql commands of ATable class.

using System;
using System.Collections.Generic;
using System.Text;

namespace AlSql
{
    public class AForeignKey
    {
        public class Option
        {
            public static Option CHECK = new Option("CHECK");
            public static Option NOCHECK = new Option("NOCHECK");

            private string mSqlValue;

            private Option(string pSqlValue)
            {
                mSqlValue = pSqlValue;
            }

            public string SqlValue()
            {
                return mSqlValue;
            }
        }

        /// <summary>
        /// Parent table of foreign key.
        /// </summary>
        private ATable mParentTable;
        /// <summary>
        /// Child table of foreign key.
        /// </summary>
        private ATable mChildTable;
        /// <summary>
        /// Column pairs to construct the foreign key.
        /// </summary>
        private List<AColPair> mCols = new List<AColPair>();
        /// <summary>
        /// Name complement.
        /// </summary>
        private string mName = "";
        /// <summary>
        /// Indicates if generates the cascade delete.
        /// </summary>
        private bool mCascade = false;
        /// <summary>
        /// Indicates if generates the check.
        /// </summary>
        private AForeignKey.Option mCheck = AForeignKey.Option.CHECK;

        /// <summary>
        /// Foreign key constructor.
        /// </summary>
        /// <param name="pParent">Parent table.</param>
        /// <param name="pChild">Child table.</param>
        /// <param name="pCheck">Indicates check constraint.</param>
        /// <param name="pCascade">Indicates Cascade constraint.</param>
        /// <param name="pCols">Parent/Child column pairs.</param>
        public AForeignKey(ATable pParent, ATable pChild, AForeignKey.Option pCheck, bool pCascade, params AColPair[] pCols)
        {
            mParentTable = pParent;
            mChildTable = pChild;
            mCheck = pCheck;
            mCascade = pCascade;
            foreach (AColPair vColPair in pCols)
                mCols.Add(vColPair);
        }

        /// <summary>
        /// Name complement.
        /// </summary>
        public string Name
        {
            get { return mName; }
            set { mName = value; }
        }

        /// <summary>
        /// Parent table.
        /// </summary>
        public ATable ParentTable
        {
            get { return mParentTable;}
        }

        /// <summary>
        /// Child table.
        /// </summary>
        public ATable ChildTable
        {
            get { return mChildTable; }
        }

        /// <summary>
        /// Indicates if it is delete cascade.
        /// </summary>
        public bool Cascade
        {
            get { return mCascade; }
            set { mCascade = value; }
        }

        /// <summary>
        /// Indicates check option.
        /// </summary>
        public AForeignKey.Option Check
        {
            get { return mCheck; }
            set { mCheck = value; }
        }

        /// <summary>
        /// Columns pair.
        /// </summary>
        public List<AColPair> Cols
        {
            get
            {
                return mCols;
            }
        }

        /// <summary>
        /// Fullname generated by framework.
        /// </summary>
        /// <returns>Fullname.</returns>
        public string FullName()
        {
            string mFullName = "FK_" + mChildTable.Name + "_" + mParentTable.Name;
            if (mName != "")
                mFullName += "_" + mName;
            return mFullName;
        }

        /// <summary>
        /// Generates the sql create command to create the Foreign Key.
        /// ALTER TABLE [ChildTable] WITH NOCHECK
        /// ADD CONSTRAINT [FullName]
        /// FOREIGN KEY([ChildColumns])
        /// REFERENCES [ParentTable] ([ParentColumn])
        /// </summary>
        /// <returns>Sql command.</returns>
        public string GetSqlForeignKey()
        {
            StringBuilder vSql = new StringBuilder("ALTER TABLE [" + mChildTable.Name + "]");
            vSql.Append(" WITH " + mCheck.SqlValue());
            vSql.Append(" ADD CONSTRAINT " + FullName());
            string vColListParent = "";
            string vColListChild = "";
            string vDelim = "";
            foreach (AColPair vColPair in mCols)
            {
                vColListParent += vDelim + vColPair.Col1.Name;
                vColListChild += vDelim + vColPair.Col2.Name;
                vDelim = ", ";
            }
            vSql.Append(" FOREIGN KEY (" + vColListChild + ")");
            vSql.Append(" REFERENCES [" + mParentTable.Name + "]");
            vSql.Append(" (" + vColListParent + ")");
            if (mCascade)
                vSql.Append(" ON DELETE CASCADE");
            // Apply the check/no check for the foreign key.
            // ALTER TABLE [ChildTable] NOCHECK CONSTRAINT [ForeignKey]
            vSql.Append("; ALTER TABLE [" + mChildTable.Name + "]");
            vSql.Append(" " + mCheck.SqlValue());
            vSql.Append(" CONSTRAINT " + FullName());

            return vSql.ToString();
        }
    }
}

We made use of the class AColPair. This class is usefull when we need to associate columns from two different tables like in the foreign key.

using System;
using System.Collections.Generic;
using System.Text;

namespace AlSql
{
    public class AColPair
    {
        private ACol mCol1;
        private ACol mCol2;

        public AColPair(ACol pCol1, ACol pCol2)
        {
            mCol1 = pCol1;
            mCol2 = pCol2;
        }

        public ACol Col1
        {
            get
            {
                return mCol1;
            }
        }

        public ACol Col2
        {
            get
            {
                return mCol2;
            }
        }

    }
}

AlSql - Indexes

Indexes are created to improve the application performance, and also to allow declaration of foreign keys relationships.
Indexes can be unique or non-unique. Unique indexes can act as a kind of constraint that prevents duplicated records.

Primary Index
In the framework for every table we will need to declare a primary unique index. The reason is to use that index when executing the sql UPDATE command to build the WHERE clause.
Most tables have a column to be used as unique id, like customer number, order number, product id, etc. So we can use those ids as component for the primary index. If you have a table that doesn't have a column for the unique index, you can add a sql identity key. For now our framework doesn't support identity key, but can be easily added. Just leave a message that I'll add it.

AIndex class
The constructor of an index will need the following parameters:
Table: table which will have the index.
Name: the name of the index.
Primary: indicates if the index primary.
Unique: indicates if index is unique.
Columns: components for the index.

Creating the index
To create the index on the table we will use the method AIndex.GetSqlCreate. The we can use the ADB.ExecuteCommand().

Here is the complete code for AIndex class. We will see foreign keys next, and then show the complete definition of our table class.

using System;
using System.Collections.Generic;
using System.Text;

namespace AlSql
{
    public class AIndex
    {
        /// <summary>
        /// Table which the index belongs to.
        /// </summary>
        private ATable mTable;

        /// <summary>
        /// Index name.
        /// </summary>
        private string mName;

        /// <summary>
        /// Indicates the primary index for the table.
        /// </summary>
        private bool mIsPrimary;

        /// <summary>
        /// Indicates a unique index.
        /// </summary>
        private bool mIsUnique;

        private List<ACol> mCols = new List<ACol>();

        /// <summary>
        /// Constructs an index.
        /// </summary>
        /// <param name="pTable">Table</param>
        /// <param name="pName">Index Name</param>
        /// <param name="pIsPrimary">Is table's primary index</param>
        /// <param name="pIsUnique">Is Unique</param>
        public AIndex(ATable pTable, string pName, bool pIsPrimary, bool pIsUnique, params ACol [] pCols)
        {
            mTable = pTable;
            mName = pName;
            mIsPrimary = pIsPrimary;
            mIsUnique = pIsUnique;
            foreach (ACol vCol in pCols)
            {
                mCols.Add(vCol);
            }
        }

        /// <summary>
        /// Table property.
        /// </summary>
        public ATable Table
        {
            get { return mTable; }
        }

        /// <summary>
        /// Index Name.
        /// </summary>
        public string Name
        {
            get { return mName; }
        }

        /// <summary>
        /// Full name: Table.Index
        /// </summary>
        public string FullName
        {
            get { return mTable.Name + "." + mName; }
        }

        /// <summary>
        /// Indicates if index is primary. Used for updates and deletes.
        /// </summary>
        public bool IsPrimary
        {
            get { return mIsPrimary; }
        }

        /// <summary>
        /// Indicates if index is unique.
        /// </summary>
        public bool IsUnique
        {
            get { return mIsUnique; }
        }

        /// <summary>
        /// Index column list.
        /// </summary>
        public List<ACol> Cols
        {
            get { return mCols; }
        }

        /// <summary>
        /// Sql Create Index method.
        /// </summary>
        /// <returns>Sql command to create the Index.</returns>
        public string GetSqlCreate()
        {
            StringBuilder vSql = new StringBuilder();

            vSql.Append("CREATE ");
            if (this.IsUnique)
                vSql.Append("UNIQUE ");
            vSql.Append("INDEX " + this.Table.Name + "_" + this.Name + " ON [" + this.Table.Name + "] (");
            foreach (ACol vCol in this.Cols)
            {
                if (vCol != this.Cols[0])
                    vSql.Append(", ");
                vSql.Append(vCol.Name);
            }
            vSql.Append(")");

            return vSql.ToString();
        }
    }
}