How to use STRING_AGG in C# winForms?

Question

I tried this code in C# winforms but its not working like MSSQL ,

when i select data and using string_agg in SQL its working , but in C# forms its not working and

show the data on multiple lines

This is the code :

private void BtnSearch_Click(object sender, EventArgs e)
    {
        if (chkCash.Checked == false && chkCovid.Checked == false)
        {
            btnCash.Enabled = false;
            BtnPrint.Enabled = true;
            string sql = @" SELECT distinct a.patient_no as 'File No' , 
   a.Patient_name as 'Patient Name' , 
   b.order_id as 'Order Id' ,
   c.custid as 'Clinic No', 
   c.custname as 'Clinic Name' , 
   e.TestId as 'Test Id',
   string_agg(e.testname, ',') as 'Test',
   b.order_date as 'Order Date',
   b.lab_no as 'Lab No'
   FROM patients a , lab_orders b ,customers c , order_details d , labtests e  
   where a.patient_no = b.patient_no 
   and b.custid = c.custid
   and b.order_id = d.order_id 
   and d.testid = e.testid
   and c.CustId > 1
   and d.TESTID <> 6438
   and cast(b.order_date as time) between '01:00:00' and '23:50:50' ";

            string condition = "";
            string orderby = "";
            orderby += " ORDER BY c.custid";
            string groupby = "";
            groupby += " group by   a.patient_no,a.Patient_name , b.order_id , c.custid , c.custname  , b.order_date , b.lab_no, e.TestId ";


            DateTime fromDate;
            DateTime toDate;

            if (!DateTime.TryParse(dtFromDate.Value.ToString(), out fromDate))
            {
                System.Windows.Forms.MessageBox.Show("Invalid From Date");
            }
            else if (!DateTime.TryParse(dtToDate.Value.ToString(), out toDate))
            {
                System.Windows.Forms.MessageBox.Show("Invalid to Date");
            }
            else
            {
                condition += " and cast(b.order_date as date) between '" + fromDate + "' and '" + toDate + "'";
            }


            DataTable dt = data.fireDatatable(string.Format(sql + condition + groupby + orderby));
            OrdersDataGridView.DataSource = dt;
            OrdersDataGridView.Refresh();
        }

Can I use it in winforms ?

Example : in SQL SERVER when I run the SELECT the output for orders and tests like this :

Test                order_id   
CBC,TSH,LDL           100

In C# when run the above code the output for order multiple lines and not one row for each order in case multiple tests ordered in one order :

Test         order_id   
CBC           100

TSH           100

LDL           100

`STRING_AGG` is a SQL function. It has nothing to do with WinForms. Please clarify what is "not working". — madreflection, Jan 08 '21 at 21:59
@ZiadAdnan you didn't add anything relevant. `STRING_AGG` is a *SQL function*. You can write a SQL query that uses it and retrieve the results. In fact, you already wrote a query with it. So what's the question? If the results aren't what you expected, you need to change the query. The query's results won't change if you execute it from C# or another client tool like SSMS — Panagiotis Kanavos, Jan 08 '21 at 22:20
Again, if you run the same query in SSMS and C#, you get the same results. If you get different results, the query was different. Or the query doesn't do what you think it does. That `GROUP BY` clause contains columns that never appear in the output — Panagiotis Kanavos, Jan 08 '21 at 22:21
@PanagiotisKanavos I used same query also in crystal reports and its working as needed in crystal reports but in C# not working like SSMS and crystal reports ? — Ziad Adnan, Jan 08 '21 at 22:23
please check your code -- are you SURE the code is the same -- looks like you are doing something strange with the group by here. maybe there is a typo somewhere — Hogan, Jan 08 '21 at 22:26
for example, if you added a column in the group by you could see this result. — Hogan, Jan 08 '21 at 22:27
@PanagiotisKanavos , I put first time part of the code to show the idea I updated the question and put the full code with GROUP BY , please check — Ziad Adnan, Jan 08 '21 at 22:28
Assign `string.Format(sql + condition + groupby + orderby)` to a string variable. Show the **exact** value of that variable to us. **Do not guess**. — mjwills, Jan 08 '21 at 22:30
`condition += " and cast(b.order_date as date) between '" + fromDate + "' and '" + toDate + "'";` Do not do this - it will not work on all PCs depending on their date settings. You should pass parameters as parameters. https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection — mjwills, Jan 08 '21 at 22:30
Also once you have shown us the exact query being executed, please copy and paste the _exact_ query to SSMS and run it there. Take a screenshot such that we can see the query _and the results_ and include that in your question as well. — mjwills, Jan 08 '21 at 22:31
@mjwills this is the name of datetime picker control I search data from date to date . — Ziad Adnan, Jan 08 '21 at 22:43
If it is a datetime picker control then you don't need to `dtFromDate.Value.ToString()` and then parse the string. It _already_ has the date - https://learn.microsoft.com/en-us/dotnet/api/system.windows.forms.datetimepicker.value?view=net-5.0 . — mjwills, Jan 08 '21 at 22:44
@mjwills yes you correct i have a problem with computers using different date settings for example arabic date calender , I will change it as you said , I will check the post and what i need to change . — Ziad Adnan, Jan 08 '21 at 22:48

score 0 · Accepted Answer · answered Jan 08 '21 at 22:29

0

Are you sure you want to group by test id ? That would cause each test to not be in the list. Is this the same group by you had in the other system.

answered Jan 08 '21 at 22:29

Hogan

69,564
10
76
117

Yes thats it , thank you I removed test id from group by and used it in SELECT string_agg(e.testid, ',') as 'Test id ' :) – Ziad Adnan Jan 08 '21 at 22:41

How to use STRING_AGG in C# winForms?

1 Answers1