This week I wanted to get my ASP.NET web application to create a text file and return it to the browser. The catch was, the text files are created by some pretty gnarly FoxPro code (sourcing Fox data). The solution seems to be to use COM interop. It’s a lot easier to rework that vintage FoxPro procedural code into a VFP COM server than it is to re-implement it using DataSets in C# (or VB for that matter).

Rick Strahl has a good article discussing this, and there is quite a lot of “high level” information on COM interop out there on the web also. Briefly, here is what I did:

I re-packaged the pertinent FoxPro code into a class method of a COM server. I tested this out in the VFP Command window:

x = CREATEOBJECT("MyServer.MyClass")
_cliptext = x.GetTextDoc("123")

Now, in the web application I wanted a hyperlink that would return the text document to the browser. So I was looking to implement a URL like this:

<a href="CreateTextDoc.aspx?param=1234">Create Text Document</a>

Now, CreateTextDoc.aspx is basically all code-behind – there is no HTML code, just the directives:

<%@ Page language="c#" 
         Codebehind="CreateTextDoc.aspx.cs" 
         AutoEventWireup="false" 
         Inherits="MyCompany.MyProject.MyBasePage" 
         ASPCOMPAT=true
%>

That last page directive is specifically for pages that instantiate COM objects. Some people say it’s optional, only necessary when you are embedding the object into the page directly with <object> tags. Others say that you should use it even if you are using the early binding wrapper classes. I figured, better safe than sorry.

Now, the code-behind:

  using System;
  using System.Web;
  using MyServer;
  :
  private void Page_Load(object sender, System.EventArgs e)
  {

      // get the parameter:
      //
      string cParam = "";
      cParam = Request["param"] ;

      // We're returning a text file:
      //
      Response.ContentType = "text/plain";

      // This forces nicer behavior in IE, prompting to download or open as text.
      // also the filename is now "1234.txt" instead of "CreateTextDoc.aspx"
      //
      Response.AddHeader("content-disposition","attachment; filename="+cParam+".txt" ) ;
		
      // Create the COM server wrapper:
      //
      MyServer.MyClass x = new MyServer.MyClassClass() ;

      // Generate and return the text:
      //
      Response.Write( x.GetTextDoc( cParam )) ;

      return ;
  }

Nothing tricky there, once you understand the odd invocation of the COM server through the .NET wrapper class.

This worked a treat: In FireFox, the text file appears in the browser quite naturally. IE screwed up, however, because it appeared to be trying to recognise the plain text response as some other kind of format. Rather than do the registry hack, I added the content-disposition header to the response. Now the behavior in IE is actually preferable: I get a dialog prompting me to Open or Save As. If I Open, the text file opens in Notepad. This is better for my users – although I would have been content with the Firefox behavior (pardon the pun).

Now we get to the problem: Every time I hit that URL to generate a text file, another instance of my COM server executable was created. Ouch! If I waited long enough – about 20 minutes – they would terminate and disappear from Task Manager. But as it was, this didn’t scale well at all.

I mentioned this behavior to Rick, and he couldn’t explain why the MyServer.exe instances were hanging around. But he did have a good suggestion: “If the object is not releasing you can call Marshal.ReleaseComObject() to make sure it gets cleaned up and doesn’t wait for the GC to clean it up for you.“

This change was easy to make. First, I added the reference to the top of the file:

using System.Runtime.InteropServices;

Then, after the call to GetTextDoc(..):

      // Create the COM server wrapper:
      //
      sisserver.ArtForm x = new sisserver.ArtFormClass() ;

      // Call the Artform listing generator:
      //
      Response.Write( x.GetArtFormDoc( cArtform ) ) ;
      
      // Dispose of the COM server manually:
      // 
     System.Runtime.InteropServices.Marshal.ReleaseComObject( x );

This worked beautifully. No more hanging executables in Task Manager! My application scales again. Thanks for the pointer, Rick.

So I have this Asp.NET application that needs to run on the WEBSERVER but see files in a directory on FILESERVER out on the net. (The files are, inevitably, Foxpro tables being accessed through OleDb.)

First cut, the OleDbConnection fails to open the data (\\FILESERVER\Sharename\datafolder) with an “invalid path or file name” error. That’s because the ASP.NET application is running under the WEBSERVER/aspnet user context which does not have permission to see the network share.

“No problem,” we say. “Let’s just use the web.config file to tell Asp.NET to run under a context that does have permission to see the share:

<system.web>
    :
    <identity impersonate="true"
              userName="NETDOMAIN\net_user"
              password="secret"/>
    :
</system.web>

This works – except that every time we uploaded a new version of the .aspx files to the webserver, it threw an exception: “Cannot execute a program. The command being executed was [..]\v1.1.4322\csc.exe /noconfig …”. Basically it didn’t appear to have rights to run the compiler to do that special ASP.NET on-the-fly compilation of the classes.

Investigation seemed to indicate that it should have the appropriate access rights, but all the same, we couldn’t make it work without commenting out the <identity> specification; re-starting the IIS process; then putting it back after the successful compilation so that the network share was visible.

Second cut was to omit the <identity> override, but to bracket the OleDb call with special in-line user impersonation code using Win32 LogonUser() calls. Well that didn’t work (and when I find out why, perhaps I’ll post about it).

I found a solution to the problem on a web thread posted by shamrox (http://forums.rainbowportal.net/ShowPost.aspx?PostID=5503) which I reproduce here:

Is this error happening when you try to run your project? I guess what I am asking is that it compiles fine but when it goes to the first screen you get this error. IE would never open if it didn’t compile.

If it is what I think it has to do with the aspnet_wp. It does some weird stuff. You don’t have to restart www to fix it, all you need to do is go to task manager and [terminate the aspnet_wp.exe process]. Don’t worry it restarts automatically. That will fix the problem for now. It comes up from time to time so if there is a better way I would like to know too.

Thanks shamrox – this works! Now we can leave the <identity> switch in place in the web.config in order for my web site to “see” the network resources, and when I upload a new version of the aspxs to my web site – it will stick with the exception on the recompile but a restart of the aspnet_wp.exe process will bring it back, no worries.

I still think ASP.NET is the bomb.

I found out something interesting about the VFP OleDb driver yesterday. I’m using a DataReader to return records from a FoxPro table, and one thing about DataReaders in .NET 1.1 is that you can’t get a record count. (There’s a .RowsAffected property but that’s different, as I understand it.)

The usual thing to do is to perform a pre-select to get the record count, and use ExecuteScalar() to return the single count value:

StringBuilder sb = new StringBuilder();
sb.Append("SELECT COUNT(*) FROM customers WHERE name = '");
sb.Append(  cCustomer ) ;
sb.Append(  "'") ;

oCmd = new System.Data.OleDb.OleDbCommand( sb.ToString(), oVfpConn ) ;

int resultCount = (int) oCmd.ExecuteScalar();  

This code works fine if you’re using a SqlClient.SqlDataReader against SQL Server, but here, we get an exception: “specified cast is not valid”.

It turns out that, for the VFP OleDb source, oCmd.ExecuteScalar() returns a System.Decimal object instead of an int. After some experiments, I found this seemed to work best:

	
int resultCount = System.Decimal.ToInt32( (Decimal) oCmd.ExecuteScalar() ); 

if ( resultCount == 0 )
{
	...

This is fun!

Update: David Stevenson has a better solution: Use CAST to force the results to an Integer:

	
StringBuilder sb = new StringBuilder();
sb.Append("SELECT CAST(COUNT(*) AS Integer) FROM customers WHERE name = '");
sb.Append(  cCustomer ) ;
sb.Append(  "'") ;

oCmd = new System.Data.OleDb.OleDbCommand( sb.ToString(), oVfpConn ) ;

int resultCount = (int) oCmd.ExecuteScalar();  

Much cleaner.