Export SQl Data into PDF with image

Add Reference the ItextSharp.dll file for creating PDF File


using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using iTextSharp.text;
using iTextSharp.text.pdf;
using iTextSharp.text.pdf.events;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Web.UI.HtmlControls;
using System.Web.Security;
using iTextSharp.text.html;
using iTextSharp.text.html.simpleparser;

public partial class _Default : System.Web.UI.Page
{
    DataSet ds = new DataSet();
    DataTable dt = new DataTable();
    SqlConnection cn = new SqlConnection("Data Source=Servername;Initial Catalog=Databasename;Integrated security=True");
    protected void Page_Load(object sender, EventArgs e)
    {
       
        cn.Open();
        SqlCommand cd = new SqlCommand("select * from table_name", cn);
 
        SqlDataAdapter da = new SqlDataAdapter(cd);
        da.Fill(dt);
        //ExportDaataSetTopdf(ds."report");


        Document  document = new Document(PageSize.A4, 50, 50, 150, 50);

        // Create a new PdfWrite object, writing the output to the file ~/PDFTemplate/Report.pdf
        FileStream  output = new FileStream(Server.MapPath("~/PDFTemplate/Report.pdf"), FileMode.Create);
        PdfWriter  writer = PdfWriter.GetInstance(document, output);

        // Open the Document for writing
        document.Open();
        iTextSharp.text.Image  logo = iTextSharp.text.Image.GetInstance(Server.MapPath("~/logo.jpg"));
        logo.SetAbsolutePosition(350, 730);
        document.Add(logo);
        if (dt != null)
        {

            PdfPTable PdfTable = new PdfPTable(dt.Columns.Count);
            PdfPCell PdfPCell = null;

            for (int rows = 0; rows < dt.Rows.Count; rows++)
            {
                if (rows == 0)
                {
                    for (int column = 0; column < dt.Columns.Count; column++)
                    {
                        PdfPCell = new PdfPCell(new Phrase(dt.Columns[column].ColumnName.ToString()));
                        PdfTable.AddCell(PdfPCell);
                    }
                }
                for (int column = 0; column < dt.Columns.Count; column++)
                {
                    PdfPCell = new PdfPCell(new Phrase(dt.Rows[rows][column].ToString()));
                    PdfTable.AddCell(PdfPCell);
                }
            }

            // Finally Add pdf table to the PDF document
            document.Add(PdfTable);
        }
        document.Close();
        cn.Close();
    }
}

Export SQl Data Table into Excel,CSV with image Header

using System;

using System.Configuration;

using System.Security;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using System.Data.SqlClient;

using System.Text;

using System.Collections;

using System.IO;

using System.Data;

using System.Data.OleDb;

using System.Windows.Forms;

public partial class _Default : System.Web.UI.Page

{

#region variable

   SqlConnection connection = new SqlConnection("Data Source=servername;Initial Catalog=Databasename;Integrated Security=True");

   

    public int j;

    ArrayList list = new ArrayList();

    public static int column_count = 65;

    int column_total;

    int k;

    OleDbConnection MyConnection;

#endregion 

 

/*------------- Insert Data into Excel -------------------------*/

    public void insert_data()

    {

        MyConnection = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; Data Source='c://test.xls';Extended Properties=Excel 8.0;");

       

        SqlCommand sql_cmd = new SqlCommand("select * from table_name",connection);

        SqlDataReader dr = sql_cmd.ExecuteReader();

        while (dr.Read())

        {

            string sno = dr["Sno"].ToString();

            string name = dr["Name"].ToString();

            string address = dr["Address"].ToString();

            string grade = dr["Grade"].ToString();

            string salary = dr["Salary"].ToString();

            MyConnection.Open();

            string sql = "insert into [Sheet1$] (Sno,Name,Address,Grade,Salary) values('"+sno+"','"+name+"','"+address+"','"+grade+"','"+salary+"')";

            OleDbCommand cmd = new OleDbCommand(sql, MyConnection);

            cmd.ExecuteNonQuery();

            MyConnection.Close();

        }

    }

/*------------- End of Method -------------------------*/

    /* This Method used to read column names from table*/

    public void fetch_column_name()

    {

        connection.Open();

        SqlCommand cmd = new SqlCommand("select column_name from information_schema.columns where table_name='table_name'", connection);

        SqlDataReader dr = cmd.ExecuteReader();

        while (dr.Read())

        {

            list.Add(dr["column_name"]);

        }

        connection.Close();

    }

    public void insert_excel_image()

    {

        fetch_column_name();

        Excel.Application app;

        Excel.Workbook Excel_Book;

        Excel.Worksheet Excel_sheet;

        Excel.Range range;

        object mis_value = System.Reflection.Missing.Value;

        app = new Excel.ApplicationClass();

        Excel_Book = app.Workbooks.Add(mis_value);

        Excel_sheet = (Excel.Worksheet)Excel_Book.Worksheets.get_Item(1);

        Excel_sheet.Shapes.AddPicture(Server.MapPath("logo.jpg"), Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 50, 50, 100, 100);

        column_total = column_count + list.Count;

        /* --------------- Creating Header----------------------*/

        for (column_count = 65; column_count <= column_total; column_count++)

        {

            char c = Convert.ToChar(column_count);

            string s = c + "20";

            range = Excel_sheet.get_Range(s, mis_value);

            if (j < list.Count)

            {

                range.Value2 = list[j].ToString();

                j++;

            }

        }

        /* ----------------- End of Header -------------------------*/

        /*-----------------------------------------------------------*/

        DataTable dt = new DataTable();

        //insert_excel_image();

        connection.Open();

        SqlCommand cmd = new SqlCommand("select * from table_name", connection);

        SqlDataAdapter dr = new SqlDataAdapter(cmd);

        dr.Fill(dt);

     /*------------------------------------------------------------*/

/*-------------------- Insert Image into Excel-----------------------*/

        Excel_Book.SaveAs("C:/test.xls", Excel.XlFileFormat.xlWorkbookNormal, mis_value, mis_value, mis_value, mis_value, Excel.XlSaveAsAccessMode.xlExclusive, mis_value, mis_value, mis_value, mis_value, mis_value);

        Excel_Book.Close(true, mis_value, mis_value);

        app.Quit();

        insert_data();

/*-------------------- End of Process  -----------------------*/

    }

    protected void Page_Load(object sender, EventArgs e)

    {

       

    }

   

   

    protected void Button2_Click1(object sender, EventArgs e)

    {

        insert_excel_image(); //Using to insert data into Excel.

    }

   

}

Add image in Excel sheet using c#


Excel.Application app;
        Excel.Workbook Excel_Book;
        Excel.Worksheet Excel_sheet;
        object mis_value = System.Reflection.Missing.Value;
        app=new Excel.ApplicationClass();
        Excel_Book = app.Workbooks.Add(mis_value);
        Excel_sheet = (Excel.Worksheet)Excel_Book.Worksheets.get_Item(1);
        Excel_sheet.Shapes.AddPicture(Server.MapPath("1.bmp"), Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 50, 50, 300, 500);
        Excel_Book.SaveAs("test.xls", Excel.XlFileFormat.xlWorkbookNormal, mis_value, mis_value, mis_value, mis_value, Excel.XlSaveAsAccessMode.xlExclusive, mis_value, mis_value, mis_value, mis_value, mis_value);
        Excel_Book.Close(true, mis_value , mis_value );
        app.Quit();

Coding For Insert,Update,Delete,select from Sql Server table using Linq to Sql

Steps for add sql Table into the project before going for Code

1. Add LinQ to Sql classes file into the project file.
2.select the design page, and drag  the tables from the   DataBase Explorer Window to the design page.If you place more than one table with the relationship,the page will automatically show the relationship diagram of the selected table.


Note:

"    DataClassesDataContext " is the name of LinQ to Sql classes file.

 /* Insert data into sql server table using Linq to SQl */

        DataClassesDataContext  insert_content = new DataClassesDataContext();
        test Insert_data = new test();
        Insert_data.num = int.Parse(TextBox1.Text);
        Insert_data.test_name = TextBox2.Text.ToString();
        insert_content.tests.InsertOnSubmit(Insert_data);
        insert_content.SubmitChanges();

 /*-------------------- End Here --------------------------*/


/* Update data into Table using Linq to sql */

        DataClassesDataContext Update_table = new DataClassesDataContext();
        string num = TextBox1.Text;
        test Update = (from Up_data in Update_table.tests
                      where Up_data.num == int.Parse(num)
                      select Up_data).Single();
        Update.test_name = TextBox2.Text.ToString();
        Update_table.SubmitChanges();

 /*-------------------- End Here --------------------------*/



/* Delete data from Sql server table using Linq to Sql */

        DataClassesDataContext context = new DataClassesDataContext();
        string num = null;
        num = TextBox1.Text.ToString();
        test delete_data = (from test_data in context.tests
                                    where test_data.num == int.Parse(num)
                                     select test_data).Single();
        context.tests.DeleteOnSubmit(delete_data);
        context.SubmitChanges();

 /*-------------------- End Here --------------------------*/


 /* Select data from sql server using Linq to Sql*/
       
        DataClassesDataContext context = new DataClassesDataContext();
       
            var name = from name_values in context.tests where
                              name_values.test_name.Contains(TextBox1.Text.ToString())
                              select name_values;
            GridView2.DataSource = name;
            GridView2.DataBind();


 /*-------------------- End Here --------------------------*/