Sooda includes a code generator, which can generate entire Data Access Layer for your application.
Each class of the DAL typically corresponds to a database table,
its properties map to database columns and relationships map to object references and collections.

Sooda has been used in production systems for at least 9 years and things are generally working smooth.
Some graphical and command line tools to support the development are available, but they are generally
newer and still require some work.

Sooda supports code generation using CodeDOM technology. In theory any language should be supported,
but some languages have problems with their CodeDOM providers or their compilers are buggy.
The following languages have been tested:

Most applications written in high-level languages use the object-oriented
approach and often they need to store data in relational databases.
Working with objects, we use method calls, properties
events, collections, inheritance, polymorphism, and so on, while working with relational data
requires us to think in terms of SELECT, INSERT, UPDATE and DELETE SQL operations
on rectangular tables.

Obviously the two models (object-oriented and relational) are not
compatible. Sooda lets you bridge the gap by providing an object-oriented interface to the database so that
you can write code in your favourite high-level language without using SQL at all.

Let's see how objects can be stored in a database. Assuming we have a simple class named Employee.

Objects of this class can be persisted when we follow the rules of so called "natural mapping", where:

each object is stored in a single row of a single table whose name matches the name of the class

each property is stored in a single column, whose name matches the name of the property/field

the type of the column corresponds to the type of the property

Applying these "natural mapping" rules to the Employee class, we get the following table:

Natural mapping as described above is not always possible/desirable and therefore we need a mapping schema (3)
to
fully describe the mapping for tables/columns/datatypes and more. We can for example:

override database table name (for example TblEmployee instead of Employee).
There are some reserved SQL keywords which cannot be used as table names (SELECT, FROM, WHERE, GROUP)

define names of database columns (for example hire_date instead of HireDate). Some databases
impose limitations on the
maximum column names so you might want to make them shorter.

use other datatypes (such as a column of type "integer" instead of "bit" to store the
value of the "Active" property with "true" and "false" mapped to "1" and "0" respectively)

Entities in the databases are identified by their primary keys. In object-oriented
languages we refer to objects by their references. The references are
valid only for a single execution of the program and cannot be stored in a database directly.
O/R mapper needs to convert between primary key values and object references
when loading and storing data.

In order to participate in O/R mapping each database table needs to have one or more primary key
columns (either natural or surrogate) and you need to tell the mapper which columns are primary keys.
The primary keys must be properly maintained in the database (the best way is to create a UNIQUE index on primary
key columns). If you fail to do so (for example when there are many rows sharing the same
value in the primary key column), you may get all sorts of undefined behavior.

Each object in Sooda knows about the primary key that represents it in the database.
Transaction-level object cache (6.5)
(L1 cache) is used to map primary keys to objects.
This two-way mapping lets Sooda provide reference identity (6.5)
for objects
it manages.

When creating a new object in memory, the primary key needs to be known in advance (before
the object is actually stored in the database). Because of this, we cannot use identity columns in
databases to provide primary keys, other mechanism to generate primary keys is necessary.

Sooda provides key generators (6.9)
which may be used for this purpose.
The default one uses a special table in the database to manage batched key allocation. Alternatively, for primary
keys which are GUIDs, you can use the standard Guid.NewGuid() generation method.

customer has a collection of orders (each order has exactly one customer)

group has members (each member belongs to exactly one group)

mother has children (each child has exactly one mother)

In databases we usually represent relationships as foreign key columns stored in the
"child" objects, so to represent the customer-to-orders relationship we have a foreign
key column (perhaps named "customer") stored in the "Order" table. The value stored
in this column must match the primary key value of the Customer.
The following piece of SQL code demonstrates this for a simple Customer to Order relationship:

Sooda makes the task of managing foreign key relationships rather simple by providing
a dual view of the relationship: reference from a "child" to its "parent" and a collection
of "children" that is a member of the "parent" objects. You only need to specify which property in the
"child" object is a reference to the "parent" object. Code generated by Sooda includes the following APIs:

The OrderList is functionally equivalent to IList<Order> with some filtering
and sorting methods included for convenience (8.4.7)
. Customer is
a reference to the Customer class. Sooda lets you write:

Customer c;
// create new order
Order o = new Order()
c.Orders.Add(o);
// this is equivalent to the above
o.Customer = c;
c.Orders.Remove(o);
// get order count
c.Orders.Count;
// check it the orders contains the specified one
c.Orders.Contains(o);
// return a sorted collection
c.Orders.OrderBy(sortExpression);

Many-to-many relationships are used in many situations, such as these:

person-to-permission (each person has many permissions, each permission is held by many persons)

lecture-to-student (each student attends many lectures, each lecture is attended by many students)

and so on

Database representation of many-to-many relationships requires a separate table that will
hold pairs of primary keys that are in relationship, such as the Employee2Permission table in the following
figure:

These collections support the same set of operations as one-to-many collections, namely Add(), Remove(),
Contains(), Count and others. The collections are synchronized, no matter which object you
use to modify the relationships, the result is the same. In this example you could:

add a Permission to a collection of permissions held by Employee

add an Employee to a collection of employees which hold the permission

Often you need to operate on an object but you do not need access to its properties.
It would be very inefficient to load full record from a database each time
a reference to it is needed. For example you do not need to know the name of the employee
just to check if he is in relationship with some other objects.

Sooda supports lazy loading by maintaining a data structure that holds object data. It
loads the data and initializes the data structures on as-needed basis, which means objects do not allocate
memory for their data values until the properties are accessed.

Each object managed by Sooda can be in one of the four states:

Data Not Loaded - no properties have been accessed

Data Loaded - some properties have been read

Data Not Loaded - Modified - some properties have been written to, but not read from

Data Loaded - Modified - some properties have been written to, and some have been read from

Each time a property is read in Data Not Loaded or Data Not Loaded-Modified state
Sooda needs to access the database to load the data. If a property is only written to, there is no need
to even load the data from the database.

Objects in memory may form graphs: they may include properties which are
references to other objects and these objects may have references to other objects
and so on.

Sooda supports path expressions both in code and in query language (1.2.9)
which let you access properties of related objects easily.
For example to access the last name of the manager of a person's organization unit:

// display the last name of person's org unit
Console.WriteLine(person.OrganizationUnit.Manager.LastName);

You can access collections in the same manner:

// display the number of org unit members
Console.WriteLine(person.OrganizationUnit.Members.Count);

Object references are fully lazy-loaded and managed by transaction L1 cache (1.2.2)
so that the number of database accesses is reduced.

A class can be mapped to more than one table in the database or it can be split into many logical "tables" which
are actually mapped to the same physical table. We may want to do it for many reasons:

database engine limitations - some databases have maximum row size limit, and you may not be able to store
long texts in a single table because of this limit. Splitting table in two and performing joins by primary key
may be the only option

performance - imagine you have some large field which you access infrequently (such as a person's picture).
Loading the field from the database would not be efficient because it would consume time and memory.
Lazy loading mechanism works on a table level which means that it loads the entire row from a table each
time a single column is accessed. Putting the large column in a separate table helps remediate this issue.

This is the most typical situation where some columns may be placed in a separate table for performance:

If the data row size is not a problem, we may still want to do the logical partitioning of data while
keeping them in a single table. We only need to tell Sooda to keep some fields together by grouping them in
a logical Table.

The following example shows the Employee table which contains two large columns which we want to load separately:
Picture which is an image and VoiceSample which is a blob. Possible partitions of this table are marked with
separate colors. Note that the primary key column is not marked because it is always present in memory and
does not need to be loaded at all.

Sooda makes it possible to create a class based on up to 32 physical or logical tables. The only thing they
need to have in common is their primary key. This feature is used in inheritance, which is described
in section "1.2.8. Inheritance"
.

Inheritance ("is-a" relationship) is an object-oriented programming concept which is not present in
relational databases. One of the most important features of inheritance is polymorphism (ability to
act on objects through their base classes or interfaces without knowing their exact type).

Inheriting classes may introduce additional fields (properties) and we need a way to store them in a database.
There are 3 methods of storing inheritance hierarchy in database:

TPT or TPS- table per type (or table per subclass) - each class that adds member fields stores them in a separate table

TPH - table per hierarchy - fields for all classes in the inheritance hierarchy are stored in a single table

TPC - table per concrete type - each concrete type (non-abstract class) is kept in its own table.

Assuming you have a simple hierarchy of 4 classes:

The TPT representation of this hierarchy looks like this (note that each subclass is stored in its own table,
and only the fields added in that subclass are kept there, the other fields are accessible through primary key join):

TPH model (note that fields from all classes are gathered in a single table and each field from a non-root class
is nullable):

Or a TPC model (fields from the base class are repeated in all tables):

Supporting polymorphism in O/R mapping requires the engine to be able to create instances
of the proper subclass on load. This is required because when you load an object from
the database you may get an instance of a subclass.
Object references (stored as foreign keys) are also polymorphic (you only store the key value, not the
concrete type). We need a way to be able to quickly determine the concrete class that will
represent the object from the database.

TPT and TPH storage models require a special column (called discriminator column) to select between subclasses.
This column needs to provide a unique mapping between the concrete class and an integer value
(for example 1-QuestionWorkItem, 2-AnalysisWorkItem, 3-BugWorkItem).
Each time an object of a particular class is persisted, a value corresponding to its class is stored in
this column and on load this value is used to recreate object of the proper class.
TPC model does not need this column but makes it more difficult to access objects in a polymorphic manner,
because you need to perform a UNION select or make multiple queries in order to fetch all objects.

Sooda supports TPT and TPH, but does not support TPC model because it is less efficient
than the other two and requires complex SQL queries to be generated.

Sooda extends the concept of "tables" on which each class is based to mean not only physical tables
but also vertical table partitions. This way you can create a mixture of TPT and TPH in your program.
For example you can store the hierarchy in TWO tables instead of one. You can also
store most commonly used subclasses in a single table but move rarely used or "heavy" data (such as blobs)
to separate physical tables.

Frequent use of single rows from the database (which is common for all O/R mapping products) can be
expensive. The cost of an SQL server roundtrip can be quite high. Sooda implements in-memory caching of objects
retrieved from the SQL database (both single rows and collections) so that the speed of processing is improved.

Sooda supports creation and deletion of fields at runtime.
If this feature is enabled, fields can be added to any Sooda class.
When you add a field, Sooda creates a database table with the requested column type and optionally a foreign key constraint.
Dynamic fields can be accessed from Sooda objects and queries just like the fields defined in the mapping schema.

Sooda comes with a graphical wizard that can be used to automatically add Sooda support to your Visual Studio project.
In this tutorial we will use it to create a simple C# application that exercises various features of Sooda. We will be
using the Northwind database that comes with MSSQL Server but any database should be ok.

Let's start by creating an empty C# Console application project in Visual Studio. After the project has been created
and saved to disk (you need to save your projects explicitly when using Visual Studio Express Editions), run the wizard
by clicking on the "Add Sooda Support to Visual Studio Project" shortcut on the Start menu:

The first step of the wizard is a greeting message that reminds the user to prepare a database
and choose a Visual Studio project file to configure. Choose the project by clicking Browse.
We can proceed to the next step by clicking the Next button.

In the second step of the wizard you need to specify database server and credentials used to connect to the database.
Make sure the account used to connect has the right to create additional tables in the database or you will not be able
to complete the wizard (using a local administrator account and Windows Authentication should be sufficient in most cases).
Click Next to proceed to the database selection:

The third step of the wizard prompts you to choose one of the databases that are running on the server. Choose Northwind and click Next.

The last page of the wizard presents a list of actions the wizard can perform for you. The default set of options should
be just fine, and clicking Finish will add Sooda support to your project as requested.

If you choose to set advanced options by clicking the Advanced Options button, the wizard will present
you with a list of more settings each followed by the Explain link that will direct you to the appropriate
section of the documentation.

Once you have set up your project it is time to compile it. Switch to Visual Studio and click "Build" (note that you will
get warnings about project file modifications - choose to Reload your project when prompted).

It is possible that you get the following error when building your project.

error CS0234: The type or namespace name 'Drawing' does not exist in the namespace'System' (are you missing an assembly reference?)

If you do, add 'System.Drawing.dll' reference to your project and recompile. This error may occur if you
use have any pictures stored in the database (which is the case with Northwind database).

In addition to these your project has been modified to automatically launch the SoodaStubGen utility (8.1.1)
on each build and optionally to launch SoodaCompileStubs (8.1.1.3)
to precompile stubs to a DLL.

Class names detected by the wizard are plural nouns, because this is how tables are named in the Northwind
database. For example we have Territories instead of Territory.
Using plurals in class names is not a good idea so we should fix it by renaming the classes.

To do this, open SoodaSchema.xml and replace all plural class names with the appropriate singular forms.
Make sure to replace only class names not table names, so it is best to use the following renaming strategy:

replace "Territories" with "Territory" (quotes should be included in the
Search For and Replace With strings)

replace <table name="Territory" /> with <table name="Territories" />

Repeat the above steps to rename:

Categories with Category

Customers with Customer

Employees with Employee

Orders with Order

Products with Product

Region with Region

Shippers with Shipper

Suppliers with Supplier

Territories with Territory

In addition, you should give meaningful names to all collections declared with
<collectionOneToMany /> and <collectionManyToMany />. You should
remove CollectionOf and trailing digits from the names.

Remove the following files for classes that are no longer present
or you will get errors about some non-existent stub classes.

It is now time to add some code to the project that will demonstrate the use of DAL classes generated by Sooda.
By default Sooda generates one class for each table found in the database and the name of the class corresponds to
the name of the table as described
in section "8.1.1.5.3. genschema"
.

Before you can use Sooda objects, you need to create an instance of SoodaTransaction (6.1)
class. You can only access your objects within the transaction and after the transaction is closed, object references
are not valid. Typically you create a SoodaTransaction object in a using() clause.

To begin, add the following using clauses to the top of your Program.cs source file
replacing YOURNAMESPACE with the namespace of the project:

using Sooda;
using YOURNAMESPACE.TypedQueries;

Copy the following piece of code and paste it to replace the generated Main() method:

We are now ready to test various Sooda features. Let's start with simple object loading. To load an object given its
primary key, you can use the generated Load() static method. The following code loads a row from the
Territories table and displays its TerritoryDescription. Note that you use the
(renamed) class names instead of the table names in your code.

LINQ and Typed Queries have the advantage of queries being validated at compile time.
This is great when developing large ever-changing projects, as you can immediately know the impact
of removing a field or changing its data type.

In all three Sooda query languages you use path expressions (7.2.1)
instead of SQL JOIN operators.
The following example displays the details of all shipments made via "Speedy Express"
carrier that have more than 3 items:

The schema is contained in an XML file that is the input to SoodaStubGen utility (8.1.1)
which
generates class stubs. You can write the XML file by hand or you can use a tool, such as
SoodaSchemaTool (8.1.1.5)
to generate it for you by reverse-engineering the database.
Note that it is not possible to generate the mapping schema in a fully automated manner. There are
things that simply cannot be retrieved from the database (such as inheritance relationships, named constants, many-to-many
relationship names), anyway the tools can give you a good approximation of the actual schema.

The root of the XML document is the <schema /> element with the appropriate XML namespace (http://www.sooda.org/schemas/SoodaSchema.xsd).
Child elements of the <schema /> define classes, relations and so on, as described below.

Data Sources are defined with the <datasource /> element. A data source represents a
connection to the database (or other store) where entities are stored. Each data source has a name and type.
Sooda currently supports only Sql Data Source (6.12)
, but
it is (theoretically) possible to add more data source types. Unless specified otherwise, the
default data source for a class or relation is "default", which you need to have defined.

While Sooda supports no other data source types, the declaration that should be present at the beginning of each
mapping schema is:

You can define additional SQL-based data sources for example to connect to multiple databases at once. Sooda
will keep a single connection to each data source, but will not create one until you use an object that is based on
this data source.

Classes are defined using <class /> elements. Each class is based on
one or more tables, each represented by a <table /> element.
The following table lists available
attributes of the <class /> element (bold ones are required).

Name

Type

Description

name

string

Class name - used by SoodaStubGen (8.1.1)
to generate
the class skeleton, stub and related classes (factory, list wrapper, typed queries and so on).
Details about the generated code can be found
in section "8.4. Generated Code"
.

cardinality

ClassCardinality

Specifies the approximate number of instances of this class (number of rows in a table).
Possible values are:

Small - up to 20-30

Medium - more than Small, up to 200-300

Large - more than a few hundred

The default value is Medium. You may want to specify other values as a hint to the cache optimizer.
Other than that, this value is not used.

datasource

string

Specifies the name of the data source (6.12)
for this class. The default name is "default".

extBaseClassName

string

Specifies the name of the base class that this class should inherit from. By default each class N inherits
from N_Stub which in turn inherits from SoodaObject. Set this option to make the stub class inherit from some other class.
Note that the class must ultimately inherit from SoodaObject (either directly or indirectly).

label

string

Name of the property that provides the human readable "identity" of the object. For example the
"Person" class might have a label of "Name" or "Email", the "Location" class
might have a label of "Address".
The label is used to generate the default implementation of ToString() for object.

keygen

string

Specifies a method that will be used to generate primary keys for newly created objects.
The following options are supported:

guid - generate GUID keys in-memory by calling Guid.NewGuid()

integer - pregenerates integer keys in the database by storing them in the KeyGen table. This is the default.

long - pregenerates long (64-bit) keys in the database by storing them in the KeyGen table.

none - don't generate a key automatically

Alternatively you can provide a name of the class that implements IPrimaryKeyGenerator that will provide primary keys.
This class call a webservice or use other means necessary to generate keys in a coordinated manner, guaranteeing that
no two sessions will ever get the same primary key.

Specifies the default precommit value for this class. This is the value that will be INSERTed into
the database for each not-nullable column that does not have a value specified. You can override it on the
<field /> (3.4)
level.

subclassSelectorValue

depends on primary key type

Specifies the value of the subclass selector field that determines this object type. This parameter
is explained in the inheritance section (3.9)
.

subclassSelectorField

string

Specifies the name of the subclass selector field that determines this object type. This parameter
is explained in the inheritance section (3.9)
.

cached

bool

Caching hint. The actual interpretation depends on the caching policy (1.2.10)
that is in effect.

cacheCollections

bool

Collection caching hint. The actual interpretation depends on the caching policy (1.2.10)
that is in effect.

<table /> elements are used to define the mapping from database tables to classes and relations as described
in section "1.2.1. Mapping Classes to Tables"
. Each class is based on at least one <table /> element. Each table consists of <field />
elements which represent columns in a database and properties in C#/VB. Each table must have at least one
field which is marked as the primary key. Compound primary keys (formed by multiple fields with primaryKey attribute)
are supported, but tables with zero primary keys are not allowed.

Objects may need to be stored in multiple tables (1.2.7)
for efficiency reasons.
In the following example we have two tables - one has employee ID, Name, Salary, HireDate and Active flag,
the other stores his/her Picture (potentially very large piece of binary data that encodes the person's photo).
By storing the picture in a separate table we can avoid reading from it until the data is actually needed.

Each <table /> is a unit for the load operation, which means whenever you access
one field from a table, the entire table row gets loaded and is cached for further accesses. This is easy way
to implement fine-grained lazy loading (1.2.5)
by splitting your fields in groups that are always accessed together.

It is possible to have two <table /> elements that are bound to the same physical database table. You only need to repeat
the primary key field in each <table /> element. This allows for column-based lazy loading without the need to create
additional database tables. For example, the following Employee class uses mentioned partitioning feature
to avoid loading Picture and VoiceSample with other columns.

The table definition looks like this (different lazy loading groups are marked with different colors):

Example: We have 4 objects: Adam (Person, with primary key 1), Eva (with primary key 2),
Mary (with primary key 3), John (with primary key 4). Let's assume they are represented by the
corresponding variable names and see how lazy loading works as we access their properties and
display them.

Name of the database column. If it is not specified, Sooda assumes this is the same
as 'name'

size

integer

Data type size.

precision

integer

Data type precision.

references

string

Name of the class that this field references. The referenced class must have a single-field (i.e. not
compound) primary key. It is not possible to automatically generate references to classes
with compound primary keys, but it is possible to do it in code.

onDelete

DeleteAction

The action to be taken when the referenced object is deleted (valid when references is not empty).

Nothing - do nothing, the programmer will take care of updating/deleting the
appropriate object references. Failing to do so will result in referential integrity violation

Nullify - set this field value to null (possible only for nullable fields)

Always generate triggers (6.8)
for this field even if triggers attribute for the enclosing class is set to false.

primaryKey

boolean

Mark this field as primary key. Each <table /> (3.3)
must have at least one field marked as primary key.

precommitValue

depends on data type

The value to be used when precommitting (6.10)
not-nullable field and the value is not yet provided. Makes sense only for not-null fields.

find

boolean

Generate "finder" method for this field. The method will be named FindByNNN where NNN is the name of the field and will return
an instance of the class the field is defined in that has field NNN set to the passed value.
For example, when you declare:

Generate list "finder" method for this field. The method will be named FindListByNNN where NNN is the name of the field and will return
a list of objects that have NNN set to the passed value.
For example, when you declare:

The <const /> element defines named constants (8.4.6)
to be generated.
Each constant has a name and value, whose type depends on the primary key type of this class. Constants are
not supported for classes with compound primary keys or where the type of the primary key is other than Integer or
String.

Once you have defined the constants you may use them instead of calling classname.GetRef(). Note that you cannot
pass the instance of SoodaTransaction (6.1)
so constants are limited to
be used in the context of implicit transaction.

In short, it defines a collection named COLLECTION_NAME whose items are of class CHILD_CLASS where the value of
FOREIGN_FIELD equals to this object's primary key. In other words this is a list of objects referencing this object
through the specified reference field.

Sooda also supports additional attribute named where which can be used to define where clause to
be used when fetching objects. This can be used to create filtered collections:

The above example defines two collections in the Group class: one named Members holds all members of
the group, the other is named VipMembers and holds only members who are VIPs.

CAUTION: Collections defined with "where" are not recommended because they do not automatically reflect
the state of child objects when you manually change the foreignField. Collections without where clause
do not exhibit this problem.

The <relation /> element defines a relation (table) that resolves many-to-many relationships.
It must include exactly two fields, which are references to other tables.

Each record in this table represents a relationships between two objects pointed at by the fields.
For example, assuming we have a relation Contact2Group with 2 columns: contact_id and group_id
and it contains a row where contact_id=1 and group_id=7 it means that contact[1] and group[7] are in
(some) relationship. The name of the relationship is established by <collectionManyToMany />.

Each side of the relation has a multiplicity of "many", which means each contact may be in relationship
with many groups and each group can be in relationship with many contacts. This is represented
by a pair of strongly typed collections.

The <collectionManyToMany /> declaration accepts the following attributes:

Name

Type

Description

name

string

Collection name

relation

string

Name of the relation (table) that represents the relationship.

foreignField

string

Name of the field defined in <relation /> that points back at the
object that declares the collection. For example, considering the Contact2Group relation defined above,
you should put this in <classname="Contact" /> to get Contact.Groups collection:

You can of course choose not to declare collections. This is a way to get unidirectional relationships.

masterField

integer (0 or 1)

The ordinal of the field defined in <relation /> that points at the referenced object.
Specifying this attribute is equivalent to providing the appropriate value of foreignField, but
the meaning is reversed. The following declaration declares Contact.Groups and Group.Members collections:
Then you should put this in <classname="Contact" /> to get Groups collection:

In order to define inheritance relationship (1.2.8)
you need to designate one class which will serve
as a root of the inheritance hierarchy and make other inherit (directly or indirectly) from it.
Attributes which control inheritance are specified at the <class /> level. They are:

Name

Type

Description

subclassSelectorField

string

Specifies the name of the subclass selector field that determines this object type. The
field must be defined in this <class />, in the first <table /> and must
be either integer or string. Based on the value stored in this field Sooda is
able to instantiate objects of the appropriate class in a polymorphic manner.
Values stored in this field correspond to concrete (non-abstract) classes and are defined
using subclassSelectorValue. See examples below.

The class that defines subclassSelectorField is named the root of inheritance hierarchy.
You cannot put subclassSelectorField in any class that inherits from another class
(to form sub-hierarchies). This feature might be added in future versions of Sooda.

inheritFrom

string

Inherits from the specified class. The class must either be the root of the inheritance hierarchy
or inherit (directly or indirectly) from such a class. It is an error to inherit from a class
that does not ultimately derive from a root class (that has subclassSelectorField defined).

subclassSelectorValue

integer or string

Specifies the value of the subclass selector field that determines this object type. This parameter
must be is specified for each concrete (non-abstract) class in the inheritance hierarchy.
Sooda will instantiate objects of the appropriate type based on a value of the subclassSelectorField.
If the value is equal to subclassSelectorValue for a particular class, an instance of this
class will be created to represent the object.

If you do not specify the value for subclassSelectorValue, SoodaStubGen (8.1.1.5)
will generate an abstract class that cannot be instantiated programmatically.

Values provided in subclassSelectorValue are also used in polymorphic queries to
limit the selected objects to instances of a particular class. For example if you
get a list of objects of a particular class you may get objects of this class (provided that they are
not abstract) and all non-abstract classes that derive from it, either directly or indirectly.

The following table summarizes possible combinations of the above attributes:

subclassSelectorField

subclassSelectorValue

inheritFrom

Description

not defined

not defined

not defined

A class that does not participate in inheritance hierarchy

defined

defined

not defined

Root non-abstract class. Can have subclasses.

defined

not defined

not defined

Root abstract class (cannot create instances). Can have subclasses.

not defined

not defined

defined

Non-root abstract class. Can have subclasses.

not defined

defined

defined

Non-root non-abstract class. Can have subclasses. Can create instances.

defined

(any)

defined

Not allowed. Cannot define a class that has both subclassSelectorField and inherits from another class

not defined

defined

not defined

Not allowed. When providing subclassSelectorValue you need to also provide subclassSelectorField or inheritFrom

Sooda supports two models of inheritance mapping: table-per-hierarchy (TPH) and table-per-subclass (TPS) as described
in section "1.2.8. Inheritance"
.
They only differ in actual table names used to store fields in subclasses, so the configuration is similar. Assuming
the familiar table of WorkItems structure we can map it to classes using the provided schema:

The schema that maps this to a class hierarchy using TPS model is:

<schemaxmlns="http://www.sooda.org/schemas/SoodaSchema.xsd"><datasourcename="default"type="Sooda.Sql.SqlDataSource"/><!-- Root class of the inheritance hierarchy. --><!-- Note that it is abstract because it does not define subclassSelectorValue --><classname="WorkItem"subclassSelectorField="Type"><tablename="WorkItem"><fieldname="ID"type="Integer"primaryKey="true"/><fieldname="Type"type="Integer"/><fieldname="AssignedTo"type="Integer"/><fieldname="AssignedDate"type="DateTime"/><fieldname="DueDate"type="DateTime"/></table></class><!-- Concrete class stored in QuestionWorkItem table, identified by Type=1 --><classname="QuestionWorkItem"subclassSelectorValue="1"><tablename="QuestionWorkItem"><!-- primary key must be repeated for each table --><fieldname="ID"type="Integer"primaryKey="true"/><fieldname="Question"type="String"size="40"/><fieldname="Answer"type="String"size="40"/></table></class><!-- Concrete class stored in AnalysisWorkItem table, identified by Type=2 --><classname="AnalysisWorkItem"subclassSelectorValue="2"><tablename="AnalysisWorkItem"><!-- primary key must be repeated for each table --><fieldname="ID"type="Integer"primaryKey="true"/><fieldname="Subject"type="String"size="40"/></table></class><!-- Concrete class stored in BugWorkItem table, identified by Type=3 --><classname="BugWorkItem"subclassSelectorValue="3"><tablename="BugWorkItem"><!-- primary key must be repeated for each table --><fieldname="ID"type="Integer"primaryKey="true"/><fieldname="BugID"type="Integer"/><fieldname="BugDescription"type="String"size="40"/></table></class></schema>

The mapping in TPH model is very similar. You only need to change the table names, so
that they are all "TPH_WorkItem". We will also rename fields by prefixing them with acronyms
of concrete subclasses they belong to:

<schemaxmlns="http://www.sooda.org/schemas/SoodaSchema.xsd"><datasourcename="default"type="Sooda.Sql.SqlDataSource"/><!-- Root class of the inheritance hierarchy. --><!-- Note that it is abstract because it does not define subclassSelectorValue --><classname="WorkItem"subclassSelectorField="Type"><tablename="TPH_WorkItem"><fieldname="ID"type="Integer"primaryKey="true"/><fieldname="Type"type="Integer"/><fieldname="AssignedTo"type="Integer"/><fieldname="AssignedDate"type="DateTime"/><fieldname="DueDate"type="DateTime"/></table></class><!-- Concrete class stored in TPH_WorkItem table, identified by Type=1 --><classname="QuestionWorkItem"subclassSelectorValue="1"><tablename="TPH_WorkItem"><!-- primary key must be repeated for each table --><fieldname="ID"type="Integer"primaryKey="true"/><fieldname="Question"dbcolumn="QWI_Question"type="String"size="40"/><fieldname="Answer"dbcolumn="QWI_Answer"type="String"size="40"/></table></class><!-- Concrete class stored in TPH_WorkItem table, identified by Type=2 --><classname="AnalysisWorkItem"subclassSelectorValue="2"><tablename="TPH_WorkItem"><!-- primary key must be repeated for each table --><fieldname="ID"type="Integer"primaryKey="true"/><fieldname="Subject"dbcolumn="AWI_Subject"type="String"size="40"/></table></class><!-- Concrete class stored in TPH_WorkItem table, identified by Type=3 --><classname="BugWorkItem"subclassSelectorValue="3"><tablename="TPH_WorkItem"><!-- primary key must be repeated for each table --><fieldname="ID"type="Integer"primaryKey="true"/><fieldname="BugID"dbcolumn="BWI_BugID"type="Integer"/><fieldname="BugDescription"dbcolumn="BWI_BugDescription"type="String"size="40"/></table></class></schema>

Typically Sooda objects can be stored in a database without problems.
One notable exception are not-nullable fields which are null because there were not initialized yet.

In new Sooda objects, not-nullable fields of simple types are initialized with "zero" values:

Data Type

"Zero" Value

Integer

0

Long

0

Boolean

false

BooleanAsInteger

0

Decimal

0.0

Float

0.0

Double

0.0

DateTime

00:00:00.0000000, January 1, 0001 (DateTime.MinValue)

String

(empty string)

AnsiString

(empty string)

Blob

(zero-length blob)

Guid

00000001-0000-0000-0000-000000000000

TimeSpan

00:00:00

Image

(zero-length blob)

Note that not-nullable string fields are initialized with an empty string instead of a null.

Not-nullable reference fields cannot be initialized and pose a problem to temporarily
storing an object in a database.
Historically Sooda includes highly-configurable solution to this problem called precommit values.
At the schema level you can choose one of the strategies:

<defaultPrecommitValues>true</defaultPrecommitValues> is the default and means to use
"zero" precommit values for foreign keys, as specified in the table above.

<defaultPrecommitValues>false</defaultPrecommitValues> makes Sooda throw an exception
should there be precommit without a precommit value specified otherwise.

<defaultPrecommitValues>null</defaultPrecommitValues> is a new mechanism that makes Sooda
simply insert nulls on precommit. This however requires the database fields to be nullable.

<defaultPrecommitValues>null</defaultPrecommitValues> is recommended because of its simplicity.
Below we describe the whole mechanism of configuring precommit values.

There are four levels of precommit values, listed in the order of precedence:

per-field precommit value - to be used for a particular field

per-class precommit value - used for fields that reference to this class

per-datatype precommit value - you can decide the value for each data type. For example you
may want to store -1 for all not-null integer fields or 2000-01-01 00:00:00 for all DateTime fields

per-schema precommit strategy - as described above.

Per-field precommit values are defined on each <field /> using precommitValue attribute. The
following example defines a precommitValue for the User.Manager field.

In order to define per-class precommit value, you use the defaultPrecommitValue attribute on a class.
Typically you use this feature when your database uses foreign key constraints and defaultPrecommitValue
is a value of an object that is known to exist in the table.

You can use <precommitValue /> elements in your schema to define per-datatype precommit value. This example
defines a precommit value of -1 for Integer fields, ***NONE*** string for all String fields and ***NOTHING***
for all AnsiString fields. You should pick a value that you do not commonly search for in your queries.

Sooda provides a utility called SoodaStubGen (8.1.1)
that generates stub source code for
the Data Access Layer. The generated source code needs to be compiled with a standard C#/VB compiler to
produce a working assembly. This section describes the compilation process in detail.

It is recommended to run SoodaStubGen as part of your (preferably fully automated and repeatable) build process so Sooda supports
calling stub generator from a batch file, NAnt script or Visual Studio 2005/2008/2010 solution.

Sooda supports two compilation modes:

Standard Stub Compilation (4.1.1)
- stubs and business classes are compiled together in a single compiler pass.
This should be compatible with most languages and compilers, but can be slow for really large schemas (which generate huge stub classes). This mode also
causes Visual Studio to slow down.

Separate Stub Compilation (4.1.2)
- enables stubs to be precompiled to a separate *.dll,
which speeds up large builds but has some drawbacks which are described below.

Separate compilation of stubs is difficult because of cyclic dependency between stub classes and skeleton
classes, which exists because:

skeleton classes must inherit from stub classes

stub classes must return references to skeleton classes.

The following figure describes the problem. Note how Book_Stub class must be able to return references
to Person class and Person_Stub must be able to return Book objects. At the same time Book inherits
from Book_Stub and Person inherits from Person_Stub:

CAUTION Separate Stub Compilation relies on the ability of C# compiler to compile DLLs which
reference each other (cyclic dependencies). SoodaCompileStubs is used to ensure the proper bootstrapping
of such inter-dependent DLLs. Microsoft C# compiler is supported
but Mono Project Compiler (mcs) is not because it is unable to properly handle cyclic references.

The following figure provides an overview of the standard stub compilation process:

On input the process requires an XML document with O/R Mapping Schema (3)
, typically
stored in a file named SoodaSchema.xml. Arguments to SoodaStubGen (8.1.1)
can be either passed on the command line or through a special XML project file (4.2)
which
is easier to manage.

Build steps to produce business objects assembly are:

SoodaStubGen produces two files:

_Stubs.cs (the extension is different for other languages) - stub source file

_DBSchema.bin - resources file

It also generates one "skeleton" file for each class (3.2)
defined in schema.
The files are named ClassName.cs where ClassName is the name of the class.
The skeleton is only generated in the first run and is never automatically regenerated
You can add your own properties and business methods in the skeleton and your changes will be preserved
when rebuilding project. "Skeleton" inherits from a "stub" class so you get access to all its
properties and methods.

Compiler (such as csc.exe for C# or vbc.exe for Visual Basic.NET) compiles the skeleton and stub files
together to produce business objects assembly (*.dll).

Separate Stub Compilation is a process in which you precompile stubs to a separate DLL and
rebuild the DLL only when the schema changes. This can save compile time for very large schemas
and can also make Visual Studio run faster (for some reason Visual Studio 2005 does not perform very
well on very large source files which SoodaStubGen creates).

C# compiler is invoked to build OutputAssemblyName.dll from _MiniSkeleton.csx referencing
OutputAssemblyName.Stubs.dll built in the previous step.

C# compiler is invoked to build OutputAssemblyName.Stubs.dll from _Stubs.csx replacing
the DLL built in the first step. The newly compiled DLL references OutputAssemblyName.dll built in the
second step.

Binary version of SoodaSchema.xml.
Enables access to schema-level metadata at runtime. Must be embedded into the assembly that
contains business objects. Optimized for fast loading and small size by using binary serialization.

_Stubs.cs

output directory

No

Yes

Generated code for stub classes, list wrappers, factories and other opaque types that
should be compiled together to build a Data Access Layer.
More information about the generated code is available
in the Generated Code section (8.4)
.

The exact extension of this file matches the selected language and is cs for C# and vb for VB.NET.
Other CodeDOM providers have their specific extensions.

ClassName.cs

output directory

Yes

Yes

Skeleton classes. They are generated by SoodaStubGen (8.1.1)
the first time it is executed and are not regenerated later.

You can put your business methods in these files. There is one skeleton class for
each <class /> as defined in the mapping schema (3)
.
Each class N is stored in a file named "output directory/N.cs" (for C#)
and this cannot be changed by the user.

_Stubs.csx

output directory/Stubs

No

Yes

This file is used only in separate stubs compilation mode

Generated code for stub classes, list wrappers, factories and other opaque types that
should be compiled together to build a Data Access Layer stubs library.
More information about the generated code is available
in the Generated Code section (8.4)
.

_MiniStubs.csx

output directory/Stubs

No

Yes

This file is used only in separate stubs compilation mode

Miniature version of generated code for stub classes, list wrappers, factories.
Miniature stub classes do not rely on skeleton classes so they can be compiled separately
during the separate compilation bootstrap procedure (4.1.2)
.

_MiniSkeleton.csx

output directory/Stubs

No

Yes

This file is used only in separate stubs compilation mode

Miniature version of skeleton classes. The skeleton classes have no code other than
constructors and they inherit from the appropriate stub classes. This is a temporary file
and is used
during the separate compilation bootstrap procedure (4.1.2)
.

_Stubs.csx

output directory/Stubs

No

Yes

This file is used only in separate stubs compilation mode

Same as _Stubs.cs, but located in _Stubs.csx. This file is compiled to create the stubs
assembly.

OutputAssemblyName.Stubs.dll

output directory/Stubs

No

Yes

This file is used only in separate stubs compilation mode

The resulting stubs assembly. You should reference this file each time you reference
OutputAssemblyName.dll.

OutputAssemblyName.dll

output directory/Stubs

No

Yes

This file is used only in separate stubs compilation mode

Mini-skeletons assembly. Should not be removed or your application will not build properly.

Once you have generated the stubs you should compile the skeleton classes, stub classes and embedded resources
into a DLL. <csc /> task should be used for that. The following example is taken from Sooda
unit tests:

Automating Visual Studio builds using Sooda relies on the Pre-Build Event setting which invokes
SoodaStubGen.exe (8.1.1)
and optionally SoodaCompileStubs.exe (8.1.1.3)
.
It is possible to pass arbitrary command-line arguments to these commands, but it is recommended to use
Sooda Project Files (4.2)
which make this process a simpler to manage.

Sooda Project Files are used to control the generation of stub source code with the SoodaStubGen utility (8.1.1)
.
Project files are easier to read and maintain than command-line arguments passed to SoodaStubGen.
Visual Studio supports Intellisense so editing them is easy, even without documentation.

Sooda Project file must be saved in a file with *.soodaproject extension.
It is an XML file, whose root element is <sooda-project /> and its
XML Namespace is http://www.sooda.org/schemas/SoodaProject.xsd

Enabling this option causes the following prolog code to be emitted for all property
getters:

if (this == null)
returnnull;

Without this feature we would get a NullReferenceException when traversing a path
expression where one of the non-trailing elements is null. Consider this code:

OrgUnit o = this.OrganizationUnit.Parent.Parent.Parent;

Assuming that this.OrganizationUnit.Parent == null, this code will throw a
NullReferenceException when built without null propagation. When null propagation
is enabled, however, it will assign null to o without throwing an exception.

Some languages such as VJ# do not properly support inherited classes having the
same public static method with different return type (which would be needed to support inheritance
in Sooda). To remedy this problem, this option causes the methods to be moved to a separate class.

When <loader-class /> is set to false, you can use the following methods to load objects:

Specifies the method of embedding schema in the result DLL. Can be either Xml or Binary.
Binary causes the schema to be serialized in binary format, while Xml stores the schema in the XML format.
You should use Binary (which is the default), because it gives better startup times and smaller file sizes, but if you need to perform some
text-level parsing of the XML, you can choose Xml.

<embed-schema>Binary</embed-schema>

external-projects

array

Defines external projects (such as Visual Studio projects) that need to be updated each time a new class is added to the schema.
Each project is represented by <projecttype="..."file="..." />.
The following example XML defines a Visual Studio 2005 project:

The paths are relative to the output directory. The following project types are available:

vs2005 - Visual Studio 2005

null - no project file

Note that files (such as source files and embedded resources) can only be added to projects, they
are not removed automatically. If you remove a class from the mapping schema (3)
,
you need to manually remove the appropriate file from your project.

The following values are for nullable-representation and not-null-representation:

Name

Description

Boxed

Use boxing to represent nullable values. Null value is represented
as null reference, but a cast is required to get to get the value.
This representation is not recommended because of the lack of the type safety.

Sooda configuration files are usually managed by the .NET configuration mechanism and the parameters
are stored in application configuration files (App.config) as described
in section "5.1.1. App.config - style configuration"
.
If you have many programs that share runtime configuration, Sooda can be configured from a
shared XML configuration file. This feature is described
in section "5.1.2. Shared XML configuration"
.
If you have developed your own configuration mechanism, you can write a specialized Sooda
configuration provider for it as described
in section "5.1.3. Custom configuration"
.

By default Sooda configuration parameters are stored in standard .NET configuration files named
ApplicationName.exe.config or Web.config in a section named <appSettings />.
More information about .NET Configuration files is available in MSDN Library (http://msdn.microsoft.com/en-us/library/1fk1t1t0.aspx).

Each data source configuration entry has a form:

<addname="dataSourceName.parameterName"value="parameterValue"/>

dataSourceName is the name of the datasource (usually "default") and parameterName
is the name of the setting for that datasource. The value of the setting is stored in parameterName.
Possible setting names are described
in section "5.2. Data Source Configuration"
.

Some global options can also be set this way. Global options use the "sooda" prefix instead of a data source name.
They are described
in section "5.3. Global Configuration"
.

The following configuration file defines two settings for the "default" data source: connectionString and sqlDialect.

There are situations where multiple applications need to share the same set of configuration parameters.
Instead of keeping multiple copies of Sooda-specific configuration, you can store the settings in
a single XML file which is read by the applications.

The format of the file is very simple. Each parameter is enclosed in an XML element and nesting is used to
group parameters for a single datasource. The root element of the configuration file is not important,
which means you can embed the Sooda-specific XML elements in any XML file as long as you can
add your own elements.

The following example demonstrates XML configuration file equivalent to the App.config
shown above.

XML config files also support per-host overrides. Assuming the configuration file is
read from sooda.config.xml, Sooda looks for sooda.config.HOSTNAME.xml
where HOSTNAME is the name of the machine the application is running on. If the
host-specific configuration file is found, Sooda reads it as well and any settings specified in this
file take precedence over the common configuration.

Sooda looks for an XML configuration file in the application base directory. If the file is not found
the parent directory is scanned, then its grandparent and so on until the file is found or until
ten levels of directory hierarchy have been scanned or the root directory has been reached.
Assuming that your application is stored in c:\myprojects\mysystem1\app1\bin\Debug\app.exe
and the name of the configuration file is: sooda.config.xml,
and Sooda will look for it in the following locations:

c:\myprojects\mysystem1\app1\bin\Debug\sooda.config.xml

c:\myprojects\mysystem1\app1\bin\sooda.config.xml

c:\myprojects\mysystem1\app1\sooda.config.xml

c:\myprojects\mysystem1\sooda.config.xml

c:\myprojects\sooda.config.xml

c:\sooda.config.xml

As you can see, this is convenient because you can keep the settings for all applications making up
entire system in a single file (c:\myprojects\mysystem1\sooda.config.xml) and
all applications will be able to read it.

To use XML configuration files, add this line of code to the AssemblyInfo.cs
file of your entry assembly (http://msdn2.microsoft.com/en-us/library/system.reflection.assembly.getentryassembly.aspx).

If you have developed your own configuration mechanism, you can get Sooda to use it by creating a class
that implements the Sooda.Config.ISoodaConfigProvider interface and telling Sooda to use it.
The interface has only one method: string GetString(string) which must read a configuration
setting with the specified name. The setting name is of the form dataSourceName.parameterName.

To register your configuration provider in a declarative manner, you can either use an assembly-level attribute
or an application configuration file entry. Assuming you have created a class MyProvider
that implements ISoodaConfigProvider
interface, just add the following code to your AssemblyInfo.cs:

Control the SQL trace output depending on query time. If the query execution time is
below QueryTimeTraceInfo (floating point time in seconds), log output is emitted at the lowest possible
level (Trace). If the query execution time is between QueryTimeTraceInfo
and QueryTimeTraceWarn, the query is logged at the Info level. Queries
which take more than QueryTimeTraceWarn seconds to execute are logged at the Warn
level.

The default value of QueryTimeTraceWarn is 10 seconds
and QueryTimeTraceInfo defaults to 2 seconds.
The following configuration settings set the Info threshold to 0.5 seconds and Warn
threshold to one second.

Disables the use of SQL transactions. Setting this to true is
possible but NOT RECOMMENDED because precommit operation (6.10)
may modify the database itself. If the transactions are not used, such modifications cannot be rolled
back in case of any error which may lead to data corruption. The default value is: false.

When this parameter is set to true, Sooda logs SQL queries with whitespace stripped which makes them all single-line.
Log messages which have their whitespace stripped are easier to parse with simple utilities such as grep but
are less readable to a human. The default value is: false.

Specifies whether queries sent to the RDBMS should be indented with
the whitespace characters to represent their nesting levels.
Setting this to true makes queries more human-readable,
while setting it to false can reduce network traffic
between the SQL server and application host. The default value is: false.

Controls the use of ADO.NET parameters. When this parameter is set to
false (which is the default), Sooda replaces all literals
(such as numbers, strings, dates) with ADO.NET parameters. When this parameter
is set to true, some literals which are known to be safe
from the SQL injection (http://en.wikipedia.org/wiki/SQL_Injection) standpoint
are not passed as parameters which improves SQL statement readability.

Disables batched updates. If this parameter is set to true
update commands (such as INSERT, UPDATE, DELETE)
cannot be batched and are always executed in separate ADO.NET commands.
The default value for this true except for Microsoft SQL Server where it
is false.

When batched updates are enabled, SQL can send multiple semicolon-separated DML instructions.
When you insert five objects in a single transaction, Sooda might persist them in a single
batch:

Enables use of INNER JOIN in SQL queries,
which can be more effective than OUTER JOIN.
Defaults to false

sooda.oracleClientAutoCommitBugWorkaround

Enables a workaround for a bug in Microsoft's OracleClient in .NET 3.5 SP1.
The bug causes transactions to be autocommitted in certain conditions.
The bug and the workaround are described
here (http://social.msdn.microsoft.com/forums/en-US/adodotnetdataproviders/thread/d4834ce2-482f-40ec-ad90-c3f9c9c4d4b1/).

In order to support identity management, transactions implement an L1 cache of all alive objects. SoodaTransaction
is able to return a cached object given the (className,primaryKeyValue) pair.

No object can live outside the transaction and object state is not defined after the transaction
has been closed. Keeping a transaction open for a longer period of time is not recommended because it consumes
unmanaged resources, such as database connections.

Transactions need to be closed when they are no longer needed and if you fail to ensure that, otherwise
you may get all sorts of unmanaged resource leaks. Typical usage pattern for a Sooda transaction
that ensures the transaction is properly closed is presented below. It makes use of the
IDisposable pattern and the C# using() statement:

In order to support long-running transactions, Sooda supports a differential
serialization mechanism (6.6)
, which lets you persist uncommitted changes made within transaction to an XML document,
and deserialize them later, perhaps in a different process or a different machine.

Instances of SoodaTransaction must be created before any operation
on O/R-mapped objects. You need to properly close the transaction after you are
done with the objects. The best way to ensure that your transaction is closed
regardless of any exceptions that may occur is to use the IDisposable
pattern and C# using() statement:

Transactions need to be explicitly committed to properly save all changes made to objects.
If you fail to do so, the transaction is rolled back by default.

Each transaction must be associated with a mapping schema which is embedded as a resource into the
compiled DAL assembly. You need to configure the transaction to use the appropriate assembly.
There are several ways to do it depending on how you are planning to use your DAL:

By passing the assembly as an argument to SoodaTransaction constructor:

As mentioned earlier, every object which inherits from SoodaObject must be associated with an instance
of SoodaTransaction. There are three ways to materialize objects which will be discussed in further sections:

All these methods need a transaction object. It would be inconvenient
to require the transaction reference to be passed explicitly, therefore Sooda supports the concept of
implicit active transaction (returned by the SoodaTransaction.ActiveTransaction static property).
Implicit transaction is used when no explicit transaction is specified.

SoodaStubGen generates all APIs in two versions: one that takes SoodaTransaction object as
parameter and the other that assumes implicit transaction.

When you create a new SoodaTransaction object it is automatically
stored in SoodaTransaction.ActiveTransaction unless you
pass SoodaTransactionOptions.NoImplicit argument to the constructor.
The property is restored to its previous value when the transaction is closed/disposed,
which means you can nest transactions in an intuitive manner:

Sooda allows you to create more than one object in the transaction and will properly handle situations where
two newly created objects are dependent on one another. SQL INSERT operations
will be properly ordered when Commit() is called, to ensure that all foreign
key constraints are preserved.
The following example demonstrates this:

Sooda provides two ways to get a reference to an object given its primary key:

ClassName.GetRef() - gets a reference to an object without loading its data

ClassName.Load() - gets a reference to an object and loads its data

Lazy loading is a way to get a reference to the object without actually loading data from the
database. Property values will be loaded on first read access. Getting object reference is very fast
and should be used when we do not need actual object data, just its reference, as
described
in section "1.2.5. Lazy Loading"
.

The following example demonstrates basic concepts of lazy loading:

using (SoodaTransaction t = new SoodaTransaction())
{
Employee emp;
//////////////////////////////////////////// get a reference to an object - no database access here
emp = Employee.GetRef(3);
// first READ - this causes the object to be loaded// (roughly equivalent to SELECT * FROM Employee WHERE id = 3)
Console.WriteLine(emp.Name);
// display hire date - no need to access the database // as the data is already loaded
Console.WriteLine(emp.HireDate);
//////////////////////////////////////////// get a reference and load object data
emp = Employee.Load(5);
// no database access here - data is already in memory
Console.WriteLine(emp.Name);
// no database access here - data is already in memory
Console.WriteLine(emp.HireDate);
//////////////////////////////////////////// get a reference to another object
emp = Employee.GetRef(7);
// change the state of the object// we do not need to load the object at all!
emp.Active = false;
// commit changes. this sends the following SQL to the database:// UPDATE Employee SET Active=false WHERE ID=7
t.Commit();
}

Reference fields use GetRef() internally so that when you access a property that returns another object
this object usually starts in the Data Not Loaded state.

Note that it is not always possible to provide a reference to an object without loading its data, so GetRef()
may sometimes need to load the data. When you invoke GetRef() or Load() on an
object of class that has subclasses, Sooda needs to load a record from the database to check the concrete type
to instantiate. Fortunately Sooda has a Factory Cache mechanism to avoid loading
records more than once as described
in section "8.6. Factory Cache"
.

Sooda uses a query language called SOQL (7)
to select
objects that meet the specific criteria. To get a list of matching objects you
use the GetList() method.
To load a single object by SOQL query use LoadSingleObject().

GetList() method is overloaded to support various usage scenarios.
The most complex variant of the method takes five parameters, but SoodaStubGen (8.1.1)
also generates simplified variants of the GetList() method which only accept 1, 2, 3 or 4 parameters. There are
32 overloads generated for each class.

GetList(transaction, whereClause, topCount, orderBy, options);

The parameters are:

transaction - (optional) transaction reference, in case it is omitted SoodaTransaction.ActiveTransaction is used

topCount - (optional) maximum number of objects to retrieve - this is equivalent to SELECT TOP n SQL query

orderBy - (optional) specifies ordering of result objects

options - (optional) various flags

ClassName.GetList() returns an instance of ClassNameList which is a type-safe wrapper over
ISoodaObjectList and implements IList and IList<ClassName>.
More information about collections is given
in section "6.4. Collections"
.

For example, you can use the following code to run text-based SOQL query (7.1)
to fetch at most 5 Persons living in Redmond, WA:

Options you can pass to GetList() are flags defined in
the SoodaSnapshotOptions enumeration.:

Default - Default options

NoTransaction - Do not apply changes made in transaction. Use this option to perform a
database snapshot or when you are sure that none of transaction objects will affect the result.
This helps speed things up a bit.

VerifyAfterLoad - Verify after load. In case of a precommit it is possible that the result
list will include extra objects. Enabling this option causes the resulting objects to be re-evaluated
in memory

KeysOnly - Load only the primary key values. Objects will be materialized but fields other than primary keys will require a database query.

Cache - Fetch collection items from cache if possible.

NoCache - Do not load the collection from cache even if it would be possible to do so

PrefetchRelated - Use prefetch definition from the schema to load related objects automatically

The most commonly used option here is NoWriteObjects which can be used to disable precommit.
This example demonstrates passing this option to GetList(). Passing SoodaSnapshotOptions.NoWriteObjects
causes "Nancy Newcomer" not to be included in the list of returned objects. If we omitted this flag,
Sooda would precommit (6.10)
newPerson object by making the SQL INSERT and Nancy Newcomer would be
returned by the GetList().

One-to-many collections are returned by properties declared with <collectionOneToMany /> (3.6)
.
Many-to-many collections are returned by properties declared with <collectionManyToMany /> (3.7)
.
All other collection APIs return snapshot collections.

All collection types implement unified interface named ISoodaObjectList
but not all methods of this interface can be called in all cases. The table below summarizes that.

Creates a new collection with objects sorted according to the specified order expression ex. The order can be:

field name or path expression (1.2.6)
. The string can also specify "asc" or "desc" string
for ascending/descending specification (default is "asc") and can include
multiple fields to sort by.

Examples:

// sort by Name in ascending order
collection2 = collection.Sort("Name");
// sort by Name in descending order
collection2 = collection.Sort("Name desc");
// sort by Age in ascending order then by Name in descending order:
collection2 = collection.Sort("Age asc,Name desc");
// sort by the group manager's Name in ascending order:
collection2 = collection.Sort("Group.Manager.Name asc");

// sort by Name in ascending order
collection2 = collection.Sort(PersonField.Name);
// sort by Name in descending order
collection2 = collection.Sort(PersonField.Name, SortOrder.Descending);
// sort by the group manager's Name in ascending order:
collection2 = collection.Sort(PersonField.Group.Manager.Name, SortOrder.Ascending);

SoodaStubGen utility (8.1.1)
generates type-safe
wrappers for each class, called ClassNameList where ClassName is the name of the
<class /> defined
in section "3. O/R Mapping Schema"
. Collections returned from ClassName.GetList(),
one-to-many and many-to-many collections are wrapped with the appropriate type-safe wrappers.
The wrappers inherits from SoodaObjectCollectionWrapper or SoodaObjectCollectionWrapperGeneric<T>
and implements the following set of interfaces:

IEnumerable

IList

ICollection

ISoodaObjectList

IEnumerable<T> - .NET 2.0 and above

IList<T> - .NET 2.0 and above

ICollection<T> - .NET 2.0 and above

The following example shows the typical usage of collections.
As you can see the amount of boilerplate code is reduced to minimum:

Sooda guarantees reference identity for objects retrieved from a database. It means
that if we make 2 or more queries which retrieve a particular row from the
database in the same transaction (6.1)
they are guaranteed to be mapped to the same object (not just two objects that are equal).
It means that you can modify the object through either any of the references and
you will be modifying the same object, so the state is going to be consistent.
Mechanism that ensures object identity is called Level 1 (transaction-level) caching.

The following example demonstrates multiple ways to acquire references to objects
and the identity assertions that Sooda guarantees:

There are methods and properties of the SoodaTransaction class that let you to control
the object cache mechanism:

UseWeakReferences property - enables weak references to be used for L1 cache management. Setting this property to true
causes Sooda to keep track of unmodified objects in transactions using weak references. This makes them
subject to be garbage-collected when the memory is low.
Modified (dirty) objects are always registered using strong references.
Because unmodified objects can always be retrieved from the database, this approach is useful
when you are dealing with a large number of read-only objects that are cheap to reload from the database.

FindObjectWithKey(className,keyValue,expectedType) method - returns a cached object whose class name
is className and primary key is keyValue. If the object is not found in L1 cache or if the object
is not of expectedType, this function returns null.

GetObject(className,keyValueString) method - gets a reference to the object of the specified class
and primary key. The primary key is given as a string, so this method is best suited for scripting/web environment
where the primary key can be passed in a URL. This method does not pre-load object data
into memory, that is it is equivalent to ClassName.GetRef(keyValue) method.

LoadObject(className,keyValueString) method - same as GetObject(className,keyValueString)
but preloads object data into memory. It is an equivalent of the ClassName.Load(keyValue) method.

GetNewObject(className) - creates a new object of specified class. It is same as
creating a new instance of the object by calling the constructor and passing current transaction as an argument.

Transactions in Sooda are meant to be short-lived, because each SoodaTransaction manages
one or more open database connections which should be short-lived.
It is not recommended to keep the database connections open for a longer period of time because
it might negatively impact system scalability.

There are cases when long-lived transactions are desired, such as in a web application that
gathers user input from many web pages, makes incremental changes to the database and commits the transaction
once at the very end of the process. Sooda provides differential serialization
mechanism that allows you to capture the state of an open transaction and store it in an XML document.
This XML document can be persisted to some medium (such as a file, buffer in memory, ASP.NET Session
state or ViewState) and restored in a new transaction later.
The transaction can be restored in another thread, process, or even on another machine, as long as the target
environment has access to the original database.

The API for serialization and deserialization is very simple. It consists of methods named Serialize() and
Deserialize() respectively. The serialization code is presented below:

To deserialize the transaction in a new environment, just use the Deserialize() method:

using (SoodaTransaction tran = new SoodaTransaction())
{
// load the transaction from a filestring serializedXml = File.ReadAllText("c:\\temp\\transaction.xml");
tran.Deserialize(serializedXml);
// load the object
Customer c = Customer.Load(1);
// verify that deserialization worked
Assert.AreEqual("New Address, c.Name);
// All object modifications uncommitted in
// previous transaction are reflected here
// you can either commit them here or serialize again
// and deserialize in yet another place
tran.Commit();
}

The XML returned by the Serialize() method should be treated as opaque (you should not assume
anything about its structure), but is generally human-readable. Sample transaction dump is presented below:

Stub classes generated by SoodaStubGen utility (8.1.1)
are derived from
SoodaObject which provides some common methods and infrastructure for storing
object data, caching, lazy loading, triggers (6.8)
and more.
This section describes public API exposed by
SoodaObject class. More details are available in the source code comments.

SoodaObject exposes no public properties, only methods. All properties
visible from outside of the object are generated by SoodaStubGen (8.1.1)
.
Public methods of SoodaObject are:

bool AreFieldUpdateTriggersEnabled() - returns a value indicating whether field
update triggers (BeforeFieldUpdate and AfterFieldUpdate) are enabled.
More information about field update triggers can be found
in section "6.8. Triggers"
.

bool EnableFieldUpdateTriggers() - enables field update triggers and returns a boolean value indicating their state before enabling

bool DisableFieldUpdateTriggers() - disables field update triggers and returns a boolean value indicating their state before disabling

bool EnableFieldUpdateTriggers(bool flag) - enables or disables field update triggers based on the boolean flag. This function returns the previous state of triggers.

An ordinary pattern for disabling field update triggers for some region of code is presented below.

void ForcePostCommit() - forces the postcommit trigger (AfterObjectUpdate()) to be called even if there are no changes to object's internal state

bool IsInsertMode() - returns a value indicating whether the object is in insert mode (that is, it has been created in the transaction). Note that it does not indicate
that object has not been inserted into the database yet, because of possible precommit (6.10)
.

SoodaTransaction GetTransaction() - returns a reference to the transaction that manages this object.

void MarkForDelete() - marks the object for deletion and executes any cascade deletes on all objects that reference this object.
Sooda issues SQL DELETE statement as soon as objects are marked for deletion,
but since the transaction is not committed until the very end, this operation can be reversed.
Deleting objects immediately helps Sooda detect any foreign key constraint violations.

bool IsMarkedForDelete() - returns a value indicating whether the object has been marked for deletion.

bool IsFieldDirty(fieldNumber) - returns a value indicating whether the specified field (indicated by its ordinal) is dirty.

bool SetFieldDirty(fieldNumber,dirty) - sets or clears the specified field's dirty flag, which causes it to be saved to the database on commit.
Sooda normally manages the dirty flag automatically, but you might want to override it in certain situation. Use with caution.

bool IsObjectDirty() - returns a value indicating whether the object has been modified in the transaction. Note that resetting field statuses back to non-dirty does not automatically make the object non-dirty.
Only Commit() or Rollback() clears this flag.

ClassInfo GetClassInfo() - gets the ClassInfo object that include the schema-level metadata about the class. More information about ClassInfo is available
in section "8.5. Schema API"
.

string GetObjectKeyString() - returns a unique string that includes object's primary key. The string is of the form "ClassName[PrimaryKeyValue]", for example "Person[10]"
or "Employee[1234]".

object GetPrimaryKeyValue() - returns a primary key of the object. For scalar (single-columns) primary keys actual values (integer or string) are returned, for multi-column primary keys this function
returns an instance of the SoodaTuple class.

Sooda supports application triggers (as opposed to database triggers) which are fired each time one of the following events occurs:

object is inserted into a database

object is updated

object is deleted

field is modified

There are two variants of each trigger: "before" trigger and "after" trigger. Triggers are methods declared
in the SoodaObject class
that can be overridden in skeleton classes generated by the SoodaStubGen utility (8.1.1)
.
Overriding a "before" trigger allows you to prevent some operation (such as field modification) from happening
while "after" triggers are called after the operation has been already performed.

They object-level triggers have canonical names:

void BeforeObjectInsert() - fires immediately before an object is inserted into the database

void AfterObjectInsert() - fires after all objects have been inserted into the database and committed, once for each object.

void AfterObjectUpdate() - fires immediately before an object is updated

void AfterObjectDelete() - fires after all objects have been updated and committed, once for each object.

void BeforeObjectDelete() - fires immediately before the object is deleted

void AfterObjectDelete() - fires after the object has been deleted and committed

To prevent the field update from happening, you need to override the "before" trigger method and throw an exception from it.
The following example demonstrates overriding the triggers:

publicclass Person : Person_Stub
{
// constructors omittedprotectedoverridevoid BeforeObjectInsert()
{
base.BeforeObjectInsert();
Console.WriteLine("Object is about to be inserted.");
}
protectedoverridevoid AfterObjectInsert()
{
base.AfterObjectInsert();
Console.WriteLine("Object has been inserted.");
}
protectedoverridevoid BeforeObjectUpdate()
{
base.BeforeObjectUpdate();
Console.WriteLine("Object is about to be updated.");
}
protectedoverridevoid AfterObjectUpdate()
{
base.AfterObjectUpdate();
Console.WriteLine("Object has been updated.");
}
protectedoverridevoid BeforeObjectDelete()
{
base.BeforeObjectDelete();
Console.WriteLine("Object is about to be deleted.");
}
protectedoverridevoid AfterObjectDelete()
{
base.AfterObjectDelete();
Console.WriteLine("Object has been deleted.");
}
protectedoverridevoid BeforeFieldUpdate_Age(object oldValue, object newValue)
{
base.BeforeFieldUpdate_Age(oldValue, newValue);
Console.WriteLine("Modifying field Age from {0} to {1}", oldValue, newValue);
if (newValue.Equals(42))
{
// this exception prevents the field update from happening when the user// attempts to change Age to 42.thrownew Exception("Cannot set Age to 42!");
}
}
protectedoverridevoid AfterFieldUpdate_Age(object oldValue, object newValue)
{
base.AfterFieldUpdate_Age(oldValue, newValue);
Console.WriteLine("Field Age has been modified from {0} to {1}", oldValue, newValue);
}
}

Object-level triggers are always available and cannot be disabled. You can disable field-level triggers
(BeforeFieldUpdate_xxx and AfterFieldUpdate_xxx) either at stub generation stage or at runtime.
The former option is useful to reduce the amount of generated code, because stub classes can get pretty large
when you declare many fields in a class.

SoodaStubGen (8.1.1)
provides two ways to disable the generation of field-level triggers. You can
disable them at the class level, by specifying <classtriggers="false" /> and you can enable
them individually at the field level by specifying <fieldforceTrigger="true" />. For example:

This declaration will cause the following trigger methods to be generated:

Employee_Stub.BeforeFieldUpdate_Name()

Employee_Stub.AfterFieldUpdate_Name()

Employee_Stub.BeforeFieldUpdate_Salary()

Employee_Stub.AfterFieldUpdate_Salary()

Employee_Stub.BeforeFieldUpdate_HireDate()

Employee_Stub.AfterFieldUpdate_HireDate()

Employee_Stub.BeforeFieldUpdate_Active()

Employee_Stub.AfterFieldUpdate_Active()

Vehicle_Stub.BeforeFieldUpdate_Description()

Vehicle_Stub.AfterFieldUpdate_Description()

The following trigger methods will not be generated because of the triggers="false" attribute of <classname="Vehicle" />:

Vehicle_Stub.BeforeFieldUpdate_Price()

Vehicle_Stub.AfterFieldUpdate_Price()

Note that Sooda does not generate triggers for primary key fields. That is because they are immutable - you cannot the
value of a primary key once it has been set, as it would break the rules of reference identity. It would lead to a situation
where two different objects would have the same primary key values which is forbidden.

Key generation is a process of generating unique identifiers to be used for primary keys of newly created objects.
Because primary keys must be known before objects can be committed, you cannot use SQL Server identity columns for key
generation. By default Sooda uses a table-based key generator that manages primary keys by storing their values in
a special database table, but you can write your own generator by creating a class that implements
the IPrimaryKeyGenerator interface.

Table-based primary key generation uses a database table (called KeyGen by default)
with 2 columns: key_name and key_value which store class name and
next primary key value to be generated respectively:

Because keys are stored in the database table, generating them requires two SQL statements to be executed. One is SELECT to
fetch the current value of the primary key, the other one is to update the database table so that next
generation will not use the same value again. Because of this cost, Sooda allocates keys in batches of ten (the number is
configurable) so it only needs to make 2 round-trips to the server for every ten new keys generated.

This algorithm is multi-process safe, so multiple processes can create new objects concurrently,
but it does not guarantee that the primary keys of newly created objects will be monotonously increasing.

You can configure the name of the table and batch size on a per-datasource basis
by using the configuration parameters:

datasourcename.keygentable.name - name of the keygen table (by default (KeyGen)

datasourcename.keygentable.keycolumn - name of the columns that holds key names (by default key_name)

datasourcename.keygentable.valuecolumn - name of the columns that holds values (by default key_value)

datasourcename.keygentable.pool_size - the number of keys to allocate at one (by default 10)

The following example demonstrates using application configuration file to override default name
of the KeyGen table and names of its columns.

Sooda supports the GetList() method to get the list of objects matching the specified criteria. It is not possible
to calculate the list in memory because it would be very inefficient, therefore the criteria written in SOQL
(textual or typed) are converted to SQL and executed by the database engine.
Objects in transaction can be modified, created and deleted and RDBMS needs to be notified of all
such modifications or it will return an outdated list of matching objects.

The condition true passed to the GetList() method is always met, so you expect
it to return all objects of class Person including the newly created 'Nancy Newcomer' object.
GetList() ultimately results in an SQL query, whose result should
include the mentioned object. For this reason, Sooda needs to issue an INSERT INTO Person statement
before executing the SELECT statement on the database.

In general, Sooda ensures that any modifications made in transaction, which can affect the query results,
are saved to the database before the query is executed. Objects that need to be precommitted are:

all objects of the queried class (Person in the above example) and its subclasses

all objects of classes mentioned in the WHERE clause and their subclasses

all uncommitted, dependent objects of the above which are in insert mode

For example, the following code causes all objects of Person,
Group and OrganizationUnit classes to be precommitted because they are mentioned
in the WHERE clause

To disable precommit, you need to pass the SoodaSnapshotOptions.NoWriteObjects option
to the GetList() method. To manually cause a precommit at any time, you can
invoke the SoodaTransaction.SaveObjectChanges() method.

Note that if your query contains a RAWQUERY() clause, determining the list of
objects to be precommitted may not be possible, so all objects are precommitted to ensure consistent
results.

Precommitting objects can become difficult if we have non-nullable fields in the database and
data for these fields has not been provided yet (this is only possible when the object is in insert mode).
When inserting such objects, Sooda needs to provide SOME value for the column, otherwise the insert will fail.
You can specify what values should be written to the database for each column type, referenced table or you can
set them on a per-field basis as described
in section "3.10. Precommit Values"
.
It is important to specify correct precommit values for all referenced tables if you
use foreign key constraints in the database. If you do not specify the precommit values, Sooda will insert
the default value of zero into foreign key columns which may violate the constraints.

Level 2 Cache - which optimizes database access by caching results of SQL queries that read single objects and collections

First level of cache is totally transparent to the user, while the second level of cache is configurable
and may need to be tuned for the best performance. There are two aspects of the cache mechanism that are tunable:

caching policy

cache storage

Caching policy determines what objects should be stored in cache and how long they are kept there.
There are many possible approaches here:

none - do not use Level 2 cache

all - all objects are subject to be cached - useful for small databases (up to 1 GB)

small - cache small cardinality objects, large and medium cardinality objects are always reloaded from the database

smallmedium - cache small and medium cardinality objects, large cardinality objects are always reloaded from the database

Objects are considered Small, Medium or Large depending on the
cardinality setting in the mapping schema (3)
.

In addition to this, you can also create your own caching policy by creating a class that implements
the ISoodaCachingPolicy interface. Your caching policy can use caching hints (cached,
cacheCollections, cardinality as defined in the schema) or your algorithm to decide
which objects or collections should be put into cache.

To configure the caching policy for your application, just set the sooda.cachingPolicy key in the
configuration file (5.1)
to one of the predefined policy names or use the name
of your own class which implements the ISoodaCachingPolicy. To configure expiration timeout and
sliding expiration use sooda.cachingPolicy.expirationTimeout (timeout in seconds) and
sooda.cachingPolicy.slidingExpiration (boolean true or false) respectively.
The following example configures caching of small and medium objects, with the expiration timeout of one
hour and sliding expiration set to true:

<configuration><appSettings><!-- cache small and medium objects --><addkey="sooda.cachingPolicy"value="smallmedium"/><!-- retain objects for 3600 seconds - one hour --><addkey="sooda.cachingPolicy.expirationTimeout"value="3600"/><!-- each access to an object resets the one-hour timeout --><addkey="sooda.cachingPolicy.slidingExpiration"value="true"/></appSettings></configuration>

Alternatively you can configure caching in code, by setting the static property called CachingPolicy
of the SoodaCache class:

Newly created transactions default to the caching policy stored in SoodaCache.DefaultCachingPolicy, but
you can override it for each transaction separately. To disable caching for a particular transaction, use this code:

using (SoodaTransaction t = new SoodaTransaction())
{
// don't use caching in this transaction
t.CachingPolicy = new SoodaNoCachingPolicy();
}

You can implement other cache storage mechanism by creating a class that
implements the ISoodaCache interface. It should be possible to integrate with commercial
cache engines through this interface for great scalability.

To configure cache storage mechanism, just set the sooda.cache parameter in in the configuration file to
one of the mentioned caching implementations. You can also specify a fully qualified name of the class that implements
ISoodaCache interface:

Sooda supports pluggable data sources. In theory it should be possible to implement support backends
other than relational databases, but current version of Sooda only supports SQL Data sources.

SQL Data source provides a way to programmatically execute SOQL queries against relational databases. It is
also used internally by Sooda to provide low-level mapping functionality such as loading single
objects, collections and saving changes back to the database.
It currently supports the following RDBMSes:

mssql - Microsoft SQL Server 2000, 2005, MSDE and SQL Server Express

postgresql - PostgreSQL 8.x and higher

oracle - Oracle 8i and higher

mysql4 - MySQL 4.x and higher

SQL Data Source is implemented in Sooda.Sql.SqlDataSource class.
To use it in the mapping schema (3)
, declare a <datasource /> with a type
of Sooda.Sql.SqlDataSource:

Note that at this point, you do not specify the database connection type, connection string or SQL
dialect - these things are configured at runtime, so that applications can run unmodified with
a database engine of choice. Data source runtime configuration is explained
in section "5.2. Data Source Configuration"
.

There are two ways to acquire a SqlDataSource object, depending on whether you
have an open transaction. If you do, getting the SQL data source is a matter of calling OpenDataSource()
method on a SoodaTransaction instance, passing the name of the data source to open.
SoodaTransaction manages a list of open data sources and will return the same data source object when OpenDataSource()
is called with the same data source name twice.

using (SoodaTransaction t = new SoodaTransaction())
{
// get a reference to open datasource
SqlDataSource ds = (SqlDataSource)t.OpenDataSource("default");
// you do not need to (and should not) close the data source // explicitly it will be closed when the transaction terminates.
}

If you are not within the context of a transaction or you want to create a
detached SqlDataSource object, create a new object with new
passing the data source name as an argument to the constructor. To make sure that the SQL Data Source
is properly closed, it is best to use the C# using() statement:

using (SqlDataSource ds = new SqlDataSource("default"))
{
}

Before you can use the SqlDataSource object, you need to initialize its properties.
If you pass the name of the datasource in the constructor invocation, default values for
the properties are read automatically from the configuration file as
described
in section "5.2. Data Source Configuration"
. Once the properties have been initialized,
you should call the Open() method to connect to the database.

The following methods and properties of the SqlDataSource class can be invoked from user code:

Open() method.

Opens the data source which connects to the database and opens a new transaction (unless DisableTransactions has been set to true).

IsOpen property.

Returns true if the connection is open, false otherwise

Rollback() method.

Performs rollback of the open transaction and starts a new one. This method does nothing if DisableTransactions is set to true.

Executes SOQL query represented as SoqlQueryExpression, which can be retrieved
from a textual representation by calling SoqlParser.ParseExpression(soqlText).
The schema parameter is an instance of the SchemaInfo (8.5.1)
class
and parameters is an array of values to be used for
{0}, {1}, {2} ... parameter placeholders. See below for an example
use of this method.

Executes raw SQL query on an open data source. parameters is an array of values to be used for
{0}, {1}, {2} ... parameter placeholders.
Other than that, this method is equivalent to IDbCommand.ExecuteReader().

int ExecuteNonQuery(string queryText, object[] parameters); method

Executes raw SQL command that does not return any records (such as SQL INSERT or UPDATE)
on an open data source. parameters is an array of values to be used for
{0}, {1}, {2} ... parameter placeholders.
Other than that, this method is equivalent to IDbCommand.ExecuteNonQuery().

There are other public methods available in the SqlDataSource class, but they are reserved for
Sooda internal use. The following example demonstrates the most typical use of SqlDataSource which is executing
an SOQL query and iterating over the result set:

In addition to fields defined in the mapping schema, Sooda supports creation and deletion of fields at runtime.
Fields created at runtime are called dynamic.
Dynamic fields are defined per-class and have the following properties:
name, type (optionally a reference), nullability and, for selected types, size and precision.
Dynamic fields can be accessed using a string indexer (obj["MyField"])
or the .NET 4 dynamic type. The indexer can be used in LINQ queries (7.6)
.

Definition of dynamic fields is stored in the database table called SoodaDynamicField
while field values are stored in separate tables, one table per dynamic field.
A table for dynamic field values is created when the field is added, and dropped when the field is removed.
The name of the table is the parent class and the field name combined with an underscore, e.g. MyClass_MyDynamicField.
Dynamic field table usually contains two columns: id and value,
where id is the object primary key and value is the value of the dynamic field.
There are more columns (id2, id3, ...) if the primary key is composite.
To conserve storage, null values are not stored in the database. Therefore the value column is never nullable.

The last step is re-generating stubs from the modified mapping schema.

Now your application supports dynamic fields.
The first time it creates a SoodaTransaction, it will query the SoodaDynamicField table
and use the dynamic fields defined there. When you add dynamic fields with the Sooda API, this table will get updated
and the dynamic field tables will get created or dropped.

Instead of setting DataType and References, you can set TypeName.
It is either the string representation of DataType or the name of the referenced class without the namespace.

Another alternative is setting the Type property. For value types, it also sets nullability, e.g. typeof(int) is non-nullable
while typeof(int?) is nullable. You still need to set IsNullable for string and reference types.
Setting Type to typeof(string) will select the String data type and never an AnsiString.
typeof(bool) selects BooleanAsInteger and not Boolean.

Once your FieldInfo contains all the required information, pass it to the Add method
of DynamicFieldManager, together with a SoodaTransaction:

Sooda.Schema.DynamicFieldManager.Add(fieldInfo, transaction);

Adding a dynamic field changes the internal structure of the objects.
Therefore, until you are finished with adding dynamic fields, you must not use any objects of the class you modify.
Currently this requirement is not verified by Sooda and failing to fulfill it can lead to exceptions and data corruption.
An exception from this rule is that you can read objects which won't be modified in this transaction.
Same applies to removing a dynamic field.

Dynamic fields can be accessed with an indexer, passing a field name as a string:

object value = obj["MyField"];

For value types, the indexer always returns a boxed object which can be unboxed by casting
to a raw value type or a Nullable type. That is, the indexer never returns an SqlType.
If a dynamic field is added when there are already objects of the parent class,
the field values for existing objects will be null, even if the field is non-nullable.

When you assign a dynamic field:

obj["MyField"] = value;

the type and nullability of the field are validated.
ArgumentNullException will be thrown when null is assigned to a non-nullable field.
InvalidCastException will be thrown when value type is invalid.
On setting field value, BeforeFieldUpdate(fieldName, oldValue, newValue)
and AfterFieldUpdate(fieldName, oldValue, newValue) triggers will be called if enabled.

In addition to dynamic fields, the indexer can be also used to read (but not write) fields defined in the mapping schema.

Alternatively to the indexer, you can access dynamic fields with the .NET 4 dynamic type:

Sooda supports a query language named SOQL which is similar to SQL (Structured Query Language)
enhanced with path expressions, collections, named constants and inheritance support. Only SELECT operation is
supported which means no INSERT/UPDATE/DELETE support exists in SOQL.

SOQL queries are based on classes and fields as opposed to tables and columns. You should use names defined in the mapping schema (3)
with <classname="..." /> and <fieldname="..." />. Let's assume you have the following class definition that
maps table ctct to class Contact and uses field names different from column names:

You can write the following SOQL statement that gets the name of some particular contact:

select Name
from Contact
where ID=123

It is equivalent to the following SQL query:

select contact_name
from ctct
where contact_id = 123

SQL-like query from multiple classes with JOIN specified in the where clause is supported but rarely needed
because Sooda supports path expressions (7.2.1)
which greatly
simplify query code. There are other useful extensions to SQL, as described
in section "7.2. SOQL Language Elements"
.

SOQL is case-insensitive on keywords, class names and property names.
For readability, it is recommended to use the original casing when
referring to fields and classes. Case sensitivity of string comparisons and SOQL LIKE operator are database-dependent.

Path expressions let you traverse many-to-one and one-to-one relationships
without JOIN operators. To access a property of a related object you simply prefix the name of the property
with the path of dot-separated property names that leads to the object. Path length is unlimited.

Here is an example of a SOQL query that retrieves all contacts who belong to groups managed by 'Mary Manager':

Sooda supports one-to-many and many-to-many collections in queries. SOQL queries
can get the size of a collection as well as check if a particular
object is contained in the collection. The following operations are supported:

collection.Count - get the size of a collection

collection.Contains(object) - check whether a particular object is included in the collection

collection.Contains(ClassName where condition) - check whether any object from the collection matches the specified condition

To get the number of elements in the collection, just append the .Count after the collection name:

-- select all contacts that have more than one roleSELECT *
FROM Contact
WHERE Roles.Count > 1

Using Count is supported in select expressions, not just in the WHERE clause:

-- return the name of each person followed by the number -- of members in his groupselect Name,
PrimaryGroup.Members.Countfrom Contact

To check if a given collection contains a particular object, use the Contains(object).
The object can be a value of a object field, a named constant or a literal value (an integer or a string):

-- get the names of all groups that Mary Manager belongs to-- Contact.Mary is a named constantselect Name
fromGroupwhere Members.Contains(Contact.Mary);

The same condition can be specified by using the value of 1 (the primary key) instead of Contact.Mary.

-- get the names of all groups including Mary Managerselect Name
fromGroupwhere Members.Contains(1);

More complicated queries are possible of course:

-- return all groups where the manager-- is not one of the membersselect *
fromGroupwherenot Members.Contains(Manager)

The argument of Contains() may be a subquery or a simplified sub-query (7.2.4)
.
The following query returns a list of all groups which have at least one female member:

select *
fromGroupwhere Members.Contains(Contact where Sex = 'F')

Contains() expressions can be nested:

-- find all groups which have at least one contact who is -- in a role whose name starts with 'Admin'select *
fromGroupwhere Members.Contains(Contact
where Roles.Contains(Role where Name like'Admin%'))

You can see that Count and Contains() are
quite powerful constructs that let you write simple and complex queries without
the use of explicit joins. Join-free SOQL queries can be translated to SQL
thanks to a translator provided by Sooda described
in section "8.3. SOQL to SQL Conversion"
.

SoodaClass is a virtual property that is present in all Sooda classes. It returns name
of the class as defined in the mapping schema (3)
.
It can be used to distinguish between subclasses in polymorphic queries.

SOQL provides a convenient notation for specifying subqueries that are used as arguments to EXISTS() and Contains()SELECT id FROM class WHERE condition can be shortened to Class WHERE condition. Thus the following two
SOQL queries are equivalent:

-- Using full subqueriesselect *
fromGroupwhere Members.Contains(select ID from Contact where Name = 'Mary Manager')

Raw queries can be used to take advantage of RDBMS-specific SQL constructs, such as full-text-search, custom
operators and built-in functions. Raw queries are passed unmodified by the SOQL to SQL Conversion (8.3)
.

To write a raw query, simply use RAWQUERY(text). The text can be any SQL, but you
must be careful to correctly balance parenthesis. RAWQUERY() may also be a potential security risk
if you pass unvalidated user input as raw queries.

SOQL expressions can be used to filter objects returned by the GetList() or
LoadSingleObject() methods. You simply pass the WHERE clause as a string
argument to the constructor of the SoodaWhereClause class and pass the
constructed where clause object to the appropriate method. You cannot specify a list of columns here, GetList()
and LoadSingleObject() always operate on objects.

Manually concatenating string literals can be dangerous and lead
to cross-site scripting vulnerabilities (http://en.wikipedia.org/wiki/Cross-site_scripting) in web applications.
Fortunately Sooda lets you separate string literals from the rest of the query code.
You can use the {0}, {1}, {2} notation similar to Console.WriteLine().
Note that you do not need to quote the strings yourself nor surround {0} with apostrophes. In fact
this would cause the '{0}' string to be treated literally and not as a parameter reference.

Textual queries passed to SoodaWhereClause have one deficiency: property/field names
that you use are not validated at compilation time. It is possible to write code that contains
references to non-existing fields. The compilation will succeed, but you will get runtime
errors.

When developing large systems that change often (such as workflow applications with
ever-changing customer requirements) it may be beneficial to statically validate all queries used in
the application to make sure that you only reference correct fields. This way, when you remove a field
from the schema or change its type, the application will simply not compile and the compiler will show
you statements that need to be corrected because of the original change.

Sooda implements typed SOQL queries, which let you write SOQL-like expressions in pure C# using a technique
called operator overloading. Sooda comes with a large set of classes that represent nodes in query expression
trees (such as relational operators, path expressions, Count, Contains, boolean and,
or, not and so on).
These classes overload standard C# operators such as +,-,&&,
|| so that they return elements of the syntax tree instead of performing actual computations.

To properly support path expressions, some schema-specific code needs to be generated as well
which SoodaStubGen utility (8.1.1)
takes care of. It generates a set of
classes that let you build typed path expressions and typed collection expression builders. They both
let you express almost all SOQL features. In some rare cases (such as SQL functions or subqueries)
you can resort to text-based SOQL and even combine the two approaches in a single query.

Typed queries are very concise, because you do not need to wrap them with strings or use
any special objects (such as SoodaWhereClause) that will parse them. You do not need
to use any form of string escaping and because there is no parsing involved whatsoever you can be
sure that the code is cross-site-scripting-safe.

The following table compares features of typed queries and text-based SOQL. The syntax for typed queries
and operator precedence is based on the C# language. Note that other languages that support
operator overloading may use different notation and/or different operator precedence.

SOQL

Typed Query

Remarks

path expressions

path expressions prefixed with ClassNameField

You need to prefix all path expressions in typed queries with the ClassNameField where ClassName is
the name of the class the expression applies to. For example:

ContactField.PrimaryGroup.Manager.Name

is equivalent to the following SOQL expression:

PrimaryGroup.Manager.Name

=

==

Equality operator

<>

!=

Inequality operator

<=

<=

Less than or equal operator

<

<

Less than

>

>

Greater than

>=

>=

Greater than or equal to

and

&&

Boolean conjunction

or

||

Boolean alternative

not

!

Boolean negation

+

+

Addition or string concatenation

-

-

Subtraction

*

*

Multiplication

/

/

Division

%

%

Modulus

-

-

Unary minus

like

Like() method

LIKE relational operator. Because C# does not support the definition of extra operators this is a method.
The following C# expression:

ContactField.Name.Like("Mary%");

is equivalent to the expression in SOQL:

Name like'Mary%'

Count

Count

Gets the number of elements in a collection.

Contains(obj)

Contains(obj)

Determines whether obj is contained in the collection.

Contains(X where condition)

ContainsXWhere( condition )

Determines whether any object contained in the collection meets the specified condition.

is null

IsNull() method

Determines whether the expression is null. This typed query predicate can be only used on nullable fields. It fails to compile when used on fields that are known to be not-null.
The following C# expression:

ContactField.PrimaryGroup.Manager.IsNull()

is equivalent to the expression in SOQL:

PrimaryGroup.Manager isnull

is not null

IsNotNull() method

Determines whether the expression is not null. This typed query predicate can be only used on nullable fields. It fails to compile when used on fields that are known to be not-null.
The following C# expression:

ContactField.PrimaryGroup.Manager.IsNoNull()

is equivalent to the expression in SOQL:

PrimaryGroup.Manager isnotnull

in (a1,a2,...,aN)

In(a1,a2,...,aN) method

Determines whether the left hand side of the expression equals to any value on the right hand side
of the expression.
The following C# expression:

Gets the concrete class name of the object as defined in the mapping schema (3)
.

The following typed query retrieves a list of vehicles which are bikes or boats.

Vehicle.GetList(VehicleField.SoodaClass.In("Bike","Boat"))

It is equivalent to this SOQL query:

select *
from Vehicle
where SoodaClass in ('Bike','Boat')

in (subquery)

not supported

This feature is not supported in typed queries. You can use SoqlParser to parse it from a textual representation.

exists (subquery)

not supported

This feature is not supported in typed queries. You can use SoqlParser to parse it from a textual representation.

positional parameters: {0}, {1}, {2}

Soql.Param(0), Soql.Param(1), Soql.Param(2)

Reference to the positional parameter passed to the SoodaWhereClause constructor.
Positional parameters are not usually needed in typed queries because string literals
are safe by default.

Examples of queries that can be formulated with typed queries include:

// find all contacts named 'Mary Manager'
Contact.GetList(ContactField.Name == "Mary Manager");
// find all contacts whose name starts with 'Mary' (using LIKE operator)
Contact.GetList(ContactField.Name.Like("Mary%"));
// find all employees hired in the last year
Employee.GetList(EmployeeField.HireDate > DateTime.Now.AddYears(-1));
// find all groups having at least one contact
Group.GetList(GroupField.Members.Count > 1);
// find all groups that Mary Manager is a member of
Group.GetList(GroupField.Members.ContainsContactWhere(ContactField.Name == "Mary Manager"));
// find all employees
Employee.GetList(1 == 1);
Employee.GetList(true);
// return an empty list of employees by executing// a database query with a WHERE clause that is never true.// The same thing can be achieved without a database query// by creating a new EmployeeList() object.
Employee.GetList(false);
// return a list of contacts who do not belong to any group
Contact.GetList(ContactField.PrimaryGroup.IsNull());
// find all contacts where Name is different from (FirstName + ' ' + LastName)
Contact.GetList(ContactField.Name != ContactField.FirstName + " " + ContactField.LastName);
// find all groups managed by Mary Manager (using named constant)
Group.GetList(GroupField.Manager == Contact.Mary);

Sooda now supports LINQ, which has become the standard query language in .NET 3.5.
LINQ has all the benefits of Typed Queries, which existed in Sooda before LINQ appeared.
It is recommended that you use LINQ for all new code.
Typed Queries remain in Sooda for backward compatibility.

LINQ methods described below become available on LINQ sources
as you import the System.Linq namespace
(note System.Linq and not Sooda.Linq).
This is necessary for all LINQ queries except for the trivial cases such as the above.

There are two ways of writing LINQ queries: method calls and LINQ keywords.
These two are equivalent: the C#/VB.NET compiler translates LINQ keywords to method calls.

Let's start with method calls. Most LINQ methods accept lambda expressions
where the only parameter is the so called range variable.

Contact.Linq().Where(c => c.Name == "Mary Manager");

In the above code, the Where() method is called with the lambda expression
c => c.Name == "Mary Manager".
In this expression, c is the range variable and
c.Name == "Mary Manager" is the body of the lambda expression.
This code is equivalent to:

Contact.GetList(ContactField.Name == "Mary Manager");

The body of the lambda expression is specified in C#.
Therefore you get the advantages of compile-time type checking which can detect
errors such as misspelled property names and type mismatch
(e.g. comparing an integer field to a string).
However, you need to be aware that this code gets translated to SQL
and executed by the database engine. If you use a construct that cannot be
translated to SQL, you will get a runtime error.
Therefore you must write lambda expressions carefully.
A section below (7.6.6)
lists the supported constructs.
When in doubt, always test your code.

The Where() method that we saw in the example is used to filter rows,
just like SQL WHERE.

Here, the list is first sorted by ContactId and then sorted again by Name.
The result is that Name has priority for ordering.
To write multiple order expressions as you are used to,
use ThenBy() and ThenByDescending():

Contact.Linq().OrderBy(c => c.Name).ThenByDescending(c => c.ContactId);
// same result as in the previous example

Use GroupBy() for aggregate queries. GroupBy() must be followed by a Select()
and possibly OrderBy() and Where(). The result of GroupBy() is a sequence
of IGrouping<TKey, TElement>, of which you can access:

As a rule of thumb, if you need to iterate LINQ query results more than once,
you should materialize the results to a collection and then iterate over this collection.
The most efficient way is calling ToList() to get a generic list.
Alternatively you may call ToArray():

Contact[] ca = Contact.Linq().OrderBy(c => c.Name).ToArray();

Using LINQ with Sooda lists, such as ContactList, is discouraged.
However you may need them for interoperability with legacy code.
To convert LINQ query results to a Sooda list, import Sooda.Linq
(in addition to the usual System.Linq), call ToSoodaObjectList()
and pass the result to a Sooda list constructor:

The key thing here is the type of the query variable.
If you want LINQ methods to build the query for the database,
use var or IQueryable<...>.
In contrast, the following code will fetch all Contacts
from the database and do the filtering on the .NET side
(using LINQ2Objects):

Deferred query execution applies only to methods that return a sequence of objects.
Below are described methods that return single objects (7.6.4)
and scalar results (7.6.5)
- these methods actually execute queries
instead of constructing it.

This code will return a person with highest last salary.
It throws InvalidOperationException if there are no Contacts.
If you want a null instead, use FirstOrDefault()
(origin of this name: default value for reference objects is null).
You may also want to use Last() or LastOrDefault():

Single() throws an InvalidOperationException
if there isn't one match. SingleOrDefault will return
null if there are no matches, but still throw
InvalidOperationException if there is more than one match.

You almost certainly need a filter for Single(),
thus this method is overloaded:

Contact c = Contact.Linq().Single(c => c.Name == "Mary Manager");

There are also overloads of First(), FirstOrDefault(),
Last(), LastOrDefault() that accept a filter.

In total, there are six methods, each with two overloads. The pattern is:
{First|Last|Single}[OrDefault]([filter])

The "OrDefault" methods can be used in subqueries.
The following code lists a name of the top paid Contact for each ContactType:

Here the SQL translation of myVariable.MyMethod().MyOtherMethod() is just a string literal
that is the result of this expression computed in .NET.
Remember about deferred query execution (7.6.3)
.

To use the SQL LIKE operator, you need to import the Sooda.Linq namespace
(in addition to the usual System.Linq):

using Sooda.Linq;
Contact.Linq().Where(c => c.Name.Like("C%"));
// as a bonus, the Like() method becomes available on all .NET stringsbool ok = "Foo".Like("%o"); // computed in .NET, not the database engine

Collection sizes are available either through the Count property
or the Count() LINQ extension method:

The following string methods may be used: Concat(),
IsNullOrEmpty(), Remove(),
Substring(startIndex, length),
Replace(), ToLower() and ToUpper().
StartsWith(), EndsWith() and Contains()
can be used with the restriction that the argument cannot come from the database.

You can call ToString() on int, long, double, decimal and bool expressions.

Methods not mentioned above have no translation to SQL.
It is safe to call them if they can be evaluated before the SQL translation,
and in Select().

You can also provide an extra method/property that returns a LINQ expression corresponding to your custom method/property.
This LINQ expression can then be translated to SQL.

Let's start with properties, which are simpler because they don't have parameters.
To specify an expression that corresponds to a property, create a public static property
which is by convention named the same as your custom property plus the Expression suffix.
The getter of the expression property must return Expression<Func<TClass,TResult>
where TClass is the class the properties are defined in and TResult is the type of your custom property.
Example for a property in the Contact class:

With the above code you can use NameAndType anywhere in LINQ and it will get translated to SQL.

Defining expressions for custom methods is similar: you write a method named with the Expression suffix.
If your custom method is not overloaded, you can keep the expression method parameterless.
Otherwise, use same parameter types as in your custom method.
Never use the parameters in the expression method. Instead, specify the corresponding parameters in the lambda expression.

Sooda provides command-line tools that are used during the build process (4.1)
to create
a Data Access Layer library and some additional tools that can help you manage mapping schema files (3)
.
The following sections explain each tool and provide guidelines for their use.

SoodaStubGen utility is responsible for generating stub code for a Data Access Layer. The generated code needs
to be compiled with the rest of the application code as described
in section "4.1. Compilation Process"
.
You usually invoke SoodaStubGen as part of your automated build process to ensure that the
generated code (8.4)
is in sync with the mapping schema (3)
.
Visual Studio 2003 and 2005 are also supported.

This section describes command line options supported by SoodaStubGen and provides guidelines on
usage of this tool.

%SOODA_DIR% is Sooda installation path. Sooda Installer registers the SOODA_DIR
environment variable to point to this directory so you can use %SOODA_DIR% instead
of specifying the exact path in your build file.

If your project relies on a specific version of Sooda it is recommended to create a local copy
of the %SOODA_DIR%\bin\net-X.Y directory within your source code structure and
use relative paths.

Options to SoodaStubGen can be passed in a *.soodaproject (4.2)
file
or directly as command line parameters. The latter option is available for compatibility with older versions
of Sooda and using Sooda Project files is generally recommended.

SoodaStubGen.exe usage is:

SoodaStubGen.exe path_to_soodaproject_file

path_to_soodaproject_file is a full path to a *.soodaproject file. Sooda Project files are described
in section "4.2. Sooda Project Files - *.soodaproject"
.
If you prefer to use command-line options, the usage is:

SoodaStubGen.exe [options]

[options] represents a whitespace-separated sequence of options.
Most options have equivalent Sooda Project settings which are described
in section "4.2. Sooda Project Files - *.soodaproject"
.
The following options are available:

SoodaCompileStubs utility can be used to precompile generated stubs into a separate DLL.
Separate stub compilation is an advanced feature of Sooda and should be used with caution as described
in section "4.1.2. Separate Stub Compilation"
.

The utility is located in the same directory as SoodaStubGen (8.1.1.1)
.
It is a commmand-line program (no GUI is provided) that is meant to be part of an automated build
process. You should run it after SoodaStubGen to automatically compile stub code generated by it.

The usage is:

SoodaCompileStubs assembly_base_name stubs_dir [extra_files]

assembly_base_name - is the base name of the output assembly.
SoodaCompileStubs will generate assembly_base_name.Stubs.dll that contains stubs for
skeleton classes contained in assembly_base_name.dll.

stubs_dir - is the path to the generated Stubs directory. The directory contains the following files:

_DBSchema.bin

_MiniSkeleton.csx

_MiniStubs.csx

_Stubs.csx

This set of files is generated automatically by SoodaStubGen when <stubs-compiled-separately /> parameter is set to true
or when --separate-stubs command line option is passed to it.

extra_files - is a list of additional source files to be compiled with the skeleton classes.
The files should be written in C# and they should include the same assembly-level attributes as
assembly_base_name.dll will have. It is recommended to reuse the same AssemblyInfo.cs
file for separate stub compilation and normal compilation of assembly_base_name.dll.

Assuming your output DAL assembly is DAL.dll and the source files are located in the DAL directory,
typical command line options to generate DAL.Stubs.dll are:

SoodaCompileStubs DAL source\DAL\Stubs source\DAL\AssemblyInfo.cs

When compiling the DAL.dll you should reference source\DAL\Stubs\DAL.Stubs.dll. It is important to
not to remove the source\DAL\Stubs\DAL.dll file as it is necessary for compilation.

SoodaCompileStubs checks modification dates on source files in the Stubs directory and recompiles the output only
if the files are newer than the resulting DLL or if the DLL does not exist.

There are situations where you need to synchronize the contents of the KeyGen table (6.9)
to match actual primary key values. Do this after running any SQL script that inserts new rows
into mapped tables. If you fail to synchronize the KeyGen table, Sooda will generate non-unique primary
keys which will cause primary key constraints to be violated.

For example, assume that the KeyGen table contains a record where key_name="Foo" and key_value="100".
You now run an SQL script that inserts new records into Foo table:

After you do this, you need to update the appropriate KeyGen record to have key_value = 1000, because this is the next
value that should be generated for the primary key. If you fail to do so, newly created objects will get IDs of 100, 101, 102
which are invalid.

SoodaFixKeygen is a command-line utility that reads a mapping schema (3)
file and generates an SQL
script to properly update the KeyGen table. The SQL is written to the specified file.

Usage is:

SoodaFixKeygen soodaschema.xml outputFile.sql

After running the command, outputFile.sql will contain statements similar to:

In order to properly update the KeyGen table, you should run the generated SQL code using
SQL Query Analyzer or any other SQL client utility. Note that the SQL generated is specific to SQL Server,
other databases may require some post-processing.

Generates DDL commands to create tables, public keys, foreign keys based on the mapping schema
and writes them to the standard output. The SQL uses T-SQL (Microsoft SQL-Server specific) syntax.
You should use shell redirection mechanism to output the SQL to a file.

Logging in Sooda can be used to trace Sooda components such as transaction management, caching,
data sources and SOQL to SQL conversion. Having a trace file in place can be useful for
post-execution analysis of your program and to understand the inner workings of Sooda.

When using NLog or Log4Net you need to provide additional configuration files. Refer to
the documentation of the product in question for more information.

To write your own logging provider, you need to create a class that implements the
Sooda.Logging.ILoggingImplementation interface defined by Sooda and
put the fully qualified name of the class in sooda.logging configuration setting:

Each logging message in Sooda is associated with a source that identifies the component that emitted the log.
When using NLog or log4net, you can use the source to filter log messages. When console logging is used, there is no
way to specify which messages are emitted so it is recommended to use one of the mentioned log routing packages
or disable logging entirely, because emitting large amounts of tracing information can severely slow down the application.

Most logging messages are emitted with the lowest severity available (Trace for NLog,
Debug for log4net), but Sooda also uses the Error and Warning
severities as necessary.

Typically you should keep detailed logging disabled, with the possible exception of tracing SQL queries
emitted through Sooda.SQL and increase log level to diagnose and fix bugs in your code
or Sooda code. Error and warning messages should never be filtered out.

Relational databases do not understand Sooda schemas nor SOQL language,
therefore a translation from SOQL to SQL is necessary before any query can be executed.
Sooda comes with a SoqlToSqlConverter class that does the necessary translation.

The result of the conversion is dependent on the mapping schema (3)
, because SOQL to SQL converter
must translate classes to database table names and named constants to literals. As database engines implement
slightly different SQL dialects, you must also pass
an instance of an SQL builder class that implements ISqlBuilder interface and abstracts the SQL dialect
detail. Sooda comes with the following SQL builders for the most popular databases:

SqlServerBuilder - Microsoft SQL Server 2000 and 2005

OracleBuilder - Oracle 8i and above

MySqlBuilder - MySQL 4.x and above

PostgreSqlBuilder - PostgreSQL 7.3 and above

By default Sooda does the SOQL to SQL conversion automatically for all GetList-based queries.
Data Source (6.12)
classes provide methods that can be used to issue
SOQL queries on a database.
There are situations where you might want to do the conversion by hand, for example when you
need to modify/instrument the result of conversion before executing it.

To start the conversion, you need to pass the tree representation of your SOQL query,
namely a SoqlQueryExpression object, obtained it by calling SoqlParser.ParseQuery().
SoqlToSqlConverter writes the result of conversion to the specified TextWriter object - you can use an
instance of the StringWriter class to store the result in a string variable.

The following sections provide an overview of the SOQL to SQL conversion process.
Knowing the SQL that is generated from various SOQL constructs may be
useful to fully understand the cost of some Sooda features, but it is not
generally needed for normal use of Sooda.

The number of joins required is always equal to the number of dots in the path expression.
SOQL to SQL converter is smart enough to use the minimal number of joins when you reuse path expression prefixes.
Consider a query that finds all contacts who belong to groups managed by 'Mary Manager' or whose
managers earn more than one million is:

Operations on one-to-many collections (such as Group.Members where each person may belong to one group),
are translated to subqueries on the "child" table (Contact in this example).

The Count operation gets converted to a subquery containing the select count(*) expression.
Consider this query:

select *
fromGroupwhere Members.Count > 3

It translates to:

select t0.id as [Id],
t0.manager as [Manager],
t0.name as [Name]
from _Group t0
where ((selectcount(*) from Contact where primary_group=t0.id) > 3)

Converting Contains() on one-to-many collections is easy, because the expression
parentObject.collection.Contains(childObject) can be translated to
childObject.ReferenceField = parentObject, so this query:

select *
fromGroupwhere Members.Contains(Contact.Mary)

translates to

select t0.id as [Id],
t0.manager as [Manager],
t0.name as [Name]
from _Group t0
whereexists (select * from Contact where primary_group=t0.id and id in (1))

As you can see Contains() has
been translated to SQL EXISTS() clause. Note that Contact.Mary has been replaced with 1.

Contains(subquery) translates to similar EXISTS() expression with numeric literals replaced
with the translated subquery. SOQL code:

select t0.id as [Id],
t0.manager as [Manager],
t0.name as [Name]
from _Group t0
whereexists (select * from Contact where primary_group=t0.id and id in (
select t1.id as [ContactId]
from Contact t1
where (t1.name like'Mary%')))

Operations on many-to-many collections (such as Contact.Roles where each contact may hold many roles and
a role can be held by many persons), are translated to subqueries on the resolving table
(declared with the <relation /> element in the mapping schema (3)
).

select t0.id as [ContactId],
t0.primary_group as [PrimaryGroup],
t0.type as [Type],
t0.name as [Name],
t0.active as [Active],
t0.last_salary as [LastSalary]
from Contact t0
where ((selectcount(*) from ContactRole where contact_id=t0.id) > 3)

Translating Contains() that operates on single objects is only slightly more complicated
than in the one-to-many case. It gets converted to an SQL EXISTS() clause,
but it requires additional expression to join back to the "parent" table:

Given this SQOL:

select *
from Contact
where Roles.Contains(Role.Manager)

SOQL to SQL Converter produces:

select t0.id as [ContactId],
t0.primary_group as [PrimaryGroup],
t0.type as [Type],
t0.name as [Name],
t0.active as [Active],
t0.last_salary as [LastSalary]
from Contact t0
whereexists (select * from ContactRole where contact_id=t0.id and role_id in (2))

Contains() with subquery as an argument produces the most complicated
result, still using SQL EXISTS() clause and a subquery.

select *
from Contact
where Roles.Contains(Role where Name like'Admin%')

Produces:

select t0.id as [ContactId],
t0.primary_group as [PrimaryGroup],
t0.type as [Type],
t0.name as [Name],
t0.active as [Active],
t0.last_salary as [LastSalary]
from Contact t0
whereexists (select * from ContactRole where contact_id=t0.id and role_id in (
select t1.id as [Id]
from _Role t1
where (t1.name like'Admin%')))

SoodaClass returns class name as a string. In cases where the class is known statically (when the class
is known to have no subclasses), it is emitted directly into the SQL output. When the class has
subclasses, Sooda needs to resort to runtime evaluation using SQL CASE ... WHEN ... END
construct.

For example, this SOQL code:

select SoodaClass
from Contact

Is equivalent to the following SQL, because class Contact does not have any subclasses.

select'Contact'as SoodaClass
from Contact t0

On the contrary, the class Vehicle has many subclasses, so translating a similar query
returns very different result:

select SoodaClass
from Vehicle

The result of the translation is shown below. Note the CASE expression that
returns 'Car' when type is 1, 'Bike' when type is 2,
and so on. The mapping is taken from subclassSelectorValue attributes
defined in the mapping schema (3.9)
.

Pay attention to the extra WHERE clause that has been added by the conversion routine. This is needed to
ensure that the result of this query will be limited to instances of Vehicle and Bike
subclasses. The additional where clause always includes the identifiers of all concrete types that
may be returned. If we wanted to limit ourselves to the bikes, we cold have written:

select *
from Bike

The result of the translation would be (note that the identifiers of possible subclasses are
different from the previous case, even though the Bike class is based on the same database table):

Please also note how additional fields from the Bike class are made available in the results. There is a special
join, called "primary key join" and a special table alias "tk_pkjoin_Bike" which gives access to two_wheels
column in the Bike table.

Each stub class NNN_Stub generated by SoodaStubGen corresponds to a <classname="NNN" /> declaration in the mapping schema (3)
.
The stub class is enclosed in the output namespace defined in the Sooda Project File (4.2)
followed by .Stubs. It ultimately
derives from the SoodaObject class as described
in section "8.4.2. Class hierarchy"
:

one public instance property for each <field /> declared locally in the schema file (3)
. The name of the property matches the name defined in the schema and exact type
depends on chosen primitive representation (4.2.1)
and field nullability as described
in section "8.4.3. Properties"
.

Each <class /> element defined in the mapping schema (3)
is represented
in generated code by two classes: skeleton and stub. Stub classes typically inherit from the SoodaObject
class, but when Sooda class inheritance is used, the stub classes may inherit from parent skeleton classes.

In addition to this, you may "inject" your own class into the inheritance hierarchy at two levels:

The generated class hierarchy is presented in the following figure.
Note how all classes ultimately derive from the SoodaObject class
and how Bicycle_Stub, Car_Stub and Boat_Stub
classes inherit from Vehicle skeleton class.

SoodaStubGen generates one property for each <field /> declared in the mapping schema. The
name of the property is taken from the name attribute and the property type
depends on two factors:

the nullability of the field

the primitive representation as defined in the Sooda Project File

If the field is nullable, SoodaStubGen uses the representation passed in <nullable-representation /> parameter.
If the field is not nullable, the representation passed in <not-null-representation /> is used.
Particular representations are presented in the following sections.

RawWithIsNull generates a pair of properties: P and P_IsNull,
the latter being read-only returning true when the field value is null. In addition to this
the P_SetNull() method is generated that can be used to set the field value to null.

Each collection (both one-to-many and many-to-many) is represented as a read-only property whose type is
MMMList where MMM is the type of the collection element. The MMMList is a
list wrapper described
in section "8.4.7. List wrappers"
.

oldValue and newValue passed to these methods are old and new values of the field
being set, respectively. The values are boxed to represent the null. For reference fields, the type
of objValue and newValue arguments is the referenced class itself.

List wrappers are type-safe wrappers around ISoodaObjectList that can be used in all places
where the interface is expected. They provide typed Add(), Remove() and Contains()
methods and a number of convenience methods. The following wrapper is generated for each class NNN:

The FindByNNN and FindListByNNN methods are used to quickly locate one or all objects such that
their NNN is equal to the provided value. The finders are generated as public static methods of
stub classes:

Each Sooda class has an associated abstract factory that can create instances of the class and
return mapping metadata. The abstract factory is a class that
implements ISoodaObjectFactory. The following factory code is generated for
each class NNN:

There are two sets of classes that support typed queries: public classes and supporting classes. Public
classes are named OutputNamespace.TypedQueries.NNNField where NNN is the name
of the appropriate class. Supporting classes are located in the OutputNamespace.Stubs namespace
and are named: NNNWrapperExpression and NNNNullableWrapperExpression:

Sooda.Schema.SchemaInfo class represents the <schema /> element of the mapping schema (3)
.
You typically acquire a reference to the SchemaInfo object that matches the compiled
stubs assembly by calling _DatabaseSchema.GetSchema() method as in the following example.
The _DatabaseSchema class is generated by the SoodaStubGen utility (8.1.1)
and
so the namespace depends on your code generation settings.

Sooda.Schema.ClassInfo class represents the <class /> element of
the mapping schema (3)
. It can be used to retrieve class-level mapping metadata.
Objects of this class are stored in SchemaInfo.Classes and SchemaInfo.LocalClasses collections.

ClassInfo has the following public properties:

Name - class name

ExtBaseClassName - Specifies the name of the base class that this class should inherit from.

DataSourceName - name of the data source

LocalTables - collection of TableInfo (8.5.4)
objects representing local tables of this class (excluding any tables defined in parent classes in the inheritance hierarchy)

UnifiedTables - collection of TableInfo (8.5.4)
objects representing all tables that this class is based on (including tables defined in parent classes in the inheritance hierarchy)

DatabaseTables - collection of TableInfo (8.5.4)
objects representing database tables of this class ignoring any vertical partitions (so two <table /> declarations
based on a single physical table are merged together. This structure is used to properly INSERT objects
into the database.

LocalFields - collection of FieldInfo (8.5.5)
objects defined in all tables defined in this class (primary key fields are not duplicated)

UnifiedFields - collection of FieldInfo (8.5.5)
objects defined in all tables defined in this class and all parent classes in the inheritance hierarchy (primary key fields are not duplicated)

OrderedFieldNames - array of ordered field names (as in UnifiedFields)

Collections1ToN - one-to-many collections defined in this class

CollectionsNToN - many-to-many collections defined in this class

LocalCollections - all collections (one-to-many and many-to-many) defined in this class

UnifiedCollections - all collections (one-to-many and many-to-many) defined in this class and all parent classes in the inheritance hierarchy

Constants - collection of ConstantInfo (8.5.7)
that define named constants for this class

DefaultPrecommitValue - default precommit value to be used for reference fields that refer to this class

SubclassSelectorFieldName - name of the subclass selector field (propagated from parent classes if necessary)

SubclassSelectorField - a FieldInfo (8.5.5)
object that represents the subclass selector field

SubclassSelectorStringValue - string value of the subclass selector field

SubclassSelectorValue - typed value of the subclass selector field (string or integer)

InheritFrom - name of the base Sooda class in the inheritance hierarchy

InheritsFromClass - a ClassInfo (8.5.2)
object that represents the base class in the inheritance hierarchy

KeyGenName - name of the key generator that provides keys for this class

OuterReferences - collection of FieldInfo (8.5.5)
objects that are references to this class

DisableTypeCache - whether type cache is disabled for this class

In addition to the properties, ClassInfo exposes the following public methods:

FindCollectionOneToMany(name) - finds the specified one-to-many collection in this class or any
ancestors in the inheritance hierarchy

FindCollectionManyToMany(name) - finds the specified many-to-many collection in this class or any
ancestors in the inheritance hierarchy

ContainsCollection(name) - determines whether the specified collection is defined in this class
or any ancestor class

GetPrimaryKeyFields() - returns an array of FieldInfo (8.5.5)
objects which are marked as primary keys

GetFirstPrimaryKey() - returns the FieldInfo (8.5.5)
object for the first primary key field

GetSubclassesForSchema(schema) - returns the collection of ClassInfo (8.5.2)
objects that are subclasses of this class in the specified schema.
Note that the list of subclasses may be different depending on the schema you use if you define a root class in one
schema file (3)
and classes inheriting from it in another one.

Let's assume that you have defined the following classes in the mapping schema (3)
:

The LocalTables collection of class A contains two items, because we have defined two
<table /> elements inside the <classname="A" /> element. The DatabaseTables of
this class contains one item, which is a table TA containing the
Id, F1 and F2, because both <table />s that define them are
based on the same database table. The UnifiedTables contents are the same as LocalTables
because the class does not inherit from any other class.

The LocalTables collection of class B contains three items, because this is the number of
<table /> elements defined within <classname="B" />. There are two items in the
DatabaseTables collection, because there are two physical database tables:
TA and TB. The UnifiedTables of class B contains
five objects of class TableInfo, one for each defined table in class B and
its parent class A.

Sooda.Schema.RelationInfo class represents the <relation /> element of
the mapping schema (3)
. It can be used to retrieve relation-level mapping metadata.
Objects of this class are stored in SchemaInfo.Relations and SchemaInfo.LocalRelations collections.

RelationInfo has the following public properties:

Name - relation name

DataSourceName - data source name for this relation

Table - the table that holds the fields referencing objects in relation

Sooda.Schema.TableInfo class represents the <table /> element of
the mapping schema (3)
. It can be used to retrieve table-level mapping metadata.
Objects of this class are stored in ClassInfo.LocalTables and ClassInfo.UnifiedTables collections.
RelationInfo maintains a reference to the underlying TableInfo as well.

Sooda.Schema.CollectionOneToManyInfo class represents the <collectionOneToMany /> element of
the mapping schema (3)
. It can be used to retrieve information about
defined one-to-many collections of a class:

Sooda.Schema.CollectionManyToManyInfo class represents the <collectionManyToMany /> element of
the mapping schema (3)
. It can be used to retrieve information about
defined many-to-many collections of a class:

CollectionManyToManyInfo class has the following public properties:

Name - collection name

Relation - name of the relation that resolves many-to-many relationship

ForeignField - name of the field in Relation that references at the object that defines the collection

Factory cache is a mechanism that is meant to optimize the GetRef() operation for classes
that are part of inheritance relationships. For all other cases GetRef() can be very cheap,
constant-time operation, but if a class has subclasses, Sooda needs to issue a database query to
determine the concrete class that should be instantiated. Once the query has been executed and the concrete
class is known, we can cache it so that further GetRef()s can be done without any database queries.

By default Sooda comes with eternal factory cache that stores the primary key to concrete class association in
a hashtable that is never emptied. This is sufficient for most databases with less than a few million records. For
larger databases you should implement your own factory cache by creating a class that implements the ISoodaObjectFactoryCache
interface.

Once you have defined your own factory cache, you should assign it to the static
property SoodaObjectFactoryCache of the SoodaTransaction class:

Implicit transaction (stored in SoodaTransaction.ActiveTransaction) is kept in
thread's local storage (TLS). It means that as long as you access the objects from the thread
that created the transaction, you do not need to pass the transaction reference explicitly.
There are situations where storing the transaction on per-thread basis is not feasible. For example
events in the ASP.NET environment are not guaranteed to be thread-bound so you cannot store
implicit SoodaTransaction in TLS.

Fortunately, Sooda supports multiple strategies for managing the implicit transaction reference.
There are three predefined strategies:

To implement your own strategy, create a class that implements the
IDefaultSoodaTransactionStrategy interface. Two methods need to be implemented: one that
gets current transaction and the other that sets it and returns a previous value.

Prefetching is a technique that allows object references to be pre-loaded from the database
at the same time the object is loaded. This is possible thanks to the SQL OUTER JOIN
operators. Normally Sooda issues a simple SQL statement to load an object from the database::

select column1, column2, ..., columnN
from table1
where id=123

Assuming that columnR is a foreign key reference to table2, we can load two
objects in a single query by using an outer join:

Note that columns 1..N belong to objects stored in "table1" and
columns N+1..N+M are from "table2".

Sooda extends this concept with the possibility to include arbitrary number of objects being prefetched
on each load. This can be controlled by the prefetch attribute of the <field /> element in the
mapping schema (3)
. By default it is zero, meaning that no prefetch should be performed,
but you can set it to a higher value to indicate level of objects that should be prefetched.

When you load a movie object m Sooda automatically prefetches related objects, so that
the following properties can be accessed with no additional SQL queries:

m.ID
m.Title
m.Genre
m.Subgenre
m.AudioLanguage
m.VideoFormat
m.ParentalGuidance
// these fields are prefetched because field Genre has prefetch="1"
m.Genre.Name
// these fields are prefetched because field Subgenre has prefetch="2"
m.Subgenre.Name
m.Subgenre.ParentGenre.ID
m.Subgenre.ParentGenre.Name
// these fields are prefetched because field VideoFormat has prefetch="1"
m.VideoFormat.FrameWidth
m.VideoFormat.FrameHeight
m.VideoFormat.ColorDepth
m.VideoFormat.Compression

Note that the following fields are NOT prefetched automatically and will involve
a database access when first accessed:

Prefetch can be used to optimize preloading of hierarchies, such as Employee-Manager relationship which is effectively
a tree. Let's assume you have a class named Employee which includes a reference to its manager:

Without the prefetch, Sooda would have to issue the SQL query to retrieve each Employee object separately.
Assuming you have 10 levels of management in your company, that would be 10 SQL queries. To minimize the number of queries, you can add prefetch="3"
to the "Manager" field declaration:

<fieldname="Manager"type="Integer"reference="Employee"prefetch="3"/>

When you load an Employee object e, Sooda preloads the following referenced objects, so you can access
their properties without an SQL query:

e.Manager
e.Manager.Manager
e.Manager.Manager.Manager

Once you access e.Manager.Manager.Manager.Manager, Sooda will preload:

If there are at most three managers in the hierarchy, setting the prefetch to 3 ensures that
the entire structure will be fetched in a single SQL query.
The cost of prefetch is usually very small, but the number of columns returned in a query grows as you
prefetch, so you should limit the depth of the prefetch operation. Prefetch value of one is usually sufficient.