Click here to Skip to main content
15,860,972 members
Articles / Programming Languages / C#

An Introduction to a Post-relational Database for .NET: Matisse - Part 4

Rate me:
Please Sign up or sign in to vote.
4.86/5 (29 votes)
26 Apr 20044 min read 164K   18   27
ADO.NET programming with a post-relational database

Introduction

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

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

UML Diagram

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

C#
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:

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

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

Screenshot of DataGrid Example

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:

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

C#
// 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".

C#
// 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 >>

History

  • 27th April, 2004: Initial version

License

This article has no explicit license attached to it, but may contain usage terms in the article text or the download files themselves. If in doubt, please contact the author via the discussion board below. A list of licenses authors might use can be found here.


Written By
Web Developer
United States United States
John is a software consultant and currently working for a large .NET project. He has an extensive experience in object-oriented technologies for more than 15 years ranging from Smalltalk, C++, Java, .NET to databases.

Comments and Discussions

 
GeneralComplex hierarchy Pin
dtroller17-Jun-04 10:58
dtroller17-Jun-04 10:58 
GeneralRe: Complex hierarchy Pin
John Sasak17-Jun-04 13:06
John Sasak17-Jun-04 13:06 
GeneralRe: Complex hierarchy Pin
dtroller17-Jun-04 20:50
dtroller17-Jun-04 20:50 
GeneralRe: Complex hierarchy Pin
John Sasak18-Jun-04 13:43
John Sasak18-Jun-04 13:43 
QuestionCase sensitive ? Pin
Ms Blue4-May-04 2:10
Ms Blue4-May-04 2:10 
AnswerRe: Case sensitive ? Pin
John Sasak4-May-04 8:56
John Sasak4-May-04 8:56 
GeneralRe: Case sensitive ? Pin
John Sasak4-May-04 9:00
John Sasak4-May-04 9:00 
GeneralComplicated select statement Pin
Ms Blue22-Apr-04 14:37
Ms Blue22-Apr-04 14:37 
GeneralRe: Complicated select statement Pin
John Sasak26-Apr-04 13:44
John Sasak26-Apr-04 13:44 
GeneralRe: Complicated select statement Pin
Ms Blue2-May-04 4:32
Ms Blue2-May-04 4:32 
GeneralFound your next artical Pin
robert_jenkin21-Apr-04 10:49
robert_jenkin21-Apr-04 10:49 
GeneralEstablishing a reference between a new object and an existing object Pin
Ms Blue20-Apr-04 4:16
Ms Blue20-Apr-04 4:16 
GeneralRe: Establishing a reference between a new object and an existing object Pin
John Sasak20-Apr-04 8:42
John Sasak20-Apr-04 8:42 
Questionmany to many on same key? Pin
bhardman18-Apr-04 8:58
bhardman18-Apr-04 8:58 
AnswerRe: many to many on same key? Pin
John Sasak19-Apr-04 9:36
John Sasak19-Apr-04 9:36 
GeneralRe: many to many on same key? Pin
bhardman20-Apr-04 22:50
bhardman20-Apr-04 22:50 
GeneralMtObject & invalid cast Pin
Ms Blue14-Apr-04 15:16
Ms Blue14-Apr-04 15:16 
GeneralMatisse and images Pin
Ms Blue11-Apr-04 10:24
Ms Blue11-Apr-04 10:24 
GeneralRe: Matisse and images Pin
John Sasak3-May-04 11:04
John Sasak3-May-04 11:04 
Questioncompared to entity classes? Pin
Roger Alsing29-Mar-04 19:37
Roger Alsing29-Mar-04 19:37 
AnswerRe: compared to entity classes? Pin
Adam Otory31-Mar-04 5:34
Adam Otory31-Mar-04 5:34 
GeneralRe: compared to entity classes? Pin
Roger Alsing31-Mar-04 8:30
Roger Alsing31-Mar-04 8:30 
GeneralThanks, John! Pin
dshilov21-Mar-04 10:01
dshilov21-Mar-04 10:01 
GeneralRe: Thanks, John! Pin
ENewton27-Mar-04 20:03
ENewton27-Mar-04 20:03 
GeneralRe: Thanks, John! Pin
John Sasak5-Apr-04 14:28
John Sasak5-Apr-04 14:28 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.