Search code examples
nullwebmatrixwebgrid

Replacing nulls with strings in a webgrid

I am using webmatrix and I would like to store a webgrid in my variable "grid", however I would like all my null values in one column from the table I am creating it from to be replaced by the string: "aString". I load my grid variable from my database table with the following code.

@{
var db = Database.Open("databaseName");
var selectedData = db.Query("SELECT * FROM TableName");
var grid = new WebGrid(source: selectedData, rowsPerPage: 500); 
}

Is there any simple way of doing this?

Solution

  • There are several approaches to solving the problem. One approach is to change the SQL so that no null is returned. For example, you could put:

    var selectedData = db.Query("SELECT *, coalesce(OriginalField, 'aString')  AS improvedField FROM TableName");

    The coalesce() function replaces nulls with the string you want. The "AS" indicates that the results should be aliased to a field named "improved field".

    One disadvantage of the code I put above is that since it has the * in it, you will get both the original field with nulls and the improved field without nulls. You could replace the * with a list of all the fields you want.

    Another approach is not change the SQL but to change the output when you go to use it. That depends on what you're doing with it, so i won't show an example here.