stored procedure return 1 value 1

[tester321]

Hi i'm tring to return a "TOP 1" value from column "MID" from a table using a storedproc. This is what i have, this is also my first attempt as this, any suggstions on making this work, thanks:

....
SqlCommand Command = new SqlCommand("sp_loginInfo", connection);
            
Command.CommandType = CommandType.StoredProcedure;

Command.Parameters.Add("@user", SqlDbType.NVarChar).Value = strEmail;     
 
Command.Parameters.Add("@pass", SqlDbType.NVarChar).Value = strPassword;  

using (SqlDataReader rdr = Command.ExecuteReader())
                {
                 if (rdr.HasRows)
                        
                 ??? what do i need to do to finish it off and clean up the objects?  I working with login info, so if there is a method that is "more secure" please let me know. Thanks
                 }

 

[onpnt]

First don't use readers. Get out of the recordset days

If you are only returning one value from the procedure then use ExecuteScalar

example:

 SqlCommand cmd = new SqlCommand("Select TOP 1 MID from SomeTable", connectoin);
Strng val = cmd.ExecuteScalar();

What you're doing is really a 2-3 line task. Try not to make it harder than it is


[sub]____________ signature below ______________
I am Tedward Keyboardhands!!!
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]

 

[AlexCuse]

If you only want to return 1 row, and one column, have a look at the command object's ExecuteScalar() method. You can use this method to put your result directly into a variable.

Then throw an if in there to tell your code what to do based on the value returned, get rid of all your objects and you should be good to go.

Hope this helps,

Alex



[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[AlexCuse]

Hey Jeff - great minds, and all that ;-)

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[onpnt]

I knew I was onto something right


[sub]____________ signature below ______________
I am Tedward Keyboardhands!!!
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]

 

[tester321]

i wanted to use the reader cause i did not want the read value in memory, i was reading that reader does not keep it in memory, does ExecuteScalar() ?

 

[onpnt]

unless you are running a machine with 64MB of RAM the cost of memory is far outweighing the cost of performance to your application and future applications using the correct method for the task at hand


[sub]____________ signature below ______________
I am Tedward Keyboardhands!!!
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]

 

[tester321]

i was thinking security of keeping the password in memory

 

[AlexCuse]

What exactly is in your MID column that you need to protect? If you want to proceed based simply on whether rows are returned or not, why not just make your proc return a 1 or a 0, instead of worrying about bringing confidential information back to the client?

Regardless of what you may have read, I don't think that using a reader here is a good idea at all.

Hope this helps,

Alex

ps - you have much bigger worries here than whether your password remains in memory or not. And if you use the reader, your password will still remain in memory in some form (not to mention the place the user enters it). The MID column does not contain the password does it?

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[tester321]

no but it is a db identifer to sensitive info. I just trying to cover my tracks as much as possible as i code

 

[AlexCuse]

can anyone get into the database or application using this db identifier? If not, then you have nothing to worry about.

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[tester321]

does need to be cleaned up/disposed of in the end?

 

[onpnt]

So does the reader

If you manage your code you do not need to worry about memory with values like that. The reader will take up far more resources than the way you've been shown. Of course with a small application you may not notice the difference. That is not the point and should never be an excuse though over using the proper methods for the task


[sub]____________ signature below ______________
I am Tedward Keyboardhands!!!
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]

 

[tester321]

thanks!

 

[onpnt]

[sub]____________ signature below ______________
I am Tedward Keyboardhands!!!
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]

 

[AlexCuse]

>>> Of course with a small application you may not notice the difference. That is not the point and should never be an excuse though over using the proper methods for the task


Well put Jeff

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[VolkerStamme]

>> First don't use readers. Get out of the recordset days

Why? Assuming you have more than one field and you're not dealing with DataSet's either, what's wrong with them?

V

 

[AlexCuse]

VolkerStamme -

From msdn2:

While the SqlDataReader is in use, the associated SqlConnection is busy serving the SqlDataReader, and no other operations can be performed on the SqlConnection other than closing it. This is the case until the Close method of the SqlDataReader is called. For example, you cannot retrieve output parameters until after you call Close.

This makes me think that the connection to the database may be open longer than it needs to be, so I prefer to bring any data back into a data table/data set etc, and do my 'reading' on the client. This may be because I am mainly a database guy and I don't want anything connecting to my db's longer than it has to though.

That being said, I am sure the Reader has its' place, just not here

Alex

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[chrissie1]

Yep the reader has it place. Personally I don't use the fill method of the dataset/datatable and use datareaders instead. Datareaders are a lot faster then datatables and datatables/sets have alot of overhead.

So I use nhibernate instead.

Christiaan Baes
Belgium

"In a system where you can define a factor as part of a third factor, you need another layer to check the main layer in case the second layer is not the base unit." - jrbarnett

 

[VolkerStamme]

That's what I do, read into my own structures and close the reader and it has proven to be quite fast. Also, I would assume that while the DataSet is being read you'd have the same issue with the underlying connection.
Was merely curious if there's anything that generally speaks against the Reader, e.g. security, known bugs etc.

V