OLEDB and stored procedures

I would like to return a cursor from a stored procedure into ASP.NET but seem to get nowhere. Reason why I want to do that is performance. I need to pull a customers open invoices from a table with about 250,000 records. I was unhappy with the performance of the following code.

Dim cn_ageing As New OleDbConnection ("Provider=VFPOLEDB.1;" & "Data Source=" & Chr(34) & "f:\asptest\Accounts Receivables\ACCOUNTS RECEIVABLES.DBC" & Chr(34))

Dim strsql2 As String = "select invno, invdte, invamt, paidamt, balance from Balance_due_detail where custno = '" & DropDownList1.SelectedValue & "' AND balance # 0"

Dim cmd_ar As New OleDbDataAdapter (strsql2, cn_ageing)

Dim ds_age As DataSet = New DataSet()

cmd_ar.Fill(ds, "ageing")

Grd_Age.DataSource = ds.Tables("ageing")

DataBind()

Takes about 9 seconds when selecting the customer "TEST"

I created a view called testview in the VFP9.0 database with the SQL statement (hardcoding a customer.) AS select invno, invdte, invamt, paidamt, balance from Balance_due_detail where custno = 'TEST' AND balance # 0" and then changed the SQL statement in the ASP page to "SELECT * FROM testview" And the data returned in about 2 seconds. A considerable improvement Now I could solve that with a parameterized view but could not find anything on how to pass a parameter to a parameterized view through VFPOLEDB

So the other idea was to use a stored procedure

PROCEDURE getage

PARAMETERS lc_custno

select invno, invdte, invamt, paidamt, balance from Balance_due_detail where custno = lc_custno AND balance # 0"

RETURN

Now changing the SQL statement in ASPNET to

"EXEC getage('" & DropDownList1.SelectedValue & "')"

will return a table with 1 record and one field containing -1

How can I get the whole cursor

Visual FoxPro2

To return a recordset you would use SetResultSet().

VFP9 Northwind database have sample procedures with SetResultSet in it. ie: One of them:

*------------------------------------------------
PROCEDURE CustOrderHist(tcCustomerID as String )
*------------------------------------------------
LOCAL lcCustomerID as String
lcCustomerID = IIF(VARTYPE(tcCustomerID)!="C","",tcCustomerID)
SELECT ProductName, SUM(Quantity) AS Total ;
FROM Products P, OrderDetails OD, Orders O, Customers C ;
WHERE C.CustomerID = lcCustomerID ;
AND C.CustomerID = O.CustomerID ;
AND O.OrderID = OD.OrderID ;
AND OD.ProductID = P.ProductID ;
GROUP BY ProductName ;
INTO CURSOR CustOrderHist

SETRESULTSET('CustOrderHist')
RETURN RECCOUNT('CustOrderHist')
ENDPROC

And here is an ASP.Net page using that procedure (along with an additional grid which just selects and shows the customers id,company,contact fields):

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server"><div>
<asp:Label ID="Label1" runat="server" Text="Customer Id"></asp:Label><asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
<asp:Button ID="Button1" runat="server" Text="Go" /><br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="productname" HeaderText="productname" SortExpression="productname" />
<asp:BoundField DataField="total" HeaderText="total" SortExpression="total" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" SelectCommand='CustOrderHist( )'>
<SelectParameters>
<asp:ControlParameter ControlID="TextBox1" Name=" " PropertyName="Text" />
</SelectParameters>
</asp:SqlDataSource>
<asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="False" DataKeyNames="customerid" DataSourceID="SqlDataSource2">
<Columns>
<asp:BoundField DataField="customerid" HeaderText="customerid" ReadOnly="True" SortExpression="customerid" />
<asp:BoundField DataField="companyname" HeaderText="companyname" SortExpression="companyname" />
<asp:BoundField DataField="contactname" HeaderText="contactname" SortExpression="contactname" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>"
SelectCommand="SELECT customerid, companyname, contactname FROM Customers WHERE (customerid = )">
<SelectParameters>
<asp:ControlParameter ControlID="TextBox1" Name=" " PropertyName="Text" />
</SelectParameters>
</asp:SqlDataSource>
</div></form>
</body></html>

Config file has:

PS: It's suspicious that a view made such a big difference.