HTML java c#
appla · 2008-03-22(近似)
2008年02月22日
Accessing VFP Data with OleDb
- [
]
Tag:
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).
Listing 1 (C# ASP.NET): Retrieving a VFP OleDb result into an ASP.NET DataSet
…
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
OleDb
DataAdapter
. If you go against SQL Server you can use the
Sql
DataAdapter
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
).
Listing 2 (C# ASP.NET): Retrieving and displaying the customer list and first record
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
).
Listing 3 (C#): Simplified base business object for handling data retrieval
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
).
Listing 3.1 (C#): Overridden Open() method that handles VFP OleDb environment settings
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
).
Listing 4 (C#): Displaying a by
binding the individual fields to a DataRow
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
).
Listing 5 (C#): Retrieving a single customer with the business object
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
).
Listing 6 (C#): Saving a customer record
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
).
Listing 6.1 (C#): Saving a customer record from the DataSet back to the VFP table
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
):
Listing 6.5 (VFP): Insert Customer VFP Stored Procedure
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
).
Listing 7 (C# ADO.NET): Adding a customer with a stored procedure
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
):
Listing 8 (C#): Setting Language Options for the current OleDb session
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
).
Listing 9 (VFP): A simple Counter VFP COM object
*************************************************************
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
luguangjun
发表于18:58:42 |
引用(0)
|
编辑
原始快照:web.archive.org