如何将存储过程中的错误消息传递到企业类库中 [英] How do I pass an error message from stored procedure into enterprise class library
问题描述
这是我的存储过程
更新程序dbo.spInsertLoanReviewer
（
@SUBSCRIBER_ID varchar（ 20），
@STAFF_ID varchar（20），
@STAFF_NAME varchar（200），
@BRANCH_CODE varchar（20），
@text VARCHAR（100）输出
）
AS
IF EXISTS（SELECT * FROM LNS_APPROVAL_PERSON_MASTER_TBL WHERE STAFF_ID = @STAFF_ID AND STAFF_NAME = @STAFF_NAME）
BEGIN
SET @text = @STAFF_NAME +''已经添加''
结束
ELSE
BEGIN
INSERT INTO LNS_APPROVAL_PERSON_MASTER_TBL（SUBSCRIBER_ID，STAFF_ID，STAFF_NAME，BRANCH_CODE）
VALUES（@ SUBSCRIBER_ID，@ STAFF_ID，@ STAFF_NAME，@ BRANCH_CODE）
SET @text = @STAFF_NAME +''已成功添加''
END
返回@文字
这是我的班级图书馆
public void InsertLoanReviewerPerson（ string SubscriberID，string staffID，string staffname，string branchCode，ref string StoredProcMsg，ref string msg）
$
数据库db = EnterpriseLibraryContainer。 Current.GetInstance< database>（）;
DbCommand cmdInsertIncomeExpenditure = db.GetStoredProcCommand（spInsertLoanReviewer）;
db.AddInParameter（cmdInsertIncomeExpenditure，SUBSCRIBER_ID，DbType.String，SubscriberID）;
db.AddInParameter（cmdInsertIncomeExpenditure，STAFF_ID，DbType.String，staffID）;
db.AddInParameter（cmdInsertIncomeExpenditure，STAFF_NAME，DbType.String，staffname）;
db.AddInParameter（cmdInsertIncomeExpenditure，BRANCH_CODE，DbType.String，branchCode）;
/ / Add（@ ReturnValue，SqlDbType.Int，4）.Direction = ParameterDirection.ReturnValue;
// cmdInsertIncomeExpenditure.Parameters.Add（@ result）。Direction = ParameterDirection.Output; < br $>
//cmdInsertIncomeExpenditure.Parameters.Add(\"\").Direction = ParameterDirection.Output;
使用（DbConnection conn = db.CreateConnection（））
{
conn.Open（）;
DbTransaction trans = conn.BeginTransaction（）;
尝试
{
//执行命令，将当前事务传递给每一个
db.ExecuteNonQuery（cmdInsertIncomeExpenditure，trans）;
trans.Commit（）; //提交交易
StoredProcMsg =（string）cmdInsertIncomeExpenditure.Parameters [@ text]。值;
}
catch
{
trans.Rollback（）; //回滚交易
conn.Close（）;
}
终于
{
if（conn！= null&& conn.State == ConnectionState.Open）
conn.Close （）;
}
}
}
this is my stored procedure
ALTER PROCEDURE dbo.spInsertLoanReviewer
(
@SUBSCRIBER_ID varchar(20),
@STAFF_ID varchar(20),
@STAFF_NAME varchar(200),
@BRANCH_CODE varchar(20),
@text VARCHAR(100) OUTPUT
)
AS
IF EXISTS (SELECT * FROM LNS_APPROVAL_PERSON_MASTER_TBL WHERE STAFF_ID = @STAFF_ID AND STAFF_NAME = @STAFF_NAME)
BEGIN
SET @text = @STAFF_NAME + '' Has Already been Added''
END
ELSE
BEGIN
INSERT INTO LNS_APPROVAL_PERSON_MASTER_TBL (SUBSCRIBER_ID,STAFF_ID,STAFF_NAME,BRANCH_CODE)
VALUES(@SUBSCRIBER_ID,@STAFF_ID,@STAFF_NAME,@BRANCH_CODE)
SET @text = @STAFF_NAME + '' was successfully Added''
END
RETURN @text
THIS IS MY CLASS LIBRARY
public void InsertLoanReviewerPerson(string SubscriberID,string staffID,string staffname,string branchCode,ref string StoredProcMsg, ref string msg)
{
Database db = EnterpriseLibraryContainer.Current.GetInstance<database>();
DbCommand cmdInsertIncomeExpenditure = db.GetStoredProcCommand("spInsertLoanReviewer");
db.AddInParameter(cmdInsertIncomeExpenditure, "SUBSCRIBER_ID", DbType.String, SubscriberID);
db.AddInParameter(cmdInsertIncomeExpenditure, "STAFF_ID", DbType.String, staffID);
db.AddInParameter(cmdInsertIncomeExpenditure, "STAFF_NAME", DbType.String, staffname);
db.AddInParameter(cmdInsertIncomeExpenditure, "BRANCH_CODE", DbType.String, branchCode);
//Add("@ReturnValue", SqlDbType.Int, 4).Direction = ParameterDirection.ReturnValue;
// cmdInsertIncomeExpenditure.Parameters.Add("@result").Direction = ParameterDirection.Output;
//cmdInsertIncomeExpenditure.Parameters.Add("").Direction = ParameterDirection.Output;
using (DbConnection conn = db.CreateConnection())
{
conn.Open();
DbTransaction trans = conn.BeginTransaction();
try
{
// execute commands, passing in the current transaction to each one
db.ExecuteNonQuery(cmdInsertIncomeExpenditure, trans);
trans.Commit(); // commit the transaction
StoredProcMsg = (string)cmdInsertIncomeExpenditure.Parameters["@text"].Value;
}
catch
{
trans.Rollback(); // rollback the transaction
conn.Close();
}
finally
{
if (conn != null && conn.State == ConnectionState.Open)
conn.Close();
}
}
}
推荐答案
你可以这样做
里面SP
SET @ErrorMessage =''错误已经发生。'' - 错误信息你想给的任何东西
RAISERROR（@ ErrorMessage，16,1）
在你的InsertLoanReviewerPerson方法中捕获错误
Catch（Exception ex）
{
trans.Rollback（）; //回滚交易
conn.Close（）;
MessageBox.Show（ex.message）;
}
快乐编码
You can do like
Inside SP
SET @ErrorMessage=''error has occured.'' --error message whatever you wish to give
RAISERROR(@ErrorMessage,16,1)
And in your InsertLoanReviewerPerson method to catch error put
Catch(Exception ex)
{
trans.Rollback(); // rollback the transaction
conn.Close();
MessageBox.Show(ex.message);
}
happy coding
这篇关于如何将存储过程中的错误消息传递到企业类库中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
