Database

22-Sep-06 2:41

Greetings:

I'm learning how to access SQL databases under C#. SQL is a little new to me but I'm really kind of enjoying it. I have a general sort of question about strategies and accepted approaches though:

Let's say that I have to search a single table for records that match a certain criteria. What would be the best approach and why:

1. Implement an SqlCommand and use a SELECT statement to get SQL to do all the work and search for the records.
2. Load the table into either a DataReader or DataAdaptor and search the table myself(programmatically).

What are the key considerations? Speed? Memory usage? Which approach is faster? Safer? Is there a universally prefered approach or is it case by case?

I'm not seeing anything in the literature that I have that address these questions.

Thanks in advance to anyone that responds,
Mark

Not Active22-Sep-06 2:53

Not Active

22-Sep-06 2:53

Using stored procedures is the preferred and recommended way access your data. If the tables have been properly constructed and have good indexes then in general it would be more advantageous to run the query in SQL Server and return the results. I say in general because there are other factors; is it a very complex search, size the dataset involved, etc.

only two letters away from being an asset

Jethro6322-Sep-06 3:52

22-Sep-06 3:52

Hi Mark:

I haven't got to stored procedures yet. I'm not sure what they are - just a script of consecutive SQL statements?

By "properly constructing" my tables, are you refering to the various levels of normalization? I think I have a pretty good understanding of that. I'm a little sketchy on indexing though.

Thanks for your input.
Mark

Not Active22-Sep-06 4:03

Not Active

22-Sep-06 4:03

I would recommend some reading: Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design[^]

only two letters away from being an asset

Jethro6322-Sep-06 4:05

22-Sep-06 4:05

Thanks again, Mark!

Jerry Hammond22-Sep-06 14:56

Jerry Hammond

22-Sep-06 14:56

If you're using SQL2005Express and you're completely new to SQL and SQL Server then I would recommend this book[^]for your learning pleasure.

"We've all heard that a million monkeys banging on a million typewriters will eventually reproduce the entire works of Shakespeare. Now, thanks to the internet, we know this is not true." -- Professor Robert Silensky

Mike Dimmick23-Sep-06 0:29

Mike Dimmick

23-Sep-06 0:29

You almost always want SQL Server to do the work. If you index the table appropriately and supply a suitable query, SQL Server can find a given row by touching only a small number of pages making up the index (loading them from the disk), and one page of the main table, if the columns you're requesting aren't part of the index. It only returns one row across the wire, which will reduce the time spent getting the network to transfer the row.

If you make SQL Server give you all the rows, it will touch - and briefly lock - all the rows in the table, requiring all pages of the table to be read from the disk (slow), and the network time will be much higher. It'll take much longer to get your data. Then, you'll be using more memory on the client side to store the results while you search for the row you're after.

Stability. What an interesting concept. -- Chris Maunder

Jethro6324-Sep-06 15:55

24-Sep-06 15:55

Thanks for the responses everybody.

Mark

Multiple select

netDeveloper22-Sep-06 1:21

netDeveloper

22-Sep-06 1:21

I have 2 tables : Client(ClientID and other informations) and ProjectClient(ProjectID,ClientID).
I must select clients and all project id's they are working at, the project id's being in one field, separated by comma.Could you help me, pls?
Thank you.

Kschuler22-Sep-06 9:08

Kschuler

22-Sep-06 9:08

So you are basically just asking how to join the two tables together?

It would look like this:
SELECT * FROM Client, ProjectClient WHERE Client.ClientID = ProjectClient.ClientID

If you want all the info for just one client then you just do this (if for example the client you wanted had a clientID of 5):

SELECT * FROM Client, ProjectClient WHERE Client.ClientID = ProjectClient.ClientID AND Client.ClientID = 5

Eric Dahlvang22-Sep-06 9:37

Eric Dahlvang

22-Sep-06 9:37

I don't know why you would want something like this, but it sounds like you need a function:

CREATE FUNCTION GetProjectIDs (@nClientID int)  
RETURNS  varchar(1000) AS  
BEGIN 

DECLARE @nProjectID int
DECLARE @cProjectIDs varchar(50)

SELECT @cProjectIDs = ''

DECLARE  ProjectIDs CURSOR FOR 
SELECT ProjectID 
FROM ProjectClient
WHERE ClientID = @nClientID
ORDER BY ProjectID

OPEN ProjectIDs
FETCH NEXT FROM ProjectIDs INTO @nProjectID 
WHILE @@FETCH_STATUS = 0
BEGIN
	select @cProjectIDs = @cProjectIDs + case when len(@cProjectIDs) = 0 then '' else ',' end + cast( @nProjectID as varchar)
	FETCH NEXT FROM ProjectIDs INTO @nProjectID 
END

CLOSE ProjectIDs
DEALLOCATE ProjectIDs
RETURN(@cProjectIDs)
END

Then call it like this:

select *, dbo.GetProjectIDs(client.clientid) as ProjectIDs from client

--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters

Mike Dimmick23-Sep-06 0:55

Mike Dimmick

23-Sep-06 0:55

Use SQL Server to get your data. Do the formatting in the client side program. Keep both sides doing what they're best at: SQL Server is not really that good at string manipulation.

For this task I would use a DataSet and use the version of DataAdapter.Fill which fills a DataSet. I'd write a query that returned two resultsets:

CREATE PROC uspGetClientsProjects
AS
SELECT ClientID, Field1, Field2
FROM Client

SELECT ClientID, ProjectID
FROM ProjectClient

If you Fill a DataSet from that, you get two DataTables in your DataSet. You can then add a DataRelation to the DataSet's Relations property to link Client and ProjectClient together. Then, to get your output, iterate over the Client table, and use the GetChildRows method of the DataRow object to get the matching ProjectClient rows.

Stability. What an interesting concept. -- Chris Maunder