Convert an untyped Data Table to a typed Data Table

14 May 2009 5:26 PM

John Wright

I need to convert some of my untyped datatables to typed datatables. Is
there a way to do this? I am assuming I need to convert the table to XML
then back, but I am unsure. Does anyone have any code or can point me in the
right direciton? I really appreciate the help.

John

14 May 2009 6:11 PM

sloan

You might grab an idea from here:
http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!148.entry

Or you can loop over the untyped rows, and then add rows to the typed
datatable. (which I guess is the brute force method).

..........

Show quoteHide quote

"John Wright" <JohnWri***@discussions.microsoft.com> wrote in message
news:AF05C078-73B3-4589-90D8-1B4E2558468B@microsoft.com...
>I need to convert some of my untyped datatables to typed datatables. Is
> there a way to do this? I am assuming I need to convert the table to XML
> then back, but I am unsure. Does anyone have any code or can point me in
> the
> right direciton? I really appreciate the help.
>
> John

14 May 2009 7:32 PM

Branco

John Wright wrote:

> I need to convert some of my untyped datatables to typed datatables. Is
> there a way to do this? I am assuming I need to convert the table to XML
> then back, but I am unsure. Does anyone have any code or can point me in the
> right direciton? I really appreciate the help.

The easier and most flexible solution (IMHO) is the dataset designer
which will generate a dataset with datatables built after the schemma
of an existing database. It will create the dataset, the datatables,
the relations between the tables and even the table adapters.

If you have the datatables but no database, you may use the xsd.exe
utility (in my setup it lives in %programfiles%\Microsoft Visual
Studio 8\SDK\v2.0\Bin"). This utility will practically mimic the work
of the designer (except for not automagically creating the table
adapters). It feeds on XSD files and generates datasets (or classes)
based on that. The nice touch is that it can create a XSD file based
on a sample XML file.

For example, given the following Dept.xml file:

<Dept>
<Employee>
<Id>10</Id>
<Name>Jack</Name>
</Employee>
<JobType>
<Id>1</Id>
<Name>Director</Name>
</JobType>
</Dept>

after executing "xsd Dept.xml", a Dept.xsd file is created (beware
word wrap):

<?xml version="1.0" encoding="utf-8"?>
<xs:schema id="Dept" xmlns="" xmlns:xs="http://www.w3.org/2001/
XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="Dept" msdata:IsDataSet="true" msdata:Locale="en-
US">
    <xs:complexType>
      <xs:choice minOccurs="0" maxOccurs="unbounded">
        <xs:element name="Employee">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="Id" type="xs:string" minOccurs="0" />
              <xs:element name="Name" type="xs:string" minOccurs="0" /

>

            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element name="JobType">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="Id" type="xs:string" minOccurs="0" />
              <xs:element name="Name" type="xs:string" minOccurs="0" /

>

            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:choice>
    </xs:complexType>
</xs:element>
</xs:schema>

You may edit the XSD to your liking (for example, changing the type
attribute of both Id columns from "xs:string" to xs:int"). Afterwards,
executing "xsd Dept.xsd /dataset /language:VB" generates the Dept.vb
file, containing the declaration for a strongly typed "Dept" dataset
as well as the two strongly typed tables "Employee" and "JobType".
Maybe the resulting code isn't pretty enough to your tastes (it isn't
to mine), but it makes a stepping stone for your further edits.

HTH.

Regards,

Branco.

14 May 2009 7:48 PM

John Wright

I guess I need to qualify me first question a little. I have some code that
we use now in programs that returns an untyped datatable. Recently, we have
found a need to use this untyped datatable with some LINQ queries. I can
write the LINQ over ADO.NET with untyped datatables, but it is nice to have
the intellisense for development. What I want to do is pass in an untyped
datatable to a function and have it return a typed datatable. What I am
looking for is some code or ideas to do this.

John

Show quoteHide quote

"Branco" wrote:

> John Wright wrote:
> > I need to convert some of my untyped datatables to typed datatables. Is
> > there a way to do this? I am assuming I need to convert the table to XML
> > then back, but I am unsure. Does anyone have any code or can point me in the
> > right direciton? I really appreciate the help.
>
> The easier and most flexible solution (IMHO) is the dataset designer
> which will generate a dataset with datatables built after the schemma
> of an existing database. It will create the dataset, the datatables,
> the relations between the tables and even the table adapters.
>
> If you have the datatables but no database, you may use the xsd.exe
> utility (in my setup it lives in %programfiles%\Microsoft Visual
> Studio 8\SDK\v2.0\Bin"). This utility will practically mimic the work
> of the designer (except for not automagically creating the table
> adapters). It feeds on XSD files and generates datasets (or classes)
> based on that. The nice touch is that it can create a XSD file based
> on a sample XML file.
>
> For example, given the following Dept.xml file:
>
> <Dept>
> <Employee>
> <Id>10</Id>
> <Name>Jack</Name>
> </Employee>
> <JobType>
> <Id>1</Id>
> <Name>Director</Name>
> </JobType>
> </Dept>
>
> after executing "xsd Dept.xml", a Dept.xsd file is created (beware
> word wrap):
>
> <?xml version="1.0" encoding="utf-8"?>
> <xs:schema id="Dept" xmlns="" xmlns:xs="http://www.w3.org/2001/
> XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
>   <xs:element name="Dept" msdata:IsDataSet="true" msdata:Locale="en-
> US">
>     <xs:complexType>
>       <xs:choice minOccurs="0" maxOccurs="unbounded">
>         <xs:element name="Employee">
>           <xs:complexType>
>             <xs:sequence>
>               <xs:element name="Id" type="xs:string" minOccurs="0" />
>               <xs:element name="Name" type="xs:string" minOccurs="0" /
> >
>             </xs:sequence>
>           </xs:complexType>
>         </xs:element>
>         <xs:element name="JobType">
>           <xs:complexType>
>             <xs:sequence>
>               <xs:element name="Id" type="xs:string" minOccurs="0" />
>               <xs:element name="Name" type="xs:string" minOccurs="0" /
> >
>             </xs:sequence>
>           </xs:complexType>
>         </xs:element>
>       </xs:choice>
>     </xs:complexType>
>   </xs:element>
> </xs:schema>
>
> You may edit the XSD to your liking (for example, changing the type
> attribute of both Id columns from "xs:string" to xs:int"). Afterwards,
> executing "xsd Dept.xsd /dataset /language:VB" generates the Dept.vb
> file, containing the declaration for a strongly typed "Dept" dataset
> as well as the two strongly typed tables "Employee" and "JobType".
> Maybe the resulting code isn't pretty enough to your tastes (it isn't
> to mine), but it makes a stepping stone for your further edits.
>
> HTH.
>
> Regards,
>
> Branco.
>