如何在SQLDataAdapter中使用更新语句

问题描述

在构建sqldataadapter之后，我试图运行更新语句.我的表中有一个称为INIT_PHASE的列，如果INIT_PHASE为null或没有数据，那么我想将其设置为1.请帮助.这是我的代码:

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 like to set it to 1. I have tried but i can't seem to get it right the update statement. Pls. help. here is my code:

 string ID = ddlPractice.SelectedValue;
    string TYPE = DDL_TYPE.SelectedValue;
    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["myConnection"].ConnectionString);
    SqlDataAdapter da = new SqlDataAdapter(@"select SET_SK, UNIT_NM, TYPE, INIT_PHASE FROM myTable WHERE UNIT_NM =@ID AND TYPE = @TYPE", con);
    DataTable dtSETS = new DataTable();
    da.SelectCommand.Parameters.AddWithValue("@ID", (ID));
    da.SelectCommand.Parameters.AddWithValue("@TYPE", (TYPE));
    da.Fill(dtSETS);

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

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

        if (dtSETS_row.Field<string>("INIT_PHASE") == null)
        {

           //run update command here

             update myTable set INIT_PHASE = 1;

        }
    }

推荐答案

这里的一种方法是使用 SqlCommandBuilder 来构建 UPDATE 语句:

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

string ID = ddlPractice.SelectedValue;
string TYPE = DDL_TYPE.SelectedValue;
SqlConnection con = new SqlConnection(
    ConfigurationManager.ConnectionStrings["myConnection"].ConnectionString);
SqlDataAdapter da = new SqlDataAdapter(
    @"select SET_SK, UNIT_NM, TYPE, INIT_PHASE FROM myTable WHERE UNIT_NM =@ID AND TYPE = @TYPE",
    con);

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

SqlCommandBuilder builder = new SqlCommandBuilder(da);
da.UpdateCommand = builder.GetUpdateCommand();

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

    long SET_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:

SqlCommandBuilder builder = new SqlCommandBuilder(da);
da.UpdateCommand = builder.GetUpdateCommand();

在这里，我们实际上是在修改 DataRow ，以便将其 RowState 更改为 Modified :

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

dtSETS_row["INIT_PHASE"] = 1;

最后，在这里，我们使用 SqlDataAdapter 上的 Update 方法将更新发送到数据库:

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

da.Update(dtSETS);

这将只发送 RowState 为 Modified 的行的更新.

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

注意::每个ADO.NET对象都应使用包裹.重构代码以匹配这种类型的模板:

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(...))
    {

    }
}

这篇关于如何在SQLDataAdapter中使用更新语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！