(SQL) Efficient way of replacing of NULL values in DataGridView?

Question

I have a database table like this on SQL Server:

USER   PASSWORD
1      samplepassword
2      NULL
3      NULL
4      sample_password

I want to replace the NULL values in the PASSWORD column, along with other columns, with values like '(Not set)' or '-' upon displaying it to the user in a DataGridView.

There are three ways I know of in achieving this. First is to use the NullValue property of the column's DefaultCellStyle. The concern with this method is that the designer would create multiple copies of the same DefaultCellStyle - one per column.

Then there's the CellFormatting event of the DataGridView. Lastly, the replacing can be done on the SQL statement itself, ala ISNULL(password, '(Not set)').

Considering that this DataGridView can be filtered afterwards by the user (e.g. show only those without a password), what is the more suggested way in doing this?

Thanks!

for handle in DataGridView ,check this link. [Check null in DataGridView](http://stackoverflow.com/questions/1979806/using-evalitem-handling-null-value-and-showing-0-against) — ihsan, Jan 04 '17 at 07:37

Fabio · Accepted Answer · 2017-01-04T07:03:55.277

Formatting is not SQL server responsibility, keep formatting in your UI code.

Use DefaultCellStyle and create instance of DefaultCellStyle in the code and set same instance to the all columns of datagridview manually.

Or assign only NullValue property to already existed styles

const string NULL_VALUE = "not set";
DataGridView.Columns["ColumnName1"].DefaultCellStyle.NullValue = NULL_VALUE;
DataGridView.Columns["ColumnName2"].DefaultCellStyle.NullValue = NULL_VALUE;

M.Y.Mnu · Answer 2 · 2017-01-04T07:05:44.890

0

SELECT ISNULL(YourColumn, 'yourcharacter' ) FROM YourTableName

edited Jan 04 '17 at 07:05

answered Jan 04 '17 at 06:55

M.Y.Mnu

718
8
22

score 0 · Answer 3 · answered Jan 04 '17 at 06:58

0

Not 100% sure on SQL Server but on MySQL I wold do the following

SELECT USER, IF(PASSWORD IS NULL,'Not Set', PASSWORD) AS PASSWORD FROM TABLE

answered Jan 04 '17 at 06:58

badger0053

1,179
1
13
19

score 0 · Answer 4 · answered Jan 04 '17 at 07:05

0

Run a JavaScript or jQuery function after your DataGridView load, to find empty values from DataGridView and replace it with "(Not set)" or "-".

OR

Update your dataset values which are empty with values "-".

answered Jan 04 '17 at 07:05

Harshal Yelpale

535
3
21

I'd put it at the frontend as well. Exactly how to change the values depends on what frameworks are at play there though. If AngularJs or similar framework it could be done in the controller before loading the grid with data, would probably be way easier that way – JFM Jan 04 '17 at 07:22

CDove · Answer 5 · 2017-01-04T17:00:36.243

The selected answer is the best one from a paradigm standpoint, though you can also handle this by creating a helper function to handle nulls. This will make your default values something you can change based on your datatype. It also lets you manage nulls before they ever touch the UI, but without affecting your queries, which is essential if you have to handle mathematics before displaying output.

public static dynamic NullCheck(object d, dynamic default)
{      
      return DbNull.Value == d ? default : d;
}

Just be ready to cast the result as needed in your code, such as ((foo)(Nullcheck(foo, bar))).

(SQL) Efficient way of replacing of NULL values in DataGridView?

5 Answers5