How to export PDF from .Net C# and SQL server windows application

Question

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

date	Income	Expenses
5/16/2022 12:00:00 AM	12000	120
5/28/2022 12:00:00 AM	15000	145
6/02/2022 12:00:00 AM	3200	60
.	.	.
.	.	.

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

Thank you!!!

SlobodanT · Accepted Answer · 2022-06-07 03:58:34Z

3

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.

answered Jun 7, 2022 at 3:58

SlobodanT

4313 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Lochana Liyange Over a year ago

There we can get all thing but line by line. But the first three rows must be in the same line. Do you have an idea to fix it?

SlobodanT Over a year ago

I am sorry I can't help you with that. Find documentation on how to style pdf with what you currently use.I assume it shouldn't be paragraph for everything.

SlobodanT Over a year ago

If you are using iTextSharp take look at PdfTable class.

Collectives™ on Stack Overflow

How to export PDF from .Net C# and SQL server windows application

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related