Query output in datagridview

[rony001]

I am new to c# .I am having trouble to add a datagridview in the below code. can someone tell me how to do it? thanks

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlServerCe;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace _35c
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();

        }


        private void button2_Click(object sender, EventArgs e)
        {

        }


        private static int getTotal(string connectionString)
        {
            int total = 0;

            using (var connection = new SqlCeConnection("Data Source=|DataDirectory|Northwind.sdf;Password=**"))
            {
                connection.Open();

                var command = new SqlCeCommand("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'Total';"
                    , connection);

                foreach (var table in from IDataRecord row in command.ExecuteReader()
                                      select new { name = row.GetValue(row.GetOrdinal("TABLE_NAME")) }
                    )
                {
                    command.CommandText = String.Format("SELECT SUM(Total) AS Total FROM {0}", table.name);  
                    var tableTotal = command.ExecuteScalar();
                    total = total + (tableTotal == DBNull.Value ? 0 : (int)command.ExecuteScalar()); 
                }
                connection.Close();
            }
            return total;

        }


    }
} 

[djjeavons]

Hi

What data are you trying to show in a DataGridView. Your code only shows how you are getting a Total from a table?

[rony001]

I have tables with similer structure and column name in database. when i run the code it does not give me any error.

table1

col1 | total
-----|-------
A       | 10
B       | 20

table2
col1 | total
-----|-------
C       | 15
D       | 10

I am trying to get the following output 
table name | Total 
---------- |-------
table1           | 30
table2           | 25

[djjeavons]

Hi

One way I see of doing this based on your existing code is to create a class to store the TableName and the Total values. For example:

public class TableTotal
{
    public string TableName { get; set; }
    public int Total { get; set; }    
}

Then, when getting your totals you already know the Table Name so you would add a new instance of this class to a List of TableTotal with the values you have collected. You can then bind this list to the DataGridView. The following is just an example of how you would do this, you will need to modify it to suit your actual needs by adding the table.Name and Total within your For Loop to a new TableTotal and adding this to the list:

List<TableTotal> tableTotals = new List<TableTotal>();

for (int i = 0; i < 10; i++)
{
    tableTotals.Add(new TableTotal {TableName = "Table" + i.ToString(), Total=i});
}

dataGridView1.AutoGenerateColumns = true;
dataGridView1.DataSource = tableTotals;

HTH

[rony001]

Thank you so much for your reply. I am slowly working on it. I am just curious to know is it also possible to get sum of a dynamic tables column?