using dataset to retrieve data from SQL Database -versus- Retrieving data directly from SQL database.

General question regarding using dataset to retrieve data  from SQL Database  -versus-   Retrieving data directly  from SQL database.
I have a loop function that will traverse multiple checkboxlist controls.  For every checkedbox that is equal to true, data is retrieved from the database.

My question,
 Is it better to use a data adapter to fill a dataset with the entire table and read the data from the Dataset?
 or
Use sql command to retrieve the data directly every time the loop finds checkbox = true?

What are the pros and cons?
bojeff30Asked:
Who is Participating?
 
udaya kumar laligondlaTechnical LeadCommented:
you should not have too many server calls from an applicaiton it will affect the performance of both application and server. it is better to create a query with all the values that you want to match as
select a,b,c,d,e from tablename where f in (VALUES)
use the loop to create a concatinated list of values. in the loop use string builder to create comma separated values of the checkbox.  you replace VALUES  with the string.
this will reduce the no of records selected and will select only the required rows to select. it will be only one call to server and use dataReader if you only want to read. this will give you best performance
0
 
Chris LuttrellSenior Database ArchitectCommented:
I see that you are a beginer in this subject so you may not know that tou will not be able to just put the string of comma separated values as a single string value in place of VALUES in that example.  We use this type logic in our applications and then use a routine like below to parse out the values from the string into a table to join with or use for the VALUES part like this:
select a,b,c,d,e from tablename where f in (select id from dbo.fnParseList(@Values, ','))
This function works for integers, you can change it for other data types as needed.
HTH


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create FUNCTION [fnParseList]
(
	@List varchar(500),
	@Delim varchar(1)
)
RETURNS 
@ParsedList table
(
	ID int
)
AS
BEGIN
	DECLARE @ListValue varchar(10), @Pos int
 
	SET @List = LTRIM(RTRIM(@List))+ @Delim
	SET @Pos = CHARINDEX(@Delim, @List, 1)
 
	IF REPLACE(@List, @Delim, '') <> ''
	BEGIN
		WHILE @Pos > 0
		BEGIN
			SET @ListValue = LTRIM(RTRIM(LEFT(@List, @Pos - 1)))
			IF @ListValue <> ''
			BEGIN
				INSERT INTO @ParsedList (ID) 
				VALUES (CAST(@ListValue AS int)) --Use Appropriate conversion
			END
			SET @List = RIGHT(@List, LEN(@List) - @Pos)
			SET @Pos = CHARINDEX(@Delim, @List, 1)
		END
	END	
	RETURN
END

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.