ADO.net一些测试

-- 主健字段不考虑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=NULL

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("操作完成!");
        }
    }
}

---

测试下自定义CURD 命令

 

 

SqlDataAdapter.Update 后更新RecId, 并返回RecId --自增长列更新问题

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", "项目2x" + Guid.NewGuid().ToString());
            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.UpdateCommand = new SqlCommand("Update UEM_PrjDic Set Name='@Name' where RecId=10" );
            var nameParam=new SqlParameter();
            nameParam.ParameterName="@Name";
            nameParam.SourceColumn="Name";
            nameParam.SourceVersion=DataRowVersion.Current;
            sqlDa.UpdateCommand.Parameters.Add(nameParam);
            sqlDa.DeleteCommand = cmdBuilder.GetDeleteCommand();


            var insertCmd = new SqlCommand("INSERT INTO [UEM_PrjDic] ([AddTime], [AddEmpNo], [Name], [Img]) VALUES (@p1, @p2, @p3, @p4);select SCOPE_IDENTITY() as RecId"); //如果RecI->Name,会发现Name变成59 ,60...
            //sqlDa.InsertCommand = cmdBuilder.GetInsertCommand();
            //sqlDa.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
            insertCmd.Parameters.Add(new SqlParameter(){ParameterName="@p1", SourceColumn = "AddTime" }); //参数名称@p1
            insertCmd.Parameters.Add(new SqlParameter() { ParameterName = "@p2", SourceColumn = "AddEmpNo" });
            insertCmd.Parameters.Add(new SqlParameter() { ParameterName = "@p3", SourceColumn = "Name" });
            insertCmd.Parameters.Add(new SqlParameter() { ParameterName = "@p4", SourceColumn = "Img" });
            sqlDa.InsertCommand = insertCmd;
            sqlDa.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;

            var updateSQL = sqlDa.UpdateCommand.CommandText;
            var insertSQL = sqlDa.InsertCommand.CommandText;
            var delSQL = sqlDa.DeleteCommand.CommandText;
            sqlDa.Update(ds);
            dataGridView1.AutoGenerateColumns = true;
            bindingSource1.DataSource = ds;
            bindingSource1.DataMember = "Table";
            Console.WriteLine("操作完成!");
        }
    }
}