可以用 Merge

public class Department
{
	public int Id { get; set; }
	public string Name { get; set; }
}

void Main()
{
	using (var conn = this.Connection as SqlConnection)
	{
		conn.Open();
	
		var departmentList = new List<Department>
		{
			new Department { Id = 1, Name = "IT"},      //Update
			new Department { Id = 2, Name = "Salse"},   //Update
			new Department { Id = 3, Name = "xxx"},     //Insert
		};
		
		int insnum = 0;  
		int updnum = 0;	
		int errnum = 0; 
		
		var sql = @"MERGE INTO Department AS target
					USING (
					    VALUES (@Id, @Name)  
					) AS source (Id, Name)
					ON (target.Id = source.Id)
						WHEN MATCHED THEN
					    UPDATE SET Name = source.Name
					WHEN NOT MATCHED THEN   
                        INSERT (Id, Name)
					    VALUES (source.Id, source.Name)
					OUTPUT $action;";
					
		foreach (var item in departmentList)
		{
			try
			{
				var cmd = new SqlCommand(sql, conn);
				cmd.Parameters.Add("@Id", SqlDbType.Int).Value = item.Id;
				cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 255).Value = item.Name;
				
				var action = cmd.ExecuteScalar().ToString();
				
				if (action == "INSERT")
					insnum++;	
				if (action == "UPDATE")
					updnum++;
			}
			catch (Exception)
			{
				errnum++;
			}
		}
		
		Console.WriteLine("新增 {0} 筆，更新 {1} 筆。", insnum, updnum);
	}
}

既然是 2005 那就只能回歸 IF ELSE。

public class Department
{
	public int Id { get; set; }
	public string Name { get; set; }
}

void Main()
{
	using (var conn = this.Connection as SqlConnection)
	{
		conn.Open();

		var departmentList = new List<Department>
		{
			new Department { Id = 1, Name = "IT"},      //Update
			new Department { Id = 2, Name = "Salse"},   //Update
			new Department { Id = 3, Name = "xxx"},     //Insert
		};

		int insnum = 0;  
		int updnum = 0;	
		int errnum = 0; 

		var sql = @"IF NOT EXISTS(SELECT 1 FROM Department WHERE Id=@Id)
				    BEGIN
						INSERT INTO Department(Id, Name)
						VALUES(@Id, @Name)
						SELECT 'INSERT'
					END
					ELSE
					BEGIN
						UPDATE Department SET Name=@Name WHERE Id=@Id
						SELECT 'UPDATE'
					END";

		foreach (var item in departmentList)
		{
			try
			{
				var cmd = new SqlCommand(sql, conn);
				cmd.Parameters.Add("@Id", SqlDbType.Int).Value = item.Id;
				cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 255).Value = item.Name;

				var action = cmd.ExecuteScalar().ToString();

				if (action == "INSERT")
					insnum++;	
				if (action == "UPDATE")
					updnum++;
			}
			catch (Exception)
			{
				errnum++;
			}
		}

		Console.WriteLine("新增 {0} 筆，更新 {1} 筆。", insnum, updnum);
	}
}

ExecuteNonQuery本身就會返回新增或是更新的資料筆數
如圖片

測試的Code:

	using (var conn=this.Connection as SqlConnection)
	{
		conn.Open();

		//先建立測試資料，紀錄比數
		new SqlCommand(@"
			CREATE TABLE #AffectedCountTable
				([type] varchar(6), [affectedCount] int)
			;
		", conn).ExecuteNonQuery();

		//測試新增次數
		var insertCount = new SqlCommand(@"
			select * into #TempTable from (
				select 'IT邦你好1' text union all
				select 'IT邦你好2' text 
			) T
		", conn).ExecuteNonQuery();

		//測試更新次數
		var updateCount = new SqlCommand(@"
			update #TempTable set [text] = 'IT邦你好3' where [text] = 'IT邦你好1'
		", conn).ExecuteNonQuery();

		//儲存新增更新次數到表格
		new SqlCommand($@"
INSERT INTO #AffectedCountTable
	([type], [affectedCount])
VALUES
	('insert', {insertCount}),
	('update', {updateCount})
;
		", conn).ExecuteNonQuery();
		
		//檢驗
		conn.Query(@"select * from #AffectedCountTable").Dump();
		Console.WriteLine($"總共新增 {insertCount} 筆資料 / 更新 {updateCount} 筆資料");
	}

但我是insert及update寫在一起呢! 請問這樣要怎麼分筆數呢??

使用@@ROWCOUNT來記錄新增or更新筆數

	using (var conn = this.Connection as SqlConnection)
	{
		conn.Open();

		new SqlCommand(@"
		
			--先建立測試資料，紀錄比數
			CREATE TABLE #AffectedCountTable
				([type] varchar(6), [affectedCount] int)
			;
			
			
			--測試新增次數
			select * into #TempTable from (
				select 'IT邦你好1' text union all
				select 'IT邦你好2' text 
			) T;
			
			declare @insertCount int = @@ROWCOUNT;
			
			--測試更新次數
			update #TempTable set [text] = 'IT邦你好3' where [text] = 'IT邦你好1';
			
			declare @updateCount int = @@ROWCOUNT;
			
			--儲存新增更新次數到表格
			INSERT INTO #AffectedCountTable
				([type], [affectedCount])
			VALUES
				('insert', @insertCount),
				('update', @updateCount)
			;			
		", conn).ExecuteNonQuery();


		//檢驗
		conn.Query("select [type],sum([affectedCount]) affectedCount from #AffectedCountTable group by [type]").ToList().ForEach(
			result =>
			{
				$"總共 {result.type} {result.affectedCount} 筆資料 ".Dump();
			}
		);
	}