how to export multiple query?

[paul_huynh]

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]

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();
                }
            }

1) How can I export multiple query

Where do you want export it?

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();
            }