how to create /export PDF report from c# windows forms with sql server data using itextsharp

See more:

C#

SQL

SQL-Server-2008

C#4.0

hi my name is vishal for past 10 days i was breaking my head on how to create PDF report from c# windows forms with sql server data using itextsharp.

I have two tables in sql server 2008.
table name: Technician
Column Name DataType AllowNulls
technician_id(primary key) Int No
technician_dob date Yes
technician_sex nvarchar(10) Yes
row_upd_date datetime Yes
user_id Int Yes

table name:Techniciandetail
Column Name DataType AllowNulls
Technician_first_name nvarchar(50) Yes
Technician_middle_name nvarchar(50) Yes
Technician_last_name nvarchar(50) Yes
row_upd_date datetime Yes
status bit Yes
agn(primary key) Int No
technician_id Int Yes
user_id Int Yes
technician_type Int Yes

Given below is my c# code with sql server 2008 using itextsharp:

<pre lang="c#">
 using System;
using System.Collections.Generic;
using System.Configuration;
using System.Text;
using System.Data;
 using System.IO;
using System.Data.SqlClient;
using System.Windows.Forms;
using iTextSharp.text;
using iTextSharp.text.pdf;
namespace DRRS_CSharp
{
    public partial class frmPDFTechnician : Form
    {
        public frmExcelTechnician()
        {
            InitializeComponent();
        }
        public DataTable GetDataTable()
        {

            DataTable table = new DataTable("Techniciandetail" + "Technician");
            DataColumn datacolumn_ID = new DataColumn("ID", typeof(Int32));
            table.Columns.Add(datacolumn_ID);
            DataColumn dataFname = new DataColumn("First Name", typeof(string));
            table.Columns.Add(dataFname);
            DataColumn dataMname = new DataColumn("Middle Name", typeof(string));
            table.Columns.Add(dataMname);
            DataColumn dataLname = new DataColumn("Last Name", typeof(string));
            table.Columns.Add(dataLname);
            DataColumn DOB = new DataColumn("DOB", typeof(DateTime));
            table.Columns.Add(DOB);
            DataColumn Sex = new DataColumn("Gender", typeof(string));
            table.Columns.Add(Sex);
            DataColumn Type = new DataColumn("Designation", typeof(Int32));
            table.Columns.Add(Type);
            DataRow drow = table.NewRow();
                string connect = "Data Source=NPD-4\\SQLEXPRESS;Initial Catalog=DRRS;Integrated Security=true";
               SqlConnection conn = new SqlConnection(connect);
                    string query = "Select t.technician_id,td.Technician_first_name,td.Technician_middle_name,td.Technician_last_name,t.technician_dob,t.technician_sex,td.technician_type from Techniciandetail td,Technician t where td.technician_id=t.technician_id and td.status=1";
                    SqlCommand cmd = new SqlCommand(query, conn);
                      conn.Open();
                        SqlDataReader rdr = cmd.ExecuteReader();
                            while (rdr.Read())
                            {
                                drow["ID"] = rdr[0].ToString();
                                drow["First Name"] = rdr[1].ToString();
                                drow["Middle Name"] =rdr[2].ToString();
                                drow["Last Name"] =  rdr[3].ToString();
                                drow["DOB"] = rdr[4].ToString();
                                drow["Gender"] = rdr[5].ToString();
                                drow["Designation"] = rdr[6].ToString();
                            }
                            table.AcceptChanges();
                            return table;
                        }
private void btnExport_Click(object sender, EventArgs e)
        {
            Document doc = new Document(PageSize.A4.Rotate());
                PdfWriter.GetInstance(doc, new FileStream("Technician22.pdf", FileMode.Create));
                doc.Open();
                Font font10 = FontFactory.GetFont("Arial", 10, BaseColor.BLACK);
                Paragraph para = new Paragraph("Technician Details in PDF Document");
                DataTable dt = GetDataTable();
                    PdfPTable table = new PdfPTable(dt.Columns.Count);
                    PdfPCell cell = null;
                    cell = new PdfPCell(new Phrase(new Chunk("ID", font10)));
                    table.AddCell(cell);
                    cell = new PdfPCell(new Phrase(new Chunk("First Name", font10)));
                    table.AddCell(cell);
                    cell = new PdfPCell(new Phrase(new Chunk("Middle Name", font10)));
                    table.AddCell(cell);
                    cell = new PdfPCell(new Phrase(new Chunk("Last Name", font10)));
                    table.AddCell(cell);
                    cell = new PdfPCell(new Phrase(new Chunk("DOB", font10)));
                    table.AddCell(cell);
                    cell = new PdfPCell(new Phrase(new Chunk("Gender", font10)));
                    table.AddCell(cell);
                    cell = new PdfPCell(new Phrase(new Chunk("Designation", font10)));
                    table.AddCell(cell);
                    for (int rows = 0; rows < dt.Rows.Count; rows++)
                    {
                        for (int column = 0; column < dt.Columns.Count; column++)
                        {
                            cell = new PdfPCell(new Phrase(new Chunk(dt.Rows[rows][column].ToString(), font10)));
                            table.AddCell(cell);
                        }
                    }
                    table.SpacingBefore = 15f;
                    doc.Add(para);
                    doc.Add(table);
                doc.Close();
            }

Given below is my sql select query:
"Select t.technician_id,td.Technician_first_name,td.Technician_middle_name,td.Technician_last_name,t.technician_dob,t.technician_sex,td.technician_type from Techniciandetail td,Technician t where td.technician_id=t.technician_id and td.status=1";

The above query executes to some extent as i get column names in table in PDF report. But the problem is that i get column names only and below it is not being filled by sql data. I know there is some mistake in public DataTable GetDataTable() function in my coding but i dont know how to fix it?
What i want is when user click btnExport i want datas/values from Technician table-technician_id,technician_dob,technician_sex and values from Techniciandetail table-Technician_first_name,Technician_middle_name,Technician_last_name and technician_type to be present or embedded or binded into appropriate columns in PDF Report.
Can anyone help me please! I have tried through net but with no success. Any help or guidance in solving this problem would be greatly appreciated.