Create a datatable in Foxpro to send to sql stored procedure

[flaspk]

I have a user defined table in sql which is the parameter I pass in a stored procedure.
Using C#, I create a datatable to match the structure and pass the datatable to the stored procedure.

Is this possible in Foxpro? How can I pass the a foxpro database/cursor to the same stored procedure?

Thanks in advance,
Flasphk

 

[Mike Lewis]

Where is the stored procedure stored? In VFP, stored procedures are always stored in a database, so passing a database as a parameter to the stored procedure doesn't make sense. If you want to "pass" a table or a cursor, you don't have to do anything special. The procedure will have access to the table or cursor, just as with any other bit of Foxpro code.

Or, is the stored procedure stored in a back-end database like MySQL or SQL Server? If so, there is no way of directly passing a table or cursor (I assume you mean a VFP table or cursor?) to the procedure. But you can pass the contents of the table or cursor in the form of a stream of text or perhaps an XML string.

It would be helpful if you could clarify the above point.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[flaspk]

Sorry for the lack of information.

The stored procedure is located in a SQL Server database.

I created by user defined table type in SQL Server
Create type myuserdefinedtabletype AS Table
(
Value1 as varchar(20),
Value2 as varchar(10),
Value3 as varchar(30)
)


The stored procedure itself is basically:

Create procedure [sp_UpdateTable] @datatable myuserdefinedtabletype READONLY
as
(
Insert into InsertTable (value1, value2, value3)
Select Value1, Value2, Value3 from @datatable
)

In C# I can create a datatable to pass to the stored procedure using SQLCommand.
**conn = Connection String
** dt = My data table
SqlCommand cmd = new SQLCommand("sp_UpdateTable", conn);
cmd.CommandType = Command.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@datatable", dt))
var value = cmd.ExecuteNonQuery();


I need to do the same thing in Foxpro. . .Is this possible?

Thanks

 

[Olaf Doschke]

There is no such equivalent in VFP.

VFP has something similar to the SQLCommand, but not as an object, just an SQLExec() function, it has a parameter to send over a sql scropt, not just one command. This can obviously be used quite universally, from

SQLExec(h,"Insert Into sometable (field1, field2,...) values (?foxprovariable1, ?foxprovariable2,...)")

up to

TEXT TO lcSQL NOSHOW
Declare @tableparameter Table (Value1 as varchar(20), Value2 as varchar(10), Value3 as varchar(30));

Insert Into @tableparameter VALUES ('record','number','one') , ('record','number','two)....
sp_UpdateTable(@tableparameter)
ENDTEXT
SQLExec(h,lcSQL)

If your sp is about updating one single table, then the usual way would be to actually first QUERY records of a table you want to modify into a VFP cursor, make that cursor updatable, modify data, delete records or add records in this VFP cursor, and then do TABLEUPDATE(), which rather works like the entity frameworks Context.SaveChanges(). It is limited to one cursor on the VFP side, but can even update multiple tables, as you can do any query with joins and then map back VFP cursor fields to remote table fields via some properties. The usual case is the simplest "entity model" with a single table.

To create such an updatable VFP cursor you have three ways:
1. SQL passthrough queries combined with CURSORSETPROP you do on the result cursor.
2. Remote views
3. Cursoradapters

Any of these mechanism plus VFPs own way of the SaveChanges method, the TableUpdate() function call, makes up the best practice to act on data. In the same manner VFP handles its native tables, DBF files, for which you don't need to create a cursor, but simply open them with USE.

Only want to insert? Well, your initial query just needs to be WHERE 1=0 and you only append data into the VFP cursor and push this data into SQL Server via the final TableUpdate(). Only want to delete? Well, then you can query all the data you want to delete, delete it in the VFP cursor and then forward all the single DELETE FROM TABLE WHERE id = ? via a TABLEUPDATE(), but in that case rather just send over the DELETE sql command instead of first loading data.

More complex situations, more tables? Well, you do more VFP cursors, the practical limit is your RAM only. Especially with the Cursoradapter base class you can design classes, which you can let reference each other and cascade TABLEUPDATEs of a hierarchy of tables like orders and orderdetails, defining the orderdetail cursoradapterobject as childobject of the orders cursoradapter. VFP also has collections, though don't get the idea you need an object per record, every cursoradapter object maintains a cursor, which by now should be clear, is similar to a C# datatable.

OK, that said this is the top of the iceberg, there's a lot to learn, if you're new to VFP. It's easy to SQLExec a query and let a cursor metarialize without first needing to define any model and context, only prerequisite is a connection you do with SQLStringConnect needing a connection string as major ingredient and resulting in a handle used by SQLExec() and other SQL passthrough functions. Preparing a cursor to write back to SQL is more complex, though in the end it's just a bunch of details you specify: 1. tables, 2. keyfield(s), 3. which fields to update and 4. mapping of VFP cursor fuield names and SQL Server full qualilfied names (table.field). Remote View and Cursor Adapter allow you to define a schema the cursor is pressed into, allowing other types, more precise types than VFP and ODBC infer from the query alone. Without going too far, that mainly means I suggest you concentrate on Cursoradapter as the most complete mechanism to remote data in VFP.

Bye, Olaf.

 

[flaspk]

Thank you so much Olaf. . .

Your answer was exactly what I needed. I did not know if there was a compatible way of doing the same thing in Foxpro I was doing in C# ( )

I will just write a different sql pass through for my foxpro process not using the user define table type in SQL.

Again thank you,
Flaspk