iT邦幫忙

0

T-SQL如何計算新增、修改筆數,並回傳給C#,去新增資料到另一資料表

各位前輩好,新手小弟又來詢問問題了,如下:

DataTable dt = new DataTable();

using (OracleConnection oracn = new OracleConnection())
{
	//部分語法省略
}

using (SqlConnection sqlcn = new SqlConnection())
{
	int insnum = 0;  //新增資料筆數
	int updnum = 0;	 //異動資料筆數
	int errnum = 0;  //錯誤資料筆數

	for (int i = 0; i <= dt.Rows.Count - 1; i++)
	{
		try
		{
			string strSQL = " IF NOT EXISTS()" +
							" BEGIN" +
							" INSERT INTO test()" +
							" VALUES()" +
							" END " +
							" ELSE " +
							" BEGIN" +
							" UPDATE test " +
							" SET " +
							" WHERE " +
							" END";
							/*請問這樣的T-SQL語法可以計算出Insert幾筆、Update幾筆資料嗎?
							還是說我需要分兩個tr-catch語法,分別處理Insert及Update呢??
							若是要分別處理的話,我cmd.Parameters的部分就要寫兩次。*/

			SqlCommand cmd = new SqlCommand(strSQL, sqlcn);
			cmd.Parameters.Add(new SqlParameter("@DATECREATED", SqlDbType.VarChar));
			cmd.Parameters["@DATECREATED"].Value = dt.Rows[i]["DATECREATED"].ToString();
			//部分語法省略
			
			cmd.ExecuteNonQuery();
		}
		catch (Exception e)
		{
			
		}
		
		//新增轉檔紀錄
		將insnum、updnum、errnum新增進資料庫
	}
}

2 個回答

4
fysh711426
iT邦研究生 4 級 ‧ 2018-06-22 00:35:26
最佳解答

可以用 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/images/emoticon/emoticon02.gif

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);
	}
}
看更多先前的回應...收起先前的回應...
暐翰 iT邦大師 3 級 ‧ 2018-06-22 08:25:28 檢舉

/images/emoticon/emoticon12.gif MERGE精簡很多代碼

第一次看到MERGE/images/emoticon/emoticon13.gif

優悠 iT邦新手 4 級 ‧ 2018-06-22 09:42:21 檢舉

/images/emoticon/emoticon37.gif

b7307024 iT邦新手 4 級 ‧ 2018-06-22 15:54:19 檢舉

哈~ 正當我想要研究MERGE要怎麼使用的時候,發現此語法只適用2008以上,馬上查看公司的SQL Server結果卻只到2005,真是殘念。

fysh711426 iT邦研究生 4 級 ‧ 2018-06-22 21:26:50 檢舉

哈哈哈,竟然是 2005 /images/emoticon/emoticon70.gif

fysh711426 iT邦研究生 4 級 ‧ 2018-06-22 22:07:42 檢舉

暐翰 神Q超人 優悠
哈哈哈,MERGE 在做 無則新增、有則更新 時很好用,
我常用在 Excel 匯入功能上。

b7307024 iT邦新手 4 級 ‧ 2018-06-25 09:23:58 檢舉

fysh711426
您的這個SELECT 'INSERT' 及 SELECT 'UPDATE'真是淺顯易懂,我測試可以正常運行,要給你一個讚的啦!/images/emoticon/emoticon41.gif

fysh711426 iT邦研究生 4 級 ‧ 2018-06-26 00:01:05 檢舉

很高興能幫助到您。 /images/emoticon/emoticon37.gif

1
暐翰
iT邦大師 3 級 ‧ 2018-06-21 17:32:29

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();
			}
		);
	}

看更多先前的回應...收起先前的回應...
Homura iT邦高手 3 級 ‧ 2018-06-21 17:59:01 檢舉

原來ExecuteNonQuery()會回傳筆數啊!
以前都沒注意過

b7307024 iT邦新手 4 級 ‧ 2018-06-21 19:49:03 檢舉

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

暐翰 iT邦大師 3 級 ‧ 2018-06-22 08:19:28 檢舉

我更新了 b7307024

b7307024 iT邦新手 4 級 ‧ 2018-06-22 16:27:06 檢舉

所以我語法改成這樣正確嗎?

IF NOT EXISTS()
BEGIN
INSERT INTO test()
VALUES()
declare @insertCount int = @@ROWCOUNT;
END
ELSE
BEGIN
UPDATE test 
SET
WHERE
declare @updateCount int = @@ROWCOUNT;
END

且這樣的用法,是不是還要在資料庫新增一個資料表,來記錄新增及更新筆數呢?

暐翰 iT邦大師 3 級 ‧ 2018-06-22 17:17:38 檢舉

暐翰 iT邦大師 3 級 ‧ 2018-06-22 17:17:48 檢舉

改成這樣

			--先建立測試資料,紀錄比數
			CREATE TABLE #AffectedCountTable
				([type] varchar(6), [affectedCount] int)
			;

			--測試新增次數
			select * into #TempTable from (
				select 1 id,N'IT邦你好1' text union all
				select 2 id ,N'IT邦你好2' text 
			) T;

			--測試
			declare @id int = 3,@text nvarchar(100) = N'IT邦你好3';
			declare @updateCount int = 0;
			declare @insertCount int = 0;
			if  NOT EXISTS (select 1 from #TempTable where id = @id)
			begin
				insert into #TempTable (id,text) values (@id,@text);
				INSERT INTO #AffectedCountTable([type], [affectedCount])VALUES('insert', @@ROWCOUNT);
				end
			else begin
				update #TempTable set text = @text where id = @id;
				INSERT INTO #AffectedCountTable([type], [affectedCount])VALUES('update', @@ROWCOUNT);
			end

			--驗證
			select * from #TempTable
b7307024 iT邦新手 4 級 ‧ 2018-06-22 17:42:24 檢舉

請問#AffectedCountTable這個是暫存的嗎? 程式執行完畢就會消失?

暐翰 iT邦大師 3 級 ‧ 2018-06-22 19:37:21 檢舉

是的 要換成你自己的表格

b7307024 iT邦新手 4 級 ‧ 2018-06-25 13:33:37 檢舉

暐翰大抱歉,由於您的解答比較複雜一點,所以只好選fysh711426大的答案為最佳解答,也非常感謝您的協助。

我要發表回答

立即登入回答