Tuesday, December 6, 2011

Export DataSet to XML File

create xml file from a dataset
This code snippet show how to export DataSet to xml file.Here we have taken data from database and fill dataset.We have taken a aspx page and a button there,after clicking the button a xml file will be created at specified location.

 public void CreateXml()  
{
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter("usp_GetAddressDetails",con);
da.Fill(ds);
if (ds.Tables[0].Rows.Count > 0)
{
GridView1.DataSource = ds;
GridView1.DataBind();
}
StreamWriter objStreamWriter = new StreamWriter("E:\\Santosh\\Practices\\ASP\\XMLFiles\\EmpAddress.xml", false);
ds.WriteXml(objStreamWriter);
objStreamWriter.Close();
}
protected void Button1_Click(object sender, EventArgs e)
{
CreateXml();
}


Regards
Santosh Singh

Monday, December 5, 2011

Insert XML data into SQL Server 2008

 protected void Button1_Click(object sender, EventArgs e)   
  {   
       string EmpCode = null;   
       string City = null;   
       string State = null;   
       string Country = null;   
       string str = null;   
       SqlConnection con = new SqlConnection("Data Source=localhost;Initial Catalog=Test;Integrated Security=True");   
       XmlDocument document = new XmlDocument();   
       document.Load("E:\\Santosh\\EmpAddress.xml");   
       XmlNodeList xmlNodeList = document.SelectNodes("EmpAddress/Address");   
       foreach (XmlNode node in xmlNodeList)   
       {   
            EmpCode = node["EmpCode"].InnerText;   
            City = node["City"].InnerText;   
            State = node["State"].InnerText;   
            Country = node["Country"].InnerText;   
            str = "INSERT INTO AddressDetail (EmpCode,City,State,Country) values ('" + EmpCode + "', '" + City + "','" + State + "','" + Country + "')";   
            SqlCommand cmd = new SqlCommand();   
            cmd = new SqlCommand(str, con);   
            cmd.CommandType = CommandType.Text;   
            con.Open();   
            cmd.ExecuteNonQuery();   
            con.Close();   
       }   
  }    

Sunday, March 27, 2011

Create PDF from DataTable in Asp.net

In this article ,i am explaining you how to create pdf file from datatable.i am passing a datatable in this function and code to convert this in pdf file.

I am using iTextSharp you can download it from internet. it is free.
you need to create a simple asp.net page and get data from database.Then pass it to ExportToPDF method.We can set PDF page margins, change page orientation (portrait, landscape), customize headers and footers add page numbers and more.Do some R & D with iTextSharp.


 public void ExportToPdf(DataTable myDataTable)  
{
Document pdfDoc = new Document(PageSize.A4, 10, 10, 10, 10);
try
{
PdfWriter.GetInstance(pdfDoc, System.Web.HttpContext.Current.Response.OutputStream);
pdfDoc.Open();
Chunk c = new Chunk("" + System.Web.HttpContext.Current.Session["CompanyName"] + "", FontFactory.GetFont("Verdana", 11));
Paragraph p = new Paragraph();
p.Alignment = Element.ALIGN_CENTER;
p.Add(c);
pdfDoc.Add(p);
string clientLogo = System.Web.HttpContext.Current.Session["CompanyName"].ToString();
clientLogo = clientLogo.Replace(" ", "");
string clogo = clientLogo + ".jpg";
string imageFilePath = System.Web.HttpContext.Current.Server.MapPath("../ClientLogo/" + clogo + "");
iTextSharp.text.Image jpg = iTextSharp.text.Image.GetInstance(imageFilePath);
//Resize image depend upon your need
jpg.ScaleToFit(80f, 60f);
//Give space before image
jpg.SpacingBefore = 0f;
//Give some space after the image
jpg.SpacingAfter = 1f;
jpg.Alignment = Element.HEADER;
pdfDoc.Add(jpg);
Font font8 = FontFactory.GetFont("ARIAL", 7);
DataTable dt = myDataTable;
if (dt != null)
{
//Craete instance of the pdf table and set the number of column in that table
PdfPTable PdfTable = new PdfPTable(dt.Columns.Count);
PdfPCell PdfPCell = null;
for (int rows = 0; rows < dt.Rows.Count; rows++)
{
for (int column = 0; column < dt.Columns.Count; column++)
{
PdfPCell = new PdfPCell(new Phrase(new Chunk(dt.Rows[rows][column].ToString(), font8)));
PdfTable.AddCell(PdfPCell);
}
}
//PdfTable.SpacingBefore = 15f; // Give some space after the text or it may overlap the table
pdfDoc.Add(PdfTable); // add pdf table to the document
}
pdfDoc.Close();
Response.ContentType = "application/pdf";
Response.AddHeader("content-disposition", "attachment; filename= SampleExport.pdf");
System.Web.HttpContext.Current.Response.Write(pdfDoc);
Response.Flush();
Response.End();
//HttpContext.Current.ApplicationInstance.CompleteRequest();
}
catch (DocumentException de)
{
System.Web.HttpContext.Current.Response.Write(de.Message);
}
catch (IOException ioEx)
{
System.Web.HttpContext.Current.Response.Write(ioEx.Message);
}
catch (Exception ex)
{
System.Web.HttpContext.Current.Response.Write(ex.Message);
}
}

Thanks & Regards
Santosh Singh