How To Use An Update Statement In Sqldataadapter

I am trying to run an update statement after i built my sqldataadapter. I have column called INIT_PHASE in my table and if the INIT_PHASE is null or there is no data then i would

Solution 1:

One approach here would be to use the SqlCommandBuilder to build the UPDATE statement:

stringID= ddlPractice.SelectedValue;
stringTYPE= DDL_TYPE.SelectedValue;
SqlConnectioncon=newSqlConnection(
    ConfigurationManager.ConnectionStrings["myConnection"].ConnectionString);
SqlDataAdapterda=newSqlDataAdapter(
    @"select SET_SK, UNIT_NM, TYPE, INIT_PHASE FROM myTable WHERE UNIT_NM =@ID AND TYPE = @TYPE",
    con);

DataTabledtSETS=newDataTable();
da.SelectCommand.Parameters.AddWithValue("@ID", (ID));
da.SelectCommand.Parameters.AddWithValue("@TYPE", (TYPE));
da.Fill(dtSETS);

SqlCommandBuilderbuilder=newSqlCommandBuilder(da);
da.UpdateCommand = builder.GetUpdateCommand();

if (dtSETS.Rows.Count > 0)
{
    DataRowdtSETS_row= dtSETS.Rows[0];

    longSET_SK= dtSETS_row.Field<long>("SET_SK");

    if (dtSETS_row.Field<string>("INIT_PHASE") == null)
    {
        dtSETS_row["INIT_PHASE"] = 1;
    }
}

da.Update(dtSETS);

Take note to the following lines of code. Here we are building the update command:

SqlCommandBuilderbuilder=newSqlCommandBuilder(da);
da.UpdateCommand = builder.GetUpdateCommand();

here we are literally modifying the DataRow so that it's RowState is changed to Modified:

dtSETS_row["INIT_PHASE"] = 1;

and then finally, here we are sending updates to the database with the Update method on the SqlDataAdapter:

da.Update(dtSETS);

What this is going to do is only send updates for the rows with a RowState of Modified.

NOTE: each of the ADO.NET objects should be wrapped in a using. Refactor your code to match this type of template:

using (SqlConnection con = new SqlConnection(...))
{
    using (SqlDataAdapter da = new SqlDataAdapter(...))
    {

    }
}

Solution 2:

If I understand correctly, you could execute directly a command to update just this field

if (dtSETS_row.Field<string>("INIT_PHASE") == null)
{
    SqlCommand cmd = new SqlCommand(@"UPDATE myTable set INIT_PHASE = 1 " + 
                         "WHERE UNIT_NM =@ID AND TYPE = @TYPE", con);
    cmd.Parameters.AddWithValue("@ID", (ID));
    cmd.Parameters.AddWithValue("@TYPE", (TYPE));
    cmd.ExecuteNonQuery();
}

You need to open the connection though both for the SqlDataAdapter and for the following command

Solution 3:

You will have to use SqlCommandBuilder class for updating data in disconnected mode. ie) DataSet and Data Adapters.