commandtext query in asp.net

Question

I have a month and year variables and I am getting datas with this query.

EntityDataSourcePersonel.CommandText = 
"SELECT COUNT(TeklifTable.TeklifHazirlayan) AS Basari, EmployeeTable.Name, EmployeeTable.Surname, SUM(TeklifTable.TeklifTutar) AS ToplamSatis FROM EmployeeTable JOIN TeklifTable ON TeklifTable.TeklifHazirlayan = EmployeeTable.EmployeeId WHERE MONTH(TeklifTable.TeklifTarih) = "+dtM+" AND YEAR(TeklifTable.TeklifTarih) = " + dtY + " AND TeklifTable.Approved = true GROUP BY EmployeeTable.Name,EmployeeTable.Surname";

This query works fine but I need something more. As you can see I have a where condition and I take datas which have approved as true. I am wondering if there is a way too get all datas without checking it's approved or not. I mean I need both all datas and datas which has approved as true.

I think I couldn't explained my problem. I need something like approved = true datas in a column and all datas include both approved = true and approved = false in another column. @NSNoob — Koray Durudogan, Dec 17 '15 at 07:47

Mr Moose · Accepted Answer · 2015-12-17T12:56:30.420

If you mean you want a tally of Approved and Not Approved, then you could add columns to your SELECT clause like the following;

EntityDataSourcePersonel.CommandText = 
"SELECT COUNT(TeklifTable.TeklifHazirlayan) AS Basari, 
        EmployeeTable.Name, 
        EmployeeTable.Surname, 
        SUM(TeklifTable.TeklifTutar) AS ToplamSatis,
        SUM(CASE WHEN TeklifTable.Approved = true THEN 1 ELSE 0 END) as [TotalApproved], 
        SUM(CASE WHEN TeklifTable.Approved = false THEN 1 ELSE 0 END) AS [TotalUnapproved] 
   FROM EmployeeTable 
   JOIN TeklifTable ON TeklifTable.TeklifHazirlayan = EmployeeTable.EmployeeId
  WHERE MONTH(TeklifTable.TeklifTarih) = "+dtM+" 
    AND YEAR(TeklifTable.TeklifTarih) = " + dtY + " 
GROUP BY EmployeeTable.Name,EmployeeTable.Surname";

Please look at using SqlParameters though as your current command is vunerable to SQL injection (by constructing your statement through appending dtM and dtY values).

I removed 'AND TeklifTable.Approved = true' from your code and it becomes exactly what I need. Thank you very much :) — Koray Durudogan, Dec 17 '15 at 09:29
No problems. I meant to remove that line too...I just forgot. Whoops :) — Mr Moose, Dec 17 '15 at 12:54

commandtext query in asp.net

1 Answers1