5 Replies - 11860 Views - Last Post: 04 October 2011 - 02:51 PM Rate Topic: -----

#1 WabiSabi   User is offline

  • D.I.C Head

Reputation: 52
  • View blog
  • Posts: 226
  • Joined: 31-December 10

Sum column in a table returned by SQL query

Posted 03 October 2011 - 01:43 PM

I have a forms app, with a SQL query (written to perfection, not by me tho) that returns a table with one certain column of rows that I need to add up and use the value in my program.

I used the tableAdapter, and "edit queries in dataset designer" and I have a function that will fill the table on the form with required data from the SQL query.

The only part that is in my C# code is (names changed so I don't get in trouble)
MyDataTableAdapter.Fill(this.myDataSet1.myData, my_num);


Is there any way I can read from the table that is returned so I can sum up a column, or better yet, have it give me a sum of one particular column (would it have to be in the query?) and return that value?

There is another method, that I never call, in that same tableAdapter, called Get(). Should I use that? And... how?

Edit: I will leave the original question although I think I have found the road to my answer... the "Get" method returns a data table...

Thanks for any help though! I may be wrong...

This post has been edited by WabiSabi: 03 October 2011 - 01:51 PM


Is This A Good Question/Topic? 0
  • +

Replies To: Sum column in a table returned by SQL query

#2 Tanira   User is offline

  • D.I.C Head

Reputation: 10
  • View blog
  • Posts: 102
  • Joined: 30-May 09

Re: Sum column in a table returned by SQL query

Posted 03 October 2011 - 02:21 PM

You know SQL has a SUM() function?
http://www.w3schools...ql_func_sum.asp

Here's some code to do the counting in c#.

int total = 0;
foreach(DataRow row in MyDataTableAdapter.Rows)
{
    total += row["row_name_to_count"];
}



I'm not sure if it returns a string automatically. If it does then you will need to use a
Int32.Parse(string);


Was This Post Helpful? 1
  • +
  • -

#3 eclipsed4utoo   User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1535
  • View blog
  • Posts: 5,972
  • Joined: 21-March 08

Re: Sum column in a table returned by SQL query

Posted 03 October 2011 - 02:32 PM

Personally, this should be done in the SQL query rather than in your code. SQL has a function to do it. No reason not to use it.
Was This Post Helpful? 1
  • +
  • -

#4 Curtis Rutland   User is offline

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 5104
  • View blog
  • Posts: 9,283
  • Joined: 08-June 10

Re: Sum column in a table returned by SQL query

Posted 04 October 2011 - 06:46 AM

I'll jump in and agree. Usually, if an aggregate function can be performed on the database server, it's best to perform it there, rather than selecting all the data and aggregating in memory. Two advantages: it's much lighter on the client, and the database server can usually optimize its query to be much quicker than selecting all the records and allowing you to aggregate them.
Was This Post Helpful? 2
  • +
  • -

#5 WabiSabi   User is offline

  • D.I.C Head

Reputation: 52
  • View blog
  • Posts: 226
  • Joined: 31-December 10

Re: Sum column in a table returned by SQL query

Posted 04 October 2011 - 02:43 PM

I'm sure you're correct. However, 1. I hate SQL and am not very good at it... so it would take half a day to figure out the exact wording of each query. and 2. I need to use some of the other values returned by the one query I have that works correctly, for other things in the program... so since I already know how to get the table (it's not a huge one anyway) I'll do it that way for this project. Perhaps in the future I will do it your way though.

This site is the best place for learning all you never wanted to know about programming... and everything you wanted to know, too.
:D
Was This Post Helpful? 0
  • +
  • -

#6 Curtis Rutland   User is offline

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 5104
  • View blog
  • Posts: 9,283
  • Joined: 08-June 10

Re: Sum column in a table returned by SQL query

Posted 04 October 2011 - 02:51 PM

I suggest you learn an ORM like LINQ to SQL, or Entity Framework. Using these, you can query against data in a database using syntax that's more like sorting and filtering lists. The SQL code is generated and executed for you, without your intervention. So you can do things like .Count() and (if the query hasn't been enumerated yet), it'll create the SQL for you and perform the query itself. Best of both worlds.

That's not to discourage you from learning SQL. You don't have to be an expert, but it certainly pays to know in this industry. Business applications are huge business, and most of them need to talk to databases. Learn the language that databases speak!
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1