Markdown Monster
OleDb Parameters to access FoxPro Data from .NET
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 :-}
The Voices of Reason
# re: OleDb Parameters to access FoxPro Data from .NET
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.
# re: OleDb Parameters to access FoxPro Data from .NET
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 :-}
# re: OleDb Parameters to access FoxPro Data from .NET
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.
# re: OleDb Parameters to access FoxPro Data from .NET
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.
# re: OleDb Parameters to access FoxPro Data from .NET
# re: OleDb Parameters to access FoxPro Data from .NET
# re: OleDb Parameters to access FoxPro Data from .NET
# re: OleDb Parameters to access FoxPro Data from .NET
# re: OleDb Parameters to access FoxPro Data from .NET
http://www.west-wind.com/WestwindWebToolkit/
The Dal Classes are housed here:
http://www.west-wind.com/WestwindWebToolkit/docs?page=_2m616kd4a.htm
# re: OleDb Parameters to access FoxPro Data from .NET
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/.
# re: OleDb Parameters to access FoxPro Data from .NET
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
# re: OleDb Parameters to access FoxPro Data from .NET
# re: OleDb Parameters to access FoxPro Data from .NET
I do not get results of the query. Is there a way to get round this problem?
RHD
# re: OleDb Parameters to access FoxPro Data from .NET
# re: OleDb Parameters to access FoxPro Data from .NET
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.
# re: OleDb Parameters to access FoxPro Data from .NET
# re: OleDb Parameters to access FoxPro Data from .NET
@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!
# 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/" >
And here is the request of Wcf tool that works well :
<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
<s:Header>
thank you
# re: OleDb Parameters to access FoxPro Data from .NET
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