Zoltan Zoltan - 12 days ago 8
SQL Question

how to run sql query on all column data and export result to csv -in c#

I made sql queries on an access DB. In datagridview2 i see in the first column the istalled programs in the second how many computers are installed that program.

col1 col2
xxxx 1
yyyy 2
zzzz 3

OleDbCommand command2 = new OleDbCommand();
command2.Connection = connection;
string query = "SELECT Item_1, count(Item_1) FROM (SELECT Item_1 FROM Audit_data where Category_ID = 500) group by Item_1 having (count(*)>0) ";

command2.CommandText = query;

OleDbDataAdapter da1 = new OleDbDataAdapter(command2);

da1.Fill(dt2);
dataGridView2.DataSource = dt2;
dataGridView2.AutoResizeColumns();


The datagridview3 contains only the computers name where the programs are installed when the selection is changed on datagridview2:

string selcell = Convert.ToString(dataGridView2.CurrentCell.Value);



OleDbCommand command3 = new OleDbCommand();
command3.Connection = connection;
string query = "select distinct Fully_Qualified_Domain_Name from Audit_Data, Computer_master where Item_1= '"+selcell+"' and category_id=500 and Audit_Data.computer_id = Computer_master.computer_id ";

command3.CommandText = query;

OleDbDataAdapter da3 = new OleDbDataAdapter(command3);

da3.Fill(dt3);
dataGridView3.DataSource = dt3;
dataGridView3.AutoResizeColumns();


And i would like to run these queries to get all software with all installed computer names. I don't know how to run query on all data on col 1 and export it to csv like this.

xxxx; 1; qwer_pc
yyyy; 2; asdf_pc;
qwer_pc
zzzz; 3; asdf_pc;
qwer_pc;
yxcv_pc


Could anyone help to solve this problem?
Or can i somehow combine the two queries?

Tanks in advance!

Answer

The solution was easier than i thought:

SELECT distinct Item_1,Fully_Qualified_Domain_Name FROM Audit_data,Computer_master where Category_ID = 500      and    Audit_data.computer_id = Computer_master.Computer_id          

The CSV export is the following :

         if (dataGridView4.Rows.Count == 0)
        {
            return;
        }
        StringBuilder sb = new StringBuilder();
        // Column headers
        string columnsHeader = "";
        for (int i = 0; i < dataGridView4.Columns.Count; i++)
        {
            columnsHeader += dataGridView4.Columns[i].Name + ",";

        }
        sb.Append(columnsHeader + Environment.NewLine);
        // Go through each cell in the datagridview
        foreach (DataGridViewRow dataGridView4Row in dataGridView4.Rows)
        {
            foreach (DataGridViewRow dataGridView3Row in dataGridView3.Rows)
            {
                // Make sure it's not an empty row.
                if (!dataGridView4Row.IsNewRow)
                {
                    for (int c = 0; c < dataGridView4Row.Cells.Count; c++)
                    {
                        sb.Append(dataGridView4Row.Cells[c].Value + ",");

                    }
                }

                    // Add a new line in the text file.
                    sb.Append(Environment.NewLine);

            }
        }
        // Load up the save file dialog with the default option as saving as a .csv file.
        SaveFileDialog sfd = new SaveFileDialog();
        sfd.Filter = "CSV files (*.csv)|*.csv";
        if (sfd.ShowDialog() == System.Windows.Forms.DialogResult.OK)
        {
            // If they've selected a save location...
            using (System.IO.StreamWriter sw = new System.IO.StreamWriter(sfd.FileName, false))
            {
                // Write the stringbuilder text to the the file.
                sw.WriteLine(sb.ToString());
            }
        }
        // Confirm to the user it has been completed.
        MessageBox.Show("CSV file saved.");