Overview Contents
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..
}
//
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();
<?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:
public void UpdateArbitrary()
{
GetDatabase().Execute("ThisObjectType.UpdateArbitrary",null,parm1,parm2);
}
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);
}
...
}
public void UpdateCustomerPo()
{
GetDatabase().Execute("SaleRec.UpdateCustomerPo",null,customer_po,sale);
}
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.
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();
}
}