如何从存储过程获取字符串值到C#? [英] How do I get string value from stored procedure to C#?
本文介绍了如何从存储过程获取字符串值到C#?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
您好我正在使用存储过程
ALTER PROCEDURE [dbo]。[SP_InsTicketSale]
(
@ TktDate date , @ TktId nvarchar ( 50 ), @ Name nvarchar (< span class =code-digit> 50 ), @ Email nvarchar ( 50 ), @ Mobile varchar ( 15 ), @TktStatus nvarchar ( 50 )
,
@ EmpId nvar char ( 50 ), @ TblLoc_ID varchar ( 3 ), @ Promoid nvarchar ( 50 ), @ TaxId nvarchar ( 50 ), @Grandtotal decimal ( 18 , 2 ), @ subtotal 十进制( 18 , 2 ),
@ Details ITEMDATA1 Readonly,
@ id nvarchar ( 5 )= NULL <跨度class =code-keyword> OUTPUT
)
AS
BEGIN
如果 不 存在(选择 * 来自 Tbl_TicketSale 其中 TktId = @ TktId )
开始
声明 @ IdTmp varchar ( 5 )
set @ IdTmp = 00000
选择 @ IdTmp = count(*)来自 Tbl_TicketSale
设置 @ IdTmp = @ IdTmp + 1
set @TktId = 正确((' 00000' + @ IdTmp ), 5 )
开始 交易
INSERT INTO Tbl_Client(TktDate,TktId,Name,电子邮件,移动)
VALUES ( @TktDate , @TktId , @ Name , @ Email , @Mobile )
IF ( @@ ERROR <> 0 ) GOTO ERR_HANDLER
INSERT INTO Tbl_TicketSale(TktId,EmpId,TktDate,TblLoc_ID,TaxId,Grandtotal,subtotal,TktStatus)
VALUES ( @ TktId , @ EmpId , @TktDate , @ TblLoc_ID , @ TaxId , @Grandtotal , @ subtotal ,' 已交付')
IF ( @@ ERROR <> 0 ) GOTO ERR_HANDLER
- Temp T能够
INSERT INTO Tbl_TicketSaleChild(TktId,ActivityId,Qty,PoxtypeAC,ActPrice ,Amt,TktSale_childdate)
选择 TktId = @ TktId,tActivityId,tQty,tPoxtypeAC,tActPrice,tAmt,tTktSale_childdate 来自 @ Details ;
IF ( @@ ERROR <> 0 ) GOTO ERR_HANDLER
结束
提交 交易
- RETURN 0
返回 @TktId
- select @TktId
ERR_HANDLER:
PRINT ' 发生意外错误!'
ROLLBACK 交易
RETURN 1
END 跨度>
和C#我正在使用
// public int InsetTicketSale()
public void InsetTicketSale()
{
int i = 0 ;
尝试
{
SqlDBHelper SqlDb = new SqlDBHelper();
SqlDb.CreateParameters( @ TktDate,SqlDbType.Date,TktDate);
SqlDb.CreateParameters( @ TktId,SqlDbType.VarChar,TktId);
SqlDb.CreateParameters( @ Name,SqlDbType.VarChar,Name);
SqlDb.CreateParameters( @ Email,SqlDbType.VarChar,Email);
SqlDb.CreateParameters( @ Mobile,SqlDbType.VarChar,Mobile);
SqlDb.CreateParameters( @ EmpId,SqlDbType.VarChar,EmpId);
SqlDb.CreateParameters( @ TblLoc_ID,SqlDbType.VarChar,TblLoc_ID);
SqlDb.CreateParameters( @ Promoid,SqlDbType.VarChar,Promoid);
SqlDb.CreateParameters( @ TaxId,SqlDbType.VarChar,TaxId);
SqlDb.CreateParameters( @ Grandtotal,SqlDbType.Decimal,Grandtotal);
SqlDb.CreateParameters( @ subtotal,SqlDbType.Decimal,subtotal);
SqlDb.CreateParameters( @ Details,SqlDbType.Structured,Details);
SqlDb.CreateParameters( @ Servtax,SqlDbType.Decimal,Servtax);
SqlDb.CreateParameters( @ vat,SqlDbType.Decimal,vat);
SqlDb.CreateParameters( @ Tax3,SqlDbType.Decimal,Tax3);
SqlDb.CreateParameters( @ Servtaxp,SqlDbType.Decimal,Servtaxp);
SqlDb.CreateParameters( @ vatp,SqlDbType.Decimal,vatp);
SqlDb.CreateParameters( @ Tax3p,SqlDbType.Decimal,Tax3p);
SqlDb.CreateParameters( @ TktStatus,SqlDbType.VarChar,TktStatus);
SqlDb.ProcedureName = [SP_InsTicketSale];
i = Convert.ToInt32(SqlDb.ExecuteNonQueryReturn());
}
catch (Exception Excep)
{
throw new ApplicationException(Excep.Message);
}
// 返回i;
}
我尝试了什么:
$ C $ b in C#我已经尝试过int type和executenonquery()它已经完成了事务的计数,
i变为null,因为当使用字符串和执行标量组合时
解决方案
< blockquote> ExecuteNonQuery()将返回受影响的记录数。这将是一个数字。
ExecureScalar()将返回第一行的第一列。所以你应该使用ExecureScalar()。
从 ExecuteReader ExecuteScalar和ExecuteNonQuery之间的区别 [ ^ ]
Hi i am using Stored Procedure
ALTER PROCEDURE [dbo].[SP_InsTicketSale]
(
@TktDate date, @TktId nvarchar(50),@Name nvarchar(50), @Email nvarchar(50), @Mobile varchar(15),@TktStatus nvarchar(50)
,
@EmpId nvarchar(50),@TblLoc_ID varchar(3), @Promoid nvarchar(50), @TaxId nvarchar(50), @Grandtotal decimal(18,2),@subtotal decimal(18,2),
@Details ITEMDATA1 Readonly,
@id nvarchar(5) = NULL OUTPUT
)
AS
BEGIN
if not exists(select * from Tbl_TicketSale where TktId= @TktId)
Begin
declare @IdTmp varchar(5)
set @IdTmp = 00000
select @IdTmp = count(*) from Tbl_TicketSale
set @IdTmp = @IdTmp + 1
set @TktId= Right(('00000' + @IdTmp),5)
Begin Transaction
INSERT INTO Tbl_Client(TktDate, TktId, Name, Email, Mobile)
VALUES (@TktDate, @TktId,@Name, @Email, @Mobile)
IF (@@ERROR <> 0) GOTO ERR_HANDLER
INSERT INTO Tbl_TicketSale (TktId, EmpId, TktDate, TblLoc_ID, TaxId, Grandtotal,subtotal,TktStatus)
VALUES (@TktId , @EmpId, @TktDate, @TblLoc_ID, @TaxId , @Grandtotal ,@subtotal,'Delivered')
IF (@@ERROR <> 0) GOTO ERR_HANDLER
--Temp Table
INSERT INTO Tbl_TicketSaleChild (TktId, ActivityId, Qty, PoxtypeAC, ActPrice, Amt, TktSale_childdate)
Select TktId=@TktId, tActivityId, tQty, tPoxtypeAC, tActPrice, tAmt, tTktSale_childdate from @Details ;
IF (@@ERROR <> 0) GOTO ERR_HANDLER
End
Commit Transaction
--RETURN 0
Return @TktId
--select @TktId
ERR_HANDLER:
PRINT 'Unexpected error occurred!'
ROLLBACK Transaction
RETURN 1
END
and C# i am using
//public int InsetTicketSale()
public void InsetTicketSale()
{
int i = 0;
try
{
SqlDBHelper SqlDb = new SqlDBHelper();
SqlDb.CreateParameters("@TktDate", SqlDbType.Date, TktDate);
SqlDb.CreateParameters("@TktId", SqlDbType.VarChar, TktId);
SqlDb.CreateParameters("@Name", SqlDbType.VarChar, Name);
SqlDb.CreateParameters("@Email", SqlDbType.VarChar, Email);
SqlDb.CreateParameters("@Mobile", SqlDbType.VarChar, Mobile);
SqlDb.CreateParameters("@EmpId", SqlDbType.VarChar, EmpId);
SqlDb.CreateParameters("@TblLoc_ID", SqlDbType.VarChar, TblLoc_ID);
SqlDb.CreateParameters("@Promoid", SqlDbType.VarChar, Promoid);
SqlDb.CreateParameters("@TaxId", SqlDbType.VarChar, TaxId);
SqlDb.CreateParameters("@Grandtotal", SqlDbType.Decimal, Grandtotal);
SqlDb.CreateParameters("@subtotal", SqlDbType.Decimal, subtotal);
SqlDb.CreateParameters("@Details", SqlDbType.Structured, Details);
SqlDb.CreateParameters("@Servtax", SqlDbType.Decimal, Servtax);
SqlDb.CreateParameters("@vat", SqlDbType.Decimal, vat);
SqlDb.CreateParameters("@Tax3", SqlDbType.Decimal, Tax3);
SqlDb.CreateParameters("@Servtaxp", SqlDbType.Decimal, Servtaxp);
SqlDb.CreateParameters("@vatp", SqlDbType.Decimal, vatp);
SqlDb.CreateParameters("@Tax3p", SqlDbType.Decimal, Tax3p);
SqlDb.CreateParameters("@TktStatus", SqlDbType.VarChar, TktStatus);
SqlDb.ProcedureName = "[SP_InsTicketSale]";
i = Convert.ToInt32(SqlDb.ExecuteNonQueryReturn());
}
catch (Exception Excep)
{
throw new ApplicationException(Excep.Message);
}
// return i;
}
What I have tried:
in C# i have tried int type and executenonquery() it was getting count of transactions done,
i am getting null as when used string and executescalar combination
解决方案
ExecuteNonQuery() will return the number of records affected. Which would be a number.
ExecureScalar() would return the first column of the top row. So you should use ExecureScalar() instead.
Learn more from Difference between ExecuteReader ExecuteScalar and ExecuteNonQuery[^]
这篇关于如何从存储过程获取字符串值到C#?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文