Solved

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

Posted on 2011-02-17
8
911 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
8 Comments
 
LVL 10

Expert Comment

by:John Claes
Comment Utility
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
Comment Utility
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
Comment Utility
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 10

Expert Comment

by:John Claes
Comment Utility
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
Comment Utility
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
Comment Utility
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
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.

743 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now