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)
{
MtDatabase myConn = new MtDatabase(
"Server=localhost;Database=example");
myConn.Open();
IDbCommand myCmd = myConn.CreateCommand();
myCmd.CommandText = "SELECT ProjectName, Budget FROM Project;";
IDataReader reader = myCmd.ExecuteReader();
while ( reader.Read() )
{
string pname = reader.GetString(0);
decimal budget = reader.GetDecimal(1);
Console.WriteLine(pname + ", " + budget);
}
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:
MtDatabase myConn =
new MtDatabase("localhost", "example",
new MtPackageObjectFactory("MatisseApp,PersistentClasses"));
myConn.Open();
IDbCommand myCmd = myConn.CreateCommand();
myCmd.CommandText =
"SELECT REF(p) FROM Project p WHERE p.Budget >= 10000;";
MtDataReader reader = (MtDataReader) myCmd.ExecuteReader();
while ( reader.Read() )
{
Project prj = (Project) reader.GetObject(0);
Manager mgr = prj.ManagedBy;
Employee[] members = prj.Members;
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();
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:
MtCommand mtcmd = myConn.CreateCommand();
mtcmd.CommandText = "CALL Employee::FindByName('Ken Jupiter');";
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".
MtDatabase myConn =
new MtDatabase("localhost", "example");
myConn.Open();
IDbTransaction dbtran = myConn.BeginTransaction();
IDbCommand myCmd = myConn.CreateCommand();
myCmd.CommandText = "INSERT INTO Employee (Name, BirthDate)" +
"VALUES ('John Venus', DATE '1955-10-01') RETURNING INTO emp1";
myCmd.ExecuteNonQuery();
myCmd.CommandText = "INSERT INTO Employee (Name, BirthDate)"+
"VALUES ('Amy Mars', DATE '1965-9-25') RETURNING INTO emp2";
myCmd.ExecuteNonQuery();
myCmd.CommandText = "INSERT INTO Manager (Name, BirthDate, Title)"+
"VALUES ('Ken Jupiter', DATE '1952-12-15', 'Director')"+
" RETURNING INTO mgr";
myCmd.ExecuteNonQuery();
myCmd.CommandText =
"INSERT INTO Project (ProjectName, ManagedBy, Members) "+
"VALUES ('Whidbey', mgr, SELECTION(emp1, emp2))";
myCmd.ExecuteNonQuery();
dbtran.Commit();
myConn.Close();
<< Back | Next >>
|
|
 |
 | 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
|
|
|
|
|
|
|
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
|
|
|
|
|
|
|
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
|
|
|
|
|
|
|
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.
|
|
|
|
|
| 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
|
|
|
|
|
|
|
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?
|
|
|
|
|
|
|
Probably, Entry Point Dictionary gives you better performance.
|
|
|
|
|
| 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 
|
|
|
|
|
|
|
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
|
|
|
|
|
|
|
Thank you ! This was very helpful
|
|
|
|
|
| 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
|
|
|
|
|
| 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
|
|
|
|
|
|
|
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.
|
|
|
|
|
| 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
|
|
|
|
|
|
|
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.
|
|
|
|
|
|
|
Thanks for the reply. I think this will do the trick.
|
|
|
|
|
| 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"));
|
|
|
|
|
| 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
|
|
|
|
|
|
|
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
|
|
|
|
|
| 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
|
|
|
|
|
|
|
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?"
|
|
|
|
|
|
|
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
|
|
|
|
|
| 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
|
|
|
|
|
|
|
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?
|
|
|
|
|
|
|
It's not relational under the hood. It's logical (and simple) object model, and it is viewed as relational model.
|
|
|
|
|
|
|
Last Updated 27 Apr 2004 |
Advertise |
Privacy |
Terms of Use |
Copyright ©
CodeProject, 1999-2010
