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.

Quote:

---

Originally Posted by richardv2

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

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

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.

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?

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);
            }
        }
    }
}

---

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.

Quote:

---

Originally Posted by Arjay

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:
Attachment 34195

Arjay,

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

Quote:

---

Originally Posted by richardv2

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:
Attachment 34195

---

You're correct. Wow. I just saved a file with commas.