converting a VFP9 script to C#

[campagnolo1]

Greetings,

I've been working on a project for a while where I call an exe from a code-behind in a web page. This works all fine and dandy, but I would like to try and put the code directly into the code-behind page, not having to rely on the exe and VFP.
Following is the code from the VFP exe:

Close All
Set Exclusive Off
Use F:\TIW\KOKAC\immaster In 0
Set TABLEVALIDATE To 0
Use F:\TIW\KOKAC\imstock In 0
Set TABLEVALIDATE To 0
Use F:\TIW\KOKWS\Somater In 0
Set TABLEVALIDATE To 0
Use F:\TIW\KOKWS\soheader In 0
Set TABLEVALIDATE To 0
Use F:\TIW\KOKWS\Bmrev In 0
Set TABLEVALIDATE To 0
Use F:\TIW\KOKWS\bmsl In 0
Set TABLEVALIDATE To 0
Use F:\TIW\KOKWS\Soroute In 0
Set TABLEVALIDATE To 0

connstr = SQLSTRINGCONNECT("Driver={SQL Server}; Server=KOKTS-VM\SQLExpress; Database=WebPortal;Trusted Connection=Yes")

Select soheader.partno As Item,;
	soheader.sono As sono,;
	soheader.rev As sorev,;
	soheader.sqty As sqty,;
	soheader.need_date As needdate,;
	soheader.priority,;
	soheader.salesno,;
	soheader.crea_date,;
	soheader.start_date,;
	soheader.remark1,;
	soheader.remark2,;
	soheader.instr1,;
	soheader.instr2,;
	soheader.plandate,;
	soheader.rev,;
	soheader.fgloc,;
	soheader.mtlloc,;
	soheader.solineno,;
	soheader.Userid,;
	soheader.part_desc,;
	Soroute.opno As routeopno,;
	Soroute.loadcenter,;
	Soroute.Descrip As Routedes,;
	immaster.misc04 As lottrack,;
	immaster.upccode As upccode;
	FROM soheader Inner Join Soroute On soheader.sono = Soroute.sono;
	LEFT Join immaster On soheader.partno = immaster.Item;
	Into Cursor c1

Select c1.*,;
	Somater.partno As partno,;
	STR(Asc(Somater.Phanref),3)As Phanref,;
	STR(Asc(Somater.Phanid),3)As Phanid,;
	VAL(Somater.qty_assy) As qty_assy,;
	VAL(Somater.qty_aloc) As qty_aloc,;
	Somater.Delmark As Delmark;
	FROM c1 Left Join Somater On c1.sono = Somater.sono And c1.routeopno = Somater.opno;
	INTO Cursor c2


Select c2.*,;
	immaster.Descrip As Descrip,;
	immaster.stockum As stockum,;
	immaster.misc04 As misc04,;
	immaster.lotstat As lotstat;
	FROM c2 Left Join immaster On c2.partno = immaster.Item;
	Into Cursor c3

Select c3.*,;
	imstock.lonhand As lotqty,;
	imstock.locid As lotloc,;
	imstock.lot As lotnum;
	FROM c3 Left Join imstock On imstock.Item = c3.partno AND c3.mtlloc = imstock.locid;
	Into Cursor c4 

Select c4.*, Iif(Empty(Bmrev.fgparent), Bmrev.itemparent, Bmrev.fgparent ) As fg;
	FROM c4 Left Join Bmrev On c4.Item + c4.sorev = Bmrev.itemparent + Bmrev.rev;
	into Cursor C5

Select C5.*, bmsl.findno, bmsl.scrpad;
	FROM C5 Left Join bmsl On C5.fg + C5.partno + C5.sorev = bmsl.itemparent + bmsl.itemchild + bmsl.rev;
	INTO Cursor C6
	
SQLEXEC(connstr,"SELECT * FROM itemprojimstock WHERE 1=0","curAppend")	 
SET MULTILOCKS ON
CURSORSETPROP('Buffering',5,'curAppend')
CURSORSETPROP('Sendupdates',.T.,'curAppend')
CURSORSETPROP('Tables','itemprojimstock','curAppend')
CURSORSETPROP('KeyFieldList', 'ID','curAppend')
TEXT TO lcFieldList NOSHOW
Item, Sono, Sorev, Sqty, Needdate, Priority, Salesno, Crea_date, Start_date, Plandate, Rev, Fgloc, Mtlloc, solineno, userid, part_desc, routeopno, loadcenter, routedes, lottrack, upccode, partno, phanref, phanid, qty_assy, 
qty_aloc, delmark, descrip, stockum, misc04, lotstat, lotqty, lotloc, lotnum, fg, findno, scrpad
ENDTEXT
CURSORSETPROP('UpdatableFieldList', lcFieldList,'curAppend')
TEXT TO lcNameList NOSHOW 
Item itemprojimstock.Item, Sono itemprojimstock.Sono, Sorev itemprojimstock.Sorev, Sqty itemprojimstock.Sqty, Needdate itemprojimstock.Needdate, Priority itemprojimstock.Priority, Salesno itemprojimstock.Salesno, 
Crea_date itemprojimstock.Crea_date, Start_date itemprojimstock.Start_date, Plandate itemprojimstock.Plandate, Rev itemprojimstock.Rev, Fgloc itemprojimstock.Fgloc, Mtlloc itemprojimstock.Mtlloc, solineno itemprojimstock.solineno, 
userid itemprojimstock.userid, part_desc itemprojimstock.part_desc, routeopno itemprojimstock.routeopno, loadcenter itemprojimstock.loadcenter, routedes itemprojimstock.routedesc, lottrack itemprojimstock.lottrack, upccode itemprojimstock.upccode, 
partno itemprojimstock.partno, phanref itemprojimstock.phanref, phanid itemprojimstock.phanid, qty_assy itemprojimstock.qty_assy, qty_aloc itemprojimstock.qty_aloc, delmark itemprojimstock.delmark, descrip itemprojimstock.descrip, 
stockum itemprojimstock.stockum, misc04 itemprojimstock.misc04, lotstat itemprojimstock.lotstat, lotqty itemprojimstock.lotqty, lotloc itemprojimstock.lotloc, lotnum itemprojimstock.lotnum, fg itemprojimstock.fg, 
findno itemprojimstock.findno, scrpad itemprojimstock.scrpad
ENDTEXT
CURSORSETPROP('UpdateNameList', lcNameList,'curAppend') 
SELECT curAppend
APPEND FROM DBF("C6")
TABLEUPDATE(2,.T.,"curAppend") 
SQLDISCONNECT(connstr)

CLOSE ALL

As you can see, the code runs a bunch of SELECT statements, puts them in cursors and then puts the final cursor into a SQL table.
I have been working with Olaf on the SQL part, and he suggested using EXECSCRIPT with VFPOLEDB, but that produces an error (see this thread

http://www.tek-tips.com/viewthread.cfm?qid=1716941

at the end).

Does anyone have any suggestions for me?

Thanks in advance,

Chris

 

[craigber]

Why not use VFP OLE DB provider to pull the VFP data and then use the .Net SQL provider to get the SQL Server data, combine everything in C#, then update SQL Server?

Craig Berntson
MCSD, Visual C# MVP,

http://www.craigberntson.com/blog

 

[campagnolo1]

Craig,

thank you for your reply. I actually don't need the SQL data. All the VFP data gets used and then put into a SQL table. So all that would need to happen is to get the VFP data, clear the SQL table and put the VFP data into the SQL table.
Since I'm still fairly new to all this, do you maybe have an example (link) I could look at to see how I would go about this?

Thanks,

Chris

 

[craigber]

It's a bit old, but should still work for you.

http://msdn.microsoft.com/en-us/library/aa288452(v=vs.71).aspx

For accessing SQL Server, the current recommendation from Microsoft is to use Entity Framework.

http://msdn.microsoft.com/en-us/library/bb386876.aspx

Craig Berntson
MCSD, Visual C# MVP,

http://www.craigberntson.com/blog