SqlServer SqlDataAdapter 用例

SqlDataAdapter 可以实现 UpdateOrInsert

public void UpdateOrInsertLocStatus(List<RealTimeNearbyLocationCard> status)
{
    var dt = new DataTable();
    using (SqlConnection conn = new SqlConnection(MemCache.mjkqSqlconnstring))
    {
        //添加事务
        conn.Open();
        SqlTransaction myTrans = conn.BeginTransaction();
        using (var adapter = new SqlDataAdapter(@"SELECT cardnum, AttendanceWorksiteNum,Rssi,cardtype,updatetime FROM RealTimeNearbyLocationCard", conn))
        {
            adapter.SelectCommand.Transaction = myTrans;
            adapter.Fill(dt);

            foreach (var state in status)
            {
                // 联合主键
                var rows = dt.Select(string.Format("cardnum = '{0}' and cardtype = '{1}' ", state.CardNum, state.CardType));   // 查询主键记录
                //更新
                if (rows.Length > 0)
                {
                    foreach (var row in rows)
                    {
                        row["updatetime"] = state.UpdateTime;
                    }
                }
                else
                {
                    //插入
                    var drNew = dt.NewRow();
                    drNew["cardnum"] = state.CardNum;
                    drNew["AttendanceWorksiteNum"] = state.AttendanceWorksiteNum;
                    drNew["Rssi"] = state.Rssi;
                    drNew["cardtype"] = state.CardType;
                    drNew["updatetime"] = state.UpdateTime;
                    dt.Rows.Add(drNew);
                }
            }

            if (dt.GetChanges() == null)
            {
                return;
            }

            //添加指定命令
            var scb = new SqlCommandBuilder(adapter);
            adapter.InsertCommand = scb.GetInsertCommand();
            adapter.InsertCommand.Transaction = myTrans;

            //Update命令
            adapter.UpdateCommand = scb.GetUpdateCommand();
            adapter.UpdateCommand.Transaction = myTrans;

            try
            {
                var inserts = dt.GetChanges(DataRowState.Added);
                if (inserts != null)
                {
                    adapter.Update(inserts);
                }
                // 控制回写；防止主键冲突
                var updates = dt.GetChanges(DataRowState.Modified);
                if (updates != null)
                {
                    adapter.Update(updates);
                }

                dt.AcceptChanges();     // 保存更新；不影响
                myTrans.Commit();       // 提交事务
            }
            catch (Exception ex)
            {
                Logs.LogException(ex);
                myTrans.Rollback(); // 回滚事务
                return;
            }
        }   // end of adapter
    } // end of conn
}

-ref-
微软文档 SqlDataAdapter Class
SqlDataAdapter.Update 批量数据更新