Reading from databases to arrays

This example workbook demonstrates user code that reads data from a database into an array in a Resolver One workbook.

You will learn

  • How to write user code to read data from a database into an array

Running the example

  1. If you do not already have database software installed, download and install Microsoft's SQL Server Express, which is both free and simple.

  2. Create a database containing a table called Transactions, with columns like this:

    Name

    Type

    Symbol

    VARCHAR

    Price

    DECIMAL

    Currency

    VARCHAR

    Broker

    VARCHAR

    NumberOfShares

    DECIMAL

    BuySell

    VARCHAR

    Username

    VARCHAR

    Add at least eight rows.

  3. Download the example workbook from the Resolver Exchange and open it.

  4. Find the ODBC connection string in the user code and adjust it to point to your database—see the 'Business Layer' section, below.

Background

The Database Worksheet functionality makes it easy to load data from database tables into worksheets.

There is no similar built-in way to load data into ranges of cells. This is because the amount of data returned by a database query can change, and cell ranges returned by one database query can start to overwrite other data.

However, sometimes the number of useful rows is known and fixed, e.g.

  • A query where only the first, say, ten rows matter.
  • A query that returns only ten rows anyway, because you use a TOP keyword in the SELECT statement.
  • Accessing a subset of a database by referring into the returned range.

In this situation, you can use a simple bit of IronPython code, combined with Resolver One array functions, to load the data into an array.

The example worksheet shows how to do this.

About the user code

Getting data from a database

The Pre-Constants user code contains a long, but simple, function that gets data from a database and puts it into an array. (Feel free to re-use this.)

def QueryDB(connectString, query):
    from System import Array
    from System.Data import DataSet
    from System.Data.Odbc import OdbcConnection, OdbcDataAdapter
    connection = OdbcConnection(connectString)
    adaptor = OdbcDataAdapter(query, connection)
    dataSet = DataSet()
    connection.Open()
    adaptor.Fill(dataSet)
    connection.Close()
    if len(dataSet.Tables) > 0 and len(dataSet.Tables[0].Rows) > 0:
        ret = Array.CreateInstance(
            object,
            len(dataSet.Tables[0].Rows[0].ItemArray),
            len(dataSet.Tables[0].Rows))
        for rowIndex, row in enumerate(dataSet.Tables[0].Rows):
            items = (item for item in row.ItemArray)
            for colIndex, val in enumerate(items):
                ret[colIndex, rowIndex] = val
        return ret

Loading the data into the grid

Cell B1 contains a formula that uses the function to connect to the database and extract data:

=QueryDB("Driver={SQL Server};Server=NG\\SQLEXPRESS;Database=ResolverDemo;
ID=demo;PWD=demo;", "select top 20 ID, Symbol, Price, Currency, BuySell,
NumberOfShares, Broker FROM Transactions order by ID desc")

This connects to the database using the OBDC connection string

"Driver={SQL Server};Server=NG\\SQLEXPRESS;Database=ResolverDemo;UID=demo;PWD=demo;"

Customize this

Replace the connection string with one providing access to the database you created above. There's a great step-by-step guide to connection strings here.

The formula returns a .NET Array object that appears in the grid as the string "<2D_array>". There's more to learn about arrays, but for now we only need to extract the data from the array so we can use it.

Unpacking the array into a range of cells

Cell B3 holds the formula "=B1", but has been unpacked into the range B3:G10.

Unpacking allows you to make a formula in one cell that returns an array, and display the array in a range of other cells. (To unpack a cell, select it then press Control-Shift-Enter.)

So now, we have a subset of the data from the database, inserted into the grid at a fixed point, which we know will not grow any larger because the unpacked array is of fixed size.

Referencing into the array for a single value

The formula in B12 demonstrates how to reference a single item from an array without unpacking it. It uses square brackets to look at a particular item, in this case column 2, row 3. (Array indexes are zero-based.)

Comments