各位前輩好,新手小弟又來詢問問題了,如下:
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新增進資料庫
}
}
可以用 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);
}
}
MERGE
精簡很多代碼
第一次看到MERGE
哈~ 正當我想要研究MERGE要怎麼使用的時候,發現此語法只適用2008以上,馬上查看公司的SQL Server結果卻只到2005,真是殘念。
哈哈哈,竟然是 2005
ExecuteNonQuery
本身就會返回新增或是更新的資料筆數
如圖片
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();
}
);
}
原來ExecuteNonQuery()會回傳筆數啊!
以前都沒注意過
但我是insert及update寫在一起呢! 請問這樣要怎麼分筆數呢??
我更新了 b7307024
所以我語法改成這樣正確嗎?
IF NOT EXISTS()
BEGIN
INSERT INTO test()
VALUES()
declare @insertCount int = @@ROWCOUNT;
END
ELSE
BEGIN
UPDATE test
SET
WHERE
declare @updateCount int = @@ROWCOUNT;
END
且這樣的用法,是不是還要在資料庫新增一個資料表,來記錄新增及更新筆數呢?
改成這樣
--先建立測試資料,紀錄比數
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
請問#AffectedCountTable這個是暫存的嗎? 程式執行完畢就會消失?
是的 要換成你自己的表格
暐翰大抱歉,由於您的解答比較複雜一點,所以只好選fysh711426大的答案為最佳解答,也非常感謝您的協助。