Managing a Music Collection Using Visual Basic Express and SQL Server Express

 

Brian A. Randell
MCW Technologies, LLC

February 2005
Updated June 2005

Applies to:
   Microsoft SQL Server Express
   Microsoft Visual Basic 2005 Express Edition

Summary: Learn how to get started with database-driven development using Visual Basic 2005 Express Edition and SQL Server Express. (38 pages)

**Note   **You must have DirectX installed in order for the sample application to run.

Contents

Introduction
Designing the Catalog
Building the Database
Creating the User Interface
Working with the Database
Wrapping It Up
Further Reading

Introduction

A problem that afflicts me (and I've noticed many of my brethren) is the need to create a program to solve a task when there are already many perfectly suitable programs on the market for free or for very little cash. The standard justification in my mind is that there isn't a program that does things the exact way I want them to. Now realistically, I tend to pick on programs that are within reach. After all, even if Word didn't suit my needs, I have no intention of writing my own word processing program from scratch. (Although at times, I keep thinking I could build a better version of Outlook ... yet I digress).

Such is the case of creating a program to manage my digital music collection. While there are many music players that include software to manage my collection, I wanted more. The problem, of course, is that no one's going to pay me to write such a thing. Or would they? While playing with the PDC builds of Yukon and Whidbey, I figured the best way to get to know the product was to build something. I built a very basic MP3 player using Visual Basic .NET that stored its metadata in a Yukon database. As always with homegrown products, the app didn't go very far. Fast-forward and it's time to do it again—this time with Visual Basic Express and SQL Server Express. In fact the goal is to evolve the application over the beta cycle so that by the time these products are released, I'll have the player I've always wanted. And since you'll have the code too, you can make the player you've always wanted.

In case you're not aware, SQL Server Express is name for the new version of the product formally known as the Microsoft SQL Server 2000 Desktop Engine; MSDE 2000 for short. SQL Server Express is based upon the same core engine as SQL Server 2005. SQL Server Express will be the free, redistributable version of SQL Server 2005 that will be ideal for client applications that require an embedded database, new developers learning how to build data-driven applications, and small Web sites. SQL Express is included with all of the new Express developer tools recently announced (including Visual Basic Express). When you install Visual Basic Express, you will be given an opportunity to install SQL Server Express. It will be installed under Program Files and create a named instanced called SQLEXPRESS.

Designing the Catalog

The first step in building the application is designing the database schema. For many people, tracking data starts with a simple list, often created with pen and paper. When they graduate to the digital world, many people start with Excel. After all, Excel is fast, support larges lists, supports filtering and sorting, and is easy to use. One problem with Excel is repeating data. This seems like a good exercise in normalization.

Take the following Excel spreadsheet:

Figure 1. Tracking a recording in Microsoft Excel

Looking at the data in the spreadsheet, you'll see we're tracking a bare minimum of data. Things like track duration, music category, etc. are unimportant (at least in this first release). Normalizing is the process of taking large tables and breaking them down into smaller ones in order to remove (or at least reduce) unnecessary and duplicate information. SQL Server Express is a relational database management system that supports databases that follow the relational model. The late E.F. Codd, in his paper "A Relational Model of Data for Large Shared Data Banks," first defined the relational model in 1970. The relational model is based upon relational algebra (Relations actually refer to what are more commonly referred to as tables). A part of normalization, a table design is tested against normal forms. Normal forms that are in use are:

• First normal form
• Second normal form
• Third normal form
• Fourth normal form
• Fifth normal form
• Boyce-Codd normal form
• Domain/Key normal form

This list was taken from Database Design for Mere Mortals, Second Edition, authored by Michael J. Hernandez. Mike's book covers normalization and database design extensively in an easy-to-read style and is highly recommended. The database for the music collection will only require a few tests to for this first iteration of the application.

First Normal Form

First normal form states that all column values are atomic. Looking at the list of data in the Excel sheet, the only real column that's not atomic is the File Name column. This column could be broken down further into drive, path, file name, and file extension. However, for this application, this level of normalization doesn't really make sense. The application will be using all the parts as a single unit.

Second Normal Form

Second normal form states that the database must be in first normal form. Each table should contain data about a single entity. The current table can easily be broken down into three tables: artists, recordings, and tracks. In breaking the data into local groupings, we need a way to uniquely identify an entity, such as a recording, in the table. This is known as primary key. If possible, we'd prefer a natural primary key. A natural key is one that is based upon data within the table. This data needs to be unique among entities and, if possible, does not change (or at least frequently). While the artist table could use the artist's name as its primary key, this would create a large key value when used in relationships. In addition, while not common, it's possible to have a duplicate artist name. With recordings and tracks, the possibility of duplicates grows. Therefore, it will be better to use a surrogate key. In the case of all three tables it will be an auto-generated (identity) value in the form of a 32-bit integer. This will allow each table to contain about two billion records.

With the data in multiple tables, we need a way to relate the data. We'll do this using foreign keys. One way to figure out the relationships is to define them in sentences. For example:

• An artist has recordings.
• A recording has tracks. (This implies that an artist has tracks that are grouped by recording.)

Therefore, the recordings table will need to have a column that represents the artist who created the recording. In turn, the tracks table will need to have a column that references the recording on which the track appears. Using joins, an inference we can make an inference about which artist recorded which track. At this point the table structure for the database would look like this:

Figure 2. Logical database design in second normal form

Third Normal Form and Beyond

Third normal form requires that the database be in second normal form. All non-key fields must depend upon the primary key. A common problem at this point would be calculated columns. The current schema defined thus far doesn't suffer from these problems. Take a look at the Further Reading section at the end of this article for materials related to database design, normalization and the relational theory. Mike Hernandez's book, for example, provides a few sample schemas.

Building the Database

With the schema defined, the database can now be defined in SQL Server Express. The Technical Preview does not include any graphical tools (although they will be available later in the pre-release cycle). However, Visual Basic Express (as well as the other Express products) includes a set of graphical tools that can be used to define the database and its contained objects such as tables, views, and stored procedures.

With Visual Basic Express started, go to the View menu and select the Server Explorer option. A tool window labeled Database Explore opens (see Figure 3).

Figure 3. Database Explorer window in Visual Basic Express

Right-click the available Database Connections node to display a menu providing options to add a connection or create a new database (see Figure 4).

Figure 4. Create New SQL Server Database menu

Select the Create New SQL Server Database option to open a simple dialog (see Figure 5) that allows you specify the SQL Server Express instance, login credentials and the new database name. The database you create via this dialog will be stored in the default SQL Server Express data directory (typically C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data). If this is not acceptable, there are a couple of alternatives. SQL Server Express ships with the same OSQL.exe command-line tool as in previous versions of MSDE. In addition, the new SQLCMD.exe command-line tool is included. Either tool will let you issue a CREATE DATABASE statement that will give you full control over the database creation process. In addition, as mentioned before, a managed graphical environment for managing SQL Express instances will be available later in the beta cycle.

Figure 5. Create New SQL Server Database dialog

However, there is another option in Visual Basic Express. Once a project has been created, there's an option to create a new database by selecting the Add New Item command from Project menu and selecting the Blank Database item in the list (see Figure 6).

Figure 6. Add New Item-Blank Database

Choosing this option and entering a name creates a new database file and log in the same directory as the project's source files (I encourage you to save your project first. Unlike previous editions of Visual Studio .NET, Visual Basic Express supports Zero Impact projects that behave similarly to those in Visual Basic 6.0 and earlier. Basically you can create a project, write some code, run it, play with it, and then just discard it without littering your hard drive with the detritus of projects that time forgot). The database connection uses a new connection string setting, AttachDBFilename, to link to the database file. This option will force the database to be attached to the server when a connection is opened (if the database is not already attached). In addition, if AUTO_CLOSE was enabled at database creation (which is the default for SQL Server Express databases), when an application shuts down, the database and log files can be managed as regular operating system files suitable for XCOPY deployment.

Defining Tables

With the database defined, you can create tables graphically or via T-SQL commands. A new feature of the Visual Database Tools is the ability to open a query window and execute a variety of commands, including SELECT, INSERT, and CREATE TABLE commands (see Figure 7).

Figure 7. New Query Window with context menu displayed

Creating tables with the New Table designer is similar in form and function to previous editions of the Visual Data Tools (as well as SQL Enterprise Manager and Access). The designer operates as expected. Defining the tables for our application's small schema is quick and easy. To define the primary key, indexes, and relationships, there is a Table Designer menu item on the main menu bar and a corresponding toolbar.

The following tables show the table definitions. None of the columns allow null values. The sizes for the ArtistName, RecordingTitle, and TrackTitle were determined by the sizes supported by the ID3v1 specification (available at http://www.id3.org/id3v1.html).

Table 1. Artists Table

Column Name	Data Type	Notes
ArtistID	int	Primary key, identity
ArtistName	varchar(30)

Table 2. Recordings Table

Column Name	Data Type	Notes
RecordingID	int	Primary key, identity
RecordingTitle	varchar(30)
ArtistId	int	Foreign key

Table 3. Tracks Table

Column Name	Data Type	Notes
TrackID	int	Primary key, identity
TrackTitle	varchar(30)
RecordingID	int	Foreign key
TrackSequence	tinyint
TrackFileName	nvarchar(260)

Defining Relationships and Additional Indexes

With the tables defined, the next step is to define the relationships between the tables, adding additional indexes as necessary. To define a relationship using the Visual Data Tools, you need to open the table in design view via the Open Table Definition command (available when right-clicking a table in the Database Explorer window). Once you've opened the table, the Relationships command is available off of the Table Designer menu (or its corresponding toolbar). Selecting the command will open the Foreign Key Relationships dialog. A table can have any number of relationships and constraints defined. Click the Add button to create a new constraint (see Figure 8). In order to define a relationship, you must set the Tables and Column Specification property. To set it, click the editor button in the property (see Figure 8) to open the Tables and Columns dialog. This is where the actual columns are matched up for the relationship (see Figure 9).

Figure 8. Foreign Key Relationships dialog

Figure 9. Table and Columns dialog

Closing the dialogs and then saving the table will cause the "save changes to your database" warning message to appear (see Figure 10). The changes can be applied, cancelled, or saved out to a script file to be executed later. Once you've defined all the relationships (the sample database only has two at this time), you can add additional indexes. So far, each table has one index defined for the primary key column. Additional indexes can increase performance when searching for data and when performing joins between tables. However, indexes can also slow down insert operations. Since the database will be predominately used for read operations, adding indexes for the foreign key columns and for the text columns seems appropriate.

Figure 10. "Save changes to your database" warning message

To add or modify a table's indexes, you must open the table in design mode. The Indexes/Keys dialog, available via the Table Designer menu provides the UI to add, modify, or remove indexes and keys on a table-by-table basis. Figure 11 displays the dialog with a new index added for the ArtistsName column in the Artists table. Closing the dialog and saving the table applies the changes.

Figure 11. Indexes/Keys dialog

Working with Data

With the schema defined, the next step is to get some sample data into the database. This is a good way to check to see if all the relationships are defined correctly. The Visual Data Tools make it easy. Simply right-clicking a table and selecting the Show Table Data command opens the data grid window for entering data. In fact, you can copy all twelve rows of track data from Excel and paste them directly into the tracks data with correct creation of the identity values. How cool is that? Figure 12 shows all three tables with the sample data loaded. Setting up the windows, one on top of the other, is simple. Just open all three tables and then drag the tab for one window down the screen until a window frame appears; repeat one time and arrange windows to taste.

Figure 12. All Three Tables with Sample Data Loaded

If using the interactive grid doesn't suit you, the New Query command (available when right-clicking the database in the Database Explore window) will open the Query Editor, which enables you to execute of T-SQL commands to insert data.

Creating the User Interface

With the database created, the next step is creating the user interface. Using Visual Basic Express to build a Windows Forms GUI is pretty addictive. Snap-lines that help you position and align controls on the design surface are just pure happiness, and the new drag-and-drop data binding also provides a warm and fuzzy feeling (which will be covered a bit later). User interfaces, especially for fun projects, can be a very personal issue. I really didn't want to go with "battleship" grey so I whipped up a small bit of code to create forms and panel controls with rounded corners. For the first iteration, you'll create all the necessary code in a single project. In the future, it would be nice to pull out items and create a reusable library. Figure 13 shows the main player window in all its blue, rounded glory. Additional screens will be created a bit later.

Figure 13. Music Express Beta 1

Playing MP3 Files

While the Visual Basic Express toolbox is rich with wonderful components and controls, old and new, there doesn't appear to be one that plays MP3 files. The new SoundPlayer component only supports WAV files. If possible, I wanted to avoid P/Invoke. A little research and I found what I wanted: the managed DirectX libraries provide an Audio class that lives in the Microsoft.DirectX.AudioVideoPlayback.dll assembly. This class supports MP3, WMA, and other audio file types. The Audio class has two overloaded constructors. One takes a file name to an audio file; the other adds a Boolean value to specify that the file should begin playing once opened. In addition, there are two shared members for opening a file. The FromFile() method duplicates the behavior of the instance constructor that doesn't auto-play the file. The FromURL() method supports running the file from an HTTP stream.

For the application, create a module level variable using the WithEvents keyword. The Audio class exposes Stopping and Pausing events (as well as a few others) that the application will use to keep the UI in sync. The Play File button uses the Open File dialog component to enable the user to browse for an MP3 file. Once the file has been selected, the MP3 metadata is parsed, and the song begins to play. Below are the core routines to define the audio instance, pick a file, and then play the file:

' In the declarations section of frmMain
Private WithEvents mp3 As Audio = Nothing
Private Const DEF_NOWPLAYING As String = "Now Playing"
Private Sub btnPlayFile_Click( _
  ByVal sender As System.Object, _
  ByVal e As System.EventArgs) _
  Handles btnPlayFile.Click
    Select Case Me.ofdPlay.ShowDialog(Me)
        Case Windows.Forms.DialogResult.OK
            Dim strFileName As String = Me.ofdPlay.FileName
            Me.PlayMp3(strFileName)
    End Select
End Sub
Private Sub PlayMp3(ByVal FileName As String)
    Debug.Assert(FileName IsNot Nothing)
    Debug.Assert(FileName <> String.Empty)
    Me.StopMp3()
    Dim mp3Info As New ID3v1Reader(FileName)
    mp3Info.Parse()
    Me.rpnlNowPlaying.HeaderText = _
        "Now Playing: " & mp3Info.Artist
    Me.rpnlNowPlaying.BodyText = _
      mp3Info.TrackTitle
    mp3 = New Audio(FileName, True)
    Me.rpnlStatus.BodyText = "Playing"
End Sub
Private Sub StopMp3()
    If Not mp3 Is Nothing Then
        If mp3.Playing OrElse mp3.Paused Then
            mp3.Stop()
            mp3.Dispose()
            mp3 = Nothing ' Only necessary in Debug Builds
            Me.rpnlStatus.BodyText = "Ready"
            Me.rpnlNowPlaying.HeaderText = DEF_NOWPLAYING
            Me.rpnlNowPlaying.BodyText = String.Empty
        End If
    End If
End Sub

Accessing MP3 File Metadata

MP3 files can contain an enormous amount of metadata just waiting to be harvested. Most files store the metadata in a format known as ID3 tagging. If you visit http://www.id3.org/history.html you can get a brief history of tagging. To get things going quickly, I focused on the ID3 v.1.1 implementation (which, although limited, is good enough for a project such as this). Create a class called ID3v1Reader to load an MP3 file, parse the metadata, and make it available via a set of properties:

Option Strict On
Imports System.IO
Imports System.Text
Friend Class ID3v1Reader
    Private m_Artist As String
    Private m_Comment As String
    Private m_Genre As Byte = 255
    Private m_FileName As String
    Private m_Recording As String
    Private m_TrackSequence As Byte = 0
    Private m_TrackTitle As String
    Private m_Year As String
    Public Property Artist() As String
        Get
            Return Me.m_Artist
        End Get
        Set(ByVal Value As String)
            Me.m_Artist = Value
        End Set
    End Property
    Public Property Comment() As String
        Get
            Return Me.m_Comment
        End Get
        Set(ByVal Value As String)
            Me.m_Comment = Value
        End Set
    End Property
    Public Property Genre() As Byte
        Get
            Return Me.m_Genre
        End Get
        Set(ByVal Value As Byte)
            Me.m_Genre = Value
        End Set
    End Property
    Public Property FileName() As String
        Get
            Return Me.m_FileName
        End Get
        Set(ByVal Value As String)
            Me.m_FileName = Value
            Me.Reset()
        End Set
    End Property
    Public Property Recording() As String
        Get
            Return Me.m_Recording
        End Get
        Set(ByVal Value As String)
            Me.m_Recording = Value
        End Set
    End Property
    Public Property TrackSequence() As Byte
        Get
            Return Me.m_TrackSequence
        End Get
        Set(ByVal Value As Byte)
            Me.m_TrackSequence = Value
        End Set
    End Property
    Public Property TrackTitle() As String
        Get
            Return Me.m_TrackTitle
        End Get
        Set(ByVal Value As String)
            Me.m_TrackTitle = Value
        End Set
    End Property
    Public Property Year() As String
        Get
            Return Me.m_Year
        End Get
        Set(ByVal Value As String)
            Me.m_Year = Value
        End Set
    End Property
    Public Sub New()
        ' This is needed since there is a 
        ' paramaterized constructor.
        ' This is so this class can be used
        ' for multiple files.
    End Sub
    Public Sub New(ByVal FileName As String)
        Me.FileName = FileName
    End Sub
    Public Sub Parse()
        Debug.Assert(Me.FileName IsNot Nothing)
        Debug.Assert(File.Exists(Me.FileName))
        Dim fs As FileStream = Nothing
        Dim ae As ASCIIEncoding
        Dim buffer(127) As Byte
        Dim tag As String
        Dim hdr As String
        Try
            fs = New FileStream(Me.FileName, _
              FileMode.Open, FileAccess.Read)
            ae = New ASCIIEncoding
            fs.Seek(-128, SeekOrigin.End)
            fs.Read(buffer, 0, 128)
            tag = ae.GetString(buffer)
            hdr = tag.Substring(0, 3)
            If hdr = "TAG" Then
                Me.TrackTitle = SubStringNull(tag, 3, 30)
                Me.Artist = SubStringNull(tag, 33, 30)
                Me.Recording = SubStringNull(tag, 63, 30)
                Me.Year = SubStringNull(tag, 93, 4)
                Me.Comment = SubStringNull(tag, 97, 30)
                If buffer(125) = 0 Then
                    Me.TrackSequence = buffer(126)
                    Me.Genre = buffer(127)
                End If
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message, ex.Source, _
             MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If Not fs Is Nothing Then
                fs.Flush()
                fs.Close()
            End If
        End Try
    End Sub
    Private Sub Reset()
        Me.Artist = Nothing
        Me.Comment = Nothing
        Me.Genre = 255
        Me.Recording = Nothing
        Me.TrackSequence = 0
        Me.TrackTitle = Nothing
        Me.Year = Nothing
    End Sub
    Private Function SubStringNull( _
      ByVal s As String, ByVal start As Integer, _
      ByVal length As Integer) As String
        Dim subS As String = s.Substring(start, length)
        Return subS.TrimEnd(Convert.ToChar(0))
    End Function
End Class

The class is straightforward enough. If it's instantiated using the parameterized constructor, the file name is stored away. Otherwise, an instance can be instantiated and then reused over and over by changing the file name, which will call a private method, Reset(), to reinitialize all of the properties. The Parse() method does the main work. The file is opened read-only and a buffer of 128 bytes is retrieved using ASCII-encoding from the end of the file. This buffer is parsed and the public properties are set.

Working with the Database

With database defined and the core player functionally created, the next steps relate to getting data into and out of the database itself. The DataSet class in version 2.0 of the .NET Framework has bunch of new enhancements. In addition, the design-time experience in Visual Basic Express is very rich.

Loading the Database

To add information to the database, create a set of stored procedures. A total of four are needed to load data: one for each base table—Artists, Recordings, and Tracks—and a fourth to call all three in a transaction to add a new MP3 to the database. Create the stored procedures using the Visual Data Tools inside Visual Basic Express. A nice feature of the integration of the tools and the IDE is that the database schema can be explored while a stored procedure is edited in order to get names of tables, columns, and other information. An even better feature is the ability to right-click in the stored procedure designer, select Insert SQL and have it open up the interactive Query Builder (see Figure 14).

Figure 14. Query Builder

The following code shows the T-SQL for the AddMP3 stored procedure:

ALTER PROCEDURE AddMP3 
   @ArtistName varchar(30), 
   @RecordingTitle varchar(30),
   @TrackTitle varchar(50),
   @TrackSequence tinyint,
   @TrackFileName nvarchar(260)
AS
DECLARE @ArtistID int
DECLARE @RecordingID int
DECLARE @TrackID int
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
EXEC @ArtistID = AddArtist @ArtistName
EXEC @RecordingID = AddRecording @ArtistID, @RecordingTitle
EXEC @TrackID = AddTrack @TrackTitle, @RecordingID, @TrackSequence, @TrackFileName
COMMIT TRANSACTION
RETURN

Each of the child stored procedures checks to see if the Artist, Recording, or Track already exists, respectively. If the item exists, then the existing ID is returned. Using the stored procedure from the application is wrapped up in a class called LoadMp3s, listed below:

Option Strict On
Imports System.Data
Imports System.Data.SqlClient
Friend Class LoadMp3s
    Friend Event FileLoaded As FileFoundDelegate
    Private WithEvents F As FindFiles = Nothing
    Private mp3Info As ID3v1Reader = Nothing
    Private mintFilesFound As Integer = 0
    Private mstrConnectionString As String = Nothing
    Private mcon As SqlConnection = Nothing
    Private mcmd As SqlCommand = Nothing
    Public Sub New(ByVal ConnectionString As String)
        Me.mstrConnectionString = ConnectionString
        mp3Info = New ID3v1Reader
    End Sub
    Public Function Load(ByVal Path As String) As Integer
        Me.mintFilesFound = 0
        mcon = New SqlConnection(Me.mstrConnectionString)
        F = New FindFiles
        F.SearchExt = "*.mp3"
        F.Start(Path)
        Return Me.mintFilesFound
    End Function
    Private Sub F_FileFound( _
      ByVal sender As Object, ByVal e As FileFoundEventArgs) _
      Handles F.FileFound
        Me.mintFilesFound += 1
        If mcon.State = ConnectionState.Closed Then
            mcon.Open()
        End If
        If mcmd Is Nothing Then
            mcmd = Me.GetInsertCommand()
        End If
        mp3Info.FileName = e.FoundFile.FullName
        mp3Info.Parse()
        mcmd.Parameters("@ArtistName").Value = mp3Info.Artist
        mcmd.Parameters("@RecordingTitle").Value = mp3Info.Recording
        mcmd.Parameters("@TrackTitle").Value = mp3Info.TrackTitle
        mcmd.Parameters("@TrackSequence").Value = mp3Info.TrackSequence
        mcmd.Parameters("@TrackFileName").Value = mp3Info.FileName
        mcmd.ExecuteNonQuery()
        RaiseEvent FileLoaded(Me, _ 
          New FileFoundEventArgs(e.FoundFile))
    End Sub
    Private Function GetInsertCommand() As SqlCommand
        Dim cmd As New SqlCommand("AddMP3", mcon)
        cmd.CommandType = CommandType.StoredProcedure
        Dim prm As SqlParameter
        prm = New SqlParameter("@ArtistName", SqlDbType.VarChar, 30)
        prm.Direction = ParameterDirection.Input
        cmd.Parameters.Add(prm)
        prm = New SqlParameter("@RecordingTitle", SqlDbType.VarChar, 30)
        prm.Direction = ParameterDirection.Input
        cmd.Parameters.Add(prm)
        prm = New SqlParameter("@TrackTitle", SqlDbType.VarChar, 30)
        prm.Direction = ParameterDirection.Input
        cmd.Parameters.Add(prm)
        prm = New SqlParameter("@TrackSequence", SqlDbType.TinyInt)
        prm.Direction = ParameterDirection.Input
        cmd.Parameters.Add(prm)
        prm = New SqlParameter("@TrackFileName", SqlDbType.NVarChar, 260)
        prm.Direction = ParameterDirection.Input
        cmd.Parameters.Add(prm)
        Return cmd
    End Function
End Class

Access to the stored procedure is defined in the method GetInsertCommand(). When an instance of the class is created, the connection string to be used is passed into the constructor and an instance of the ID3v1Reader class is created. The Load command uses a supporting class, FindFiles, to actually search for MP3 files based upon a starting location. The FindFiles class raises a custom event, FindFound (which is based upon the FindFoundDelegate type) that exposes a custom event argument, FileFoundEventArgs. The FileFoundEventArgs exposes a System.IO.FileInfo object which is then used to the harvest the file name, which in turn is passed to the ID3v1Reader instance. Once the file is parsed, the input data is fed to the stored procedure and data is updated. Below is the code for the FileFoundEventArgs and FindFiles classes:

Option Strict On
Imports System.IO
Friend Class FileFoundEventArgs
    Private m_FoundFile As FileInfo = Nothing
    Public Sub New(ByVal FoundFile As FileInfo)
        Me.m_FoundFile = FoundFile
    End Sub
    Public ReadOnly Property FoundFile() As FileInfo
        Get
            Return Me.m_FoundFile
        End Get
    End Property
End Class
Friend Delegate Sub FileFoundDelegate( _
 ByVal sender As Object, ByVal e As FileFoundEventArgs)
Friend Class FindFiles
    Friend Event FileFound As FileFoundDelegate
    Private M_SearchExt As String = "*.mp3"
    Public Property SearchExt() As String
        Get
            Return M_SearchExt
        End Get
        Set(ByVal Value As String)
            M_SearchExt = Value
        End Set
    End Property
    Public Sub Start(ByVal Path As String)
        If Directory.Exists(Path) Then
            Dim di As New DirectoryInfo(Path)
            Me.EnumDirectory(di)
        End If
    End Sub
    Private Sub EnumDirectory(ByVal Dir As DirectoryInfo)
        EnumFiles(Dir)
        Dim ds() As DirectoryInfo = Dir.GetDirectories()
        Dim l As DirectoryInfo
        For Each l In ds
            EnumDirectory(l)
        Next
    End Sub
    Private Sub EnumFiles(ByVal Dir As DirectoryInfo)
        Dim f As FileInfo
        Dim filesFound() As FileInfo
        filesFound = Dir.GetFiles(Me.SearchExt)
        For Each f In filesFound
            RaiseEvent FileFound(Me, New FileFoundEventArgs(f))
        Next
    End Sub
End Class

Once the information has been loaded into the database, the LoadMp3s instance fires a FileLoaded event, also based upon FindFoundDelegate type, allowing the UI a chance to inform the user of progress. The code for calling the LoadMp3 instance and catching the FileLoaded event is listed below:

Private Sub btnAddFiles_Click(ByVal sender As System.Object, _
  ByVal e As System.EventArgs) Handles btnAddFiles.Click
    Try
        Select Case Me.fbd.ShowDialog(Me)
            Case DialogResult.OK
                Dim loader As New LoadMp3s(Me.m_ConnectionString)
                AddHandler loader.FileLoaded, _
                  AddressOf Me.OnFileLoaded
                Me.blnNeedRefresh = True
                Dim intFilesFound As Integer = _
                  loader.Load(Me.fbd.SelectedPath)
                MessageBox.Show(intFilesFound & _
                  " were loaded into the database", _
                  Me.Text, MessageBoxButtons.OK, _
                  MessageBoxIcon.Information)
                RemoveHandler loader.FileLoaded, _
                  AddressOf Me.OnFileLoaded
                Me.rpnlStatus.BodyText = "Ready"
        End Select
    Catch ex As Exception
        MessageBox.Show(ex.Message, ex.Source, _
          MessageBoxButtons.OK, MessageBoxIcon.Error)
    End Try
End Sub
Private Sub OnFileLoaded(ByVal sender As Object, _
  ByVal e As FileFoundEventArgs)
    If blnNeedRefresh Then
        Me.Refresh()
        Me.blnNeedRefresh = False
    End If
    Me.rpnlStatus.BodyText = _
      e.FoundFile.FullName & " added!"
        Me.rpnlStatus.Refresh()
End Sub

Browsing the Collection

Once all the MP3 data is loaded, you need a way to browse the collection. At this point, the only stored procedures that exist are those to add data. So, create another set of stored procedures. GetAllArtists returns all of the records in the Artists table, sorted by ArtistName. GetAllRecordings returns all of the records in the Recordings table, sorted by ArtistID, then by RecordingTitle. Finally GetAllTracks returns all the tracks sorted by RecordingTitle and then by TrackSequence. The following list shows the GetAllTracks stored procedure:

ALTER PROCEDURE dbo.GetAllTracks
AS
SELECT TrackID, TrackTitle, RecordingID, TrackSequence, TrackFileName,
CONVERT(varchar(3), TrackSequence) + ' - ' + TrackTitle 
AS TrackDisplayInfo
FROM Tracks
ORDER BY RecordingID, TrackSequence
RETURN

In addition to returning the rows, the stored procedure returns a calculated column, TrackDisplayInfo. Generally, it's more efficient to have the data loaded into a client data structure, such as a DataSet, in the sort order that will be used most often. The same thing applies to calculated columns. The TrackDisplayInfo column will be used by the UI for track selection.

Getting access to the stored procedures using code would be very similar to the code used earlier. However, I wanted to write less code, not more. In addition, Visual Basic Express includes some really nice data binding enhancements. As mentioned earlier, adding a database to a project also adds a DataSet. Opening the DataSet Designer provides a nicely colored design surface with hints to add items from the Database Explorer or Toolbox. Do so, dragging and dropping tables and stored procedures on to the design surface. Using the Relation object off of the toolbox, you can define the relationships between the stored procedures. Figure 15 shows the configured DataSet in the DataSet Designer. Figure 16 shows the Edit Relation dialog in action.

Figure 15. The MyMusicDataSet object in the DataSet Designer.

Figure 16. The Edit Relation Dialog

With the typed DataSet configured, create a new form, frmBrowser. You'll make it inherit from the rounded base class form (frmRound) by showing all files in the Solution Explorer and editing the frmBrowser.Designer.vb file by hand. Unlike previous versions, the Windows Forms designer in Visual Basic Express (and the other versions of Express and Visual Studio 2005) puts all the designer-generated code in a partial class file. Partial classes are a new feature supported by the .NET Framework compilers that allow a single class to be partitioned between multiple source files. The primary class file is defined just as before with a modifier and Class keyword. Partial classes are defined in any number of secondary source files by using the new Partial keyword in Visual Basic as part of the class definition between the visibility modifier and the Class keyword.

To set up the data binding for the form, open the new Data Sources window. The Data Sources window was auto-populated with entities representing the data-returning objects from the project's typed DataSet. Figure 17 displays the Data Sources window.

Figure 17. The Data Sources window

There are six items in the Data Sources windows. Three of the items represent the resultset returning stored procedures. Dragging the GetAllArtists object to the design surface creates sets up the data binding by adding a number of components and controls. Figure 18 displays a test form after the GetAllArtists data source was dragged on to the form's design surface.

Figure 18. A test form bound to a stored procedure with automatic data binding.

The drag and drop process ads three components: a DataSet, DataConnector, and a TableAdapter. It also adds, by default, a DataGridView control to display the data. In addition, there's a new hybrid component/control called the DataNavigator. This item has an object on the component try as well as a UI portion. Its whole purpose is to provide a standard implementation for navigation of a data source. The DataSet object is pretty self-explanatory. It's the form's runtime instance for accessing data in the database. The DataConnector component is all about making the process of binding controls to an underlying data source easier. The DataConnector component is the binding object between the data source and the controls that need binding. The TableAdapter is a typed version of the DataAdapter classes used in previous versions of the .NET Framework. It exposes typed methods for accessing data instead of directly exposing the underlying command objects.

With the Artist information bound, next set up a master-detail relationship between Artists and Recordings. To do this, select the Artists DataGridView control's Smart Tag (see Figure 19) to execute the Configure Master Details command. This command opens a dialog (see Figure 20) the lets you set up the parent-child relationship between two UI elements. In this case you add a second grid to the form. Now when you select an artist, the correct recording records are displayed. Repeat the process, binding the tracks to the recordings. You'll need to add fill commands to the Form's Load event (one for each TableAdapter).

Figure 19. A DataGridView controls Smart Tag displayed.

Figure 20. The Add Related Databound UI dialog

While the UI is nice, for this application it doesn't quite fit. Clicking a data source in the Data Sources window provides a drop-down to change the default UI control provided when a drag-and-drop gesture is used. In this build of our application, the only two options are a grid or a set of labels and text boxes. Future builds will support other list controls such as List Boxes and even other custom controls. To remedy the solution in this build, simply add three list box controls and adjust the DataSource and DisplayMember properties appopriately based upon the way the grids are setup. Then delete the three grids and the DataNavigtor control, adjust the form, and the results are shown in Figure 21.

Figure 21. The completed Browse for Music form displaying data from the database.

The last thing to do is to provide a way add tracks to the Play List on the main from. At this point I realized I might want a way to save play lists to the database also. Since I was using SQL Express, I knew I could take advantage of the new XML datatype. So, proceed to create a PlayLists table in the database with the following information:

Table 4. PlayLists Table

Column Name	Data Type	Notes
PlayListTitle	nvarchar(50)	Primary key
PlayList	XML

With the table defined, you can cruft up a basic XML document structure for your play lists and add it to the application as an embedded resource called PlayListTemplate.xml. Then write a class called PlayList, listed below:

Option Strict On
Imports System.Data
Imports System.IO
Imports System.Reflection
Imports System.Xml
Friend Class PlayList
    Private m_PlayListDS As DataSet = Nothing
    Private m_Tracks As DataTable = Nothing
    Public Sub New()
        Dim asm As Assembly = _
          Assembly.GetExecutingAssembly()
        Dim strm As Stream = _
         asm.GetManifestResourceStream("VBXMP3.PlayListTemplate.xml")
        m_PlayListDS = New DataSet
        m_PlayListDS.ReadXml(strm)
        m_Tracks = m_PlayListDS.Tables(0)
        m_Tracks.TableName = "Tracks"
        m_Tracks.Rows(0).Delete()
        strm.Close()
    End Sub
    Public Sub New(ByVal PlayList As DataSet)
        m_PlayListDS = PlayList
        m_Tracks = m_PlayListDS.Tables(0)
        m_Tracks.TableName = "Tracks"
    End Sub
    Public ReadOnly Property TrackCount() As Integer
        Get
            Return m_Tracks.Rows.Count
        End Get
    End Property
    Public Sub AddNewTrack( _
      ByVal TrackId As String, ByVal TrackSequence As String, _
      ByVal TrackTitle As String, ByVal Artist As String, _
      ByVal TrackFileName As String)
        Dim dr As DataRow = m_Tracks.NewRow()
        dr.BeginEdit()
        dr(0) = TrackId
        dr(1) = TrackSequence
        dr(2) = TrackTitle
        dr(3) = Artist
        dr(4) = TrackFileName
        dr.EndEdit()
        m_Tracks.Rows.Add(dr)
    End Sub
    Public Function GetXML() As String
        Dim sw As New StringWriter()
        m_PlayListDS.WriteXml(sw)
        Return sw.ToString()
    End Function
    Public ReadOnly Property TracksDataTable() As DataTable
        Get
            Return Me.m_Tracks
        End Get
    End Property
End Class

When a user double-clicks on a track in the track list box, a PlayList instance is created. In the PlayList instance's constructor, the XML template is sucked out of the assembly and an untyped dataset is created. The code in the list box's double-click handler pulls out the necessary data using DataRowView and typed DataRow objects and calls the PlayList object's AddNewTrack() method. In addition, if it's the first row added, the play list is bound to the main form's list box so the user can see the tracks that have been added. The following code displays the user code-behind for the frmBrowser:

Option Strict On
Imports System.Data
Imports VBXMP3.MyMusicDataSet
Public Class frmBrowser
    Private m_MyParentForm As frmMain = Nothing
    Private mCurrentPlayList As PlayList = Nothing
    Private Sub frmBrowser_Load(ByVal sender As System.Object, _
      ByVal e As System.EventArgs) Handles MyBase.Load
        Me.GetAllArtistsTableAdapter.Fill( _
         Me.MyMusicDataSet.GetAllArtists)
        Me.GetAllRecordingsTableAdapter.Fill( _
          Me.MyMusicDataSet.GetAllRecordings)
        Me.GetAllTracksTableAdapter.Fill( _
          Me.MyMusicDataSet.GetAllTracks)
    End Sub
    Private Sub lstTracks_DoubleClick(ByVal sender As System.Object, _
      ByVal e As System.EventArgs) Handles lstTracks.DoubleClick
        Dim rawRow As DataRowView = CType(Me.lstTracks.SelectedItem, DataRowView)
        Dim row As GetAllTracksRow = CType(rawRow.Row, GetAllTracksRow)
        Dim rawRowArtist As DataRowView = CType(Me.lstArtists.SelectedItem, DataRowView)
        Dim rowArtist As GetAllArtistsRow = CType(rawRowArtist.Row, GetAllArtistsRow)
        InitPlayList()
        mCurrentPlayList.AddNewTrack(row.TrackID.ToString(), _
          (mCurrentPlayList.TrackCount + 1).ToString(), _
          row.TrackTitle, rowArtist.ArtistName, row.TrackFileName)
        If mCurrentPlayList.TrackCount = 1 Then
            BindPlayListToParent()
        End If
    End Sub
    Public Property MyParentForm() As frmMain
        Get
            Return Me.m_MyParentForm
        End Get
        Set(ByVal value As frmMain)
            Me.m_MyParentForm = value
        End Set
    End Property
    Private Sub BindPlayListToParent()
        If mCurrentPlayList IsNot Nothing Then
            Me.MyParentForm.PlayList = mCurrentPlayList.TracksDataTable
        End If
    End Sub
    Private Sub InitPlayList()
        If Me.mCurrentPlayList Is Nothing Then
            mCurrentPlayList = New PlayList()
        End If
    End Sub
End Class

You can implement saving a play list to the database by adding a context menu to the main form. When the secondary-click is performed over the Play List list box, an option is presented to save the play list to the database. The following code shows how this is done.

    Private Sub miSaveCurrentList_Click(ByVal sender As System.Object, _
      ByVal e As System.EventArgs) Handles miSaveCurrentList.Click
        If Me.rpnlPlayList.HeaderText = "Play List" _
          AndAlso Me.lstPlayList.Items.Count > 0 Then
            Dim strTitle As String = _
              InputBox("Enter Your Playlist Title", _
                "New PlayList", String.Empty)
            If Not strTitle = String.Empty Then
                Dim pl As New PlayList( _
                  CType(Me.lstPlayList.DataSource, DataTable).DataSet)
                Dim con As New SqlConnection(Me.m_ConnectionString)
                Dim cmd As New SqlCommand("AddPlayList", con)
                cmd.CommandType = CommandType.StoredProcedure
                Dim prm As New SqlParameter( _
                  "@PlayListTitle", SqlDbType.NVarChar, 50)
                prm.Direction = ParameterDirection.Input
                prm.Value = strTitle
                cmd.Parameters.Add(prm)
                prm = New SqlParameter( _
                  "@PlayList", SqlDbType.Xml)
                prm.Direction = ParameterDirection.Input
                prm.Value = pl.GetXML()
                cmd.Parameters.Add(prm)
                Try
                    con.Open()
                    Dim i As Integer = cmd.ExecuteNonQuery()
                    Me.rpnlPlayList.HeaderText = strTitle
                Catch ex As Exception
                    MessageBox.Show(ex.Message, ex.Source, _
                      MessageBoxButtons.OK, MessageBoxIcon.Error)
                Finally
                    If con IsNot Nothing Then
                        con.Close()
                    End If
                End Try
            End If
        End If
    End Sub

The code first checks to see if the list has already been saved and then verifies there are items in the list to save. The user is asked for a name, and if one is provided, the list box's data source is cast back to a DataTable and passed to the PlayList class's overload constructor. The necessary data objects are created, pointing to the AddPlayList stored procedure (list below).

ALTER PROCEDURE dbo.AddPlayList
    @PlayListTitle varchar(50),
    @PlayList xml
    
AS
DECLARE @PLCount int
   SELECT @PLCount = Count(PlayListTitle)
   FROM PlayLists 
   WHERE PlayListTitle = @PlayListTitle
IF @PLCount > 0
    BEGIN
        UPDATE PlayLists 
        SET PlayList = @PlayList
        WHERE PlayListTitle = @PlayListTitle
    END
ELSE   
    BEGIN
        INSERT INTO PlayLists (PlayListTitle, PlayList)
        VALUES (@PlayListTitle, @PlayList)
    END
    
   RETURN

The stored procedures parameters are set, with the XML for the PlayList parameter being provided via the PlayList instance's GetXML() method. If the update was sucessful, then the Play List header is updated with the name of the play list.

Wrapping It Up

Creating even a simple application can be quite a bit of work. However using SQL Express and Visual Basic Express you can cut hours of work off of the task. Is this application complete? Not really. But remember, shipping is a feature. Hopefully in a later beta, I can revisit the application and some features.

Further Reading

Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design, Second Edition by Michael J. Hernandez. Addison-Wesley Pub Co, 2003; ISBN 0201752840

The Relational Model for Database Management: Version 2 by E. F. Codd. Addison Wesley Publishing Company, 1990. ISBN 0201141922

The Database Relational Model: A Retrospective Review and Analysis : A Historical Account and Assessment of E. F. Codd's Contribution to the Field of Database Technology by C. J. Date. Addison Wesley Longman, 2000. ISBN 0201612941

An Introduction to Database Systems, Eighth Edition by C. J. Date. Addison Wesley Publishing Company, 8th edition 2003. ISBN 0321197844

© Microsoft Corporation. All rights reserved.