SqlDataAdatper Bug?

  • Thread starter Thread starter Brian Korenaga
  • Start date Start date
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,
 
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
 
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
 
Back
Top