[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2009-04-08
2
Medium Priority
?
358 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

650 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