.Net ramblings
# Thursday, 30 June 2005
Crystal Reports for .Net, locked file when exporting to PDF with a crHtmlText field

i made a simple change to a crystal report in the VS designer, by changing the text format for a text box to crHtmlText instead of crStandardText.  little did i realise this would break the report altogether and cause it to fail when i try to export as a PDF.  i had forgotten i did this change at all and thought it was a permissions issue, but not at all, crystal reports is just crap.

my advice is don't use crHtmlText


Thursday, 30 June 2005 15:50:42 (GMT Daylight Time, UTC+01:00)  #    Comments [0]  Asp.Net

IIS6 basic authentication (challenge/response) with PHP not working

i spent ages trying to figure this out.  i set up a secure folder on a php site, and turned off anonymous access in IIS on the folder.  i have it set to basic authentication, and given a windows user account RX permissions on the folder.  so a visitor gets prompted to login with a username/password when they try to access it.  even when i enter the correct details, i am not allowed in, i get 403.1 access denied due to ACL or whatever.

i found out it was because i hadn't given my new user read/execute permissions on the php-cgi.exe file in the PHP folder.  this solved it. hope this helps someone out there who runs into the same problem.


Thursday, 30 June 2005 15:48:38 (GMT Daylight Time, UTC+01:00)  #    Comments [1]  Windows Server

# Tuesday, 24 May 2005
A few useful functions for importing and exporting data in asp.net. Excel, PDF, Datasets, DataGrids, etc.

the last few web projects i've been working on have had a lot of import/export requirements, and i've put together a class library that contains the functionality outlined below.  A lot of it i have collected from newsgroups and modified to my own purposes.

Import an Excel File

Imports all the worksheets in an excel file into a .Net dataset, with one datatable for each work sheet. Uses the ADOX COM component (reference Microsoft.ActiveX extensions 2.8 in VS, and adodb) and OleDb.  The reason i chose to use ADOX was because you need to know the worksheet names in the excel file if you're to query them with OleDb, and this isn't possible in my case.  So i use ADOX to iterate through the table names and create a new ADO.Net DataTable for each one.
I found some problems with excel documents that appeared to contain empty columns past the used range of cells, but OleDb complained about "Too many fields defined", which i presume is because it interprets all the excel columns that go on from A to XYZ or whatever, as proper columns, when we're only interested in using the used range of cells.  To overcome this, I opened the excel file and copied the range of cells into a new worksheet and deleted the old one, and it worked fine. 

Sample usage (with a Html File Control called 'fileToUpload' on the page):  

This sample code below uses the ImportExcel method in the class library code, to have the user browse to the excel file, and then upload it to the server and import it into a DataSet.

string fileName = ((System.Web.UI.HtmlControls.HtmlInputFile)this.fileToUpload).PostedFile.FileName;
if(fileName != "")
{
   try
   {
      // upload the excel file to a temp directory (needs write permissions)
      string uploadPath = Server.MapPath("/Temp/" + fileName.Remove(0,fileName.LastIndexOf("\\") + 1)));
      ((System.Web.UI.HtmlControls.HtmlInputFile)this.fileToUpload).PostedFile.SaveAs(uploadPath);

      // load the excel file into a dataset for processing
      DataSet ds = ImportExcel.ImportExcel(uploadPath);

Export Crystal Report To PDF

Converts a crystal report object into a PDF document that opens in Adobe Acrobat Reader.

Export a DataTable or DataView to Excel

This export method is similar to the common technique of binding a dataset to a datagrid/gridview and rendering the contents to produce a HTML table that Excel can understand. However the datagrid approach is not reliable if the data contains html characters, e.g. < or >, it produces invalid XML, which causes problems in Excel and OpenOffice. An alternative approach is to derive a GridView control that automatically sets HtmlEncode = true on all the BoundColumns, but this can produce very bloated output where non ASCII characters are represented and Excel will not decode the HtmlEncoded text.  I found the simplest approach is to parse the dataview and write out an XHTML table. This way the output is guaranteed to be valid XHTML, and compatible with Excel and OpenOffice (use the HtmlDocument filter when opening the file). 

In case you are worried about the performance of traversing the data like this, don't be, because it is sure to be less code than what happens inside the DataGrid class :)

Export a DataTable or DataView to CSV

this is similar to the above approach of parsing through the data and outputing the delimiters appropriately. there are regular expression based approaches, which i have tried before, but i found them unreliable when dealing with a complex character set, especially when trying to output in a format that both OpenOffice and Excel will be able to open.  This way i know i can trust, and it is lightning fast as well.

Comments?

if you have any questions on how to use it, or if you find bugs, or even better if you have some improvements... post a comment below. 
Enjoy.  Tim.

The Code

using System;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Text;
using System.Text.RegularExpressions;
using System.Web;
using System.Web.Caching;
using System.Web.Security;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace Tim.Library.WebForms
{
    /// <summary>
    /// Provides functionality to import and export datasets, datagrids, excel files etc.
    /// </summary>
    public class ImportExport
    {
        public enum ExportFormat{Excel, CSV};

        /// <summary>
        /// Imports all the worksheets in an excel file into a dataset,
        /// with one datatable for each sheet.
        /// </summary>
        public static DataSet ImportExcel(string path)
        {
            ADOX.CatalogClass cat = new ADOX.CatalogClass();
            // create an ADODB connection to use with the catalog
            ADODB.ConnectionClass connAdox = new ADODB.ConnectionClass();
            string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"" + path + "\";Extended Properties=Excel 8.0;";
            // open the excel ADOX connection to get the table names
            connAdox.Open(connectionString, "admin","",0);
            cat.ActiveConnection = connAdox;
            DataSet ds = new DataSet();
            // create an OleDb connection to get data into ADO.Net
            OleDbConnection connOleDb = new OleDbConnection (connectionString);
            connOleDb.Open();
            foreach(ADOX.Table t in cat.Tables)
            {
                try
                {
                    string name = t.Name.Trim('_');
                    if(ds.Tables.Contains(name))
                        continue;    // avoid duplicate worksheet names... strange behaviour where multiple tables were added when only one sheet existed
                    OleDbCommand cmdSelect = new OleDbCommand (@"SELECT * FROM [" + name + "]", connOleDb);
                    OleDbDataAdapter dba = new OleDbDataAdapter();
                    dba.SelectCommand = cmdSelect;
                    DataTable dt = new DataTable(name);
                    dba.Fill(dt);
                    ds.Tables.Add(dt);
                }
                catch(Exception ex)
                {    
                    throw ex;
                }
            }
            connOleDb.Close();                
            connAdox.Close();
            return ds;
        }

        /// <summary>
        /// Opens a PDF window containing the specified crystal report object
        /// The Crystal DLLs must be deployed with the web app for this to work.
        /// </summary>
        /// <param name="rpt">The report object</param>
        /// <param name="filename">Should include include .pdf</param>
        public static void ExportCrystalReportToPDF(CrystalDecisions.CrystalReports.Engine.ReportClass rpt, string filename)
        {
            MemoryStream stream = (MemoryStream)rpt.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);
            byte[] bytes = new Byte[stream.Length];
            stream.Read(bytes, 0, (int)stream.Length);
            stream.Close();
            HttpResponse response = HttpContext.Current.Response;
            response.Clear();
            response.ClearContent();
            response.ClearHeaders();
            response.Buffer= true;
            response.ContentType = "application/pdf";
            response.AddHeader("Content-Disposition", "attachment;filename=\"" + filename + "\"");
            response.BinaryWrite(bytes);
            response.End();
        }


/// <summary>
/// This method is an overload for the DataView version of the same name.
/// </summary>
public static void DataTableToCsv(DataTable dt, string filename, bool WriteToResponse)
{
DataView dv = new DataView(dt);
DataViewToCsv(dv, filename, WriteToResponse);
}

/// <summary>
/// Parses a dataview into a CSV format. I always use tab separated columns, with \n separated rows.
/// </summary>
/// <param name="dv">The data source</param>
/// <param name="filename">If WriteToResponse is true, this must be a file name, otherwise a full path+file name to save the file to</param>
/// <param name="WriteToResponse">if true, Response.Writes the output to the client browser,
/// otherwise writes the contents to the specified file path</param>
public static void DataViewToCsv(DataView dv, string filename, bool WriteToResponse)
{
char ColDelim = '\t';
char RowDelim = '\n';

using(StringWriter sw = new StringWriter())
{
// output the header row
foreach(DataColumn dc in dv.Table.Columns)
sw.Write(CsvEscape(dc.ColumnName) + ColDelim);
sw.Write(RowDelim);

foreach(DataRowView dr in dv)
{
foreach(object o in dr.Row.ItemArray)
sw.Write(CsvEscape(o.ToString()) + ColDelim);
sw.Write(RowDelim);
}

if(WriteToResponse)
{
HttpResponse response = HttpContext.Current.Response;
response.Clear();
response.Charset = System.Text.UTF8Encoding.UTF8.WebName;
response.ContentEncoding = System.Text.UTF8Encoding.UTF8;
response.AddHeader("Content-Disposition", String.Format("attachment; filename=\"{0}\";", filename));
response.ContentType = "text/txt";
response.Write(sw.ToString());
response.End();
}
else
{
File.WriteAllText(filename, sw.ToString());
}
}
}

/// <summary>
/// Strips out any row/col delimeters. This could be slightly destructive but not important in my case :)
/// </summary>
public static string CsvEscape(string s)
{
return Regex.Replace(s, "\r|\n|\t", "");
}

/// <summary>
/// This method is an overload for the DataView version of the same name.
/// </summary>
public static void DataTableToXhtmlTable(DataTable dt, string filename, bool WriteToResponse)
{
DataView dv = new DataView(dt);
DataViewToXhtmlTable(dv, filename, WriteToResponse);
}

/// <summary>
/// This export method is similar to the common technique of binding a dataset to a datagrid/gridview
/// and rendering the contents to produce a HTML table that Excel can understand. However the datagrid
/// approach is not reliable if the data contains html characters, e.g. < or >, it produces invalid XML,
/// which causes problems in Excel and OpenOffice.
/// An alternative approach is to derive a GridView control that automatically sets HtmlEncode = true on
/// all the BoundColumns, but this can produce very bloated output where non ASCII characters are represented
/// and Excel will not decode the HtmlEncoded text.
/// I found the simplest approach is to parse the dataview and write out an XHTML table. This way the
/// output is guaranteed to be valid XHTML, and compatible with Excel and OpenOffice (use the HtmlDocument filter).
/// </summary>
/// <param name="dv">The data source</param>
/// <param name="filename">If WriteToResponse is true, this must be a file name, otherwise a full path+file name to save the file to</param>
/// <param name="WriteToResponse">if true, Response.Writes the output to the client browser,
/// otherwise writes the contents to the specified file path</param>
public static void DataViewToXhtmlTable(DataView dv, string filename, bool WriteToResponse)
{
using(StringWriter sw = new StringWriter())
{
sw.Write("<table border=\"1\">\n");

// output the header row
sw.Write("<tr>\n");
foreach(DataColumn dc in dv.Table.Columns)
sw.Write("<th>{0}</th>\n", XmlEscape(dc.ColumnName));
sw.Write("</tr>\n");

foreach(DataRowView dr in dv)
{
sw.Write("<tr>\n");
foreach(object o in dr.Row.ItemArray)
sw.Write("<td>{0}</td>\n", XmlEscape(o.ToString()));
sw.Write("</tr>\n");
}
sw.Write("</table>\n");

if(WriteToResponse)
{
HttpResponse response = HttpContext.Current.Response;
response.Clear();
response.Charset = System.Text.UTF8Encoding.UTF8.WebName;
response.ContentEncoding = System.Text.UTF8Encoding.UTF8;
response.AddHeader("Content-Disposition", String.Format("attachment; filename=\"{0}\";", filename));
response.ContentType = "application/vnd.ms-excel";
response.Write(sw.ToString());
response.End();
}
else
{
File.WriteAllText(filename, sw.ToString());
}
}
}

/// <summary>
/// Replace < & > characters with their xml escaped equivalents
/// </summary>
public static string XmlEscape(string s)
{
s = Regex.Replace(s, "<", "&lt;");
s = Regex.Replace(s, ">", "&gt;");
s = Regex.Replace(s, "&", "&amp;");
return s;
}
}
}

Tuesday, 24 May 2005 23:45:00 (GMT Daylight Time, UTC+01:00)  #    Comments [7]  Asp.Net

# Wednesday, 18 May 2005
HowTo: set up disk status monitoring (i.e. for a raid array) and send results by email

background

i have a raid 1 array on my server, and i was surprised to see no easy solution to setting up an email alert if one of the disks should fail.  luckily it has never happened but apparently windows doesn't even pop up a task-bar alert if it happens. 

what doesn't work

i tried the Windows performance monitoring and alerts, and while it can tell you the average read/writes per second, it can't tell you how many disks are online.  so i looked into WMI which has an API for hard disks, but i encountered a problem with the API not returning the information it is supposed to for disk status.

enter DiskPart.exe

then i discovered DiskPart.exe, a powerful disk management utility bundled with windows server 2003, i think you can get it for server 2000 too with an admin pack or something. 
you can run DiskPart.exe from the command prompt, type: "Select Disk 0" then hit return, next type "Detail Disk" and hit return.  you should see a list of all the volumes on the disk, along with the disk status "Healthy".
In the case of a raid array, i found it more useful to run the following commands:

select disk 0
select volume 0
detail volume

this outputs all the disks that make up the volume (Disk 0 and 1, in my case), and their status. 
Fortunately, you can pipe the output of DiskPart to a text file, and you can also tell DiskPart to run a list of commands from a script, so the whole thing can be automated as a scheduled task in Windows.  I also wrote a simple c# console app to send the contents of the output file in an email.

The .cmd file i have scheduled contains the following lines:

@diskpart /s c:\Scripts\disk_part_commands.txt > c:\scripts\disk_status.txt
@SendMail server@whatever.ie tim@whatever.ie "Server Disk Status" c:\scripts\disk_status.txt

The SendMail program takes in the following parameters: from address, to address, email subject, text file path.  it is hard coded to use the localhost mail server which you can change if you want in the source code (.Net C#).

Download the send mail console app: SendMail.exe (16 KB)

Download the send mail source code: SendMail.txt (1.26 KB)

Taking it further

if i was really serious about it, i would parse the text file and read in the status directly, and only send the email if the status is something other than 'Healthy', but i think it's nice to get an email once a week anyway from the server, reporting it's disk status and letting you know it is still alive.  if somebody does write a little program to parse the output, you could post it here as a comment, that would be great.

i run the script every week, so in the worst case it could operate for 7 days with a dead drive, and the chance is very slim that the other drive will fail within this time.


Wednesday, 18 May 2005 18:39:12 (GMT Daylight Time, UTC+01:00)  #    Comments [2]  Windows Server

# Wednesday, 11 May 2005
Fix: Forms authentication redirects to a bogus default.aspx page, with RedirectFromLoginPage()

hi,
i've read a lot of posts on microsoft.public.dotnet.framework.aspnet.security about people who ran into problems using forms authentication, and the RedirectFromLoginPage() method, which always redirects to a default.aspx.  this is a big problem if you use sub-folders that don't have a default.aspx page, as in my case.
i read some posts that suggested manually Response.Redirecting the user to the url in the querystring, but actually this is incorrect because Forms Auth puts the default.aspx in that querystring even if the user wasn't at a page called default.aspx. 

i put together a simple solution to get the redirecting to work properly, and am posting it here for future reference:

  • The Login page (Login.aspx) must be set up to read the HTTP_Referrer, and add it to the ViewState in the first Page_Load on that page.
  • In the btnLogin_Click event on Login.aspx, the SetAuthCookie() event should be called, and the user should be Response.Redirected to the referrer value in the viewstate.
  • So you ignore the querystring that Forms Authentication adds on to the Login page.

Here is sample code:


*****************
Login.aspx
*****************

private void Page_Load(object sender, System.EventArgs e)
{
 if(!IsPostBack)
  ViewState["originalUrl"] = Request.UrlReferrer.AbsoluteUri;
}

private void btnLogin_Click(object sender, System.EventArgs e)
{
 string originalUrl = ViewState["originalUrl"];
 if(originalUrl == null || originalUrl == "") // in case the viewstate is corrupt, use default.aspx by 'default'
  originalUrl = "default.aspx";
 
 // do your password checking here
 // if it's all ok then...
 FormsAuthentication.SetAuthCookie(username, false);
 Response.Redirect(originalUrl, true);
}

Wednesday, 11 May 2005 13:33:48 (GMT Daylight Time, UTC+01:00)  #    Comments [0]  Asp.Net

# Tuesday, 26 April 2005
An SQL Query to find duplicate values
SELECT <KEY_COLUMN>, COUNT(*)
FROM <TABLE> GROUP BY <KEY_COLUMN> HAVING COUNT(*) > 1

Tuesday, 26 April 2005 17:00:35 (GMT Daylight Time, UTC+01:00)  #    Comments [0]  Database

# Monday, 04 April 2005
MS Access: using the LIKE function with a parameter
I wanted to do something like SELECT * from MyTable WHERE [Action] LIKE 'Hello%' but using a parameter instead of hard-coding the 'Hello' into the query. using VS and OleDb, the syntax for this wasn't obvious, what finally works is: SELECT * from MyTable WHERE ([Action] LIKE ? + '%')
Monday, 04 April 2005 17:51:14 (GMT Daylight Time, UTC+01:00)  #    Comments [0]  Database

# Sunday, 03 April 2005
MTB: Excellent trail in Limerick - Keeper Hill + Slieve Felim Way

hi,
i went exploring on a new route yesterday and it was by far the most exciting trail i've been on for a long time.  Map and Stats below.

Stats

  • Route length: 76k from Limerick city (Ireland)
  • Time taken: 8 hours including about 7x5-min rests, a half hour nap at the top, and about 10 camera stops.  so you can easily knock an hour and a half of this time.
  • Climb: 700m to Keeper Hill Summit
  • Terrain: About 50km of this trip is road, but it's the fast part and i think the 20k on trails is really worth it.  you could drive to newport and cut out the boring leg between limerick and newport.
  • Water needed: 2.5l energy drink was just about enough for me
  • Food needed: 3 snickers + banana + raisins

Map

Directions

The map has red arrows marking the route i took for the way up, and pink arrows for the way down. In case they don't make sense, here are the english directions:

  • From Limerick, take the N7 dublin road out as far as the roundabout on the new dual carriage way stretch. Take the second exit sign-posted newport.
  • Go straight through newport and keep on the main road and go past the entrance to Tooreenbrien woods. After that, take the next left up a small road.  (marked on map with a red arrow).
  • Keep going and turn right near the end of the road (down a hill) and you should link up with the Slieve Felim way, marked with the hill-walker signs.
  • Then just follow the hill walker signs up and across, and over the hill.
  • There is an extremely fast (and mucky when i did it) descent to Toor (a village apparently).
  • Go right on the main road for almost a kilometer. you get a good view of Keeper Hill from there. Turn left just at the handball court, following the hillwalker signs.
  • The Slieve Felim way doesn't actually take you to the summit, so follow the map to choose one of the paths that leads there.  I had to walk the last 150m because the path was very loose and stoney.
  • I choose the quickest way home once i had reached the top, and it is the fastest descent i've ever been on. the quality of the paths was pretty good when i was there, so i was able to clock 58kph. 
  • you can see with the pink arrows which route i took home.

if you have any corrections suggestions, post your comments below.


Sunday, 03 April 2005 10:23:40 (GMT Daylight Time, UTC+01:00)  #    Comments [1]  Outdoors

# Wednesday, 23 March 2005
SQL left join with a where clause produces strange results

I was doing a straight forward left join to include all the records of a table, regardless of whether they had records in the related table.  However, i added a where clause, which produced very strange results.  Some of the foreign records were not included in the results, specifically those that had no related records at all in the main table.  This was very frustrating to pin down, but i finally found out that the where clause in a left or right join should not use the 'where' syntax.

instead, it should use:

SELECT
    SuperMarketGroups.GroupName,
    SuperMarketGroups.ID, 
    ProductsInGroups.GroupProductCode, 
    ProductsInGroups.MinimumStoreRating, 
    ProductsInGroups.ProductID, 
    (IIf([ProductID] Is Null,'False','True')) AS GroupIncluded
FROM 
    SuperMarketGroups LEFT JOIN 
    ProductsInGroups ON
        (SuperMarketGroups.ID = ProductsInGroups.GroupID 
            AND (ProductsInGroups.ProductID Is Null OR ProductsInGroups.ProductID=?)
        )

note that there is no 'where' clause.  the extra condition is specified with the join columns. this solved my problem perfectly.


Wednesday, 23 March 2005 16:54:28 (GMT Standard Time, UTC+00:00)  #    Comments [4]  Database

Howto: Export a dataset to Excel (c# / asp.net)

In my web applications, i occassionaly need to allow the user to export a dataset as an excel file.  I was using a control written by Prashant Nayak posted on Code Project but he released a new version which was problematic for me, so i looked at other solutions.

obinna igbokwe  from  www.dedicatedsolutions.co.uk posted a good approach which creates a DataGrid object and binds it to the dataset, and then Renders the output of the control to the HttpResponse stream.  This works very well.  I have adapted his code to C# and added an option to specify a filename for the excel file.

Here is the code:

using System;
using System.Data;
using System.IO;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace Whatever
{
 /// 
 /// This class provides a method to write a dataset to the HttpResponse as
 /// an excel file. 
 /// 
 public class ExcelExport
 {
  public static void ExportDataSetToExcel(DataSet ds, string filename)
  {
   HttpResponse response = HttpContext.Current.Response;
   
   // first let's clean up the response.object
   response.Clear();
   response.Charset = "";
   
   // set the response mime type for excel
   response.ContentType = "application/vnd.ms-excel";
   response.AddHeader("Content-Disposition", "attachment;filename=\"" + filename + "\"");
   
   // create a string writer
   using (StringWriter sw = new StringWriter())
   {
    using (HtmlTextWriter htw = new HtmlTextWriter(sw))
    {
     // instantiate a datagrid
     DataGrid dg = new DataGrid();
     dg.DataSource = ds.Tables[0];
     dg.DataBind();
     dg.RenderControl(htw);
     response.Write(sw.ToString());
     response.End(); 
    }
   }
  }
 }
}

Wednesday, 23 March 2005 16:47:02 (GMT Standard Time, UTC+00:00)  #    Comments [72]  Asp.Net | Database