Sooda Documentation

Sooda (Simple Object-Oriented Data Access) is an Object-To-Relational Mapping software for the .NET (http://www.microsoft.com/NET/) environment. O/R Mapping Software enables you to express your business rules without SQL.

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.

1.1.1.Features

1.1.2.Supported Platforms

1.1.3.Supported Relational Databases

1.1.4.Supported Languages

1.2.1.Mapping Classes to Tables

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)
• objects may need to be persisted in more than one table (1.2.7)

1.2.2.Primary Keys and Object Identity

1.2.3.One-To-Many Relationships

One-to-many relationships are typically represented as collections of objects related to some particular object (only one). Typical examples are:

• 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:

create table Customer 
( 
    ID int primary key not null, 
    Name varchar(64), 
    Address1 varchar(64), 
    Address2 varchar(64) 
); 
 
create table Order 
( 
    ID int primary key not null, 
    -- more columns here 
    CustomerID int not null references Customer(ID) 
);

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:

class Customer 
{ 
    int ID { get; set; } 
    string Name { get; set; } 
    ... 
    OrderList Orders { get; } 
} 
 
class Order 
{ 
    int ID { get; set; } 
    Customer Customer { get; set; } 
}

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

Convenience methods of Sooda collections are described in section "8.4.7. List wrappers" .

1.2.4.Many-To-Many Relationships

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:

The SQL used to create this database is:

create table Employee 
( 
    ID int primary key not null, 
    Name varchar(64), 
    Address1 varchar(64), 
    Address2 varchar(64) 
); 
 
create table Permission 
( 
    ID int primary key not null, 
    PermissionName varchar(64), 
); 
 
create table Employee2Permission 
( 
    EmployeeID int not null references Employee(ID), 
    PermissionID int not null references Permission(ID) 
) 
 
alter table Employee2Permission add primary key (EmployeeID,PermissionID);

Sooda generates a pair of collection which provide access to objects in the relationship. In this example, we have:

class Employee 
{ 
    ... 
    PermissionList Permissions { get; } 
} 
 
class Permission 
{ 
    ... 
    EmployeeList Employees { get; } 
}

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

using (SoodaTransaction t = new SoodaTransaction()) 
{ 
    Employee emp = ...; 
    Permission perm = ...; 
 
    // the following two lines are equivalent 
    emp.Permissions.Add(perm); // add permission to the emp's permission collection 
    perm.Employees.Add(emp); // add employee to perm's employees collection 
 
    if (emp.Permissions.Contains(perm)) 
    { 
        Console.WriteLine("Employee is authorized."); 
    } 
 
    // this tests essentially the same thing 
    if (perm.Employees.Contains(emp)) 
    { 
        Console.WriteLine("Employee is authorized."); 
    }     
}

1.2.5.Lazy Loading

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.

Lazy loading is described in section "6.3. Loading objects" .

1.2.6.Path expressions and managing referenced objects

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

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

1.2.7.Mapping Multiple Tables to a Single Class

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" .

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.

Polymorphic access enables you to write:

WorkItem wi = WorkItem.Load(3); 
if (wi is QuestionWorkItem) 
{ 
    QuestionWorkItem qwi = (QuestionWorkItem)wi; 
 
    Console.WriteLine("question: {0}", qwi.Question); 
} 
 
// display concrete types of all work items 
foreach (WorkItem wi in WorkItem.GetList(true)) 
{ 
    Console.WriteLine("wi: {0}", wi.GetType().FullName); 
}

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.

1.2.9.Query Language

1.2.10.Cross-transaction (Level 2) Caching

1.2.11.Dynamic Fields

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).

Note how the Wizard generated the following files for your project:

• projectname.soodaproject - that stores code generation settings (4.2)
• SoodaSchema.xml - that contains the object-to-relational mapping schema (3)
• App.config - that contains runtime configuration settings

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:

1. replace "Territories" with "Territory" (quotes should be included in the Search For and Replace With strings)
2. 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.

• Categories.cs
• Customers.cs
• Employees.cs
• Orders.cs
• Products.cs
• Regions.cs
• Shippers.cs
• Suppliers.cs
• Territories.cs

When you are done, rebuild your project.

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:

public static void Main() 
{ 
    // you cannot access Sooda objects here 
 
    using (SoodaTransaction tran = new SoodaTransaction()) 
    { 
        // access your objects here 
 
        // commit all changes made within transaction 
        tran.Commit(); 
    } 
 
    // you cannot access Sooda objects here 
}

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.

Territory t = Territory.Load("98104"); 
Console.WriteLine("Territory is: {0}", t.TerritoryDescription);

When you run this program, it should display:

Territory is: Seattle

To see what region Seattle belongs to, we can use the path expression (1.2.6) t.Region.RegionDescription:

Territory t = Territory.Load("98104"); 
Console.WriteLine("Territory is: {0}", t.TerritoryDescription); 
Console.WriteLine("Region is: {0}", t.Region.RegionDescription);

We can use foreach() to enumerate all employees from Seattle:

Territory t = Territory.Load("98104"); 
 
foreach (Employee e in t.Employees) 
{ 
    Console.WriteLine("name: {0} {1} id: {2}", e.LastName, e.FirstName, e); 
}

There is one: Michael Suyama and his Employee ID is 6. Let's see a list of his all superiors:

Employee michaelSuyama = Employee.Load(6); 
 
for (Employee e = michaelSuyama; e != null; e = e.ReportsTo) 
{ 
    Console.WriteLine("id={0} name={1} {2} reports to={3}",  
         e.EmployeeID, e.LastName, e.FirstName, e.ReportsTo); 
}

Running this code presents us with a list of 3 people and their IDs:

id=6 name=Suyama Michael reports to=5 
id=5 name=Buchanan Steven reports to=2 
id=2 name=Fuller Andrew reports to=

What if we wanted to find people by their names? Sooda supports three query languages: LINQ (7.6) , typed queries (7.5) (supported only in C#) and SOQL (7.1) .

foreach (Employee emp in Employee.Linq().Where(e => e.LastName == "Suyama")) 
{ 
    Console.WriteLine("{0}", emp.FirstName); 
}

Same query using Typed Queries:

foreach (Employee emp in Employee.GetList(EmployeeField.LastName == "Suyama")) 
{ 
    Console.WriteLine("{0}", emp.FirstName); 
}

Same query using SOQL:

foreach (Employee emp in Employee.GetList(new SoodaWhereClause("LastName == {0}", "Suyama"))) 
{ 
    Console.WriteLine("{0}", emp.FirstName); 
}

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:

foreach (Order o in Order.GetList( 
    OrderField.ShipVia.CompanyName == "Speedy Express" && 
    OrderField.Items.Count > 3)) 
{ 
    Console.WriteLine("Order #{0} Date: {1}", o.OrderID, o.OrderDate); 
    Console.WriteLine("Ship to: {0} {1} {2} {3} {4}",  
            o.ShipName, o.ShipAddress,  
            o.ShipCity, o.ShipPostalCode,  
            o.ShipCountry); 
 
 
    for (int i = 0; i < o.Items.Count; ++i) 
    { 
        Product p = Product.Load(o.Items[i].Product); 
 
        Console.WriteLine("  {0}. {1} {2} x {3}", i,  
            p.ProductName, p.UnitPrice, o.Items[i].Quantity); 
    } 
    //Console.WriteLine("o: {0}", o.Items.Count); 
}

Creating and modifying objects is easy. To create objects you simply use the familiar new construct. To create new order you would write:

Order o = new Order();

To modify objects, just set their appropriate properties:

o.ShipCity = "Redmond"; 
o.ShipCountry = "US"; 
o.ShipPostalCode = "98052"; 
o.ShipAddress = "One Microsoft Way";

When you are done with object modifications, you can call the Commit() method on the transaction object to save your changes to the database.

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:

<schema xmlns="http://www.sooda.org/schemas/SoodaSchema.xsd"> 
  <datasource name="default" type="Sooda.Sql.SqlDataSource"/> 
</schema> 

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.
inheritFrom	string	Specifies the base class. This parameter is explained in the inheritance section (3.9) .
defaultPrecommitValue	depends on primary key type	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.
disableTypeCache	bool	Disable type cache for this type. This parameter is described in the inheritance section (3.9)
triggers	bool	Determines whether triggers (6.8) should be generated for this class.
readOnly	boolean	Generates read-only class. No property setters are generated in such case and some scenarios are optimized.

<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.

<class name="className"> 
  <table name="tableName1"> 
    <field name="f1" type="t1" primaryKey="true"/> 
    <field name="f2" type="t2"/> 
    <field name="f3" type="t3"/> 
    <field name="f4" type="t4"/> 
  </table> 
  <table name="tableName2"> 
    <field name="f1" type="t1" primaryKey="true"/> 
    <field name="f2" type="t2"/> 
    <field name="f3" type="t3"/> 
    <field name="f4" type="t4"/> 
  </table> 
  <table name="tableName3"> 
    <field name="f1" type="t1" primaryKey="true"/> 
    <field name="f2" type="t2"/> 
    <field name="f3" type="t3"/> 
    <field name="f4" type="t4"/> 
  </table> 
</class> 

The typical definition of a class that is based on a single table looks like this:

<class name="Person"> 
  <table name="Person"> 
    <field name="Id" type="Integer" primaryKey="true"/> 
    <field name="Name" type="String" size="40"/> 
    <field name="Location" type="String" size="40"/> 
    <field name="PictureBlob" type="Image"/> 
  </table> 
</class> 

<field /> elements are discussed in depth in section "3.4. Fields - <field />" .

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.

This is represented by the following fragment of schema:

<class name="Employee"> 
  <table name="Employee"> 
    <field name="ID" type="Integer" primaryKey="true"/> 
    <field name="Name" type="String" size="40"/> 
    <field name="Salary" type="Decimal"/> 
    <field name="HireDate" type="DateTime"/> 
    <field name="Active" type="BooleanAsInteger"/> 
  </table> 
  <table name="EmployeePicture"> 
    <field name="ID" type="Integer" primaryKey="true"/> 
    <field name="Picture" type="Image"/> 
  </table> 
</class> 

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.

<class name="Employee"> 
  <table name="Employee"> 
    <field name="ID" type="Integer" primaryKey="true"/> 
    <field name="Name" type="String" size="40"/> 
    <field name="Salary" type="Decimal"/> 
    <field name="HireDate" type="DateTime"/> 
    <field name="Active" type="BooleanAsInteger"/> 
  </table> 
  <table name="Employee"> 
    <field name="ID" type="Integer" primaryKey="true"/> 
    <field name="Picture" type="Image"/> 
  </table> 
  <table name="Employee"> 
    <field name="ID" type="Integer" primaryKey="true"/> 
    <field name="VoiceSample" type="Blob"/> 
  </table> 
</class> 

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.

Person adam; 
Person eva; 
Person mary; 
Person john; 
 
// 1. read Adam's name - this causes a SELECT on a database. 
Console.WriteLine(adam.Name);  
 
// 2. read Adam's address - database access is not needed because the appropriate row 
// from the database has been loaded in step #1 
Console.WriteLine(adam.Location);  
 
// 3. access the picture of Adam - we need to access a database 
Console.WriteLine(adam.Picture);  
 
// 4. we load PictureBlob for Eva (note that neither Name nor Location is loaded yet) 
Console.WriteLine(eva.Picture);  
 
// 5. access the Name of Eva - we load it along with her Location 
Console.WriteLine(eva.Name);  
 
// 6. location already loaded in step #5 - no database read here 
Console.WriteLine(eva.Location);  
 
// 7. read Adam's name - no database access - already loaded in step #1 
Console.WriteLine(adam.Name);  
 
// 8. read Adam's picture - no database access - already loaded #3 
Console.WriteLine(adam.Picture);  
 
// 9. read Adam's voice sample - causes a select on a database 
Console.WriteLine(adam.VoiceSample.Length); 

<field /> elements define the mapping between database columns and object properties or fields in relation table. Each field represents a single column in the database.

Usage in a class definition:

<class name="class1"> 
  <table name="table1"> 
    <field name="f1" type="t1" primaryKey="true"/> 
    <field name="f2" type="t2"/> 
    <field name="f3" type="t3"/> 
    <field name="f4" type="t4"/> 
  </table> 
</class> 

Usage in many-to-many relation definition:

<relation name="class1"> 
  <table name="table1"> 
    <field name="f1" type="t1" primaryKey="true"/> 
    <field name="f2" type="t2" primaryKey="true"/> 
  </table> 
</relation> 

The following attributes are available on the <field /> element. Attributes marked with bold are required:

Name	Type	Description
name	string	Field name - used by SoodaStubGen (8.1.1) to generate the C#/VB.NET property
type	FieldDataType	Data type. See the supported data types (3.4.1) for more information.
dbcolumn	string	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) Cascade - delete this object
prefetch	integer	Prefetch level for this field. See the prefetching objects (8.8) section for more information.
nullable	boolean	Allow this field to hold the null value. Exact representation of the null value (8.4.3) depends on options passed to SoodaStubGen (8.1.1) .
readOnly	boolean	This field cannot be modified (no property setter is generated).
forceTrigger	boolean	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: <class name="Class1">  <table name="Table1">  <field name="Id" type="Integer" primaryKey="true"/>  <field name="Name" type="String" find="true"/>  </table>  </class>  SoodaStubGen will generate: class Class1  {  ...  public static Class1 FindByName(string name)  {  //   }  ...  }
findList	boolean	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: <class name="Class1">  <table name="Table1">  <field name="Id" type="Integer" primaryKey="true"/>  <field name="Name" type="String" findList="true"/>  </table>  </class>  SoodaStubGen will generate: class Class1  {  ...  public static Class1List FindListByName(string name)  {  //   }  ...  }

There is no support for compound properties (that would map to more than one field), but it is relatively easy to do it by hand by adding a new property of the required type.

3.4.1.Supported data types

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.

<class> 
  ... 
  <const name="..." value="..."/> 
</class> 

Example:

<class name="User"> 
  <table name="User"> 
    <field name="ID" type="Integer" primaryKey="true"/> 
    <field name="Name" type="String" size="40"/> 
  </table> 
  <const name="Administrator" value="1"/> 
  <const name="Guest" value="2"/> 
  <const name="SystemUser" value="2"/> 
</class> 

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.

User.GetRef(1) == User.Administrator 
User.GetRef(2) == User.Guest 
User.GetRef(3) == User.SystemUser

One-to-many relationships are defined using <collectionOneToMany /> which needs to be placed inside <class /> element.

<class name="className"> 
  <field/> 
  <field/> 
  <field/> 
  <field/> 
  <collectionOneToMany name="COLLECTION_NAME" class="CHILD_CLASS" 
                       foreignField="FOREIGN_FIELD"/> 
</class> 

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:

<class name="Group"> 
  <collectionOneToMany name="Members" class="Person" foreignField="Group"/> 
  <collectionOneToMany name="VipMembers" class="Person" foreignField="Group" 
                       where="IsVip = True"/> 
</class> 

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 details about generated code can be found in section "8.4.4. Collections" .

Many-to-many relationships are defined using <collectionManyToMany /> in cooperation with the <relation />. The <collectionManyToMany /> needs to be placed inside <class /> element and <relation /> needs to be placed inside <schema /> element.

<schema> 
  <class name="className"> 
    ... 
    <collectionManyToMany name="COLLECTION_NAME" relation="RELATION" 
                          foreignField="FOREIGN_FIELD"/> 
  </class> 
  <relation name="RELATION"> 
    <table name="RELATION_TABLE"> 
      <field name="f1" type="t1" primaryKey="true" references="R1"/> 
      <field name="f2" type="t2" primaryKey="true" references="R2"/> 
    </table> 
  </relation> 
</schema> 

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 />.

<relation name="Contact2Group"> 
  <table name="Contact2Group"> 
    <field name="TheContact" type="Integer" references="Contact" 
           primaryKey="true"/> 
    <field name="TheGroup" type="Integer" references="Group" primaryKey="true"/> 
  </table> 
</relation> 

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 collections are declared using <collectionManyToMany />.

class Contact 
{ 
    GroupList Groups { get; } 
} 
 
class Group 
{ 
    ContactList Members { get; } 
}

The <collectionManyToMany /> declaration accepts the following attributes:

<class name="Contact"> 
  ... 
  <collectionManyToMany name="Groups" relation="Contact2Group" 
                        foreignField="TheContact"/> 
</class> 

<class name="Group"> 
  ... 
  <collectionManyToMany name="Members" relation="Contact2Group" 
                        foreignField="TheGroup"/> 
</class> 

<class name="Contact"> 
  ... 
  <collectionManyToMany name="Groups" relation="Contact2Group" masterField="1"/> 
</class> 

<class name="Group"> 
  ... 
  <collectionManyToMany name="Members" relation="Contact2Group" masterField="0"/> 
</class> 

The details about generated code can be found in in section "8.4.4. Collections" .

Schemas can include one another. This is a way to split complex domain models into many manageable files which are subject to separate compilation and code generation.

You need to use the <include /> to include another schema. The following attributes are supported (attributes marked with bold are required).

Example usage (assuming that SoodaSchema.xml is located in c:\MyProject the included file should be located in c:\MyProject\SharedBusinessObjects\SoodaSchema.xml):

<schema xmlns="http://www.sooda.org/schemas/SoodaSchema.xsd"> 
  <include schema="SharedBusinessObjects/SoodaSchema.xml" 
           namespace="MyCompany.SharedBusinessObjects" 
           assembly="MyCompany.SharedBusinessObjects"/> 
</schema> 

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:

The following table summarizes possible combinations of the above attributes:

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:

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

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

There are situations where Sooda needs to temporarily store an object in a database. This is called precommit and is described in section "6.10. Precommitting objects in transactions" .

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:

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:

1. per-field precommit value - to be used for a particular field
2. per-class precommit value - used for fields that reference to this class
3. 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
4. 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.

<schema xmlns="http://www.sooda.org/schemas/SoodaSchema.xsd"> 
  <datasource name="default" type="Sooda.Sql.SqlDataSource"/> 
  <class name="User" defaultPrecommitValue="-1"> 
    <table name="_User"> 
      <field name="ID" type="Integer" primaryKey="true"/> 
      <field name="Name" type="String" size="40"/> 
      <field name="Manager" type="Integer" references="User" 
             precommitValue="1111"/> 
    </table> 
  </class> 
</schema> 

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.

<schema xmlns="http://www.sooda.org/schemas/SoodaSchema.xsd"> 
  <datasource name="default" type="Sooda.Sql.SqlDataSource"/> 
  <class name="User" defaultPrecommitValue="-1"> 
    <table/> 
  </class> 
</schema> 

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.

<schema xmlns="http://www.sooda.org/schemas/SoodaSchema.xsd"> 
  <datasource name="default" type="Sooda.Sql.SqlDataSource"/> 
  <precommitValue type="Integer" value="-1"/> 
  <precommitValue type="String" value="***NONE***"/> 
  <precommitValue type="AnsiString" value="***NOTHING***"/> 
</schema> 

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.

4.1.1.Standard Stub Compilation

Standard Stub Compilation is a process of building Sooda-based data access library, which uses two utilities:

1. SoodaStubGen (8.1.1) which generates stub source code
2. C#/VB Compiler which compiles stub and application code together

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:

1. 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.
2. 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).

4.1.2.Separate Stub Compilation

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).

The following utilities are required:

1. SoodaStubGen (8.1.1) which generates stub source code and source files necessary to generate mini-stubs and full stubs.
2. SoodaCompileStubs (8.1.1.3) which precompiles stubs to a DLL.
3. C#/VB Compiler which compiles application code referencing the precompiled stub DLL.

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

The steps of the separate stub compilation are:

1. SoodaStubGen (8.1.1) generates stub source code for: stubs, mini-stubs and mini-skeletons as described in section "4.1.3. Files used during the compilation process" .
2. SoodaCompileStubs (8.1.1.3) is invoked to bootstrap the stubs library. This process consists of the following steps:
   1. C# compiler is invoked to build OutputAssemblyName.Stubs.dll from _MiniStubs.csx. The DLL references Sooda.dll only.
   2. C# compiler is invoked to build OutputAssemblyName.dll from _MiniSkeleton.csx referencing OutputAssemblyName.Stubs.dll built in the previous step.
   3. 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.

4.1.3.Files used during the compilation process

The following files are created or used during the compilation process:

Name	Location	Provided by user	Generated	Description
SoodaSchema.xml	any	Yes	Yes/No	Defines mapping schema (3) . Can be written by hand or reverse-engineered from an existing database with the SoodaSchemaTool utility (8.1.1.5) .
ProjectName.soodaproject	any	Yes	No	Defines code generation parameters. The format of this file is described in section "4.2. Sooda Project Files - *.soodaproject" . You may choose to not use this file, but pass all code generation parameters as command line arguments to SoodaStubGen utility (8.1.1) instead.
_DBSchema.bin	output directory	No	Yes	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.

4.1.4.NAnt

<project> 
  <loadtasks assembly="${sooda.dir}/Sooda.NAnt.Tasks.dll"/> 
</project> 

<project> 
  <loadtasks assembly="${sooda.dir}/Sooda.NAnt.Tasks.dll"/> 
  <sooda-generate-code project="path/to/proj.soodaproject"/> 
</project> 

<project> 
  <exec program="${sooda.dir}/SoodaStubGen.exe" 
        commandline="path/to/proj.soodaproject"/> 
</project> 

<csc target="library" output="${sooda.dir}/Sooda.UnitTests.BaseObjects.dll" 
     debug="true"> 
  <references> 
    <include name="${sooda.dir}/Sooda.dll"/> 
    <include name="System.Data.dll" asis="true"/> 
  </references> 
  <sources basedir="tests/BaseObjects"> 
    <include name="*.cs"/> 
  </sources> 
  <resources basedir="tests/BaseObjects" prefix="Sooda.UnitTests.BaseObjects" 
             dynamicprefix="true"> 
    <include name="_DBSchema.*"/> 
  </resources> 
</csc> 

4.1.5.Visual Studio

"%SOODA_DIR%\bin\net-2.0\SoodaStubGen.exe" "$(ProjectName).soodaproject"

"%SOODA_DIR%\bin\net-2.0\SoodaCompileStubs.exe" $(ProjectName) "$(ProjectDir)Stubs" "$(ProjectDir)AssemblyInfo.cs"

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

<sooda-project xmlns="http://www.sooda.org/schemas/SoodaProject.xsd"> 
  <!-- parameters go here --> 
</sooda-project> 

The following table describes elements which can be used in Sooda Project files:

XML Element	Type	Description
schema-file	string	Path to the file that defines the mapping schema (3) . Example: <schema-file>MySoodaSchema.xml</schema-file>
language	string	Specifies the programming language. Possible (case insensitive) values are: CS, C#, CSharp VB, VB.NET C++/CLI MC++ - Managed C++ alternatively you can provide a fully qualified class name (including assembly, primary key token) of the CodeDOM provider. The default is C#. Examples: C#: <language>c#</language> VB.NET: <language>vb</language> Boo: <language>Boo.Lang.CodeDom.BooCodeProvider, Boo.Lang.CodeDom</language>
output-assembly	string	Name of the output assembly without the DLL extension. Example: <output-assembly>MyAssembly</output-assembly>
output-namespace	string	Name of the output namespace. Example: <output-namespace>MyNamespace</output-namespace>
output-path	string	Output path. By default it is "." which represents current working directory. Example: <output-path>src\MyBusinessObjects</output-path>
nullable-representation not-null-representation	string	Specifies how nullable and not-nullable fields are mapped to properties. Possible methods are discussed in section "4.2.1. Primitive Representations" . Default value for nullable representation is SqlType and default not-null representation is Raw. Example: <nullable-representation>Boxed</nullable-representation> <not-null-representation>Raw</not-null-representation>
null-propagation	boolean	Enable null propagation for nullable properties and reference properties: <null-propagation>true</null-propagation> Enabling this option causes the following prolog code to be emitted for all property getters: if (this == null)  return null; 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. This feature is experimental and should be used with caution.
loader-class	boolean	Generate Load(), GetRef(), GetList(), FindByXXX() and FindListByXXX() static methods in a separate class called ClassNameLoader instead of ClassName. <loader-class>true</loader-class> 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: Contact.Load(10);  Contact.GetRef(10);  Contact.TryGet(10);  Contact.GetList(whereClause);  Contact.LoadSingleObject(whereClause); When <loader-class /> is set to true, you can use the following methods to load objects: ContactLoader.Load(10);  ContactLoader.GetRef(10);  ContactLoader.TryGet(10);  ContactLoader.GetList(whereClause);  ContactLoader.LoadSingleObject(whereClause);
with-typed-queries	boolean	This option causes the SoodaStubGen utility (8.1.1) to emit SQOL typed queries (7.5) . By default it is true, you can disable typed queries if you plan to use text-based queries (7.1) exclusively. <with-typed-queries>true</with-typed-queries>
stubs-compiled-separately	boolean	Enables separate compilation of stubs (4.1.2) . <stubs-compiled-separately>true</stubs-compiled-separately>
file-per-namespace	boolean	Write each namespace to a separate file: _Stubs.cs - assembly-level attributes _Stubs.OutputNamespace.cs - list wrappers and class loaders _Stubs.OutputNamespace.Stubs.cs - stubs, factories, private typed query expression classes _Stubs.OutputNamespace.TypedQueries - public typed query expression classes When this option is set to false, all files are written to _Stubs.cs. <file-per-namespace>true</file-per-namespace>
base-class-name	string	Specifies the name of the class that all stubs should ultimately derive from. If you do not specify this parameter, all root stubs classes will derive from the SoodaObject class. <base-class-name>AdditionalBaseClass</base-class-name> More ways to override base classes are discussed in section "8.4.2. Class hierarchy" .
embed-schema type	Xml|Binary	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 <project type="..." file="..." />. The following example XML defines a Visual Studio 2005 project: <external-projects>  <project type="vs2005" file="MyProject.vs2005.csproj"/>  </external-projects>  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.

4.2.1.Primitive Representations

// test for null 
if (x.Field1 != null) 
{ 
    // retrieve non-null value 
    int v = (int)x.Field1; 
 
    // WARNING - no type checking at compilation time occurs 
    // so the following will compile, but will fail at runtime: 
    bool b = (bool)x.Field1; 
 
    // set to null 
    x.Field1 = null; 
 
    // set to not-null 
    x.Field1 = 42; 
}

// retrieve non-null value 
int v = (int)x.Field1; 
 
// set to not null 
x.Field1 = 42;

// test for null 
if (!x.Field1_IsNull) 
{ 
    // retrieve non-null value 
    int v = x.Field1; 
 
    // set to null 
    x._SetNull_Field1(); 
 
    // set to not-null 
    x.Field1 = 42; 
}

// test for null 
if (!x.Field1.IsNull) 
{ 
    // retrieve non-null value 
    int v = x.Field1.Value; 
 
    // set to null 
    x.Field1 = SqlInt32.Null; 
 
    // set to not-null 
    x.Field1 = 42; 
}

// test for null 
if (x.Field1 != null) 
{ 
    // retrieve non-null value, no other casts are allowed here 
    int v = (int)x.Field1; 
 
    // set to null 
    x.Field1 = null; 
 
    // set to not-null 
    x.Field1 = 42; 
}

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" .

5.1.1.App.config - style configuration

<add name="dataSourceName.parameterName" value="parameterValue"/> 

<configuration> 
  <appSettings> 
    <add key="default.connectionString" 
         value="Integrated Security=true;Server=.;Database=MyDatabase"/> 
    <add key="default.sqlDialect" value="mssql"/> 
  </appSettings> 
</configuration> 

5.1.2.Shared XML configuration

<configuration> 
  <default> 
    <connectionString> 
      Integrated Security=true;Server=.;Database=MyDatabase 
    </connectionString> 
    <sqlDialect> 
      mssql 
    </sqlDialect> 
  </default> 
</configuration> 

using Sooda; 
 
[assembly: SoodaConfig(XmlConfigFileName="sooda.config.xml")]

<configuration> 
  <appSettings> 
    <add key="sooda.config" value="xmlconfig"/> 
    <add key="sooda.xmlconfigfile" value="sooda.config.xml"/> 
  </appSettings> 
</configuration> 

<configuration> 
  <appSettings> 
    <add key="sooda.hostname" value="ALTERNATIVEHOSTNAME"/> 
  </appSettings> 
</configuration> 

5.1.3.Custom configuration

using Sooda; 
 
[assembly: SoodaConfig(ProviderType=typeof(MyNamespace.MyProvider))]

<configuration> 
  <appSettings> 
    <add key="sooda.config" 
         value="MyNamespace.MyProvider, MyAssembly, Version=..., PublicKeyToken=..."/> 
  </appSettings> 
</configuration> 

using Sooda; 
using Sooda.Config; 
 
class Program 
{ 
    public static void Main(string[] args) 
    { 
        // create the config provider object 
        ISoodaConfigProvider provider = new MyNamespace.MyProvider(); 
 
        // make it active 
        SoodaConfig.SetConfigProvider(provider); 
    } 
}

You can configure your data source either declaratively using the configuration file (5.1) or in code by properties of the SqlDataSource objects.

<configuration> 
  <appSettings> 
    <add key="default.sqlDialect" value="postgresql"/> 
  </appSettings> 
</configuration> 

using (SqlDataSource sds = new SqlDataSource("default")) 
{ 
    sds.SqlBuilder = new MySqlBuilder(); 
    // change other properties before calling Open() 
    sds.Open(); 
}

<configuration> 
  <appSettings> 
    <add key="default.commandTimeout" value="60"/> 
  </appSettings> 
</configuration> 

<configuration> 
  <appSettings> 
    <add key="default.queryTimeTraceInfo" value="0.5"/> 
    <add key="default.queryTimeTraceWarn" value="1.0"/> 
  </appSettings> 
</configuration> 

<configuration> 
  <appSettings> 
    <add key="default.disableTransactions" value="true"/> 
  </appSettings> 
</configuration> 

<configuration> 
  <appSettings> 
    <add key="default.stripWhitespaceInLogs" value="true"/> 
  </appSettings> 
</configuration> 

<configuration> 
  <appSettings> 
    <add key="default.indentQueries" value="true"/> 
  </appSettings> 
</configuration> 

<configuration> 
  <appSettings> 
    <add key="default.useSafeLiterals" value="true"/> 
  </appSettings> 
</configuration> 

INSERT INTO T1(id,name) values(100,'aaa'); 
INSERT INTO T1(id,name) values(101,'aaa'); 
INSERT INTO T1(id,name) values(102,'aaa'); 
INSERT INTO T1(id,name) values(103,'aaa'); 
INSERT INTO T1(id,name) values(104,'aaa');

INSERT INTO T1(id,name) values(100,'aaa');

INSERT INTO T1(id,name) values(101,'aaa');

INSERT INTO T1(id,name) values(102,'aaa');

INSERT INTO T1(id,name) values(103,'aaa');

INSERT INTO T1(id,name) values(104,'aaa');

<configuration> 
  <appSettings> 
    <add key="default.disableUpdateBatch" value="true"/> 
  </appSettings> 
</configuration> 

<configuration> 
  <appSettings> 
    <add key="default.connectionType" 
         value="Npgsql.NpgsqlConnection, Npgsql, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7"/> 
  </appSettings> 
</configuration> 

<configuration> 
  <appSettings> 
    <add key="default.connectionString" 
         value="server=127.0.0.1;user id=jaak;password=jaak;database=SoodaUnitTests;encoding=unicode"/> 
  </appSettings> 
</configuration> 

Sooda has some options which are not data source specific. These options have the name prefix sooda.

<configuration> 
  <appSettings> 
    <add key="sooda.hostname" value="ALTERNATIVEHOSTNAME"/> 
  </appSettings> 
</configuration> 

Each database-backed object in Sooda is associated with a transaction represented by an instance of SoodaTransaction class. Transactions provide database connection management, identity management (ensuring reference-level object identity as mentioned in section "1.2.2. Primary Keys and Object Identity" ).

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:

using (SoodaTransaction t = new SoodaTransaction()) 
{ 
    // perform operations on Sooda objects here 
    t.Commit(); 
}

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.

6.1.1.Creating and managing transactions

using (SoodaTransaction t = new SoodaTransaction()) 
{ 
    // perform operations on Sooda objects here 
    t.Commit(); 
}

SoodaTransaction t = new SoodaTransaction(); 
try 
{ 
    // operations here 
    t.Commit(); 
} 
finally 
{ 
    t.Dispose(); 
}

6.1.2.Implicit transactions

using (SoodaTransaction transaction = new SoodaTransaction()) 
{ 
    Person person; 
    PersonList personList; 
 
    // object construction - implicit transaction 
    person = new Person(); 
 
    // load single objects 
    person = Person.Load(10); 
    person = Person.GetRef(10); 
    person = Person.LoadSingleObject(PersonField.Name == "Mary Manager"); 
    person = Person.FindByName("Mary Manager"); 
 
    // load collection 
    personList = Person.GetList(PersonField.Name == "Mary Manager"); 
    personList = Person.FindListByName("Mary%"); 
}

using (SoodaTransaction transaction = new SoodaTransaction(SoodaTransactionOptions.NoImplicit)) 
{ 
    Person person; 
    PersonList personList; 
 
    // object construction - explicit transaction passed to constructor 
    person = new Person(transaction); 
 
    // load single objects - transaction passed as first argument 
    person = Person.Load(transaction, 10); 
    person = Person.GetRef(transaction, 10); 
    person = Person.LoadSingleObject(transaction, PersonField.Name == "Mary Manager"); 
    person = Person.FindByName(transaction, "Mary Manager"); 
 
    // load collection - transaction passed as first argument 
    personList = Person.GetList(transaction, PersonField.Name == "Mary Manager"); 
    personList = Person.FindListByName(transaction, "Mary%"); 
}

using (SoodaTransaction t1 = new SoodaTransaction()) 
{ 
    // t1 is implicit transaction 
    using (SoodaTransaction t2 = new SoodaTransaction()) 
    { 
        // t2 is implicit transaction 
        using (SoodaTransaction t3 = new SoodaTransaction()) 
        { 
            // t3 is implicit transaction 
        } 
        // t2 is implicit transaction 
    } 
    // t1 is implicit transaction 
}

To create a new persistent object in Sooda you simply invoke its constructor, optionally passing the transaction reference. There are 3 constructors generated by Sooda:

• default constructor - takes no arguments and associates newly created object with the implicit transaction
• constructor which takes SoodaTransaction as an argument - associates newly created object with the specific transaction
• special constructor which takes SoodaConstructor as an argument - this constructor should never be called by the user code and is used internally by Sooda

Typical code to create an object, set its properties and save to the database is:

using (SoodaTransaction t = new SoodaTransaction()) 
{ 
     // create new object 
 
     Person p = new Person(); 
 
     // set properties 
     p.Name = "Nancy Newcomer"; 
     p.Sex = "F"; 
     p.Address = "1 Microsoft Way"; 
     p.ZipCode = "98052"; 
     p.City = "Redmond"; 
     p.State = "WA"; 
     p.BirthDate = new DateTime(1980, 1, 1); 
 
     // commit 
     t.Commit(); 
}

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:

using (SoodaTransaction t = new SoodaTransaction()) 
{ 
     // We have a new customer who whishes to make his first order of two items 
     // We want to make this transactional 
 
     Customer c = new Customer(); 
     Order o = new Order(); 
     OrderItem i1 = new OrderItem(); 
     OrderItem i2 = new OrderItem(); 
 
     c.Name = "Caroline Customer"; 
     c.Address = "..."; 
 
     o.ShipTo = c.Address; 
     o.Customer = c; 
 
     i1.ItemType = Item.Foo; 
     i1.Quantity = 10; 
 
     i2.ItemType = Item.Bar; 
     i2.Quantity = 10; 
 
     o.Items.Add(i1); 
     o.Items.Add(i2); 
 
     t.Commit(); 
 
     // Sooda properly orders the SQL INSERT statements: 
     // 
     // insert into Customer ... 
     // insert into Order ... 
     // insert into OrderItem ... 
     // insert into OrderItem ... 
     // 
     // SQL statements issued in a different order would violate 
     // referential identity constraints 
}

There are various ways to load objects from the database depending on the desired result:

• Get a reference to the object without loading any data from the database (lazy loading)
• Load single object from the database given its primary key
• Load single object or list of objects that match the specified criteria
• Load all objects from a collection

6.3.1.Loading objects by primary key

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

6.3.2.Loading objects that match specific criteria

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

using (SoodaTransaction t = new SoodaTransaction()) 
{ 
    foreach (Person p in Person.GetList( 
        new SoodaWhereClause("City={0} and State={1}",  
            "Redmond", "WA"), 5)) 
    { 
        Console.WriteLine("name: {0}", p.Name); 
    } 
}

using (SoodaTransaction t = new SoodaTransaction()) 
{ 
    foreach (Person p in Person.GetList( 
        PersonField.City == "Redmond" && PersonField.State == "WA")) 
    { 
        Console.WriteLine("name: {0}", p.Name); 
    } 
}

using (SoodaTransaction t = new SoodaTransaction()) 
{ 
    foreach (Person p in Person.GetList( 
        PersonField.City == "Redmond" && PersonField.State == "WA",  
        SoodaOrderBy.Ascending("LastName"))) 
    { 
        Console.WriteLine("name: {0}", p.Name); 
    } 
}

using (SoodaTransaction t = new SoodaTransaction()) 
{ 
    // add a person, whose address matches the following SOQL query 
    Person newPerson = new Person(); 
 
    newPerson.Name = "Nancy Newcomer"; 
    newPerson.City = "Redmond"; 
    newPerson.State = "WA"; 
 
    // GetList() without precommit - Nancy Newcomer is  
    // not returned 
 
    foreach (Person p in Person.GetList( 
        PersonField.City == "Redmond" && PersonField.State == "WA", 
        SoodaSnapshotOptions.NoWriteObjects)) 
    { 
        Console.WriteLine("name: {0}", p.Name); 
    } 
 
    // GetList() with precommit - Nancy Newcomer included  
    // in the list of matching objects 
 
    foreach (Person p in Person.GetList( 
        PersonField.City == "Redmond" && PersonField.State == "WA")) 
    { 
        Console.WriteLine("name: {0}", p.Name); 
    } 
}

There are 3 types of collections in Sooda:

• one-to-many collections
• many-to-many collections
• snapshot collections

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.

The ISoodaObjectList interface has the following methods: (OTM means One-To-Many collections, MTM is Many-To-Many collections, SNAP - snapshots):

o = collection[0];

collection[0] = o;

collection.Add(o);

collection.Remove(o);

if (collection.Contains(o)) 
{ 
    // o is in the collection 
} 
else 
{ 
    // o is not in the collection 
}

int pos = collection.IndexOf(o); 
 
if (pos >= 0) 
{ 
    // o is in the collection at position 'pos' 
} 
else 
{ 
    // o is not in the collection 
}

collection.Insert(3,o);

collection.RemoveAt(3);

collection.Clear();

// create array to hold collection items 
Person[] array = new Person[collection.Count]; 
 
// copy them 
collection.CopyTo(array,0);

int count = collection.Count;

// foreach() uses GetEnumerator() internally 
foreach (Person p in collection) 
{ 
}

collection.GetItem(10);

newCollection = collection.GetSnapshot();

// select first 5 objects 
newCollection = collection.SelectFirst(5);

// select last 5 objects 
newCollection = collection.SelectLast(5);

// select objects 3,4,5,6 from the collection  
// (7th object is not included in the result) 
newCollection = collection.SelectLast(3,7);

bool FilterMethod(SoodaObject obj) 
{ 
    Person p = (Person)obj; 
 
    return p.Name != "Mary Manager"; 
} 
 
// filter by delegate 
collection2 = collection.Filter( 
    new SoodaObjectFilter(this.FilterMethod)); 
 
// filter by where clause 
collection2 = collection.Filter( 
    new SoodaWhereClause("Name != {0}",  
        "Mary Manager")); 
 
// filter by typed boolean expression 
collection2 = collection.Filter( 
    PersonField.Name != "Mary Manager");

// 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:

using (SoodaTransaction t = new SoodaTransaction()) 
{ 
    // load Customer from the database 
    Customer c = Customer.Load(10); 
 
    // loop through the customer's orders and display their summaries 
    // c.Orders is a one-to-many collection 
    foreach (Order o in c.Orders) 
    { 
        Console.WriteLine("o.OrderDate: {0}", o.OrderDate); 
        Console.WriteLine("o.ShipTo: {0}", o.ShipTo); 
 
        Console.WriteLine("Item count: {0}", o.Items.Count); 
 
        // now display the details of each ordered item 
        foreach (OrderItem it in o.Items) 
        { 
            Console.WriteLine("item: {0} quantity: {1}", it.ItemType, it.Quantity); 
        } 
    } 
 
    // create new order with 2 items 
 
    Order newOrder = new Order(); 
    newOrder.ShipTo = c.Address; 
 
    OrderItem i1 = new OrderItem(); 
    OrderItem i2 = new OrderItem(); 
 
    c.Name = "Caroline Customer"; 
    c.Address = "..."; 
    c.Orders.Add(newOrder); 
 
    i1.ItemType = Item.Foo; 
    i1.Quantity = 10; 
 
    i2.ItemType = Item.Bar; 
    i2.Quantity = 10; 
 
    o.Items.Add(i1); 
    o.Items.Add(i2); 
 
    // display orders sorted by their date 
 
    foreach (Order o in c.Orders.Sort(OrderField.Date)) 
    { 
        Console.WriteLine("Order #{0} Date: {1}", o.OrderID, o.Date); 
    } 
}

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:

// load person  
Person p1 = Person.GetRef(10); 
 
// get a reference to a person 
Person p2 = Person.GetRef(10); 
 
// load person by text-based query 
Person p3 = Person.LoadSingleObject(new SoodaWhereClause("Id = 10")); 
 
// load person by typed query 
Person p4 = Person.LoadSingleObject(PersonField.Id == 10); 
 
// some O/R mappers only guarantee this: 
 
Assert.AreEqual(p1, p2); 
Assert.AreEqual(p1, p3); 
Assert.AreEqual(p1, p4); 
 
// but Sooda also provides reference identity guarantee 
 
Assert.AreSame(p1, p2); 
Assert.AreSame(p1, p3); 
Assert.AreSame(p1, p4); 
 
// 
// NOTATION (using NUnit-syntax): 
// 
// Assert.AreSame(a,b) asserts that Object.ReferenceEquals(a,b) 
// Assert.AreEqual(a,b) asserts that a.Equals(b) and b.Equals(a) 
//

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.
• DirtyObjects property - returns a collection of dirty objects (objects modified within the transaction)
• DeletedObjects property - returns a collection of objects marked for delete
• 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:

using (SoodaTransaction tran = new SoodaTransaction()) 
{ 
    // create, modify or delete objects here 
 
    Customer c = Customer.Load(1); 
    c.Address = "New Address"; 
 
    // do not call Commit(), but call Serialize() instead 
    string serializedXml = tran.Serialize(); 
 
    // write the state of transaction to a file 
    File.WriteAllText("c:\\temp\\transaction.xml", serializedXml); 
}

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

using (SoodaTransaction tran = new SoodaTransaction()) 
{ 
    // load the transaction from a file 
    string 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:

<transaction> 
  <object mode="insert" class="Announcement"> 
    <key ordinal="0" type="int32" value="1"/> 
  </object> 
  <object mode="insert" class="ActivityObjectInsert"> 
    <key ordinal="0" type="int32" value="222"/> 
  </object> 
  <object mode="update" class="Announcement"> 
    <key ordinal="0" type="int32" value="1"/> 
    <field name="TimeStamp" type="datetime" value="07/19/2006 09:26:10"/> 
    <field name="ActiveFlag" type="boolint" value="true"/> 
    <field name="Description" type="string" 
           value="we have a network problem blah blah blah"/> 
    <field name="Type" type="int32" value="1"/> 
    <field name="ValidFrom" type="datetime" value="07/19/2006 09:26:10"/> 
    <field name="ValidUntil" type="datetime" value="07/19/2006 09:26:10"/> 
  </object> 
  <object mode="update" class="ActivityObjectInsert"> 
    <key ordinal="0" type="int32" value="222"/> 
    <field name="ClassName" type="string" value="Announcement"/> 
    <field name="KeyValue" type="int32" value="1"/> 
    <field name="TimeStamp" type="datetime" value="07/19/2006 09:26:10"/> 
    <field name="Person" type="int32" value="1"/> 
    <field name="Type" type="int32" value="4"/> 
    <field name="SummaryPlainText" type="string" value="Object has been created"/> 
    <field name="SummaryCRTFFile" type="string" value=""/> 
    <field name="Action" type="int32" value="2"/> 
  </object> 
</transaction> 

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.

  bool oldValue = obj.DisableFieldUpdateTriggers(); 
  try 
  { 
      // modify object - no BeforeFieldUpdate or AfterFieldUpdate triggers are called 
      obj.Name = "New Name"; 
      obj.Age = 123; 
  } 
  finally 
  { 
      // restore field update triggers to the saved state 
      obj.EnableFieldUpdateTriggers(oldValue); 
  }

• 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.
• object Evaluate(SoqlExpression expression[, bool throwOnError]) - evaluates the SOQL expression (7) in memory and returns its value. throwOnError causes the function to either throw exception or return null on error (such as null reference exception).
• string GetLabel() - evaluates the label property and returns its string value. The name of the label property is defined with the <class label="..." /> declaration (3.2) in the schema file.

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

The field-level trigger methods are:

• void BeforeFieldUpdate(string fieldName, object oldValue, object newValue) - fires immediately before any of the fields is updated
• void AfterFieldUpdate(string fieldName, object oldValue, object newValue) - fires immediately after any the fields has been modified
• void BeforeFieldUpdate_FieldName(object oldValue, object newValue) - fires immediately before field FieldName is modified
• void AfterFieldUpdate_FieldName(object oldValue, object newValue) - fires immediately after field FieldName has been modified

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:

public class Person : Person_Stub 
{ 
    // constructors omitted 
 
    protected override void BeforeObjectInsert() 
    { 
        base.BeforeObjectInsert(); 
        Console.WriteLine("Object is about to be inserted."); 
    } 
 
    protected override void AfterObjectInsert() 
    { 
        base.AfterObjectInsert(); 
        Console.WriteLine("Object has been inserted."); 
    } 
 
    protected override void BeforeObjectUpdate() 
    { 
        base.BeforeObjectUpdate(); 
        Console.WriteLine("Object is about to be updated."); 
    } 
 
    protected override void AfterObjectUpdate() 
    { 
        base.AfterObjectUpdate(); 
        Console.WriteLine("Object has been updated."); 
    } 
 
    protected override void BeforeObjectDelete() 
    { 
        base.BeforeObjectDelete(); 
        Console.WriteLine("Object is about to be deleted."); 
    } 
 
    protected override void AfterObjectDelete() 
    { 
        base.AfterObjectDelete(); 
        Console.WriteLine("Object has been deleted."); 
    } 
 
    protected override void 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. 
            throw new Exception("Cannot set Age to 42!"); 
        } 
    } 
 
    protected override void 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 <class triggers="false" /> and you can enable them individually at the field level by specifying <field forceTrigger="true" />. For example:

<schema> 
  <class name="Employee"> 
    <table name="Employee"> 
      <field name="ID" type="Integer" primaryKey="true"/> 
      <field name="Name" type="String" size="40"/> 
      <field name="Salary" type="Decimal"/> 
      <field name="HireDate" type="DateTime"/> 
      <field name="Active" type="BooleanAsInteger"/> 
    </table> 
  </class> 
  <class name="Vehicle" triggers="false"> 
    <table name="Vehicle"> 
      <field name="ID" type="Integer" primaryKey="true"/> 
      <field name="Price" type="Decimal"/> 
      <field name="Description" type="String" size="40" forceTrigger="true"/> 
    </table> 
  </class> 
</schema> 

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 <class name="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.

-- fetch current value of the primary key 
SELECT @kv = key_value FROM KeyGen where key_name = 'ClassName'; 
 
-- update KeyGen table with the new possible primary key 
UPDATE KeyGen 
SET key_value = @kv + 10 
WHERE key_name = 'ClassName' AND key_value = @kv 

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.

<configuration> 
  <appSettings> 
    <add key="default.keygentable.name" value="MyKeyGen"/> 
    <add key="default.keygentable.keycolumn" value="keys"/> 
    <add key="default.keygentable.valuecolumn" value="values"/> 
  </appSettings> 
</configuration> 

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.

Consider this example:

using (SoodaTransaction t = new SoodaTransaction()) 
{ 
    // create new person object but don't commit it 
 
    Person p = new Person(); 
    p.Name = "Nancy Newcomer"; 
 
    // now get a list of all persons 
 
    PersonList pl = Person.GetList(true); 
}

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

using (SoodaTransaction t = new SoodaTransaction()) 
{ 
    Person p; 
 
    Person.GetList(PersonField.Name == "Mary Manager"  
                && PersonField.Group.Name == "Managers"  
                && PersonField.OrgUnit.Name == "Sales Department"); 
}

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.

Sooda supports two caching levels:

• transaction identity cache - also called Level 1 cache, whose primary goal is to ensure object referential identity
• 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 --> 
    <add key="sooda.cachingPolicy" value="smallmedium"/> 
    <!-- retain objects for 3600 seconds - one hour --> 
    <add key="sooda.cachingPolicy.expirationTimeout" value="3600"/> 
    <!-- each access to an object resets the one-hour timeout --> 
    <add key="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:

// cache small and medium objects 
SoodaCacheSmallAndMediumPolicy policy = new SoodaCacheSmallAndMediumPolicy(); 
 
// retain objects for one hour 
policy.ExpirationTimeout = TimeSpan.FromHours(1); 
 
// each access to an object resets the one-hour timeout 
policy.SlidingExpiration = true; 
 
// make the caching policy active for new transactions 
SoodaCache.DefaultCachingPolicy = policy;

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

In addition to various caching policies, Sooda supports pluggable storage engines. By default Sooda comes with two:

• inprocess - default - in-process LRU cache
• none - no caching

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:

<configuration> 
  <appSettings> 
    <add key="sooda.cache" 
         value="MySuperCache.SoodaCachingProvider, MySuperCache, PublicKeyToken=..., Version=..."/> 
  </appSettings> 
</configuration> 

You can achieve the same thing in code, by setting the DefaultCache property of the SoodaCache class:

MySuperCache.SoodaCachingProvider cache = MySuperCache.SoodaCachingProvider(); 
 
// configure 'cache' here - set some hypothetical properties 
cache.Size = 1000000; 
cache.Location = "c:\\mycache"; 
 
// make the cache active for new transactions 
SoodaCache.DefaultCache = cache;

As with the caching policies, you can also override the cache mechanism on a per-transaction basis:

using (SoodaTransaction t = new SoodaTransaction()) 
{ 
    // override default cache storage for this transaction 
    t.Cache = cache; 
}

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:

<schema xmlns="http://www.sooda.org/schemas/SoodaSchema.xsd"> 
  <datasource name="db" type="Sooda.Sql.SqlDataSource"/> 
</schema> 

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.

• Commit() method.

  Performs commit of the open transaction and starts a new one.

• Close() method.

  Closes the connection to the database.

• IDataReader ExecuteQuery(SoqlQueryExpression query, SchemaInfo schema, object[] parameters) method

  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.

• IDataReader ExecuteRawQuery(string queryText, object[] parameters) 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:

using Sooda; 
using Sooda.Sql; 
using Sooda.Schema; 
using Sooda.QL; 
 
class Program 
{ 
    static void Main(string[] args) 
    { 
        // connect to the data source 'default' defined  
        // in the configuration file 
 
        using (SqlDataSource sds = new SqlDataSource("default")) 
        { 
            sds.Open(); 
 
            // SOQL textual query 
            string soqlQuery = @"
                SELECT Name, PrimaryGroup.Name, PrimaryGroup.Members.Count
                FROM Contact 
                WHERE PrimaryGroup.Manager.Name = {0} OR Name = {1}"; 
 
            // parse the query to a SoqlQueryExpression object 
            SoqlQueryExpression queryExpression = SoqlParser.ParseQuery(soqlQuery); 
 
            // get the schema reference 
            SchemaInfo schema = Sooda.UnitTests.BaseObjects._DatabaseSchema.GetSchema(); 
 
            // prepare parameters 
            object[] parameters = new object[]  
            { 
                "Mary Manager", // positional parameter {0} 
                "Eva Employee" // positional parameter {1} 
            }; 
 
            // execute query 
            using (IDataReader reader = sds.ExecuteQuery(queryExpression, schema, parameters)) 
            { 
                // iterate the result set 
                while (reader.Read()) 
                { 
                    Console.WriteLine("name: {0} group: {1} members: {2}",  
                        reader.GetString(0),  
                        reader.GetString(1),  
                        reader.GetInt32(2)); 
                } 
            } 
        } 
    } 
} // class Program

When you run this example on the SoodaUnitTests database that comes with Sooda, it displays the following results:

name: Mary Manager group: Group1 members: 4 
name: Ed Employee group: Group1 members: 4 
name: Eva Employee group: Group2 members: 1 
name: Caroline Customer group: Group1 members: 4 
name: Chuck Customer group: Group1 members: 4

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.

6.13.1.Enabling Dynamic Fields

create table SoodaDynamicField ( 
    class varchar(32) not null, 
    field varchar(32) not null, 
    type varchar(32) not null, 
    nullable int not null, 
    fieldsize int null, 
    precision int null, 
    constraint PK_SoodaDynamicField primary key (class, field) 
); 
grant create table to soodatest 
grant references to soodatest -- SQL Server only

<datasource name="default" type="Sooda.Sql.SqlDataSource" dynamicFields="true"/> 

6.13.2.Managing Dynamic Fields

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

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

fieldInfo.IsNullable = true; 
Sooda.Schema.DynamicFieldManager.Update(fieldInfo, transaction);

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

6.13.3.Using Dynamic Fields

object value = obj["MyField"];

obj["MyField"] = value;

dynamic dynamicObj = obj; 
object value = dynamicObj.MyField; 
... 
dynamicObj.MyField = newValue;

MyObject.Linq().Where(o => o["MyField"] != null); 
MyObject.Linq().Where(o => (int) o["MyField"] == 42);

SOQL queries have syntax similar to SQL SELECT statements. Each query has the following form:

SELECT     [TOP number] [DISTINCT] select_expressions 
FROM       from_expressions 
[WHERE     boolean_expression] 
[GROUP BY  groupby_expressions] 
[HAVING    boolean_expression] 
[ORDER BY  sort_expressions]

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 <class name="..." /> and <field name="..." />. Let's assume you have the following class definition that maps table ctct to class Contact and uses field names different from column names:

<class name="Contact"> 
  <table name="ctct"> 
    <field name="ID" dbcolumn="contact_id" type="Integer" primaryKey="true"/> 
    <field name="Name" dbcolumn="contact_name" type="String" size="40"/> 
  </table> 
</class> 

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 supports the following language constructs known from SQL:

• relational operators (=, !=, <, <=, >=, >, LIKE)
• boolean operators (AND, OR)
• EXISTS(), IN(), IS NULL and IS NOT NULL predicates
• arithmetic operators (+,-,*,/ and %) with natural precedence and associativity
• arithmetic (-) and boolean (NOT) negation

In addition the following new constructs are supported:

• path expressions (7.2.1) for specifying join-less criteria (t.PrimaryGroup.Manager.Name)
• one-to-many and many-to-many collections (7.2.2) supporting Count and Contains operations
• named constants (as defined in the mapping schema (3) )
• TRUE and FALSE literals
• simplified subqueries
• functions
• raw SQL queries
• SoodaClass operator

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.

7.2.1.Path Expressions

SELECT * 
FROM   Contact 
WHERE  PrimaryGroup.Manager.Name = 'Mary Manager'

7.2.2.Collections

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

-- return the name of each person followed by the number  
-- of members in his group 
 
select  Name,  
        PrimaryGroup.Members.Count 
from    Contact

-- get the names of all groups that Mary Manager belongs to 
-- Contact.Mary is a named constant 
 
select  Name 
fromGroup 
where   Members.Contains(Contact.Mary);

-- get the names of all groups including Mary Manager 
 
select  Name 
fromGroup 
where   Members.Contains(1);

-- return all groups where the manager 
-- is not one of the members 
 
select  * 
fromGroup  
wherenot Members.Contains(Manager)

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

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

7.2.3.SoodaClass

-- select vehicles which are bikes or super bikes 
 
select * 
from   Vehicle v 
where  v.SoodaClass = 'Bike' or v.SoodaClass = 'SuperBike'

7.2.4.Simplified Subqueries

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

-- Using simplified subqueries: 
select * 
from Group 
where Members.Contains(Contact where Name = 'Mary Manager')

select * 
from Group g 
where exists (Group g2 where g.ID <> g2.ID and g.Name = g2.Name)

7.2.5.Raw Queries

// use SQL Server-specific full-text search function "contains()" 
 
select *  
from   Contact 
where  rawquery(contains(*,'quick'))

-- find all contacts employed earlier than 10 years ago 
 
select *  
from   Contact 
where  rawquery(datediff(y, SOQL{{HireDate}}, getdate() > 10)

Formal syntax of SOQL is presented below. The following notation is used:

• <symbol> ::= expression - production
• [X] - X is optional
• {X} - X can be repeated zero or more times
• a | b - either a or b
• 'token' or TOKEN - terminal symbol

query_expression ::= 
            SELECT     [TOP number] [DISTINCT] select_expressions 
            FROM       from_expressions 
            [WHERE     boolean_expression] 
            [GROUP BY  groupby_expressions] 
            [HAVING    boolean_expression] 
            [ORDER BY  sort_expressions] 
 
select_expressions ::= select_expression { ',' select_expression } 
 
from_expressions ::= from_expression { ',' from_expression } 
 
groupby_expressions ::= expression { ',' expression } 
 
sort_expressions ::= sort_expression { ',' sort_expression } 
 
select_expression := expression [ AS alias ] 
 
from_expression ::= class_name [ AS alias ] 
 
expression ::= boolean_or 
 
boolean_or ::= boolean_and { OR boolean_and } 
 
boolean_and ::= boolean_predicate { AND boolean_predicate } 
 
boolean_predicate ::=  
            EXISTS '(' query_expression ')' 
            | EXISTS '(' simplified_query ')' 
            | boolean_relation 
 
boolean_expression ::= boolean_or 
 
class_or_relation_name ::= class_name | relation_name 
 
sort_expression := expression [ ASC | DESC ] 
 
boolean_relation ::= additive_expression 
            | additive_expression '=' additive_expression 
            | additive_expression '==' additive_expression 
            | additive_expression '<>' additive_expression 
            | additive_expression '!=' additive_expression 
            | additive_expression '<' additive_expression 
            | additive_expression '>' additive_expression 
            | additive_expression '<=' additive_expression 
            | additive_expression '>=' additive_expression 
            | additive_expression LIKE additive_expression 
            | additive_expression IS [NOT] NULL 
            | additive_expression IN '(' expression { ',' expression } ')' 
 
additive_expression ::= multiplicative_expression 
            | additive_expression '+' multiplicative_expression 
            | additive_expression '-' multiplicative_expression 
 
multiplicative_expression ::= literal_expression 
            | multiplicative_expression '*' literal_expression 
            | multiplicative_expression '/' literal_expression 
            | multiplicative_expression '%' literal_expression 
 
literal_expression ::=  
            number 
            string 
            positional_parameter 
            '(' query_expression ')' 
            '(' expression ')' 
            NULL 
            TRUE 
            FALSE 
            SOODACLASS 
            RAWQUERY '(' raw_query ')' 
            function_call 
            path_expression 
            '(' class_name WHERE boolean_expression ')' 
            '-' literal_expression 
            NOT boolean_expression 
 
positional_parameter ::= '{' number [ ':' parameter_modifiers ] '}' 
 
parameter_modifiers ::= type_name 
 
function_call ::= function_name '(' [ function_arguments ] ')' 
 
function_arguments ::= expression { ',' expression } 
 
simplified_query ::= class_name WHERE boolean_expression 
 
path_expression ::= field_name 
            | path_expression '.' field_name 
            | path_expression '.' CONTAINS '(' expression ')' 
            | path_expression '.' CONTAINS '(' query_expression ')' 
            | path_expression '.' CONTAINS '(' simplified_query ')' 
            | path_expression '.' COUNT  
            | path_expression '.' SOODACLASS 
            | '*' 
            | path_expression '.' '*' 
 
-- the string may contain SOQL{{...}} fragments which will be 
-- inserted into the SQL sent to RDBMS 
 
raw_query ::= string 
 
alias ::= name 
 
class_name ::= name 
 
relation_name ::= name 
 
name ::= /* Non-empty sequence of letters, numbers and underscore characters,
            which cannot start with a digit */ 
 
number ::= /* Non-empty quence of digits with an optional decimal 
             point: 123 or 3.141592 */ 
 
string ::= /* String literal in apostrophes such as 'Mary Manager'.
              You can represent the apostrophe character itself by doubling it
              thus d'Artagnan needs to be written as 'd''Artagnan' */

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.

ContactList cl; 
 
// get the list of contacts whose names start with 'Mary' 
cl = Contact.GetList(new SoodaWhereClause("Name like 'Mary%'"));

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.

ContactList cl; 
string lookFor = "Mary Manager"; 
 
// get the list of contacts matching the specified name 
// note that {0} is not enclosed in apostrophes. 
cl = Contact.GetList(new SoodaWhereClause("Name = {0}", lookFor));

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.

ContactField.PrimaryGroup.Manager.Name

PrimaryGroup.Manager.Name

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

Name like 'Mary%'

ContactField.PrimaryGroup.Manager.IsNull()

PrimaryGroup.Manager is null

ContactField.PrimaryGroup.Manager.IsNoNull()

PrimaryGroup.Manager is not null

ContactField.PrimaryGroup.Manager.FirstName.In("Mary","Edmund")

PrimaryGroup.Manager.FirstName in ('Mary','Edmund')