C# Read Excel file (.xls) using JET OLEDB Provider

public static void extractExcelContent()
{
string path = "D:\shared\file.xls";
if (File.Exists(path))
{
//create the "database" connection string (FOR EXCEL)
string connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=\"" + path + "\";" +
"Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1;\"";
//create the database query
string query = "SELECT * FROM [FirstExcelSheet$]";

//create a DataTable to hold the query results
DataTable dTable = new DataTable();

//create an OleDbDataAdapter to execute the query
using (OleDbDataAdapter dAdapter = new OleDbDataAdapter(query, connString))
{
try
{
//fill the DataTable
dAdapter.Fill(dTable);
if (dTable.Rows.Count > 0)
{
foreach (DataRow row in dTable.Rows)
{
string fiscalDate = row["FiscalDate"].ToString();
string currency = row["Currency"].ToString();
double rate;
double.TryParse(row["Rate"].ToString(), out rate);
try
{
//do something
}
catch (Exception ex)
{
Console.Writeline("Error while updateExchangeRate: " + ex.ToString());
}

}
}
}
catch (InvalidOperationException e)
{
Console.Writeline(e.ToString());
}
dAdapter.Dispose();
}

//Optional: rename file
DateTime dtNow = DateTime.Now.AddDays(-1);
File.Move(path, path + dtNow.ToString("yyyy-MM-dd"));
}

}

Very quick way to generate Excel file from C#. LOVE IT!

Download the DLL from Google Code and include it in the C# project.
http://code.google.com/p/excellibrary/

//Create the dataset
DataSet ds = new DataSet();

//Set the locale for each
ds.Locale = System.Threading.Thread.CurrentThread.CurrentCulture;

//Create connection
SqlConnection sConn = new SqlConnection();
sConn.ConnectionString = ConnectionString;
sConn.Open();

// Execute stored procedure and fill dataset
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "sp_sample_stored_procedure";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = sConn;

SqlParameter param = new SqlParameter("Name", SqlDbType.NVarChar);
param.Direction = ParameterDirection.Input;
param.Value = name;
cmd.Parameters.Add(param);

SqlDataAdapter sAdapter = new SqlDataAdapter(cmd);
sAdapter.Fill(ds);
sConn.Close();

//Populate Excel worksheet from the data set
ExcelLibrary.DataSetHelper.CreateWorkbook("file.xls", ds);

Login to a website and download file using C#

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Net;
using System.Configuration;
using System.IO;

namespace HttpBrowsing
{
class SimulateHttp
{
private HttpWebRequest request;
private CookieContainer cookieContainer = new CookieContainer();

public bool doLogin(string uid, string pwd, string url)
{
// Create a request using a URL that can receive a post.
request = (HttpWebRequest)HttpWebRequest.Create(url);
request.CookieContainer = cookieContainer;

// Set the Method property of the request to POST.
request.Method = "POST";

// Set the ContentType property of the WebRequest.
request.ContentType = "application/x-www-form-urlencoded";

// Create POST data and convert it to a byte array.
string postData = "user_name=" + uid + "&user_password=" + pwd;
byte[] byteArray = Encoding.UTF8.GetBytes(postData);

// Set the ContentLength property of the WebRequest.
request.ContentLength = byteArray.Length;
request.KeepAlive = true;

// Get the request stream.
using (Stream dataStream = request.GetRequestStream())
{
dataStream.Write(byteArray, 0, byteArray.Length);
}

HttpWebResponse response = (HttpWebResponse)request.GetResponse();
response.Cookies = request.CookieContainer.GetCookies(request.RequestUri);
response.Close();
return true;

}

public void downloadFile(string url, string fileName)
{
// Create a request using a URL that can receive a post.
request = (HttpWebRequest)HttpWebRequest.Create(url);
request.CookieContainer = cookieContainer;

// Set the Method property of the request to GET.
request.Method = "GET";

// Get the response.
using (HttpWebResponse response = (HttpWebResponse)request.GetResponse())
{
using (Stream responseStream = response.GetResponseStream())
{
using (StreamReader reader = new StreamReader(responseStream))
{
using (StreamWriter writer = new StreamWriter(fileName, false))
{
writer.Write(reader.ReadToEnd());
writer.Flush();
writer.Close();
}
}
responseStream.Close();
}
response.Close();
}
}

public void doLogout(string url)
{
// Create a request using a URL that can receive a post.
request = (HttpWebRequest)HttpWebRequest.Create(url);
request.CookieContainer = cookieContainer;

// Set the Method property of the request to POST.
request.Method = "GET";

HttpWebResponse response = (HttpWebResponse)request.GetResponse();
response.Close();
}

}

Documentum Transformation Service (DTS) rendition fails intermittently

Once in a while the DTS to transform documents would fail without any special reason.
This error appears in the DTS logs: "Error: The remote server machine does not exist or is unavailable"

A machine reboot is needed in order to get services back to normal. However, this does not completely resolve the problem and the same thing happens again after some time.

Workaround:
1. Open the registry editor (Start->Run->Regedit).
2. Navigate to the following key HKEY_LOCAL_MACHINE\SOFTWARE\Adlib\Adlib FMR\Adlib Express\Default.
3. On the right hand pane, set the value of AvailMemUsageLimit to 0 (Double click and type the value in).
4. Stop all services (adlib and CTS):
- Documentum Content Transformation Services (also stop Documentum Content Transformation Monitor Services)
- Documentum CTS Admin. Agent
- Adlib Exponent Connector
- Adlib Exponent Manager
- Adlib Express Server
- Adlib FMR

5. Restart the CTS services(cts will restart adlib)
- Documentum Content Transformation Monitor Services (Monitor will restart Documentum Content Transformation Services)
- Documentum CTS Admin. Agent