Getting multiple results from one select query

Question

So I am stuck with my code and have thoroughly tried looking for an answer.

I have 1 select statement that brings about 52806 results.

I put the result of the query I run in variables and then put it into a PDF file i make. After the first result it doesn't work. I want to make it so that it puts the results in my pdf file and then goes to the next result.

if anyone can help me id appreciate it a lot.

Here is my code. Sorry for the bad practice in advance.

    private void CreateBtn_Click(object sender, EventArgs e)
    {

        try
        {
            make_pdf();
        }
        catch (Exception exe )
        {
            MessageBox.Show("failed");
        }

    }

    public static void make_pdf()
        {
        string Contact = "";
        string emailAddress = "";
        string Tel = "";
        string InvoiceDate = "";
        string address = "";
        string Reference = "";
        string AccountNo = "";
        string Debit = "";
        string Credit = "";
        string refnum = "";





        string connetionString = null;
        MySqlConnection cnn;
        connetionString = "server=********;user id=web_support;database=users;password=!w3b_supp0rt~;persistsecurityinfo=True";
        cnn = new MySqlConnection(connetionString);
        try
        {
            cnn.Open();
           // MessageBox.Show("Connection Open ! ");

        }
        catch (Exception ex)
        {
            MessageBox.Show("Can not open connection ! ");
        }


        try
        {
            MySqlDataReader reader = null;
            string selectCmd = "SELECT accounting.code,users.curr_email , users.physicaddr ,accounting.date , accounting.refnum , users.telephone , accounting.debit , accounting.acc_pdf, accounting.credit, accounting.reference, users.contact, accounting.transnum FROM accounting INNER JOIN users ON accounting.code = users.code WHERE(accounting.transtype = 1)";

            MySqlCommand command = new MySqlCommand(selectCmd, cnn);
            reader = command.ExecuteReader();

            while (reader.Read())
            {
                if (reader.HasRows)
                {

                    //get account number
                    AccountNo = reader["code"].ToString();
                    //get emailaddress
                    emailAddress = reader["curr_email"].ToString();

                    //get Contact Name
                    Contact = reader["contact"].ToString();

                    //get telephone number
                    Tel = reader["telephone"].ToString();

                    //Get Date
                    InvoiceDate = reader["date"].ToString();

                    //Get reference
                    Reference = reader["reference"].ToString();

                    //Get Address
                    address = reader["physicaddr"].ToString();

                    //Get Debit
                    Debit = reader["debit"].ToString();

                    //Get Credit
                    Credit = reader["credit"].ToString();

                    //Get Refnum
                    refnum = reader["refnum"].ToString();


                    //  MessageBox.Show(address+" "+Reference+" "+InvoiceDate+" "+emailAddress+" "+AccountNo+" "+Contact);


                    // Make The PDF File
                    Document NewDoc = new Document(iTextSharp.text.PageSize.A4, 0, 0, 0, 0);
                    PdfWriter pdfwri = PdfWriter.GetInstance(NewDoc, new FileStream("text.pdf", FileMode.Create));

                    NewDoc.Open();

                    iTextSharp.text.Image img = iTextSharp.text.Image.GetInstance("intsa_header_small.jpg");
                    // Paragraph par = new Paragraph("Everything is working");

                    //Account List
                    List AccountNolist = new List(List.UNORDERED);
                    AccountNolist.SetListSymbol("");
                    AccountNolist.IndentationLeft = 300f;
                    AccountNolist.Add(new ListItem("AccountNo   " + AccountNo));

                    // AddressList
                    List AddressList = new List(List.UNORDERED);
                    AddressList.SetListSymbol("");
                    AddressList.IndentationLeft = 300f;
                    AddressList.Add(new ListItem("Address:  " + address));
                    #region Emailaddresslist
                    //EmailAddressList
                    List emailAddresslist = new List(List.UNORDERED);
                    emailAddresslist.SetListSymbol("");
                    emailAddresslist.IndentationLeft = 300f;
                    emailAddresslist.Add(new ListItem("Email address:  " + emailAddress));
                    #endregion
                    //ContactList
                    List Contactlist = new List(List.UNORDERED);
                    Contactlist.SetListSymbol("");
                    Contactlist.IndentationLeft = 300f;
                    Contactlist.Add(new ListItem("Contact:  " + Contact));

                    //TelephoneList
                    List Telephonelist = new List(List.UNORDERED);
                    Telephonelist.SetListSymbol("");
                    Telephonelist.IndentationLeft = 300f;
                    Telephonelist.Add(new ListItem("Tel:  " + Tel));

                    // Make a Table


                    PdfPTable General_Table = new PdfPTable(1);
                    General_Table.SpacingBefore = 50f;
                    General_Table.SpacingAfter = 50f;

                    PdfPCell Caption = new PdfPCell(new Phrase("Description"));

                    PdfPCell Body = new PdfPCell(new Phrase("       " + refnum + "    " + Reference + "   Debit: " + Debit + "   Credit: " + Credit));
                    Body.HorizontalAlignment = Element.ALIGN_RIGHT;

                    Caption.Colspan = 0;
                    Caption.HorizontalAlignment = 1;
                    General_Table.AddCell(Caption);
                    General_Table.AddCell(Body);


                    // NewDoc.Add(par);

                    //add Image to pdf
                    NewDoc.Add(img);
                    //add accountNo to pdf
                    NewDoc.Add(AccountNolist);
                    //add Contact to pdf
                    NewDoc.Add(Contactlist);

                    //add emailaddress to pdf
                    NewDoc.Add(emailAddresslist);
                    //add Telephone Number to pdf
                    NewDoc.Add(Telephonelist);

                    //add address to pdf
                    NewDoc.Add(AddressList);

                    NewDoc.Add(General_Table);

                    //save Pdf
                    NewDoc.Close();




                }
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }

You removed the server name from your connection string and forgot the password there. Congratulations! Now hackers only need to find the server and they already have the credentials. You better change that password before it is too late. — Racil Hilan, Jul 12 '16 at 08:50
well if they can connect to it be my guest. the server is running offline though so good luck. — Shaun Garzouzie, Jul 12 '16 at 09:05
The hacker can be from within the organization and they already know where the server is. Unless you're the only one who has access or if everybody else already has full access to the database. — Racil Hilan, Jul 12 '16 at 09:10
only i have access to the server. but thanks for pointing it out. will keep in mind next time i ask a question :) — Shaun Garzouzie, Jul 12 '16 at 09:19

score 0 · Accepted Answer · edited May 23 '17 at 12:09

0

The problem is you are creating the PDF file in the while loop, I don't think you want 52k PDF files, so you should create the PDF file first, then loop through all of the results and paste them into the file.

EDIT: I would suggest adding line to insert/update PDF to database after

NewDoc.Close();

Then deleting the local file (text.pdf) before running loop again.

edited May 23 '17 at 12:09

Community

1
1

answered Jul 12 '16 at 08:44

Hynek Bernard

744
1
11
30

hi hynek thanks for the reply. ill give it a try. but i have to say that i am actually making this to make 52k pdf files wich goes into a database. – Shaun Garzouzie Jul 12 '16 at 08:58
Well then you should leave creation of the pdf file in the while loop, but I noticed you have static name for the pdf file, if you want more than one file, you need to change the name of every file (maybe by autoincrementing value or name of contact) – Hynek Bernard Jul 12 '16 at 09:04
ok i understand what you are saying. do you think i can maybe make a workaround by inserting the pdf file to the database after each result and then go to the next result? since i want to generate the pdf file with the query results-update it into the database then go to the next result. or would that be too much? – Shaun Garzouzie Jul 12 '16 at 09:09
I don't know what are you exactly planning to do with the files in database, but I think it would be better solution to create PDF file only when you need to use it. For example: User needs to sign the form you made in pdf file, you will generate the PDF right before downloading it from website and not even store it in Database. – Hynek Bernard Jul 12 '16 at 10:28
Also the _i want to generate the pdf file with the query results-update it into the database then go to the next result_ would work too, but I don't have experience with it. You just have to add update/insert statement at the end of the loop and then delete the file. Please always note my name with @ so I can see notification when you need answer – Hynek Bernard Jul 12 '16 at 10:34
i basicly want to make invoices in pdf format for invoices that already exist within the database. the future invoices will be handled by the above way that you mentioned. – Shaun Garzouzie Jul 12 '16 at 11:05
Hi, ok so i have everything working. thank you for your help. made 52806 pdf files and updated it into the database in 4.76seconds @hynekbernard – Shaun Garzouzie Jul 13 '16 at 05:29

Getting multiple results from one select query

1 Answers1