SqlDataAdapter .update函数未更新

问题描述

我在Sql数据库中有一个表调用RSN_ALl

，我想用我的XML文件更新它的几行，基于RSN列主键。



我尝试通过sqlcommnadBuilder和其他方式，但它没有更新。



注意：在XMl文件中我有2或3行有相同的RSN 作为数据库的价值，但其他列值不同，我需要更新

我有例外



更新需要有效在使用新行传递DataRow集合时插入命令。



当我使用CommandBuilder时，我得到了例外情况



违反PRIMARY KEY约束'PK_RSN_All'。无法在对象'dbo.RSN_All'中插入重复键。

该语句已被终止。



但此行已经存在于Sql DataBase中使用（bqlConnection cn = new SqlConnection（SqlHelper.ConString））
{
DataSet DsXmlData = new DataSet（）;
DsXmlData.ReadXml（xml_file_path）;
DsXmlData.Tables [RSN_ALL]。PrimaryKey = new DataColumn [] {DsXmlData.Tables [RSN_ALL]。列[RSN]};

using（SqlDataAdapter da = new SqlDataAdapter（select * from RSN_All，cn））
{
DataSet ds = new DataSet（）;
da.Fill（ds）;

// SqlCommandBuilder SqlcommBui = new SqlCommandBuilder（da）;
// string命令= SqlcommBui.GetUpdateCommand（）。CommandText;

string updataCommand =update RSN_All set Batch_M_id = @Batch_M_id，Parent_RSN = @ Parent_RSN，Pkg_Location = @ Pkg_Location，CompanyId = @ CompanyId，其中RSN = @ RSN;
SqlCommand cmd = new SqlCommand（updataCommand，cn）;
cmd.Parameters.Add（@ Batch_M_id，SqlDbType.BigInt，0，Batch_M_id）;
cmd.Parameters.Add（@ Parent_RSN，SqlDbType.VarChar，20，Parent_RSN）;
cmd.Parameters.Add（@ Pkg_Location，SqlDbType.NVarChar，100，Pkg_Location）;
cmd.Parameters.Add（@ CompanyId，SqlDbType.Int，0，CompanyId）;
cmd.Parameters.Add（@ RSN，SqlDbType.VarChar，20，RSN）;
da.UpdateCommand = cmd;

da.Update（DsXmlData.Tables [RSN_ALL]）;
}
}

解决方案

I have One Table Call RSN_ALl in Sql database
, I want to Updates its few row by my XML file,based On RSN column Primary key.

I try By sqlcommnadBuilder and other way but its not updating.

Noted: In XMl file I have 2 or 3 rows which have Same "RSN" value as DataBase but other Columns Values are Different Which I need to Update
I Got Exception as

Update requires a valid InsertCommand when passed DataRow collection with new rows.

And When I use CommandBuilder I got Exception as

Violation of PRIMARY KEY constraint 'PK_RSN_All'. Cannot insert duplicate key in object 'dbo.RSN_All'.
The statement has been terminated.

but this Rows Already Present in Sql DataBase TAble

using (SqlConnection cn = new SqlConnection(SqlHelper.ConString))
{
   DataSet DsXmlData = new DataSet();
   DsXmlData.ReadXml(xml_file_path);
   DsXmlData.Tables["RSN_ALL"].PrimaryKey = new DataColumn[] { DsXmlData.Tables["RSN_ALL"].Columns["RSN"] };

   using (SqlDataAdapter da = new SqlDataAdapter("select * from RSN_All", cn))
   {
      DataSet ds = new DataSet();
      da.Fill(ds);

      // SqlCommandBuilder SqlcommBui = new SqlCommandBuilder(da);
      //string Command = SqlcommBui.GetUpdateCommand().CommandText;

      string updataCommand = "update RSN_All set Batch_M_id = @Batch_M_id ,Parent_RSN =@Parent_RSN, Pkg_Location =@Pkg_Location, CompanyId =@CompanyId where RSN =@RSN";
      SqlCommand cmd = new SqlCommand(updataCommand, cn);
      cmd.Parameters.Add("@Batch_M_id", SqlDbType.BigInt, 0, "Batch_M_id");
      cmd.Parameters.Add("@Parent_RSN", SqlDbType.VarChar, 20, "Parent_RSN");
      cmd.Parameters.Add("@Pkg_Location", SqlDbType.NVarChar, 100, "Pkg_Location");
      cmd.Parameters.Add("@CompanyId", SqlDbType.Int, 0, "CompanyId");
      cmd.Parameters.Add("@RSN", SqlDbType.VarChar, 20, "RSN");
      da.UpdateCommand = cmd;

      da.Update(DsXmlData.Tables["RSN_ALL"]);
   }
}

解决方案

这篇关于SqlDataAdapter .update函数未更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！