ThinkInSQL.NET by Independent Reach

A short summary of ThinkInSql.NET features and approaches:

Author

Overview Contents


The examples and guidelines below will refer to the following data objects:
    public class CustomerRec extends BaseRecord
    {
        private long customer_id = 0;
        private string customer_name="";
        private boolean is_active=false;        
        public long CustomerId
        {
            get
            {
                return customer_id;
            }
            set
            {
                customer_id = value;
            }
        }
        //other property gets and sets...
    }
    //
    public class SaleRec extends BaseRecord
    {
        private long customer_id = 0;
        private string customer_po="";
        private double sale_amount=0;
        private string sale_desc="";
        private long sale_id = 0;       
        public long SaleId
        {
            get
            {
                return sale_id;
            }
            set
            {
                sale_id = value;
            }
        }
        //other property gets and sets...
    }
    //
    public class SaleRow extends BaseRow
    {
        private long customer_id = 0;
        private long sale_id = 0;
        private string sale_desc="";
        private string customer_name="";        
        public long SaleId
        {
            get
            {
                return sale_id;
            }
            set
            {
                sale_id = value;
            }
        }
        //other property gets and sets..
    }
toc

Creating Record and Row Objects

Use Database.GenerateClass() to generate a class definition for a table name or select statement. We suggest IRecord implementer names be suffixed with Rec, and read-only IRow implementer names be suffixed with Row. Bear in mind, you do not need to map all columns on a table for an IRecord implementer to be updatable, as long as the primary key is included.

Usage by Primary Key

    
    //
    CustomerRec theCust = new CustomerRec();
    //
    //select by passed keys
    //
    if (theCust.Select(someValueReceivedFromElsewhere))
        Debug.WriteLine("Found customer " + someValueReceivedFromElsewhere);
    //
    //select embodied
    //
    theCust.CustomerId = someValueReceivedFromElsewhere;
    if (theCust.Select())
        Debug.WriteLine("Found customer " + theCust.CustomerId);
    //
    if (theCust.Exists())
    {
        theCust.Update();
    }
    else
    {
        theCust.Insert();
    }
    //
    // Write does the same as Exists() ? Update() : Insert()
    //
    theCust.Write();
    //
    theCust.Delete();
toc

Externalizing SQL

ThinkInSql explicitly supports an SQL-externalization method as a best practice with several advantages. Consider the following xml document, to which some examples further down refer:
<?xml version="1.0" encoding="UTF-8"?>
<sql>

<CustomerRec>
    <SelectWholesale><![CDATA[
        select * from customer where type_code='WH'
    ]]></SelectWholesale>
</CustomerRec>

<SaleRec>
    <IsCustomerPoUsed><![CDATA[
        select 1 from tSale where customer_po=? and sale<>? 
    ]]></IsCustomerPoUsed>
    <IsCustomerPoUsedParms><![CDATA[
        tSale.customer_po,tSale.sale
    ]]></IsCustomerPoUsedParms>
    <SelectByCustomerPo><![CDATA[
        select * from tSale where customer_po=? order by sale_id desc
    ]]></SelectByCustomerPo>
    <SelectByCustomerPoParms><![CDATA[
        tSale.customer_po
    ]]></SelectByCustomerPoParms>
    <SelectForCustomer><![CDATA[
        select * from tSale where customer_id=? order by sale_id desc
    ]]></SelectForCustomer>
    <SelectForCustomerParms><![CDATA[
        tSale.customer_id
    ]]></SelectForCustomerParms>
    <UpdateCustomerPo><![CDATA[
        update tSale set customer_po=? where sale_id=?
    ]]></UpdateCustomerPo>
    <UpdateCustomerPoParms><![CDATA[
        tSale.customer_po,tSale.sale_id
    ]]></UpdateCustomerPoParms>
</SaleRec>

<SaleRow>
    <SelectLotsOfSales><![CDATA[
        select s.*,c.customer_name from sale s inner join customer c on s.customer_id=c.customer_id
        order by customer_name,sale_id desc
    ]]></SelectLotsOfSales>
</SaleRow>

</sql>
Our approach is to place all record and row objects for a given database in the same namespace, and include in that namespace an xml resource of SQL statements and fragments like that above. Since we set parameter types in terms of database columns, create companion nodes with the "Parms" suffix to specify parameter types for named statements, then pass in a null to the command invocation like:
    db.SelectScalar("SaleRec.isCustomerPoUsed",null,ponum,salenum);
Use Database.LoadNamedStatements(XmlDocument) if you want to try our approach, or LoadNamedStatements(Hashtable) or neither if you prefer not to use named statements. To load statements from an xml document:
    public static XmlDocument GetSql()
    {
        Assembly asm = Assembly.GetExecutingAssembly();
        Stream str = asm.GetManifestResourceStream("ThinkInSqlSamples.sql.xml");
        XmlDocument doc = new XmlDocument();            
        doc.Load(str);
        str.Close();
        return doc;
    }
Override Database.OverrideNamedStatement to perform custom substitutions. The advantages of SQL externalization are: Some related recommendations:
toc

Selecting Data

To select a record by alternate key, use Database.SelectFirst:

    public static SaleRec SelectByCustomerPO(string po)
    {
        SaleRec r = new SaleRec();
        if (r.GetDatabase().SelectFirst("SaleRec.SelectByCustomerPO",null,r,po))
            return r;
        return null;
    }
For relatively small result sets, implement set selections as static methods and return strongly typed sets to consuming code:
    public static SaleRec[] SelectForCustomer(long cust)
    {
        SaleRec r = new SaleRec();
        return (SaleRec[]) r.GetDatabase().SelectRows("SaleRec.SelectForCustomer",null,r.GetType(),cust);
    }
Related persistence objects can consume each other's methods for relation-traversal with easily readable code:
    //(instance method on CustomerRec)
    public SaleRec[] SelectSales()
    {
        return SaleRec.SelectForCustomer(this.customer_id);
    }
For potentially large result sets, use Database.SelectReader in conjunction with an IRow/IRecord implementer:
    public static IReader SelectLots() 
    {
        return new SaleRow().GetDatabase().SelectReader("SaleRow.SelectLots",null);
    }
    //
    // somewhere above the data access tier...
    //
    IReader rdr = null;
    try
    {   
        rdr = SaleRow.SelectLots();
        SaleRow row = new SaleRow();
        while (rdr.Read(row))
        {//do stuff
        }
    }
    finally
    {
        if (rdr != null)
            rdr.close();
    }
For scalar selections, see Database.SelectScalar and Database.SelectScalarList. If you want to retrieve a relatively small set of objects and you don't want to bother defining an IRow, see Database.SelectMatrix. Last, if you want to do something directly with a data provider, you can use Database.GetConnection and roll your own.

If you don't want to use select *, or you want to use field aliases, and you need to select the same fields in more than one way, you can combine multiple sql elements to build a statement:

...
<LegacyAddressRow>
    <SelectFrom><![CDATA[
        select psacn# as control_number,psast# as street_number,
            psasdr as street_direction,psas#t as street_number_to,psastr as street_name,
            d.stades as city_name,psasda as municipality,psapx1 as exchange
        from lgaddr s inner join lgcity d on s.psadtc=d.stasab  
    ]]></SelectFrom>
    <SelectByCustomerParms><![CDATA[
        lgaddr.atcus#
    ]]></SelectByCustomerParms>
    <SelectByPostalCodeParms><![CDATA[
        lgaddr.psazip
    ]]></SelectByPostalCodeParms>
    <WhereCustomerEquals><![CDATA[
        INNER JOIN lgcadd a ON a.atacl#=s.psacn# and atexpd>? and a.atapls=1  
        where a.atcus#=? 
    ]]></WhereCustomerEquals>
    <WherePostalCodeEquals><![CDATA[
        where upper(psazip)=?
    ]]></WherePostalCodeEquals>
    <WhereSuffix><![CDATA[ 
        and psasts<>'D' order by psacn#
    ]]></WhereSuffix>
</LegacyAddressRow>
...

public class LegacyAddressRow extends BaseRow
{
    ...
    public static LegacyAddressRow[] SelectByPostalCode(String pc)
    {
        LegacyAddressRow r = new LegacyAddressRow();
        Database db = r.GetDatabase();
        string sql = db.CombineNamedStatements(
            "LegacyAddressRow.SelectFrom", "LegacyAddressRow.WherePostalCodeEquals",
            "LegacyAddressRow.WhereSuffix");
        return (LegacyAddressRow[]) db.selectRows(sql,"LegacyAddressRow.SelectByPostalCodeParms",r,pc);
    }

    public static LegacyAddressRow[] SelectForCustomer(long cust) 
    {
        LegacyAddressRow r = new LegacyAddressRow();
        Database db = r.GetDatabase();
        string sql = db.CombineNamedStatements(
            "LegacyAddressRow.SelectFrom", "LegacyAddressRow.WhereCustomerEquals",
            "LegacyAddressRow.WhereSuffix");
        return (LegacyAddressRow[]) db.SelectRows(sql,"LegacyAddressRow.SelectByPostalCodeParms",r,cust);
    }
    ...
}
toc

Arbitrary Updates

When you don't want to update all the mapped columns, simply use Database.Execute:
    public void UpdateCustomerPo() 
    {
        GetDatabase().Execute("SaleRec.UpdateCustomerPo",null,customer_po,sale);
    }
toc

Transactions

Use Database.BeginTransaction, Commit, and Rollback.
toc

Events

ThinkInSql provides plug points for applications to interject processing at different points in the life of IRows, IRecords and Databases:
toc

Mapping Conventions

When generating data objects, ThinkInSQL creates private fields with names based on column names or aliases converted to lower case, with non-letter, non-digit characters replaced by underscores. Properties names are also based on column or alias names converted to CamelCase with spaces, dashes and underscores omitted.
At run time, if IRow.GetColumns is not overridden, all non-public read-write fields not starting with underscores are assumed to correspond to column names by the convention used at generation time, and if IRow.GetPropertyNames is not overridden, all public read-write properties are mapped to columns.

toc

Multi-Database Applications

IRow specifies a GetDatabase method, but this is only provided to simplify usage with the object's home database. All of the IRow and IRecord operations can be invoked via Database, with the row object used as a parameter, so copying objects from database to database is as simple as:
    public static void CopyToCache(SaleRec[] sa)
    {
        CacheDb cdb = CacheDb.Open();       
        try
        {
            foreach (SaleRec r in sa)
                cdb.Insert(r);
        }
        finally
        {
            cdb.Close();
        }
    }
One simple method to provide your data objects with access to their home database is to use an application-specific base class and ThreadStatic storage:
    public class SampleDb : Database 
    {
        [ThreadStatic]
        private static SampleDb _threadInstance;
        
        public static SampleDb GetOpen() 
        {
            if (_threadInstance == null)
            {
                _threadInstance = new SampleDb();
                _threadInstance.LoadNamedStatements(SampleRunner.GetSql());
                _threadInstance.Open();
            }
            return _threadInstance;
        }
        protected SampleDb()
        {            
        }
        ...
    }
    public class AppBaseRow : BaseRow 
    {
        public override Database GetDatabase()
        {
            return SampleDb.GetOpen();
        }
    }
toc

Performance

ThinkInSQL caches database metadata and row class information. We do not cache data because we don't know your data or whether anything other than your application is updating it. Most importantly of all with respect to performance, ThinkInSQL makes it super easy for you to craft, tune, test and maintain your SQL!
toc

Portability

ThinkInSQL has been used with Oracle, DB2/400, SQL Server, MySQL, SharpHsql, Access, Pervasive and Sqlite. If you try it with any database and have trouble, notify the maintainers and we'll try to help.
toc

Assumptions / Biases / Quirks

toc