executing and returning two queries at same time-CodePudding

I want to display data like this: image

I was fetching to fetch the details from from postgres tables with home controller like this:

        public IActionResult AllTransactionsList(DateTime startDate, DateTime endDate)
    {
        var dataset = new DataSet();
        using var connection = new NpgsqlConnection(connString);
        connection.Open();
        Console.WriteLine(startDate);
        var query = String.Format(@"SELECT accounts.account,accounts.type,DATE(transactions.date),transactions.transactionid,transactions.amount,transactions.note FROM transactions FULL JOIN accounts ON transactions.accountid=accounts.accountid WHERE transactions.date BETWEEN '{0}' AND '{1}' ORDER BY transactions.date;", startDate, endDate);


        string mainQuery = String.Format(@"SELECT sum(amount) FROM transactions INNER JOIN accounts ON transactions.accountid=accounts.accountid WHERE accounts.type='income' AND transactions.date BETWEEN '{0}' AND '{1}'", startDate,endDate);
        using var mainCommand = new NpgsqlCommand(query, connection);
        decimal mainResult = mainCommand.ExecuteNonQuery();

        using (var command = new NpgsqlCommand(query, connection))
        {

            var adapter = new NpgsqlDataAdapter(command);
            adapter.Fill(dataset);
        }

        return View(dataset);

    }

but I need to also display the income/expense that is there at the bottom image

How to fetch data from two queries at the same time and put it in dataset?

i.e., I have two queries(query, mainResult). I will get rows from query and I will get decimal value from main result I need to return both of them in the view.

How can I achieve this? can anyone help me please?

CodePudding user response：

Method 1.

You can splice two sql statements into one sentence.

Method 2.

Create a new dataset. After get mainQueryDataSet and queryDataSet, you can combine it.

CodePudding user response：

In this particular case you can make the first query

SELECT 
  a.account,
  a.type,
  DATE(t.date),
  t.transactionid,
  t.amount,
  t.note,
  SUM(CASE WHEN a.type = 'income' THEN t.amount END) OVER(PARTITION BY a.account) as sumtrans
FROM 
  transactions t 
  INNER JOIN accounts ON t.accountid=a.accountid 
WHERE 
  t.date BETWEEN @f AND @t
ORDER BY t.date

You'll need to make the command aware of the parameters:

adapter.SelectCommand.Parameters.AddWithValue("f", startDate);
adapter.SelectCommand.Parameters.AddWithValue("t", endDate);

See here for more information about passing datetimes to pg: https://www.npgsql.org/doc/basic-usage.html

Never pass data to a database in the manner you have done so. Every SQL is a small program that gets compiled and run. By concatenating user-provided data into an SQL string you're giving your end users the ability to insert their own SQL ; you're basically giving them access to the database. Yes, it's pretty hard to hack a database when it's a date time variable but if you do it for dates this way, I'm sure you'll do it for strings and then you're staring down the barrel of the http://Bobby-tables.com gun. Even if you deem it safe, passing dates as strings to a database is problematic because they have to be parsed back to a date which means C# and PG have to align on a format.suppose C# string'd the date as "09/10/11" - what date is 09/10/11 anyway? 10th sept 2011? 9th Oct 2011? 11th oct 2009? 1909?

C# has dedicated DateTime datatypes, PG has dedicated DateTime datatypes and there is a mechanism for transmitting from c# to pg that doesn't trip via a formatted string; keep your data as a date