SQL Server：在所有列的select语句中将NULL更改为空白

I am calling a SQL Server database through my Web API which is then sending the data to my webpage only problem is when it does that some of the values are null and it errors.

我通过我的Web API调用SQL Server数据库，然后将数据发送到我的网页只是问题是当它执行时某些值为空且出错。

How would I make it so when I pull the data using SQL that I make it check every column and row for nulls and change them to '' without writing ISNULL(a.BuildingNumber, '') or am I trying to attempt something that isn't possible.

当我使用SQL提取数据时，我将如何实现这一点，我会检查每个列和行是否为空，并将它们更改为''而不写ISNULL（a.BuildingNumber，''）或者我是否尝试尝试不可能。

Or is it possible to loop through the DataTable that I make when C# gets the data from SQL Server, if so would something like a foreach loop work instead.

或者，当C＃从SQL Server获取数据时，是否可以循环遍历我所创建的DataTable，如果是这样的话，就会像foreach循环那样工作。

using(SqlCommand cmd = new SqlCommand(query, con))
            {
                cmd.CommandType = CommandType.Text;
                var dtb = new DataTable();
                var da = new SqlDataAdapter(cmd);
                da.Fill(dtb);
                return Json(dtb);
            }

That is what I do after my query to return it.

这是我在查询后返回它的方法。

2 个解决方案

#1

You could check in the following way

您可以通过以下方式检查

string ServiceName = reader["ColumnNameFromQuery"] != System.DBNull.Value ? reader["ColumnNameFromQuery"].ToString() : "";

An Example

一个例子

                string QueryGetData = "select Name as Name , Age as Age from tableone;";

                        SqlCommand cmd = m_SqlConnection.CreateCommand();
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = QueryGetData;
                        SqlDataReader reader = cmd.ExecuteReader();
                        while (reader.Read())
                        {
                            List<string> Data = new List<string>();

                            string strName = reader["Name"] != System.DBNull.Value ? reader["ServiceName"].ToString() : "";
            string strName = reader["Age"] != System.DBNull.Value ? reader["Age"].ToString() : "";

           //If Int Set Default as Zero
            int nIntValue = reader["IntColumnName"] != System.DBNull.Value ? Convert.ToInt32(reader["IntColumnName"].ToString()) : 0;

            }

#2

I got the desired result by doing it in my javascript like so

我通过我的javascript这样做得到了理想的结果

getData() {
    var search_url = "http://10.0.1.96/testwebapi/api/case/"
        + this.state.ApiUrl + "/" + this.props.match.params.id

    var _this = this;
    this.serverRequest =
        axios
            .get(search_url)
            .then(function (result) {
                for (var property in result.data[0]) {
                    if (result.data[0][property] === null) {
                        result.data[0][property] = '';
                    }
                }    
                _this.setState({
                    [_this.state.ApiUrl]: result.data[0]
                }, function () {
                });

            });
}

#1