how to export multiple query?

how to export multiple query?

Postby 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.
paul_huynh
 
Posts: 4
Joined: Fri 16 Dec 2011 19:04

Postby 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();
            }
Pinturiccio
Devart Team
 
Posts: 2046
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for MySQL