也许有人知道我可以检查什么:
using (var cnn = new SqlConnection(connection))
{
cnn.Open();
using (var cmd = new SqlCommand())
{
cmd.Connection = cnn;
cmd.CommandText = "IF EXISTS (SELECT * FROM kruserprofile WHERE lactorid=@1 AND strname=@2) " +
" UPDATE kruserprofile SET txtvalue=@3 WHERE lactorid=@1 AND strname=@2 " +
"ELSE " +
" INSERT INTO kruserprofile (lactorid, strname, txtvalue) " +
" VALUES (@1, @2, @3)";
cmd.CommandTimeout = 120;
cmd.Parameters.AddWithValue("@1", actorId);
cmd.Parameters.AddWithValue("@2", ident);
cmd.Parameters.AddWithValue("@3", _mXml.ToString());
cmd.ExecuteNonQuery();
}
cnn.Close();
}
正如您在这段代码中看到的,我检查记录是否存在并更新或插入。这在SQL Server系统上工作得很好。在Azure SQL上,我有时会这样做,但不能重现错误:Violation on PRIMARY KEY...
,其中约束是在lactorid
和strname
上。
表:
CREATE TABLE dbo.kruserprofile (
lactorid int NOT NULL,
strname nvarchar(254) NOT NULL,
txtvalue nvarchar(max) NULL
);
GO
ALTER TABLE dbo.kruserprofile ADD CONSTRAINT PK__kruserpr__6E092EE804688C07 PRIMARY KEY (lactorid, strname);
GO
所以我没有找到任何原因,但有时似乎EXISTS
返回false,所以它试图插入,然后失败。知道我能查什么吗?
update:
Solution1:
我们在这里也找到了解决方案。
Solution2:
我创建了一个关于在Azure SQL中使用MERGE的测试。
- 创建表:
CREATE TABLE dbo.kruserprofile (
lactorid int NOT NULL,
strname nvarchar(254) NOT NULL,
txtvalue nvarchar(max) NULL
);
GO
ALTER TABLE dbo.kruserprofile ADD CONSTRAINT PK__kruserpr__6E092EE804688C07 PRIMARY KEY (lactorid, strname);
GO
- 创建一个名为
dbo.kruserprofile_type
的表值参数,它将用于我的存储过程:
create TYPE dbo.kruserprofile_type AS TABLE(
lactorid int NOT NULL,
strname nvarchar(254) NOT NULL,
txtvalue nvarchar(max)
)
GO
- 创建一个存储过程,它将合并相同的记录,并根据主键插入新的记录:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[spUpsertKruserprofile]
@profile dbo.kruserprofile_type READONLY
AS
BEGIN
MERGE dbo.kruserprofile AS target_sqldb
USING @profile AS source_tblstg
ON (target_sqldb.lactorid = source_tblstg.lactorid and target_sqldb.strname = source_tblstg.strname )
WHEN MATCHED THEN
UPDATE SET
txtvalue = source_tblstg.txtvalue
WHEN NOT MATCHED THEN
INSERT (
lactorid,
strname,
txtvalue
)
VALUES (
source_tblstg.lactorid,
source_tblstg.strname,
source_tblstg.txtvalue
);
END
GO
- 之后,我们可以通过以下代码执行存储过程:
DECLARE @profileVar AS dbo.kruserprofile_type;
/* Add data to the table variable. */
INSERT INTO @profileVar (lactorid, strname, txtvalue) values (1, 'tom','wednesday');
exec [dbo].[spUpsertKruserprofile] @profileVar
。