Solved

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

Posted on 2011-02-17
8
928 Views
Last Modified: 2012-06-21
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!!!
0
Comment
Question by:2ooth
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 10

Expert Comment

by:John Claes
ID: 34917878
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
 

Author Comment

by:2ooth
ID: 34917978
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
 
LVL 26

Accepted Solution

by:
wilcoxon earned 500 total points
ID: 34922734
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 10

Expert Comment

by:John Claes
ID: 34924178
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
 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 34932821
hi

you should use BCP out  utill this is the best and fastest way to extract data from sybase.
0
 
LVL 26

Expert Comment

by:wilcoxon
ID: 35369614
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

689 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question