DeriveParameters

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I understand when I add a paramete like (name, value)

p = new SqlParameter("@someINT", 111);

that behind the sceens, a trip to the db is made to derive the parameter.

When creating a parameter what values must be specified to prevent the trip
to the server? For example below the size property is not specified. Does
that mean a trip will occur?


p = new SqlParameter("@RETURN_VALUE", SqlDbType.Int);
p.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(p);

thanks,
 
What makes you think that ADO.NET makes this round trip? None of my books
say so... While ADO classic once made round trips for this sort of thing
(under special circumstances), it does not happen in ADO.NET. The only
round-tripping done by ADO.NET is when you use the CommandBuilder or
DeriveParameters (specifically). You aren't doing so in this case...

hth

--
____________________________________
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)
 
Ah, those are there so your code can define the properties of the input
Parameter object. These don't actually have to be defined. Just use the
AddValue method.

--
____________________________________
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)
Between now and Nov. 6th 2006 you can sign up for a substantial discount.
Look for the "Early Bird" discount checkbox on the registration form...
-----------------------------------------------------------------------------------------------------------------------
 
So I can get an exception if I put in the wrong datatype in the value at
runtime?
Seems odd because they are sql types and not framework types. So to
evaluate on the framework side at runtime it would have to know how to
convert and I believe the framework does not know how to do that?

What's the purpose of specifing the direction parameter (e.g. output) and
length then?

SQL server sticks in the parameter @RETURN_VALUE automagically.
So why do I have to specify output parameters?

I am confused.
 
Nope. That's ADO classic that does that. ADO.NET does not throw an exception
when you assign a value to the Value property. The ReturnValue and Output
Direction settings are required to fetch the returned values. The Input
Direction (the default) is required for all SP Parameters that don't have a
defined default in the SP declaration (on the server). These issues and
behaviors are discussed in (great) detail in chapters 2, 5 and 10 of my new
book.
--
____________________________________
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)
 
Say I have a stored proc that only returns a value and the stored
procedure says the parameter is output.

If either I don't declare the parameter or I don't give it a value,
I will get an exception.
If I declare the parameter and give it an Output direction, I don't
need to give it a value.

I will get two parameters back the one I declared and @RETURN_VALUE

Just seems funny that you have SET properties for type and length if
they are just ignored by the framework.
 
--
____________________________________
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)
-----------------------------------------------------------------------------------------------------------------------
 
See inline>>>

--
____________________________________
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)
-----------------------------------------------------------------------------------------------------------------------

Say I have a stored proc that only returns a value and the stored
procedure says the parameter is output.

If either I don't declare the parameter or I don't give it a value,
I will get an exception.
Click to expand...
If I declare the parameter and give it an Output direction, I don't
need to give it a value.
Click to expand...
 
Hi Chuck

Just want to check if there is anything I can help with.
Please feel free to contact me.

Sincerely,
WenYuan
 
Back
Top