You are here:

C#/sum of dynamic table column

Advertisement


Question
QUESTION: Please help me with this.
I want to list all dynamic tables in server compact 3.5 database and total of a specific column (I have a column name "total" in all tables).the code does not gives me any error.is there anyway to get the sum of the column ítotalí from all tables and place next to corresponding table?   
thanks  

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

         using (var connection = new SqlCeConnection(connectionString))
         {
         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;
       }

ANSWER: Hi there,

The code you sent already answers your question, or I did not get it well. In which form do you want it exactly?

---------- FOLLOW-UP ----------

QUESTION: Thank you so much for your reply
I have added a datagridview in the same form and I want to display like this
tables | total
---------------
table1 | total
table2 | total

what I wanted to know is it possible to get sum of dynamic columns ?

Answer
Hi there,

I slightly modified your code according to your needs. Here is it.


private static void FillTable(string connectionString, DataGridView dgv)
      {
        int total = 0;

        using (var connection = new SqlCeConnection(connectionString))
        {
        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());
      dgv.Rows.Add(table.name, total);
        }
        connection.Close();
        }
      }


Hope that helps,

Murat

C#

All Answers


Answers by Expert:


Ask Experts

Volunteer


Murat Mehmet

Expertise

I can help with questions about desktop and web programming in C#, including SOAP, XML, database managing, custom controls, security etc.

Experience

I have been developing web and especially desktop applications in C# and VB.Net for almost 5 years. My programming life has begun with VB6 long time ago, so its about 8 years that I am in this business.

Organizations
Was in R & D for 2 years in a popular Turkish technology website: cyber-warrior.com

Education/Credentials
2011 Computer Engineering graduation in University of Trakia in Turkey.

©2016 About.com. All rights reserved.