Zoltan Zoltan - 1 year ago 83
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);

dataGridView2.DataSource = dt2;

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

dataGridView3.DataSource = dt3;

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;
zzzz; 3; asdf_pc;

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

Tanks in advance!

Answer Source

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)
        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.

        // 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.
        // Confirm to the user it has been completed.
        MessageBox.Show("CSV file saved.");
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download