Filling a muti-table typed dataset using a stored procedure

[Jeronimo Bertran]

Hi,

I have a stored procedure that makes two separate SELECT. I then create
a typed dataset and drag the stored procedure to the designer which creates
2 tables.... the first one by default has the name of the stored procedure
and the second one has the name Table1. I rename both tables to more
significant names.

I am trying to fill the typed dataset with a call to the stored
procedure.... however, calling Fill will actually create 2 additional
tables on my dataset. I have filled untyped datasets with multiple table
stored procedures but I can't seem to be able to solve it for typed
datasets.

Thanks,

Jeronimo

 

[Kevin Yu [MSFT]]

Hi Jeronimo,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that when you're trying to fill a typed
DataSet with DataAdapter with multiple result sets. If there is any
misunderstanding, please feel free to let me know.

In this case, two new tables are created because the DataAdapter cannot
decide which table the result should be filled to. So the resolution is to
add TableMappings to the DataAdapter. In the TableMappings property, we use
the database table name as the source, and table name in the DataSet as the
target name. Please check the following link for more information.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
frlrfsystemdatacommondataadapterclasstablemappingstopic.asp

Also the TableMappings property can be configured from IDE as the following
link shows.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vsintro7/ht
ml/vburfmappingsdialogbox.asp

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Jeronimo Bertran]

Thanks Kevin...

However I am still unsure on how to determine the database table name from
the Stored Procedure... Here is the example:

The stored procedure looks like the following:


ALTER PROCEDURE dbo.spSelectEvents
AS

SELECT Event.EventID, Event.DeviceID, Event.EventTime, Event.EventTypeID,
Device.DeviceName FROM Event INNER JOIN Device ON Event.DeviceID =
Device.DeviceID

SELECT EventTypeID, EventName, FROM EventType

RETURN


If I drag the stored procedure to a new DataSet in the designer, this is
the xsd generated:


<xs:element name="Dataset1" msdata:IsDataSet="true">
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element name="spSelectEvents">
<xs:complexType>
<xs:sequence>
<xs:element name="EventID" msdata:ReadOnly="true"
msdata:AutoIncrement="true" type="xs:long" />
<xs:element name="DeviceID" type="xs:int" minOccurs="0" />
<xs:element name="EventTime" type="xs:dateTime" />
<xs:element name="EventTypeID" type="xs:int" />
<xs:element name="DeviceName" type="xs:string" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Table1">
<xs:complexType>
<xs:sequence>
<xs:element name="EventTypeID" msdata:ReadOnly="true"
msdata:AutoIncrement="true" type="xs:int" />
<xs:element name="EventName" type="xs:string" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>



Should I use spSelectEvents for the first table and Table1 for the second
table???

 

[Kevin Yu [MSFT]]

Hi Jeronimo,

The name of the first table in the result set is Table. The second one is
Table1. The third one is Table2 and so on.

Now, if you change you table names into tblEvent and tblEventType, you can
add the following table mappings.

sda.TableMappings.Add("Table", "tblEvent");
sda.TableMappings.Add("Table1", "tblEventType");

I tried this on my machine, and it works fine. HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Jeronimo Bertran]

Thanks , it worked just fine.

 

[Kevin Yu [MSFT]]

You're welcome.

Thanks for sharing your experience with all the people here. If you have
any questions, please feel free to post them in the community.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."