How to efficiently write to file from SQL datareader in c#?

Question

I have a remote sql connection in C# that needs to execute a query and save its results to the users's local hard disk. There is a fairly large amount of data this thing can return, so need to think of an efficient way of storing it. I've read before that first putting the whole result into memory and then writing it is not a good idea, so if someone could help, would be great!

I am currently storing the sql result data into a DataTable, although I am thinking it could be better doing something in while(myReader.Read(){...} Below is the code that gets the results:

          DataTable t = new DataTable();
            string myQuery = QueryLoader.ReadQueryFromFileWithBdateEdate(@"Resources\qrs\qryssysblo.q", newdate, newdate);
            using (SqlDataAdapter a = new SqlDataAdapter(myQuery, sqlconn.myConnection))
            {
                a.Fill(t);
            }

            var result = string.Empty;
    for(int i = 0; i < t.Rows.Count; i++)
    {
        for (int j = 0; j < t.Columns.Count; j++)
        {
            result += t.Rows[i][j] + ",";
        }


        result += "\r\n";
    }

So now I have this huge result string. And I have the datatable. There has to be a much better way of doing it?

Thanks.

Possible duplicate of stackoverflow.com/questions/2244655/… — Dennis Traub
– Dennis Traub, Commented Jan 29, 2012 at 18:21
Are you just writing to an unformatted flat file, or would it be better to put the data into columns such as a .csv spreadsheet? — DOK
– DOK, Commented Jan 29, 2012 at 18:23

Anders Abel · Accepted Answer · 2014-07-09 18:51:36Z

23

You are on the right track yourself. Use a loop with while(myReader.Read(){...} and write each record to the text file inside the loop. The .NET framework and operating system will take care of flushing the buffers to disk in an efficient way.

using(SqlConnection conn = new SqlConnection(connectionString))
using(SqlCommand cmd = conn.CreateCommand())
{
  conn.Open();
  cmd.CommandText = QueryLoader.ReadQueryFromFileWithBdateEdate(
    @"Resources\qrs\qryssysblo.q", newdate, newdate);

  using(SqlDataReader reader = cmd.ExecuteReader())
  using(StreamWriter writer = new StreamWriter("c:\temp\file.txt"))
  {
    while(reader.Read())
    {
      // Using Name and Phone as example columns.
      writer.WriteLine("Name: {0}, Phone : {1}", 
        reader["Name"], reader["Phone"]);
    }
  }
}

edited Jul 9, 2014 at 18:51

answered Jan 29, 2012 at 18:20

Anders Abel

69.6k18 gold badges154 silver badges221 bronze badges

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

4 Comments

Sam Over a year ago

Thanks. which writing method should I use? streamwriter? IO? an example would be very helpful. thanks

Anders Abel Over a year ago

Use a StreamWriter if you want to write to a file. See my update with an example.

Sam Over a year ago

Thanks. For all rows I am using:

StringBuilder row = new StringBuilder();                     for(int i = 0; i < myReader.FieldCount; i++){                        row.Append(myReader[i]);                         if(i!= myReader.FieldCount -1 )  row.Append("\t");                     }                     writer.WriteLine(row);

StevenWhite Over a year ago

You are missing a closing parentheses on line 9. Tried to edit, but it's less than 6 characters.

Rob Sedgwick · Accepted Answer · 2016-11-24 14:45:05Z

7

I came up with this, it's a better CSV writer than the other answers:

public static class DataReaderExtension
{
    public static void ToCsv(this IDataReader dataReader, string fileName, bool includeHeaderAsFirstRow)
    {

        const string Separator = ",";

        StreamWriter streamWriter = new StreamWriter(fileName);

        StringBuilder sb = null;

        if (includeHeaderAsFirstRow)
        {
            sb = new StringBuilder();
            for (int index = 0; index < dataReader.FieldCount; index++)
            {
                if (dataReader.GetName(index) != null)
                    sb.Append(dataReader.GetName(index));

                if (index < dataReader.FieldCount - 1)
                    sb.Append(Separator);
            }
            streamWriter.WriteLine(sb.ToString());
        }

        while (dataReader.Read())
        {
            sb = new StringBuilder();
            for (int index = 0; index < dataReader.FieldCount; index++)
            {
                if (!dataReader.IsDBNull(index))
                {
                    string value = dataReader.GetValue(index).ToString();
                    if (dataReader.GetFieldType(index) == typeof(String))
                    {
                        if (value.IndexOf("\"") >= 0)
                            value = value.Replace("\"", "\"\"");

                        if (value.IndexOf(Separator) >= 0)
                            value = "\"" + value + "\"";
                    }
                    sb.Append(value);
                }

                if (index < dataReader.FieldCount - 1)
                    sb.Append(Separator);
            }

            if (!dataReader.IsDBNull(dataReader.FieldCount - 1))
                sb.Append(dataReader.GetValue(dataReader.FieldCount - 1).ToString().Replace(Separator, " "));

            streamWriter.WriteLine(sb.ToString());
        }
        dataReader.Close();
        streamWriter.Close();
    }
}

usage: mydataReader.ToCsv("myfile.csv", true)

edited Nov 24, 2016 at 14:45

answered Sep 26, 2014 at 14:03

Rob Sedgwick

4,5646 gold badges60 silver badges93 bronze badges

2 Comments

vapcguy Over a year ago

You don't want the - 1 on the for (int index = 0; index < dataReader.FieldCount - 1; index++) loop. It has to run while less than the count. If the count was 1 item, it just ends if the - 1 is there, since 0, the index, is not < 0.

laughsloudly Over a year ago

Rob, I really appreciated this implementation using a generic export of all columns and rows. However, I end up with a double-export of the last field in the list due to the following lines: if (!dataReader.IsDBNull(dataReader.FieldCount - 1)) sb.Append(dataReader.GetValue(dataReader.FieldCount - 1).ToString().Replace(Separator, " ")); Can you help me understand the purpose of these lines? Otherwise, I am just going to leave them out of my own code...

RWC · Accepted Answer · 2015-12-04 11:57:05Z

Rob Sedgwick answer is more like it, but can be improved and simplified. This is how I did it:

string separator = ";";
string fieldDelimiter = "";
bool useHeaders = true;

string connectionString = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx";

using (SqlConnection conn = new SqlConnection(connectionString))
{
     using (SqlCommand cmd = conn.CreateCommand())
     {
          conn.Open();
          string query = @"SELECT whatever";

          cmd.CommandText = query;

          using (SqlDataReader reader = cmd.ExecuteReader())
          {
                if (!reader.Read())
                {
                     return;
                }

                List<string> columnNames = GetColumnNames(reader);

                // Write headers if required
                if (useHeaders)
                {
                     first = true;
                     foreach (string columnName in columnNames)
                     {
                          response.Write(first ? string.Empty : separator);
                          line = string.Format("{0}{1}{2}", fieldDelimiter, columnName, fieldDelimiter);
                          response.Write(line);
                          first = false;
                     }

                     response.Write("\n");
                }

                // Write all records
                do
                {
                     first = true;
                     foreach (string columnName in columnNames)
                     {
                          response.Write(first ? string.Empty : separator);
                          string value = reader[columnName] == null ? string.Empty : reader[columnName].ToString();
                          line = string.Format("{0}{1}{2}", fieldDelimiter, value, fieldDelimiter);
                          response.Write(line);
                          first = false;
                     }

                     response.Write("\n");
                }
                while (reader.Read());
          }
     }
}

And you need to have a function GetColumnNames:

List<string> GetColumnNames(IDataReader reader)
{
    List<string> columnNames = new List<string>();
    for (int i = 0; i < reader.FieldCount; i++)
    {
         columnNames.Add(reader.GetName(i));
    }

    return columnNames;
}

user596075 · Accepted Answer · 2012-01-29 18:23:12Z

I agree that your best bet here would be to use a SqlDataReader. Something like this:

StreamWriter YourWriter = new StreamWriter(@"c:\testfile.txt");
SqlCommand YourCommand = new SqlCommand();
SqlConnection YourConnection = new SqlConnection(YourConnectionString);
YourCommand.Connection = YourConnection;
YourCommand.CommandText = myQuery;

YourConnection.Open();

using (YourConnection)
{
    using (SqlDataReader sdr = YourCommand.ExecuteReader())
        using (YourWriter)
        {
            while (sdr.Read())
                YourWriter.WriteLine(sdr[0].ToString() + sdr[1].ToString() + ",");

        }
}

Mind you, in the while loop, you can write that line to the text file in any format you see fit with the column data from the SqlDataReader.

Nicolas · Accepted Answer · 2016-07-28 10:33:02Z

2

Keeping your original approach, here is a quick win:

Instead of using String as a temporary buffer, use StringBuilder. That will allow you to use the function .append(String) for concatenations, instead of using the operator +=.

The operator += is specially inefficient, so if you place it on a loop and it is repeated (potentially) millions of times, the performance will be affected.

The .append(String) method won't destroy the original object, so it's faster

answered Jul 28, 2016 at 10:33

Nicolas

1,5142 gold badges17 silver badges30 bronze badges

Comments

Marijn · Accepted Answer · 2015-12-31 13:42:41Z

1

Using the response object without a response.Close() causes at least in some instances the html of the page writing out the data to be written to the file. If you use Response.Close() the connection can be closed prematurely and cause an error producing the file.

It is recommended to use the HttpApplication.CompleteRequest() however this appears to always cause the html to be written to the end of the file.

I have tried the stream in conjunction with the response object and have had success in the development environment. I have not tried it in production yet.

edited Dec 31, 2015 at 13:42

Marijn

10.6k5 gold badges62 silver badges81 bronze badges

answered Dec 31, 2015 at 13:21

David Coulter

111 bronze badge

Comments

Nigje · Accepted Answer · 2017-05-23 15:17:54Z

1

I used .CSV to export data from database by DataReader. in my project i read datareader and create .CSV file manualy. in a loop i read datareader and for every rows i append cell value to result string. for separate columns i use "," and for separate rows i use "\n". finally i saved result string as result.csv.

I suggest this high performance extension. i tested it and quickly export 600,000 rows as .CSV .

edited May 23, 2017 at 15:17

answered May 23, 2017 at 14:13

Nigje

3111 gold badge2 silver badges9 bronze badges

2 Comments

Bhavesh Odedra Over a year ago

While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. - From Review

Nigje Over a year ago

Thank you for your comment. I completed my answer

Luis Bernal · Accepted Answer · 2021-09-24 01:09:52Z

I use:

private void SaveData(string path)
{
    DataTable tblResult = new DataTable();
    using(SqlCommand cm = new SqlCommand("select something", objConnect))
    {
        tblResult.Load(cm.ExecuteLoad());
    }
    if (tblResult != null)
    {
        using(FileStream fs = new FileStream(path, FileMode.Create, FileAccess.Write))
        {
            BinaryFormatter bin = new BinaryFormatter();
            bin.Serialize(fs, tblResult);
        }
    }
}

ease to use, and easy to load, with:

private DataTable LoadData(string path)
{
    DataTable t = new DataTable();
    using(FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read))
    {
        BinaryFormatter bin = new BinaryFormatter();
        t = (DataTable)bin.Deserialize(fs);
    }

    return t;
}

you can use this method also to save a DataSet.

Collectives™ on Stack Overflow

How to efficiently write to file from SQL datareader in c#?

8 Answers 8

4 Comments

2 Comments

Comments

Comments

Comments

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

4 Comments

2 Comments

Comments

Comments

Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related