-- 主健字段不考虑null情况,每次都带 -- 非主健考虑null情况, @IsNuull_xxxx=1 -- 所以一条记录加载后到提交时被别人修改了 --那么更新返回数应该是0-----报错{"违反并发性: UpdateCommand 影响了预期 1 条记录中的 0 条。"} -- 导致同一批次的Insert语句也没挣钱执行 -- 先执行了更新语句,然后删除,最后插入 exec sp_executesql N' UPDATE [UEM_PrjDic] SET [Name] = @Name, [Img] = @Img WHERE ( ([RecId] = @Original_RecId) AND ((@IsNull_AddTime = 1 AND [AddTime] IS NULL) OR ([AddTime] = @Original_AddTime)) AND ((@IsNull_AddEmpNo = 1 AND [AddEmpNo] IS NULL) OR ([AddEmpNo] = @Original_AddEmpNo)) AND ((@IsNull_Name = 1 AND [Name] IS NULL) OR ([Name] = @Original_Name)) AND ((@IsNull_Img = 1 AND [Img] IS NULL) OR ([Img] = @Original_Img)) ) ' , N'@Name nvarchar(8), @Img nvarchar(16), @Original_RecId bigint, @IsNull_AddTime int, @Original_AddTime datetime, @IsNull_AddEmpNo int, @Original_AddEmpNo nvarchar(4000), @IsNull_Name int, @Original_Name nvarchar(8), @IsNull_Img int, @Original_Img nvarchar(4000)', @Name=N'项目名称5108', @Img=N'ldeyzelg.dvu.jpg', @Original_RecId=4, @IsNull_AddTime=1, @Original_AddTime=NULL, @IsNull_AddEmpNo=1, @Original_AddEmpNo=NULL, @IsNull_Name=0, @Original_Name=N'项目名称6205', @IsNull_Img=1, @Original_Img=NULLView Code
C#
using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; using System.Data.SqlClient; using System.IO; namespace ADO_Test { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void button1_Click(object sender, EventArgs e) { var connStr = @"data source=192.168.9.2;initial catalog=NewEC;persist security info=True;user id=sb;password=sbadmin;multipleactiveresultsets=True;persist security info=True;"; var sqlDa = new SqlDataAdapter("Select * from UEM_PrjDic", connStr); var cmdBuilder = new SqlCommandBuilder(sqlDa); var ds=new DataSet(); sqlDa.Fill(ds); #region 添加一行 var row = ds.Tables[0].NewRow(); row.SetField("Name", "项目2"); ds.Tables[0].Rows.Add(row); #endregion #region 删除最后第2行 var lastIndex = ds.Tables[0].Rows.Count -2; ds.Tables[0].Rows[lastIndex].Delete(); #endregion #region 更改行 var rnd=new Random(Environment.TickCount); ds.Tables[0].Rows[3]["Name"] = "项目名称" + rnd.Next(1, 10000); ds.Tables[0].Rows[3]["Img"] = Path.GetRandomFileName() + ".jpg"; #endregion //var changeDs = ds.GetChanges(); sqlDa.UpdateCommand = cmdBuilder.GetUpdateCommand(); sqlDa.DeleteCommand = cmdBuilder.GetDeleteCommand(); sqlDa.InsertCommand = cmdBuilder.GetInsertCommand(true); var updateSQL = sqlDa.UpdateCommand.CommandText; var insertSQL = sqlDa.InsertCommand.CommandText; var delSQL = sqlDa.DeleteCommand.CommandText; sqlDa.Update(ds); Console.WriteLine("操作完成!"); } } }View Code
---
测试下自定义CURD 命令