One unfortunate thing about FoxPro (One, James? i thought there were hundreds!) is that it doesn't allow you to use named parameters in ADO.NET using the Visual FoxPro OleDb Driver (VFPOLEDB.1).
You want to use parameters for queries so that it's easier to keep protected from sql injection, so here's how i setup my Select command factory in the webservice data layer:
public System.Data.Common.DbCommand ConstructSelectCommand(Database db, ByPersonIdentity idObject)
{
OleDbCommand command = new OleDbCommand(DataConstants.WorkHistoryByPersonSelect, (OleDbConnection)db.Connection);
command.Parameters.Add(
null, OleDbType.Char);
command.Parameters[0].Value = idObject.OfficeCode.ToString();
command.Parameters.Add(
null, OleDbType.Integer);
command.Parameters[1].Value = idObject.PersonId;
return command as DbCommand;
}
Basically, DataConstants.WorkHistoryByPersonSelect is my select string and idObject is my identity object which is storing my two parameters for the query.
The string for the query then looks like this:
internal
static string WorkHistoryByPersonSelect = @"SELECT ? as office_code,
workhistory_id,person_id,isclient,company,city,state,from_date,to_date,
title,wh_shift,beginpay,endpay,payfrequency,leavereason,wh_return,supervisor,
super_title,super_ext,super_phone,rehire,comment,refconfirmed,updated_by,updated,(SELECT description from codes where codes.code_id = workhistory.leavereason) as leavereasondescription
FROM WorkHistory where person_id =?"
;
(These rows are from dataforce starsearcher, a dated and unsupported software were still using because it gets the job done!)
I haven't begun implementing any Insert or Update functionality yet, so i'm not sure if i can do out parameters or not...