SqlDataReader Stored Procedure

[Varangian]

I have an SQL Server 2005 Express Stored Procedure .. which first I
select if that record exists, if it exists I update that record .. if
not I insert. It then selects the record with the new data updated or
inserted...

however the .NET SqlDataReader always takes the first query results
(which the query that first ran to check if that record exists) .. How
can I take the second query results?. I can use the Try Catch but that
will reduce the system performance..

thanks all!

 

[Nicholas Paldino [.NET/C# MVP]]

Varangian,

This archive thread on google will answer your question (although it
does degenerate towards the end):

http://groups.google.com/group/micr...5f8ec?lnk=st&q=&rnum=3&hl=en#8f2dbb3be315f8ec

Basically, you would create a temp table and insert into that table,
then, select from that table in the end.

Hope this helps.

 

[Varangian]

hmmm...yes ok thanks for the reply.. however if I need to check in a
table, I need to insert all the records from the true one to the Temp
one... which means overhead over the SQL Server no?

Varangian,

This archive thread on google will answer your question (although it
does degenerate towards the end):

http://groups.google.com/group/micr...5f8ec?lnk=st&q=&rnum=3&hl=en#8f2dbb3be315f8ec

Basically, you would create a temp table and insert into that table,
then, select from that table in the end.

Hope this helps.

--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

I have an SQL Server 2005 Express Stored Procedure .. which first I
select if that record exists, if it exists I update that record .. if
not I insert. It then selects the record with the new data updated or
inserted...

however the .NET SqlDataReader always takes the first query results
(which the query that first ran to check if that record exists) .. How
can I take the second query results?. I can use the Try Catch but that
will reduce the system performance..

thanks all!

 

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

If the record is inserted all you may need is the generated ID, all the
other info being inserted you already have it. In this case you can use a
parameter.

If the record exists then you already have all the info you need, why are
you getting back the same info?
unless you do some transformation of the data you already have the info
being inserted .

Additionally IIRC the DataReader can access all the result sets, it does has
a NextResult that advance to the next recordset

 

[Marc Gravell]

which first I select if that record exists,

OK - more SQL than C#, but:

Can I verify: so you do a select and then test @@ROWCOUNT or something?
That's a bit ropy...

You should be able to do all of this without the first select, either by
testing IF EXISTS (SELECT ...), or by treating e.g. the identity / PK
parameter as the toggle - meaning if it is NULL do an INSERT, else do an
UPDATE. You could then do a single SELECT at the bottom, which SqlDataReader
will handle happily.

Finally - why SELECT this data at all? Since you just told it the values,
your code already knows (unless this is an update of a limited set of data,
followed by a SELECT of everything including e.g. computed values - but I
don't like that design anyway ;-p). If the aim is to return e.g. the new
identity value, then a better approach is to declare the param as OUT in the
SQL (InputOutput in the C#), and then set the value to SCOPE_IDENTITY()
after the INSERT. Likewise with any Timestamp columnn you may have.

Does that help at all?

Marc

 

[Marc Gravell]

BTW, if UPDATEs vastly outnumber INSERTs, and it is suitably indexed on the
identity, then you could try (as below) being optimistic on the data already
being there (for updating) - but I must stress that I prefer toggling this
based on the @ID being e.g. 0/NULL, as the caller should know (in advance)
whether they are attempting an insert or an update, and may not expect an
"UPDATE" on an incorrect ID to perform an insert and change the current id.
I also quite like using the return value to indicate the rows affected -
which should always be 1 in this case (with NOCOUNT ON).

Marc

-- blah params... @ID int OUTPUT = NULL... blah

UPDATE ...
WHERE [ID] = @ID

IF @@ROWCOUNT = 0
BEGIN
INSERT ...

SET @ID = SCOPE_IDENTITY()
END

-- the bit I don't like anyway
SELECT ...
WHERE [ID] = @ID

 

[Nicholas Paldino [.NET/C# MVP]]

Varangian,

You might be able to get away with using "select into", but either way,
you will have to store it in a temp table.

The thing is, that's to be expected. Queries either have to be
delivered back to the user, or they have to be stored in a temp cursor/table
on the server. There is no other place for them to be put.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

hmmm...yes ok thanks for the reply.. however if I need to check in a
table, I need to insert all the records from the true one to the Temp
one... which means overhead over the SQL Server no?

Varangian,

This archive thread on google will answer your question (although it
does degenerate towards the end):

http://groups.google.com/group/micr...5f8ec?lnk=st&q=&rnum=3&hl=en#8f2dbb3be315f8ec

Basically, you would create a temp table and insert into that table,
then, select from that table in the end.

Hope this helps.

--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

I have an SQL Server 2005 Express Stored Procedure .. which first I
select if that record exists, if it exists I update that record .. if
not I insert. It then selects the record with the new data updated or
inserted...

however the .NET SqlDataReader always takes the first query results
(which the query that first ran to check if that record exists) .. How
can I take the second query results?. I can use the Try Catch but that
will reduce the system performance..

thanks all!