Writing String To File While Exporting Database Table

Question

I have a table that has a field called ACTION_TYPE. The potential values for it are "ADD ", "Change", "DELETE", and "-". I want to export the rows which have an ACTION_TYPE value that isn't "-". I'm successfully exporting the table row in the correct format to the file, however the headers aren't working/ being written.

Basically what I want is to separate the entries by the type in the file. In example

ADD
00           00      56               55
00           00      59               42
00           00      36               45
CHANGE
00           00      96               25
DELETE
00           00      76               75
00           00      38               95

So far I've been unsuccessful in even getting it to work with ADD. Also, when I do get it working with one of the three types, is there a better way than just cloning the process for each type (ADD, CHANGE, DELETE)?

using (StreamWriter writer =
                    new  StreamWriter("C:\\BillingExport\\EXPORTS\\EFTHLDAY.TABLE.TYPE08.TRANSACTIONS.txt"))
                    {
                        writer.Write("ADD");
                    }
                    string selectCommandText42 = "SELECT * FROM HOLIDAY_DATE_TABLE WHERE ACTION_TYPE = 'ADD   '";
                    using (SqlDataAdapter adapter42 = new SqlDataAdapter(selectCommandText42, connection))
                    {
                        using (DataTable table42 = new DataTable("HOLIDAY_DATE_TABLE"))
                        {
                            adapter42.Fill(table42);
                            System.Text.StringBuilder commaDelimitedText = new System.Text.StringBuilder();
                            //commaDelimitedText.AppendLine("col1,col2,col3"); // optional if you want column names in first row
                            foreach (DataRow row in table42.Rows)
                            {
                                string value = string.Format("{0}                  {0}         {1}                 {2}", row[1], row[2], row[3]); // how you format is up to you (spaces, tabs, delimiter, etc)                                   
                                commaDelimitedText.AppendLine(value);
                            }
                                    System.IO.File.WriteAllText("C:\\BillingExport\\EXPORTS\\EFTHLDAY.TABLE.TYPE08.TRANSACTIONS.txt",     commaDelimitedText.ToString());                              
                        }
                    }

Update: Implementing some of the suggestions below, I now have the following code

string selectCommandText42 = "SELECT * FROM HOLIDAY_DATE_TABLE WHERE ACTION_TYPE != '-'";
                    using (SqlDataAdapter adapter42 = new SqlDataAdapter(selectCommandText42, connection))
                    {
                        using (DataTable table42 = new DataTable("HOLIDAY_DATE_TABLE"))
                        {
                            adapter42.Fill(table42);
                            System.Text.StringBuilder commaDelimitedText = new System.Text.StringBuilder();
                            //commaDelimitedText.AppendLine("col1,col2,col3"); // optional if you want column names in first row

                            foreach (DataRow row in table42.Rows)
                            {
                                string rowtype = row[4].ToString();
                                using (StreamWriter writer =
                                                                new StreamWriter("C:\\BillingExport\\EXPORTS\\EFTHLDAY.TABLE.TYPE08.TRANSACTIONS.txt")){
                                writer.WriteLine(rowtype);
                            }
                                string value = string.Format("{0}                  {0}         {1}                 {2}", row[1], row[2], row[3]); // how you format is up to you (spaces, tabs, delimiter, etc)                                   
                                commaDelimitedText.AppendLine(value);
                            }
                            System.IO.File.AppendAllText("C:\\BillingExport\\EXPORTS\\EFTHLDAY.TABLE.TYPE08.TRANSACTIONS.txt", commaDelimitedText.ToString());                              
                        }
                    }

This is providing the following output

ADD
00           00      96               25
00           00      76               75
00           00      38               95
00           00      56               55
00           00      59               42
00           00      36               45

It seems to not be applying the CHANGE or DELETE headers, and is also not listing entries in the appropriate order (the ADD entries are at the bottom of the created file now).

A much faster and easier solution would be to use SSIS and export the results of your queries to a CSV file. This will also avoid loading everything in memory before writing it back to the disk — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Oct 31, 2014 at 13:49

Solomon Rutzky · Accepted Answer · 2014-10-31 14:56:21Z

2

Yes, there is a much better way that does not require:

duplicating code
pulling in the entire result set into memory (i.e. the DataTable)
duplicating the entire result set in memory again but in the form of a StringBuilder

The general concept is:

Query for all of the data you want in a single SELECT. You can do this by matching your require of

ACTION_TYPE value that isn't "-"

by using a WHERE clause of WHERE ACTION_TYPE <> '-'
Sort the results so that you get them in the intended order of "ADD", then "CHANGE", then "DELETE". You can do this by adding a simple ORDER BY ACTION_TYPE ASC
Loop through SqlDataReader.Read() and do a "control break" (I'm sure the concept goes by various names) to apply the new header IF the value of the current row for ACTION_TYPE is different from the previous row.
Write each line out to the file as it is processed. Use StreamWriter.WriteLine(String, Object, Object, Object) to output the line while formatting it at the same time.

Please note:

You might need to adjust the index value of the _Reader.GetString call as I have no insight into the schema and am not sure which column is ACTION_TYPE
It is recommended to explicitly SELECT the fields by name, rather than using SELECT *.
By prefixing a string with @ it won't translate escape sequences and hence no need for double-backslashes in file paths :)

The code:

using System.IO;
using System.Data.SqlClient;

string _CurrentActionType = "";

SqlConnection _Connection = new SqlConnection("connection string");
SqlCommand _Command = new SqlCommand(
                            @"SELECT *
                                FROM HOLIDAY_DATE_TABLE
                                WHERE ACTION_TYPE <> '-'
                                ORDER BY ACTION_TYPE ASC;",
                            _Connection
                          );
SqlDataReader _Reader = null;

try
{                         
   _Connection.Open();

   _Reader = _Command.ExecuteReader();

   if (_Reader.HasRows())
   {
      using (StreamWriter writer =
            new StreamWriter(
               @"C:\BillingExport\EXPORTS\EFTHLDAY.TABLE.TYPE08.TRANSACTIONS.txt"))

      {
         while (_Reader.Read())
         {
            // assuming [ACTION_TYPE] field is 5th column; adjust accordingly
            if (_Reader.GetString(4) != _CurrentActionType)
            {
               _CurrentActionType = _Reader.GetString(4);
               writer.WriteLine(_CurrentActionType);
            }

            writer.WriteLine(
               "{0}                  {0}         {1}                 {2}",
               _Reader.GetInt32(1), _Reader.GetInt32(2), _Reader.GetInt32(3));
         }
      }
   }
}
finally
{
   _Reader.Close();
   _Connection.Close();
}

edited Oct 31, 2014 at 14:56

answered Oct 31, 2014 at 14:04

Solomon Rutzky

49.3k11 gold badges141 silver badges185 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Solomon Rutzky Over a year ago

@bob-francis please use comments to post messages. Message was: I have tried implementing this, although it had a lot of errors for me. I've made some adjustments, and now I'm just left with 3 errors, which is the 3 instances of the row[] calls (the name row does not exist in the current context).

Bob Francis Over a year ago

I changed row[1] to _Reader[1], 2 etc. Tested and it works perfectly. Thanks!

Solomon Rutzky Over a year ago

@bob-francis : I have updated the code to swap row[] for _Reader.GetInt32(). Sorry about that. Are these fields really INT values? If not, the GetXXXX() can be easily changed to fit the appropriate data type.

Solomon Rutzky Over a year ago

@BobFrancis : great! I think the syntax I just updated to is preferred. The only other thing I left out was the appropriate using references for the libraries. But it seems that you figured that out :).

Community · Accepted Answer · 2020-06-20 09:12:55Z

0

Every time you call File.WriteAllText in your loop, you're overwriting previous changes:

Creates a new file, write the contents to the file, and then closes the file.

If the target file already exists, it is overwritten.

Use File.AppendAllText instead, as it will not overwrite your file:

Opens a file, appends the specified string to the file, and then closes the file.

Modified code:

File.AppendAllText(@"C:\BillingExport\EXPORTS\EFTHLDAY.TABLE.TYPE08.TRANSACTIONS.txt",
                   commaDelimitedText.ToString());

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Oct 31, 2014 at 13:39

Grant Winney

66.6k13 gold badges120 silver badges167 bronze badges

Comments

Vladimir Korennoy · Accepted Answer · 2014-10-31 13:43:06Z

0

Alternative way is to use DataSet. sql command will be something like

var sqlQuery = "SELECT * FROM HOLIDAY_DATE_TABLE WHERE ACTION_TYPE = 'ADD'; 
                SELECT * FROM HOLIDAY_DATE_TABLE WHERE ACTION_TYPE = 'CHANGE';
                SELECT * FROM HOLIDAY_DATE_TABLE WHERE ACTION_TYPE = 'DELETE';"

and then load it to DataSet and refer via Tables property

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/5e594f9e-98b2-4a47-a87f-a5151e0c7828/loading-multiple-tables-within-dataset-in-single-call-c

answered Oct 31, 2014 at 13:43

Vladimir Korennoy

12 bronze badges

Comments

Stefano Bafaro · Accepted Answer · 2014-10-31 14:22:42Z

You can change the Query ordering by the ACTION_TYPE:

"SELECT * FROM HOLIDAY_DATE_TABLE WHERE ACTION_TYPE IN ('ADD   ', 'DELETE   ', 'Change  ') ORDER BY ACTION_TYPE ASC"

Then, apply this logic in your LOOP:

1) Save the first Type you encounter, and write it:

string rowtype = table42.Rows[0].Item("ACTION_TYPE");   
writer.Write(rowtype);

2) Check when the type change, and write it only when it changes:

 foreach (DataRow row in table42.Rows)
   {
        if (row.Item("ACTION_TYPE").ToString() != rowtype)
        {
            commaDelimitedText.AppendLine(rowtype);
        }   
        string value = string.Format("{0}                  {0}         {1}                 {2}",      row[1], row[2], row[3]); // how you format is up to you (spaces, tabs, delimiter, etc)                                   
        commaDelimitedText.AppendLine(value);
   }

3)

To write your file, use File.AppendAllText:

File.AppendAllText(_YOURFILEHERE_, commaDelimitedText.ToString());

I wrote it without trying, it might work

Collectives™ on Stack Overflow

Writing String To File While Exporting Database Table

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related