Managing a Music Collection Using Visual Basic Express and SQL Server Express
Brian A. Randell
MCW Technologies, LLC
Updated June 2005
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.
Designing the Catalog
Building the Database
Creating the User Interface
Working with the Database
Wrapping It Up
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.
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|
Table 2. Recordings Table
|Column Name||Data Type||Notes|
|RecordingID||int||Primary key, identity|
Table 3. Tracks Table
|Column Name||Data Type||Notes|
|TrackID||int||Primary key, identity|
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|
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.
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