dcsimg
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 8 of 8

Thread: Basic Database Options Question

  1. #1
    Join Date
    Feb 2014
    Posts
    8

    Basic Database Options Question

    I have asked this in several forums and cannot seem to get an answer. Perhaps I'm not asking the question correctly. Let me try again.

    I have a Karaoke collection of almost 200,000 songs. Each song consists of two files like: songTitle.CDG and songTitle.MP3. Each file has three main pieces of data: A serial number assigned by the publisher, the song title and the artist. Something like:
    SC5005-01 - Johnny Cash - I Walk the Line.cdg
    SC5005-01 - Johnny Cash - I Walk the Line.mp3

    I'm trying to straighten out several problems:
    1. Name backwards. e.g. "Cash, Johnny" instead of "Johnny Cash" or
    "Beatles, The" instead of "The Beatles" or "Beatles"
    2. Three parts of file name NOT in order: (Most Karaoke databases expect serial number, artist, title), such as SC2001-03 - The Other Side of Nowhere - Kris Kristofferson.mp3. This entry sometimes results in a Title of "Kris Kristofferson" and the Artist shown as "I Walk the Line".
    3. Delimiter incorrect: Most databases want the " - " (space-dash-space) to be the delimiter between the three parts.
    4. And several other problems, but those above give you the idea.

    I want to dump the entire list of file names into a data base, then write code to fix minor or major problems.

    So that's the situation.

    I am medium fluent with databases. I have taught several classes in Access and SQL Server 2005, and I also completed small database apps using the built in database in Visual Basic up to Version 6.

    Now the question... I can't get a clear picture of what databases and/or database options are available to me using VC# to code. The VC# jargon isn't familiar to me. What's the difference between LINQ and List? Are these two different approaches to database inside VC#? I'm looking for something that will search, correct and rename Windows files quickly and efficiently with VC# code.

    I might be way off, but if I had to guess right now, I'd say I have 3 options:
    1. Use SQL
    2. Use VC# with one of the List variations.
    3. Use VC# with LINQ.

    Can you correct and provide some details on my answer above, so I can get enough info to make an intelligent choice on how to proceed with the solution to the problem I described above.

  2. #2
    Arjay's Avatar
    Arjay is offline Moderator / EX MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    13,227

    Re: Basic Database Options Question

    Quote Originally Posted by richardv2 View Post
    I want to dump the entire list of file names into a data base, then write code to fix minor or major problems.
    Okay if you want to use a db to do this, but you probably can do this all in memory without using a database. However, if you decide to use a SQL, Microsoft has some free SQL db options.

    Quote Originally Posted by richardv2 View Post
    Now the question... I can't get a clear picture of what databases and/or database options are available to me using VC# to code. The VC# jargon isn't familiar to me. What's the difference between LINQ and List? Are these two different approaches to database inside VC#?
    No, a List is an in memory collection (like an array). LINQ is short for Language-Integrated Query - it allows you to do database-like queries on collections (e.g. a list). Neither of these are databases, per se.

    Quote Originally Posted by richardv2 View Post
    I'm looking for something that will search, correct and rename Windows files quickly and efficiently with VC# code.
    Depending on how many files you need to do, you could do these in memory (200,200 probably are ok).

    I would create a simple class that contains properties with the original file name and the new file name (call the class FileCompare). I store a list of these objects into a collection (List<FileCompare>) Then use Directory.GetFiles() to read the file system and add an entry to the list.

    Next, walk the list and see if the original file name needs to be modified. If it does, set the new file name for that entry.

    Afterwards, walk the list again and check for any entries that have a modified file name. Use the File.Copy command to rename these entries.

  3. #3
    Join Date
    Feb 2014
    Posts
    8

    Re: Basic Database Options Question

    I pretty much understand what you said, but let me repeat back to verify.
    First... YES, I WOULD LIKE TO DO THIS ALL IN MEMORY. It didn't occur to me when I asked the question.

    So, List will do it. Knowing that, I have several text books, and can find tons of info online to use List.
    ...and I don't need any permanent storage of the list. The files are already on disk. All I need the List for is to rename and rewrite the name.

    I would:
    1. Load the file names into the list.
    2. Scan for a specific error; changing/correcting each one as needed, then write the modified name back with File.Copy.
    3. Once the renamed files are written, I have no use for the List, so it can die when I exit the program.

    I have at least ten different kinds of errors I want to fix, so each time I scan in Step 2 above, I would have just written and tested the code to fix one specific error. Here are a couple of the errors I will attack:
    A. Serial - Title - Artist.ext (Reverse Title and Artist)
    B. Change [lastName, firstName] to [firstName lastName], so "Cash, Johnny" becomes "Johnny Cash".

    Am I on the right track?

  4. #4
    Arjay's Avatar
    Arjay is offline Moderator / EX MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    13,227

    Re: Basic Database Options Question

    Yes. It's pretty simple.
    Code:
    using System;
    using System.Collections.Generic;
    using System.IO;
    
    namespace FileProcessor
    {
        // a class to store original file names and the corresponding renamed file
        class FileCompare
        {
            public string FileNameOriginal { get; set; }
            public string FileNameRenamed { get; set; }
        }
    
        class Program
        {
            static void Main(string[] args)
            {
                var fileList = new List<FileCompare>();
    
                // Read all the files in the C:\\ directory and add them to our list
                foreach (var fileName in Directory.GetFiles("C:\\", "*.*"))
                {
                    var fi = new FileInfo(fileName);
                    fileList.Add(new FileCompare {FileNameOriginal = fi.Name});
                }
    
                // Walk through the file list and rename the appropriate files
                // Note: we mock up the file rename operation here - we take any filename with ABC and rename the string to CBA
                foreach (var file in fileList)
                {
                    // If the file name contains ABC, rename it to CBA (case-sensitive)
                    if (file.FileNameOriginal.Contains("ABC"))
                    {
                        file.FileNameRenamed = file.FileNameOriginal.Replace("ABC", "CBA"); // renames all ABC occurances to CBA and save it in the FileNameRenamed property
                    }
                }
    
                // Walk through the file list and for files that have been renamed, rename them on the file system
                foreach (var file in fileList)
                {
                    if (!String.IsNullOrEmpty(file.FileNameRenamed))
                    {
                        File.Copy(file.FileNameOriginal, file.FileNameRenamed);
                    }
                }
            }
        }
    }
    Here is the equivalent solution using LINQ:
    Code:
    using System;
    using System.IO;
    using System.Linq;
    
    namespace FileProcessor
    {
        // a class to store original file names and the corresponding renamed file
        class FileCompare
        {
            public string FileNameOriginal { get; set; }
            public string FileNameRenamed { get; set; }
        }
    
        class Program
        {
            static void Main(string[] args)
            {
                // Read all the files in the C:\\ directory and store them in a List<FileCompare> list
                var fileList = Directory.GetFiles("C:\\", "*.*").Select(fileName => new FileInfo(fileName)).Select(fi => new FileCompare {FileNameOriginal = fi.Name}).ToList();
    
                // Walk through the file list and rename the appropriate files
                // Note: we mock up the file rename operation here - we take any filename with ABC and rename the string to CBA
                foreach (var file in fileList.Where(file => file.FileNameOriginal.Contains("ABC")))
                {
                    file.FileNameRenamed = file.FileNameOriginal.Replace("ABC", "CBA"); // renames all ABC occurances to CBA and save it in the FileNameRenamed property
                }
    
                // Walk through the file list and for files that have been renamed, rename them on the file system
                foreach (var file in fileList.Where(file => !String.IsNullOrEmpty(file.FileNameRenamed)))
                {
                    File.Copy(file.FileNameOriginal, file.FileNameRenamed);
                }
            }
        }
    }

  5. #5
    Arjay's Avatar
    Arjay is offline Moderator / EX MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    13,227

    Re: Basic Database Options Question

    My previous solution will get the job done, but I have a question.

    You mention that you have files with names where the author's name is reversed. Such as Cash, Johnny instead of Johnny Cash.

    I'm wondering about this since file names can only contain certain characters and can't contain other characters such as commas. In other words, you'll never have a file name such as "Cash, Johnny.txt" as Windows won't allow it.

  6. #6
    Join Date
    Feb 2014
    Posts
    8

    Re: Basic Database Options Question

    Quote Originally Posted by Arjay View Post
    In other words, you'll never have a file name such as "Cash, Johnny.txt" as Windows won't allow it.
    I'm not sure what to say. I have a Windows 10 laptop, a Windows 7 laptop and a Windows machine at work. They all have my test folder with comma files in them so that I can test my VC# code. Here is the screen shot I just took on a Windows computer that has many of my "comma" files:
    Name:  Commas.jpg
Views: 87
Size:  747 Bytes

  7. #7
    Join Date
    Feb 2014
    Posts
    8

    Re: Basic Database Options Question

    Arjay,

    WOW! Many thanks. You gave me a lot to work with, and really pointed me in the right direction

  8. #8
    Arjay's Avatar
    Arjay is offline Moderator / EX MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    13,227

    Re: Basic Database Options Question

    Quote Originally Posted by richardv2 View Post
    I'm not sure what to say. I have a Windows 10 laptop, a Windows 7 laptop and a Windows machine at work. They all have my test folder with comma files in them so that I can test my VC# code. Here is the screen shot I just took on a Windows computer that has many of my "comma" files:
    Name:  Commas.jpg
Views: 87
Size:  747 Bytes
    You're correct. Wow. I just saved a file with commas.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width




On-Demand Webinars (sponsored)