如何在WinForm文本框中从SQL Server数据库中获取记录(列总和) [英] How to fetch records(sum column values) from SQL Server database in WinForm textBoxes

问题描述

我正在尝试将我的SQL Server数据库中的某些列的总和显示到Winforms文本框中.
我的数据库结果: [ ^ ]

这就是我的winForms的样子: [

I''m trying to display the sum up certain columns from my SQL Server database into Winforms textboxes.
My Database results: [^]

And this is how my winForms looks like: [^]

My C# code:

if (combobox1.SelectedText != null)
{
    string CS = (@"Data )

    SqlConnection con = new SqlConnection(CS);

    SqlCommand sqlCmd = new SqlCommand();
    sqlCmd.Connection = con;
    sqlCmd.CommandText = ("[dbo].[spSalesAnalyse]");
    sqlCmd.CommandType = CommandType.StoredProcedure;

    cbAnalyse.SelectedValue.ToString());
    SqlDataReader myReader;

    try
    {
         con.Open();
         SqlDataAdapter adapter = new SqlDataAdapter(sqlCmd);
       // Save the results in the DT. Call the adapter statement and fill it in the DT
                DataTable dt = new DataTable();
                adapter.Fill(dt);
                //Fill textBoxes with data in DT
                textBox1.Text = dt.Rows[0].Field<string>("North");
                textBox2.Text = dt.Rows[1].Field<string>("East");
                textBox3.Text = dt.Rows[2].Field<string>("West");
                ....
    }
    catch(Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
    finally
    {
        con.Close();
    }
}
else
    MessageBox.Show("Error");

问题:如果我调试到该行adapter.Fill(dt)，我确实会看到记录，但它们没有到达我的文本框.当调试达到文本框级别时，它们为空

我的期望: [

The Problem: If I debugg till this line adapter.Fill(dt), I do see the records BUT they are not getting to my textBoxes. They are empty when debugg reach textBoxes level

My Expectation:[^]

推荐答案

请尝试以下代码，并让我知道任何问题:

看起来您正在尝试一次获取一个文本框值.您可以在SQL中使用GROUP BY子句来获取数据.

Please try below code and let me know for any issues:

Looks like you are trying to get one textbox value at a time. You could use the GROUP BY clause in SQL to fetch your data.

SELECT
  Country,
  Region,
  SUM([Sales1]) AS [First Sales],
  SUM([Sales2]) AS [Sec Sales],
  SUM([Sales3]) AS [Third Sales]
FROM 
  [dbo].[tblSales]
GROUP BY Country, Region

单个查询将返回您的所有结果.您可以将此结果放入DataSet中，然后通过从DataSet中获取值来设置相应文本框的值.

A single query will return all your results. You can take this results in a DataSet and then set the values of the corresponding textboxes by fetching values from the DataSet.

//Use query string as show above
SqlDataAdapter adapter = new SqlDataAdapter(queryString, connection);
DataSet dataset = new DataSet();
adapter.Fill(dataset);

现在您已经在DataSet中有了记录，可以填充文本框了.

Now that you have records inside your DataSet, you can fill your textboxes.

DataRow dataRow = dataset.Tables[0].Select("Country = 'Italy' AND Region = 'North'").FirstOrDefault();
if (dataRow != null) {
    textbox1.Text = dataRow["First Sales"].ToString();
}

Miky，您混合了所有内容！

为什么要强行打开门?使用适当的工具，例如 ReportViewer [分组，排序等. [ ^ ].

Miky, you mixed everything!

Why to force doors wide open? Use proper tools, such as ReportViewer[^]. It enables grouping, sorting, etc.[^].

这篇关于如何在WinForm文本框中从SQL Server数据库中获取记录(列总和)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！