使用foreach构建SQL [英] Building SQL with foreach

问题描述

我有此代码:

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);
}

所以在这里我有List<Int_String> partneri，其中包含5k行.对于每个命令，我运行Komercijalno.Partner.PoslednjaKupovina(partner._int)，其中包含如下所示的SQL语句:

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);
        }
    }
}

因此，SQL语句执行了5k次，这太慢了.

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

所以我该如何将我的List<Int_String>或仅将整数数组传递给sql命令，以便在那里执行一次(在foreach或其他方式中)并返回dataAdapter，所以我仅执行一次SQL.

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应该如下所示:

DataAdapter returned should look like this:

| ppid     | datum    |

因此，假设我有一个表PARTNER(从中用SELECT PPID, NAZIV FROM PARTNER填充列表partneri)，其中包含以下数据:

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  |

假设我的DOKUMENT表包含以下数据:

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 |

我想要的输出是:

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

推荐答案

您可以用执行以下操作的单个查询替换所有逻辑:

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

您可能需要添加带有附加条件的where条款，以选择要拥有的合作伙伴.

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

这篇关于使用foreach构建SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！