c# - Command.Parameters["@name"].Value Correct syntax -

i attempting use query select table using 1 - 3 parameters;

select * plantareacodes  (@areacode null or areacode = @areacode)  , (@areaname null or areaname @areaname)  , (@comments null or comments @comments); 

here c# code query:

mysqlcommand = mysqlconnect.createcommand();  mysqlcommand.commandtext = "select * plantareacodes (@areacode null or areacode = @areacode) , (@areaname null or areaname @areaname) , (@comments null or comments @comments);";  mysqlcommand.parameters.add("@areacode", mysqldbtype.int32); mysqlcommand.parameters.add("@areaname", mysqldbtype.varchar); mysqlcommand.parameters.add("@comments", mysqldbtype.text);  mysqlcommand.parameters["@areacode"].value = (string.isnullorempty(convert.tostring(pmodel.areacode)) ? (object)dbnull.value : pmodel.areacode); mysqlcommand.parameters["@areaname"].value = (string.isnullorempty(pmodel.areaname) ? (object)dbnull.value : pmodel.areaname); mysqlcommand.parameters["@comments"].value = (string.isnullorempty(pmodel.comments) ? (object)dbnull.value : pmodel.comments);  mysqlreader = mysqlcommand.executereader(); 

this query allows user search one, two, 3 or no fields without throwing errors, works nullable types. therefpre since pmodel.areacode int, search uses 0 instead of null. ie.

select * plantareacodes  (null null or areacode = null)  , (null null or areaname null)  , (null null or comments null); 

which return no fields because areacode = 0 valid query.

i have tried making areacode nullable declaring int? areacode = null, doesn't work. returns fields, instead of one, same if used default null:

select * plantareacodes  (110 null or areacode = 110)  , (null null or areaname null)  , (null null or comments null); 

however query returns 1 field:

select * plantareacodes  (110 null or areacode = 110)  , ('%general%' null or areaname '%general%')  , (null null or comments null); 

this returns fields containing word 'general' in name:

select * plantareacodes  (null null or areacode = null)  , ('%general%' null or areaname '%general%')  , (null null or comments null); 

how can use non-nullable types in query?

ok got working. main issue needs compare null null, int can't null value type.

so had tweaking code , got working workaround

int? areacode = null; 

this allowed me change code:

mysqlcommand.parameters["@areacode"].value = (pmodel.areacode.hasvalue ? pmodel.areacode.value : object)dbnull.value ); 

for aren't aware of what's happening here:

the operator ? gives value type ability have no value, can = null.

ie.

int n = 0; int? n = null;  bool b = false; bool? b = null;