creating Temp Varaible.

http://www.eggheadcafe.com/articles/20010823.asp



Let's take a very simple example that you can test for yourself. We'll use the trusty old Northwind sample database. Let's say that you run the Northwind Traders operation and you suddenly find a new shipper whose prices on everything that you normally ship with shipper number "1" that costs over a certain price level ( let's say we've determined the level is $50.25) can now be shipped with your new shipper number 4 for a flat rate of $21. You think, "Oboy - I'll be able to put in for a raise on this one!'

So you want to update all your open orders that had instructions to use Shipper 1 where the price came out to be greater than $50.25, and change the shipper to shipper number 4 and the new flat rate of $21.00.

Now before we begin, I know and you know that we can definitely do this with a single SQL Statement using a correlated subquery with a derived table holding the items to change. But for the sake of simplicity, let's just say for the sake of example that we've decided we need to do it either with a cursor or a temporary table. Now let's see how we could do it using the new TABLE variable, and avoid all the extra disk access and locking:

USE NORTHWIND

-- we declare our table variable first

declare @SpecialCustomers TABLE (
CustomerID nchar (5) NOT NULL ,
OrderID int NOT NULL ,
ShipVia int NOT NULL,
Freight money NOT NULL)

-- now we populate the in-memory table variable with the record information needed for the update

insert into @SPecialCustomers select CustomerID, OrderID, ShipVia, Freight
from dbo.Orders where ShipVia =1 AND Freight >50.25

-- and finally we update the affected records in our regular orders table with our new shipper and price information,
-- using the @SpecialCustomers TABLE variable just as we would a real, physical table in the database:

UPDATE ORDERS SET ShipVia=4, Freight =21.00
where ORDERS.OrderID IN (SELECT ORDERID FROM @SpecialCustomers)

Read html and Wrtie to HTMl fiile

public static bool WriteHtmlFile(string pHtmlFileNameWithFullPath, string pValue)
{
bool mSuccess = false;

if (pValue.Trim().Length > 0)
{

try
{
System.IO.StreamWriter sw = new System.IO.StreamWriter(pHtmlFileNameWithFullPath, false);

sw.Write(pValue);

sw.Close();
}
catch (Exception)
{
Exception ex = new Exception(pHtmlFileNameWithFullPath.Substring(pHtmlFileNameWithFullPath.LastIndexOf("\\")) + " not accessible !! contact administrator..");
throw ex;
}

mSuccess = true;
}

return mSuccess;
}

private string GetHTML1(string url, string resourceName)
{
try
{
StringBuilder content = new StringBuilder();

HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url);
HttpWebResponse response = (HttpWebResponse)request.GetResponse();

using (StringWriter writer = new StringWriter(content))
{
using (StreamReader reader = new StreamReader(response.GetResponseStream(), System.Text.Encoding.UTF8))
{

writer.Write(reader.ReadToEnd());
reader.Close();
}

writer.Close();
}

return content.ToString();
}
catch (Exception ex)
{
throw ex;
}
}

public string GetAbsoluteURI(string virtualPath, string queryString, bool useSecure)
{
try
{
string path = VirtualPathUtility.ToAbsolute(virtualPath);
Uri newUri = new Uri(HttpContext.Current.Request.Url, path);

UriBuilder uriBuilder = new UriBuilder(newUri);

if (!string.IsNullOrEmpty(queryString))
{
if (queryString.StartsWith("?")) queryString = queryString.Substring(1);
uriBuilder.Query = queryString;
}

if (!useSecure)
{
uriBuilder.Scheme = "http";
if (uriBuilder.Port == 443) uriBuilder.Port = 80;
}

return uriBuilder.Uri.ToString();
}
catch
{
return string.Empty;
}
}

convert xml to HTML

private void ConvertXMLToHTML(string xmfile)
{

//string xmlPath = HttpContext.Current.Server.MapPath(xmfile);


try
{

string XSLpath = System.Configuration.ConfigurationSettings.AppSettings.Get("XSLFile").ToString();

string originalhtmlpath = System.Configuration.ConfigurationSettings.AppSettings.Get("HTMLFile").ToString();

StringBuilder strb = new StringBuilder();

//+ "/" + System.Configuration.ConfigurationSettings.AppSettings.Get("ConfigFile").ToString());

string xsltpath = VirtualPathUtility.ToAbsolute("/PdfProject/BlGeneratePdf/BlGeneratePdf/NBS.xslt");

theText1 = this.GetXSLT(xsltpath, "");
// string xslPath = HttpContext.Current.Server.MapPath("NBS.xslt");

//Instantiate the XPathDocument Class
XmlDocument doc = new XmlDocument();
doc.LoadXml(xmfile);
////Instantiate the XslTransform Class
// XslTransform transform = new XslTransform();
// // transform.Load("NBS.xslt");
// transform.Load(XSLpath);
// XmlTextWriter writer = new XmlTextWriter(originalhtmlpath,null);
// writer.Formatting = Formatting.Indented;
// writer.Indentation=4;
// transform.Transform(doc, null, writer);

XslTransform xslt = new XslTransform();
xslt.Load(XSLpath);


XmlTextWriter writer = new XmlTextWriter(originalhtmlpath, null);

xslt.Transform(doc, null, writer);
writer.Close();
}
catch (Exception ex)
{
throw;
}




}

pick connection from app.config

http://www.dreamincode.net/forums/topic/45321-grabbing-connectionstring-from-appconfig/

PDF generation using abcpdf

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using ABCpdf = WebSupergoo.ABCpdf7;
using WebSupergoo.ABCpdf7;
using System.Net;
using System.IO;
using System.Text;
using System.Configuration;
using System.Collections;

public partial class Headerfooter : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

if (!IsPostBack)
{


ABCpdf.Doc theDoc = new Doc();

//BELOW CODE IS USED TO SET COLOR OF DOCUMENT
//theDoc.Color.String = "255 0 0";

ABCpdf.Doc theDoc1 = new Doc();
ABCpdf.Doc theDoc2 = new Doc();
ABCpdf.Doc theDoc3 = new Doc();
ABCpdf.Doc theDoc4 = new Doc();


int theID = 0, thID1=0, theCount = 0, i = 0;

//Header html
string theText1 = string.Empty;
string theText2 = string.Empty;
string theText3 = string.Empty;
theText1 = this.GetHTML(GetAbsoluteURI("~/headerwithhtml.htm", "", false), "TDR");
theText2 = this.GetHTML(GetAbsoluteURI("~/test2.htm", "", false), "TDR");
theText3 = this.GetHTML(GetAbsoluteURI("~/SampleDocument.htm", "", false), "TDR");

//Header logo
XImage theImg = new XImage();
theImg.SetFile(Server.MapPath("../HTMLToPDF/logo.jpg"));

/*Start Content*/
string test = "Add Text to First Document";
int theFont1 = theDoc1.EmbedFont("Comic Sans MS", "Latin", false, true);
int theFont2 = theDoc1.EmbedFont("Comic Sans MS", "Latin", false, true);

test = "" + test + "";
test = test.Replace("", "");
test = test.Replace("", "");

string teststring = "Add Text to Second Document";

int theFont3 = theDoc2.EmbedFont("Comic Sans MS", "Latin", false, true);
int theFont4 = theDoc2.EmbedFont("Comic Sans MS", "Latin", false, true);

teststring = "" + teststring + "";
teststring = teststring.Replace("", "");
teststring = teststring.Replace("", "");


theDoc.Rect.String = "50 50 560 680";
theID = theDoc.AddHtml(theText2);
while (theDoc.Chainable(theID))
{
// theDoc.Page = theDoc.AddPage();
theID = theDoc.AddHtml("", theID);
}

//XReadOptions xr = new XReadOptions();
//xr.ReadModule = ReadModuleType.OpenOffice;

theDoc1.Rect.String = "50 50 560 680";
//theDoc1.FontSize = 8;
thID1 = theDoc1.AddHtml(theText3);
/*
* Loop till there are more pages in the output
*/
while(theDoc1.Chainable(thID1))
{
theDoc1.Page = theDoc1.AddPage();
thID1 = theDoc1.AddHtml("", thID1);
}
//theDoc2.Read("test5.rtf", xr);
//theDoc3.Read("test4.rtf", xr);
//theDoc4.Read("test5.rtf", xr);


//theDoc1.Pos.X = 100;
//theDoc1.Pos.Y = 20;
// theDoc1.Rect.Position(70, -1000);


//theDoc1.AddHtml(test);

//theDoc2.Pos.X = 200;
//theDoc2.Pos.Y = 100;
//theDoc2.AddHtml(teststring);

//theDoc.Append(theDoc1);
//theDoc.Append(theDoc2);
//theDoc.Append(theDoc3);
theDoc.Append(theDoc1);
theCount = theDoc.PageCount;

/*End Content*/

/*Start header*/
theDoc.HPos = 0.5;
theDoc.VPos = 0.5;
for (i = 1; i <= theCount; i++)
{
theDoc.PageNumber = i;
//this loop set header on odd pages
if (theDoc.PageNumber % 2 != 0)
{
theDoc.Rect.Left = 60;
theDoc.Rect.Right = 250;
theDoc.Rect.Top = 10;
theDoc.Rect.Bottom = 725;
theDoc.Rect.Width = 60;
theDoc.Rect.Height = 60;
theDoc.AddImageObject(theImg, false);
theID = theDoc.AddImageObject(theImg, false);
while (theDoc.Chainable(theID))
{
theID = theDoc.AddImageObject(theImg, false);
}
theDoc.Rect.String = "90 780 560 670";
theDoc.FontSize = 8;
theID = theDoc.AddHtml(theText1);
/*
* Loop till there are more pages in the output
*/
while (theDoc.Chainable(theID))
{
theID = theDoc.AddHtml("", theID);
}
theDoc.Rect.String = "30 -40 106 100";
theDoc.FontSize = 8;
theDoc.AddText("Confidential to Ebix");

}
else
{



}

}
/*end header*/


/*Start footer "100 50 500 150";*/
theDoc.Rect.String = "570 -40 300 100";
theDoc.HPos = 1.0;
theDoc.VPos = 0.5;
theDoc.FontSize = 8;
for (i = 1; i <= theCount; i++)
{
theDoc.PageNumber = i;
theDoc.AddText("Page " + i.ToString() + " of " + theCount.ToString());
//theDoc.FrameRect();
}

/*end footer*/

/*Save file as PDF*/

string path = System.Web.HttpContext.Current.Request.PhysicalApplicationPath + "Headerfooter.pdf";
if (File.Exists(path))
{
File.Delete(path);
theDoc.Save(Server.MapPath("headerfooter.pdf"));
theDoc.Clear();

}
else
{

theDoc.Save(Server.MapPath("headerfooter.pdf"));
theDoc.Clear();

}

}
}

private string GetHTML(string url, string resourceName)
{
try
{
StringBuilder content = new StringBuilder();

HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url);
HttpWebResponse response = (HttpWebResponse)request.GetResponse();

using (StringWriter writer = new StringWriter(content))
{
using (StreamReader reader = new StreamReader(response.GetResponseStream(), System.Text.Encoding.UTF8))
{

writer.Write(reader.ReadToEnd());
reader.Close();
}

writer.Close();
}

return content.ToString();
}
catch (Exception ex)
{
throw ex;
}
}



public string GetAbsoluteURI(string virtualPath, string queryString, bool useSecure)
{
try
{
string path = VirtualPathUtility.ToAbsolute(virtualPath);
Uri newUri = new Uri(HttpContext.Current.Request.Url, path);

UriBuilder uriBuilder = new UriBuilder(newUri);

if (!string.IsNullOrEmpty(queryString))
{
if (queryString.StartsWith("?")) queryString = queryString.Substring(1);
uriBuilder.Query = queryString;
}

if (!useSecure)
{
uriBuilder.Scheme = "http";
if (uriBuilder.Port == 443) uriBuilder.Port = 80;
}

return uriBuilder.Uri.ToString();
}
catch
{
return string.Empty;
}
}


}

GET Fetch Data USing Adaperter with input parameter.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using ABCpdf = WebSupergoo.ABCpdf7;
using WebSupergoo.ABCpdf7;
using System.Net;
using System.IO;
using System.Text;
using System.Configuration;
using System.Collections;
using System.Data;
using System.Data.SqlClient;

public partial class Headerfooter : System.Web.UI.Page
{

Pdfgenration ObjPdf = new Pdfgenration();

protected void Page_Load(object sender, EventArgs e)
{

if (!IsPostBack)
{


string HtmlString = string.Empty;
string strfileName = string.Empty;
StringBuilder strHtml = new StringBuilder();
string dbstring = ConfigurationManager.AppSettings["DB_WEBGRID_CON_STRING"].ToString();
SqlConnection sqlcon = new SqlConnection(dbstring);
sqlcon.Open();

if (sqlcon.State.ToString() == "Open")
{
SqlCommand command = new SqlCommand("Sproc_POL_INSTALLMENT_BOLETO", sqlcon);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@Boleto_ID", SqlDbType.Int).Value = 821;
SqlDataAdapter adapter = new SqlDataAdapter(command);
DataSet ds = new DataSet();
adapter.Fill(ds);
HtmlString = ds.Tables[0].Rows[0]["BOLETO_HTML"].ToString();
string Customer_ID = ds.Tables[0].Rows[0]["CUSTOMER_ID"].ToString();
string Policy_ID = ds.Tables[0].Rows[0]["POLICY_ID"].ToString();
string Installement_ID = ds.Tables[0].Rows[0]["INSTALLEMT_ID"].ToString();
string Boleto_ID = ds.Tables[0].Rows[0]["BOLETO_ID"].ToString();
strfileName = Customer_ID + "_" + Policy_ID + "_" + Installement_ID + "_" + Boleto_ID+".pdf";


}

string strPath = "../HTMLToPDF"+ConfigurationManager.AppSettings["PDFDocumentsPath"].ToString();

//string path = System.Web.HttpContext.Current.Request.PhysicalApplicationPath + Filename;

string strLocation = Server.MapPath(strPath) + strfileName;
if (!Directory.Exists(Server.MapPath(strPath)))
Directory.CreateDirectory(Server.MapPath(strPath));
ObjPdf.GeneratePdf(HtmlString, strfileName, strLocation);

//if (!File.Exists(strPath))
//{
// File.Create(strPath);
// ObjPdf.GeneratePdf(HtmlString, strfile, strPath);
//}
//else
//{
// File.Delete(strPath);
// ObjPdf.GeneratePdf(HtmlString, strfile, strPath);
//}
}


}


}

get file extension

using System;
using System.IO;

class Program
{
static void Main()
{
string p = @"C:\Users\Sam\Documents\Test.txt";

string e = Path.GetExtension(p);
if (e == ".txt")
{
Console.WriteLine(e);
}
}
}