SqlDataAdatper Bug?

B

Brian Korenaga

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,
 
D

David Browne

Brian Korenaga said:
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,
Click to expand...

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
 
B

Brian Korenaga

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
 
Top