c# - 在 SqlDataAdapter.Update() 中获取错误消息

Question

我有一个数据库表(在这个例子中是空的)

create table words
(
    id int not null,
    word nvarchar(50) not null
)

和我正在填充的 DataGridView:

    private SqlConnection _conn;
    private SqlDataAdapter _wordsAdapter;
    private DataTable _wordsDataTable = new DataTable();
    private DataGridView _tblWords;

    private void FillWords()
    {
        _wordsAdapter = new SqlDataAdapter(new SqlCommand("select id, word from words", _conn));
        _wordsAdapter.Fill(_wordsDataTable);

        DataGridViewColumn column = new DataGridViewTextBoxColumn();
        column.DataPropertyName = "id";
        column.Name = "id";
        _tblWords.Columns.Add(column);

        column = new DataGridViewTextBoxColumn();
        column.DataPropertyName = "word";
        column.Name = "word";
        _tblWords.Columns.Add(column);

        _tblWords.DataSource = _wordsDataTable;
    }

    private void Update()
    {
        _wordsAdapter.InsertCommand = new SqlCommand("insert into words (id, word) values (@id, @word)", _conn);
        SqlParameter p = _wordsAdapter.InsertCommand.Parameters.Add("@id", SqlDbType.Int);
        p.SourceColumn = "id";
        p.SourceVersion = DataRowVersion.Original;
        p = _wordsAdapter.InsertCommand.Parameters.Add("@word", SqlDbType.NVarChar);
        p.SourceColumn = "word";
        p.SourceVersion = DataRowVersion.Original;
        _wordsAdapter.Update(_wordsDataTable);
    }

然后我用一些值填充单行 _tblWords 并调用 Update()。并收到此消息:

Exception thrown: 'System.Data.SqlClient.SqlException' in System.Data.dll
Additional information: Cannot insert the value NULL into column 'id', table 'DB.MDF.dbo.words'; column does not allow nulls. INSERT fails.

为什么“id”的值不是从 DataTable 中获取的？我做错了什么？

更新:在 Update() 函数的开头插入此代码后，一切正常:

    private void Update()
    {
        _wordsDataTable.Rows.Clear();
        _wordsDataTable.Rows.Add(0, "xxx");
        ...

所以只有当我通过DataGridView填充DataTable时才会出现这个问题。为什么？!

Answer 1

我猜测问题与 DataGridView 中的最后一个空行有关。

你可以尝试这样的事情而不是最后的 _wordsAdapter.Update(_wordsDataTable); 行

var haveDbNull = _wordsDataTable.Rows.Cast<DataRow>().ToLookup(r => r.ItemArray.Contains(DBNull.Value));

Debug.Print("have DbNull values: " + haveDbNull[true].Count()); // check this number in the Debug Output window
Debug.Print("don't have DbNull values: " + haveDbNull[false].Count()); 

_wordsAdapter.Update(haveDbNull[false].ToArray());  // to update just the rows that dont have DBNull values