我写了一个存储过程,其中前面基本上是我照抄别人的存储过程,如下:Create ProCedure Yl_Sp_PackageAction
AS
set xact_abort on
set nocount on
set transaction isolation level repeatable read
declare @intTranCount int
set @intTranCount=@@TranCount
if @intTranCount<> 0 save transaction Savepoint else begin transaction 后面结尾我自己修改了一下,如下:if @intTranCount<>@@TranCount
Begin --2
commit transaction
return (1)
End --2
Else if @intTranCount=@@TranCount
begin --3
rollback transaction Savepoint
return(0)
end --3
else
begin --4
rollback transaction
return(0)
end --4存储过程的内容我就省略了,是可以顺利执行的。问题是我在C#2005的程序中执行这个过程,它总是失败。(就是达不到我要的结果),可是我把C#里(拼接的)执行SQL的字符串拷出来在查询分析器中执行是成功的。我很迷惑。希望大家能给我帮助。
AS
set xact_abort on
set nocount on
set transaction isolation level repeatable read
declare @intTranCount int
set @intTranCount=@@TranCount
if @intTranCount<> 0 save transaction Savepoint else begin transaction 后面结尾我自己修改了一下,如下:if @intTranCount<>@@TranCount
Begin --2
commit transaction
return (1)
End --2
Else if @intTranCount=@@TranCount
begin --3
rollback transaction Savepoint
return(0)
end --3
else
begin --4
rollback transaction
return(0)
end --4存储过程的内容我就省略了,是可以顺利执行的。问题是我在C#2005的程序中执行这个过程,它总是失败。(就是达不到我要的结果),可是我把C#里(拼接的)执行SQL的字符串拷出来在查询分析器中执行是成功的。我很迷惑。希望大家能给我帮助。
功能描述:包装物日耗表单操作
编制人 :韩文勇 编制日期 :2010-05-28
修改人 : 修改日期 :
*/Create ProCedure Yl_Sp_PackageAction
@PackageYlMainID int=0, --单据ID
@Option int=0, --操作选项(1保存、2删除、3审核、4弃审)
@Checker char(10)=null, --审核人
@CheckDate datetime=null --审核日期
AS
set xact_abort on
set nocount on
set transaction isolation level repeatable read if @Option not in (1,2,3,4)
begin
raiserror('操作参数不正确!',16,1)
return(0)
end declare @intTranCount int
set @intTranCount=@@TranCount
if @intTranCount<> 0 save transaction Savepoint else begin transaction
if @Option=1 --保存
begin
declare
@NumP1 decimal(20,6), --P1 数量
@NumP2 decimal(20,6), --P2 数量
@NumP3 decimal(20,6), --P3 数量
@NumP4 decimal(20,6), --P4 数量
@NumP5 decimal(20,6), --P5 数量
@NumP6 decimal(20,6), --P6 数量
@NumP7 decimal(20,6), --P7 数量
@NumP8 decimal(20,6), --P8 数量
@NumP9 decimal(20,6), --P9 数量
@NumPA decimal(20,6), --PA 数量
@NumPB decimal(20,6), --PB 数量
@NumPC decimal(20,6) --PC 数量 Select @NumP1=sum(SNumP1),@NumP2=sum(SNumP2),@NumP3=sum(SNumP3),@NumP4=sum(SNumP4),@NumP5=sum(SNumP5),@NumP6=sum(SNumP6),
@NumP7=sum(SNumP7),@NumP8=sum(SNumP8),@NumP9=sum(SNumP9),@NumPA=sum(SNumPA),@NumPB=sum(SNumPB),@NumPC=sum(SNumPC)
From Yl_PackageSub Where PackageYlMainID=@PackageYlMainID Update Yl_Package Set NumP1=@NumP1,NumP2=@NumP2,NumP3=@NumP3,NumP4=@NumP4,NumP5=@NumP5,NumP6=@NumP6,NumP7=@NumP7,NumP8=@NumP8,NumP9=@NumP9,NumPA=@NumPA,NumPB=@NumPB,NumPC=@NumPC
Where PackageYlMainID=@PackageYlMainID
end if @Option=2 --删除
begin
--删除单据
Delete From Yl_PackageSub Where PackageYlMainID=@PackageYlMainID
end if @Option=3 --审核
begin
update Yl_Package
set Checker =@Checker,
CheckDate =isnull(@CheckDate,getdate())
where (PackageYlMainID=@PackageYlMainID)
end if @Option=4 --弃审
begin
update Yl_Package
set Checker =null,
CheckDate =null
where (PackageYlMainID=@PackageYlMainID)
end --完成
if @intTranCount<>@@TranCount
Begin --2
commit transaction
return (1)
End --2
Else if @intTranCount=@@TranCount
begin --3
rollback transaction Savepoint
return(0)
end --3
else
begin --4
rollback transaction
return(0)
end --4
GO我在C#里执行的时候是通过这几句执行的:drDataAccessSub = dtDataAccessSub.NewRow();
drDataAccessSub["CommandText"] = tmpCommandText;
dtDataAccessSub.Rows.Add(drDataAccessSub);
//
//访问数据层
//
AccessSqlCommand tmpAccessSqlCommand = new AccessSqlCommand(tmpDataAccessParameter); if (tmpAccessSqlCommand.Execute(out OutDataSet))
{
return true;
}
else
{
return false;
}
}
catch (Exception e)
{
OutDataSet = new DataSet();
new AddExceptionTable(OutDataSet, "", e.ToString());
return false;
}
}其中我的tmpCommandText是下面的字符串:
EXEC YL_SP_PACKAGEACTION 1,2
UPDATE YL_PACKAGE
SET BILLDATE ='2010-05-14',
SHIFTCODE = '2',
REMARK ='111',
MAKER ='000',
MAKEDATE ='2010-06-01',
PRICEP1 ='125.0000',
PRICEP2 ='129.5000',
PRICEP3 ='83.3000',
PRICEP4 ='2.8000',
PRICEP5 ='3.5000',
PRICEP6 ='16.0000',
PRICEP7 ='28.0000',
PRICEP8 ='23.0000',
PRICEP9 ='0.4000',
PRICEPA ='6.4000',
PRICEPB ='0.8700',
PRICEPC ='0.0000',
ROWID ='6',
BEGINDATETIME ='2010-05-14',
ENDDATETIME ='2010-05-15'
WHERE PACKAGEYLMAINID =1
INSERT INTO YL_PACKAGESUB (PACKAGEYLMAINID,PACKAGEYLSUBID,PRODUCTCODE,REMARKSUB,SNUMP1,SNUMP2,SNUMP3,SNUMP4,SNUMP5,SNUMP6,SNUMP7,SNUMP8,SNUMP9,SNUMPA,SNUMPB,SNUMPC)
VALUES (1 ,1,'P1','','10','10','50','40','20','15','30','10','0','0','0','0')
EXEC YL_SP_PACKAGEACTION 1,1但是C#程序执行时总是返回False,而我把这一句放在查询分析器中却是正确的。C#中返回的错误是:违反了 PRIMARY KEY 约束 'PK_Yl_PackageSub'。不能在对象 'Yl_PackageSub' 中插入重复键。
语句已终止。 EXEC YL_SP_PACKAGEACTION 1,2
UPDATE YL_PACKAGE
SET BILLDATE ='2010-05-14',
SHIFTCODE = '2',
REMARK ='111',
MAKER ='000',
MAKEDATE ='2010-06-01',
PRICEP1 ='125.0000',
PRICEP2 ='129.5000',
PRICEP3 ='83.3000',
PRICEP4 ='2.8000',
PRICEP5 ='3.5000',
PRICEP6 ='16.0000',
PRICEP7 ='28.0000',
PRICEP8 ='23.0000',
PRICEP9 ='0.4000',
PRICEPA ='6.4000',
PRICEPB ='0.8700',
PRICEPC ='0.0000',
ROWID ='6',
BEGINDATETIME ='2010-05-14',
ENDDATETIME ='2010-05-15'
WHERE PACKAGEYLMAINID =1
INSERT INTO YL_PACKAGESUB (PACKAGEYLMAINID,PACKAGEYLSUBID,PRODUCTCODE,REMARKSUB,SNUMP1,SNUMP2,SNUMP3,SNUMP4,SNUMP5,SNUMP6,SNUMP7,SNUMP8,SNUMP9,SNUMPA,SNUMPB,SNUMPC)
VALUES (1 ,1,'P1','','10','10','50','40','20','15','30','10','0','0','0','0')
EXEC YL_SP_PACKAGEACTION 1,1
上面的 EXEC YL_SP_PACKAGEACTION 1,2 就是删除子表的数据,后面再有Insert 子表,可是在程序中跑老是删除失败,致使insert失败。可是那句我放在查询分析器中执行是正确的,能得到正确的结果。我是去年才开始学习C#,去年才开始比较多地接触SQL,其中有很多东西不是很明白。
功能描述:包装物日耗表单操作
编制人 :韩文勇 编制日期 :2010-05-28
修改人 : 修改日期 :
*/CREATE PROCEDURE Yl_Sp_PackageAction
@PackageYlMainID INT=0, --单据ID
@Option INT=0, --操作选项(1保存、2删除、3审核、4弃审)
@Checker CHAR(10)=NULL, --审核人
@CheckDate DATETIME=NULL --审核日期
AS
SET NOCOUNT ON
IF @Option NOT IN (1, 2, 3, 4)
BEGIN
RAISERROR('操作参数不正确!', 16, 1)
RETURN(0)
ENDBEGIN TRAN
IF @Option=1 --保存
BEGIN
DECLARE @NumP1 DECIMAL(20, 6), --P1 数量
@NumP2 DECIMAL(20, 6), --P2 数量
@NumP3 DECIMAL(20, 6), --P3 数量
@NumP4 DECIMAL(20, 6), --P4 数量
@NumP5 DECIMAL(20, 6), --P5 数量
@NumP6 DECIMAL(20, 6), --P6 数量
@NumP7 DECIMAL(20, 6), --P7 数量
@NumP8 DECIMAL(20, 6), --P8 数量
@NumP9 DECIMAL(20, 6), --P9 数量
@NumPA DECIMAL(20, 6), --PA 数量
@NumPB DECIMAL(20, 6), --PB 数量
@NumPC DECIMAL(20, 6) --PC 数量
SELECT @NumP1 = SUM(SNumP1), @NumP2 = SUM(SNumP2), @NumP3 = SUM(SNumP3), @NumP4 = SUM(SNumP4),
@NumP5 = SUM(SNumP5), @NumP6 = SUM(SNumP6), @NumP7 = SUM(SNumP7), @NumP8 = SUM(SNumP8),
@NumP9 = SUM(SNumP9), @NumPA = SUM(SNumPA), @NumPB = SUM(SNumPB), @NumPC = SUM(SNumPC)
FROM Yl_PackageSub
WHERE PackageYlMainID = @PackageYlMainID
UPDATE Yl_Package
SET NumP1 = @NumP1, NumP2 = @NumP2, NumP3 = @NumP3, NumP4 = @NumP4, NumP5 = @NumP5, NumP6 =
@NumP6, NumP7 = @NumP7, NumP8 = @NumP8, NumP9 = @NumP9, NumPA = @NumPA, NumPB = @NumPB,
NumPC = @NumPC
WHERE PackageYlMainID = @PackageYlMainID
END
ELSE IF @Option=2 --删除
BEGIN
--删除单据
DELETE
FROM Yl_PackageSub
WHERE PackageYlMainID = @PackageYlMainID
END
ELSE IF @Option=3 --审核
BEGIN
UPDATE Yl_Package
SET Checker = @Checker, CheckDate = ISNULL(@CheckDate, GETDATE())
WHERE (PackageYlMainID=@PackageYlMainID)
END
ELSE--IF @Option=4 --弃审
BEGIN
UPDATE Yl_Package
SET Checker = NULL, CheckDate = NULL
WHERE (PackageYlMainID=@PackageYlMainID)
END
COMMIT
GO因你整个过程中只有执行一个动作(保存,删除,审核或弃审),故不用考虑语句中的事务,每个语句本身就是一个事务,只需加个事务控制并发,你把过程改为这样试试。
有关事务与锁定,参考石头兄博客:http://blog.csdn.net/happyflystone/category/348844.aspx