Query return on C#/mysql

Question

I have in my class "fattura" this

public string sommaFattura(String costo)
    {
MySqlCommand command = new MySqlCommand();
            String sumQuery = "SELECT SUM(`prezzo`) FROM `fatturetemp`";
            command.CommandText = sumQuery;
            command.Connection = conn.getConnection();
            command.Parameters.Add("@prezzo", MySqlDbType.Int32).Value = costo;
            conn.openConnection();
//Need the command for take the result
                conn.closeConnection();
}

What is the command for take the result of Sum query? I want use this command, insert in a variable. Can you correct me?

You code doesn't look like mysql see https://stackoverflow.com/a/17835514/5193536 — nbk, Apr 21 '20 at 22:33

score 1 · Accepted Answer · answered Apr 22 '20 at 00:38

I think the command you want is

command.ExecuteScalar();

However, note, Execute Scalar is intended that the result will return a single row, single column which your query does without regard to your parameter. However your query does not specifically make sense either..

Now your query itself. You have an explicit prezzo which would imply an existing column name and not that of a parameter. It would have to a numeric field for obvious reasons to sum it.

Now you also have an expected string coming in via "costo" parameter to the function call. If your intent is to have the incoming string be representative of a known column of the table, this is not going to work for you. You would have to build the SQL command with that explicit column name, or build doing dynamic-sql, but that would really be too much.

The concern with building a SQL statement with string parameters is you would be open to SQL-injection, especially if web-based. If your incoming string is coming from a controlled source, such as you are presenting a list of columns to a user and they can only pick one such column. Or, you have buttons on a screen asking for a sum of a certain thing, and YOU control that column name you would be in a better condition, but still be cautious passing parameter to build out SQL.

Say for example your table has numeric fields of qtySold and dollarSales. You want either the sum of either of these columns and are passing one of these respective strings into the function such as

var qtySoldAnswer = sommaFattura( "qtySold" );
   or
var dollarSalesAnswer = sommaFattura( "dollarSales" );

Then your function would be CLOSER to...

public string sommaFattura(String costo)
{
   MySqlCommand command = new MySqlCommand();
   command.CommandText = "SELECT SUM(" + costo + ") FROM fatturetemp";
   command.Connection = conn.getConnection();
   conn.openConnection();
   var answer = command.ExecuteScalar();
   conn.closeConnection();

   // I would put a breakpoint in here to see the results.
   // you can then format the answer such as decimal point, etc.
   return answer.ToString();
}

AGAIN, ONLY if YOU have control of the column name you are trying to send in. Someone could do sql-injection and do a lot of damage. I would only do with heavily controlled consideration and explicitly validating the columns you WOULD allow, and if so, set your variable at that point. Don't allow for any more or less than the column name. No special characters, quotes, comments, sql-terminator and new statement...

But hopefully this clarifies what I THINK you are trying to accomplish.

Query return on C#/mysql

1 Answers1