title: For loop query sql database in asp.net c#
body: <p>I am not too sure how to use a for loop or a while loop which ever suits my problem better.<br>
  I need to query database based on selected item in drop down list and the field Processed ='False'  </p>
  
  <p>then my code within the loop</p>
  
  <p>then update database based on selected item in drop down list and the field Processed becomes ='True'</p>
  
  <p>my code is as below, I need the the loop to be within document.open and document.close</p>
  
  <pre><code>    protected void GenerateReport(object sender, EventArgs e)
      {
          DataRow dr = GetData("SELECT * FROM OnSiteWorkTx where DocID = " + DropDownListPdf.SelectedItem.Value).Rows[0]; ;
          Document document = new Document(PageSize.A4, 88f, 88f, 10f, 10f);
          Font NormalFont = FontFactory.GetFont("Arial", 12, Font.NORMAL, BaseColor.BLACK);
  
          using (System.IO.MemoryStream memoryStream = new System.IO.MemoryStream())
          {
              PdfWriter writer = PdfWriter.GetInstance(document, memoryStream);
              Phrase phrase = null;
              PdfPCell cell = null;
              PdfPTable table = null;
              BaseColor color = null;
  
              document.Open();
  
              //Header Table
              table = new PdfPTable(2);
              table.TotalWidth = 500f;
              table.LockedWidth = true;
              table.SetWidths(new float[] { 0.6f, 0.3f });
  
  
              cell = ImageCell("~/pic.jpg", 40f, PdfPCell.ALIGN_LEFT);
              table.AddCell(cell);
  
  
  
              phrase = new Phrase(new Chunk("QAF 018/2\n", FontFactory.GetFont("Arial", 8, Font.NORMAL, BaseColor.BLACK)));
              phrase.Add(new Chunk("On Site Work                        " + "Visit " + dr["VisitNumber"] + "   " + dr["PageNumber"], FontFactory.GetFont("Arial", 15, Font.NORMAL, BaseColor.BLACK)));
              // table.AddCell(PhraseCell(new Phrase("On Site Work", FontFactory.GetFont("Arial", 20, Font.UNDERLINE, BaseColor.BLACK)), PdfPCell.ALIGN_LEFT));
              table.AddCell(PhraseCell(phrase, PdfPCell.ALIGN_LEFT));
              cell = PhraseCell(new Phrase(), PdfPCell.ALIGN_RIGHT);
              cell.Colspan = 2;
              cell.PaddingBottom = 13f;
              table.AddCell(cell);
  
  
  
              //Separater Line
              color = new BaseColor(System.Drawing.ColorTranslator.FromHtml("#A9A9A9"));
              DrawLine(writer, 25f, document.Top - 79f, document.PageSize.Width - 25f, document.Top - 79f, color);
              DrawLine(writer, 25f, document.Top - 80f, document.PageSize.Width - 25f, document.Top - 80f, color);
              document.Add(table);
  
  
              table = new PdfPTable(2);
              table.SetWidths(new float[] { 2f, 10f });
              table.TotalWidth = 480f;
              table.LockedWidth = true;
              table.SpacingBefore = 15f;
              table.HorizontalAlignment = Element.ALIGN_RIGHT;
  
  
  
              table.AddCell(PhraseCell(new Phrase("Company: ", FontFactory.GetFont("Arial", 10, BaseColor.BLACK)), PdfPCell.ALIGN_LEFT));
              phrase = new Phrase(new Chunk(dr["Company"] + "          " + "           " + "          " + "           " + "           " + "           " + "           " + "Email: " + dr["Email"], FontFactory.GetFont("Arial", 10, Font.NORMAL, BaseColor.BLACK)));
              table.AddCell(PhraseCell(phrase, PdfPCell.ALIGN_LEFT));
              cell = PhraseCell(new Phrase(), PdfPCell.ALIGN_CENTER);
              cell.Colspan = 7;
              cell.PaddingBottom = 10f;
              table.AddCell(cell);
  
              table.AddCell(PhraseCell(new Phrase("Plant: ", FontFactory.GetFont("Arial", 10, BaseColor.BLACK)), PdfPCell.ALIGN_LEFT));
              phrase = new Phrase(new Chunk(dr["Plant"] + "          " + "           " + "          " + "           " + "           " + "           " + "          " + "Contact Tel: " + dr["ContactTel"], FontFactory.GetFont("Arial", 10, Font.NORMAL, BaseColor.BLACK)));
              table.AddCell(PhraseCell(phrase, PdfPCell.ALIGN_LEFT));
              cell = PhraseCell(new Phrase(), PdfPCell.ALIGN_CENTER);
              cell.Colspan = 2;
              cell.PaddingBottom = 10f;
              table.AddCell(cell);
  
              table.AddCell(PhraseCell(new Phrase("Contact Person: ", FontFactory.GetFont("Arial", 10, BaseColor.BLACK)), PdfPCell.ALIGN_LEFT));
              phrase = new Phrase(new Chunk(dr["ContactPerson"] + "          " + "           " + "          " + "           " + "           " + "           " + "Fax No: " + dr["FaxNo"], FontFactory.GetFont("Arial", 10, Font.NORMAL, BaseColor.BLACK)));
              table.AddCell(PhraseCell(phrase, PdfPCell.ALIGN_LEFT));
              cell = PhraseCell(new Phrase(), PdfPCell.ALIGN_CENTER);
              cell.Colspan = 2;
              cell.PaddingBottom = 10f;
              table.AddCell(cell);
              document.Add(table);
              document.NewPage();
  
              try
         {    string constring = "Data Source=(local);Initial Catalog=FAUMA;User ID=sa;Password=P@ssw0rd";
              string Query = "update OnSiteWorkTx set Processed = 'True' where Company = '" + DropDownListPdf.Text + "'and Processed = 'False'";
              SqlConnection conDatabase = new SqlConnection(constring);
              SqlCommand cmdDatabase = new SqlCommand(Query, conDatabase);
              SqlDataReader reader;
              conDatabase.Open();
              reader = cmdDatabase.ExecuteReader();
  
              while (reader.Read())
              {
              }
  
          }
          catch (Exception ex)
          {
              Response.Write(ex.Message);
          }
          finally
          {
  
              conDatabase.Close();
  
          }
              document.Close();
  
              byte[] bytes = memoryStream.ToArray();
              memoryStream.Close();
              Response.Clear();
  
  
                  //Save as Attachment
  
                  Response.ContentType = "application/pdf";
                  Response.AddHeader("Content-Disposition", "attachment; filename=OnSiteWork.pdf");
                  Response.ContentType = "application/pdf";
                  Response.Buffer = true;
                  Response.Cache.SetCacheability(HttpCacheability.NoCache);
                  Response.BinaryWrite(bytes);
                  Response.End();
                  Response.Close();
              }
          }
  
  
      }
  </code></pre>