SQL Server : change NULL to blank on select statement for all columns

Question

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.

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.

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.

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.

What are you using to interface with the database, is it pure ADO.Net or are you using an ORM? What are the nullable data types? — Joe_DM, Jul 20 '17 at 08:25
I believe you would be mapping data from DB to an object which is where you can make this change. — Praveen, Jul 20 '17 at 08:25
@Joe_DM ADO.Net and the nullable data types are all strings but my web page can't use the nulls. — andy wilson, Jul 20 '17 at 08:25
@Praveen How would I go about doing that as I am fairly new to C# — andy wilson, Jul 20 '17 at 08:26
Also, what are you returning? E.g. Are you manually reading and parsing values out of a datareader — Joe_DM, Jul 20 '17 at 08:26
If it's all strings, you can safe cast. E.g. Datareader[0] as string — Joe_DM, Jul 20 '17 at 08:27
@Joe_DM could you make an example in an answer so that I can see if its what i'm looking for — andy wilson, Jul 20 '17 at 08:30
Just read that null is no good. You can use 'reader[0] as string ?? String.Empty'... I'm happy for someone else to post answer with explanation. I'm on phone and fighting autocorrect — Joe_DM, Jul 20 '17 at 08:30
Alternatively, maybe check out dapper. Might be exactly what you want. https://github.com/StackExchange/Dapper — Joe_DM, Jul 20 '17 at 08:38
Instead of using `Json()` you can write your own subclass of `JsonResult` that converts nulls to empty strings using a custom serializer/resolver and return that (`return new MyJson(dtb)`) . See https://stackoverflow.com/a/23832417/491907 for how you might implement the null to empty. See https://stackoverflow.com/a/20981338/491907 for how to implement a subclass of JsonResult (though this answer is about excluding nulls, you can combine the two approaches). The plus side to this is then you have something reusable and you don't have to do it in SQL. — pinkfloydx33, Jul 20 '17 at 08:42
@pinkfloydx33 I think the main problem is that the type is DbNull which is different to null — Joe_DM, Jul 20 '17 at 08:47
I just need to be able to turn any of the columns if they are null to '' — andy wilson, Jul 20 '17 at 08:50

SH7 · Answer 1 · 2017-07-20T08:45:56.563

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;

            }

edited Jul 20 '17 at 08:45

answered Jul 20 '17 at 08:27

SH7

732
7
20

Wouldn't I have to do that for each column though? – andy wilson Jul 20 '17 at 08:28
Yes , i'm afraid so. – SH7 Jul 20 '17 at 08:32
do you know how to do datareader?? as im looking at a scalable system that i dont have to edit every part to update 1 thing – andy wilson Jul 20 '17 at 08:33
I will add my sample query and how i do that for reference. Use if you find it useful. – SH7 Jul 20 '17 at 08:35
This can be done easier with: 'string ServiceName = reader["ColumnNameFromQuery"] as string ?? string.Empty;' and since all columns are string it can be put in a loop – Joe_DM Jul 20 '17 at 08:35
for loop would i just do foreach(DataRow row in dtb.Rows){ string ServiceName = reader["ColumnNameFromQuery"] as string ?? string.Empty – andy wilson Jul 20 '17 at 08:37
It would be more like 'row as string ?? string.Empty' in that example for each. – Joe_DM Jul 20 '17 at 08:41

score 0 · Accepted Answer · answered Jul 20 '17 at 09:04

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

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 () {
                });

            });
}

SQL Server : change NULL to blank on select statement for all columns

2 Answers2