ODBC select huge data rows

Dear Experts,
 I'm using ODBC in C# to select data (SQL statement) from a database then to insert these data into CSV file.

How do I improve my code when the SQL result is huge data?

        public Void executeQuery(string query)
        {

            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }

            OdbcCommand dc = new OdbcCommand(query, conn);
            OdbcDataReader reader = dc.ExecuteReader();

            schematab = reader.GetSchemaTable();

            DataTable dt = new DataTable();
            dt.Load(reader);

            reader.Close();

            Insert dt to CSV file

        }

Open in new window



Thanks
Jamil MuammarAsked:
Who is Participating?
 
Máté FarkasDatabase Developer and AdministratorCommented:
        public void executeQuery(string query)
        {
            System.IO.TextWriter CSVFile = new System.IO.File.CreateText("C:\ExportedData.csv");


            if (conn.State == ConnectionState.Closed) conn.Open();

            OdbcCommand dc = new OdbcCommand(query, conn);
            OdbcDataReader reader = dc.ExecuteReader();


            while (reader.Read())
            {
                
                for(int i = 0; i < reader.FieldCount; i++)
                {
                    CSVFile.Write(reader.Item(i).ToString());
                    CSVFile.Write(';'); // delimiter
                }
                CSVFile.WriteLine();
            }

            reader.Close();
        }

Open in new window

0
 
Mark WillsTopic AdvisorCommented:
Maybe tell SQL to output the file ?
1
 
Máté FarkasDatabase Developer and AdministratorCommented:
Your possibilities:
  • Export the result of your select directly to CSV file. Most of SQL Server can do this (MySQL, MSSQL, Oracle etc.)
  • Don't serialize the result of your query into a DataSet/DataTable. Use DataReader to run a loop and export rows one-by-one.
0
 
Jamil MuammarAuthor Commented:
Hello Máté Farkas,

 Can you please show me the second option, how to code it in my attached code ?

Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.