SqlBulkCopy and File Archiving

c# file-io sqlbulkcopy

Question

My procedure needs to quickly transfer the file to an archive folder after loading data from a flat file into a SQL database. The code imports the data, but when it is executed, it raises an IOException that reads, "The process cannot access the file because it is being utilized by another process."

There seems to be some disagreement in the procedure. Where should I prevent this, and how?

 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;
    }
}   
1
0
3/30/2013 12:02:47 AM

Accepted Answer

Most likely, your software is keeping the file open. You should encloseFileStream and StreamReader things inusing statements. When the item is closed, theusing block edgings.

a portion of yourLoadRecordsFromFile The file reading function should like the following:

...
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();
        }
    }
}
...
1
3/30/2013 12:28:02 AM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow