Random Dev Notes

June 2, 2013

Query large number of Ids without getting VFP Error 1812 – Statement too long

Filed under: .Net Development — Tags: — Tom Brothers @ 9:08 am

I actually wrote something about this issue awhile back in regards to LINQ to VFP.  In this post I’d like to show a simpler example.

   1:  // Simulate getting a large number Ids 
   2:  var orderIds = Enumerable.Range(10000, 1000).ToArray();
   3:   
   4:  // ToXmlToCursorFormattedXml is an Array extension method in the VfpClient namespace.
   5:  var xml = orderIds.ToXmlToCursorFormattedXml();
   6:   
   7:  using (var connection = new VfpConnection(ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString)) {
   8:      using (var command = connection.CreateCommand()) {
   9:          command.CommandText = @"
  10:  select o.OrderId, c.CustomerId, c.CompanyName
  11:      from Orders o
  12:      inner join Customers c on upper(allt(o.CustomerId)) == upper(allt(c.CustomerId))
  13:      where o.OrderId in (select Id from (iif(XmlToCursor(@OrderIdsXml, 'curTempIdList') > 0, 'curTempIdList', '')))";
  14:   
  15:          command.Parameters.AddWithValue("@OrderIdsXml", xml);
  16:   
  17:          var dataAdapter = new VfpDataAdapter(command);
  18:          var dataTable = new DataTable();
  19:   
  20:          dataAdapter.Fill(dataTable);
  21:      }
  22:  }


* Example code can be found in Visual FoxPro .Net Examples.

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: