CODEWITHSUNDEEP
c#sql-server-2012strongly-typed-datasetdataset-designer
Julien N
Julien N

Reputation: 3920

Strongly Typed Dataset designer resets AllowDbNull on parameter

I'm working on updating an existing application that relies heavily on Strongly Typed Datasets.
The original developer created a lot of very similar queries on each dataset to handly any combination of parameters possible, which is a pain to update.

So I to create only a few of them but with optional parameters, so the filter is ignored if the parameter is set to null : WHERE (@code IS NULL OR art_code = @code) and that with most parameters.
For this to work I have to manually access and edit the Parameters collection in the Query properties to set AllowDbNull to True (I guess that as the column is not nullable, the designer automatically set it to False)

This works very well and as expected.

The problem is that the designer keeps resetting this value and there is a high risk of regression each time I edit a Dataset, even if I don't touch the query.

Is there something I can do to prevent that ?
It could be something in the way I write the query ?

C# 4.0 / Visual Studio 2012 / Sql server 2012

Upvotes: 0

Views: 399

Answers (1)

Caius Jard
Caius Jard

Reputation: 74700

Indeed, it's a complete pain in the arse that the designer has these foibles. I have in the past introduced bogus columns to tables or where clauses that will never work out, just to get the designer to form a different opinion

Try adding something like this to your query WHERE:

WHERE ((@code IS NULL OR art_code = @code) ... other clauses ...) 
OR
--non-op to change visual studio dataSet designer behaviour, do not remove
(some_other_nullable_column_with_same_type = @code AND 0=1)

If it doesn't work out, swap the order around so your dummy column appears first in the where.

And if that doesn't work out, you could write a program that inspects the dataSet xml and reverts any relevant changes, put it as part of your pre build events setting in e project so it runs every time a build is done. The app might also have to invoke the dataSet generator. How you do this I'm not sure as MSDatasetGenerator in the custom tool might not be the same thing as xsd.exe with the /dataSet param. At the very least you could quit your dataSet repair app with an error code, which halts the build and make sure an error like "dataSet repairs have been performed, right click the dataSet xx and choose run custom tool, in solution explorer" is pumped to the command line

You can also save yourself some pain by using source control, like mercurial or git and keeping your code in somewhere like bibucket. The manhours I've saved since keeping my code in a version control system is uncountable; every time you do a commit, the option of reverting changes to your dataSet will avail itself if the set has changed even if you didn't touch it, ought you'll need to recompile (rerun custom tool too probably) after you revert

Upvotes: 2

Related Questions