Writing to a database

This example workbook demonstrates user code that copies data from Resolver One workbooks into a database.

You will learn:

  • How to write user code that copies data to a database.

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

  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.

About the workbook

The workbook contains a single sheet. The user enters the parameters of a share trade then clicks a button. This writes the details to a database.

The interesting details are all to be found in the user code.

About the user code

The code has three layers:

  • General Database Access Layer—connects the workbook to the database
  • Business Layer—translates concepts that make sense in the application domain (such as stock symbols and prices) into database commands
  • UI Layer—interfaces between the spreadsheet and the Business Layer.

Database Access Layer

The Database Access Layer is a simple bit of generic IronPython in the Pre-Constants user code:

# A simple general-purpose function to write to a database
def ExecuteDBNonQuery(connectString, sql):
    from System.Data.Odbc import OdbcConnection, OdbcCommand
    connection = OdbcConnection(connectString)
    command = OdbcCommand(sql, connection)
    connection.Open()
    command.ExecuteNonQuery()
    connection.Close()

This uses the .NET API to execute a non-query SQL statement against a database, the latter being specified by an ODBC connection string.

Business Layer

The Business Layer is a little more complex.

Part of it resides in the Pre-constants user code:

# A function to add a row with specific values to a transaction database
def addTransaction(
    symbol, price, currency, broker, numberOfShares, buySell, username):

    try:
        ExecuteDBNonQuery(
            "Driver={SQL Server};Server=NG\SQLEXPRESS;"
            "Database=ResolverDemo;UID=demo;PWD=demo;",
            "INSERT INTO Transactions "
            "(Symbol, Price, Currency, Broker, NumberOfShares, BuySell, Username) "
            "VALUES ('%s', %f, '%s', '%s', '%d', '%s', '%s')" %
            (symbol, price, currency, broker, numberOfShares, buySell, username)
        )
        print "Transaction added"
    except Exception, e:
        print e

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 final piece of code, which connects the UI to the Business Layer, is in the Post-Formulae user code:

ws = workbook["Sheet1"]
ws.A9 = Button(Text='Add transaction')
ws.A9.Click += lambda : addTransaction(
    ws.B1, ws.B2, ws.B3, ws.B4, ws.B5, ws.B6, ws.B7
)

This adds a new button to the grid, then wires it up to call the addTransaction function defined in the Business Layer. The lambda construct creates a new function that calls the addTransaction function with the specific parameters.

In action

Once you've created a database and set up the connection string, try entering different values in column B then hitting the Add transaction button in cell A9. The values will be written to the database.

Comments