DataBase

	DataBase	This node provides bi-directional communication with ODBC, MSSQL, OleDB, Oracle or MySQL databases within a scene.
	Query	This node provides a database query command.
	NonQuery	This node provides a database non-query command.
	Row	This node provides a database row access.
	ColConcat	This node provides a database column concatenation functionality.
	Scalar	This node provides a scaler database command.

The Database functionality inside Ventuz is based on the ​ADO.NET (System.Data .NET framework and supports ​ODBC, ​MS-SQL, ​OleDB, ​Oracle and ​MySQL. Setting up and querying a database is a complex topic and doing a comprehensive introduction is beyond the scope of this documentation. It is therefore assumed that the reader has sufficient knowledge of databases that this documentation can focus on the Ventuz specific aspects.

Database

The Database node is the central element of interacting with a database. In addition to the database Type, a ConnectionString has to be specified. A connection string specifies all information that is required to access a database, including the URI of the database, required passwords and user accounts, and much more. To construct an appropriate connection string, consult the MSDN documentation:

• ​MSDN: ODBC connection string
• ​MSDN: OleDB connection string
• ​MSDN: Oracle connection string
• ​MSDN: SQL connection string

As an example, to connect to an Access database file located at C:\Temp\database.db the connection string might look like this:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp\database.db;User Id=admin;Password=;

Query

To query a database for information, add a Query node to the scene and bind its Database input property to a Database node. To edit the query string itself, press the Edit SQL button at the bottom of the Property Editor.

Besides the actual query statement itself, a number of input and output properties can be created. Input properties can be used to fill parameters in the query statements, for example an input property with the name @street would be filled into the WHERE-clause.

SELECT description from Club WHERE address=@street

The Output Properties can be used for getting output values. If the name of an output property exactly matches the name of one of the column names specified in the query, its value will be filled with the value of that column in the row specified with Row.

The MaxRows property defines how many row are stored into the result DataSet. If possible try to avoid SQL queries that return thousand of rows which are limited by MaxRows. This could impact the query performance drastically. The use of PAGE or ROWCOUNT techniques may help here - but are not supported by all SQL dialects.

Row

If a Query returns more than one row, one needs a way to iterate over the different rows. This is done with the Row Node which can be bound to a Query Node. Same as with the Query, the Custom Model can be used to add output properties that have the same name as the table columns. By changing the Row input property, a specific row of the result data set can be selected.

Column Concatenation

As an alternative to the Row node, a Column Concatenation can be used to produce one joined string composed of the entries of the individual rows. The node accepts both a Column name and a Separator string which is inserted between two consecutive entries. The result string could be used to create a dynamic SQL statement again (see Convert to Text node).

Scalar Operation

The Scalar Node provides the ability to execute aggregate functions that deliver a numeric value such as COUNT.

Non Query

Non-Query is the .NET Framework term for database operations that do not retrieve or update table entries. It can be used to query information about or change the structure of tables among other things.