Email Password Remember me?  Lost your password?

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

You must Sign In to use this message board.

Per page 102550

FirstPrevNext

Complex hierarchy dtroller 11:58 17 Jun '04   I'm trying to see how to model a definition of "complex types". The database is to hold type definitions, with base types as well as structures (basically, a type is a name and optionnally a list of members). example : 1) boolean 2) string 3) Person FirstName string LastName string Dead boolean I don't know how to get multiple ordered references to the same object in an object (here, Person references String twice) In relational, this is easy through a rel table holding the member name as well as its position in the structure. But in Object model I can't see how to do that. Can anyone help ? Denis Troller Sign In·View Thread·PermaLink Re: Complex hierarchy John Sasak 14:06 17 Jun '04   Hi Denis, You can define a class Person with the following SQL DDL statement, for example, CREATE CLASS Person ( FirstName STRING, LastName STRING, Dead BOOLEAN ); The members in the class are ordered. So, when you execute SELECT * FROM Person, the resulted columns are always, FirstName, LastName, Dead I may have misunderstood your point. Please correct me if so. John Sign In·View Thread·PermaLink Re: Complex hierarchy dtroller 21:50 17 Jun '04   Thanks, but my point is : You have an object "String" from the class "Type", as well as an object "Boolean" and an object "Person". Moreover, I want a relationship stating that a Person has three members : 2 strings and a boolean. In terms of cardinality, that means a type can be a member of another type several time... I am not trying to define the class person, but to define a class "Type" that can be hierarchically composed of other "Type"s, and thus define the "Type" person, but also any other "Type" i'd like. In relational, you would have : a table "Type" containing "String", "Boolean", "Person" a table "TypeMember" containing two refs to the able type, one being "Parent", one being "Member". Do you see what I mean ? Denis Troller Sign In·View Thread·PermaLink Re: Complex hierarchy John Sasak 14:43 18 Jun '04   OK, I see what you mean. You want to store type information as data. You can define it with Matisse in simpler way. CREATE CLASS Type ( Name STRING, Members REFERENCES(Type) ); The relationship Members means that a Type object can have 0-to-many Type objects as its members, and also it means a Type can be a member of another Type several times. The above definition is the simplest way, but we can extend it. For example, if you want to know in which Type(s) a Type is used as a member, we can add a "inverse relationship" to the "Members": CREATE CLASS Type ( Name STRING, Members REFERENCES(Type) INVERSE Type.ParentTypes, ParentTypes REFERENCES(Type) INVERSE Type.Members ); Further more, if you want to put a cardinality constraint on Members, for example a Type can have up to 10 members: CREATE CLASS Type ( Name STRING, Members REFERENCES(Type) CARDINALITY (0, 10) INVERSE Type.ParentTypes, ParentTypes REFERENCES(Type) INVERSE Type.Members ); If you are interested in the meta-schema of Matisse, just install and play with it. It's as simple and powerful as relational. Basically, it consists of three things: MtClass (meta-class), MtAttribute, and MtRelationship. Sign In·View Thread·PermaLink Case sensitive ? Ms Blue 3:10 4 May '04   Hello, When I do queries on my database it appears as though Matisse is case sensitive. IE if I were to look for 'venus' instead of 'Venus' as it is in the database, I will not get any results. How can I ensure that my queries are case insensitive ? Isabelle Sign In·View Thread·PermaLink Re: Case sensitive ? John Sasak 9:56 4 May '04   Hi Isabelle, VARCHAR (or STRING) fields are case sensitive just like any other database products. I think there are two ways to execute case insensitve queries with Matisse: 1) use LOWER() ... WHERE LOWER(LastName) = 'venus' 2) use case insensitive look-up of Entry Point Dictionary John P.S. Did you look at the 6th article of this series? Sign In·View Thread·PermaLink Re: Case sensitive ? John Sasak 10:00 4 May '04   Probably, Entry Point Dictionary gives you better performance. Sign In·View Thread·PermaLink Complicated select statement Ms Blue 15:37 22 Apr '04   Hello, I was wondering if it is possible, in ONE select statement, to get the projects that have John Venus AND Amy Mars in their team... ? In other words, I am only interested in the the projects that have both these employees as team members, and I want to do it in one statement rather than having to iterate through all the projects that have one of them in order to keep those that also have the other. Thanks Sign In·View Thread·PermaLink Re: Complicated select statement John Sasak 14:44 26 Apr '04   Hi Ms Blue, I would write an SQL method on the class Project: CREATE METHOD ContainsBoth(name1 STRING, name2 STRING) RETURNS BOOLEAN FOR Project BEGIN DECLARE mems SELECTION(Employee); DECLARE emp Employee; DECLARE i, cnt INTEGER; DECLARE bool1, bool2 BOOLEAN DEFAULT FALSE; SET mems = SELF.Members; SET cnt = mems.count(); SET i = 1; WHILE i <= cnt DO SET emp = mems.get(i); IF emp.Name = name1 THEN SET bool1 = TRUE; ELSEIF emp.Name = name2 THEN SET bool2 = TRUE; END IF; SET i = i + 1; END WHILE; IF bool1 = TRUE AND bool2 = TRUE THEN RETURN TRUE; ELSE RETURN FALSE; END IF; END; and then, execute a SELECT query using the created method: select * from Project p where p.ContainsBoth('John Venus', 'Amy Mars') = true; You can change the SQL method as you need (e.g., so it takes a list of names, etc.) John Sign In·View Thread·PermaLink 5.00/5 Re: Complicated select statement Ms Blue 5:32 2 May '04   Thank you ! This was very helpful Sign In·View Thread·PermaLink Found your next artical robert_jenkin 11:49 21 Apr '04   Part 4 link to the next installment is not enabled. I did find part 5 when seeing what other articals you have submitted. thanks As a software developer with sql databases pushing the 40 gig mark, wholesale bank application, I am always looking in to database related topics and I have found this product as well as your write up one of the top topics I have read in sometime. I wonder if Microsoft is taking a simular approach to databases in the next release. Sincerely, Bob Jenkin Sign In·View Thread·PermaLink Establishing a reference between a new object and an existing object Ms Blue 5:16 20 Apr '04   Hello, The explanation above for retrieving the project object, and consequently the the members and manager of the project, is very clear. However, I was wondering how one would add a reference to an existing object. Lets say that you create a new project, and the manager of that project is supposed to be an employee that is already in the database, how does one do this ? I am working with an art object database, where one should be able to create a new art object, retrieve an existing artist from the database, and set artObject.creator = artist. When I do this I get the error MATISSE-E-VERSIONMODE, Attempted to start a transaction in version mode. It appears that Matisse does not like it when one retrieves and commits to the database on the same connection...? Everything works fine as long as I do not try to retrieve an object in the same go as creating a new one... Isabelle Sign In·View Thread·PermaLink Re: Establishing a reference between a new object and an existing object John Sasak 9:42 20 Apr '04   Hi Isabelle, You are getting the MATISSE-E-VERSIONMODE error because you are trying to update object(s) within a read-only transaction, which is called "version access" with Matisse. When using ADO.NET with the database, you can access the database without explicitly starting transaction. In this case, the ADO.NET provider starts a read-only transaction. (There is an exceptional case, but let's ignore it for now). If you update objects, you need to start a transaction yourself: MtDatabase db = new MtDatabase("host", "db"); db.Open(); db.StartTransaction(); ... db.Commit(); db.Close(); Also, look at the example above in the Appendix: Using ADO.NET to insert objects. Sign In·View Thread·PermaLink many to many on same key? b_hardman 9:58 18 Apr '04   Excellent series of articles!   Thanks very much! As an experiment, I'm trying to convert a schema that has a many-to-many relationship on the same key.   In relational, this is easy to do with a rel table.   We also have various relation types defined on the rel table. The desired effect is to represent a hierarchy of instances (like a tree structure) with different relationship types.   Its kind of a document mgmt system, so for instance a Folder might have a collection of documents within it (child relation) or the documents may have been scanned (reproduction relation).   The different rel types are less important that representing a hierarchy, but both are issues I am trying to work out.   Any thoughts on how to accomplish this in Matisse?   Also, any thoughts (or experience) on how this might perform with more than 15 million rows in the main table? /brettman Sign In·View Thread·PermaLink Re: many to many on same key? John Sasak 10:36 19 Apr '04   Hi Brettman, The next SQL DDL statements achieve your example of documents and folders. Two classes: Document and Folder which inherits from Document. Folder can contain many Document or Folder objects. A document or a folder can be contained in more than one folder. CREATE CLASS Document ( Name VARCHAR(255) NOT NULL, CreationDate DATE NOT NULL ); CREATE CLASS Folder UNDER Document ( Documents REFERENCES LIST(Document) ); If you want a document or a folder to be contained only in a folder, the DDL would be like this: CREATE CLASS Document ( Name VARCHAR(255) NOT NULL, CreationDate DATE NOT NULL, ContainedIn REFERENCES (Folder) CARDINALITY (0, 1) INVERSE Folder.Documents ); CREATE CLASS Folder UNDER Document ( Documents REFERENCES LIST(Document) INVERSE Document.ContainedIn ); You don't need an intermediate (rel) table. Sign In·View Thread·PermaLink Re: many to many on same key? bhardman 23:50 20 Apr '04   Thanks for the reply. I think this will do the trick. Sign In·View Thread·PermaLink MtObject & invalid cast Ms Blue 16:16 14 Apr '04   I just spent 4 hours not understanding why I was getting an InValidCastException when using the SQL REF function, so I thought I would leave a little note for others like myself... Make sure that you DO NOT specify the namespace in the MTPackageObjectFactory constructor if the namespace is anonymous !! It is not very obvious that this is the problem considering the error message MtDatabase myConn = new MtDatabase("localhost", "example", new MtPackageObjectFactory(",PersistentClasses")); Sign In·View Thread·PermaLink Matisse and images Ms Blue 11:24 11 Apr '04   Hello, Although this question is not directly pertinent to any of the articles, I permit myself to ask as I really really need some help !! I am trying to retrieve an image from Matisse and then display it on my webform... Please note that I am new both to Matisse and to ASP.NET... I would really appreciate your input ! Regards, Isabelle Sign In·View Thread·PermaLink Re: Matisse and images John Sasak 12:04 3 May '04   Hi Isabelle, I've just uploaded a new article that should answer your question above. Currently it is at: http://www.codeproject.com/useritems/introtomatisse_part6.asp (but, it will be moved after the code project editor edits it) Thank you for waiting. John Sign In·View Thread·PermaLink compared to entity classes? Roger J 20:37 29 Mar '04   Hi, what is the major advantages over using this DB instead of using mssql/oracle with an entity model? (eg generated with llblgen pro or any other orm tool) the entity model does pretty much the same, or? //Roger Sign In·View Thread·PermaLink Re: compared to entity classes? Adam Otory 6:34 31 Mar '04   Your question is (not exactly, but) like "Well, I can simulate object-oriented programming with C and I think memory management is not that difficult problem. So, what's the advantages of using .NET instead of C?" Sign In·View Thread·PermaLink Re: compared to entity classes? Roger J 9:30 31 Mar '04   so what can matisse do that cant be done with a enity layer ontop of sqlserver/oracle? or even , what does it do so much better? (ok i saw in another article that matisse was faster when it comes to relations.) //Roger Sign In·View Thread·PermaLink Thanks, John! dshilov 11:01 21 Mar '04   You did a great job with all these articles. I think now I can recommend your articles as must-read thing for new Matisse developers. Hope you will continue your cycle and maybe I can even help you weith that. I work with Matisse for a while now, also I personally familiar with Matisse team and there're great guys. Meanwhile I'll continue to post my thoughts about Matisse in my blog (http://blogs.sibman.com/mtdto) Dmitry Shilov Sign In·View Thread·PermaLink Re: Thanks, John! ENewton 21:03 27 Mar '04   dmitry, i'm currently considering mattise instead of sql server as a backend. are their many pitfalls to this database engine? is it still relational underneath the hood? how good is the TPC? and how reliable is it, IE is their any enterprise level clustering, etc? Sign In·View Thread·PermaLink Re: Thanks, John! John Sasak 15:28 5 Apr '04   It's not relational under the hood. It's logical (and simple) object model, and it is viewed as relational model. Sign In·View Thread·PermaLink 2.00/5

Last Visit: 0:05 23 Nov '09     Last Update: 0:05 23 Nov '09 12 Next »