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 --------------------------*/

Query for selecting first 3 rows from the table

set rowcount 3
delete fromexcel
set rowcount 0
select * from excel

The above query retrieve and show first 3 rows of records from the table,The set rowcount=0 stop the process suddenly and help to show only 3 rows.

store procedure for searching particlaur table all the table have a access rights from the SQL Server

create Procedure Sp_CTSI_internal
@start_date nvarchar(50),
@end_date nvarchar(50)
as
declare @count_database int
declare @inc_count int
declare @name_database nvarchar(4000)
declare @sql_query nvarchar(4000)
declare @table_name nvarchar(4000)
declare @report_path nvarchar(4000)
declare @get_query nvarchar(4000)
declare @query nvarchar(4000)
--declaration end

EXEC('Create table internal_ctsi_result(Custno nvarchar(100),Total_Count int,Database_Name nvarchar(1000))')
--initialize the value for the varibales
set @inc_count=5
set @table_name='[master].[CTSI\Vijay].internal_ctsi_result'
select @count_database=count(dbid) from sql4.master.dbo.sysdatabases

--End of Initialization

while(@inc_count<=@count_database)
begin
select @name_database=name from sql4.master.dbo.sysdatabases where dbid=@inc_count and ISNULL(HAS_DBACCESS ([Name]),0)=1 order by [Name]
if exists(select top 1 name from sql4.master.dbo.sysdatabases where dbid=@inc_count and ISNULL(HAS_DBACCESS ([Name]),0)=1 order by [Name])
begin

--begin exec
exec('
declare @sql nvarchar(4000)
declare @sql_query nvarchar(4000)
use '+@name_database+'
if exists(select TOP 1 table_name from information_schema.tables where table_catalog='''+@name_database+''' and table_name=''shipment'' and table_type=''base table'')
begin
--print ''tables Exists in the Database :''+'''+@name_database +'''
if exists(select top 1 column_name from information_schema.columns where table_catalog='''+@name_database+''' and table_name=''shipment''  and column_name=''entdate'')
begin
set @sql=''insert into '+@table_name+'(Custno,Total_Count,Database_Name) select custno,count(*)as Total_count,'''''+@name_database+''''' from  sql4.'+@name_database+'.dbo.shipment where entdate between ''''1/1/2010'''' and ''''12/31/2010'''' group by custno''
exec(@sql)
print @sql
end
end
')
--end exec

end
set @inc_count=@inc_count+1
end
exec('use master')
--Export report ot Excel
--set @get_query='select * into sql4.[MASTER].[CTSI\Vijay].temp# from [master].[CTSI\Vijay].internal_ctsi_result'
set @query='select * from SQL4.[MASTER].[CTSI\Vijay].internal_ctsi_result'
exec(@query)
drop table [master].[CTSI\Vijay].internal_ctsi_result
go



Run the Procedure
exec sql4.[master].[CTSI\Vijay].Sp_CTSI_internal '1/1/2010','12/31/2010'

Storing image into database and retrieve from database and display in web form image control

code for Aspx.cs Page

protected void btnSubmit_Click(object sender, EventArgs e)

        {

            try

            {

                //typecasete imageupload control in a varaiable

                FileUpload img = (FileUpload)imgUpload;

                Byte[] imgByte = null;

                if (img.HasFile && img.PostedFile != null)

                {

                    //To create a PostedFile

                    HttpPostedFile File = imgUpload.PostedFile;

                    //Create byte Array with file len

                    imgByte = new Byte[File.ContentLength];

                    //force the control to load data in array

                    File.InputStream.Read(imgByte, 0, File.ContentLength);

                }

                // Insert the employee name and image into db

                connection.Open();

                string sql = "INSERT INTO image_test(image_name,image_source) VALUES(@image_name, @image_source)";

                SqlCommand cmd = new SqlCommand(sql, connection);

                cmd.Parameters.AddWithValue("@image_name", imgUpload.PostedFile.FileName.ToString() );

                cmd.Parameters.AddWithValue("@image_source", imgByte);

                cmd.ExecuteNonQuery();

                connection.Close();                       

            }

            catch

            { lblResult.Text = "There was an error"; }

        }

        protected void Button1_Click(object sender, EventArgs e)

        {

            // For reading image from database and display in a image control in web form.

            Image1.ImageUrl = "~/image.ashx?image_name=" + txt_imageshow.Text.Trim();

        }

Next step add ashx extension file with following instruction:

click project > Add New Item > Generic Handler > image.ashx

then write the code in the form. 

public void ProcessRequest(HttpContext context)
        {

            string image_name = null;
            if (context.Request.QueryString["image_name"] != null)
               image_name  = Convert.ToString(context.Request.QueryString["image_name"]);
            else
                throw new ArgumentException("No parameter specified");

            context.Response.ContentType = "image/jpeg";
            Stream strm = ShowEmpImage(image_name);
            byte[] buffer = new byte[strm.Length];
            int byteSeq = strm.Read(buffer, 0, Convert.ToInt32(strm.Length));

            while (byteSeq > 0)
            {
                context.Response.OutputStream.Write(buffer, 0, byteSeq);
                byteSeq = strm.Read(buffer, 0, 4096);
            }       
           // context.Response.ContentType = "text/plain";
           // context.Response.Write("Hello World");
        }
        public Stream ShowEmpImage(string image_name)
        {
           
            string sql = "SELECT image_source FROM image_test WHERE image_name = @name";
            SqlCommand cmd = new SqlCommand(sql, connection);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.AddWithValue("@name", image_name);
            connection.Open();
            object img = cmd.ExecuteScalar();
            try
            {
                return new MemoryStream((byte[])img);
            }
            catch
            {
                return null;
            }
            finally
            {
                connection.Close();
            }
        }

Code for Run a DOS command from c#.net

FileInfo f = new FileInfo(TextBox1.Text.ToString());


if (f.Exists)

{

Response.Write("file exists");

}

else

{

string command = "copy " + Server.MapPath("template\\canada.xls") + " " + TextBox1.Text.ToString() + "";

System.Diagnostics.ProcessStartInfo procStartInfo =

new System.Diagnostics.ProcessStartInfo("cmd", "/c " + command);



// The following commands are needed to redirect the standard output.

// This means that it will be redirected to the Process.StandardOutput StreamReader.

procStartInfo.RedirectStandardOutput = true;

procStartInfo.UseShellExecute = false;

// Do not create the black window.

procStartInfo.CreateNoWindow = true;

// Now we create a process, assign its ProcessStartInfo and start it

System.Diagnostics.Process proc = new System.Diagnostics.Process();

proc.StartInfo = procStartInfo;

proc.Start();

// Get the output into a string

string result = proc.StandardOutput.ReadToEnd();

// Display the command output.

Response.Write(result);

}

}

catch (Exception objException)

{

// Log the exception

}

Code Example for abstract and interface

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;

namespace ConsoleApplication3
{
   abstract class test
    {
        abstract public void print();
        public int y; //can initialise fields 
    }

     class Program:test

    {
         //abstract class
        public override void print()
        {
            x = 10;
            Console.WriteLine("Abstract  : test value :"+y.ToString());
        }
         //end of abstract
        public int x;
     }
     interface test_interface
     {
        void test();
         // int x; it gives the error as Error    "Interfaces cannot contain fields"            
     }
     class tt:Program,test_interface //inherit base class and interface
    {
        //interface
        public  void test()
        {
           
            Console.WriteLine("interface value");
        }
        //end
     }

   
    class main_class
    {
        static void Main(string[] args)
        {
           
            Program pp = new Program();//create instance for base class for abstract only;
            tt te=new tt(); //for both abstract and derived class
            te.print();//abstract
            te.test();//interface

            pp.print();//
           
            pp.x = 100;
            Console.WriteLine(pp.x);
            test_interface ttt = (test_interface)te;//type casting into interface type
            ttt.test();
        }
    }
}

code for creating sql server table as a xml file

DataTable data_table = new DataTable();
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter("select * from test_table", connection);
da.Fill(ds);
ds.WriteXml(@"c:\output2.xml", XmlWriteMode.IgnoreSchema);
connection.Close();


Read XML file data

XmlTextReader xml_reader = new XmlTextReader(@"c:\output1.xml");
        XmlDocument xml_doc = new XmlDocument();
        xml_doc.Load(xml_reader);
        XmlNode select_node = xml_doc.SelectSingleNode("NewDataSet");
        node_list = xml_doc.GetElementsByTagName("Table");

        for (i = 0; i < node_list.Count; i++)
        {
            for (j = 0; j < node_list[i].ChildNodes.Count; j++)
            {
                     Response.Write(node_list[i].ChildNodes[j].InnerText);
            }
}