Interacting with a Database Using Scripts (Advanced)¶

In this tutorial, you will understand how to dynamically interact with a Database while using 2 scripts.

In detail, we are going to interact with a Store using two NetLogic scripts: the first script will insert values into the Database and the second will execute a query and display its results in a label. Note that while this tutorial uses an ODBCStore, the procedure for an embedded database is the same.

ODBC Store

An ODBC store is an interface to any DBMS that supports the Open Database Connectivity API. For this tutorial, it is assumed an ODBC store has already been configured in one of these three ways in the Q Studio:

Configure, in the Property view, the type of DBMS and all the database configuration parameters such as Server, Port, Database and User Info.

Set a preconfigured Data Source Name or DSN with all the database Configurations.

Set a preconfigured Data Source Name or DSN with all the database configurations and specifying in the Property View some specific connection properties.

Note: The store modules of Q Studio supports ANSI SQL-92. For more information, refer to this document.

Adding a Table to a Store

Before running queries, a table should be manually added to the Store object in this manner:

Select the current project’s store, from the Data-Stores folder in the Project View.
On the Tables property, in the Property View, click the + sign to add a new table to the store.
Add columns to the created table by clicking on the + sign of the column property (under the Tables property) in the Property View.

For this tutorial add a table named Table1 with 3 columns of the following types: Int16, Int32 and Float. To select a type, click on BaseDataType near a column, then select the appropriate type by expanding BaseDataType in the popup.

Inserting Values into a Table of an ODBC Store

In this phase, we will add a NetLogic to the ODBC Store that will be run when a button is clicked. The NetLogic will insert random values into a table of the ODBC Store, in our case Table1. First, locate the project’s ODBC Store in the Project View. Next, right-click it then select New >> NetLogic. Double-click on the newly created NetLogic, named ODBCStoreLogic1, to begin editing in Visual Studio.

CAUTION! Names of scripts shouldn’t have spaces in them.

Add the following method to ODBCStoreLogic1.cs:

[ExportMethod]
public void InsertRandomValues (){

    // Get the current project folder.
    var project = Project.Current;

    // Save the names of the columns of the table to an array
    string[] columns = { "Column1", "Column2", "Column3" };

    // Create and populate a matrix with values to insert into the odbc table
    var rawValues = new object[1000, 3];

    Random randomNumber = new Random ();

    for (UInt16 i = 0; i < 1000; ++i)
    {
        // Column1
        rawValues[i, 0] = i;

        // Column2
        rawValues[i, 1] = randomNumber.Next(0, 5000);

        // Column3
        rawValues[i, 2] = randomNumber.NextDouble() * 5000;
    }
    var myStore = LogicObject.Owner as Store;

    // Get Table1 from myStore
    var table1 = myStore.Tables.Get<Table>("Table1");

    // Insert values into table1
    table1.Insert(columns, rawValues);
}

When the project is run and Table1 doesn’t exist on the database, it will be created. However, if Table1 already exists but doesn’t have the correct columns, a best-effort will be made to match the already existing columns with the ones created in the IDE.

Running InsertRandomValues()

To run the NetLogic, add a button named InsertButton to the project. Next, click the + sign next to the MouseClickEvent property ( of InsertButton ) in the EventView. After that, select Project >> DataStores >> ODBCStore >> ODBCStoreLogic1 >> InsertRandomValues.

Displaying the Result of a Query on a Label

For the next script, we will query a database and display the result on a Label (QueryResultLabel). Create a new Netlogic on QueryResultLabel as seen in the previous step.

Add the following method to QueryResultabelLogic1.cs:

[ExportMethod]
public void RunQuery (){
    // get the current project folder
    var project = Project.Current;

    // Get the project's store
     var myStore = Project.Current.Get<Store>("DataStores/DatabaseOdbc1");
     if (myStore == null)
     {
         Log.Error("Run Query", "Missing Store Object");

    object[,] resultSet;
    string[] header;

    // execute query on store of the current project
    myStore.Query("SELECT COUNT(Column1) FROM Table1", out header, out resultSet);

    if (resultSet.Rank != 2)
        return;

    var rowCount = resultSet != null ? resultSet.GetLength(0) : 0;
    var columnCount = header != null ? header.Length : 0;

    if (rowCount > 0 && columnCount > 0)
    {
        // get the first result of the query on myStore
        var columm1 = Convert.ToInt32(resultSet[0, 0]);

        StringBuilder sb = new StringBuilder("Record count: ");
        sb.Append(columm1);

        // get the owner of the NetLogic i.e QueryResultLabel1 then set its text
        var queryResultLabel = (Label)Owner;
        queryResultLabel.Text = sb.ToString();
    }
}

Running RunQuery

To run the NetLogic, add a button named RunQueryButton to the project. Next, click the + sign next to the MouseClickEvent property ( of RunQueryButton ) in the EventView. After that, select Project >> UI >> MainWindow >> QueryResultLabelLogic1 >> RunQuery.

Download the project.