Creating SQL Queries with a Loop

Data and Databases
1

I have a piece of code that performs SQL queries within a loop, and it’s running very slowly. Here’s what I have:

private void button1_Click(object sender, EventArgs e)
{
    foreach(Int_String partner in partnerList)
    {
        double daysSinceLastPurchase = (DateTime.Today - Commercial.Partner.LastPurchase(partner._id)).TotalDays;
        if (daysSinceLastPurchase > 31 && daysSinceLastPurchase < 1100)
        {
            InactivePartner inactive = new InactivePartner();
            inactive.id = partner._id;
            inactive.name = partner._name;
            inactive.daysSincePurchase = daysSinceLastPurchase;
            inactivePartners.Add(inactive);
        }
    }
    dataGridView1.DataSource = M.List.ConvertToDataTable(inactivePartners);
}

My List<Int_String> partnerList contains about 5,000 entries, and each partner prompts a call to Commercial.Partner.LastPurchase(partner._id), which includes a SQL command similar to:

public static DateTime LastPurchase(int id)        
{
    using (FbConnection connection = new FbConnection(M.Database.connectionCommercial2018))
    {
        connection.Open();
        using (FbCommand command = new FbCommand("SELECT DATE FROM DOCUMENT WHERE ID = @ID ORDER BY DATE DESC", connection))
        {
            command.Parameters.AddWithValue("@ID", id);

            FbDataReader reader = command.ExecuteReader();

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

This SQL is executed 5,000 times, which is far too inefficient.

How can I efficiently pass my List<Int_String> or an array of integers to a SQL command in a single execution, returning a dataAdapter to minimize the number of SQL calls?

The dataAdapter I want should resemble the format:

| id      | date      |

For context, the PARTNER table from which I retrieve my partnerList has:

| ID      | NAME      |
| 1       | name001   |
| 2       | name002   |
| 3       | name003   |
| 4       | name004   |

And the DOCUMENT table is structured like this:

| ID      | ID       | 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 |

The desired output should be:

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

You could try making the query for all partners at once by using JOIN and GROUP BY in SQL. Pull all latest dates for each id, something like SELECT d.ID, MAX(d.DATE) FROM DOCUMENT d JOIN PARTNER p ON d.ID = p.ID WHERE p.ID IN (...) GROUP BY d.ID. This should reduce redundancy and increase speed.