An introduction to a post-relational database for .NET : Matisse - Part 4

Introduction

This is the fourth article of the introductory series regarding the Matisse post-relational database for .NET. The former articles covered:

• Part 1: Overview of Matisse
• Part 2: Schema definition
• Part 3: .NET programming to insert objects

Part 4 covers ADO.NET programming. It starts with a simple example, and then illustrates how to retrieve .NET objects, not values, using the SQL REF() function, which is one of the advantages of using Matisse. The article also shows a simple GUI application using DataGrid with the post-relational database.

If you are already familiar with ADO.NET, there is nothing really new in the article except how to use the SQL REF() function to directly retrieve objects without any mapping. It is explained in the Returning Objects section below.

Simple Example

To describe ADO.NET programming in the article, I use the same schema as the one I used in the previous articles.

The next program connects to a Matisse database, executes an SQL SELECT statement, and then retrieves returned values:

using System;
using System.Data;
using com.matisse;
using com.matisse.Data;namespace ConsoleApplication1
{
  class ConsoleAppClass1
  {
    [STAThread]
    static void Main(string[] args)
    {
      // Create a connection object using a connection string

      MtDatabase myConn = new MtDatabase(
         "Server=localhost;Database=example");
      myConn.Open(); 
 
      // Create an instance of MtCommand

      IDbCommand myCmd = myConn.CreateCommand(); 
      myCmd.CommandText = "SELECT ProjectName, Budget FROM Project;";
 
      // Execute the SELECT statement. 

      // A read-only transaction started by Matisse

      IDataReader reader = myCmd.ExecuteReader();
      // Read rows

      while ( reader.Read() )
      {
        // Get value for the first and second columns

        string pname   = reader.GetString(0);
        decimal budget = reader.GetDecimal(1);
        Console.WriteLine(pname + ", " + budget);
      }
 
      // Clean up

      reader.Close();
      myCmd.Dispose();
      myConn.Close();
    }
  }
}

Here are a few explanations for this program:

The ADO.NET connection object for Matisse is created by the class MtDatabase, whose name is a little different from the standard naming convention.

The class MtDatabase and other ADO.NET related classes are defined in the com.matisse and com.matisse.Data namespaces.

Returning Objects

The above program is the most basic ADO.NET example of the type that you see everywhere. Although it demonstrates that Matisse just works like a relational product, it is not that exciting (to me). A very interesting feature of Matisse is that you retrieve objects out of ADO.NET without mapping. The next piece of code shows how to do it:

      // Create a connection object using a connection string

      MtDatabase myConn = 
        new MtDatabase("localhost", "example", 
              new MtPackageObjectFactory("MatisseApp,PersistentClasses")); 
      
      myConn.Open();
       
      // Create an instance of MtCommand

      IDbCommand myCmd = myConn.CreateCommand(); 
      myCmd.CommandText = 
        "SELECT REF(p) FROM Project p WHERE p.Budget >= 10000;";   // -- A

 
      // Execute the SELECT statement.

      MtDataReader reader = (MtDataReader) myCmd.ExecuteReader();
      while ( reader.Read() )
      {
        // Get the Project object from the ADO.NET Reader object

        Project prj = (Project) reader.GetObject(0);  // -- B

 
        // Get the manager of the project.

        // This is a navigation through relationship.

        Manager mgr = prj.ManagedBy;  // -- C 

 
        // Get the members of the project

        Employee[] members = prj.Members;  // -- D

        foreach (Employee emp in members) 
        {
          ...
        }
      }

First, you need to have the class Project generated from the database schema using the code generation tool (explained in Part 3 of this series), in order to directly retrieve objects.

As shown above, to retrieve objects using an SQL SELECT statement, use the REF function in the select-clause (line A), and call the GetObject method on the ADO.NET DataReader object (line B).

After you get a Project object, you can get the manager of the project (line C) and the members working in the project (line D) by accessing the properties of the Project object. This is another advantage of using a post-relational database. It really simplifies the data access layer.

Note that you need to pass a MtPackageObjectFactory object to the MtDatabase constructor to specify where the stub classes (e.g., Manager) are located, i.e., in the namespace MatisseApp in the assembly named PersistentClasses.

DataGrid Example

The next piece of code is extracted from a program that uses DataGrid to show an SQL query result (see the figure below). Again, there is nothing special in this code, but it demonstrates that Matisse provides a relational interface.

    private void button1_Click(object sender, System.EventArgs e)
    {
      MtDatabase myConn = new MtDatabase("localhost", "example");

      myConn.Open(); 

      // Create a DataAdapter 

      MtDataAdapter myCommand = 
        new MtDataAdapter(
          "SELECT ProjectName, Budget, ManagedBy.Name Manager 
                           FROM Project;", myConn);
 
      DataSet ds = new DataSet();
      myCommand.Fill(ds, "Projects");
 
      dataGrid1.SetDataBinding(ds, "Projects");
 
      myConn.Close();
    }

Calling SQL Stored Methods

You can call Matisse SQL stored methods using ADO.NET command object of the 'StoredProcedure' type. Alternatively, you may use the CALL syntax to call a static stored method. For example, suppose you define the next stored method, which finds an Employee object from a name:

CREATE STATIC METHOD FindByName(nameToFind VARCHAR)
RETURNS Employee
FOR Employee
BEGIN
  DECLARE emp Employee;
 
  SELECT REF(e) INTO emp FROM Employee e
   WHERE Name = nameToFind;
 
  RETURN emp;
END;

Since the above method returns a single object, you can use the ExecuteScalar method to execute the stored method:

// Create a command object from a connection object

MtCommand mtcmd = myConn.CreateCommand();
 
// Set the CALL statement to call the stored method

mtcmd.CommandText = "CALL Employee::FindByName('Ken Jupiter');";
 
// Execute the stored method, and get the returned object

Employee emp = (Employee) mtcmd.ExecuteScalar();

Matisse also has an interesting feature that allows you to call an SQL stored method (non-static) on an object as a regular .NET method call; no SQL statement is required. I will talk about that feature in my next article.

Summary and Next Article

In this article, I showed how to use ADO.NET with Matisse, especially how to return objects using ADO.NET.

In my next article I will discuss the use of "Object APIs" for .NET programming with Matisse, which provide performance improvements as well as the ability to use the full-text indexing.

Appendix: Using ADO.NET to insert objects

The next piece of code is equivalent to the program shown in the previous article to insert objects of Employee, Manager, and Project using "Object APIs".

    // Create a connection object

    MtDatabase myConn = 
     new MtDatabase("localhost", "example");

    myConn.Open(); 
    IDbTransaction dbtran = myConn.BeginTransaction(); 

    // Create an instance of MtCommand

    IDbCommand myCmd = myConn.CreateCommand(); 

    // Insert an Empoyee #1

    myCmd.CommandText = "INSERT INTO Employee (Name, BirthDate)" +
         "VALUES ('John Venus', DATE '1955-10-01') RETURNING INTO emp1";
    myCmd.ExecuteNonQuery();
 
    // Insert another Employee #2

    myCmd.CommandText = "INSERT INTO Employee (Name, BirthDate)"+ 
        "VALUES ('Amy Mars', DATE '1965-9-25') RETURNING INTO emp2";
    myCmd.ExecuteNonQuery();
 
    // Insert a Manager

    myCmd.CommandText = "INSERT INTO Manager (Name, BirthDate, Title)"+ 
       "VALUES ('Ken Jupiter', DATE '1952-12-15', 'Director')"+
       " RETURNING INTO mgr";
    myCmd.ExecuteNonQuery();
 
    // Insert a Project with the above manager 

    // and two employees as its members

    myCmd.CommandText = 
       "INSERT INTO Project (ProjectName, ManagedBy, Members) "+
        "VALUES ('Whidbey', mgr, SELECTION(emp1, emp2))";
    myCmd.ExecuteNonQuery();

    dbtran.Commit();
    myConn.Close();

<< Back | Next >>