Examples

Sooda provides an easy-to-use and convenient way to manipulate objects and their relationships stored in relational databases. It's best to demonstrate this with some examples:

Accessing object attributes

All database entities are mapped to .NET objects, where table columns are mapped to object properties. With this approach, object access becomes very easy, as shown in the following example:

using System; 
using Sooda; 
 
class Sample1  
{ 
    static void Main() 
    { 
        using (SoodaTransaction transaction = new SoodaTransaction()) 
        { 
            // load group with primary key 10 
            Group g = Group.Load(10); 
 
            // display its manager's name 
            Console.WriteLine("name: {0}", g.Manager.Name); 
 
            // double the salary 
            g.Manager.Salary *= 2; 
 
            // save changes to the database 
            transaction.Commit(); 
        } 
    } 
}

Easy-to-use relationships

Database relationships (both one-to-many and many-to-many) are mapped as collections implementing ICollection interface and providing type-safe wrappers.

Basic operations on collections are simple and intuitive. All standard collection methods are available, namely Add, Remove, Contains, iterating with foreach() and indexers, plus some convenience methods:

using System; 
using Sooda; 
 
class Sample2 { 
    static void Main() { 
        using (SoodaTransaction transaction = new SoodaTransaction()) { 
            // load group with primary key 10 
            Group g = Group.Load(10); 
 
            // check if the manager is in role 'Customer' 
            if (g.Manager.Roles.Contains(Role.Customer)) { 
                // we have a manager that is a customer 
                Console.WriteLine("{0} is both a manager and a customer!", 
                                  g.Manager.Name); 
            } 
 
            // create a new contact that will become a member of Group[10] 
            Contact newEmployee = new Contact(); 
 
            // add necessary roles 
            newEmployee.Roles.Add(Role.Customer); 
            newEmployee.Roles.Add(Role.Employee); 
 
            // set some required attributes 
            newEmployee.Name = "Nancy Newcomer"; 
            newEmployee.Type = ContactType.Employee; 
 
            // add new employee as a member of the group 
            g.Members.Add(newEmployee); 
 
            // print some debugging information 
            Console.WriteLine("Added {0} as a new member of {1}", 
                              newEmployee.Name, g.Name); 
 
            // display group members - this includes Nancy Newcomer which 
            // has just been added 
            foreach (Contact c in g.Members) { 
                Console.WriteLine("member name: {0}", c.Name); 
            } 
 
            // check if the administrator is a member of the group 
            if (g.Members.Contains(Contact.Administrator)) { 
                Console.WriteLine("Administrator is a member of {0}", g.Name); 
            } 
 
            // commit changes to the database 
            transaction.Commit(); 
        } 
    } 
}

Inheritance and polymorphism

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). Sooda supports two methods of representing inheritance in the database: table-per-type (TPT) and table-per-hierarchy (TPH).

using System; 
using Sooda; 
 
class Sample3 { 
    static void Main() { 
        using (SoodaTransaction transaction = new SoodaTransaction()) { 
            Vehicle v; 
 
            // load vehicle with #10 
            v = Vehicle.Load(10); 
 
            if (v is Bike) { 
                Console.WriteLine("vehicle #10 is a bike."); 
 
                // do something with the bike 
                Bike b = (Bike)v; 
            } 
        } 
    } 
}

Query language

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

SOQL 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 for specifying join-less criteria (t.PrimaryGroup.Manager.Name)
• one-to-many and many-to-many collections supporting Count and Contains operations
• named constants (as defined in the mapping schema)
• TRUE and FALSE literals
• simplified subqueries
• functions
• raw SQL queries
• SoodaClass operator

// get all products whose category names match: 
 
foreach (Product p in Product.GetList( 
    new SoodaWhereClause("Category.Name in ({0},{1})", "Water creatures", "Liquids"),  
    SoodaOrderBy.Ascending("Product.Name"))) 
{ 
    Console.WriteLine("product: {0} category: {1}", p.Name, p.Category.Name); 
} 
 
// examples of more complex Soql expressions: 
// 
// find all suppliers which ship beverages 
 
Supplier.GetList(new SoodaWhereClause( 
            "Products.Contains(Product where Category.Name = 'Beverages')")); 
 
//  
// find all products supplied by Polish suppliers 
// who belong to categories which have at least 10 products 
Product.GetList(new SoodaWhereClause( 
            "Supplier.Country = 'Poland' and Category.ProductsInThisCategory.Count > 10" 
            ));

Typed Query language

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.

// get all products whose category names match: 
 
foreach (Product p in Product.GetList( 
    ProductField.Category.Name.In("Water creatures", "Liquids") 
    SoodaOrderBy.Ascending(ProductField.Name))) 
{ 
    Console.WriteLine("product: {0} category: {1}", p.Name, p.Category.Name); 
} 
 
// examples of more complex Soql expressions: 
// 
// find all suppliers which ship beverages 
 
Supplier.GetList( 
        SupplierField.Products.ContainsProductWhere( 
            ProductField.Category.Name == "Beverages")); 
 
//  
// find all products supplied by Polish suppliers 
// who belong to categories which have at least 10 products 
Product.GetList(ProductField.Supplier.Country == "Poland" 
        && ProductField.Category.ProductsInThisCategory.Count > 10);

Schema definition

Sooda uses an XML-based schema definition. You provide an XML document that describes the mapping between the database (tables, columns, relationships) and CLI world (classes, properties, collections). By writing an appropriate schema you can use features like column-renaming, lazy-loading, split-classes, inheritance, collections, enumerations and so on.

The following example demonstrates a mapping schema for the Northwind database.

<schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
        xmlns="http://www.sooda.org/schemas/SoodaSchema.xsd" 
        xml:space="preserve"> 
  <!--
    We'll be connecting to the SQL data source.
    --> 
  <datasource name="default" type="Sooda.Sql.SqlDataSource"/> 
  <class name="Category"> 
    <table name="Categories"> 
      <field name="ID" dbcolumn="CategoryID" type="Integer" primaryKey="true" 
             nullable="false"/> 
      <field name="Name" dbcolumn="CategoryName" type="String" size="15" 
             nullable="false"/> 
      <field name="Description" dbcolumn="Description" type="String" size="1" 
             nullable="true"/> 
      <field name="Picture" dbcolumn="Picture" type="Blob" nullable="true"/> 
    </table> 
    <collectionOneToMany name="Products" class="Product" foreignField="Category"/> 
    <!-- an example constant --> 
    <const name="Beverages" key="1"/> 
    <const name="Seafood" key="8"/> 
  </class> 
  <class name="Product"> 
    <table name="Products"> 
      <field name="ID" dbcolumn="ProductID" type="Integer" primaryKey="true" 
             nullable="false"/> 
      <field name="Name" dbcolumn="ProductName" type="String" size="40" 
             nullable="false"/> 
      <field name="Supplier" dbcolumn="SupplierID" type="Integer" 
             nullable="true" references="Supplier"/> 
      <field name="Category" dbcolumn="CategoryID" type="Integer" 
             nullable="true" references="Category"/> 
      <field name="QuantityPerUnit" dbcolumn="QuantityPerUnit" type="String" 
             size="20" nullable="true"/> 
      <field name="UnitPrice" dbcolumn="UnitPrice" type="Decimal" 
             nullable="true"/> 
    </table> 
  </class> 
  <class name="Supplier"> 
    <table name="Suppliers"> 
      <field name="ID" dbcolumn="SupplierID" type="Integer" primaryKey="true" 
             nullable="false"/> 
      <field name="Company" dbcolumn="CompanyName" type="String" size="40" 
             nullable="false"/> 
      <field name="ContactName" dbcolumn="ContactName" type="String" size="30" 
             nullable="true"/> 
      <field name="ContactTitle" dbcolumn="ContactTitle" type="String" size="30" 
             nullable="true"/> 
      <field name="Address" dbcolumn="Address" type="String" size="60" 
             nullable="true"/> 
      <field name="City" dbcolumn="City" type="String" size="15" nullable="true"/> 
      <field name="Region" dbcolumn="Region" type="String" size="15" 
             nullable="true"/> 
      <field name="PostalCode" dbcolumn="PostalCode" type="String" size="10" 
             nullable="true"/> 
      <field name="Country" dbcolumn="Country" type="String" size="15" 
             nullable="true"/> 
      <field name="Phone" dbcolumn="Phone" type="String" size="24" 
             nullable="true"/> 
      <field name="Fax" dbcolumn="Fax" type="String" size="24" nullable="true"/> 
      <field name="HomePage" dbcolumn="HomePage" type="String" size="1" 
             nullable="true"/> 
    </table> 
    <collectionOneToMany name="Products" class="Product" foreignField="Supplier"/> 
  </class> 
  <class name="Shipper"> 
    <table name="Shippers"> 
      <field name="ID" dbcolumn="ShipperID" type="Integer" primaryKey="true" 
             nullable="false"/> 
      <field name="Company" dbcolumn="CompanyName" type="String" size="40" 
             nullable="false"/> 
      <field name="Phone" dbcolumn="Phone" type="String" size="24" 
             nullable="true"/> 
    </table> 
  </class> 
</schema>