Connecting to your database

You only have to specify your database connection once in your application. You can do this in code, or specify the connection in your application's configuration file (app.config or web.config).

We will show both methods by means of an example:

CSConfig.SetDB(
    new CSDataProviderSqlServer("Server=SQL1;Database=activa;UID=dblogin;PWD=dbpassword;")
   );

Or in your configuration file (not available in MonoTouch):

<configuration>

  <configSections>
    <section name="CoolStorage" 
      type="System.Configuration.NameValueSectionHandler, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
      requirePermission="false" />
  </configSections>

  <CoolStorage>
    <add 
      key="Connection" 
      value="CSDataProviderSqlServer, Vici.CoolStorage.SqlServer / Server=SQL1;Database=vici" />
  </CoolStorage>

</configuration>

(this example connects to the database "vici" on server "SQL1")

Defining your mapping objects

Mapping an object to a database table requires very little effort. You just have to decide on the name of the object (usually the same name as the table), and define the table fields as abstract properties:

[MapTo("Customer")]
public abstract partial class Customer : CSObject<Customer,int>
{
  public abstract int CustomerID { get; } // Primary key, so read-only
  public abstract string Name { get; set; } // Mapped to "Name" field
  public abstract string Address { get; set; } // Mapped to "Address" field
  public abstract string City { get; set; } // Mapped to "City" field
  public abstract string ZipCode { get; set; } // Mapped to "ZipCode" field
  
  [MapTo("Phone_Number")]
  public abstract string Phone { get; set; } // Mapped to "Phone_Number" field
}

(note: for MonoTouch, mapping classes have to be declared differently. See the MonoTouch section for more info)

As long as the names of your fields are the same as the field names in your table, you just have to declare the abstract properties. Only if the property name is different from the field name, you should add the MapTo() attribute to the property. CoolStorage will scan the table definition and map all properties with the same name as a column.

Note that your Customer class is derived from CSObject<Customer,int>. This may seem a little odd, but it is required to have easy access to all underlying CoolStorage methods and properties, and it also allows the underlying methods to return objects of the correct type. This eliminates the need for casting, as you will see later on.

The partial keyword is not required, but it is useful if you want to define your mapping declarations in one part of the class specification, and define some additional business logic in another partial class of the same type.

The MapTo() attribute on your class tells CoolStorage the name of the table to map this class to. You can also declare a MapTo() attribute for any of the properties to let them map to a different field name. When you define abstract properties that don't have a corresponding field in the table, a runtime error (exception) will occur.

The data types of your fields should be convertable to the types of the table fields. For example, it is ok to define a property of type int (Int32) and map it to a field of type smallint (Int16). At runtime, CoolStorage will attempt to perform the necessary type conversions.

Defining relations

Defining relations between tables (objects) is extremely simple. Let's define a second class Order:

[MapTo("Order")]
public abstract partial class Order : CSObject<Order,int>
{
  public abstract int OrderID { get; }
  public abstract DateTime OrderDate { get; set; }
}

Your database table Order defines a field CustomerID holding the ID of the customer linked to an order. You don't have to map the CustomerID field (altough you could if you wanted), but you just define a ManyToOne relation between Order and Customer:

[MapTo("Order")]
public abstract partial class Order : CSObject<Order,int>
{
  public abstract int OrderID { get; }
  public abstract DateTime OrderDate { get; set; }
  [ManyToOne]
  public abstract Customer Customer { get; set; }
}

From now on, in your code you can refer to the related customer object just by accessing the Customer property of the Order class.

Of course, when there is a many-to-one relation, there should be a corresponding one-to-many relation from Customer to Order. To complete the picture, the classes will look like this:

[MapTo("Customer")]
public abstract partial class Customer : CSObject<Customer,int>
{
  public abstract int CustomerID { get; }
  public abstract string Name { get; set; }
  public abstract string Address { get; set; }
  public abstract string City { get; set; }
  public abstract string ZipCode { get; set; }
  public abstract string Country { get; set; }

  [MapTo("Phone_Number")]
  public abstract string Phone { get; set; }

  public abstract DateTime? lastVisit { get; set; }

  [OneToMany]
  public abstract CSList<Order> Orders { get; }
}

[MapTo("Order")]
public abstract partial class Order : CSObject<Order,int>
{
  public abstract int OrderID { get; }
  public abstract DateTime OrderDate { get; set; }

  [ManyToOne]
  public abstract Customer Customer { get; set; }
}

You may wonder how CoolStorage determines the foreign key relations. It is actually very simple: it will look at the primary key of the "One" side of the relation, and assumes that the name of the foreign key is the same (according to database design best practices). If the keys cannot be determined like this, you can override the key names in the attribute declaration. You can find more information in the reference section of the documentation.

Creating a new object

Creating a new object is done by calling the static New() method of the object class:

Order order = Order.New();
//...
order.Save();

int newOrderId = order.OrderID; // get the autonumber (identity) primary key value

If the primary key is defined as an autonumber (identity) field in the database, your can retrieve the generated primary key after the object is saved.

Reading / writing a single object

Reading a single object from the database by its primary key is as easy as creating a new one:

Order order = Order.Read(orderID);

// Do something with the object

order.Save(); // Save the object back to the database

When you want to read an object using an expression, you can use the ReadFirst() method. This method will return the first object that meets the specified criteria:

Customer customer = Customer.ReadFirst("Login=@Login and Password=@Password",
                                           "@Login" , loginName,
                                           "@Password", loginPassword);

if (customer == null)
{
  Console.WriteLine("Uknown user name or bad password");
  return;
}

customer.LastVisit = DateTime.Now;

customer.Save();

As you can see in the examples above, saving an object is as simple as calling Save() on the object. Only the fields/properties that have been changed will be saved to the database, including any objects in the object's relations that have changed.

Working with object collections

Object collections are lists of objects of the same table (corresponding to the records in a table). You can create lists representing all objects in a table, or a specific subset of a list.

The following example will print a list the names of all customer in the database:

CSList<Customer> customers = new CSList<Customer>();

foreach (Customer customer in customers)
  Console.WriteLine(customer.Name);

// or using a different syntax:

foreach (Customer customer in Customer.List())
  Console.WriteLine(customer.Name);

Of course, you can filter and sort your list. The following example creates a list of all customers from the USA, ordered by Name:

CSList<Customer> customers = Customer.List("Country='USA'").OrderedBy("Name");
// or
CSList<Customer> customers = Customer.OrderedList("Name").FilteredBy("Country='USA'");
// or
CSList<Customer> customers = new CSList<Customer>("Country='USA'").OrderedBy("Name");

Paging

CoolStorage.NET allows you to read a specific range of records. The filtering is done on the database server, so only the needed records are read from the database.

For example, if we want to read customer records from record 11 to 20 (10 records):

CSList<Customer> customers = Customer.List().OrderedBy("Name").Range(11,10);

Working with relations

Earlier you learned how to define relations for your objects. Using these relations is even easier. An example tells more than a
thousand words, so here is an example that lists all orders with their order number and customer name:

CSList<Order> orders = Order.List();

foreach (Order order in orders)
{
  string customerName = order.Customer.Name;
  int orderNr = order.OrderNr;

  Console.WriteLine("Order #{0}: {1}", orderNr, customerName);
}

This example lists all customers, and for each customer a list of their orders (orders will be sorted by order number):

CSList<Customer> customers = Customer.List();

foreach (Customer customer in customers)
{
  Console.WriteLine("Customer Name = {0}", customer.Name);

  foreach (Order order in customer.Orders.OrderedBy("OrderNr"))
  {
    Console.WriteLine(" - Order #{0}", order.OrderNr);
  }
}

That's just reading, but writing is just as simple.

For example:

Order order = Order.New();
// setup the order

order.Customer Customer.Read(customerId);
order.Save();

This will automatically set all foreign keys correctly so the order and the customer are linked in the database.

The other way around works too:

Order order = Order.New();
// setup the order

Customer customer = Customer.Read(customerId);
customer.Orders.Add(order);
customer.Save();

The end result is exactly the same as the first example, but here you're working with the one-to-many relation instead of the many-to-one relation.