|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Why aren't data types in SqlParameters validated?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
Show quote
"Jerad Rose" <no@spam.com> wrote in message Yes. Write strongly-typed wrappers for your procedures.news:uztygYXWHHA.1036@TK2MSFTNGP03.phx.gbl... >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? > 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 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. -- Show quote____________________________________ 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) ----------------------------------------------------------------------------------------------------------------------- "David Browne" <davidbaxterbrowne no potted m***@hotmail.com> wrote in message news:%23UQsNwbWHHA.4132@TK2MSFTNGP06.phx.gbl... > > > "Jerad Rose" <no@spam.com> wrote in message > news:uztygYXWHHA.1036@TK2MSFTNGP03.phx.gbl... >>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 > > > > 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 Show quote "William (Bill) Vaughn" <billvaRemoveT***@nwlink.com> wrote in message news:e%23$WtreWHHA.3948@TK2MSFTNGP05.phx.gbl... > 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) > ----------------------------------------------------------------------------------------------------------------------- > > "David Browne" <davidbaxterbrowne no potted m***@hotmail.com> wrote in > message news:%23UQsNwbWHHA.4132@TK2MSFTNGP06.phx.gbl... >> >> >> "Jerad Rose" <no@spam.com> wrote in message >> news:uztygYXWHHA.1036@TK2MSFTNGP03.phx.gbl... >>>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 >> >> >> >> > > On Mon, 26 Feb 2007 02:17:32 -0500, Jerad Rose wrote:
Show quote > I believe this question is simple enough -- why aren't data types in Probably because stored procedures have the same behavior and will try to> 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 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  |
|||||||||||||||||||||||
Other interesting topics