Fastest way to retrieve a million records and write to a file

What is the most efficient way to retrive a million records from a Sybase database stored proc and write the result to a file via C# 2010?

A sample code snippet / example would be great!!!
2oothAsked:
Who is Participating?
 
wilcoxonConnect With a Mentor Commented:
I don't think there is a really efficient way to do so in C# (or any other high-level language).  If all the data you need is in a single table, I'd suggest using bcp directly.  If you need to run a specific query, I'd suggest running it via sqsh with -mbcp and redirecting to a file.  You can always do a system call from C# to run bcp or sqsh (I assume it supports output redirection but I don't know much C#).
0
 
John ClaesSenior .Net Consultant & Technical AnalistCommented:
using (SqlConnection sqlConnection= new SqlConnection(connectionString))
{
     sqlConnection= ();
     SqlCommand sqlCommand = new SqlCommand("YourProcedureName");
     sqlCommand.CommandType = CommandType.StoredProcedure;
     sqlCommand.Parameters.Add(new SqlParameter("@parameterName", SqlDbType.ParameterType)).Value = ParameterValue;
                using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader())
                {
                    if (sqlDataReader.HasRows == true)
                    {
                  System.IO.FileStream fileStream = new FileStream("c:\\test.txt",FileMode.CreateNew);
                        while (sqlDataReader.Read())
                        {
                      
                            fileStream.Write(sqlDataReader["Field1"]);
                            fileStream.Write(";");
                            fileStream.Write(sqlDataReader["Field2"]);
                            fileStream.Write(";");
                            fileStream.Write(sqlDataReader["Field3"]);
                            fileStream.WriteLine();


                        }
                  fileStream.Close();
                    }
                }
}
0
 
2oothAuthor Commented:
Thank you for your response.

as mentioned I am retrieving data from a sybase database so i cannot use SQL classes. But i am dependent on OLEDB class. Also, i am curious to know how using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader()) will be efficient in retrieving a million rows?
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
John ClaesSenior .Net Consultant & Technical AnalistCommented:
I've not used SyBase myself but I'm sure you can change all the sql objects into Sybase objects so you can work with Sybase

For this hugh set of records I suggest indeed to work upon a other level (DB).

0
 
Aaron ShiloChief Database ArchitectCommented:
hi

you should use BCP out  utill this is the best and fastest way to extract data from sybase.
0
 
wilcoxonCommented:
As 2ooth requested in my request for attention, my recommendation...

3) I would recommend awarding full points to wilcoxon.  wilcoxon suggested using bcp a full 11 hours before ashilo suggested using it.  wilcoxon also suggested sqsh as a way to work around limitations in bcp if needed (original question did not give enough details to determine if bcp was sufficient).
0
All Courses

From novice to tech pro — start learning today.