SqlBulkCopy and File Archiving

Question

I have a process that loads data into a sql table from a flat file then needs to immediately move the file to an archive folder. However when running the code it imports the data but throws and IOException {"The process cannot access the file because it is being used by another process."}

There appears to be some contention in the process. Where and how should I avoid this?

 internal class Program
{
    private static void Main(string[] args)
    {
        string sourceFolder = @"c:\ImportFiles\";
        string destinationFolder = @"c:\ImportFiles\Archive\";

        foreach (string fileName in Directory.GetFiles(sourceFolder, "*.*"))
        {
            string sourceFileName = Path.GetFileName(fileName);
            string destinationFileName = Path.GetFileName(fileName) + ".arc";

            ProcessFile(fileName);

            string source = String.Concat(sourceFolder,sourceFileName);
            string destination = String.Concat(destinationFolder,destinationFileName);
            File.Move(source, destination);        
        }
    }



    static void ProcessFile(string fileName)
    {
        Encoding enc = new UTF8Encoding(true, true);
        DataTable dt = LoadRecordsFromFile(fileName, enc, ',');

        SqlBulkCopy bulkCopy = new SqlBulkCopy("Server=(local);Database=test;Trusted_Connection=True;",
                                               SqlBulkCopyOptions.TableLock);
        bulkCopy.DestinationTableName = "dbo.tblManualDataLoad";
        bulkCopy.WriteToServer(dt);
        bulkCopy.Close();

    }


    public static DataTable LoadRecordsFromFile(string fileName, Encoding encoding, char delimeter)
    {
        DataTable table = null;

        if (fileName != null &&
            !fileName.Equals(string.Empty) &&
            File.Exists(fileName))
        {
            try
            {
                string tableName = "DataImport";
                FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);

                List<string> rows = new List<string>();
                StreamReader reader = new StreamReader(fs, encoding);
                string record = reader.ReadLine();

                while (record != null)
                {
                    rows.Add(record);
                    record = reader.ReadLine();
                }

                List<string[]> rowObjects = new List<string[]>();
                int maxColsCount = 0;
                foreach (string s in rows)
                {
                    string[] convertedRow = s.Split(new char[] { delimeter });
                    if (convertedRow.Length > maxColsCount)
                        maxColsCount = convertedRow.Length;
                    rowObjects.Add(convertedRow);
                }

                table = new DataTable(tableName);
                for (int i = 0; i < maxColsCount; i++)
                {
                    table.Columns.Add(new DataColumn());
                }

                foreach (string[] rowArray in rowObjects)
                {
                    table.Rows.Add(rowArray);
                }
                //Remove Header Row From Import file
                DataRow row = table.Rows[0];
                row.Delete();
                table.AcceptChanges();
            }
            catch
            {
                //TODO SEND EMAIL ALERT ON ERROR
                throw new Exception("Error in ReadFromFile: IO error.");
            }
        }
        else
        {
            //TODO SEND EMAIL ALERT ON ERROR
            throw new FileNotFoundException("Error in ReadFromFile: the file path could not be found.");
        }
        return table;
    }
}

chue x · Accepted Answer · 2013-03-30T00:28:02.770

Your program is likely holding the file open. You should wrap FileStream and StreamReader objects in using statements. This closes those objects when the using block finishes.

The part of your LoadRecordsFromFile function that reads the file should look something like:

...
string tableName = "DataImport";
List<string> rows = new List<string>();
using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
    using (StreamReader reader = new StreamReader(fs, encoding))
    {
        string record = reader.ReadLine();
        while (record != null)
        {
            rows.Add(record);
            record = reader.ReadLine();
        }
    }
}
...

SqlBulkCopy and File Archiving

1 Answers1