Hi There

Having a few problems figuring this out & seem to be going around in Circles.

I am retrieving data from SQL through a stored procedure because I want to ensure it never returns more than 100 rows ( I force a top 100 ). I can't afford for there to be a huge amount of data returned because of memory restrictions on a VPS.

I tried doing it according to the documentation ( .Net version 2.0 ) .

There are 2 issues.

1) I can't seem to follow the docuimentation properly - the documentation says it is for .net 1.x but to use .net 2.x generic classes. I don't quite get it. Can you provide an example in pure .net version 2? I want to project all fields - I think there is a shortcut for this?

2) I am running this code on 2 seperate databases using adapter, so I need to set the default catalog & connections string. It doesn't appear that you can use .. IRetrievalQuery query = RetrievalProcedures.xxxx(params, adapter ) - and I can't see how to specify the default catalog / connection string for the default adapter at runtime.

I would have thought this was easy but I have spent quite a few hours on it.

Can you help?

Kind Regards Marty

1) I can't seem to follow the docuimentation properly - the documentation says it is for .net 1.x but to use .net 2.x generic classes. I don't quite get it. Can you provide an example in pure .net version 2? I want to project all fields - I think there is a shortcut for this?

Just use the following 2 lines instead of the ones found in the .NET 1.x example, to instantiate customers and orders collections.

EntityCollection<CustomerEntity> customers = new EntityCollection<CustomerEntity>();
EntityCollection<OrderEntity> orders = new EntityCollection<OrderEntity>();

2) I am running this code on 2 seperate databases using adapter, so I need to set the default catalog & connections string. It doesn't appear that you can use .. IRetrievalQuery query = RetrievalProcedures.xxxx(params, adapter ) - and I can't see how to specify the default catalog / connection string for the default adapter at runtime.

The catalog name can be changed using a config setting. Please check Catalog name overwriting

THanks, I know how to do catalog overwriting - but the problem is when I use the method

RetrievalProcedures.GetCommandasQuery(params) - there is no place to put the adapter with the overwritten ( overwritten at runtime ) catalog - and I can't figure out how to change the default adapter catalog at runtime. So when I run the GetxxxasQuery() it uses the default adapter settings.

Make sense?

This kind of works the other way round, once you have fetched the RetrievalQuery using

IRetrievalQuery query = RetrievalProcedures.GetCommandasQuery(params)

you run it using

DataAccessAdapter.FetchDataReader(query, CommandBehavior.Default)

or any of the other CommandBehaviour values

using a DataAccessAdapter on which you have already done your Schema and Catalog name overwriting as needed.

I'm changing the way I'm doing this I think.

How can I convert a Dataset retrieved from a web service into an Entity collection ( dataset always contains 2 datatables, generated from a stored procedure ). I can see how to do it the other way, but not from a dataset to an entity collection.

Note: I don't want to put any projections into the web service - I want to do it after I retrieve the dataset. Is that possible?

Can you help?

Thanks for your time Marty

martytheman wrote:

How can I convert a Dataset retrieved from a web service into an Entity collection ( dataset always contains 2 datatables, generated from a stored procedure ). I can see how to do it the other way, but not from a dataset to an entity collection.

static EntityCollection<CustomerEntity> CustomersDataSetToEntityCollection(DataSet customersDS)
{
    // collection to fill
    EntityCollection<CustomerEntity> customersToRetun = new EntityCollection<CustomerEntity>();

    // the datareader to loop on        
    // here we use the first table, suppose this is "Customer". You can use the table name too. 
    using (IDataReader reader = customersDS.Tables[0].CreateDataReader())
    {
        List<IDataValueProjector> valueProjectors = new List<IDataValueProjector>();

        /// Set the valueProjector for each field you want to project. 
        /// You have to be aware of the order of the ds columns, so here, firt is Fax, then Phone, etc. 
        valueProjectors.Add(new DataValueProjector(CustomerFields.Fax.Name, 0, typeof(System.String)));
        valueProjectors.Add(new DataValueProjector(CustomerFields.Phone.Name, 1, typeof(System.String)));
        // ... next fields

        // switch to the next resultset in the datareader
        reader.NextResult();

        // create the projector
        DataProjectorToIEntityCollection2 projector = new DataProjectorToIEntityCollection2(customersToRetun);

        // fetch the projection. This actually wont fire any fetch from DB. Just project the values from you filled DS
        DataAccessAdapter adapter = new DataAccessAdapter();                
        adapter.FetchProjection(valueProjectors, projector, reader);

        // close the reader
        reader.Close();     
    }

    return customersToRetun;
}

Do the same for the other table. So the usage would looks like:

EntityCollection<CustomerEntity> convertedCustomers = CustomersDataSetToEntityCollection(ds);
EntityCollection<OrdersEntity> convertedOrders = OrdersDataSetToEntityCollection(ds)

The downside: if you want to merge those to collections you have to do it by yourself.

Merging them is indeed a tad problematic as the logic used by the prefetch paths is internal static in PersistenceCore.cs (MergeNormal). That logic uses hashcodes for PK/FK key values to quickly find the parent for a child.

You could use a hashtable/dictionary here, and store the parents in that, using the PK field as the key, then traverse the children, and for the FK value grab the parent from the dictionary and call parent.SetRelatedEntityProperty(propertyName, childEntity);

e.g.: customer.SetRelatedEntityProperty("Orders", order);

hmm.

Is there a way to explicitly add Top N to the beginning of the Dynamic SQL? THen I could avoid this whole issue.

It seems if you restrict the number returned it doesn't actually put a top n in the SQL Clause, just returns n rows ( but might use heaps of memory ) . I can't afford to use large amounts of memory as it's on a VPS with limited resources. That's the whole reason I am putting it into a stored proc.

Thanks for your time & comments guys.

Marty

Is there a way to explicitly add Top N to the beginning of the Dynamic SQL? THen I could avoid this whole issue.

It seems if you restrict the number returned it doesn't actually put a top n in the SQL Clause, just returns n rows ( but might use heaps of memory ) . I can't afford to use large amounts of memory as it's on a VPS with limited resources. That's the whole reason I am putting it into a stored proc.

Is this a DynamicList fetch or an EntityCollection fetch?

It's Entity Collection Fetch

Thx Marty

This fetch snippet code will return only the first 10 entities into the orders collection.

int itemsToReturn = 10;
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.FetchEntityCollection(
         orders, 
         someIRelationPredicateBucket, 
         itemsToReturn, 
         someSortExpression, 
         somePrefetchPath);
}

Or you can use pages. This code will return the 21-30 records.

int pageNumber = 3;
int pageSize = 10;
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.FetchEntityCollection(
         orders, 
         someIRelationPredicateBucket, 
         itemsToReturn, 
         someSortExpression, 
         somePrefetchPath, 
         pageNumber, 
         pageSize);
}

Thanks for the Idea, but doesn't solve my problem.

I am using SQL Server 2000 ( maybe different for 2005 ) .

I have tried the # of items to return , and yes it only returns the top N.
However the SQL Statement doesn't explicitly say "SELECT TOP 10..." ( I put a trace on the SQL Server ) .

This is a problem because it is obviously returning all rows from SQL Server and the only putting the first 10 into the collection. I have limited resources on the machine that I am running this code on.

Basically what happenned the other night is that I had the web service going, there was something slighly wrong with the data it was requestiong, so it selected a large part of the table , which resulted in the whole VPS going down because it ran out of resources.

I wouldn't have thought it was too difficult to add "TOP 10 " into the Dynamic SQL and would be more efficient than returning all the data and just putting the first 10 rows.

Maybe I am missing something?

Thanks Marty

You have a blob field in the resultset ? (ntext/image/text) ? If so, it could be it can't specify TOP because the DQE thinks duplicates will occur and it can't emit DISTINCT when image/text/ntext fields are in the select list. It will limit on the client. This isn't that bad: it will fetch only the number of rows you specified, and after that it will close the datareader. If you can, you can exclude the image/ntext/text fields.

In a recent build of the v2.6 runtimes we updated the detection code for duplicates so if you're using outdated v2.6 runtimes it also might help.