retrieving just one row based on the primary key

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

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" <fexley221@cox.com> wrote in message
news:12p3p9sd323k6e2@corp.supernews.com...
> 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]" <WilliamRyan@nospam.gmail.com> wrote in message
news:ORHSPdWKHHA.4712@TK2MSFTNGP04.phx.gbl...
> 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

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.

"Fred Exley" <fexley221@cox.com> wrote in message
news:12p3p9sd323k6e2@corp.supernews.com...
> 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
>

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" <fexley221@cox.com> wrote in message
news:12p3p9sd323k6e2@corp.supernews.com...
> 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
>

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" <billvaRemoveThis@nwlink.com> wrote in message
news:e7hSGcjKHHA.5000@TK2MSFTNGP03.phx.gbl...
> 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" <fexley221@cox.com> wrote in message
> news:12p3p9sd323k6e2@corp.supernews.com...
>> 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
>>
>
>

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" <fexley221@cox.com> wrote in message
news:12p6tbulkv6lv3d@corp.supernews.com...
> 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" <billvaRemoveThis@nwlink.com> wrote in message
> news:e7hSGcjKHHA.5000@TK2MSFTNGP03.phx.gbl...
>> 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" <fexley221@cox.com> wrote in message
>> news:12p3p9sd323k6e2@corp.supernews.com...
>>> 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
>>>
>>
>>
>
>

"William (Bill) Vaughn" <billvaRemoveThis@nwlink.com> wrote in message
news:Oo$q$HrKHHA.4928@TK2MSFTNGP06.phx.gbl...
> 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

On Thu, 28 Dec 2006 10:57:17 -0800, "William \(Bill\) Vaughn"
<billvaRemoveThis@nwlink.com> wrote:

>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