ADO.net question

[Matt]

Hi,

I have an xsd which contains two tables, for the sake of this example
I'll call them Person and Salutation. Person contains personal
details, plus a key reference to the salutation table which contains
salutations i.e. Mr Ms Mrs etc.

So, I have filled the two tables in the xsd and would like to display
the results of a join on these two tables (linking the salutation key
to an actual salutation) into a datagrid (web or client doesn't
matter). I cannot write custom SQL in the DAL and feed that into the
grid, I have to use the given tables in the xsd.

So my question is, other than writing logic to iterate over the person
data and insert the correct salutation into a new dataview and pass
this to the grid, is there an alternative? Can you specify how to join
ado.net datatables - in a view for example?

Thanks in advance,
Matt

 

[Alberto Poblacion]

So my question is, other than writing logic to iterate over the person
data and insert the correct salutation into a new dataview and pass
this to the grid, is there an alternative? Can you specify how to join
ado.net datatables - in a view for example?

No, the current version does not provide a "join" functionality for
tables in a DataSet. When C# version 3.0 comes out (in the next version of
Visual Studio, codename "Orcas"), you will be able to do that by means of
LINQ. Curently you have to iterate and perform the join within your own
code.

 

[Peter Bradley]

No, the current version does not provide a "join" functionality for
tables in a DataSet. When C# version 3.0 comes out (in the next version of
Visual Studio, codename "Orcas"), you will be able to do that by means of
LINQ. Curently you have to iterate and perform the join within your own
code.

But you could presumably create a stored procedure that returned the result
of the join and bind that to a Typed DataSet ...


Peter

 

[Marc Gravell]

Not ADO.Net specific, but another way to looks at this is with
type-converters. E.g. I quite often do the following:

public class SomeClass {
[TypeConverter(typeof(KeywordIdConverter)), Keyword("TITLE")]
public int TitleId {get; set;} // fairly normal implementations not
shown
}

Where (for instance) KeywordIdConverter that you write which converts
to/from strings looking at the bespoke KeywordAttribute (via the
ITypeDescriptorContext) to resolve the actual string, usually
involving local caching and lazy-loading. The great thing is: once
written it simply works. Most data-bindings respect TypeConverter
flags and so in a DataGridView or whatever you see the text that the
converter returns. You can also implement drop-downs etc via
SupportsStandardValues. The other advantage is that code that only
needs ids can work quite happily against the object model without ever
invoking the type-converter code, which is UI-specific.

Marc

 

[sloan]

You can bind the Person table to the GridView.

Then you can write a little function ... (code behind of the webpage)


Public string GetSalutation (int salid) //<< this salutationid is the
PERSON's salutation id
{

return m_modelDS.Salutation.Select("SalutationID=" +
convert.ToString(salid) )[0].SalutationName;

}

something like that.

the key is that you have a member variable with all the ds info in it.

and you write a .Select to get the correct salutationid. the code above
won't work perfectly, you'll have to fudge it a bit.

then in the binder code you have

<% GetSalutation(Convert.ToInt32("SalutationID")) %>
or something like that. (going from memory right now) There is prob a Eval
in there somewhere.

That's what I do when I have this type if scenario. Basically "I need to
Present a lookup table value"

 

[Matt]

Thanks for all the quick replies. Unfortunately I can't write any
stored procedures or alter the database schema in any way. Im working
on a legacy system the has been implemented by someone who should
really needs to read about 3 tier architectures. To deviate from their
design pattern would just obfuscate things further.

Looks like i'm going to be writing iterative methods until the might
LINQ is released, man that looks awesome!!!!


Thanks once again,
Matt