How to add Column and Row for total in datagrid

Question

I have populated my data in datagrid1. Here is my code.

SqlCommand cmd = new SqlCommand("SELECT ID, Item, ItemPrice, Quantity, FROM tblItem", conn.con);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataTable table = new DataTable();
adapter.Fill(table);
datagrid1.DataSource = table;

My output is:

ID	Item	ItemPrice	Quantity
1	Mango	$10	10
2	Coconut	$30	15
3	Apple	$15	20

I want to add Extra Column and a row in footer for calculation like below:

ID	Item	ItemPrice	Quantity	Total
1	Mango	$10	10	$100
2	Coconut	$30	15	$450
3	Apple	$15	20	$300
			Total:	$850

How can i populate and calculate my data in datagrid like this?

Daniel Wright · Answer

To achieve the desired outcome of adding an extra column for total and a row in the footer for calculations in your datagrid, you can follow these steps: 1. Modify Your SQL Query : You need to modify your SQL query to calculate the total price for each item. You can achieve this by multiplying the ItemPrice with Quantity within your SQL query. Here's an example of how you can modify your SQL query: SqlCommand cmd = new SqlCommand("SELECT ID, Item, ItemPrice, Quantity, (ItemPrice * Quantity) AS TotalPrice FROM tblItem", conn.con); 2. Update DataGrid Column Configuration : After updating your SQL query, you need to configure your datagrid to display the new TotalPrice column. You can do this either programmatically or through the designer. Ensure that you add a column for TotalPrice in your DataGrid configuration. 3. Calculate Total in Code Behind : To calculate the total sum of all item prices in the datagrid, you can iterate through the rows of the DataTable after it is populated and sum up the TotalPrice for each row. Here's an example of how you can calculate the total: decimal totalSum = 0; foreach (DataRow row in table.Rows) { totalSum += Convert.ToDecimal(row["TotalPrice"]); } // Assign the total sum to a label or another UI element to display in the footer row 4. Display Total in Footer Row : Finally, you can add a footer row to your datagrid where you display the total sum calculated in the previous step. You can add this row by either programmatically injecting it into the datagrid or by using the designer with a predefined footer template. By following these steps, you can successfully populate and calculate your data in the datagrid with an additional Total column and a footer row displaying the total sum. If you encounter any specific challenges or need further assistance with a particular step, feel free to ask for more guidance.

Mithila Das · Answer

I have Assigned the total sum in the footer row in this way. Is it right? decimal totalSum = 0; foreach (DataRow row in table.Rows) { totalSum += Convert.ToDecimal(row["TotalPrice"]); } // Assign the total sum to a label or another UI element to display in the footer row table.Rows.Add(null, null, null, null, null, "†gvU:", totalSum); datagrid1.DataSource = table;