C# SQLite file import prevent duplicates

Question

Hi, I am attempting to get a directory (which is ever-growing) full of .txt comma delimited files to import into my SQLite db. I now have all of the files importing ok, however I need to have some way of excluding the files that have been previously added to db. I have a column in the db called FileName where the name and extension are stored next to each record from each file. Now I need to say 'If the code finds XXX.txt and XXX.txt is already in db, then skip this file'. Can I somehow add this logic to the getfiles command or is there another easy way?

using (SQLiteCommand insertCommand = con.CreateCommand())
                {
                    SQLiteCommand cmdd = con.CreateCommand();
                    string[] files = Directory.GetFiles(@"C:\Documents and Settings\js91162\Desktop\", "R303717*.txt*", SearchOption.AllDirectories);
                    foreach (string file in files)
                    {

                        string FileNameExt1 = Path.GetFileName(file);



                        cmdd.CommandText =
                            @" 
                    SELECT COUNT(*) FROM Import WHERE FileName = @FileExt;";
                        cmdd.Parameters.Add(new SQLiteParameter("@FileExt", FileNameExt1));

                    int count = Convert.ToInt32(cmdd.ExecuteScalar());
                    //int count = ((IConvertible)insertCommand.ExecuteScalar().ToInt32(null));

                    if (count == 0)
                    {

                        Console.WriteLine("Parsing CMM data for SQL database... Please wait.");

                        insertCommand.CommandText =
                            @" 
                    INSERT INTO Import  (FeatType, FeatName, Value, Actual, Nominal, Dev, TolMin, TolPlus, OutOfTol, PartNumber, CMMNumber, Date, FileName) 
                    VALUES     (@FeatType, @FeatName, @Value, @Actual, @Nominal, @Dev, @TolMin, @TolPlus, @OutOfTol, @PartNumber, @CMMNumber, @Date, @FileName);";

                        insertCommand.Parameters.Add(new SQLiteParameter("@FeatType", DbType.String));
                        insertCommand.Parameters.Add(new SQLiteParameter("@FeatName", DbType.String));
                        insertCommand.Parameters.Add(new SQLiteParameter("@Value", DbType.String));
                        insertCommand.Parameters.Add(new SQLiteParameter("@Actual", DbType.Decimal));
                        insertCommand.Parameters.Add(new SQLiteParameter("@Nominal", DbType.Decimal));
                        insertCommand.Parameters.Add(new SQLiteParameter("@Dev", DbType.Decimal));
                        insertCommand.Parameters.Add(new SQLiteParameter("@TolMin", DbType.Decimal));
                        insertCommand.Parameters.Add(new SQLiteParameter("@TolPlus", DbType.Decimal));
                        insertCommand.Parameters.Add(new SQLiteParameter("@OutOfTol", DbType.Decimal));
                        insertCommand.Parameters.Add(new SQLiteParameter("@Comment", DbType.String));




                        string FileNameExt = Path.GetFileName(file);
                        string RNumber = Path.GetFileNameWithoutExtension(file);

                        string RNumberE = RNumber.Split('_')[0];

                        string RNumberD = RNumber.Split('_')[1];
                        string RNumberDate = RNumber.Split('_')[2];

                        DateTime dateTime = DateTime.ParseExact(RNumberDate, "yyyyMMdd", Thread.CurrentThread.CurrentCulture);
                        string cmmDate = dateTime.ToString("dd-MMM-yyyy");
                        string[] lines = File.ReadAllLines(file);
                        bool parse = false;

                        foreach (string tmpLine in lines)
                        {


                            string line = tmpLine.Trim();
                            if (!parse && line.StartsWith("Feat. Type,"))
                            {
                                parse = true;
                                continue;
                            }
                            if (!parse || string.IsNullOrEmpty(line))
                            {
                                continue;
                            }

                            Console.WriteLine(tmpLine);
                            foreach (SQLiteParameter parameter in insertCommand.Parameters)
                            {
                                parameter.Value = null;
                            }

                            string[] values = line.Split(new[] { ',' });

                            for (int i = 0; i < values.Length - 1; i++)
                            {
                                SQLiteParameter param = insertCommand.Parameters[i];
                                if (param.DbType == DbType.Decimal)
                                {
                                    decimal value;
                                    param.Value = decimal.TryParse(values[i], out value) ? value : 0;
                                }
                                else
                                {
                                    param.Value = values[i];
                                }
                            }
                            insertCommand.Parameters.Add(new SQLiteParameter("@PartNumber", RNumberE));
                            insertCommand.Parameters.Add(new SQLiteParameter("@CMMNumber", RNumberD));
                            insertCommand.Parameters.Add(new SQLiteParameter("@Date", cmmDate));
                            insertCommand.Parameters.Add(new SQLiteParameter("@FileName", FileNameExt));
                            // 
                            insertCommand.ExecuteNonQuery();

                        }


                    } 
                    }
                    Console.WriteLine("CMM data successfully imported to SQL database...");
                } 
                con.Close(); 
            } 

EDIT . Perhaps if there is a way to say if (file 'is present in db') { } ???

Answer 1

Why not simply move the files toanother folder once you have processed them.

Edit: Your updated code will do the trick but it will take longer and longer to run as your directory fills up because you are querying the database for every file in the directory. If you could retrive the list of imported files from the database into a List<string> you could then do the following using linq:

List<string> ImportedFiles = GetImportedFileList() // Method that gets the list of files from the db
foreach (string file in files.Where(fl => !ImportedFiles.Contains(fl)))

This would mean your foreach loop would only iterate through files that were not in the database already.

I have assumed you know how to write a method GetImportedFileList() that gets the list of files from the db and returns them as a List<string>.

Answer 2

Make the filename column unique or add a unique index for it, and then do an INSERT OR IGNORE, causing duplicates to be ignored without throwing exceptions.

Answer 3

You can load the filenames from the database into a list of string, and then you use the Except extension method to exclude those files from the filenames returned from your directory.

foreach (string file in directoryFilenames.Except(databaseFilenames))
{
   // do something
}

Ben Robinson offers a similar answer, but using Where and Contains is going to iterate over the second list for each item in the first list. The Except method will only iterate over the second list once.