title: Generate multiple pdf based on result of sql
body: <p>I am generating a pdf with the results of sql query. There may be a possibility that sql returns more than 1 booking_id. What it does now is, it puts the result of all the booking id into one pdf. I want to fetch every booking id and generate its pdf. Basically, 1 pdf per booking_id and zip each pdf(its a requirement). Not sure if iTextSharp can help here.</p>
  
  <pre><code>using System;
  using System.Configuration;
  using System.Data;
  using System.Data.SqlClient;
  using System.IO;
  using System.IO.Compression;
  using iTextSharp.text;
  using iTextSharp.text.pdf;
  
  namespace ConvertPdf
  {
  class Program
  {
  
       private static DataTable GetData(SqlCommand cmd)
      {
          DataTable dt = new DataTable();
          String strConnString = ConfigurationManager.AppSettings["dbConn"];
          SqlConnection con = new SqlConnection(strConnString);
          SqlDataAdapter sda = new SqlDataAdapter();
          cmd.CommandType = CommandType.Text;
          cmd.Connection = con;
          try
          {
              con.Open();
              sda.SelectCommand = cmd;
              sda.Fill(dt);
              return dt;
          }
          catch (Exception ex)
          {
              throw ex;
          }
          finally
          {
              con.Close();
              sda.Dispose();
              con.Dispose();
          }
      }
  
  
  
      static void Main(string[] args)
      {
  
          //Get the data from database into datatable
          string strQuery = "select * from emp_booking where emp_booking_id in (select e.emp_booking_id from emp_booking e, emp_booking_file ef where ef.booking_date&gt;=getdate() and e.emp_booking_id = ef.emp_booking_id)";
          SqlCommand cmd = new SqlCommand(strQuery);
          DataTable dt = GetData(cmd);
          Transpose(dt);
          int i=1;
  
          foreach(DataRow r in dt.Rows)
          {
          Document document = new Document();
  
          PdfWriter writer = PdfWriter.GetInstance(document, new FileStream("M://Bookingsheet/Pdf/Bookingsheet" + i + ".pdf", FileMode.Create));
  
          document.Open();
          iTextSharp.text.Font font5 = iTextSharp.text.FontFactory.GetFont(FontFactory.HELVETICA, 5);
  
          PdfPTable table = new PdfPTable(dt.Columns.Count);
  
  
          PdfPRow row = null;
          float[] widths = new float[] { 4f, 4f, 4f, 4f, 4f };
  
          table.SetWidths(widths);
  
          table.WidthPercentage = 100;
          int iCol = 0;
          string colname = "";
          PdfPCell cell = new PdfPCell(new Phrase("EmpSheet"));
  
          cell.Colspan = dt.Columns.Count;
  
         foreach (DataColumn c in dt.Columns)
          {
  
              table.AddCell(new Phrase(c.ColumnName, font5));
          }
  
  
              if (dt.Rows.Count &gt; 0)
              {
                  table.AddCell(new Phrase(r[0].ToString(), font5));
                  table.AddCell(new Phrase(r[1].ToString(), font5));
                  table.AddCell(new Phrase(r[2].ToString(), font5));
                  table.AddCell(new Phrase(r[3].ToString(), font5));
                  table.AddCell(new Phrase(r[4].ToString(), font5));
              }
  
          document.Add(table);
          document.Close();
          ZipFile.CreateFromDirectory(@"D://BookingSheet/Pdf", @"M://BookingSheetUpload/Zip/Bookingsheet.zip");
          i++;
      }
  }
  }
  }
  </code></pre>