how to export multiple query?

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for MySQL
Post Reply
paul_huynh
Posts: 4
Joined: Fri 16 Dec 2011 19:04

how to export multiple query?

Post by paul_huynh » Wed 18 Jan 2012 23:34

Hi All,
I am writing an application in asp.net (C#) with using dotConnect for MySQL. In my application, I allow user query multiple command such as(

> SELECT * FROM myTable1;
> SELECT * FROM myTable2;
> UPDATE myTable3 SEET COLUMNA = 'hi' WHERE COLUMNID = 1;

In order to allow execute in one command, I will use MySQLDataTable class.
My codes look like this:

string strQuery = "SELECT * FROM myTable1;\nSELECT * FROM myTable2;\nUPDATE myTable3 SEET COLUMNA = 'hi' WHERE COLUMNID = 1;

.......

MySqlCommand myComm = new MySqlCommand();
MySqlDataTable myDataTable = new MySqlDataTable();
myDataTable.Connection = OpenMySQLConnection();
myComm.CommandText = strQuery;
(myDataTable.SelectCommand = myComm;
myDataTable.Active = true;
myDataTable.FetchAll = true;
foreach (DataRow myRow in myDataTable.Rows)
{
foreach (DataColumn myCol in myDataTable.Columns)
{
Console.Write("\"" + myRow[myCol] + "\"");
}
Console.WriteLine();
}

........

So, with using dotConnect for MySQL. My question is:

1) Is it possible to have multiple query
1) How can I export multiple query
2) How can I write to different output (like: each query in one output file)

Thanks a lot for your help.

Pinturiccio
Devart Team
Posts: 2138
Joined: Wed 02 Nov 2011 09:44

Post by Pinturiccio » Thu 19 Jan 2012 12:17

MySqlDataTable can store the result of a single query. You also can use only select statements for MySqlDataTable.SelectCommand. Update commands for MySqlDataTable are generated automatically. You can generate them manually but only for a table with corresponding data. If you never fetched data from myTable3, you could not update this data by using MyDataTable. In this case you need to use MySqlCommand.

You can use DataSet and MySqlDataAdapter for filling it:

Code: Select all

            DataSet ds = new DataSet();
            MySqlDataAdapter da = new MySqlDataAdapter();
            da.SelectCommand= new MySqlCommand(strQuery,conn);
            da.Fill(ds);            

            foreach (DataTable DT in ds.Tables)
            {
                Console.WriteLine(DT.TableName + "\n");
                foreach (DataRow myRow in DT.Rows)
                {
                    foreach (DataColumn myCol in DT.Columns)
                    {
                        Console.Write(""" + myRow[myCol] + """);
                    }
                    Console.WriteLine();
                }
            }
paul_huynh wrote:1) How can I export multiple query
Where do you want export it?
paul_huynh wrote:2) How can I write to different output (like: each query in one output file)
There is no function for this. You should do it in your code. For example:

Code: Select all

            DataSet ds = new DataSet();
            MySqlDataAdapter da = new MySqlDataAdapter();
            da.SelectCommand= new MySqlCommand(strQuery,conn);
            da.Fill(ds);

            StreamWriter sw;
            foreach (DataTable DT in ds.Tables)
            {
                sw = new StreamWriter("D:\" + DT.TableName+".txt");
                Console.WriteLine(DT.TableName + "\n");
                foreach (DataRow myRow in DT.Rows)
                {
                    foreach (DataColumn myCol in DT.Columns)
                    {
                        sw.Write(""" + myRow[myCol] + """);
                    }
                    sw.WriteLine();
                }
                sw.Close();
            }

Post Reply