retrieving just one row based on the primary key

[Fred Exley]

Is there a preferred way to obtain one row of data based on the PK? I can
retrieve the record onto my C# app using a dataset or datareader, but then I
have to loop thru this result set even though I know there will always be
either zero or one row retrieved. Isn't there a more elegant way to address
this common need? thanks

 

[W.G. Ryan [MVP]]

So is the problem that the while(reader.Read()) lacks elegance? You can
check the HasRows property of the reader and then just use dataReader.Read()
if you are positive there is only 1 row (if there are not 0 rows) but I
would strongly recommend agasint this. If the app logic changes somewhere,
by hook or by crook, then there'll be a whole buffer waiting for clear
itself and you won't know until users report errors. Similarly, you can just
reference DataTable.Rows[0] to get the first row, instead of looping
through it but I'm not sure that gets you a whole lot b/c in order to be
'sure' that the row is the one you want (b/c the logic may have changed
somewhere), you'llneed to do a check on the rows.Count property and ensure
it's either 0 or 1 so code wise, you'll have the same amount of code either
way.

However, if it's just one value you are looking for, you can use the
ExecuteScalar command to just return one value - I'm not sure if that'll
help or not.

Just so I'm clear though, you aren't asking how to find one record in many
based on a PK right? You are asking, assuming there is only 0 or 1 rows,
how is the simplest way to iterate to that row?

 

[Fred Exley]

So is the problem that the while(reader.Read()) lacks elegance? You can
check the HasRows property of the reader and then just use
dataReader.Read() if you are positive there is only 1 row (if there are
not 0 rows) but I would strongly recommend agasint this. If the app logic
changes somewhere, by hook or by crook, then there'll be a whole buffer
waiting for clear itself and you won't know until users report errors.
Similarly, you can just reference DataTable.Rows[0] to get the first row,
instead of looping through it but I'm not sure that gets you a whole lot
b/c in order to be 'sure' that the row is the one you want (b/c the logic
may have changed somewhere), you'llneed to do a check on the rows.Count
property and ensure it's either 0 or 1 so code wise, you'll have the same
amount of code either way.

However, if it's just one value you are looking for, you can use the
ExecuteScalar command to just return one value - I'm not sure if that'll
help or not.

Just so I'm clear though, you aren't asking how to find one record in many
based on a PK right? You are asking, assuming there is only 0 or 1 rows,
how is the simplest way to iterate to that row?

Yes, you're clear -just wondering if the 'iterating' part could be
streamlined -something like the DetailsView control in asp 2.0, only
programatically do-able in C#.

The other idea I had was to call a SQLServer proc, and return the fields as
output parameters, but maybe that's an even less elegant way, or worse?



thanks

 

[Earl]

Assuming I have no other need for a dataset, and I need more than one
column, I generally use an untyped datatable to hold the contents of one
row. Or I use a datareader to populate an arraylist.

 

[William \(Bill\) Vaughn]

The Command object has a number of "execute" methods. While ExecuteScalar
can fetch a single value, ExecuteReader can be programmed to only process
the first row if you set the CommandBehavior.SingleRow option. However, if
more than one row is returned by the SELECT, these are discarded by ADO.NET.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)

 

[Fred Exley]

Thanks much, everybody. Apparently the 'preferred way' depends on what one
is trying to do, but there is no 'get the one and only record for these PK
values' method in existence (yet). -Fred

 

[William \(Bill\) Vaughn]

Sure there is.

SELECT stuff FROM MyTable WHERE myPKColumn = @PKColumnValue

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

 

[Fred Exley]

Sure there is.

SELECT stuff FROM MyTable WHERE myPKColumn = @PKColumnValue

Thanks much, that's how I wrote the called sqlserver proc, along with your
SingleResult suggestion on the receiving end:


SqlDataReader myDataReader;

myDataReader = cmdGetPicsRec.ExecuteReader(CommandBehavior.SingleResult);

while (myDataReader.Read())

{

_picDesc = myDataReader["picDesc"].ToString();

_catA = myDataReader["catA"].ToString();

}

myDataReader.Close();

cq.Close();





It's working fine now! -Fred

 

[Otis Mukinfus]

Sure there is.

SELECT stuff FROM MyTable WHERE myPKColumn = @PKColumnValue

I like your answer best Bill.

It seems to me these days everyone wants to use DataSets/DataTables for
everything.

1. Execute the query.
2. Read the returned data with the reader using single row.
3. Use the data.

Good luck with your project,

Otis Mukinfus
http://www.arltex.com
http://www.tomchilders.com