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.
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