How to export/download table from mysql database using C#?

Question

I want to export a table from a mysql database to a txt or csv file like the way you can in phpmyadmin . I currently use the code below but after the program has been running for a few hours it will throw an "tried to read past the stream" error.

MySqlConnection connection = new MySqlConnection(MyConString);
            MySqlCommand command = connection.CreateCommand();
            connection.Open();
            command.CommandText = "SELECT * FROM mytable";
            MySqlDataReader result = command.ExecuteReader();
                if (result != null)
                    while (result.Read())
                    {
                        string thisrow = "";
                        for (int i = 0; i < result.FieldCount; i++)
                            thisrow += result.GetValue(i).ToString() + ",";
                        pass = Regex.Replace(thisrow, @"\W*", "");
                        if (!hshTable.ContainsKey(pass)) hshTable.Add(pass, pass);
                    }
            connection.Close();

is there a command like myquerystring = "LOAD DATA LOCAL INFILE 'C:/mysqltable.txt' INTO TABLE mytable FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'";

that instead of loading a file to the database it downloads the table instead?

ok I tried

MySqlConnection connection = new MySqlConnection(MyConString);
        MySqlCommand command = connection.CreateCommand();
        connection.Open();
             myquerystring = "SELECT * INTO OUTFILE 'E:/mysqldump.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' FROM mytable";
                command.CommandText = myquerystring;
                command.ExecuteNonQuery();

but it keeps giving me a "Access denied for user 'admin'@'myip' (using password: YES)" error where myip is really my ip and not the servers even though the servers IP is specified in MyConString.

Answer 1

You can try this:

SELECT * INTO OUTFILE 't:/mysqldump.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
FROM mytable

And remember that owner of MySql process must have privileges to write in given location.

Answer 2

hi did something similar some years ago using xsl. I recieved an xml from a web service and transformed it into a csv file to be passed to a batch which inserted data into some tables. It's not exactly your situation but dataset provides methods to export to xml. The number of records might be a problem (long time to fill the datatables) and you must add a step (from dataset to xml and then to csv)