DutyMan Administrators' Guide

CONTENTS

Skip Navigation Links.

Reading the DutyMan Database

You can read member and duty records directly from the DutyMan database using Structured Query Language (SQL) commands.

The records you can read are those associated with your DutyMan roster ONLY.

No update functions are available via SQL.

Data is encrypted during transmission.

Prerequisites

You must install an appropriate MySQL connector available for free download from www.mysql.com.  

For example:

MySQL Connector/ODBC for Windows, Linux, Mac OS X, and Unix platforms.

MySQL Connector/Net for .NET platforms.

We recommend that you install the current GA version.

Code Samples

In the connection string in the following code samples:

ROSTERID is your usual DutyMan Roster Identification

DBPSWD is the database password for user ROSTERID as shown on the DutyMan Administrators >Set Up >Security and Privacy page. To change your database password, for instance because you suspect it has been compromised, contact DutyMan Support.

Skip Navigation Links
VB.Net
C#
Imports MySql.Data.MySqlClient   ' ... but first add a project reference for MySql.Data.dll

Module VBnet_Example

    Public Function ReadDutyMan(ByVal readType As String) As DataTable
        ' readType values:
        '    "members" to read all members without infos
        '    "spmembers" to read all members with infos
        '    "duties" to read all duties

        ' Note: "members" executes more quickly than "spmembers"

        Dim conn As New MySqlConnection
        Dim cmd As New MySqlCommand()
        Dim aDA As MySqlDataAdapter = New MySqlDataAdapter()
        Dim aDS As DataSet = New DataSet()

        Try

            ' set up and open a connection to the DutyMan database
            conn.ConnectionString = _
               "Server=dutyman.biz;Port=3307;Database=dutyman;Uid=ROSTERID;Pwd=DBPSWD;"
            ' or better, define the connections string in your application settings
            conn.Open()

            ' set up the MySQL command to read records
            cmd.Connection = conn
            cmd.CommandType = CommandType.Text

            Select Case readType
                Case "members"
                    cmd.CommandText = "SELECT * FROM members"
                Case "spmembers"
                    cmd.CommandText = "CALL spmembers()"
                Case "duties"
                    cmd.CommandText = "SELECT * FROM duties"
                Case Else
                    cmd.CommandText = "SELECT 'Invalid readType'"
            End Select

            ' set up the data adapter and read records
            aDA.SelectCommand = cmd
            aDA.Fill(aDS)

            ' clean up
            aDA.Dispose()
            cmd.Dispose()
            conn.Close()
            conn.Dispose()

            ' return records
            Return aDS.Tables(0)

            ' handle errors
        Catch ex As MySqlException
            MsgBox(ex.Message)
            Return Nothing

        End Try

    End Function

End Module
               

Programming Notes

For a description of database fields see Duty Upload File Format and Member Upload File Format

The uploadable Member DBID field is returned in both duty and member records. It can be used to synchronise with corresponding records in a membership database.

Profile Updated is a DATETIME returned in member records. It contains the date and time that a member last updated his or her profile and can be used to resolve synchronisation conflicts.
The format of the time stamp is yyyy-MM-dd hh:mm:ss