Pass null sqlparameter

Please Sign up or sign in to vote. AddWithValue method. I'm sure you are all familiar with code such as the following, to easily convert a bunch of parameter values stored in good old. NET object properties: C. Copy Code. Brady Kelly. Founder Erisia Web Development. I am a software developer in Johannesburg, South Africa.

I specialise in C and ASP. I have been in this business for about eighteen years, and am currently trying to master Angular 4 and. NET Core, and somehow find a way to strengthen my creative faculties. First Prev Next Thanks so much!! Member 6-Sep AlanSB Jan Brady Kelly Jan PSU Steve 6-Jan Brady Kelly 6-Jan Go to top. Layout: fixed fluid. South Africa. It's terse but ugly IMHO :. What I like about method 3 is that you can use it with cmd.

That was initially my preference as well, but I'm also considering option 3 which seems more flexible. What do you think? FYI, option 4 is out because SqlParameter is not inheritable. Not that it matters because I didn't like this option anyway. I would name the method something like. I was using 1 because it was the most obvious, but as I was building my DAL I kept having this same statement repeated over and over and I thought there has to be a better way.

I don't like his method for my situation, but it got me thinking. You're right. I was using the wrong terminology. Also, I think that if statement obstructs the intention of the code which is simply to add a parameter. It gives the appearance of something more complicated happening. But you're right, it's not very generic and doesn't provide any advantage over 3.

The content you requested has been removed. Ask a question. Quick access. Search related threads. Remove From My Forums. Answered by:.

Archived Forums. Architecture for ASP. NET application designs. Sign in to vote. User posted An exception will eventually be caused by the following line if clientID is null. IIRC the difference is significant in some places, e. I do know that it's not something we can just consider doing without a lot of careful analysis. IMO this is also something that needs to be considered from a cross-database perspective, and not as a pure SqlClient change.

I guess the question then if I use a standard DbParameter in npgsql, does it consider null to be DbNull or does it throw an obscure message? It seems most client abstraction libraries like EFCore and Dapper will hide the difference for the user when using parameterized queries. Now I suppose in the case of a store procedure call, a parameter that has no value will invoke the default.

Of course, if this is the desired nonsense behavior than perhaps it could be solved with a boolean on SqlParameter like IsDefault or whatever. The crux I keep hearing is that sqlclient was written in. Hi mburbea. NET driver to suddenly treat language null as database null instead of default , you're potentially breaking a whole lot of programs out there which rely on this behavior which has been around since forever.

Of course, if there's a reliable way to detect the error situation and error with a better message, that's always a good idea but I'm not sure that's the case here. I'm going to say that the behavior is safe to treat null as DbNull when the CommandType property is set to CommandType.

Text the default for SqlParameters. I am also not suggesting to make this change in the DataReader or in ExecuteScalar. Since procedures, may want this behavior where unsupplied parameters are treated as default, and I have no idea what the CommandType. TableDirect edit: docs seem to suggest this only works for OLEDB option does for parameters, we can safely make this change.

I suppose if anyone was testing for this exception, they would break. Like I acknowledged earlier, it's a breaking change, but outside of testing for an exception, I can't imagine anyone relying on this behavior. Much like the BCL behavioural changes are extremely hard to justify in old apis, subtle behavioural changes in this library even though it's optional are hard to justify.

The upgrade path from System to Microsoft versions needs to be smooth to allow adoption in LOB software. Now, when we get generic parameters like postgres has that'll be all new surface area with no need to use DBNull and then we can do things more appropriate for the language and runtime we have today.

The runtime has already made some breaking changes to allow behavior that has always thrown an exception. Again, since this is only an exception, and it is very unlikely to be caught in anything but a unit test testing for an exception they still made the change. I think that being so cautious means that the library can never evolve and we are simply stuck with crappy behavior that nobody uses, and basically everyone is required to adopt an abstraction layer.

FYI I guess this is known workaround already This issue is solved by passing parameter as below:. I'm also sure that it will have broken some code being ported from netfx. Going from System to Microsoft versions of this library really needs not to contain breaking changes if at all possible. I'm not saying it won't I know cancellation exceptions are going to change soon but the bar for approval of those changes is pretty high. In context I don't think there is a lot that can be done with the legacy parameter mechanism without high risk of breaking someone's code, even if they wrote bad code I still don't want to break it if I can help it.

Wraith2 , ha didn't realize you made that change. That's pretty funny as that was the first breaking change that came to my mind. Edit: I guess like , we worst case can introduce a switch and default it to false in.

Also nit: If I don't specify dbtype the inferred type by the library is nvarchar , where as sqlservers inferred default type is int. Like issue , I can assume this incongruity is in the hell freezing over category before this ever gets fixed. I don't see how CommandType is related, or how it's safe. The change you propose will insert database NULL instead, which is a pretty major breaking change. Changing behavior to not throw is by definition not considered a breaking change.

What you're proposing would change the meaning of existing, working code to do something entirely different. To be honest, I don't really see the point of this discussion The current way things work don't block anyone - as cheenamalhotra mentioned above, users simply need to pass DBNull. Value instead of null; regardless of what we think of it, it is what it is and that's how ADO. NET works. If users accidentally use null in the wrong way, it's unfortunate that they get a cryptic message, but that certainly doesn't seem worth a potentially huge breaking change.

That isn't to say things shouldn't be improved if they can be, but it's better to concentrate on more valuable things. SqlException 0x : The parameterized query ' nvarchar insert into dbo.

However, if I use a store procedure which does have a default value supplied for a parameter like Then the following works as you describe note that I have to set the mode to procedure. This is why I mentioned to keep the behavior of null meaning default for procedures. I say it's an error and an odd design because lots of people run into this problem. I disagree with the viewpoint that it doesn't block people so it shouldn't be fixed.

I don't think just because the workaround is simple, doesn't mean we shouldn't correct the problem. I can't think of any case where calling AddWithValue with a null value is ever the right thing to have done and I think I'd have said it should throw an exception.

We can't change that either.