TableAdapter UpdateCommand on JOINed table

问题:

Suppose I have a DataGridView that is loading from a TableAdapter whose content is from 2 JOINed tables, so Table C is:

SELECT A.*, B.name LEFT JOIN B ON B.id = A.b_id

No UpdateCommand is generated for this by the wizard, I know. However, if the data of the table is almost entirely from Table A, and Table B is only joined in to provide the name of data referenced by id in Table A, can I just supply my own UpdateCommand that updates Table A if the user changes a value in the DataGridView?

That is, I would like to set Table C's UpdateCommand to:

UPDATE A SET value = [[new value]] WHERE id = [[current item]]

If worse comes to worse, I can make a dialog for the user to enter their new value into and do it that way. It just seems like it would be a lot simpler to do it as above. Will that method work?

回答1:

You can do exactly what you want within the confines of the DataAdapter. You can find a good walkthrough on MSDN.

Without seeing your code, the setup of your adapter might look something like this:

var dataAdapter = new SqlDataAdapter(
    "SELECT A.*, B.name FROM A LEFT JOIN B ON B.id = A.b_id", sqlConn);

var dataAdapter.UpdateCommand = new SqlCommand(
    "UPDATE A SET value = @Value WHERE id = @Id", sqlConn);

// Define the parameters to be used in the update command.
dataAdapter.UpdateCommand.Parameters.Add(
    "@Value", SqlDbType.NVarChar, 100, "Value_Column_Name");

dataAdapter.UpdateCommand.Parameters.Add(
    new SqlParameter("@Id", SqlDbType.Int)
    {
        SourceColumn = "Id_Column_Name"
    });