Hi All,

I have 3 SmartObjects; a header, an item and an approval step.  For each workflow process I have one header.  Each header has many items and the header also has many approval steps.

I want to query the SmartBox to return all items where header.workflowstatus = 'InProgress'.  How would I go about writing a smartobject method that does a join on two SmartObjects?  In SQL the query would look like:

 

SELECT     *
FROM        
  Item INNER JOIN
  Header ON Item.Parent = Header.ID
WHERE
  Header.WorkflowStatus <> 'Completed'

 

Many Thanks

Dave

Hi Dave,

As far I know you can run a sql query like above on your smart objects through the ado.net data provider...

Some example code:

SOConnection conn = new SOConnection(“blackpearl”, 5555);
SOCommand cmd = new SOCommand();
cmd.Connection = conn;
cmd.CommandText = “INSERT INTO [Employee] (FirstName) VALUES (@FirstName)”;
cmd.Parameters.Add(new SOParameter(“FirstName”, FirstNameTextBox.Text));
int recordsInserted = cmd.ExecuteNonQuery();

I think there are some limits to the SQL commands you can use - the last I heard the limits where:

• No nested queries
• No aggregate functions
• and no TOP syntax.

These things will be possible in the feuture too, not sure in which release..

Ciao,

 

---

Gerhard
-----------------------------------------------------------------------------

Thanks Gerhard, that's good to know, I thought it was hands off the database.

Dave

Hi Dave,

No let me clarify - these SQL commands are NOT run against the SQL database - this is done through the service object API, so these are ADO.Net "SQL" commands, and never directly on the DB. Have a look at the SourceCode.Data.SmartObjectClient namespace in the developer reference. Hope that makes sense?

Cheers,

 

---

Gerhard
-----------------------------------------------------------------------------

Thanks Gerhard, that makes sense.  I've used the SourceCode.Data.SmartObjectClient namespace.

I thought there would be a SmartObject orientated way of retrieving, rather than ADO.NET.  I've seen the Conditional Class and Join Class in the SDK.  SmartProperty's have AssociationsTo collection so thought there would be a way of retrieving SmartObjects and their Associated SmartObject using a SmartObject method.

Dave