用webgrid中的字符串替换空值(Replacing nulls with strings in a webgrid)

用webgrid中的字符串替换空值(Replacing nulls with strings in a webgrid)

我正在使用webmatrix，我想在我的变量“grid”中存储一个webgrid，但是我希望我创建它的表中的一列中的所有空值都被字符串替换：“aString”。 我使用下面的代码从我的数据库表中加载我的网格变量。

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

有没有简单的方法来做到这一点？

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?

最满意答案

有几种方法可以解决这个问题。 一种方法是更改​​SQL，以便不返回null。 例如，你可以把：

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

coalesce（）函数用你想要的字符串替换空值。 “AS”表示结果应该改为名为“改进字段”的字段。

我上面提到的代码的一个缺点是，由于它具有*，因此您将获得具有空值的原始字段以及没有空值的改进字段。 您可以将*替换为所需的所有字段的列表。

另一种方法不是更改SQL，而是在使用它时更改输出。 这取决于你正在做什么，所以我不会在这里展示一个例子。

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.