Here is soultion and it is working for me for update operation then delete far easier than update :
Create Class to pass parameter.
For Example : I am passing paramter for Usermaster table : UserMasterDT :
public UserMasterDT(int userid, int roleid, string Firstname, string )
{
this.muserid = userid;
this.mroleid = roleid;
this.mfirstname = Firstname;
this.mlastname = Lastname;
}
//Here set this prop as DUMMY output variable, that u used everywhere to get output value
public int iRecUpdated
{
get { return mrecupdated; }
set { mrecupdated = value; }
}
I am binding objectdatasource to formview for update uperation.Set objectdatasource as follows :
OldValuesParameterFormatString="original_{0}"
UpdateMethod="UpdateUserWithTransaction" onupdated="odsUser_Updated">
NOTICE THERE IS NO ANY OUTPUT PARAMTER HERE
- Now, IN U R BLL In update method, pass and return UserMasterDT as input and output. DO NOT PASS SEPERATE PARAMETER AND OUT VARAIBLE . Write code like :
HERE I AM PASSING UserMasterDT AS IN AND OUT
[System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Update, true)]
public UserMasterDT UpdateUserWithTransaction(UserMasterDT tempuser)
{
int iRecUpdated = -1;
Adapter.BeginTransaction();
try
{
string sFlag = "UpdateUserRole";
int iUserId = tempuser.UserId;
int iRoleId = tempuser.RoleId;
string sFirstname = tempuser.Firstname;
string sLastname = tempuser.Lastname;
int? iReturnData;
//THIS OUT IS FROM MY SQL STORED PROCE NOT WITH OBJECTDATASOURCE OUT PARAMETER. IT HAS NOTHING TO DO WITH OBJECT DATA SOUCE.
Adapter.UpdateUserRole(sFlag,iUserId,iRoleId,sFirstname,sLastname,out iReturnData);
if (iReturnData == 1)
{
iRecUpdated = 1;
this.Adapter.CommitTransaction();
}
else if (iReturnData == null)
iRecUpdated = -1;
//What ever is return, set it back to UserMasterDT's iRecUpdated prop
tempuser.iRecUpdated = iRecUpdated;
return tempuser;
}
catch (Exception ex)
{
if (ex != null)
{
Adapter.RollbackTransaction();
//CustomEX objCUEx = new CustomEX(ex.Message, ex);
ex = null;
iRecUpdated = -1; //-1 : Unexpected Error
//What ever is return, set it back to UserMasterDT's iRecUpdated prop
tempuser.iRecUpdated = iRecUpdated;
return tempuser;
}
}
finally
{
tempuser.iRecUpdated = iRecUpdated;
}
//Return tempuser back
return tempuser;
}
then in aspx.cs page read property of UserMasterDT as follows :
if (e.Exception == null)
{
UserMasterDT tempuser = (UserMasterDT) e.ReturnValue;
lblMsg.Text = "Record Updated : " + tempuser.iRecUpdated.ToString();
}
My stored proc is :
set ANSI_NULLS ON
create PROCEDURE [dbo].[UpdateUserRole]
(
@sFlag varchar(50),
@iUserId int,
@iRoleId int,
@sFirstname varchar(50),
@sLastname varchar(50),
@iReturnData int output
)
as
Begin
Declare @errnum as int
Declare @errseverity as int
Declare @errstate as int
Declare @errline as int
Declare @errproc as nvarchar(100)
Declare @errmsg as nvarchar(4000)
-----------------------
if @sFlag = upper('UPDATEUSERROLE')
begin
begin try
begin tran
--Update User Master Table
UPDATE tblUserMaster
SET Firstname = @sFirstname,
Lastname = @sLastname,
WHERE UserId = @iUserId
--Update tblUserRolesTran Table
update tblUserRolesTran
set roleid = @iRoleId
where Userid = @iUserId
commit tran -- If commit tran execute then trancount will decrease by 1
-- Return Flag 1 for update user record and role record
SET @iReturnData = 1
end try
begin catch
IF @@Trancount > 0
-- Get Error Detail In Variable
Select @errnum =@@ERROR,
@errseverity = ERROR_SEVERITY(),
@errstate = ERROR_STATE(),
@errline = ERROR_LINE(),
@errproc = ERROR_PROCEDURE(),
@errmsg = ERROR_MESSAGE()
rollback tran
-- To see print msg, keep raise error on, else these msg will not be printed and dislayed
print '@errnum : ' + ltrim(str(@errnum ))
print '@errseverity : ' + ltrim(str(@errseverity))
print '@errstate : ' + ltrim(str(@errstate))
print '@errline : ' + ltrim(str(@errline))
print '@errproc : ' + @errproc
print '@errmsg : ' + @errmsg
--Raise error doesn't display error message below 50000
--So we have to create custom message and add to error table using sp_addmessage ( so_addmessage is built-in sp)
--In custom error message we have line number and error message
Declare @custerrmsg as nvarchar(4000)
Select @custerrmsg = 'Proc: UpdateUserRole, Line: ' + ltrim(str(@errline)) + ': ' + @errmsg
if (@errnum < 50000)
EXEC SP_ADDMESSAGE @msgnum = 50002, @severity = 16, @msgtext = @custerrmsg, @replace = 'REPLACE'
--After adding custom error message we need to raise error
--Raise error will appear at client (.net application)
RAISERROR(50002,16,1)
end catch
end
set nocount off
End
Hope this helps you whatever u need to do.
sairam