如何在所有列数据上运行SQL查询并将结果导出到CSV -在C#中

x3naxklr 于 5个月前发布在 C#

关注(0)|答案(2)|浏览(79)

我在access数据库上做了sql查询。在datagridview2中，我在第一列中看到了第二列中安装的程序，有多少台计算机安装了该程序。

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

字符串
当在datagridview2上更改选择时，datagridview3仅包含安装程序的计算机名称：

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

型
我想运行这些查询，以获得所有软件与所有安装的计算机名称。我不知道如何运行查询所有数据上的col 1和导出到csv像这样。

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

型
有谁能帮我解决这个问题吗？或者我能把这两个问题合并结合起来吗？

csv

来源：https://stackoverflow.com/questions/40656887/how-to-run-sql-query-on-all-column-data-and-export-result-to-csv-in-c-sharp

2条答案

按热度按时间

jc3wubiy1#

解决办法比我想象的要简单：

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

字符串
CSV导出如下：

string csv = string.Empty;

//Add the Header row for CSV file.
foreach (DataGridViewColumn column in dataGridView4.Columns)
{
    csv += column.HeaderText + ',';
}

//Add new line.
csv += "\r\n";

//Adding the Rows
foreach (DataGridViewRow row in dataGridView4.Rows)
{
    foreach (DataGridViewCell cell in row.Cells)
    {
        //Add the Data rows.
        csv += cell.Value.ToString().Replace(",", ";") + ',';
    }

    //Add new line.
    csv += "\r\n";
}

//Exporting to CSV.
string folderPath = txt_csv_exp_path.Text;
File.WriteAllText(folderPath +txt_exp_file_name.Text +" .csv", csv);

MessageBox.Show("CSV file saved.");

型

赞(0）回复(0）举报 5个月前

flmtquvp2#

await conn.OpenAsync(token).ConfigureAwait(false);

const string Sql = "SELECT * FROM table_name;";

DataTable dataTable = new DataTable();        
using var command = new SqlCommand(Sql, conn);        
using var reader = await command.ExecuteReaderAsync(token);        
dataTable.Load(reader);

var columns = dataTable.Columns.Cast<DataColumn>()
    .Select(c => c.ColumnName);        
var results = dataTable.Rows.Cast<DataRow>()
    .Select(row => columns.ToDictionary(column => column, column => row[column]));        
var head_str = string.Join(";", columns.Values)));        
var result_str = string.Join("\r\n", results.Select(t => string.Join(";", t.Values)));

字符串

赞(0）回复(0）举报 5个月前

我来回答

如何在所有列数据上运行SQL查询并将结果导出到CSV -在C#中

2条答案

相关问题

热门标签

最新问答