title: How to convert nullable int to SQL Null value?
body: <p>I need to perform an update to a View that has multiple underlying tables using the ExecuteCommand method of a DataContext. I am using this method because of the known restriction of linqToSQL when performing this type of operation on Views having multiple underlying tables.</p>
  
  <p>My existing SQL statement is similar to the following where I am setting newFieldID to a null value simply for this post to illustrate the issue. In the application, newFieldID is assigned a passed parameter and could actually be an integer value; but my question is specific to the case where the value being provided is a null type:</p>
  
  <pre><code>using (var _ctx = new MyDataContext())
  {
     int? newFieldID = null;
     var updateCmd = "UPDATE [SomeTable] SET fieldID = " + newFieldID + " 
     WHERE keyID = " + someKeyID;
  
     try
     {
        _ctx.ExecuteCommand(updateCmd);
        _ctx.SubmitChanges();
     }
     catch (Exception exc)
     {
        // Handle the error...
     }
  }
  </code></pre>
  
  <p>This code will fail for the obvious reason that the updateCmd won't be completed in the case of a null value for the newFieldID. So how can I replace or translate the CLR null value with an SQL null to complete the statement?</p>
  
  <p>I know I could move all of this to a Stored Procedure but I am looking for an answer to the existing scenario. I've tried experimenting with DBNull.Value but aside from the challenge of substituting it for the newFieldID to use in the statement, simply placing it into the string breaks the validity of the statement.</p>
  
  <p>Also, enclosing it within a single quotes:</p>
  
  <pre><code>var updateCmd = "UPDATE [SomeTable] SET fieldID = '" + DBNull.Value + "'
     WHERE keyID = " + someKeyID;
  </code></pre>
  
  <p>Will complete the statement but the value of the field is translated to an integer 0 instead of an SQL null.</p>
  
  <p>So How does one go about converting a CLR null or nullable int to an SQL Null value given this situation?</p>