SQL Statement

Posts   
 
    
wexa
User
Posts: 38
Joined: 27-Jul-2007
# Posted on: 06-Mar-2008 05:23:04   

Hello, I have a select statement, regularly I make a stord proc to get complicated selects, but is it possible to send directly an SQL statement and get the result as datatable??

Or do I have to do it by regularexpressions?

I also would like to know how to make a JOIN between 2 tables that have not a relation, I have a key that I can make a join but not a relation.

Is like this:

Cities CityID CityName StateId

States StateId StateName

How do you get the cities list with the state name if you have not the relation in database???

Regards

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 06-Mar-2008 09:30:20   

You can manually create an EntityRelation in code.

EntityRelation relation = new EntityRelation(StatesFields.StateId, CitiesFields.StateId, RelationType.OneToMany);

Then you can use this relation in a fetch method.

DataAccessAdapter adapter = new DataAccessAdapter();
ResultsetFields fields = new ResultsetFields(2);
fields.DefineField(CitiesFields.CityName, 0);
fields.DefineField(StatesFields.StateName, 1);

IRelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.Relations.Add(new EntityRelation(StatesFields.StateId, CitiesFields.StateId, RelationType.OneToMany));

DataTable dynamicList = new DataTable();
adapter.FetchTypedList(fields, dynamicList, bucket, 0, null, true, null);
wexa
User
Posts: 38
Joined: 27-Jul-2007
# Posted on: 16-Mar-2008 23:54:11   

Thank you very much.

One more question, If you have a datagrid with the ID of a related entity, and want to display related entitie's name in the grid, do you have to create an extra column? Do you make a view or what is the best way to do this??

For this case I have a relation in the database

Suppose I have

City State Los Angeles 3 Fresno 3

I wanto to display

City State Los Angeles California Fresno California

wexa
User
Posts: 38
Joined: 27-Jul-2007
# Posted on: 17-Mar-2008 00:27:07   

I have fixed the above question using a TypedList, but now my problem is this:

I am using windows forms, I have a grid and when you double click a row I open an edit Form for it.

I am using a currencyManager (datanavigator) so I was passing a collection to the EditForm and you could navigate trought the fetched collection and edit data.

Now that I have used the TypedList I am having troubles to send all the collection to the EditForm and allow user to navigate and edit results. What I am doing now is to send only the ID of the element that I need to edit.

How can you get an entitycollection from a TypedList??

Supose you have the CityId, CityName, StateID entitycollection and now I changed to a CityId,CityName, StateId,StateName typedList and need to create an EntityCollection of CityId,CityName and StateId so I can send it as I was doing before the TypedList

Regards

wexa
User
Posts: 38
Joined: 27-Jul-2007
# Posted on: 17-Mar-2008 01:07:11   

After checking the help (I was lost on this), I found that if I am not wrong I should use prefetch, so I did that, now the problem is how do you display the mapped fields on the grid ??

How do you display in a grid the related entities fields???

I hope I am in the right way

Regards

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 17-Mar-2008 06:21:59   

wexa wrote:

After checking the help (I was lost on this), I found that if I am not wrong I should use prefetch, so I did that, now the problem is how do you display the mapped fields on the grid ??

How do you display in a grid the related entities fields???

I hope I am in the right way

Regards

This time are you using WinForms or WebForms? This is thread about winforms: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=5018 and this is about webforms: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=6922&StartAtMessage=0&#38136

I recommend you to download and try the LLBLGenPro examples: http://www.llblgen.com/pages/examples.aspx

David Elizondo | LLBLGen Support Team
wexa
User
Posts: 38
Joined: 27-Jul-2007
# Posted on: 17-Mar-2008 21:24:33   

Thank you very much!!