.NET Interop for Visual FoxPro Applications
By Rick Strahl
http://www.west-wind.com/
Last Update:
September 23,
2002
Code for this
article:
http://www.west-wind.com/presentations/VfpDotNetInterop/VfpDotNetInterop.zip
This document covers:
Note: The code
listings in this article use C# for .NET code and Visual FoxPro for COM or
client code.
Now that .NET is here you've undoubtedly have the urge to use or
at least play with the new functionality that the platform provides.
Unfortunately migrating to .NET from Visual FoxPro (or most other development
languages) is a big step that requires a steep learning curve. Integration
between the old and the new will be crucial as a first step to provide for the
ramp up time that's needed to get up to speed on the new platform as well as
providing vital links between old and new applications. In this article Rick
looks at the most common ways that you can use to integrate logic and data
between Visual FoxPro and .NET.
Like any new development platform or environment, .NET
introduces a brand new environment and many new concepts for developing
applications. To us as developers this usually means that there's a large
learning curve involved in understanding the new platform, finding the the most efficient ways of performing common tasks and
working around the limitations of the new environment. Although Microsoft will
be pushing hard to convert as many developers to .NET as quickly as
possibility, it's obvious that the transition to an all .NET world will take a fair
amount of time to accomplish.
For this reason, it's vital that there are mechanisms
readily available to share non-managed .NET code with managed .NET code and
vice versa. If you've been reading the trade press, you've undoubtedly heard
that Web Services are one of the biggest features of .NET and one of the big
selling points of Web Services of course is the fact that they can be accessed
by many different kinds of development environments both old and new. However,
Web Services are only one of the options available for interoperating and
probably the least likely to be useful for extending the life of old applications.
As in previous updates of development environments data
access through standard data access components make it possible to directly use
data from various different data sources including Visual FoxPro. Visual FoxPro
7 sports a new OleDb driver which improves
significantly on some of the limitations of the VFP ODBC driver and makes it a
capable provider for accessing data directly from .NET applications even if
there are a number of features that are useful for .NET that are missing.
Prior to .NET, COM has been the primary interop
mechanism that applications could use to communicate amongst each other and
.NET provides extensive support for COM. You can both access COM components
from .NET and expose .NET components through COM through its Runtime Callable
Wrapper (RCW) which provides a COM wrapper around the .NET runtime.
I'll go over each of these approaches and in the process
point out some issues that you need to watch out for. As all tools geared
toward providing backwards compatibility they rarely provide all the
functionality that was provided by the original implementations, so some things
require special attention or changes to properly work in a .NET interop environment.
Although this article discusses Interop
mechanisms in general I'll focus more specifically on ASP.NET as this is likely
the most used platform of .NET technology at this point. Keep in mind that the
same principles apply to Windows Forms or even Console applications. ASP.NET
however introduces some additional considerations because it is a server based
technology and requires special considerations for performance and scalability.
So I'll use ASP.NET in this article as my vehicle to describe most of these
technologies.
Accessing VFP Data with OleDb
This hardly qualifies as an eye opening topic, but OleDB support in Visual FoxPro 7.0 has been significantly
updated over the existing ODBC implementation that makes it more realistic to use
VFP data via a generic driver, especially in an ASP.NET Web environment.
By using the OleDb driver you have
the ability to use ADO.NET and the DataSet style
implementation given some limitations that I'll discuss later on. Performance
of the driver is adequate although as we'll see still a bit of a bottleneck
compared to native performance using pure Visual FoxPro code or code loaded
from COM objects. The new OleDb provider is also
multi-threaded and can run multiple queries simultaneous, which was probably
the biggest problem with the ODBC driver when used in ASP and now ASP.NET
applications.
To demonstrate the basic principles of OleDb
access against a Fox data source I'll build a small and simplistic sample
ASP.NET Web Form applet that lets you browse and edit entries in the TASTRADE
sample database shipped with Visual FoxPro.
File Access requirements for ASP.NET
If you plan to use ASP.NET to access VFP via OleDb you have to make sure that your data directories are
properly configured to allow the ASPNET user full access to your data paths.
ASP.NET runs under this user by default although you can change the actual
account in Web.config if you choose. Whatever account
you use this account must be able to have full NTFS access rights in the data
directories where data is read from and written to. You can use Explorer and
the Security tab to accomplish this.
Simple DataSet retrieval
But before I do this let me just give you a small example
that demonstrates the basics of creating a DataSet
with VFP data on an ASP.NET form. This example consists of a very simple
ASP.NET page that contains only two controls: a label to show error information
(lblErrorMsg) and a DataGrid
(oCustList) to display the data from a query against
VFP data. The following code is the CodeBehind page
that uses a separate GetCustomerList() method on the form to perform the data retrieval into a DataSet (Listing 1).
…
using System.Data.OleDb;
namespace ASPInterOp
{
public class
SimpleDataSet : System.Web.UI.Page
{
OleDbConnection oConn = null;
DataSet oDS =
null;
protected
System.Web.UI.WebControls.DataGrid oCustList;
protected
System.Web.UI.WebControls.Label lblErrorMsg;
private void Page_Load(object sender,
System.EventArgs e)
{
if (
this.GetCustomerList("CustomerList") )
{
this.oCustList.DataSource = this.oDS.Tables["CustomerList"];
this.oCustList.DataBind();
}
}
private bool
GetCustomerList(string lcCursor)
{
// Put user
code to initialize the page here
this.oConn
= new OleDbConnection("Provider=vfpoledb.1;" +
@"Data Source=D:\Programs\vfp70\Samples\Data\testdata.dbc;" +
"Exclusive=false;Nulls=false");
try {
this.oConn.Open();
}
catch(Exception ex)
{
this.lblErrorMsg.Text = ex.Message;
return
false;
}
string
lcSQL = "select company, contact, city from customer";
OleDbDataAdapter oAdapter = new OleDbDataAdapter(lcSQL,this.oConn);
if
(this.oDS == null)
{
this.oDS
= new DataSet();
}
try
{
oAdapter.Fill(this.oDS,lcCursor);
}
catch(Exception ex)
{
this.lblErrorMsg.Text = ex.Message;
return
false;
}
finally
{
this.oConn.Close();
}
return true;
}
}
As you can see it takes a fair amount of code in the GetCustomerList()
method to do this, but the code is pretty straight forward. You start with a
connection object that is passed an OleDb connection
string that points at the VFP OleDb driver and a path
to a DBC or a free table directory (if you don't include a DBC tables are
treated as free tables).
Next a DataAdapter is created. The
DataAdapter is the 'data retrieval' object. It knows
how to talk to the data source and return the data in a specified format. The
adapter is specific to the backend used, just as the connection is. Hence the
class name is OleDbDataAdapter.
If you go against SQL Server you can use the SqlDataAdapter instead, which is
the SQL Server specific implementation that bypasses OleDb.
With VFP you have to use the OleDb adapter and
connection objects.
The adapter acts as the intermediary that's used to query
the data from the database and return a result to the application. Natively a DataAdapter returns a DataReader,
which is basically a stream of forward only data. Using the DataReader
to retrieve the data is the most efficient way to get the data if you only need
to display data and don't need any special filtering or jump through the data.
The DataReader stream is also used
internally by the DataAdapter and can be used to
create other objects which can populate their own data control objects. One of
these and probably the most important one in .NET is the DataSet.
In this case I'm using the DataAdapter's Fill() method to 'fill' the data into a DataSet
object. Notice that the DataSet is not prefixed by OleDb or Sql. This is because a DataSet is a generic object that is completely disconnected
from the parent datasource. Think of the DataSet as a container for multiple cursors – DataSets can run and hold results from multiple queries
simultaneously. So when a new query is executed you need to tell the DataAdapter what name to give the table created into the DataSet.
Once a DataSet has been created
you can access its data directly either using programmatic access or by databinding to a data aware control. Many of the user
interface controls in .NET have intrinsic support for DataSets.
Here I'm using a DataGrid to bind the data and simply
display a simple list of the data in an HTML table through the DataGrid Server Control object (oCustList)
by using:
this.oCustList.DataSource =
this.oDS.Tables["CustomerList"];
DataSets are made up of Tables,
Rows and Columns (and a few other things like Relations and Constraints). Here
I'm binding to a table in the DataSet. Tables inside
of a DataSet have an automatic data view that
represents a layout that determines how the table renders into the DataGrid. The default iew is very
basic and displays all the fields in the table in a grid format with the field
names as headers. The result is very raw, but useful for quickly seeing the
data displayed (Figure 1). To customize the view you have to set properties on
the DataGrid either in the <asp:DataGrid> tag or by setting properties on the
object. I'm not going into the details
of the datagrid here but rather focus on the data
access issues.
You can also access the data directly via code. A DataSet contains a collection of Tables, which in turn
contains a collection of Rows. The Rows then contain the individual fields. The
following snippet demonstrates:
DataRow loRow =
this.oDS.Tables["CustomerList"].Rows[0]; // first data row
int lnRowCount =
this.oDS.Tables["CustomerList"].RowCount;
string lcCompany = loRow["company"].ToString();
decimal MaxOrdAmt = (decimal)
loRow["maxordamt"];
Note that you have to cast the data items to a specific type
as the actual fields are objects. You can use ToString() on strings, but
you'll need to use casts for most other datatypes.
Take 2: A little more generic
The code above is fine, but it gets repetitive in a hurry,
so rather than write it each time it's a good idea to isolate some of the data
access code into a separate business object layer. This is overly simplistic
for this article's purposes, but demonstrates the basics of what it takes to
simplify data access and separate the business logic from the ASP.NET page.
The following example creates a small ASP.NET application
that displays a list of customers and lets you browse through the list and view
and edit each of the customers. You can also add new customers. I'll
demonstrate querying, updating and finally adding a customer using a stored
procedure in this example all using the simple business object template.
To do this I use a very simplified business object called tasCustomer that's subclassed
from aBusObj, which is a very minimal helper business
object class that simplifies some of the queries and update operations.
Figure 1 –
This customer form browses and allows editing of customers from the TasTrade sample application.
This Web Form performs two separate data tasks: Retrieval of
the list displayed and the update operations on the individual customers.
Since this is ASP.NET I'll use DataSets
for these operations and also take advantage of the fact that the form's viewstate can persist the list for
me without going back to the database. In essence the form loads the list of
customers only once, and then persists the list as
part of the form's viewstate that is passed back and
forth over the wire. This view state also includes settings of the list such as
the currently selected item(s). This works well for smallish lists like this
one, but I wouldn't recommend this same approach for long lists as you are
essentially trading bandwidth for server resources against a database
operation. If you don't use ViewState you have to
manually retrieve the data each time for the list, and then set the selected
item based on the form variable returned from the list (Request.Form("oCustomerList")).
Ok, let's see how we retrieve this data using the VFP OleDb driver. As I mentioned I'm using a simple business
object implementation to help with my work here. In the ASP.NET form (CustomerForm.aspx) the first thing I do is run an Execute
to return the list if the form is not in PostBack
mode. PostBack mode for an ASP.NET form means that
the form is not being loaded for the first time and returning values from the
form – in other words the user has clicked a button. The first time through the
form will not be in PostBack mode and we'll have to
run the query. To do so I use in the form's Page_Load event method (Listing 2 from CustomerForm.aspx.cs).
private void Page_Load(object sender, System.EventArgs e)
{
// *** Clear the
Error Message always
this.lblErrorMessage.Text = "";
this.oCustomer =
new tasCustomer();
if
(!this.IsPostBack)
{
if
(this.LoadCustomerList()) {
// ***
Force first item to display in edit view
if
(this.oCustomerList.Rows > 0)
{
this.oCustomerList.SelectedIndex = 0;
this.ShowCustomer();
}
}
}
}
protected bool LoadCustomerList()
{
int lnCount =
this.oCustomer.Execute(
"select company,
cust_id from customer order by Company",
"customer");
if
(this.oCustomer.lError)
{
this.lblErrorMessage.Text = this.oCustomer.cErrormsg;
return false;
}
else
{
// *** Databind the DataTable
this.oCustomerList.DataSource =
this.oCustomer.oDS.Tables["customer"];
this.oCustomerList.DataTextField = "company";
this.oCustomerList.DataValueField = "cust_id";
this.oCustomerList.DataBind();
}
return true;
}
This code fires off in the Page_Load
of the ASP.NET page and then calls the LoadCustomerList() method to actually perform the data retrieval and binding
of data tied to the list. The Execute method on the business object handles running
the query. The business object provides several methods such as Open() and Execute() that remove some of the code that is
required in setting up a DataSet and returns the
actual DataSet as an object member oDS. oCustomer.oDS
holds the result and as you can see in the code above the "customer"
result table is then bound to the list box. Both a display field and a key
field are bound – we'll use the key field later on to retrieve a specific
customer.
The key methods of the base business object are set up as
shown in Listing 3 (from tasCustomer.cs).
public class aBusObj
{
public string
cConnectString = "";
/// these
properties along with Open,Close,Load,Save really belong
/// into a data
wrapper or high level business object. For compactness'
/// sake I've
included them as part of the class here.
public
OleDbConnection oConn = null;
public
DataSet oDS = null;
public string
cErrormsg =
"";
public bool
lError = false;
protected virtual bool Open()
{
/// create if it
doesn't exist already
if (this.oConn ==
null)
{
try
{
this.oConn
= new OleDbConnection(this.cConnectString);
}
catch(Exception e)
{
this.SetError( e.Message );
return
false;
}
}
/// check if
connection is open - if not open it
if
(this.oConn.State != ConnectionState.Open)
{
try
{
oConn.Open();
}
catch(Exception e)
{
this.SetError( e.Message );
return
false;
}
}
/// make sure our
dataset object exists
if (oDS == null)
oDS = new
DataSet();
return true;
}
protected virtual bool Close()
{
if (oConn.State
== ConnectionState.Open)
try
{
oConn.Close();
}
catch(Exception e)
{
this.SetError( e.Message );
return
false;
}
return true;
}
public virtual int Execute(string lcSQL, string lcCursor)
{
if (!this.Open())
return 0;
OleDbDataAdapter
oDSAdapter = new OleDbDataAdapter();
oDSAdapter.SelectCommand = new OleDbCommand(lcSQL,oConn);
// *** remove the
table if it exists - fail and ignore
try
{
oDS.Tables.Remove(lcCursor);
}
catch(Exception
e) { } // Ignore the error
try
{
oDSAdapter.Fill(oDS,lcCursor);
}
catch(Exception
e)
{
this.SetError(e.Message);
return 0;
}
return
oDS.Tables[lcCursor].Rows.Count;
}
// … more methods here not included – see source code
} // end of aBusObj
The Open() and Close() metods basically deal with setting up the connection and
making sure that the connection strings are valid. The Execute()
method then runs the specified query and creates a cursor in the business
object's DataSet object. If you'll recall a DataSet can contain more than a single table, so if you run
multiple queries they can all be stored in a single dataset. If you have
multiple business objects they can also potentially share the same DataSet object reference. Note that Execute tries to remove
the table name before creating the result so that if a cursor by the same name
exists already it's overwritten – otherwise an exception would occur.
With this code in place running a query then becomes as easy
as calling the Execute method with a SQL statement. In this case the SQL
statement retrieves the customer list, and then assigns the result table to the
listbox of the TasTrade
customer form.
Note that all the methods in the aBusObj
class are virtual, which means they can be overridden by the implementation
classes. For example, the Open method is overridden in the tasCustomer
class to set properties on the VFP OleDb session. In
order to do this you have to actually run a command against the VFP data source
using language commands such as SET EXCLUSIVE OFF, SET DELETED ON etc (Listing 3.1
from tasCustomer.cs).
protected override bool Open()
{
if (base.Open()) // call the Parent class' Open() (no laughing
please! <g>)
{
try
{
OleDbCommand oCommand = new OleDbCommand();
oCommand.Connection = this.oConn;
oCommand.CommandText = "SET NULL OFF\r\nSET DELETED ON";
oCommand.ExecuteNonQuery();
}
catch(Exception ex)
{
this.SetError(ex.Message);
return
false;
}
return true;
}
return false;
}
Databinding record data fields
Once the list has been loaded the next step is to retrieve
an individual customer for display. To accomplish this task I use ASP.NET databinding, which is somewhat limited if you compare it to
databinding in desktop applications. Keep in mind
ASP.NET databinding is really a one way affair meant
only to bind data for display – to read data back you have to explicitly
extract the data from the controls that it's held in.
To do the databinding I use a
combination of the Server control expressions (<%# %> syntax) and code in
the ShowCustomer method. ShowCustomer() get fired from
the form's SelectedIndex_Changed event method
(Listing 4 from CustomerForm.aspx.cs).
private void oCustomerList_SelectedIndexChanged(object
sender,EventArgs e)
{
this.ShowCustomer();
}
protected string GetIdFromList()
{
if
(this.oCustomerList.SelectedItem == null)
{
this.lblErrorMessage.Text = oCustomer.cErrormsg;
return null;
}
return
this.oCustomerList.SelectedItem.Value;
}
private bool ShowCustomer()
{
// *** Figure out
which item is selected
string lcID =
this.GetIdFromList();
// *** Load that
customer
if
(!this.oCustomer.LoadCustomer(lcID)) {
this.lblErrorMessage.Text =
oCustomer.cErrormsg;
return false;
}
// *** Force
databinding to occur to the ASP Server Controls
this.dtrCurrent =
this.oCustomer.oDS.Tables["Customer"].Rows[0];
this.DataBind();
return true;
}
The business object LoadCustomer
method is fired which does little more than run an Execute and retrieve the
single record using the existing business object (Listing 5 from tasCustomer.cs).
public bool LoadCustomer(string lcID)
{
int lnCount =
this.Execute("select * from customer where cust_id='" +
lcID +
"'","Customer");
if (this.lError)
{
return false;
}
return true;
}
This single record table row is then assigned to the dtrCurrent property of the form, which in turn is databound to the ASP.NET server controls.
DataBinding in .NET to textbox
controls is one way which means that data is bound for display only. This makes
sense given the stateless nature of Web requests and display and update
happening usually in the same request.
Databinding to text box controls
is accomplished by setting the Value tag of the ASP.NET control in the ASP.NET
display page. For example the Company field is bound as follows in CustomerForm.aspx:
<asp:TextBox id="txtCompany"
runat="server"
Text='<%#
this.dtrCurrent["company"] %>'>
</asp:TextBox>
Note that databinding is not
automatic – it doesn't occur until you explicitly call the DataBind
method of the individual control or the Page object. Above the ShowCustomer()
method calls the DataBind() method of the page (this.DataBind())to force all the controls to bind. This is
good too, because of the way that the form works. First the list loads, and no
items are available yet. An item is loaded only after the list is loaded and
the first item can be selected.
This is important to understand as you can't bind to a datasource that is not loaded yet or else the page will
fail. ASP.NET gives you full control on when the actual data is bound while
allowing you to set the binding expression any time. Hey, that's a request I've
had for VFP forms forever and it actually works here.
Updating the data
As I mentioned above ASP.NET data is not really databinding in the traditional sense because it's only one
way. So in order to update data you have to read the values explicitly from the
controls and back into the underlying data source. Here I read the data back
into the DataRow object after pressing the save button
(Listing 6 from CustomerForm.aspx.cs).
private void btnSave_Click(object sender, System.EventArgs
e)
{
string lcID =
this.GetIdFromList();
if ( !this.oCustomer.LoadCustomer(lcID) )
{
this.lblErrorMessage.Text
= oCustomer.cErrormsg;
return;
}
/// Update the
row contents
DataRow oRow =
this.oCustomer.oDS.Tables["Customer"].Rows[0];
oRow["company"] = this.txtCompany.Text;
oRow["address"] = this.txtAddress.Text;
oRow["city"]
= this.txtCity.Text;
oRow["MaxOrdAmt"]
= Convert.ToDecimal(this.txtCredit.Text);
// … more fields
omitted here
/// Call the bus
object to save the data to disk
this.oCustomer.SaveCustomer();
}
The code then defers to the business object to save the data
back to the VFP data source.
Up to now our VFP datasource has
had no issues in interaction with ADO.NET. Unfortunately for any kind of data
updates the VFP OleDb provider has a few missing
features that make an otherwise simple process a lot more code intensive than
it is say with a SQL Server datasource.
ADO.NET supports the concept of database schemas being
retrieved along with the data when a query is run. So, normally when you run a
query against a datasource the Primary key info is
retrieved. This makes it possible to automatically update a datasource
because the DataAdapter can use the schema
information to decide how to update the DataSource
based on the key info.
Unfortunately the VFP OleDb
provider in its current incarnation does not support this information and
you're required to manually create INSERT and UPDATE statements. The following
work with SQL Server, but not with
VFP. With SQL Server you can do the following:
OleDbDataAdapter oDSAdapter = new
OleDbDataAdapter("select * from customer",
this.oConn);
/// This builds the Update/InsertCommands for the data
adapter
OleDbCommandBuilder oCmdBuilder = new
OleDbCommandBuilder(oDSAdapter);
lnRows = oDSAdapter.Update(this.oDS,"Customer");
The OleDbCommandBuilder is an
object that can automatically build SQL INSERT/UPDATE/DELETE commands if the datasource supports schema information. But this doesn't
work with the VFP OleDb driver as this information is
not provided.
To work around this you have to manually assign the
INSERT/UPDATE/DELETE statements. The following example demonstrates the UPDATE
command (limited to a couple of fields).
string lcSQL =
"UPDATE
customer SET company='" + oRow["company"].ToString() + "' " +
"where
cust_id='" +oRow["cust_id"]+ "'";
oDSAdapter.UpdateCommand = new
OleDbCommand(lcSQL,this.oConn);
Once that's done the oDSAdapter.Update() method call succeeds.
Of course if you have to go through all the trouble of
updating the UPDATE command manually you might as well just execute the UPDATE
command manually with less code without requiring the DataSet
to be directly involved at all:
string lcSQL =
"UPDATE
customer SET company='" + oRow["company"].ToString() + "' " +
"where
cust_id='" +oRow["cust_id"]+ "'";
OleDbCommand oCommand = new
OleDbCommand(lcSQL,this.oConn);
oCommand.ExecuteNonQuery();
Schema retrieval functionality is scheduled to be provided
in the next version of the VFP OleDb provider, but
currently we're stuck with having to manually write our own UPDATE/INSERT
statements. Putting all of this together the SaveCustomer
method looks like Listing 6.1 (tasCustomer.cs).
public bool SaveCustomer()
// tasCustomer object
{
if (!this.Open())
{
return false;
}
OleDbDataAdapter
oDSAdapter = new
OleDbDataAdapter("select * from customer",this.oConn);
// This builds
the Update/InsertCommands for the data adapter
// THIS DOES NOT WORK WITH VFP DATA!
// OleDbCommandBuilder
oCmdBuilder = new OleDbCommandBuilder(oDSAdapter);
DataRow oRow =
this.oDS.Tables["Customer"].Rows[0];
string lcSQL =
"UPDATE
customer " +
"SET
company='" + oRow["company"].ToString() + "'," +
"contact='" + oRow["contact"].ToString() +
"'," +
"address='" + oRow["address"].ToString() +
"'," +
"city='" + oRow["city"].ToString() + "', "
+
"region='" +
oRow["region"].ToString() + "', " +
"postalcode='" +
oRow["postalcode"].ToString() + "'," +
"country='" + oRow["country"].ToString() +
"'," +
"maxordamt=" + oRow["maxordamt"].ToString() + "
" +
"where
cust_id='" +oRow["cust_id"]+ "'";
oDSAdapter.UpdateCommand =
new
OleDbCommand(lcSQL,this.oConn);
int lnRows = 0;
try
{
/// Take the
changes in the dataset and sync to the database
lnRows =
oDSAdapter.Update(this.oDS,"Customer");
// *** Or you can just directly execute the
Command object
// oDSAdapter.UpdateCommand.ExecuteNonQuery();
}
catch(Exception
e)
{
this.cErrormsg
= e.Message;
return false;
}
return true;
}
Using Stored Procedures
As a last issue here I want to demonstrate calling a Stored
Procedure in a VFP DBC to perform the Insert operation for a new customer. Again,
standard syntax that's supported with SQL Server doesn't work with the VFP OleDb provider at this time, so you need to use explicit
syntax to call the stored procedure.
I kept this method to only a few field values to keep these
code snippets short enough for display – you'd probably want to add all the
remaining fields. The VFP Stored Procedure looks like this added to the TasTrade sample data (testdata.dbc):
FUNCTION InsertCustomer
LPARAMETERS lcCompany, lcContact, lnMaxOrdAmt
lcCustId = LEFT( UPPER(lcCompany), 5)
INSERT INTO Customer (CUST_ID,Company, Contact, MaxOrdAmt)
VALUES
(lcCustId,lcCompany,lcContact,lnMaxOrdAmt)
RETURN lcCustId
When you click on the Add button of the ASP.NET form the
currently active data is stored to the database by calling the AddCustomer method of the business object.
private void cmdAdd_Click(object sender, System.EventArgs
e)
{
if
(!this.oCustomer.AddCustomer(this.txtCompany.Text,this.txtContact.Text,
Convert.ToDecimal( this.txtCredit.Text)) )
{
this.lblErrorMessage.Text = this.oCustomer.cErrormsg;
}
}
AddCustomer in the business object
then calls the stored procedure explicitly (Listing 7 – tasCustomer.cs).
public bool AddCustomer(string lcCompany, string
lcContact, decimal lnMaxOrdAmt)
{
if (!this.Open())
{
return false;
}
OleDbCommand oCommand = new OleDbCommand();
oCommand.Connection = this.oConn;
oCommand.CommandText = "InsertCustomer('" +
lcCompany + "','" +
lcContact + "',lnMaxOrdAmt.ToString())";
try
{
oCommand.ExecuteNonQuery();
}
catch(Exception ex)
{
this.SetError(ex.Message);
return false;
}
return true;
}
I ran into some problems here with this code that worked
fine in VFP, but didn't at first with .NET due to the NULL requirements. The OleDb provider string doesn't support provider options so
the only way to set these particular settings is to actually run a command
against the connection. To do this I overrode the Open()
method of the aBusObj class to do the following (tasCustomer.cs):
protected override bool Open()
{
if (base.Open())
{
OleDbCommand
oCommand = new OleDbCommand();
oCommand.Connection = this.oConn;
oCommand.CommandText
= "SET NULL OFF\r\nSET DELETED ON";
oCommand.ExecuteNonQuery();
return true;
}
return false;
}
Interestingly enough I also ran into a major bug in the
driver before I added the code above. The INSERT from the stored procedure
would fail with an error: Field TITLE
cannot contain Nulls. The Command's ExecuteNonQuery()
would throw an exception indicating that the stored procedure call failed, yet
when I checked the table I ended up with a new record anyway! This issue
appears to be specific to this NULL error as other errors like violating the
Primary key rule did not cause this behavior. Microsoft is aware of this issue
and looking into it.
OleDb Summary
The VFP OleDb provider makes it
possible to access Visual FoxPro data through the .NET environment but in its
current implementation it doesn't provide all the functionality that ADO.NET offers.
This doesn't exclude VFP from .NET integration, but it does make it more code
intensive to use VFP data than say SQL Server or even Access (Jet) data. Plan on writing SQL statements by hand as opposed to using ADO.NET's auto-update features at least in this release of
the driver. Microsoft has indicated that they are planning to support
more of the .NET specific features in future versions of the driver – they are
aware of some of the limitations and are working to address them at this time
so we can look forward to fuller .NET support.
COM Interoperability
For many years the main call from Microsoft has been to
build component based applications based on the Component Object Model (COM) by
creating components in your language of choice and then exposing those
components or classes as COM objects to the operating system.
.NET bucks this trend by using a whole new mechanism of
interoperability via the Common Language Runtime which provides intermediary
byte code that is compatible with multiple languages that can output .NET
capable byte code that is compiled into executable binaries on the fly by the
runtime. What this means is that the days for binary incompatibility are gone. As are some of the issues that have plagued the COM infrastructure
– namely the issue of versioning and DLL Hell.
However, COM remains very important even with .NET both as a
backwards compatibility feature as well as a mechanism to provide interop with the runtime as a whole. For example, many of
the scalability features of the Windows operating system are still implemented using
the COM+ system. .NET itself also uses COM+ underneath to implement many of the
Enterprise features like
distributed transaction management, context and process management and many
security features. What's exciting here is though that the intricacies of COM+
are mostly hidden behind more user friendly .NET classes that expose the
functionality through class interfaces or attribute based descriptors.
However, COM usage in .NET for the developer is played way
down and the focus in .NET is on building .NET classes that perform
functionality rather than calling out to 'legacy' COM components to perform
business logic or other tasks. COM interop is
provided more as a backward compatibility feature than a 'moving forward'
feature. But because there's a large, large investment in COM by many
organizations, COM support in .NET is fairly strong.
The good news is that you can access most COM components in
.NET without any fuss. It isn't quite as easy as it was say in Visual
FoxPro/Visual Basic with late binding and CreateObject(),
but with a little bit more work COM interop is easily
accomplished. The not so good news though is that there's a performance penalty
for using COM objects in .NET resulting in reduced performance when compared to
previous COM based technologies. It's clear that the focus of .NET is not
necessarily to provide the best hosting environment to COM components but
rather to provide a mechanism to allow applications to co-exist until they can
be rebuilt with .NET.
COM interop in .NET is a two way
affair – you can call COM components from .NET and you can call .NET components
from classic COM capable applications. Let's look at each of the approaches,
how they work and what they offer to the developer.
Calling COM components from .NET
This mechanism is likely to be the most commonly used
functionality of COM interop as it allows you to
integrate existing functionality into new applications that are being built or
migrated to the .NET platform.
This process uses internal Interop
classes in the .NET framework to link and bind to COM components. The standard
process to accomplish this is to import the COM component which creates a
compiled wrapper .NET class that implements the COM objects interfaces as a
.NET class.
Because .NET is a type safe environment this .NET interface
is necessary to allow access to the class methods and properties. This
satisfies the compiler for type safety through mapping all the COM
properties/parameters/return types to the appropriate .NET types. This process is similar to early binding in
traditional COM client, but yet it is somewhat different. Behind the scenes the
wrapper class makes the appropriate Interop class
calls to call the COM component and then maps the parameters and return values
to the wrapper class.
Those Interop classes can also be
accessed directly via code to provide the equivalent of Late Binding,
but be advised that this can take a lot of code to do. It's similar to using C++ and the IDispatch COM interface to query information about a class and
then calling the method signature with generic parameters that need to be
translated and typecast into the proper typesafe
values that the compiler can deal with. This code can be messy and I wouldn't
recommend it for anything but objects that aren't properly described inside of
their respective type libraries (more on this later).
To demonstrate let's create a simple object that I've used
in the past for ASP COM interop and walk through
creating the object in VFP and then making it available in .NET to an ASP.NET
page/application. The class is very simple and doesn't do anything fancy – the
purpose here is to demonstrate the operation of the interop
mechanism not to show what you can do with it. I'll leave that for some future
article <g>.
I'll create a COM object here and show a few methods at a
time. To review the basics of creating a COM object, I'll start with a simple
object that serves as a Counter manager that stores and increases named counter
values. The first step is to create the object as a COM capable class (listing
9 – asptools.prg).
*************************************************************
DEFINE CLASS ASPTools
AS
Custom OLEPUBLIC
*************************************************************
*** Custom Properties
cDataPath=LOGFILEPATH
cAppStartPath = ""
oScriptingContext = .NULL.
nCounter = 0
lError = .f.
cErrorMsg = ""
************************************************************************
* aspTools :: Init
*********************************
*** Function: Set
the server's environment. IMPORTANT!
************************************************************************
FUNCTION INIT
*** Make all required environment settings here
*** KEEP IT SIMPLE: Remember your object is created
***
on EVERY ASP page hit!
SET RESOURCE OFF
&& Best to compile into a CONFIG.FPW
SET EXCLUSIVE OFF
SET REPROCESS TO 2 SECONDS
SET CPDIALOG OFF
SET DELETED ON
SET EXACT OFF
SET SAFETY OFF
*** Add this mainly so that the project will include
*** this stuff here
SET PROCEDURE TO wwUtils ADDITIVE
SET PROCEDURE TO wwAPI ADDITIVE
*** IMPORTANT: Figure out your DLL startup path
THIS.cAppStartPath =
ADDBS(JUSTPATH(Application.ServerName))
*** If you access VFP data you probably will have to
*** use this path plus a relative path to get to it!
*** You can SET PATH here, or else always access data
*** with the explicit path
SET PATH TO (THIS.cAppStartpath)
DO PATH WITH THIS.cAppStartPath + "DATA"
ENDFUNC
************************************************************************
* aspTools :: IncCounter
*********************************
*** Function:
Increments a counter in the registry.
*** Pass:
lcCounter - Name of counter to increase
***
lnValue - (optional) Set the value of the counter
*** -1 delete the
counter.
*** Return:
Increased Counter value - -1
on failure
************************************************************************
FUNCTION IncCounter(lcCounter as String, lnSetValue as
Integer) as Integer
LOCAL lnValue
lnSetValue=IIF(EMPTY(lnSetValue),0,lnSetValue)
oMTS = CreateObject("MTxAS.AppServer.1")
THIS.oScriptingContext = oMTS.GetObjectContext()
this.oScriptingContext.Item("Response").Write("Inccounter
from VFP<p>")
IF !USED("WebCounters")
IF
!FILE(THIS.cAppStartPath + "WebCounters.dbf")
SELE 0
CREATE table
(THIS.cAppStartPath + "WEBCOUNTERS") ;
( NAME
C (20),;
COUNTER I )
USE
ENDIF
USE
(THIS.cAppStartPath + "WEBCOUNTERS") IN 0 ALIAS WebCounters
ENDIF
SELE WebCounters
LOCATE FOR UPPER(name) = UPPER(lcCounter)
IF !FOUND()
INSERT INTO
WEBCounters VALUES (lcCounter,1)
lnValue = 1
ELSE
IF RLOCK()
IF lnSetValue
> 0
REPLACE
Counter with lnSetValue
ELSE
IF
lnSetValue < 0
REPLACE
Counter with 0
DELETE
ELSE
REPLACE Counter with Counter + 1
ENDIF
ENDIF
lnValue =
Counter
UNLOCK
ELSE
lnValue = 0
ENDIF
ENDIF
*** For testing
This.ncounter = lnValue
RETURN lnValue
ENDFUNC
ENDDEFINE
Remember that's it's real easy to create a COM object in VFP
simply by setting the OLEPUBLIC attribute on the DEFINE CLASS statement of a
PRG based class or by setting the OLE Public checkbox on the class property
settings of a VCX based class. However, it's equally important to set up your
COM object properly to run in the hosted environment, especially if that hosted
environment happens to be a service such as Internet Information Server (IIS).
The INIT method of the object sets the environment and saves some state
information that preserves the startup location of the application and set's
the path to it so we can find the data located there.
To create this class as a COM object, add this class to a
project (I named mine ASPDemos) and COMPILE it into a
Multi-Threaded COM Server or use BUILD MTDLL AspDemos
from AspDemos.
Once this is done you can now use the object from VFP with:
oDemo = CREATEOBJECT("AspDemos.AspTools")
? o.IncCounter("CodeDemo") && 1
? o.IncCounter("CodeDemo") && 2
? o.IncCounter("EssentialFoxDemo") && 1
? o.IncCounter("CodeDemo") && 3
For most of you this is nothing new. But note that if you
are building COM objects for use with .NET there is at least one major
difference: You MUST use VFP 7.0 and it's typing features to describe your
parameter and return value types, or else your methods will not be accessible
properly.
Importing the COM object into .NET
Ok, once your object is built you need to import it into
.NET. The easiest way to do this is to use Visual Studio.NET and the References
selection in the Project manager. Right click on the References tree item and
Add Reference. Then pick the COM tab and select your object from the list, or
if it's not registered yet, browse to it on disk (Figure 2).
Figure 2 – Importing a COM object into .NET is as
easy as selecting the object from the installed COM components or physically
selecting a typelibrary/dll from disk. This process
creates a .NET wrapper class for your COM object that is treated as any other
.NET object.
Once the object has been added to your project you can
access it pretty much like any other .NET object. For ASP.NET pages you'll have
to do a couple of additional things to get it to run properly. Since I'm using
VS.NET the actual ASP.NET page contains nothing more than the page header, while
all the logic goes into a codebehind class. The main
thing on the ASPX page header is the PAGE directive:
<%@ Page ASPCOMPAT=true language="c#"
Codebehind="ComInterop.aspx.cs" AutoEventWireup="false"
Inherits="ASPInterOp.ComInterop" %>
This tells the CLR that the actual code that runs on this
page is stored in COMInterop.aspx.cs. This is where
I'll put the code to demonstrate the use of the COM object I created above.
There's another very important directive in the PAGE tag
above! The ASPCOMPAT=true attribute tells the ASP.NET page that it is hosting
COM components that don't conform to the ASP.NET threading model. Visual FoxPro
(and Visual Basic 6) COM objects are Single Threaded Apartment (STA) objects,
while .NET natively runs ASP.NET requests on MultiThreaded
Appartment (MTA) threads. This requires the .NET
framework to make some changes into how requests are handled by essentially
switching the request into STA compatible mode. I'll talk more about this a
little later in relation to how this affects performance.
ASPCOMPAT also provides compatibility to classic the ASP COM
object environment by providing the appropriate Context objects that were
supported to retrieve the ASP intrinsic objects before. In particular the ObjectContext object provider which uses the COM+ IApplicationContext object interface is provided to COM
objects which would otherwise not be available. I'll come back to this in a bit
as well.
The code behind page then contains all the logic of the page
with the ASPX page only containing the display elements. I want to keep things
simple so I created a simple form that lets you increment counters, delete them
and show them all. The final running form is shown in Figure 3.
Figure 3 –
The COM Counter sample demonstrates using a basic COM object in a Web form.
The actual page is made up of a textbox (txtCounterName),
a label (lblCounter), a set of operational buttons (btnIncrement, btnDelete, btnShowAll) and an empty label control that is filled from
the COM object – in this case with an HTML string that shows the table you see
in Figure 3. There's also a label between the table and the buttons called lblErrorMsg that is used for status information. For
example, when you delete a counter this label is updated with a message that
says that the counter was deleted.
Let's start with how the basic incrementation
mechanism works. Here's the Page Load and Increment button click code (Listing
10 – ComInterop.aspx.cs).
...
using aspdemos; //
COM object wrapper
namespace ASPInterOp
{
// … control
declarations left out
public class
ComInterop : System.Web.UI.Page
{
private void
Page_Load(object sender, System.EventArgs e)
{
if
(!this.IsPostBack)
{
this.btnIncrement_Click(this,e);
}
}
private void
btnIncrement_Click(object sender, System.EventArgs e)
{
ASPToolsClass oVFP = new ASPToolsClass();
this.lblCounter.Text =
oVFP.IncCounter(this.txtCounterName.Text,0).ToString();
}
}
Ok, let's look at what happens here. First make sure the aspdemos namespace which represents the imported COM
object, is added to your class so that you can access the class. With the
namespace and reference added our VFP class behaves just like any other .NET
class including full access to Intellisense (Figure 4).
Figure4 – Once imported your COM object looks
like any other .NET object with full access to Intellisense.
It's important that you define your methods with proper Type
information using VFP 7.0's parameter typing.
FUNCTION IncCounter(lcCounter as String, lnSetValue as
Integer) as Integer
If you don't provide the type information, the method will
not import properly and show up without parameters and return values – and will
fail at runtime when called. (Note: as of .NET Framework SP1 this has been
fixed – methods with variants will import with object parameters, which still are a pain to use so make sure you use
typed parameters/returns whenever possible (ie. all
the time!!!)).
Properties names are translated to upper case so nCounter becomes NCOUNTER. Remember C# is case-sensitive so
this makes a big difference. Better yet, always use Intellisense
to provide you with the proper property names.
Note that all properties are returned as type Object unless
you specifically type them with VFP's _ComAttrib functionality part of the class definition. Type
object is similar but not quite the same as a Variant in COM. Object types can
contain different sub types (such as strings, ints
etc), but they are cannot directly be accessed by their sub-type. In order to
use the variable safely in C# you have to explicitly cast it in many cases:
int
lnCounter = (int) oVFP.NCOUNTER + 10;
this.txtCounterProperty.Text
= lnCounter.ToString();
If you use the object variable multiple times it's probably
more efficient to store it into a properly typed variable first, then use it as
you intend to. It's both easier to work with and faster in execution.
Changing Code in your COM object
When it comes time to make a change to the COM object,
things are very similar to the way it was with classic ASP – in order to
compile the COM object you need to restart the Web Service or at least the Web
application that hosts the COM component.
The easiest way to do this for me is to run the IISRESTART
utility. And for this purpose I use an Intellisense
script (tied to the IIS key sequence)
that simply does:
RUN /n4 IISRESET
IISRESET is a IIS 5.0 and later
utility that has a number of options for restarting the Web server. It can
restart all the services or with the proper options any of the specific
services. By the way, it can also be used to reboot a machine reliably. The nice
thing about running IISRESET from Fox is that it runs in a separate window that
starts and finishes when it's done so there's no more user interaction
required.
While the reset is working let's add another method to our
server to delete a counter (Listing 11 - asptools.prg).
Function DeleteCounter(lcCounterName as String) as Boolean
IF EMPTY(lcCounterName)
RETURN .F.
ENDIF
DELETE FROM WebCounters where Name = lcCountername
RETURN .T.
Then rebuild your server with BUILD MTDLL AspDemos from ASPDemos. To hook
up this method I'll edit the visual ASPX page add the btnDelete
button and add the following event code to the click event:
