Building SQL with foreach

Question

I have this code:

private void button1_Click(object sender, EventArgs e)
{
    foreach(Int_String partner in partneri)
    {
        double danaBezProdaje = (DateTime.Today - Komercijalno.Partner.PoslednjaKupovina(partner._int)).TotalDays;
        if (danaBezProdaje > 31 && danaBezProdaje < 1100)
        {
            NeaktivniPartner np = new NeaktivniPartner();
            np.ppid = partner._int;
            np.naziv = partner._string;
            np.danaBezKupovine = danaBezProdaje;
            neaktivniPartneri.Add(np);
        }
    }
    dataGridView1.DataSource = M.List.ConvertToDataTable(neaktivniPartneri);
}

So in here i have List<Int_String> partneri which contains 5k rows. For each of it i run Komercijalno.Partner.PoslednjaKupovina(partner._int) which contains SQL statement which looks like this:

public static DateTime PoslednjaKupovina(int ppid)        
{
    using (FbConnection con = new FbConnection(M.Baza.connectionKomercijalno2018))
    {
        con.Open();
        using (FbCommand cmd = new FbCommand("SELECT DATUM FROM DOKUMENT WHERE PPID = @PPID ORDER BY DATUM DESC", con))
        {
            cmd.Parameters.AddWithValue("@PPID", ppid);

            FbDataReader dr = cmd.ExecuteReader();

            if (dr.Read())
                return Convert.ToDateTime(dr[0]);
            else
                return new DateTime(1, 1, 1);
        }
    }
}

So SQL statement is executed 5k times which is way too slow.

So how could i pass my List<Int_String> or just array of ints to sql command so there i execute one time (inside foreach or how else) and return dataAdapter so i execute SQL only once.

DataAdapter returned should look like this:

| ppid     | datum    |

So let's say i have Table PARTNER (from which i populate List partneri with SELECT PPID, NAZIV FROM PARTNER) with this data in it:

| PPID    | NAZIV    |
| 1       | name001  |
| 2       | name002  |
| 3       | name003  |
| 4       | name004  |

And let's say my DOKUMENT table has this data:

| ID      | PPID    | DATE       |
| 1       | 2       | 12.03.2018 |
| 2       | 3       | 12.03.2018 |
| 3       | 2       | 05.03.2018 |
| 4       | 2       | 03.04.2018 |
| 5       | 1       | 26.03.2018 |
| 6       | 4       | 21.02.2018 |
| 7       | 4       | 06.05.2018 |

And output i want is:

| PPID    | DATE       |
| 1       | 26.03.2018 |
| 2       | 03.04.2018 |
| 3       | 12.03.2018 |
| 4       | 06.05.2018 |

Answer 1

Making it as an answer, for there is already very many comments below the question, so it would just be lost in the noise there.

So how could i pass my List or just array of ints to sql command so there i execute one time

That question was discussed here: Filter sql based on C# List instead of a filter table

Depending upon how typical(frequent) is the query and how large is the data there are few ways.

• Global Temporary Tables
• Imitation of GTT using UNION ALL and several select {constants} from RDB$database
• Flattening the list into one long string of special format and using reversed LIKE matching.

PS. For your specific task Mark's answer is definitely much better: you do not have to fetch lot of data from the server only to pass it back in another query. I just wanted to link a question, related to a part of this one, that was already answered.

Answer 2

You can replace all your logic with a single query that does:

select d.ppid, max(d.datum)
from partner p
inner join dokument d
    on d.ppid = p.ppid
group by d.ppid

You may need to add a where-clause with additional conditions to select which partners you want to have.