Why aren't data types in SqlParameters validated?

[Jerad Rose]

I believe this question is simple enough -- why aren't data types in
SqlParameters validated in ADO.NET?

For example, why is an exception *not* thrown on this line:

cmd.Parameters.Add(New SqlParameter("@Id", Data.SqlDbType.Int)).Value = "not
an int"

Ideally, this wouldn't even compile, let alone execute. But it's even worse
that this line will execute, only later to bomb out when the stored proc is
executed, giving me this error:

Failed to convert parameter value from a String to a Int32.

Which can lead you on a wild goose chase tracking down the one out of
possibly several parameters that had a type issue.

My next question is, if the data type parameter in this constructor is not
for the sake of type safety, what is its purpose?

Lastly, is there anything that can be done (within reason) to ensure type
safety when setting parameter values, so that an exeption is thrown on the
line where the parameter value was attempted to be set? If not, what is the
best practice for accomplishing type safety when setting up store procedures
in ADO.NET?

Just FYI, this is in reference to ADO.NET 2.0.

Thanks in advance for your feedback.

Jerad

 

[David Browne]

I believe this question is simple enough -- why aren't data types in
SqlParameters validated in ADO.NET?

For example, why is an exception *not* thrown on this line:

cmd.Parameters.Add(New SqlParameter("@Id", Data.SqlDbType.Int)).Value =
"not an int"

Ideally, this wouldn't even compile, let alone execute. But it's even
worse that this line will execute, only later to bomb out when the stored
proc is executed, giving me this error:

Failed to convert parameter value from a String to a Int32.

Which can lead you on a wild goose chase tracking down the one out of
possibly several parameters that had a type issue.

My next question is, if the data type parameter in this constructor is not
for the sake of type safety, what is its purpose?

Lastly, is there anything that can be done (within reason) to ensure type
safety when setting parameter values, so that an exeption is thrown on the
line where the parameter value was attempted to be set? If not, what is
the best practice for accomplishing type safety when setting up store
procedures in ADO.NET?

Yes. Write strongly-typed wrappers for your procedures.

void MyProc(id int, ...)
{
...
cmd.Parameters.Add(New SqlParameter("@Id", Data.SqlDbType.Int)).Value =
id;
. . ..
cmd.ExecuteNonQuery();
)

You can also find many helpful code generation tools (eg www.codesmith.com)
to spit out such wrappers automatically.

David

 

[William \(Bill\) Vaughn]

This is surely a good solution but IMHO, VS and ADO.NET should provide this
functionality as well as hooks to define business rules as well.
Jerad is right. One would think that this should be done. I suggest a couple
of other (albeit similar) approaches in my 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)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

 

[Rad [Visual C# MVP]]

I believe this question is simple enough -- why aren't data types in
SqlParameters validated in ADO.NET?

For example, why is an exception *not* thrown on this line:

cmd.Parameters.Add(New SqlParameter("@Id", Data.SqlDbType.Int)).Value = "not
an int"

Ideally, this wouldn't even compile, let alone execute. But it's even worse
that this line will execute, only later to bomb out when the stored proc is
executed, giving me this error:

Failed to convert parameter value from a String to a Int32.

Which can lead you on a wild goose chase tracking down the one out of
possibly several parameters that had a type issue.

My next question is, if the data type parameter in this constructor is not
for the sake of type safety, what is its purpose?

Lastly, is there anything that can be done (within reason) to ensure type
safety when setting parameter values, so that an exeption is thrown on the
line where the parameter value was attempted to be set? If not, what is the
best practice for accomplishing type safety when setting up store procedures
in ADO.NET?

Just FYI, this is in reference to ADO.NET 2.0.

Thanks in advance for your feedback.

Jerad

Probably because stored procedures have the same behavior and will try to
convert data types during execution ...

Also, it would be difficult to enforce if some of the parameters being
passed is a user defined data type

 

[Jerad Rose]

Thank you for the responses.

I actually do have my stored procedures encapsulated in wrappers, but what
happens when the issue is in the wrapper itself? For example, when the
types are mistransleted when coding the wrappers from the stored proc to the
wrapper? This was what happened with me. Admittedly, it was fairly easy to
spot, but it still begs the question.

Also, I'm still not clear as to the purpose behind the DbType property of
the SqlParameter. It would seem that this is only for documentation
purposes, but it is definitely misleading in the fact that the DbType does
not have to match the type of the Value.

Thanks again, at least I don't feel like I'm just totally missing something
(or am I?).

Jerad