For loop query sql database in asp.net c# -

i not sure how use loop or while loop ever suits problem better.
need query database based on selected item in drop down list , field processed ='false'

then code within loop

then update database based on selected item in drop down list , field processed becomes ='true'

my code below, need the loop within document.open , document.close

    protected void generatereport(object sender, eventargs e)     {         datarow dr = getdata("select * onsiteworktx 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' 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);         }                 {              condatabase.close();          }             document.close();              byte[] bytes = memorystream.toarray();             memorystream.close();             response.clear();                   //save 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();             }         }       } 

to update company selected in dropdownlist, need this.

try {          string constring = "data source=(local);initial catalog=fauma;user id=sa;password=p@ssw0rd";      string query = @"update onsiteworktx set processed = 1                        company = @company , processed = 0";      using(sqlconnection condatabase = new sqlconnection(constring))      using(sqlcommand cmddatabase = new sqlcommand(query, condatabase))      {          cmddatabase.parameters.addwithvalue("@company", dropdownlistpdf.text);          condatabase.open();          cmddatabase.executenonquery();      } } catch (exception ex) {     response.write(ex.message); } 

you prepare command using parameterized query instead of string concatenation , use 1 executenonquery update relevant records company. notice passing strings 'true' or 'false' values bit field in sqlserver doesn't work. pass 1 true , 0 false.

concerning execution of code every row returned getdata method, coud refactor code in way

protected void generatereport(object sender, eventargs e) {     datatable dt = getdata("select * onsiteworktx docid = " +                             dropdownlistpdf.selecteditem.value);     foreach(datarow dr in dt.rows)     {         preparedocument(dr);     } } 

where preparedocument code have written , works on single row

private void preparedocument(datarow dr) {     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())     {          ...... code above, datarow passed calling method     } }