Problem in building dynamic queries, using Order by and built in Stored Procedures

Hi,

I have a problem with building dynamic queries.

There is table XXX with a Primary key “pk_id” in it. That table also has a column called “deleted”. Say I want to get a single record based on a particular id and deleted tag is 0.

So, what I have done is created a sp for it

Create procedure getbyidcheckdeltag

@id int

as

begin

            select * from XXX where id = @id and deleted!=1;

end

This should return a XXX record.

But it returns a Dataset.

Am I missing anything in this?

Or should I use the following solution

XXXParameterBuilder param = new XXXParameterBuilder();

Param.Append(XXXColumns.Deleted,0);

Param.Append(XXXColumns.id,23);

Datarepository.XXXProvider.find(Param.getparameters());

Why is the first solution returning a dataset ?

The second solution returns a tlist<>. But I need a XXX object.

I could do

Foreach(XXX test in (object of Tlist<>)) to get the entity but is there any direct method to get the entity object.

Consider the following query,

Select * from XXX where name like ‘a%’ order by name desc, address asc;

This too returns a dataset if created as a SP.

Is there any other way to specify the order by clause rather than

1. using a SP(I would go for this if it returns a Tlist<>)
2. tlist<XXX> test = Datarepository.XXXProvider.getAll();

PropertyDescriptorcollection coll = typedescriptor.getproperties(typeof(XXX));

test.ApplySort(coll[“name”],listsortdirection.Descending);

test.ApplySort(coll[“add”],listsortdirection.Ascending);

I am looking for a direct way which will hit the database and get me a result that does not require any iterations after that.

Is there any way to access a built in SQL Server 2005 Stored procedure in the DAL.

Assume that I am trying to use a built in stored procedure that returns a last identity column value (so it returns a int) when given the table name as the parameter.

If I embed this Sp in another SP so that I can use it my application using the nettires, it returns a dataset. I expect a int.

How do I solve this ?

Thanks in advance for any help guys,