How to remove tables from a strongly typed dataset

  • Thread starter Thread starter Kenneth Bohman
  • Start date Start date
K

Kenneth Bohman

I usually hard code my datasets, but this time I thought
I'd use the drag-and-drop features of 1.0.

I created a command object pointing to a SP. The SP always
return one resultset consisting of one record. No more, no
less.

I created a dataadapter object no problem, but when
generating the dataset I'm always given two tables: Table
and Table1. There is no way I can (de)select on and not
the other. Table Mapping one does not effect the other
(thanks for that). When previewing data only the first
table get filled.

How can I get rid of the other table? Removing it from the
xsd doesn't change anything of course. There probably lies
some very clever logic behind, but it is at this point
rather difficult for me to appreciate it fully.

Regards,

Kenneth Bohman
 
Thanks for your reply Miha

Here is the (very simple) SP

CREATE PROCEDURE dbo.GetReportPeriodByID
(
@ReportPeriodID nvarchar(8)
)
AS

SELECT [ReportPeriodID], [EndDate], [CurrencyID], [TimeStamp]
FROM dbo.ReportPeriods
WHERE ReportPeriodid = @ReportPeriodID
ORDER BY ReportPeriodID

RETURN
GO
 
Hi Kenneth,

Huh, that's weird.
What do your strong typed tables look like?
What does adapter put in?

--
Miha Markic - RightHand .NET consulting & software development
miha at rthand com

Kenneth Bohman said:
Thanks for your reply Miha

Here is the (very simple) SP

CREATE PROCEDURE dbo.GetReportPeriodByID
(
@ReportPeriodID nvarchar(8)
)
AS

SELECT [ReportPeriodID], [EndDate], [CurrencyID], [TimeStamp]
FROM dbo.ReportPeriods
WHERE ReportPeriodid = @ReportPeriodID
ORDER BY ReportPeriodID

RETURN
GO
Click to expand...
 
Yes, isn't it weird?

The second table doesn't really do any harm. What I'm interested in is why
it happens, where I can find what controls this behaviour, and how to
correct it.

1) This is what the table looks like in SQL Server 2000
CREATE TABLE "dbo"."ReportPeriods" (
"ReportPeriodID" nvarchar (8) NOT NULL PRIMARY KEY,
"EndDate" smalldatetime NOT NULL DEFAULT GETDATE(),
"CurrencyID" nchar(3) NOT NULL
REFERENCES Currencies (CurrencyID)
ON DELETE NO ACTION,
"TimeStamp" RowVersion
)

2) Here is the table definition in the vb class
Private Sub InitClass()
Me.columnReportPeriodID = New DataColumn("ReportPeriodID",
GetType(System.String), Nothing, System.Data.MappingType.Element)
Me.Columns.Add(Me.columnReportPeriodID)
Me.columnEndDate = New DataColumn("EndDate", GetType(System.DateTime),
Nothing, System.Data.MappingType.Element)
Me.Columns.Add(Me.columnEndDate)
Me.columnCurrencyID = New DataColumn("CurrencyID", GetType(System.String),
Nothing, System.Data.MappingType.Element)
Me.Columns.Add(Me.columnCurrencyID)
Me.columnTimeStamp = New DataColumn("TimeStamp", GetType(System.Byte()),
Nothing, System.Data.MappingType.Element)
Me.Columns.Add(Me.columnTimeStamp)
Me.Constraints.Add(New UniqueConstraint("Constraint1", New DataColumn()
{Me.columnReportPeriodID}, true))
Me.columnReportPeriodID.AllowDBNull = false
Me.columnReportPeriodID.Unique = true
Me.columnEndDate.AllowDBNull = false
Me.columnCurrencyID.AllowDBNull = false
Me.columnTimeStamp.ReadOnly = true
End Sub

There is an identical one for the correct table, eg ReportPeriods, and the
extra table, eg Table1. See InitVars below

Friend Sub InitVars()
Me.tableTable1 = CType(Me.Tables("Table1"),Table1DataTable)
If (Not (Me.tableTable1) Is Nothing) Then
Me.tableTable1.InitVars
End If
Me.tableReportPeriods =
CType(Me.Tables("ReportPeriods"),ReportPeriodsDataTable)
If (Not (Me.tableReportPeriods) Is Nothing) Then
Me.tableReportPeriods.InitVars
End If
End Sub

3) And finally here is the XML-representation. As you can see there are
argain two tables
- <xs:element name="Table1">
- <xs:complexType>
- <xs:sequence>
<xs:element name="ReportPeriodID" type="xs:string" />
<xs:element name="EndDate" type="xs:dateTime" />
<xs:element name="CurrencyID" type="xs:string" />
<xs:element name="TimeStamp" msdata:ReadOnly="true" type="xs:base64Binary"
minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
- <xs:element name="ReportPeriods">
- <xs:complexType>
- <xs:sequence>
<xs:element name="ReportPeriodID" type="xs:string" />
<xs:element name="EndDate" type="xs:dateTime" />
<xs:element name="CurrencyID" type="xs:string" />
<xs:element name="TimeStamp" msdata:ReadOnly="true" type="xs:base64Binary"
minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>

The DataAdapter puts in the following
SelectCommand: GetCurrentReportPeriod
there are two parameters:
@RETURN_VALUE: no value
@ReportPeriodID: value 2003Q2 [the app was meant to test something
completely different so I might as well use a literal value]
(name): GetCurrentReportPeriod
CommandText: [GetReportPeriodByID]
CommandType: StoredProcedure
Connection: SqlConnection1 [Have checked that one to make sure it's not
pointing to the wrong database or anything]

Regards,

Kenneth


Miha Markic said:
Hi Kenneth,

Huh, that's weird.
What do your strong typed tables look like?
What does adapter put in?

--
Miha Markic - RightHand .NET consulting & software development
miha at rthand com

Kenneth Bohman said:
Thanks for your reply Miha

Here is the (very simple) SP

CREATE PROCEDURE dbo.GetReportPeriodByID
(
@ReportPeriodID nvarchar(8)
)
AS

SELECT [ReportPeriodID], [EndDate], [CurrencyID], [TimeStamp]
FROM dbo.ReportPeriods
WHERE ReportPeriodid = @ReportPeriodID
ORDER BY ReportPeriodID

RETURN
GO

Miha Markic said:
Hi Kenneth,

What is your sp like?

--
Miha Markic - RightHand .NET consulting & development
miha at rthand com

I usually hard code my datasets, but this time I thought
I'd use the drag-and-drop features of 1.0.

I created a command object pointing to a SP. The SP always
return one resultset consisting of one record. No more, no
less.

I created a dataadapter object no problem, but when
generating the dataset I'm always given two tables: Table
and Table1. There is no way I can (de)select on and not
the other. Table Mapping one does not effect the other
(thanks for that). When previewing data only the first
table get filled.

How can I get rid of the other table? Removing it from the
xsd doesn't change anything of course. There probably lies
some very clever logic behind, but it is at this point
rather difficult for me to appreciate it fully.

Regards,

Kenneth Bohman
Click to expand...
Click to expand...
Click to expand...
 
Hi Miha,

I decided to recreate the whole thing, starting a new project but with the
same settings. Maybe there was some garbage I couldn't get rid of.

This time when I configured the dataadapter there was
1) only one table (ReportPeriods) when I chose Generate Dataset
2) still two tables (ReportPeriods and Table1) when I chose Preview Dataset

The strongly typed classes still contain both ReportPeriods and Table1


Regards,

Kenneth
Miha Markic said:
Hi Kenneth,

Huh, that's weird.
What do your strong typed tables look like?
What does adapter put in?

--
Miha Markic - RightHand .NET consulting & software development
miha at rthand com

Kenneth Bohman said:
Thanks for your reply Miha

Here is the (very simple) SP

CREATE PROCEDURE dbo.GetReportPeriodByID
(
@ReportPeriodID nvarchar(8)
)
AS

SELECT [ReportPeriodID], [EndDate], [CurrencyID], [TimeStamp]
FROM dbo.ReportPeriods
WHERE ReportPeriodid = @ReportPeriodID
ORDER BY ReportPeriodID

RETURN
GO

Miha Markic said:
Hi Kenneth,

What is your sp like?

--
Miha Markic - RightHand .NET consulting & development
miha at rthand com

I usually hard code my datasets, but this time I thought
I'd use the drag-and-drop features of 1.0.

I created a command object pointing to a SP. The SP always
return one resultset consisting of one record. No more, no
less.

I created a dataadapter object no problem, but when
generating the dataset I'm always given two tables: Table
and Table1. There is no way I can (de)select on and not
the other. Table Mapping one does not effect the other
(thanks for that). When previewing data only the first
table get filled.

How can I get rid of the other table? Removing it from the
xsd doesn't change anything of course. There probably lies
some very clever logic behind, but it is at this point
rather difficult for me to appreciate it fully.

Regards,

Kenneth Bohman
Click to expand...
Click to expand...
Click to expand...
 
Back
Top