Click here to Skip to main content
12,244,103 members (50,163 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: C#
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();

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();

}
}


}
Posted 31-Jan-13 3:11am

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

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
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160426.1 | Last Updated 31 Jan 2013
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100