StrataFrame Forum » StrataFrame Application Framework - V1 » Business Objects and Data Access (How do I?) » FillByParent

FillByParent

Paul Chase — Thu, 10 Jul 2008 18:50:08 GMT

If I attempt to use the FillByParent Method where the Parent has more than 2100 records the method fails with the following exception. I may have a few places where it is possible that the user can select more than 2100 parent records and a fill by parent would be called. I havent looked into how the fillbyparent method works yet but was hoping for any suggestions on how to best handle this.

Thanks

Paul

[codesnippet]InnerException: System.Data.SqlClient.SqlException
       Class=16
       ErrorCode=-2146232060
       LineNumber=1
       Message="The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Too many parameters were provided in this RPC request. The maximum is 2100."
       Number=8003
       Procedure=""
       Server="ares"
       Source=".Net SqlClient Data Provider"
       State=1[/codesnippet]

RE: FillByParent

Paul Chase — Thu, 10 Jul 2008 18:50:08 GMT

Thanks Ben,

I was thinking along the lines of the first suggestion you had anways , I just haven't gotten back to it yet, it should be an infrequent thing but it is possible and valid for a user to need that much data so for the time being I put in a freindly message that lets the user know that they are selecting to many records and bail just to keep the whining to a minumum. most likely I will implement the first route and come back to the CLR to improve perfomance.

RE: FillByParent

StrataFrame Team — Thu, 10 Jul 2008 10:07:45 GMT

If you go the CLR UDF route, your query would then look like this:

SELECT [fields] FROM ChildTable AS CT INNER JOIN dbo.Udf_ByteParser(@pks) AS BP ON CT.foreignkey = BP.primarykey

The @pks would be the byte[] containing the pk values

RE: FillByParent

StrataFrame Team — Thu, 10 Jul 2008 10:04:09 GMT

The FillByParent method creates a big query that fills by the foreign key of the child using an IN() to list the primary keys of the parent. So, say the parent is filled with 5 records, pk 2,4,6,8, & 10. The query would look like this:

SELECT [allfields list] FROM ChildSchema.ChildTable WHERE foreignkey IN (@p1,@p2,@p3,@p4,@p5)

with the @p1-5 being the evens 2-10. The TDS parser of SQL Server only supports 2100 parameters; though I have had problems with more than 1000. Basically, when we tried to do this in a VFP setting (only supported ~25 parameters before it died), we had to break the command into multiple queries, pass 25 parameters at a time, and AppendDataTable() the results into the business object.

Congratulations, though, Paul, you're the first person to reach the 2100 limit on SQL Server :)

Basically, if you need it soon, you could write your own method using the source from the current FillByParent and break the query into several queries and AppendDataTable() the results each time. We'll also modify the FillByParent to use separate commands if it gets larger than 2100, but I can't guarantee when that will happen.

Also, if you really wanted to get creative ;), you could use a System.IO.BinaryWriter to write a byte[] of the primary keys from the business object. You could then pass that byte[] to a CLR table-valued UDF that would return a single column of the pks. You could then join this result-set into the child table on the foreign key and get all of the records in one big query. In fact, that would probably give you much better performance than using the IN() operator of SQL Server (a join is always going to be faster than using an IN). We've had to use this functionality (passing byte arrays into SQL server and converting them into joinable result-sets) because we had some non-committed records that we had to use in a query (so the user didn't have to save before getting some info we were calculating).