Solved

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

Posted on 2009-04-08
2
304 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
Comment Utility
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
Comment Utility
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

771 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

13 Experts available now in Live!

Get 1:1 Help Now