using System;
using System.Web;
using System.Data;
using System.Data.OracleClient;
using System.Data.SqlClient;
using System.Text;
using System.Configuration;
using System.IO;
using System.Web.SessionState;
namespace DiamondB.TextApp
{
/// <summary>
/// Export a SQL query to excel from ASP.NET
/// </summary>
public class ExportToExcel: IHttpHandler, IRequiresSessionState
{
public ExportToExcel()
{
//
// TODO: Add constructor logic here
//
}
#region IHttpHandler Members
/// <summary>
/// This is the main entry point. You can read about this in other places
/// from more reliable sources than me. The context object contains
/// the session, user, and other objects you are used to.
/// </summary>
public void ProcessRequest(HttpContext context)
{
string sHtml = "";
try
{
string sSQL = CreateSQLQuery();
sHtml = RenderGrid(sSQL);
}
catch(Exception ex)
{
sHtml = "<body>There was a problem executing the query.<br>"+ex.Message+"</body>";
}
// return the results to the browser
context.Response.Clear();
context.Response.ContentType = "application/vnd.ms-excel";
context.Response.Charset = "";
context.Response.Write(sHtml);
context.Response.End();
}
/// <summary>
/// DESCRIPTION: This property defines whether another HTTP handler can
/// reuse this instance of the handler.
///
/// NOTE: false is always returned since this handler is synchronous
/// and is not pooled.
/// </summary>
public bool IsReusable
{
get { return false;}
}
#endregion
/// <summary>
/// I have specialized dynamic queries that I create on the fly. That is why I have
/// the query creation routine separate. For most cases this is probably not needed.
/// </summary>
/// <param name="data"></param>
/// <returns></returns>
private string CreateSQLQuery()
{
string sQuerySQL = "SELECT * FROM SCHEMA.TABLE"
return sQuerySQL;
}
/// <summary>
/// This code takes the SQL query, executes it, pumps the data into a data grid,
/// grabs the HTML out of the grid, and returns. Simple, right!
/// Also left in this code is some of my query logging code. Every time the query
/// is run I log the query, how long it took to execute, and how many rows were returned.
/// But that is just me.
/// </summary>
private string RenderGrid(string sql)
{
// I just put this here so something is outputted.
string sHTML = "<html><body><pre>" + sql + "</pre></body></html>";
int iRows = -1;
double dSeconds = 0.0;
DateTime dtStart;
try
{
string sConn = ConfigurationSettings.AppSettings["OracleConnectionString"];
OracleConnection oConn = new OracleConnection(sConn);
OracleCommand oCmd = new OracleCommand(sql, oConn);
oConn.Open();
try
{
dtStart = DateTime.Now; // get the current time
// get the data
DataSet ds = new DataSet();
OracleDataAdapter da = new OracleDataAdapter(oCmd);
da.Fill(ds);
// check how long the query took
TimeSpan ts = DateTime.Now - dtStart;
dSeconds = ts.TotalSeconds;
// setup and load the grid.
System.Web.UI.WebControls.DataGrid oGrid = new
System.Web.UI.WebControls.DataGrid();
oGrid.HeaderStyle.BackColor = System.Drawing.Color.Silver;
oGrid.HeaderStyle.ForeColor = System.Drawing.Color.Black;
oGrid.DataSource = ds;
oGrid.DataBind();
// get the html from the grid
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHTMLWriter = new
System.Web.UI.HtmlTextWriter(oStringWriter);
oGrid.RenderControl(oHTMLWriter);
sHTML = oStringWriter.ToString();
oHTMLWriter.Close();
oStringWriter.Close();
// get the number of rows returned
iRows = ds.Tables[0].Rows.Count;
}
finally
{
oConn.Close();
}
}
catch(Exception ex)
{
sHTML += ex.Message;
}
return sHTML;
}
}
}
