SqlDataAdatper Bug?

I am new to .Net but have been unable to find anything about this in
the newsgroups.

Using C# and the following SQL stored procedure in my SqlDataAdapter:

CREATE PROCEDURE HouseholdNextPrevious
@PassedName varchar(150),
@Direction varchar(4)
AS
Set NoCount on
if @Direction = 'down' /* next household */
begin
SELECT TOP 1 BuildingId, LabelName, Street1, Street2, City,
StateProvince, Zipcode, Country, Phone, PhoneNote,
Household.CellPhone, Fax, NoNewsletter, NoAppeal,
NoCalendarAnnouncement, NoFlyer, Household.InactiveId,
FirstConnectionId, Salutation, ICodeId, IncomeId, PrimaryContactId,
Household.Note, Household.LastEditDate,
SortName = isnull(LastName + ' ' + FirstName + ' ' +
isnull(MiddleInitial,'') +
Convert(varchar(40),Household.BuildingId),'aaaaa' +
Convert(varchar(40),Household.BuildingId))
FROM Household
LEFT OUTER JOIN Individual
on IndividualId = PrimaryContactId
where (isnull(LastName + ' ' + FirstName + ' ' +
isnull(MiddleInitial,'') +
Convert(varchar(40),Household.BuildingId),'aaaaa' +
Convert(varchar(40),Household.BuildingId))) > @PassedName
order by SortName asc
end
else /* previous household */
begin
SELECT TOP 1 BuildingId, LabelName, Street1, Street2, City,
StateProvince, Zipcode, Country, Phone, PhoneNote,
Household.CellPhone, Fax, NoNewsletter, NoAppeal,
NoCalendarAnnouncement, NoFlyer, Household.InactiveId,
FirstConnectionId, Salutation, ICodeId, IncomeId, PrimaryContactId,
Household.Note, Household.LastEditDate,
SortName = isnull(LastName + ' ' + FirstName + ' ' +
isnull(MiddleInitial,'') +
Convert(varchar(40),Household.BuildingId),'aaaaa' +
Convert(varchar(40),Household.BuildingId))
FROM Household
LEFT OUTER JOIN Individual
on IndividualId = PrimaryContactId
where (isnull(LastName + ' ' + FirstName + ' ' +
isnull(MiddleInitial,'') +
Convert(varchar(40),Household.BuildingId),'aaaaa' +
Convert(varchar(40),Household.BuildingId))) < @PassedName
order by SortName desc
end

When I generate the dataset based on the above stored procedure, it
constructs two tables (HouseholdNextPrevious and Table1) in the
dataset which have identical columns.

If I use an OledbDataAdapter, it only contructs one table in the
dataset (HouseholdNextPrevious). Any insight as to why this is
happening?

Thanks,

David
Tue Sep 16 14:01:47 CDT 2003


"Brian Korenaga" <B_Korenaga@NOSPAMhotmail.com> wrote in message
news:mmjemvsjamva7985bhfmgt67leusncar5b@4ax.com...
> I am new to .Net but have been unable to find anything about this in
> the newsgroups.
>
> Using C# and the following SQL stored procedure in my SqlDataAdapter:
>
> CREATE PROCEDURE HouseholdNextPrevious
> @PassedName varchar(150),
> @Direction varchar(4)
> AS
> Set NoCount on
> if @Direction = 'down' /* next household */
> begin
> SELECT TOP 1 BuildingId, LabelName, Street1, Street2, City,
> StateProvince, Zipcode, Country, Phone, PhoneNote,
> Household.CellPhone, Fax, NoNewsletter, NoAppeal,
> NoCalendarAnnouncement, NoFlyer, Household.InactiveId,
> FirstConnectionId, Salutation, ICodeId, IncomeId, PrimaryContactId,
> Household.Note, Household.LastEditDate,
> SortName = isnull(LastName + ' ' + FirstName + ' ' +
> isnull(MiddleInitial,'') +
> Convert(varchar(40),Household.BuildingId),'aaaaa' +
> Convert(varchar(40),Household.BuildingId))
> FROM Household
> LEFT OUTER JOIN Individual
> on IndividualId = PrimaryContactId
> where (isnull(LastName + ' ' + FirstName + ' ' +
> isnull(MiddleInitial,'') +
> Convert(varchar(40),Household.BuildingId),'aaaaa' +
> Convert(varchar(40),Household.BuildingId))) > @PassedName
> order by SortName asc
> end
> else /* previous household */
> begin
> SELECT TOP 1 BuildingId, LabelName, Street1, Street2, City,
> StateProvince, Zipcode, Country, Phone, PhoneNote,
> Household.CellPhone, Fax, NoNewsletter, NoAppeal,
> NoCalendarAnnouncement, NoFlyer, Household.InactiveId,
> FirstConnectionId, Salutation, ICodeId, IncomeId, PrimaryContactId,
> Household.Note, Household.LastEditDate,
> SortName = isnull(LastName + ' ' + FirstName + ' ' +
> isnull(MiddleInitial,'') +
> Convert(varchar(40),Household.BuildingId),'aaaaa' +
> Convert(varchar(40),Household.BuildingId))
> FROM Household
> LEFT OUTER JOIN Individual
> on IndividualId = PrimaryContactId
> where (isnull(LastName + ' ' + FirstName + ' ' +
> isnull(MiddleInitial,'') +
> Convert(varchar(40),Household.BuildingId),'aaaaa' +
> Convert(varchar(40),Household.BuildingId))) < @PassedName
> order by SortName desc
> end
>
> When I generate the dataset based on the above stored procedure, it
> constructs two tables (HouseholdNextPrevious and Table1) in the
> dataset which have identical columns.
>
> If I use an OledbDataAdapter, it only contructs one table in the
> dataset (HouseholdNextPrevious). Any insight as to why this is
> happening?
>
> Thanks,

It's because you have 2 tables coming out of your procedure.

Here's what you sould do. Create a view.

create view vHousehold as
select BuildingId, LabelName, Street1, Street2, City,
StateProvince, Zipcode, Country, Phone, PhoneNote,
Household.CellPhone, Fax, NoNewsletter, NoAppeal,
NoCalendarAnnouncement, NoFlyer, Household.InactiveId,
FirstConnectionId, Salutation, ICodeId, IncomeId, PrimaryContactId,
Household.Note, Household.LastEditDate,
SortName = isnull(LastName + ' ' + FirstName + ' ' +
isnull(MiddleInitial,'') +
Convert(varchar(40),Household.BuildingId),'aaaaa' +
Convert(varchar(40),Household.BuildingId))
FROM Household
LEFT OUTER JOIN Individual
on IndividualId = PrimaryContactId


Then use the view to generate your DataSet. Also your procedure collapses
to:

CREATE PROCEDURE HouseholdNextPrevious
@PassedName varchar(150),
@Direction varchar(4)
AS
Set NoCount on
if @Direction = 'down' /* next household */
begin
SELECT TOP 1 *
FROM vHousehold
LEFT OUTER JOIN Individual
on IndividualId = PrimaryContactId
where SortName > @PassedName
order by SortName asc
end
else /* previous household */
begin
SELECT TOP 1 *
FROM vHousehold
LEFT OUTER JOIN Individual
on IndividualId = PrimaryContactId
where SortName < @PassedName
order by SortName desc
end

Which is still blindingly slow, but that's another story.

David

Brian
Tue Sep 16 15:51:45 CDT 2003

David,

Thanks for your reply. However, the question as to why this occurs
with the SqlDataAdapter but not with the OledbDataAdapter is still up
for grabs.

Brian

On Tue, 16 Sep 2003 14:01:47 -0500, "David Browne" <davidbaxterbrowne
no potted meat@hotmail.com> wrote:

>
>"Brian Korenaga" <B_Korenaga@NOSPAMhotmail.com> wrote in message
>news:mmjemvsjamva7985bhfmgt67leusncar5b@4ax.com...
>> I am new to .Net but have been unable to find anything about this in
>> the newsgroups.
>>
>> Using C# and the following SQL stored procedure in my SqlDataAdapter:
>>
>> CREATE PROCEDURE HouseholdNextPrevious
>> @PassedName varchar(150),
>> @Direction varchar(4)
>> AS
>> Set NoCount on
>> if @Direction = 'down' /* next household */
>> begin
>> SELECT TOP 1 BuildingId, LabelName, Street1, Street2, City,
>> StateProvince, Zipcode, Country, Phone, PhoneNote,
>> Household.CellPhone, Fax, NoNewsletter, NoAppeal,
>> NoCalendarAnnouncement, NoFlyer, Household.InactiveId,
>> FirstConnectionId, Salutation, ICodeId, IncomeId, PrimaryContactId,
>> Household.Note, Household.LastEditDate,
>> SortName = isnull(LastName + ' ' + FirstName + ' ' +
>> isnull(MiddleInitial,'') +
>> Convert(varchar(40),Household.BuildingId),'aaaaa' +
>> Convert(varchar(40),Household.BuildingId))
>> FROM Household
>> LEFT OUTER JOIN Individual
>> on IndividualId = PrimaryContactId
>> where (isnull(LastName + ' ' + FirstName + ' ' +
>> isnull(MiddleInitial,'') +
>> Convert(varchar(40),Household.BuildingId),'aaaaa' +
>> Convert(varchar(40),Household.BuildingId))) > @PassedName
>> order by SortName asc
>> end
>> else /* previous household */
>> begin
>> SELECT TOP 1 BuildingId, LabelName, Street1, Street2, City,
>> StateProvince, Zipcode, Country, Phone, PhoneNote,
>> Household.CellPhone, Fax, NoNewsletter, NoAppeal,
>> NoCalendarAnnouncement, NoFlyer, Household.InactiveId,
>> FirstConnectionId, Salutation, ICodeId, IncomeId, PrimaryContactId,
>> Household.Note, Household.LastEditDate,
>> SortName = isnull(LastName + ' ' + FirstName + ' ' +
>> isnull(MiddleInitial,'') +
>> Convert(varchar(40),Household.BuildingId),'aaaaa' +
>> Convert(varchar(40),Household.BuildingId))
>> FROM Household
>> LEFT OUTER JOIN Individual
>> on IndividualId = PrimaryContactId
>> where (isnull(LastName + ' ' + FirstName + ' ' +
>> isnull(MiddleInitial,'') +
>> Convert(varchar(40),Household.BuildingId),'aaaaa' +
>> Convert(varchar(40),Household.BuildingId))) < @PassedName
>> order by SortName desc
>> end
>>
>> When I generate the dataset based on the above stored procedure, it
>> constructs two tables (HouseholdNextPrevious and Table1) in the
>> dataset which have identical columns.
>>
>> If I use an OledbDataAdapter, it only contructs one table in the
>> dataset (HouseholdNextPrevious). Any insight as to why this is
>> happening?
>>
>> Thanks,
>
>It's because you have 2 tables coming out of your procedure.
>
>Here's what you sould do. Create a view.
>
>create view vHousehold as
>select BuildingId, LabelName, Street1, Street2, City,
>StateProvince, Zipcode, Country, Phone, PhoneNote,
>Household.CellPhone, Fax, NoNewsletter, NoAppeal,
> NoCalendarAnnouncement, NoFlyer, Household.InactiveId,
>FirstConnectionId, Salutation, ICodeId, IncomeId, PrimaryContactId,
>Household.Note, Household.LastEditDate,
> SortName = isnull(LastName + ' ' + FirstName + ' ' +
>isnull(MiddleInitial,'') +
>Convert(varchar(40),Household.BuildingId),'aaaaa' +
>Convert(varchar(40),Household.BuildingId))
> FROM Household
> LEFT OUTER JOIN Individual
> on IndividualId = PrimaryContactId
>
>
>Then use the view to generate your DataSet. Also your procedure collapses
>to:
>
>CREATE PROCEDURE HouseholdNextPrevious
> @PassedName varchar(150),
> @Direction varchar(4)
> AS
> Set NoCount on
> if @Direction = 'down' /* next household */
> begin
> SELECT TOP 1 *
> FROM vHousehold
> LEFT OUTER JOIN Individual
> on IndividualId = PrimaryContactId
> where SortName > @PassedName
> order by SortName asc
> end
> else /* previous household */
> begin
> SELECT TOP 1 *
> FROM vHousehold
> LEFT OUTER JOIN Individual
> on IndividualId = PrimaryContactId
> where SortName < @PassedName
> order by SortName desc
> end
>
>Which is still blindingly slow, but that's another story.
>
>David
>