Accessing Data In The Music DB (PMD & Editor Libraries)

[Sox]

The music library for the Editor and on the Pacemaker device is in a SQLite database. For further information on the database structure see
this thread. If you just want to have a nose around the database without writing an application, then you can use SQLite Expert

The information in this post is based on accessing one of these databases from within a VB .Net application, though it shouldn't be too taxing to translate the methods used into C# .Net.

SQLite Data Adapter
The first thing you will need is a Data Adapter for the SQLite database. This allows you to connect to your database from within your application.
I think the one I used was System.data.SQLite which can be downloaded from here. I have successfully used this data adapter from VB .Net within visual Studio 2008 and 2010.

[Sox]

Making A Connection
You need to know the fullpath to the DB file for whichever library you are interested in.
For the Editor Library, you will need to

• Locate the DB on your PC

For the Pacemaker device you will need to

• Find the Pacemaker drive
• Locate the DB on your PC

Once you know this you use it in a Data Source string when making a connection to the database in the format
Data Source=<DB filename>

For example, the string for my Editor library on my Vista PC is
Data Source=C:\Users\Sox\AppData\Roaming\Tonium\Pacemaker\Music.DB

In my own code I find the name of the file and store it in a global variable, and use this to build the data source string which I store in a "DBConnection" variable. This is then used in any database connections from that point on (every time access the database).

Code: Select all

    Public DBConnection As String = ""

    Public Sub New()
        '...... code has also found DB file and loaded into MusicDBFilename variable .........
        MusicDBFilename = MusicDBFolder & "\" & DatabaseFilename
        If File.Exists(MusicDBFilename) Then
            DBConnection = "Data Source=" & MusicDBFilename
        End If
   End Sub

[Sox]

Selecting Some Data
To retrieve any data from the data we need to know both the location of the DB file (the data source string) and the SQL which would select the data you require. At it's simplest this would be every record from a named table. For example to retrieve all records from the Tracks table the SQL would be "select * from Tracks"

The following function will retrieve data according the to passed SQL string from the specified datasource, and populate an instance of a DataTable object

Code: Select all

    'Retrieve a data set dependant on the input SQL
    Public Function GetDataTable(ByVal DBConnStr As String, ByVal sql As String) As DataTable
        Dim dt As DataTable
        Dim mycommand As SQLiteCommand
        Dim reader As SQLiteDataReader
        dt = New DataTable()
        Try
             Dim cnn As SQLiteConnection = New SQLiteConnection(DBConnStr)
            cnn.Open()

            mycommand = New SQLiteCommand(cnn)
            mycommand.CommandText = sql

            reader = mycommand.ExecuteReader()
            dt.Load(reader)

            reader.Close()
            cnn.Close()
        Catch
            'add your error handling here
        End Try
        Return dt
    End Function

And using this in practise to get all records from the Tracks

Code: Select all

    Public dtTracks As DataTable
    dtTracks = GetDataTable(DBConnectionStr, "select * from Tracks")

A More Selective Data Selection Example
Had I wanted to select all tracks with a genre of "RnB" sorted by Artist, I would have used this

Code: Select all

    Public dtTracks As DataTable
    dtTracks = GetDataTable(DBConnectionStr, "select * from Tracks where genre='RnB' order by artist")

[Sox]

A Master/Detail Example
This is bit more complex as here we would like 2 DataTable objects loaded with records that have a master/detail relationship between them. For example the master table could contain all Cases and the detail table could hold all Tracks in those cases. The master/detail relationship can be used on a form (eg. within grids) so that when the user selects a case in the master grid, the details grid shows only the tracks in that case. This is without having to requery the database everytime a new case is selected.

In this example I have 2 DataTables, and a single DataSet which is used to link the two together as a master/detail. I then use this datasource in configuring my grid(s) to show the data.

Code: Select all

    Private dsCases As DataSet
    Public dtCases As DataTable
    Public dtCaseTracks As DataTable
    
    Public Function ViewDBCases(ByVal DBConnectionStr As String, ByVal FilterSQL As String) As Object
        Dim sb As StringBuilder = New StringBuilder()

        'Query what cases are in the DB
        dtCases = GetDataTable(DBConnectionStr, "Select * from Cases")
        dtCases.TableName = "Cases"

        'Query what tracks are in those cases
        sb.Append("Select CT.Case_Id, T.Title, T.Artist, T.Genre, T.Year, T.Play_Time_Secs, T.Bpm ")
        sb.Append("  from CaseTracks CT")
        sb.Append("  left outer join Tracks T on (T.Track_Id=CT.Track_Id)")
        If FilterSQL <> "" Then
            sb.Append(" where " & FilterSQL)
        End If
        dtCaseTracks = GetDataTable(DBConnectionStr, sb.ToString())
        dtCaseTracks.TableName = "CaseTracks"

        'Define the master/detail data relation
        dsCases = New DataSet
        dsCases.Tables.Add(dtCases)
        dsCases.Tables.Add(dtCaseTracks)
        Dim relation As New DataRelation("CasesAndTracks", dtCases.Columns("Case_Id"), dtCaseTracks.Columns("Case_Id"))
        dsCases.Relations.Add(relation)

        Return dtCases
    End Function

    grdDBCases.DataSource = ViewDBCases(DBConnection, "")

[Sox]

A Data Modification Example
Warning - Modify you database at your own risk! I advise you take a backup copy of the database file (Music.db) before any experimenting

I can't be doing with RnB so let's delete all tracks of that genre from my library using the SQL "delete from Tracks where genre='RnB'"

Code: Select all

    'Insert/Update/Delete via SQL (returns no. of records affected)
    Public Function ExecuteNonQuery(ByVal DBConnStr As String, ByVal sql As String) As Integer
        Dim cnn As SQLiteConnection
        Dim mycommand As SQLiteCommand
        Dim rowsUpdated As Integer

        cnn = New SQLiteConnection(DBConnStr)
        cnn.Open()

        mycommand = New SQLiteCommand(cnn)
        mycommand.CommandText = sql

        rowsUpdated = mycommand.ExecuteNonQuery()
        cnn.Close()
        Return rowsUpdated
    End Function

    DBUtils.ExecuteNonQuery(DBConnection,"delete from Tracks where genre='RnB'")