Solved

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

Posted on 2009-04-08
2
315 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
2 Comments
 
LVL 12

Accepted Solution

by:
udayakumarlm earned 125 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 26

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
Need to grow your business through quality cloud solutions? With everything required to build a cloud platform and solution, you may feel like the distance between you and the cloud is quite long. Help is here. Spend some time learning about the Con…

943 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

Need Help in Real-Time?

Connect with top rated Experts

1 Experts available now in Live!

Get 1:1 Help Now