SqlDataReader change column names when exporting to csv

Question

I have a query that gets report data via a SqlDataReader and sends the SqlDataReader to a method that exports the content out to a .CSV file; however, the column names are showing up in the .CSV file the way that they appear in the database which is not ideal.

I do not want to alter the query itself (changing the names to have spaces) because this query is called in another location where it maps to an object and spaces would not work. I would prefer not to create a duplicate query because maintenance could be problematic. I also do not want to modify the method that writes out the .CSV as this is a method that is globally used.

Can I modify the column names after I fill the data reader but before I send it to the .CSV method? If so, how?

If I can't do it this way, could I do it if it was a DataTable instead?

Here is the general flow:

    public static SqlDataReader RunMasterCSV(Search search)
    {
        SqlDataReader reader = null;

        using (Network network = new Network())
        {
            using (SqlCommand cmd = new SqlCommand("dbo.MasterReport"))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                //Parameters here...

                network.FillSqlReader(cmd, ref reader); 

                <-- Ideally would like to find a solution here --> 
                return reader;
            }
        }
    }

    public FileInfo CSVFileWriter(SqlDataReader reader)
    {
        DeleteOldFolders();

        FileInfo file = null;

        if (reader != null)
        {
            using (reader)
            {
                var WriteDirectory = GetExcelOutputDirectory();
                double folderToSaveInto = Math.Ceiling((double)DateTime.Now.Hour / Folder_Age_Limit.TotalHours);
                string uploadFolder = GetExcelOutputDirectory() + "\\" + DateTime.Now.ToString("ddMMyyyy") + "_" + folderToSaveInto.ToString();

                //Add directory for today if one does not exist
                if (!Directory.Exists(uploadFolder))
                    Directory.CreateDirectory(uploadFolder);

                //Generate random GUID fileName
                file = new FileInfo(uploadFolder + "\\" + Guid.NewGuid().ToString() + ".csv");
                if (file.Exists)
                    file.Delete();

                using (file.Create()) { /*kill the file stream immediately*/};
                    StringBuilder sb = new StringBuilder();

                    if (reader.Read())
                    {
                        //write the column names
                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            AppendValue(sb, reader.GetName(i), (i == reader.FieldCount - 1));
                        }

                        //write the column names
                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            AppendValue(sb, reader[i] == DBNull.Value ? "" : reader[i].ToString(), (i == reader.FieldCount - 1));
                        }

                        int rowcounter = 1;

                        while (reader.Read())
                        {
                            for (int i = 0; i < reader.FieldCount; i++)
                            {
                                AppendValue(sb, reader[i] == DBNull.Value ? "" : reader[i].ToString(), (i == reader.FieldCount - 1));
                            }

                            rowcounter++;

                            if (rowcounter == MaxRowChunk)
                            {
                                using (var sw = file.AppendText())
                                {
                                    sw.Write(sb.ToString());
                                    sw.Close();
                                    sw.Dispose();
                                }

                                sb = new StringBuilder();
                                rowcounter = 0;
                            }
                        }

                        if (sb.Length > 0)
                        {
                            //write the last bit
                            using (var sw = file.AppendText())
                            {
                                sw.Write(sb.ToString());
                                sw.Close();
                                sw.Dispose();

                                sb = new StringBuilder();
                            }
                        }
                    }
                }
        }

        return file;
    }

Without seeing how you actually use the reader to write the file it's hard to give you a definitive answer, but why not simply change the column names when you export it to the CSV file? — Tim, Apr 03 '15 at 20:04
I've included the code to write the CSV, however, as stated before, I do not want to customize this method just for this one report as it is a globally used method. Thanks — Elaine K, Apr 03 '15 at 20:19
However you could create an overload that takes two parameters, the second one is a delegate to a function that gives back a rewritten name of the column. The previous CSVFileReader call this new method passing null for the delegate so the code used by other will works as if nothing has changed. — Steve, Apr 03 '15 at 20:33
Like this Steve... public FileInfo CSVFileWriter(SqlDataReader reader, delegate nameOfCustomizedMethod) .. ? Where the nameOfCustomizedMethod represents a separate method I can write to handle the output of the columns? — Elaine K, Apr 03 '15 at 20:43
Yes, exactly. The trick is to check if this delegate is null inside the revised CSVFileWriter. If it is null the do as before (leaving other user of the method happy) if is not null call it and return the renamed column — Steve, Apr 03 '15 at 20:44
Okay, if you're able to, would you mind making your suggestion into an answer so I can accept it? Thanks. — Elaine K, Apr 03 '15 at 20:47

score 1 · Accepted Answer · answered Apr 03 '15 at 20:52

I would try a refactoring of your CSVFileWriter.

First you should add a delegate declaration

public delegate string onColumnRename(string);

Then create an overload of your CSVFileWriter where you pass the delegate together with the reader

public FileInfo CSVFileWriter(SqlDataReader reader, onColumnRename renamer)
{
     // Move here all the code of the old CSVFileWriter
     .....
}

Move the code of the previous CSVFileWriter to the new method and, from the old one call the new one

public FileInfo CSVFileWriter(SqlDataReader reader)
{
    // Pass null for the delegate to the new version of CSVFileWriter....
    return this.CSVFileWriter(reader, null)
}

This will keep existing clients of the old method happy. For them nothing has changed.....

Inside the new version of CSVFileWriter you change the code that prepare the column names

for (int i = 0; i < reader.FieldCount; i++)
{
    string colName = (renamer != null ? renamer(reader.GetName(i)) 
                                      : reader.GetName(i))
    AppendValue(sb, colName, (i == reader.FieldCount - 1));
}

Now it is just a matter to create the renamer function that translates your column names

private string myColumnRenamer(string columnName)
{
    if(columnName == "yourNameWithoutSpaces")
        return "your Name with Spaces";
    else 
        return text;
}

This could be optimized with a static dictionary to remove the list of ifs

At this point your could call the new CSVFileWriter passing your function

FileInfo fi = CSVFileWrite(reader, myColumnRenamer);

You mentioned using a static dictionary instead of if's, would a switch statement also be a good option or is the static dictionary the best option performance wise? — Elaine K, Apr 06 '15 at 12:21
I would prefer a static dictionary just for the concise code that would be required to execute the mapping, but as you can [read from this answer](http://stackoverflow.com/questions/11617091/in-a-switch-vs-dictionary-for-a-value-of-func-which-is-faster-and-why) a dictionary is probably better also for performances — Steve, Apr 06 '15 at 13:08

SqlDataReader change column names when exporting to csv

1 Answers1