?
Solved

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

Posted on 2009-04-08
2
Medium Priority
?
351 Views
Last Modified: 2013-11-07
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?
0
Comment
Question by:bojeff30
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 12

Accepted Solution

by:
udaya kumar laligondla earned 375 total points
ID: 24099535
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
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 24099768
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

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question