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;
}




}