使用C＃插入SQL表时如何求和值 [英] How to sum value when using insert to SQL table with C#

问题描述

这项工作，但问题是每次当我退出应用程序并重新启动它从头开始总结，我想继续加起来。





这是我的班级：

This work, but the problem is every time when I exit the application and restart it starts summing from the beginning, and I want to continue adding up.


This is my class:

public class clans

        public string Name{ get; set; }
        public string Craft { get; set; }
        public int Month { get; set; }
        public int Year { get; set; }
        public decimal Price { get; set; }   
        public decimal Total {get; set; }

此代码用于插入SQL表obb：

This code is for insert into SQL Table obb:

cl.Name = txtInsertName.Text;
            cl.Craft = txtInsertCraft.Text;
            cl.Month = Convert.ToInt32(txtInsertMounth.Text);
            cl.Year = Convert.ToInt32(txtInsertYear.Text);
            cl.Price = Convert.ToDecimal(txtInsertPrice.Text);

          cl.Total += cl.Price;

            SqlCommand cmd;

                string query = "insert into obb(Name,Craft,Month,Year,Price,Total) values(@p1,@p2,@p3,@p5,@p6,@p7)";
            cmd = new SqlCommand(query, db);
            cmd.Parameters.AddWithValue("@p1", cl.Name);
            cmd.Parameters.AddWithValue("@p2", cl.Craft);
            cmd.Parameters.AddWithValue("@p3", cl.Month);
            cmd.Parameters.AddWithValue("@p5", cl.Year);
            cmd.Parameters.AddWithValue("@p6", cl.Price);
            cmd.Parameters.AddWithValue("@p7", cl.Total);

            cmd.CommandType = CommandType.Text;
            int i = cmd.ExecuteNonQuery();
            //This line is for datagridview
            string query1 = @" SELECT * FROM obb order by Name ASC, Year ASC";
            clansBindingSource1.DataSource = db.Query(query1, commandType: CommandType.Text);

欢迎提供解决方案..



我尝试过的事情：



我想在插入数据时总计每个名称和年份的总价格到SQL表。

Solutions are welcome..

What I have tried:

I want to sum Price in Total ordered per Name and Year when inserting data to SQL Table.

推荐答案

如果我理解正确，你想计算一个运行总计。一种简单的方法是使用窗口函数。考虑以下示例

If I understand you correctly, you want to calculate a running total. One easy way is to use windowing function. Consider the following example

SELECT Name, 
       Year,
       SUM(Price) OVER (ORDER BY Name, Year)
FROM Obb
ORDER BY Name, Year

对于不同类型的示例，请查看计算SQL Server中的简单运行总计 [ ^ ]



但是，存储结果到单个行不是一个好习惯。当数据发生变化时，您需要始终重新计算所有行。普遍接受的最佳做法是仅在查询时获取总和而不是存储值。

For different kinds of examples, have a look at Calculating simple running totals in SQL Server[^]

However, storing the result onto individual rows is not a good practice. When the data changes you would need to always recalculate all rows. Commonly accepted best practice is only to fetch the sum when queried and not to store values.

这篇关于使用C＃插入SQL表时如何求和值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！