c# wpf sql calculation

Question

i am working with an application(c# wpf, sql) What i want to do with this program is that when i retrieve data from SQL database( Product, Price , qty) and show in datagrid the program should update automatically the column named total

The code I used to retrieve data is shown below

SqlCommand cmd = new SqlCommand("SELECT * From evid", conn);
DataTable dt = new DataTable("dtList");
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
dtg.ItemsSource = dt.DefaultView;
SqlDataAdapter adapt = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adapt.Fill(ds);

conn.Close();

And the code i used to do the calculation is shown bleow:

int a = Convert.ToInt32(dtg.Columns[0]);
int b = Convert.ToInt32(dtg.Columns[1]);
int c = Convert.ToInt32(dtg.Columns[2]);
c = a * b;

I also want that from example when i update the column quantity from 1 to 2 ; the column total should update itself Thanks to everyone

You may want to look into [expression columns](https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/dataset-datatable-dataview/creating-expression-columns) — GarethD, Feb 07 '18 at 08:32
Possible duplicate of [DataGrid Multiplicating Two Columns](https://stackoverflow.com/questions/30059607/datagrid-multiplicating-two-columns) — mjwills, Feb 07 '18 at 09:11

Hex · Answer 1 · 2018-02-07T15:16:33.707

It would probably be easier, rather than attempting to calculate the new columns data on c#, to do it in the SQL query. Firstly this will be less stressful on the system than having to calculate multiple rows of variables, secondly it will be more efficient code-wise.

c = a * b; //This assumed that those two columns are simply variables, which is not how DataTable rows work.

In your sql, according to the information you provided, I recommend something along these lines:

SELECT 
   Product, 
   Price, 
   Quantity, 
   SUM(Price * Quantity) as Calculation
FROM
   evid
GROUP BY
   Product, 
   Price, 
   Quantity

This SQL query would go into your SqlCommand:

SqlCommand cmd = new SqlCommand("--Query above--", conn);

From there you can simply add the calculation as a new datatable column.

UPDATE Apologies everyone, I misread the question. You made me curious about figuring a solution to this problem. I whipped up some code which solves the issue you have and will explain as below:

Firstly in order to make it work I had to change your method of filling the table, using DataGrid.Fill(DataTable) wouldn't work as I had to use a custom expression as a data source.

I handled this all programatically for the sake of easy readability, however this should be easy enough to convert to WPF if you wish.

The code:

SqlConnection sqlConn = new SqlConnection("server = ServerName; " + "Trusted_Connection = yes; " + "database = ReportPool; " + "connection timeout = 120");//Sql connection

            SqlCommand sqlCmd = new SqlCommand(String.Format("SELECT {0} FROM {1}",//SQl command
                "Product, Price, Quantity",
                "ReportPool.dbo.TestTable"
                ), sqlConn);




            DataTable dataTable = new DataTable();//Created a new DataTable

            DataColumn dc = new DataColumn();//Made a new DataColumn to populate above DataTable
            dc.DataType = System.Type.GetType("System.String");//Defined the DataType inside, this can be [[int]] if you want.
            dc.ColumnName = "Product";//Gave it a name (important for the custom expression - can only be one word so use underscores if you need multiple words)

            DataColumn dc2 = new DataColumn();
            dc2.DataType = System.Type.GetType("System.Decimal");
            dc2.ColumnName = "Price";

            DataColumn dc3 = new DataColumn();
            dc3.DataType = System.Type.GetType("System.Decimal");
            dc3.ColumnName = "Quantity";

            DataColumn dc4 = new DataColumn();
            dc4.DataType = System.Type.GetType("System.Decimal");
            dc4.ColumnName = "CalculatedColumn";
            dc4.Expression = "Price * Quantity";//Multiplying the Price and Quantity DataColumns

            dataTable.Columns.Add(dc);//Add them to the DataTable
            dataTable.Columns.Add(dc2);
            dataTable.Columns.Add(dc3);
            dataTable.Columns.Add(dc4);

            dataGridControl.ItemsSource = dataTable.DefaultView;//Set the DataGrid ItemSource to this new generated DataTable

            sqlConn.Open();//Open the SQL connection

            SqlDataReader reader = sqlCmd.ExecuteReader();//Create a SqlDataReader

            while (reader.Read())//For each row that the SQL query returns do
            {
                DataRow dr = dataTable.NewRow();//Create new DataRow to populate the DataTable (which is currently binded to the DataGrid)
                dr[0] = reader[0];//Fill DataTable column 0 current row (Product) with reader[0] (Product from sql)
                dr[1] = reader[1];
                dr[2] = reader[2];

                dataTable.Rows.Add(dr);//Add the new created DataRow to the DataTable
            }

Hopefully you can now solve the issue you've been experiencing, feel free to comment if you need any help interpreting this code or just need more assistance.

Apologies for the late update.

*"I also want that from example when i update the column quantity from 1 to 2 ; the column total should update itself"* — grek40, Feb 07 '18 at 10:12
@user9290729 Additionally the reason why your calculation won't work is because the columns cannot be calculated like that. What you're saying by that is c = (Int)(Column)A * (Int)(Column)B - A column data type is not a numeric value, its a control type - meaning arithmetic won't work on it. It is not simply an integer value and cannot be calculated as such. — Hex, Feb 08 '18 at 09:53
How can i show the datagrid column "CalculatedColumn " to textbox controll — user9290729, Jun 28 '19 at 08:25
@user9290729 That's a totally different question, if you just wanted a textbox to show the calculation you could use the above SQL in my answer and using the SqlCommand do: int/string variableName = Cmd.ExecuteScalar(); TextBox.Text = variableName.ToString(); — Hex, Jun 28 '19 at 14:47

c# wpf sql calculation

1 Answers1