I want to export pdf file from my desktop application. I tried following code snap

        private void button1_Click(object sender, EventArgs e)
        {
            Document doc = new Document();
            PdfWriter.GetInstance(doc, new FileStream("myPdf.pdf",FileMode.Create));
            doc.Open();
            SqlConnection con = new SqlConnection(connectionString);
            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }

            SqlCommand cmd = new SqlCommand("SELECT b.date,a.TotalIncome,b.TotalExpense FROM (SELECT c.date, sum(c.amout) as TotalIncome FROM (SELECT name FROM category_tbl where type = 'Income') a, transactions c where c.type = a.name group by c.date) a, (SELECT c.date, sum(c.amout) as TotalExpense FROM (SELECT name FROM category_tbl where type = 'Expense') a, transactions c where c.type = a.name group by c.date) b", con);
            SqlDataReader dr = cmd.ExecuteReader();


            if (dr.Read())
            {
                Paragraph p1 = new Paragraph(dr.GetValue(0).ToString());
                Paragraph p2 = new Paragraph(dr.GetValue(1).ToString());
                Paragraph p3 = new Paragraph(dr.GetValue(2).ToString());
                doc.Add(p1);
                doc.Add(p2);
                doc.Add(p3);
                doc.Close();
                MessageBox.Show("PDF create");
            }



        }

Then I got the following output. Here I got only 1st row in SQL output.

5/16/2022 12:00:00 AM
12000
120

But I need to get all results in the table which are come from the SQL query, like this

So, I want to know how to update the code for getting my achievement

Thank you!!!

CodePudding user response：

Your code executes reader.read() only once which means you will process only first row. Try to use it this way

while(dr.Read())
{
   Paragraph p1 = new Paragraph(dr.GetValue(0).ToString());
   Paragraph p2 = new Paragraph(dr.GetValue(1).ToString());
   Paragraph p3 = new Paragraph(dr.GetValue(2).ToString());
   doc.Add(p1);
   doc.Add(p2);
   doc.Add(p3);
}
doc.Close();
MessageBox.Show("PDF create");

Also consider adding using statement for your sql connection, or add con.Close() at the end.