Al Sql Framework

ADataSet joins

2009-10-24T08:35:00.000-07:00

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

2009-10-12T08:47:00.000-07:00

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

2009-10-03T12:44:00.000-07:00

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

2009-09-21T16:49:00.000-07:00

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

2009-08-31T19:25:00.000-07:00

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

2009-07-27T19:14:00.001-07:00

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

2009-07-16T19:03:00.000-07:00

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();
        }
    }
}

AlSql - Sample Application Tables

2009-07-09T18:52:00.000-07:00

Our sample application will have the following tables: Customer, Category, Product, Order and OrderLine. We will use those tables to demonstrate the framework characteristics.

Customer
Contains a list of people buying products. It is used to demonstrate how to declare columns and indexes and general sql commands.

using System;
using System.Collections.Generic;
using System.Text;
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 Customer() : base(AppCfg.MainDB, "CUSTOMER")
        {
        }
    }
}

Category
Is a list of product categories, and will be used to demonstrate an optional relationship. Some products are not required to be associated with category and we will use LEFT OUTER JOIN query.

using AlSql;

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

        public Category() : base(AppCfg.MainDB, "CATEGORY")
        {
        }
    }
}

Product
Will contain a list of products to be sold, will demonstrate how to create a
mandatory relationship for OrderLine table.

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 Product() : base(AppCfg.MainDB, "PRODUCT")
        {
        }
    }
}

Order
Will contains orders from Customers. Also demonstrate a mandatory relationship with Customer table. Customer record must exists before adding an order record.

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 Order() : base(AppCfg.MainDB, "ORDER")
        {
        }
    }
}

OrderLine
List of products bought by customer. The product needs to exist to create an 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 OrderLine() : base(AppCfg.MainDB, "ORDERLINE")
        {
        }
    }
}

Those are the tables for our application, we will modify the code further in order to add indexes and foreign keys.
We will take a look at indexes.

AlSql - ADB - Connecting database to tables

2009-07-04T07:19:00.000-07:00

Now that we have our database class, ADBSQL.cs, we can connect our table to the database. We can add a member to ATable for the database class. Then whenever we execute sql commands we can use the database member.

Our ATable class will require a new parameter for the constructor, so we always create a table associated to a database. All sql commands for the table will be using this database object. The code for the constructor will be like this:

public ATable(ADB pDB, string pName)
{
    mDB = pDB;
    mName = pName;
}

public ADB DB
{
    get { return mDB; }
}

Application main database

For our test application we will use a utiltity class to provide the database object, named AppCfg.cs. Whenever you need to access the main database you have to make use of the AppCfg.MainDB object. Here is the code for AppCfg class.

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

namespace AlTest
{
class AppCfg
{
        private static ADBSQL mMainDataBase = null;

        public static ADB MainDB
        {
            get
            {
                if (AppCfg.mMainDataBase == null)
                {
                    AICSProvider vConnStrProvider = new AppConn();
                    AppCfg.mMainDataBase = new ADBSQL(vConnStrProvider);
                }
                return AppCfg.mMainDataBase;
            }
        }
}
}

Assigning the database to a table

Now we can assign a database to the customer table class in our sample application. We will need to review the code for customer to include the database in the constructor. See the constructor code bellow:

using System;
using System.Collections.Generic;
using System.Text;
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 Customer() : base(AppCfg.MainDB, "CUSTOMER")
        {
        }
    }
}

Creating a table in the database

Finally we can create the table using the following command.

Customer vCustomer = new Customer();
vCustomer.DB.ExecuteCommand(vCustomer.GetSqlCreate());

The previous command will create the table Customer in our sample database.
In order to automate all database changes, we will create a utility class. But before that, we need need to declare some more tables for our application and show how to add indexes and relations to our tables.

AlSql - ADB - Database class

2009-05-28T19:36:00.000-07:00

The ABD class is declared as virtual, so you have to extend it to create the concret class with the functionality for database access.
I'm using Microsoft sql server as backend database. So our sample application will have a ADBSQL.cs and we will add code for our database specifics. You don't need to change ADB.cs class, you need to create your own db class and implement the methods. If you use Microsoft sql server, you can use the ADBSQL.cs.
If you use a different database, for example Oracle, you can create a class called ADBORACLE.cs and implement the methods to access the Oracle db.

Transaction Control

The ADB class will have the transaction control mechanism. We will have the methods BeginTransaction, CommitTransaction and RollbackTransaction.
The way you will use in your application will be like this:

try {
    AppCfg.MainDB.BeginTransaction();
    // perform insert, update, deletes
    AppCfg.MainDB.CommitTransaction();
} catch (Exception e) {
    AppCfg.MainDB.RollbackTransaction();
    Console.WriteLine(e.Message);
}

Start the transaction inside a try/catch block by using BeginTransaction() method. The transaction scope is per database, so all updates should refer to the same database. If there are no exceptions we should reach CommitTransaction() method and all the changes are commited to the database. In case of any exception the RollbackTransaction() method inside the catch will be called and the changes will be undone for that transaction, and you can take appropriate action.

The ADB.vb base class

This class uses the interfaces to deal with the database access, such as "IDbConnection", "IDbTransaction", "IDataAdapter", etc. The concrete implementation will come from ADBSQL.cs class, in our case, or any other class you create for your database.
Also will have some global variables (mCurrentConnection, mCurrentTransaction, mThereIsActiveTransaction) that are used for transaction control.
The methods necessary for you to implement in your database class are:

public abstract IDataAdapter CreateDataAdapter(string pSelect);
public abstract IDbConnection CreateConnection(AICSProvider pCSProvider);
public abstract IDbCommand CreateCommand(IDbConnection pConnection);

See the ADBSQL.vb code bellow for actual implementation.
Then you have the methods for transaction control:

public void BeginTransaction()
public void CommitTransaction()
public void RollbackTransaction()

And finally we have the utility methods to allow other classes to perform sql commands using the ADB class. Usually the ATable class will be using those methods.

public int ExecuteCommand(string pCommandText)
public IDataReader ExecuteQuery(string pQuery)
public string ExecuteScalar(string pCommandText)

Bellow is the source code for ADB.cs and ADBSQL.cs. Next we will show the use of database classes by the ATable.

ADB.cs

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

namespace AlSql
{
    /// <summary>
    /// Base class to create sql connection to database.
    /// This class make reference to ado.net interfaces and the
    /// implementation should be made at the inherited classes.
    /// For example: ADBSql.vb for System.Data.SqlClient.Sql*
    /// </summary>
    public abstract class ADB
    {
        /// <summary>
        /// Connection string provider.
        /// </summary>
        private AICSProvider mCSProvider;

        /// <summary>
        /// Current connection to be used when there's an active transaction.
        /// </summary>
        private static IDbConnection mCurrentConnection;
        /// <summary>
        /// Current transaction object.
        /// </summary>
        private static IDbTransaction mCurrentTransaction;
        /// <summary>
        /// Indicates there's an active transaction.
        /// </summary>
        private static bool mThereIsActiveTransaction = false;

        /// <summary>
        /// Constructs the database connections using the connection string.
        /// </summary>
        /// <param name="pCSProvider"></param>
        public ADB(AICSProvider pCSProvider)
        {
            mCSProvider = pCSProvider;
        }

        /// <summary>
        /// Should be implemented in the inherited class.
        /// Must return the Data Adapter using the parameters and connection string.
        /// </summary>
        /// <param name="pSelect">Sql select command</param>
        /// <param name="pCSProvider">Connection string provider</param>
        /// <returns>Data Adapter object</returns>
        public abstract IDataAdapter CreateDataAdapter(string pSelect);

        /// <summary>
        /// Create a sql connection to the database.
        /// </summary>
        /// <param name="pCSProvider">Connection string provider</param>
        /// <returns>Sql Connection</returns>
        public abstract IDbConnection CreateConnection(AICSProvider pCSProvider);

        /// <summary>
        /// Create a sql command using the connection.
        /// </summary>
        /// <param name="pConnection">Sql connection</param>
        /// <returns>Sql Command</returns>
        public abstract IDbCommand CreateCommand(IDbConnection pConnection);

        /// <summary>
        /// Initiates a new transaction. Can have only one active transaction at a time.
        /// </summary>
        public void BeginTransaction()
        {
            if (ADB.mThereIsActiveTransaction)
                throw new Exception("Attempt to start a new transaction with an active transaction.");
            ADB.mCurrentConnection = CreateConnection(mCSProvider);
            ADB.mCurrentTransaction = ADB.mCurrentConnection.BeginTransaction();
            ADB.mThereIsActiveTransaction = true;
        }

        /// <summary>
        /// Commits current transaction.
        /// </summary>
        public void CommitTransaction()
        {
            ADB.mCurrentTransaction.Commit();
            ADB.mCurrentTransaction = null;
            ADB.mThereIsActiveTransaction = false;
            ADB.mCurrentConnection.Close();
        }

        /// <summary>
        /// Rollbacks current transaction.
        /// </summary>
        public void RollbackTransaction()
        {
            try
            {
                ADB.mCurrentTransaction.Rollback();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                Console.WriteLine(ex.StackTrace);
            }
            finally
            {
                ADB.mCurrentTransaction = null;
                ADB.mThereIsActiveTransaction = false;
                ADB.mCurrentConnection.Close();
            }
        }

        /// <summary>
        /// Executes the sql command.
        /// </summary>
        /// <param name="pCommandText">Sql command to be executed</param>
        /// <returns>Sql command result number</returns>
        public int ExecuteCommand(string pCommandText)
        {

            //Console.WriteLine("Execute Command: [" + pCommandText + "]");
            IDbConnection vConn = null;
            try
            {
                if (ADB.mThereIsActiveTransaction)
                    vConn = ADB.mCurrentConnection;
                else
                    vConn = CreateConnection(mCSProvider);
                IDbCommand vCommand = CreateCommand(vConn);
                vCommand.CommandText = pCommandText;
                if (ADB.mThereIsActiveTransaction == true)
                    vCommand.Transaction = ADB.mCurrentTransaction;
                int vResult = vCommand.ExecuteNonQuery();
                if (ADB.mThereIsActiveTransaction == false)
                    vConn.Close();
                return vResult;
            }
            catch (Exception e)
            {
                Console.WriteLine("Exception: " + e.GetType().Name);
                Console.WriteLine("Message: [" + e.Message + "]");
                Console.WriteLine("SQL=[" + pCommandText + "]");
                Console.WriteLine(e.StackTrace);
                if (ADB.mThereIsActiveTransaction == false)
                    vConn.Close();
                throw e;
            }
        }

        /// <summary>
        /// Execute a query command.
        /// </summary>
        /// <param name="pQuery">Sql query</param>
        /// <returns>Data Reader with the results</returns>
        public IDataReader ExecuteQuery(string pQuery)
        {
            //Console.WriteLine("Execute Query: [" + pQuery + "]");

            IDbConnection vConn = null;
            try
            {
                vConn = CreateConnection(mCSProvider);
                IDbCommand vCommand = CreateCommand(vConn);
                vCommand.CommandText = pQuery;
                IDataReader vDataReader = vCommand.ExecuteReader(CommandBehavior.CloseConnection);
                return vDataReader;
            }
            catch (Exception e)
            {
                Console.WriteLine("Exception: " + e.GetType().Name);
                Console.WriteLine("Message: [" + e.Message + "]");
                Console.WriteLine("SQL=[" + pQuery + "]");
                Console.WriteLine(e.StackTrace);
                if (vConn != null)
                    vConn.Close();
                throw e;
            }
        }

        /// <summary>
        /// Executes a scalar command, such as functions.
        /// </summary>
        /// <param name="pCommandText">Sql command.</param>
        /// <returns>Result of scalar command</returns>
        public string ExecuteScalar(string pCommandText)
        {
            IDbConnection vConn = null;
            try
            {
                if (ADB.mThereIsActiveTransaction)
                    vConn = ADB.mCurrentConnection;
                else
                    vConn = CreateConnection(mCSProvider);
                IDbCommand vCommand = CreateCommand(vConn);
                vCommand.CommandText = pCommandText;
                if (ADB.mThereIsActiveTransaction)
                    vCommand.Transaction = ADB.mCurrentTransaction;
                object vResult = vCommand.ExecuteScalar();
                if (ADB.mThereIsActiveTransaction == false)
                    vConn.Close();
                if (vResult == null)
                    return "";
                else
                    return vResult.ToString();
            }
            catch (Exception e)
            {
                Console.WriteLine("Exception: " + e.GetType().Name);
                Console.WriteLine("Message: [" + e.Message + "]");
                Console.WriteLine("SQL=[" + pCommandText + "]");
                Console.WriteLine(e.StackTrace);
                if (ADB.mThereIsActiveTransaction == false)
                    vConn.Close();
                throw e;
            }
        }
    }
}

ADBSQL.cs

using System;
using System.Data;
using System.Data.SqlClient;

namespace AlSql
{
    public class ADBSQL : ADB
    {
        private AICSProvider mCSProvider;

        public ADBSQL(AICSProvider pCSProvider) : base(pCSProvider)
        {
            mCSProvider = pCSProvider;
        }

        public override IDataAdapter CreateDataAdapter(string pSelect)
        {
            return new SqlDataAdapter(pSelect, mCSProvider.ConnectionString);
        }

        public override IDbConnection CreateConnection(AICSProvider pCSProvider)
        {
            IDbConnection vConn = new SqlConnection(pCSProvider.ConnectionString);
            vConn.Open();
            return vConn;
        }

        public override IDbCommand CreateCommand(System.Data.IDbConnection pConnection)
        {
            IDbCommand vCommand = new SqlCommand();
            vCommand.Connection = pConnection;
            return vCommand;
        }
    }
}

AlSql - ADB Class - Connection String

2008-07-15T19:42:00.000-07:00

In order to connect to a database in our sql framework we will declare the abstract class ADB.vb. This class will have the general functionality for database operations such as execute command and queries. However, you will need to extend this class to implement specific commands for your database. In our test application I'm using Microsoft Sql Server, so we will have the ADBSQL.vb class. The idea here is that if you have a different database or connection you can write the concrete class and only implement what is specific to your database.
Our framework will look only at the ADB class implementation, it doesn't matter what is the database. The ADBSQL class will use the classes provided by microsoft for sql server, which are declared at System.Data.SqlClient. If we needed a different database access, for example ODBC, we can declare an ADBODBC class and use the classes of System.Data.Odbc.
Connection String
To connect to the database we will need a connection string to specify the connection parameters. Our database will be called ASAMPLE and will be hosted at my computer. So my connection string is something like:

Data Source=MyServer; Initial Catalog=ASAMPLE; User ID=sa

Where to store this string ? There are different options, each one with pros and cons. I'm using the app.Config file. I added in the application settings a key for the the connection string. The main disavantage is because the config file is a text file and the user/password will be exposed, security is an issue here. Well I don't want to go in details about this, for the sake of simplicity for our framework, but keep in mind that needs to be addressed for your application.
So, we will add an entry in our app.config like this:

<add key="ASAMPLE" value="Data Source=MyServer; Initial Catalog=ASAMPLE; User ID=sa">

Connection String Provider
To help with different locations of connection string we created an interface class. Once more our framework only see the methods of this interface and the implementation is done by the application developer.
The interface is named AICSProvider.cs and it only one method to return the connection string. Our ADB class will be using the connection string provider. See bellow the implementation for our sample application.

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

namespace AlSql
{
    /// <summary>
    /// Interface for classes that will return the connection string.
    /// </summary>
    public interface AICSProvider
    {
        /// <summary>
        /// Method to return the connection string.
        /// </summary>
        string ConnectionString {get;}
    }
}

Application Connection String Provider
I said before that our application will locate the connection string in our config file. So our implementation will read the string from the config using the .net class AppSettingsReader. Here you have work, if your connection string comes from LDAP or if you have some encryption for password then here is the place you handle it.
Here is our code for our connection string provider:

using System;
using System.Collections.Generic;
using System.Text;
using AlSql;
using System.Configuration;
using System.Xml.Serialization;

namespace AlTest
{
    /// <summary>
    /// Connection string provider for sample application. Will look at the config file for ASAMPLE element.
    /// </summary>
    class AppConn : AICSProvider
    {
        /// <summary>
        /// Application connection string.
        /// </summary>
        private string mConnString;

        /// <summary>
        /// Creates an instance of the Sample application connection string provider.
        /// </summary>
        public AppConn()
        {
            // Reads the connection from application config file.
            AppSettingsReader vASR = new AppSettingsReader();
            mConnString = vASR.GetValue("ASAMPLE", "".GetType()).ToString();
        }

        /// <summary>
        /// Returns the connection string.
        /// </summary>
        public string ConnectionString
        {
            get
            {
                return mConnString;
            }
        }

    }
}

In the next post we will show our ADBSQL class, that will make use of connection string provider and ADB interface.

AlSql - ATable.CreateTable

2008-06-19T12:21:00.000-07:00

We will start to generate sql commands based on our framework. Let's start with CREATE TABLE, using column objects associated with the table. We will show how to add columns to a table and how to generate the "CREATE TABLE" sql command.
The ATable class is going to have Columns, Indexes and Foreign Keys. The classes in the framework are ACol, AIndex and AForeignKey. Also will have methods to generate sql commands and also execute the sql operations of INSERT, SELECT, UPDATE and DELETE.
ATable constructor
The constructor for the table will accepts the name that is going to be used in the sql commands. Later we will need another parameter, the database. It will be convered later.
When you need a table, you are going to inherit ATable and declare the columns as public readonly members. Of course some discussion can take place here because we are exposing class members, but this facilitates the use of columns by the framework and developers, as you will see later.
Customer table
As an example we will declare a Customer table. For now we are just declaring the columns, and later we will add other components like database, indexes, etc.
The will have one column of each data type. Note the name of the column starts with "c", so all column members are grouped together. It is just a naming convention, you can name the column members at your preference. Here is the code for the customer table:

using System;
using System.Collections.Generic;
using System.Text;
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 Customer() : base("CUSTOMER")
        {
        }
    }
}

ATable.GetCols
This a method returns the list of table columns. We are going to use reflection to automate that task. Otherwise the developer would need some work to build the list of columns. The only restriction is that inside the ATable class you always refer to GetCols method instead of the member mCols, because the method populates the list and then returns it. If you access the mCols, before calling GetCols, it will not be initialized yet.
ATable.GetSqlCreate
This method will return a string with the sql command to create the table. We will provide an utility to help the database changes. This utiltity is called ADBUpdate and we will describe it later.
For now we are just showing table creation command using the ACol.GetSqlDef method from each column. The SQL command for our sample Customer table would be like this:

CREATE TABLE CUSTOMER(ID INT NOT NULL, NAME VARCHAR(50) NOT NULL, MAXCREDIT DECIMAL(11,2) NOT NULL, ISACTIVE BIT NOT NULL, CUSTSINCE DATETIME NOT NULL)

The initial code for ATable is bellow. This code will change a lot with the addition of more functionality, such as indexes and constraints and command generation.
But, for next step, we need to take a look at the ADB class, so we can connect our table object to the database and actually create the table.

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

namespace AlSql
{
    public class ATable
    {
        /// <summary>
        /// Table name.
        /// </summary>
        private string mName;

        /// <summary>
        /// List of columns. This list is initialized during the first call of GetFields().
        /// </summary>
        private List<ACol> mCols = null;

        /// <summary>
        /// Constructs a table with the name provided.
        /// </summary>
        /// <param name="pName">Sql name of the table</param>
        public ATable(string pName)
        {
            mName = pName;
        }

        /// <summary>
        /// Return the list of columns for this table.
        /// </summary>
        /// <returns>Collection of columns.</returns>
        public List<ACol> GetCols()
        {
            if (mCols == null){
                mCols = new List<ACol>();
                FieldInfo[] vFields = this.GetType().GetFields();
                foreach (FieldInfo vFieldInfo in vFields)
                {
                    object vValue = vFieldInfo.GetValue(this);
                    if (vValue.GetType().IsSubclassOf(typeof(ACol)))
                    {
                        ACol vCol = (ACol)vValue;
                        vCol.Table = this;
                        mCols.Add(vCol);
                    }
}
            }
            return mCols;
        }

        /// <summary>
        /// Prepare and return the Sql Create command
        /// based on table and column definition.
        /// </summary>
        /// <returns>Create table command.</returns>
        public string GetSqlCreate()
        {
            StringBuilder vSql = new StringBuilder("CREATE TABLE " + mName + "(");
            string vDelimiter = "";
            foreach (ACol vCol in GetCols())
            {
                vSql.Append(vDelimiter + vCol.GetSqlDef());
                vDelimiter = ", ";
            }
            vSql.Append(")");
            return vSql.ToString();
        }

    }
}

AlSql - ABool Class

2008-06-03T12:40:00.000-07:00

This class will represent the boolean type, and will be declared in the database as BIT. In sql server the BIT type can have 1 and 0 values, and in our framework we will map it to true and false. Also it supports NULL value.
GetSqlDef
It returns the sql declaration as BIT.
BoolValue
Will have true or false.
SqlValue
Will return 1 or 0 values to be used in sql command.

Code for the class is bellow.
Next we will start to look at the table class, and finally be able to generate some sql commands using our framework.
See you there.

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

namespace AlSql
{
    /// <summary>
    /// Represents a boolean column. The TRUE value will be represented
    /// by 1 and FALSE value will be represented by 0.
    /// The sql definition is BIT.
    /// </summary>
    public class ABool : ACol
    {
        /// <summary>
        /// Constructs a boolean column with the name provided.
        /// </summary>
        /// <param name="pName">Column Name</param>
        public ABool(string pName) : base(pName)
        {
            BoolValue = false;
        }

        /// <summary>
        /// True or false value for the column.
        /// </summary>
        public bool BoolValue
        {
            get {
                return (Value == "1");
            }
            set
            {
                if (value == true)
                    Value = "1";
                else
                    Value = "0";
            }
        }

        /// <summary>
        /// Sql definition of the column.
        /// </summary>
        /// <returns>Sql definition as BIT</returns>
        public override string GetSqlDef()
        {
            return Name + " BIT " + SqlNullDef();
        }

        /// <summary>
        /// Returns the sql value for the column:
        /// TRUE: 1
        /// FALSE: 0
        /// </summary>
        /// <returns>Sql value as 1 or 0.</returns>
        public override string SqlValue()
        {
            if (IsNull())
                return "NULL";
            if (BoolValue)
                return "1";
            else
                return "0";
        }
    }
}

AlSql - ADate class

2008-05-29T13:12:00.000-07:00

This class will represent the Date type, and will be declared as DATETIME. We will not be using the time. If you prefer you may create a class "ADateTime" to use time. To keep simple we will be working with month/day/year dates.
If a date column is NULL in the database we will represent it as DateTime.MinValue and set the IsNull to true. The DateTime.MinValue is "1/1/0001". It would be nice if all types in the language could represent null, so that's why we are using the IsNull() methods for our ACol classes.
GetSqlDef
It returns the sql declaration as DATETIME.
DateValue
It's the ADate specific property to faciliate the use of the column date value. Will return DateTime.MinValue when the column is NULL.
SqlValue
We will use the DateTime.ToShortDateString() function to return the date value. In case of null we return NULL value.

Bellow we have the complete code for the class.
We have one more data type class, which is the boolean, and then we will move to the ATable class.

using System;

namespace AlSql
{
    /// <summary>
    /// Represents the Date data type. It will have the DATETIME definition for sql.
    /// </summary>
    public class ADate : ACol
    {
        /// <summary>
        /// Constructs a date column with the name provided.
        /// </summary>
        /// <param name="pName">Name of the date column</param>
        public ADate(string pName) : base(pName)
        {
            Value = null;
        }

        /// <summary>
        /// DateValue property. It will return a DateTime value or DateTime.MinValue if value is not valid or is null.
        /// </summary>
        public DateTime DateValue
        {
            get
            {
                try
                {
                    return DateTime.Parse(Value);
                }
                catch (Exception)
                {
                    return DateTime.MinValue;
                }
            }
            set
            {
                Value = value.ToShortDateString();
            }
        }

        /// <summary>
        /// Returns the sql definition for the Date column as DATETIME.
        /// </summary>
        /// <returns>Sql definition</returns>
        public override string GetSqlDef()
        {
            return Name + " DATETIME " + SqlNullDef();
        }

        /// <summary>
        /// Returns the value for sql updates.
        /// </summary>
        /// <returns>Value to be used in sql update command.</returns>
        public override string SqlValue()
        {
            if (Value == null)
                return "NULL";
            else
                return "'" + DateValue.ToShortDateString() + "'";
        }
    }
}

AlSql - AInt and ADec classes

2008-05-07T11:39:00.000-07:00

Those two classes will represent integer and decimal numbers. The main feature is the ability to represent the format. AInt is declared as INT data type in the sql server table and ADec is declared as DECIMAL.
NULL value
In order to handle NULL values, we will need to use the base method "IsNull", since we cannot store the value "nothing" in numeric data types. If the column has a null value, and you access the Value property you will get 0. The easy way is to not design the application with null values in numeric columns, this make life easier, however if you need to handle nulls you will test for IsNull before accessing the value.
For example you will need to do something like this:
if (col.IsNull)
// handle null
else
// handle not null
Format
We will have a new member to hold the format, which we assume the standard formating strings from the language, such as "###,##0". And we can use String.Format(format) to do the formatting.
There will be one constructor with format, and another one where we initialize with a standard format.
IntValue and DecValue
Those are unique properties of AInt and ADec to help the use of numeric columns, because you won't need to cast the string Value to a numeric value, it is done at the method itself.
It will return 0 when Value has a non-numeric value. Why an integer column can have a non-numeric value? Can be a logic error where a string value is assigned using the Value() method. You can catch that in many different ways, however for simplicity we just return 0. You adjust the error handling according to your necessity.
GetFormattedValue
This method will be override to return the formatted value, using the Format string. We will be using the String.Format method and the mFormat member variable.
Scale and Precision
Those two methods will help in the declaration of the decimal format for SQL server. For example, if we have the format "###,##0.00" we need to generate the sql definition as "DECIMAL(8,2)" where 8 is the precision and 2 is the scale. The GetSqlDef method will use those 2 methods.

Where are we at ? We have 2 more data type classes to go: Date and Boolean. Then we will start with Table class where things become more interesting with the sql command generation.

AInt Class

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

namespace AlSql
{
///

/// Represents an Integer column: Sql INT data type.
///

    public class AInt : ACol
    {
        ///

/// The numeric format, following the c# formatting syntax.
///

private string mFormat;

///

/// Constructor with format.
///

        /// Column name
        /// Numeric Format as in C# syntax
        public AInt(string pName, string pFormat) : base(pName)
        {
            mFormat = pFormat;
            IntValue = 0;
        }

        ///

/// Constructor for a default format of "###,##0"
///

        /// Column name
        public AInt(string pName) : this(pName, "###,##0")
        {
        }

        ///

/// Sql definition of the column.
///

        ///
        public override string GetSqlDef()
        {
            return Name + " INT " + SqlNullDef();
        }

        ///

/// Integer value property. Will return 0 for an invalid value.
///

        public int IntValue
        {
            get {
                try
                {
                    return Int32.Parse(base.Value);
                }
                catch (Exception)
                {
                    return 0;
                }
            }
            set {
                base.Value = value.ToString();
            }
        }

        ///

/// Return the value for sql updates.
///

        /// Value to be used in sql commands.
        public override string SqlValue()
        {
            if (IsNull())
                return "NULL";
            else
                return IntValue.ToString();
        }

        ///

/// Returns the current value using the column format.
///

        /// Formatted value.
        public override string GetFormattedValue()
        {
            return IntValue.ToString(mFormat);
        }
    }
}

ADec Class

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

namespace AlSql
{
    ///

/// Represents a Decimal column: Sql type is DECIMAL.
///

    public class ADec : ACol
    {
        ///

/// The numeric format, following the c# formatting syntax.
///

private string mFormat;

///

/// Constructor with format.
///

        /// Column name
        /// Numeric Format as in C# syntax
        public ADec(string pName, string pFormat) : base(pName)
        {
            mFormat = pFormat;
            DecValue = 0;
        }

        ///

/// Constructor for a default format of "###,###,##0.00"
///

        /// Column name
        public ADec(string pName): this(pName, "###,###,##0.00")
        {
        }

        ///

/// Sql definition of the column.
///

        ///
        public override string GetSqlDef()
        {
            return Name + " DECIMAL(" + Precision() + "," + Scale() + ") " + SqlNullDef();
        }

        ///

/// Decimal value property. Will return 0 for an invalid value.
///

        public decimal DecValue
        {
            get
            {
                try
                {
                    return Decimal.Parse(base.Value);
                }
                catch (Exception)
                {
                    return 0;
                }
            }
            set
            {
                base.Value = value.ToString();
            }
        }

        ///

/// Return the number of decimal places in the format.
///

        /// Decimal places of the format
        public int Precision()
        {
            int vPrecision = 0;
            string vFirstPart = mFormat.Split(new Char[]{ ';' })[0];

            foreach (char vDigit in vFirstPart)
            {
                if ("#0".IndexOf(vDigit) != -1)
                    vPrecision += 1;
            }
            return vPrecision;

        }

        ///

/// Return the number of decimal places to the right of the decimal point.
///

        /// Number of decimal places to the right of the decimal point
        public int Scale()
        {
            string vFirstPart = mFormat.Split(new Char[]{ ';' })[0];
            int vPos = vFirstPart.IndexOf(".");
            if (vPos == -1)
                return 0;
            int vScale = 0;
            while (true)
            {
                vPos += 1;
                if (vPos >= vFirstPart.Length)
                    break;
                if ("#0".IndexOf(vFirstPart[vPos]) != -1)
                    vScale += 1;
            }
            return vScale;
        }

        ///

/// Return the value for sql updates.
///

        /// Value to be used in sql commands.
        public override string SqlValue()
        {
            if (IsNull())
                return "NULL";
            else
                return DecValue.ToString();
        }

        ///

/// Returns the current value using the column format.
///

        /// Formatted value.
        public override string GetFormattedValue()
        {
            return DecValue.ToString(mFormat);
        }
    }
}

AlSql - AChar class

2008-04-01T10:16:00.000-07:00

This class will represent the string value, and will be stored as VARCHAR.
Length
We will need a new property to specify the length. We will be using the constructor "AChar(string pName, int pLength)". We can add another constructor with just the name and assume a default length of 10 characters. I prefer to force the user to enter the length so it's clear what is the length for each component. It is just a matter of preference, can be changed if you want.
SqlValue
We will need to override the SqlValue property to do some processing. We need to enclode in single quotes and also add another quote when we need to store quotes in the database value, otherwise we get a syntax error from sql. For example, if we have the value "O'Connor". The value to be stored should be 'O''Connor', the sql command will be something like:
UPDATE TABLE SET NAME = 'O''Connor'
GetSqlDef
This method also needs to be overrided to use the length property.

Bellow we have the complete code for the class. Next post we will be talking about integer and decimal class.

using System;
using System.Collections.Generic;
using System.Text;
using System.ComponentModel;
using System.Data;

namespace AlSql
{
///

/// Represents a character column. It will have the VARCHAR sql data type.
///

    public class AChar : ACol
    {
        ///

/// Length for SQL definition.
///

private int mLength = 0;

///

/// Constructor
///

        /// Column name.
        /// Maximum length
        public AChar(string pName, int pLength) : base(pName)
        {
            mLength = pLength;
        }

        ///

/// Return the value for sql updates.
///

        /// Value to be used in sql commands.
        public override string SqlValue()
        {
            if (Value == null)
                return "NULL";
            else
                return "'" + Value.Replace("'", "''") + "'";
        }

        ///

/// Returns the sql definition as VARCHAR.
///

        /// Sql column definition
        public override string GetSqlDef()
        {
            return Name + " VARCHAR(" + mLength.ToString() + ") " + SqlNullDef();
        }
    }
}

AlSql - ACol class

2008-02-20T13:53:00.000-08:00

We are going to start with the Column class. The idea is to create a base class for each data type. We will support the following data types: Character, Integer, Decimal, Boolean and DateTime.
Each data type will extend the Column class to override some methods in order to province the specific functionality for itself. This functionality includes the sql definition and value. But later and at the appropriate moment will be adding more functionality.

Naming convention
Before we begin with the code, I'd like to say we will using the follwing naming convention.

All framework classes will start with "A";

Member variables will start with "m";

Parameter names start with "p";

Local variables start with "v";

ACol class
The ACol is declared as abstract, meaning it cannot be instantiated directly, it is necessary to extend this class. We will be extending the ACol to create specialized classes for each data type, which are: AChar, AInt, ADec, ADate and ABool. We will get into each class detail.
This class is basically managing the sql definition and column value. We will extend the functionality later. Let's start with the basics. For example, later we will integrate the sql framework to .net DataSet class, then we will need new methods.
Bellow is the complete code for ACol class. Next we going to show how to create the AChar class for the character field.

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

namespace AlSql
{
    /// <summary>
    /// Base class for all column types.
    /// </summary>
    public abstract class ACol
    {
        /// <summary>
        /// Column name for SQL.
        /// </summary>
        private string mName;
        /// <summary>
        /// Column current value.
        /// </summary>
        private string mValue = "";
        /// <summary>
        /// Flag to represent the sql Nullable property in the column definition.
        /// </summary>
        private bool mAllowNull = false;

        /// <summary>
        /// Constructs a column with provided name.
        /// </summary>
        /// <param name="pName">Column name.</param>
        public ACol(string pName)
        {
            mName = pName;
        }

        /// <summary>
        /// Returns column name.
        /// </summary>
        public string Name
        {
            get { return mName; }
        }

        /// <summary>
        /// Current column value.
        /// </summary>
        public string Value
        {
            get { return mValue; }
            set { mValue = value; }
        }

        /// <summary>
        /// Returns a string for sql column definition of the NULL property.
        /// </summary>
        /// <returns>String representing if column can have null values or not.</returns>
        public string SqlNullDef()
        {
            if (AllowNull)
                return "NULL";
            else
                return "NOT NULL";
        }

        /// <summary>
        /// To indicate if this column allow NULL values.
        /// </summary>
        public bool AllowNull
        {
            get { return mAllowNull; }
            set { mAllowNull = value; }
        }

        /// <summary>
        /// Indicate if this column have a null value.
        /// </summary>
        /// <returns>If the current value is null or not.</returns>
        public bool IsNull()
        {
            return (mValue == null) ;
        }

        /// <summary>
        /// Returns the formatted value to display.
        /// Can be overrided in the sub classes for appropriate formating, specially for numeric columns.
        /// </summary>
        /// <returns>Formatted value.</returns>
        public virtual string GetFormattedValue()
        {
            return Value;
        }

        /// <summary>
        /// Method to return a string representing the sql defintion of the column.
        /// </summary>
        /// <returns>Column Sql Defintion.</returns>
        public abstract string GetSqlDef();

        /// <summary>
        /// Sql value of the column. This value will be passed to the sql commmand. Should follow sql syntax.
        /// For example, string values should be enclosed by single quotes.
        /// </summary>
        /// <returns>Column sql value.</returns>
        public abstract string SqlValue();

    }
}

AlSql - Concepts

2008-02-18T15:43:00.000-08:00

The main idea of the Al Sql Framework is to create a couple of classes that encapsulate sql statements: select, insert, update and delete. Also we will be able to automate the DDL, Data Definition Language: create table, alter table, etc.
I'm assuming you are familiar with sql programming. During the construction of the framework I'll try to explore some concepts in order to apply to our framework.

Main Classes
The main classes of our framework will be Table, Column, and DB. The Table class will handle one table definition at a time, and will used by a dataset class to handle the joins. We are going to create an extension to the .net dataset class. Also we will have classes for Index and Foreign keys.

Sample Application
To demonstrate the use of the framework we will build a sample application. This application will have the following tables: customer, product, order and order-line.
Pretty much will cover the use of the framework for most applications.

Transaction Control
The framework will allow declaration of transactions, where you will be able to declare transaction start, commit or rollback.

Multi-User control
The framework will work using the concept of original values and optimist lock. Will get into details when we start using the UPDATE command.

That's for today. In the next post I'll promise to include some code, so we start to view some code.

AlSql - Introduction

2008-02-11T16:20:00.000-08:00

I've been a computer programmer for almost 20 years and recently I've been programming in java, vb.net, and C#. Mostly I write business applications to access sql databases.

The idea of this blog is to describe a sql framework to avoid hard coding sql statements into the application code. I will go, step by step, showing all necessary classes.

I have been using this framework for quite a while with good results. It automates very well all sql statements generation.

Also it can be a tutorial on developing an Object Oriented application.

So, my goal would be to post once in a while part of the framework, until we have the complete framework and a sample application using it!

C# will be language used. In the past I created the framework in java and vb.net, so now I'm going to give a chance to C#.

In the next post we will start with a description of the framework.
See you there.