WPF: How do I set different default null values in a DataGrid if a new row is added?

Question

I have a DataGrid which is bound to a DataTable that is filled by an SqlDataAdapter, which fills it with an unspecified table, and I therefore have no way of knowing how the structure in the table is. I've set the TargetNullValue to "NULL", and the cells that contains a null-value from the database shows NULL in the DataGrid, which is perfect.

However... when I want to add a new row using DataGrid's CanUserAddRow, the default values are all "NULL", and I want them to be empty.

I am able to change cells that expect the type of string to show up empty, but in the cells that expect int's for example, it can't contain an empty string, or an int that is NULL. If I want it to be NULL, it has to be DBNull instead, which gives me "NULL" in the cell, which doesn't make any difference.

Any one with an idea how to overcome this issue of mine?

Thanks in advance.

Mainwindow.xaml

<DataGrid x:Name="dataGrid_Table" AutoGenerateColumns="True" CanUserAddRows="true" AutoGeneratingColumn="dataGrid_Table_AutoGeneratingColumn" InitializingNewItem="dataGrid_Table_InitializingNewItem"/>

Mainwindow.xaml.cs

private void dataGrid_Table_AutoGeneratingColumn(object sender, DataGridAutoGeneratingColumnEventArgs e)
    {
        // Make all columns nullable
        ((DataGridBoundColumn)e.Column).Binding.TargetNullValue = "NULL";
    }

    private void dataGrid_Table_InitializingNewItem(object sender, InitializingNewItemEventArgs e)
    {
        DataRowView drv = (DataRowView)e.NewItem;

        for (int i = 0; i < drv.Row.ItemArray.Count(); i++)
        {
            DataColumn col = drv.Row.Table.Columns[i];

            if (col.DataType.ToString() == "System.String")
            {
                drv.Row[i] = "";
            }
            else if (col.DataType.ToString() == "System.Int32")
            {
                // Set cells that expect an int to show up empty
            }
        }
    }

Method for filling the DataTable and binding it to the DataGrid

internal void FillDataGrid(User user, MainWindow mainWindow)
    {
        dt = new DataTable();

        using (SqlConnection con = new SqlConnection(ConnectionString))
        {
            con.Open();

            using (SqlCommand cmd = new SqlCommand("SELECT * FROM " + Name, con))
            {
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                sda.Fill(dt);
                mainWindow.dataGrid_Table.ItemsSource = dt.DefaultView;
            }
        }
    }

@sagar I'm afraid it isn't. Thanks a bunch for trying to help, though! — nickosv

kipras kipras · Accepted Answer · 2019-10-17T15:55:41

I have a similar setup, where a SqlDataAdapter was populating a DataGrid. I needed Boolean fields to be shown on the DataGrid, but it seems that the default values are always NULL for every column value in the row. The Boolean NULL value in the UI looks like a checked box, which misrepresents what the value actually is.

I found a solution that looks a little messy but it get the job done.

You want to add the LoadingRow option to the DataGrid XAML

    <DataGrid .... LoadingRow="myDataGrid_LoadingRow" />

This will appear sometime during the creation of the row. Whether or not this is a new line. So if you have a datagrid with 100 Rows, it will call this function 100 times as each row is loaded. So I did a few things here:

(1) Made sure that the e.Row.Item is a DataRowView Object to avoid an error throw

(2) Checking to see if the row is being created by using .IsNew, avoiding all rows from the original list of rows

(3)As you cast the object as System.Data.DataRowView you will be able to pull up the future values of that row. I have static columns, so that 11 and 10 are my columns. I need to set the values for the new rows to false, but you could enter any values you want (if you want them to be empty use "")

    private void myDataGrid_LoadingRow(object sender, System.Windows.Controls.DataGridRowEventArgs e)
    {
        if (e.Row.Item is System.Data.DataRowView)
        {
            if (((System.Data.DataRowView)e.Row.Item).IsNew)
            {
                ((System.Data.DataRowView)e.Row.Item)[11] = false;
                ((System.Data.DataRowView)e.Row.Item)[10] = false;
            }
        }
    }

I also tried to use LoadingRow but I couldn't work it out correctly.

WPF: How do I set different default null values in a DataGrid if a new row is added?

1 Answers