In my C# code, I have to do an SQL Query like this :
context.ExecuteStoreQuery("SELECT * FROM MyTable WHERE Field0 = {0} AND
Field1 = {1}", field0, field1)
When field1 = null in c# and NULL in database this query doesn't work. (I have to use a different syntax with IS NULL)
How can I correct this without make an if (in reality, I have 10 fields...) ?
By default, SQL server does not allow you to compare a value to null. All comparisons resolve to false, even those that are logically opposite. In other words, you can do:
where field = 1 and where field <> 1. If field is null, both logically resolve to false.
In any case, you need an explicit check for null in your query:
context.ExecuteStoreQuery(@"SELECT * FROM MyTable WHERE
(Field0 = {0} or (Field0 is null and {0} is null)) AND
(Field1 = {1} or (Field1 is null and {0} is null))", field0, field1)
public string appendCondition(String sqlQuery, String field, Object value)
{
string resultQuery = sqlQuery + " " + value == null ? " IS NULL " : "=" + value.ToString();
return resultQuery;
}
Hope you can add simple logic to add "WHERE" or "AND" by yourself.
well, the first thing i would do is remove the select *. BAD!
the second thing i would do is make this a stored procedure.
create procedure dbo.MyTableSelect
@field0 int,
@field1 int=null
as
begin
select
*
from MyTable
where Field0=@field0
and (@field1 is null or Field1=@field1)
end
you then can change your code to this
context.ExecuteStoreQuery("exec dbo.MyTableSelect @field0={0}, @field1 = {1}", field0, field1)
you can use the short variant of the if statement. I don't think you can handle your problem without an if statement. Example:
String.Format("SELECT * FROM MyTable WHERE Field0 {0} ", value==null ? "IS NULL" : String.Format("= {0}", value))
It is also possible to parameterize the query by using "@ParameterName"
context.ExecuteStoreQuery<ProductionUnit>(
String.Format("SELECT * FROM MyTable WHERE Field0 {0} @Parameter1",
value==null ? "IS", "="), new SqlParameter("@Parameter1", value));
Regards
null to string.Format, though this design won't work in this particular scenario. ObjectContext.ExecuteStoreQuery (which is what the user is using) is not just a string formatting function, as it automatically parameterizes the query using the supplied values. This approach will not product valid SQL.
ObjectContext.ExecuteStoreQueryis not the same asstring.Format, despite the similarity in syntax.ObjectContext.ExecuteStoreQueryparameterizes the query automatically.