Using Visual FoxPro resources in. NET-visual FoxPro OLE DB Provider

First part: Visual FoxPro OLE DB Provider

Speaking of Fox, the first impression is DBF data table, nearly 20 years of development process, do not know how many systems use DBF storage data. When you go to the. NET platform, how to access the Fox series of data is naturally a concern for many developers.

Prior to Visual FoxPro 7, Microsoft only provided Fox's ODBC driver. Using ODBC to access Fox data is inefficient and, more importantly, many of the features in the Database container (DB Container, abbreviated DBC) that are added to Visual FoxPro 3 are not supported, including stored procedures, triggers, defaults, constraints, Referential integrity, and so on.

to cater to. NET ERA, Microsoft launched the visual FoxPro OLE DB Provider program with Visual FoxPro 7 release, which has the following characteristics: (note: At present, Microsoft only in Visual FoxPro 7 Professional Edition of this program!) )

• Data access is more efficient

• Support access to stored procedures in DBC

• New feature database events that support Visual FoxPro 7 (DB Container events)

• Support for triggers, defaults, constraints, referential integrity in DBC

With Ole DB Provider, we can use Ado.net's System.Data.OleDb to access Fox data. There are a lot of information about ado.net, and we don't have to be tired of that, but here are just three more specific questions:

• Set data session environment, such as processing mode for null value, processing mode of time value, string comparison mode, etc.

• Call stored procedures with no return value

• Calling stored procedures with return values

Setting Data Session Environment

Friends who have used Fox know that the beginning of the application will use a lot of SET statements to set the default data working environment, extend to ado.net, we want to set the working environment of the "Connection" channel, which requires sending the SET statement. The Command object ExecuteNonQuery () method can be invoked in ado.net, requiring the transfer of commands. (CODE #1)

C # code to set data working period environment
OleDbConnection oconn=new OleDbConnection ("Provider=vfpoledb.1;data Source=c:\\data\\testdata.dbc; Exclusive=1 ");
oConn.Open ();
OleDbCommand ocommand=new OleDbCommand ();
Ocommand.connection=oconn;
ocommand.commandtext= "SET EXACT on\r\nset DELETED on\r\nset Null off";
Ocommand.executenonquery ();

Call stored procedures with no return value

Visual FoxPro 3 introduces the concept of DBC, which technically enables the DBF series data tables to rise to the height of the database, supporting the typical characteristics of the database, including stored procedures. Here the so-called stored procedures with Fox's vision is stored in the DBC inside the function or process, and the normal Fox code does not make any difference.

It is to be noted that the OLE DB Provider provided by Visual FoxPro 7 do not support the way the canonical stored procedure is invoked. More specifically, you cannot invoke DBC stored procedures in a way that calls stored procedures in SQL Server.

For example, there is a stored procedure in DBC: (CODE #2)

A stored procedure in *dbc that writes a row to the log table.
PROCEDURE logmsg (cmsg as String)
INSERT into LOG (eventime,logmsg) VALUES (DATETIME (), m.cmsg)
Endproc

This is a procedure that does not return a value, and the procedure that calls it is similar to sending statements to the database, so the specific processing is this: (CODE #3)

C # code calls stored procedures with no return value
String cmsg= "write a row of logs";
ocommand.commandtext= "Logmsg ('" +cmsg+ ")";
Ocommand.executenonquery ();

Calling stored procedures with return values

In DBC there is such a stored procedure: (CODE #4)
A stored procedure in *DBC that is the total net sales of the designated country
FUNCTION get_sum_order_net (Ccountry as String) as Currency
Local isum as Currency
Select SUM (order_net) as order_net from orders where cust_id to (select cust_id from Customer WHERE country=m.ccountry) INT O CURSOR Temp
Isum=temp.order_net
Use in Temp
Return isum
Endfunc

We remember that there is a method ExecuteScalar () for returning a single value in the Command object of Ado.net, as follows: (CODE #5)

C # code calls stored procedures that have return values
String lccommand= "get_sum_order_net (' UK ')";
OleDbCommand ocommand=new OleDbCommand (lccommand,oconn);
Object oresult=ocommand.executescalar ();

Fox is the fastest desktop database, for many years Microsoft has put the main focus on Fox's own changes, ignoring the external access interface (DBF table structure is simple, many systems can directly access). Now, in order to. NET to access the Fox series of data Sources, Microsoft released the OLE DB Provider to meet the needs of day-to-day development work. We also see that this version of the program is handled differently than the standard ado.net, but Microsoft has made it clear that it will upgrade the visual FoxPro OLE DB Provider so that it can be done for. NET provides more perfect support!