如何循环访问数据表 SELECT 查询以获取具有相同linked_comp_id的所有记录

Question

所以我需要遍历查询，然后生成一个pdf报告，其中包含在2个日期之间找到的所有记录，这些记录链接到公司。我的问题是查询只选择第一条记录，然后 pdf 为链接到公司的记录数量生成相同的记录，这意味着如果找到 4 条记录，pdf 会生成 4 个相同记录的表而不是 4 个不同的记录。

查询

public static DataTable db_reports( int comp_id, DateTime startdate, DateTime enddate)
        {
            DataTable Table = new DataTable();
            using (MySqlConnection connection = new MySqlConnection(helpers.ConnectionString))
            {
                connection.Open();
                using (MySqlCommand Command = connection.CreateCommand())
                {
                    Command.CommandText = @"SELECT id, linked_comp_id, comp_name, client_comp_name, inv_number, inv_date, due_date, ref_number, pastel_ref, status_id, 
                                            invoice_total FROM view_Invoices WHERE inv_date BETWEEN @startdate AND @enddate";
                    Command.Parameters.AddWithValue("@startdate", startdate);
                    Command.Parameters.AddWithValue("@enddate", enddate);
                    Command.Parameters.AddWithValue("@linked_comp_id", comp_id);
                    using (MySqlDataAdapter Adapter = new MySqlDataAdapter(Command))
                    {
                        Adapter.Fill(Table);
                        return Table;
                    }
                }
            }
        }

生成 PDF 的按钮

            start_date = DateTime.TryParse(txt_start_date.Text, out start_date) ? start_date : DateTime.MinValue;
            end_date = DateTime.TryParse(txt_end_date.Text, out end_date) ? end_date : DateTime.MinValue;

            Session["invoice_name"] = "REPORT " + DateTime.Now;
            Response.Write("<script>window.open('view_report.aspx',target='new');</script>");

PDF 代码

DataTable dtR = db_Reports.db_reports(linked_id, startdate, enddate);
                foreach (DataRow r in dtR.Rows)
                {
                    if (dtR.Rows.Count > 0)
                    {
                        table = new PdfPTable(7);
                        table.SetWidths(new float[] { 3f, 3f, 3f, 4f, 4f, 4f, 4f });
                        table.WidthPercentage = 93;
                        //row 1
                        cell = new PdfPCell(new Phrase("Invoice Number", titleFont)); cell.BorderColor = BaseColor.WHITE; table.AddCell(cell);
                        cell = new PdfPCell(new Phrase("Invoice Date", titleFont)); cell.BorderColor = BaseColor.WHITE; table.AddCell(cell);
                        cell = new PdfPCell(new Phrase("Due Date", titleFont)); cell.BorderColor = BaseColor.WHITE; table.AddCell(cell);
                        cell = new PdfPCell(new Phrase("Client Refrence", titleFont)); cell.BorderColor = BaseColor.WHITE; table.AddCell(cell);
                        cell = new PdfPCell(new Phrase("Pastel Reference", titleFont)); cell.BorderColor = BaseColor.WHITE; table.AddCell(cell);
                        cell = new PdfPCell(new Phrase("Status", titleFont)); cell.BorderColor = BaseColor.WHITE; table.AddCell(cell);
                        cell = new PdfPCell(new Phrase("Total", titleFont)); cell.BorderColor = BaseColor.WHITE; table.AddCell(cell);

                        //row 2
                        cell = new PdfPCell(new Phrase(invR.inv_number, subTitleFont)); cell.BorderColor = BaseColor.BLACK; table.AddCell(cell);
                        cell = new PdfPCell(new Phrase(invR.inv_date.ToString("dd/MM/yyyy"), subTitleFont)); cell.BorderColor = BaseColor.BLACK; table.AddCell(cell);
                        cell = new PdfPCell(new Phrase(invR.due_date.ToString("dd/MM/yyyy"), subTitleFont)); cell.BorderColor = BaseColor.BLACK; table.AddCell(cell);
                        cell = new PdfPCell(new Phrase(invR.ref_number, subTitleFont)); cell.BorderColor = BaseColor.BLACK; table.AddCell(cell);
                        cell = new PdfPCell(new Phrase(invR.pastel_ref, subTitleFont)); cell.BorderColor = BaseColor.BLACK; table.AddCell(cell);
                        cell = new PdfPCell(new Phrase(invR.status_id.ToString(), subTitleFont)); cell.BorderColor = BaseColor.BLACK; table.AddCell(cell);
                        cell = new PdfPCell(new Phrase(invR.invoice_total.ToString(), subTitleFont)); cell.BorderColor = BaseColor.BLACK; table.AddCell(cell);
                        doc.Add(table);

                doc.Add(new Paragraph(new Phrase(new Chunk(" ", titleFont))));
                doc.Add(new Paragraph(new Phrase(new Chunk(" ", titleFont))));
                    }
                }

和 pdf 重定向页面

int comp_id = int.TryParse(Session["comp_id"].ToString(), out comp_id) ? comp_id : 0;
                int logged_in_id = int.TryParse(Session["logged_in_id"].ToString(), out logged_in_id) ? logged_in_id : 0;
                int id = int.TryParse(Session["linked_comp_id"].ToString(), out id) ? id : 0;
                iTextSharp.text.Image comp_logo = iTextSharp.text.Image.GetInstance(Server.MapPath(".") + "/img/logo.jpg");
                DateTime date1 = reports.start_date;
                DateTime date2 = reports.end_date;
                string _name = reports.compName;
                MemoryStream memoryStream = itext_docs.pdf_report(id, comp_logo, date1, date2);
                Byte[] buffer = memoryStream.ToArray();
                if (buffer != null)
                {
                    Response.ContentType = "application/pdf";
                    Response.AddHeader("Content-Disposition", "inline; filename=" + Session["invoice_name"].ToString() + ".pdf");
                    Response.AddHeader("Content-length", buffer.Length.ToString());
                    Response.BinaryWrite(buffer);
                }

Answer 1

我想通了。问题是我在 foreach 循环中使用了 invR，而我应该使用 DataRow r .

 foreach (DataRow r in dtR.Rows)
                {
                    if (dtR.Rows.Count > 0)
                    {
                        //row 2
                        cell = new PdfPCell(new Phrase(r["inv_number"].ToString(), subTitleFont)); cell.BorderColor = BaseColor.BLACK; table.AddCell(cell);
                        cell = new PdfPCell(new Phrase(r["inv_date"].ToString(), subTitleFont)); cell.BorderColor = BaseColor.BLACK; table.AddCell(cell);
                        cell = new PdfPCell(new Phrase(r["due_date"].ToString(), subTitleFont)); cell.BorderColor = BaseColor.BLACK; table.AddCell(cell);
                        cell = new PdfPCell(new Phrase(r["ref_number"].ToString(), subTitleFont)); cell.BorderColor = BaseColor.BLACK; table.AddCell(cell);
                        cell = new PdfPCell(new Phrase(r["pastel_ref"].ToString(), subTitleFont)); cell.BorderColor = BaseColor.BLACK; table.AddCell(cell);
                        cell = new PdfPCell(new Phrase(r["status_id"].ToString(), subTitleFont)); cell.BorderColor = BaseColor.BLACK; table.AddCell(cell);
                        cell = new PdfPCell(new Phrase(r["invoice_total"].ToString(), subTitleFont)); cell.BorderColor = BaseColor.BLACK; table.AddCell(cell);
                    }
                }