Rick Strahl's Weblog  

Wind, waves, code and everything in between...
.NET • C# • Markdown • WPF • All Things Web
Contact   •   Articles   •   Products   •   Support   •   Advertise
Sponsored by:
Markdown Monster - The Markdown Editor for Windows

OleDb Parameters to access FoxPro Data from .NET


:P
On this page:

OleDb – what a blast from the past. Got a couple of basic questions in the last week on how to access FoxPro data from .NET via OleDb and both questions got tripped up with how the query parameters are passed. It’s been so long since I’ve used the OleDb driver I had to look this up again myself to remember how this works  through FoxPro OleDb.

The issue is that the VFP OleDb driver deals with parameters only on a positional basis rather than on a named basis. Since most examples of ADO.NET access data with SqlClient and use named parameters you’ll find that those types of queries against the VFP driver will fail.

With SqlClient you can specify a parameter by name like this:

SqlCommand cmd = new SqlCommand("select * from wws_customers where company like @Company");
cmd.Parameters.Add( new SqlParameter("@Company",'West Wind%');

Parameters are named and can be provided out of order – the native Sql Syntax handles matching up the parameter definitions with the named parameters in the actual string.

Visual FoxPro handles parameters using ? evaluations. In pure FoxPro code you can use placeholders with ?lcParameter where lcParameter has to be a variable in scope. When using OleDb though there’s no real operating environment and variables aren’t set so you typically only supply a parameters as a sole ?  (without a name) which effectively is treated as a positional parameter.

OleDb access to the FoxPro provider requires that you use only a ? placeholder like this:

string connString = @"Provider=vfpoledb.1;Data Source=c:\wwapps\wc3\wwdemo;Exclusive=false;Nulls=false";

OleDbConnection Conn = new OleDbConnection(connString);
OleDbCommand Cmd = new OleDbCommand("select * from tt_cust where Company > ? and Company < ? ");
Cmd.Connection = Conn;

Cmd.Parameters.Add(new OleDbParameter("?", "D"));
Cmd.Parameters.Add(new OleDbParameter("?", "M"));

Conn.Open();
IDataReader reader = Cmd.ExecuteReader();

while (reader.Read())
{
    Console.WriteLine(reader["Company"]);
}
Conn.Close();

The named parameter approach obviously is more flexible as you don’t have to ensure the same order when parameters are added as you have to with the VFP access. It’s crucial that parameters are added in the right order in this scenario or you’ll get invalid results.

In the example above I explicitly used just the ? for the parameter name, but the name is irrelevant in the driver. You can give the OleDbParameter any name of you choosing so you can just as easily use:

Cmd.Parameters.Add(new OleDbParameter("?startChar", "D"));
Cmd.Parameters.Add(new OleDbParameter("?endChar", "M"));
which is a little easier to read and keep straight. Remember though the position is what counts with those parameters! This should work both for query parameters as above in the SELECT WHERE clause as well as for INSERT/UPDATE statements and other explicit commands.

SQL Literal Strings – Don’t go there, please!

Incidentally both people who asked about the parameters ended up not using Sql parameters, but instead embedded the evaluated parameter values directly into the SQL string to get their queries to work.

So rather than using parameters they’d use code like this:

OleDbCommand oCmd = new OleDbCommand("select * from tt_cust where Company > '" +
startChar + "' and Company < '" + endChar + "'");

<rant>

This is EVIL! Please do not go down this path and embed literal values into SQL strings to ensure your applications are not vulnerable to SQL Injection attacks. SQL Injection attacks even for simple things like this are dangerous and once code is written as above they are difficult to find and fix later. Regardless of development language or SQL interface driver you are using make sure you use the provided parameter embedding language constructs which will ensure that parameter are only used as parameter values rather than embedded SQL commands that can compromise your database.

</endrant>

It’s kind of interesting to look back at raw ADO.NET code – man I can’t remember how long it’s been since I’ve written code like this actually – instead relying on either a Data Access layer or ORM to handle data access for me transparently. But there are still lots of people starting out with raw ADO.NET and there’s nothing wrong with that of course. Maybe one of the days I’ll post my basic DAL component here to help some folks to get past that initial pain – especially when coming from the ease of FoxPro data access :-}

Posted in FoxPro  ADO.NET  

The Voices of Reason


 

Darrell
March 12, 2009

# re: OleDb Parameters to access FoxPro Data from .NET

Rick,

What ORM(s) do you/have you used? I have toyed around with Linq to Sql but if MS is not going to put more work into it then I don't want to go down that path. Lately, I've been testing Lightspeed. I think something like Nhibernate is more than I need. Just wondering what your ORM of preference is.

Darrell

Steve from Pleasant Hill
March 12, 2009

# re: OleDb Parameters to access FoxPro Data from .NET

I so want to use an ORM but it is hard to let go of (time-consuming) hand crafted DAL code that is exactly what I want.

Entity Framework is not perfect, nor are the layers that you can buy to sit on top of it.

I get just "so far" with Subsonic, and then the lack of ready documentation makes me give up.

Telerik sells one that has a free version which only works with the personal editions of SQL Server, which I look to try soon.

With Silverlight 3 and the next version of EF I think the development process will change quite a bit.

Having started with large scale databases in the early '80s and seen lots of bad db design and data access strategies over the years, admittedly it's hard to let go and let an ORM do my work.

Rick Strahl
March 12, 2009

# re: OleDb Parameters to access FoxPro Data from .NET

@Darrell - I've used a lot of ORMs in different situations, but I don't have any clear answers. In fact I'm still struggling with finding a good data access solution that works in most data scenarios and I haven't found it.

ORMs don't always make a good fit in general and that's probably the greatest downside IMHO. They are NEVER what I would call a generic solution that you can use in any data scenario - as my old DAL based approach used to be.

As far as they go I like LINQ to SQL when working with fixed data (ie. nothing dynamic which unfortunately for the work I do is very frequent) and SQL Server only because of the ease of LINQ integration and the control I have to feed the framework SQL commands directly if necesasry (or bypass LINQ to SQL if necessary). There are warts (like disconnected entities and lifetime management of DataContext) but in many typical data situations that hit SQL Server that's not the problem. The biggest problem with LINQ to SQL (again for me) is that it only works with SQL - I have a number of custom providers and local data I work with often and LINQ to SQL doesn't work there.

Entity Framework as it is now is complete dud for me. The lack of low level control to drop to raw SQL, the limited LINQ support and the performance of the framework make this a non-starter for me. MS says they are going to at least get EF on par with LINQ to SQL, but I'll believe it when I see it - I don't think it can backfitted that way.

I've been dabbling with nHibernate a bit too although frankly I haven't spent a lot of time with it. I like what I see but the ramp up curve of NH is pretty steep. Mainly because it seems resources about NH are scattered all over the place (and of wide varying quality). NH overall though seems to be a more mature solution and one that from a pure ORM perspective is much more reliable than either L2S or EF especially when we're talking about the update of multiple entity scenarios. But there's no official LINQ support (yet - some preview stuff available) which is one of the big draws for LINQ to SQL for me.

Also checked out LLBLGen from Frans Bourma which frankly was the most comprehensive solution that addresses many of the issues I'm dealing with. Unfortunately though, it's a commercial product which for the give-away and source distribution stuff I build is often not a viable option due to re-distribution issues.

There are lots of choices out there but I think we're still one generation away from more universal tools in this space. nHibernate with good LINQ support I think would be a killer combination. EF with the basic feature set that L2S had - including low level access into the DAL - might make EF more interesting. L2S is dead unfortunately but that actually had good potential to with additional provider support and more control over DataContext lifetime and option management. I guess Microsoft continues on the tradition of screwing up every data layer they've ever built (with the possible exception of the core ADO.NET base libraries).

I had an interesting discussion yesterday with Markus Egger about Data Access. Years back in the .NET 1.x days, both of us built custom data access layers which effectively were based on DataSets with an entity layer on top of it. All the ORM solutions provide many cool high level features, but when you really think about it they only provide a subset of what DataSets have always provided. Yeah DataSets are heavy and verbose, but they are also dynamic (you can access fields by name so things like user defined fields are a snap), support updates and disconnected operation easily and are for many data binding scenarios actually significantly faster than entity frameworks (because no Reflection is required). Many of these things all the ORM tools don't have clean solutions for.

At the same time it's easy to see the appeal of ORM tools. Although my old business object toolset did entities it often couldn't express relationships very naturally or only through manual creation of the related lists or entities. Although that's fixable in the framework, the truth is that I don't want to be in the business of writing and maintaining code like that. That's complex stuff when doing it on a generic basis anyway.

No easy answers. I think Steve has it partially right. With all the experimenting and fucking around with these solutions trying to make things work right in some cases it might just be easier to stick with a lower level DAL approach and at least end up with all the flexibility you need.

I still believe that a 'simple' ORM\DAL combination that provides both the basic entity services plus a traditional DAL feature set are what's needed, but that makes DDD freaks scream and it doesn't follow the accepted way of doing things. Shrug.

Too many choices are not always a good thing :-}

Brian Vander Plaats
March 12, 2009

# re: OleDb Parameters to access FoxPro Data from .NET

Hi Rick, my company is very much in the process of moving from FoxPro to .NET as well as FoxPro databases to SS, so we are knee deep in OLEDB here. I hope to begin investing some alternate data access solutions this year.

I agree that OledbParams are the only way to go, but you needn't overstate your case :P. I found out quickly that ' " [ and other special chars quickly lead to numerous page crashes, so It's hard for me to imagine any production system going on very long inserting the params directly into the string.

If you have time I'd love to see your DAL, and I'm sure others would as well.

Andy Carey
March 13, 2009

# re: OleDb Parameters to access FoxPro Data from .NET

Yes, Rick, you 'da man! Brilliant techie. Always remembers to share. Pushes the envelope a bit though... You've been spot-on with so many answers over the years -- and free code -- your DAL would be a huge addition.

Vfp Dinosaur ------> .Net: Kevin McNeish's book was very helpful (http://www.hentzenwerke.com/catalog/netvfp.htm) and included rudimentary classes, including DAL. We're looking at MM.Net as a possibility, but we're pretty anti-framework here since we're likely to be forced into CA Plex.

I guess I'm asking you to write another book! And I hereby publicly offer to spend 100 hours -- for free -- helping you out on it however I can as gratitude. In the alternative, maybe I'll just donate via PayPal.

Peter Bromberg
March 13, 2009

# re: OleDb Parameters to access FoxPro Data from .NET

When I finally got a friend to understand why SQL literal strings with embedded values were evil, he had to go back and rewrite weeks worth of stuff at his company to use correctly parameterized SQL Commands. I'm sure it was a painful lesson.

David Boatright
March 24, 2009

# re: OleDb Parameters to access FoxPro Data from .NET

If I may ask a stupid question, how do you do this in ASP .NET.

Boudewijn Lutgerink
April 06, 2009

# re: OleDb Parameters to access FoxPro Data from .NET

Rick, I am truly looking forward to your DAL components. I have been struggling with Data Access in a VB.NjET project and I am sure now that the intent from MS engineers must be to make the lifes of developers one long tear jerking drama with daily abuse of our rights to be productive when it comes to Data Access. They apearantly forgot that Data is the 2nd biggest asset in companies (Employees being #1). The framework may be good, the tools to use it, and I *try* to put it mildly here, "are not very productive", so do us all a favour and show your DAL components.

Alan Bourke
April 16, 2009

# re: OleDb Parameters to access FoxPro Data from .NET

An EF provider for VFP ... now *that* would be nice.

Rick Strahl
April 16, 2009

# re: OleDb Parameters to access FoxPro Data from .NET

@Boudewijn - the core DAL (not the business layer, just the DAL) is part of the West Wind Web Toolkit for ASP.NET:

http://www.west-wind.com/WestwindWebToolkit/

The Dal Classes are housed here:
http://www.west-wind.com/WestwindWebToolkit/docs?page=_2m616kd4a.htm

Tom Brothers
August 18, 2009

# re: OleDb Parameters to access FoxPro Data from .NET

Alan Bourke

I can’t help you with an EF provider but if you are interested in something a little more like LinqToSql… then check out http://linqtovfp.codeplex.com/.

Victor
September 30, 2009

# re: OleDb Parameters to access FoxPro Data from .NET

Hi,

I'm trying to VFP access tables/views in C# with oleDB.

Accessing the tables is no problem, but most of the views have variables in the select statement, like this:

SELECT *;
FROM ;
ca!depatient;
WHERE Depatient.pat_pk = ( ?lcpat_pk )

I tried your approach (adding a parameter with Cmd.Parameters.Add) to read the data but it does not seem to work.
I keep receiving the error "SQL: Column 'LCPAT_PK' is not found.".

Any idea how I can achieve this (sending the variable's value without having to change the VIEW's statment)?

Thanks in advance.

Victor

ChiranthaW
June 21, 2010

# re: OleDb Parameters to access FoxPro Data from .NET

Great post. This fixed my problem..Thnx :)

R Hasha D
October 02, 2010

# re: OleDb Parameters to access FoxPro Data from .NET

I tried the code at the beggining, but when the value in the parameter is a string with spaces,
I do not get results of the query. Is there a way to get round this problem?
RHD

Abdul Rehman
September 08, 2011

# re: OleDb Parameters to access FoxPro Data from .NET

You're awesome. Saved me from a lot of headache as I was using named parameters and had no idea why my queries were failing. Thank you.

Steve Roberts
November 11, 2011

# re: OleDb Parameters to access FoxPro Data from .NET

Hi Rick,

You mention that you might post your basic DAL component, is there any chance you can do this? I'm in the process of moving from VFP to c# and the data access side of the language is very different from VFP. It would be great to see how you achieve this.

Thanks

Steve.

Kev
October 31, 2012

# re: OleDb Parameters to access FoxPro Data from .NET

Thanks so much for this post, I was completly stuck trying to figure out if the parameter symbol was an @ or % or ?. First time using VFP in C#. Keep up the great work..!

Alan
October 08, 2015

# re: OleDb Parameters to access FoxPro Data from .NET

Wow, this blog entry is timeless considering it's still useful after all these years! Thanks Rick.

@Victor: you can do what you're asking - that is, you can create parameterized views in a VFP data container and then select from that view via the VfpOleDB driver after setting the values of your parameterized view variables. But its not exactly obvious and requires your data be in VFP9 - at least the only way I've figured out how to do it.

Lets assume you have a parameterized view in your DBC, like this:
CREATE SQL VIEW victorsView AS ;
SELECT *;
FROM ;
ca!depatient;
WHERE Depatient.pat_pk = ( ?lcpat_pk )


Now, in your C# code, you need to establish your connection, and use foxpro's EXECSCRIPT function to string several vfp commands together. Those commands, if run in VFP, will set your values for your view parameter, select from the view into a cursor, then mark that cursor as the result set to be returned. Something like this:
// Lets assume you want to pass the string 999 as your view parameter, lcpat_pk
string viewParameterValue = "999";
 
//Put each vfp command line into string
string vfpCommand1 = "STORE '" + viewParameterValue + "' TO lcpat_pk";
string vfpCommand2 = "SELECT * FROM victorsView INTO CURSOR curViewResults";
string vfpCommand3 = "SETRESULTSET('curViewResults')"; //Here's the part that requires VFP9, I believe
 
//Write those vfp command line strings into a vfp script
string vfpCR = "CHR(13)+CHR(10)";
string vfpScript = "EXECSCRIPT([" + vfpCommand1 + "]+" + vfpCR + "+[" + vfpCommand2 + "]+" + vfpCR + "+[" + vfpCommand3 + "])";
 
//Make that script the OleDbCommand's CommandText and execute.
OleDbCommand command = new OleDbCommand(vfpScript, connection);
OleDbDataReader reader = command.ExecuteReader();


Since I specified that the results of the query be marked as the result set to be sent back via the vfpOleDb driver, I can create a reader object using ExecuteReader or even fill a dataset. Alternatively (and I would think a better option), you could write a stored procedure in your DBC rather than string together a vfp script. Pass the value you want your view to use as a normal bind parameter as Rick described in his post.

This works for me. I've read where others claim to be able to send multiple vfp commands individually rather than using EXECSCRIPT or a stored proc. But I wasn't able to get that to work. Hope this is helpful to you, Victor, or anyone else *still* using vfp data who happens to stumble across this post. Good luck!

Abbel
July 11, 2017

# re: OleDb Parameters to access FoxPro Data from .NET

hi, i need your help please

Excuse my English, for days I try to call a method in a web service soap from FoxPro

http://...........svc?singleWsdl

When i use wcf test client it works

Here is my code

The query calls a datacontract that has parameters that the lomethod object in foxpro can not read

loSoap.AddParameter("AdresseLigne1", alltrim(pers.e_addr1))

loSoap.AddParameter("AdresseLigne2", alltrim(pers.e_addr2))

loSoap.AddParameter("Cellulaire", alltrim(pers.e__cphone2))

loSoap.AddParameter("CodeBureau", alltrim(jobhist.h_locn))

loSoap.AddParameter("CodePostal", alltrim(pers.e_postcd))

loSoap.AddParameter("CodeSecteur", alltrim(jobhist.h_entity1))

loSoap.AddParameter("CodeStatut", alltrim(jobhist.h_pstat))

loSoap.AddParameter("CodeEmploi", alltrim(jobhist.h_jobid))

loSoap.AddParameter("Courriel", alltrim(pers.e_email))

loSoap.AddParameter("DateDebut", pers.e_orighire)

loSoap.AddParameter("DateFin", jobhist.h_ondate)

loSoap.AddParameter("DateFin", )

loSoap.AddParameter("Langue", lcLang) loSoap.AddParameter("Matricule", lcPersid)

loSoap.AddParameter("Nom", alltrim(pers.e_lname))

loSoap.AddParameter("Prenom", alltrim(pers.e_fname))

loSoap.AddParameter("Province", alltrim(pers.e_prov))

loSoap.AddParameter("Sexe", lcSex)

loSoap.AddParameter("Ville", alltrim(pers.e_city))

lvResult = loSoap.CallMethod("UpdateSalariee")

How to add these parameters to an object (datacontract c #) to pass them into an object before calling the method ?

Here is my xml request generated :

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" soap:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" > soap:Body <d4p1:UpdateSalariee xmlns:d4p1="http://semainier-ws/" > avenue 5 02 YYYXXX 13000 PTC 0104 name@mail.com 2014-04-14T00:00:00.00000 2017-01-01T00:00:00.00000 Eng 02800 Lastname Firstname QC M QC </d4p1:UpdateSalariee></soap:Body> </soap:Envelope>

And here is the request of Wcf tool that works well :

<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/"> <s:Header> http://semainier-ws/IServiceSemainier/UpdateSalariee </s:Header> <s:Body> d4p1:AdresseLigne1avenue 5</d4p1:AdresseLigne1> <d4p1:AdresseLigne2 i:nil="true" /> <d4p1:Cellulaire i:nil="true" /> d4p1:CodeBureau02</d4p1:CodeBureau> d4p1:CodePostalYYYXXX</d4p1:CodePostal> d4p1:CodeSecteur13000</d4p1:CodeSecteur> d4p1:CodeStatutPTC</d4p1:CodeStatut> d4p1:CodeEmploi0104</d4p1:CodeEmploi> d4p1:Courrielname@mail.com</d4p1:Courriel> d4p1:DateDebut2014-04-14T10:02:00</d4p1:DateDebut> <d4p1:DateFin i:nil="true" /> d4p1:LangueEng</d4p1:Langue> d4p1:Matricule02800</d4p1:Matricule> d4p1:NomLastname</d4p1:Nom> d4p1:PrenomFirstname</d4p1:Prenom> d4p1:ProvinceQC</d4p1:Province> d4p1:SexeM</d4p1:Sexe> d4p1:VilleQC</d4p1:Ville> </s:Body>

thank you


West Wind  © Rick Strahl, West Wind Technologies, 2005 - 2024