CODEWITHSUNDEEP
c#sql-servercsvado.net
dotnetdevcsharp
dotnetdevcsharp

Reputation: 3980

Batch Exporting to CSV

I am processesing a large number of appointments to csv sending notifications out to users etc. What I want to do is set a isProcessed flag to say that the current line has already been processed I not sure how to do that in my current loop.

public void DumpTableToFile(SqlConnection connection, string tableName, string destinationFile)
    {
        using (var command = new SqlCommand("select LineType,CustomerFirstName AS 'Forename' ,CustomerLastName,Age,dob as 'Date of Birth',maritalStatus AS 'Marital Status',homePhone AS 'Home', mobileNumber AS Mobile,emailAddress AS Email,Address1 + Address2  + PostCode AS 'Address' ,employmentStatus AS Employment,occupation AS Occupation,propertyValue AS 'Property Value',mortgageBalance AS 'Mortgage Balance',balanceOnSecuredDebt AS 'Balance on secured Debt',mortgageType as 'Mortgage Type' from " + tableName, connection))
        using (var reader = command.ExecuteReader())
        using (var outFile = File.CreateText(destinationFile))
        {
            string[] columnNames = GetColumnNames(reader).ToArray();
            int numFields = columnNames.Length;
            outFile.WriteLine(string.Join(",", columnNames));
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    string[] columnValues =
                        Enumerable.Range(0, numFields)
                                  .Select(i => reader.GetValue(i).ToString())
                                  .Select(field => string.Concat("\"", field.Replace("\"", "\"\""), "\""))
                                  .ToArray();

                    outFile.WriteLine(string.Join(",", columnValues));
                }
            }
        }

The flag is called isProcessed and I want to set it to true once its gone through the csv export. This is so I can do batching export. It exists within the same table appointments

Edit 1

Sorry for not stating I am wanting this flag to be written back to the appointments table for the curent record it is spitting out in the csv export the csv export works I just need a way of identifying that its been exported so its not processed a second time.

Upvotes: 3

Views: 1690

Answers (1)

Martin Mulder
Martin Mulder

Reputation: 12954

Do the follow steps:

  1. Add a criterium to your query: WHERE NOT isProcessed, so next the you do an export, only the records that are not processed will be processed.
  2. After finishing your export, send this command to the database:"UPDATE " + tableName + " SET isProcessed=true WHERE <exact same criteria as the select statement>". This way all records are now marked as processed.
  3. Wrap a TransactionScope arround export-mechanisme incl. the update. This way, when something fails, the whole operation will be rolled back.
  4. Wrap a try-catch arround the TransactionScope, so when the export fails, the CSV-file is deleted, so you will never have a half exported batch.

Your code would become something like this (I did not test it):

public void DumpTableToFile(SqlConnection connection, string tableName, string destinationFile)
{
    try
    {
        using(var transaction = new TransactionScope())
        {
            // Select all non-processed records.
            using (var command = new SqlCommand("select LineType,CustomerFirstName AS 'Forename' ,CustomerLastName,Age,dob as 'Date of Birth',maritalStatus AS 'Marital Status',homePhone AS 'Home', mobileNumber AS Mobile,emailAddress AS Email,Address1 + Address2  + PostCode AS 'Address' ,employmentStatus AS Employment,occupation AS Occupation,propertyValue AS 'Property Value',mortgageBalance AS 'Mortgage Balance',balanceOnSecuredDebt AS 'Balance on secured Debt',mortgageType as 'Mortgage Type' from " + tableName 
                + " WHERE NOT isProcessed", connection))
            using(var reader = command.ExecuteReader())
            using(var outFile = File.CreateText(destinationFile))
            {
                string[] columnNames = GetColumnNames(reader).ToArray();
                int numFields = columnNames.Length;
                outFile.WriteLine(string.Join(",", columnNames));
                if (reader.HasRows)
                {
                    while(reader.Read())
                    {
                        string[] columnValues =
                            Enumerable.Range(0, numFields)
                                .Select(i => reader.GetValue(i).ToString())
                                .Select(field => string.Concat("\"", field.Replace("\"", "\"\""), "\""))
                                .ToArray();

                        outFile.WriteLine(string.Join(",", columnValues));
                    }
                }
            }

            // Update the same records that were just exported.
            using (var command = new SqlCommand("UPDATE " + tableName + " SET isProcessed=true WHERE NOT isProcessed", connection))
                command.ExecuteNonQuery();

            transaction.Complete();
        }
    }
    catch
    {
        // If something went wrong, delete the export file.
        File.Delete(destinationFile);
        throw;
    }
}

Upvotes: 2

Related Questions