Figure 1.
As you can see each execution of the query:
DELETE FROM dbo.HelthParameter WHERE (HelthParameterID = @Original_HelthParameterID);
has to execute some query like this (because of the HelthParameterID column in the CheckPapameter):
DELETE FROM dbo.CheckPapameter WHERE (HelthParameterID = @Original_HelthParameterID);
Now we add to our query OUTPUT clause, which returns information from each row, affected by DELETE statement, into a table variable. Something like this:
OUTPUT DELETED.<SomeColumnOfOurTable> INTO <@TableVariable>
Now we just count rows of our @TableVariable and return results to the client.
In order to return results it is better to use output parameter. By this way we can return variable of any type (char, int, etc.) and with any “additions†we want.
Our stored procedure will look like this:
IF OBJECT_ID ( '[dbo].[usp_HelthParameter_Delete]', 'P')
IS NOT NULL
DROP PROCEDURE [dbo].[usp_HelthParameter_Delete];
GO
CREATE PROCEDURE [dbo].[usp_HelthParameter_Delete]
(
@Original_HelthParameterID smallint,
@DeletedCount nvarchar(200) OUTPUT
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @TableHelth table (DeletedCheck int );
DECLARE @TableCheck table (DeletedCheck int );
DECLARE @CountHelth int;
DECLARE @CountCheck int;
BEGIN TRY
BEGIN TRANSACTION;
DELETE FROM dbo.HelthParameter
OUTPUT DELETED.HelthParameterID
INTO @TableHelth
WHERE (HelthParameterID =
@Original_HelthParameterID);
DELETE FROM dbo.CheckPapameter
OUTPUT DELETED.CheckPapameterID
INTO @TableCheck
WHERE (HelthParameterID =
@Original_HelthParameterID);
SELECT @CountHelth =
(select count(*) from @TableHelth)
SELECT @CountCheck =
(select count(*) from @TableCheck)
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
SELECT @CountHelth = -1;
SELECT @CountCheck = -1;
ROLLBACK TRANSACTION;
END CATCH;
SELECT @DeletedCount = cast(@CountHelth as varchar(4)) + ';' +
cast(@CountCheck as varchar(4));
END;
GO
Of course, you can change @DeletedCount variable to more “complicated†one and get fuller information. For example:
SELECT @DeletedCount = 'HelthParameter, ' + cast(@CountHelth as varchar(4)) + ' rows affected;' +
' CheckPapameter, ' + cast(@CountCheck as varchar(4)) + ' rows affected';
Now, let’s suppose, that you already have the HelthParameterTableAdapter with all commands (DeleteCommand, InsertCommand, etc.) in your DAL (data access layer) part of the solution. You only have to change CommandText (to the name of your stored procedure) and CommandType (to StoredProcedure) and add the @DeletedCount parameter:
Figure 2.
In your BL part (frequently, it is some separate project in your solution) you add any logic (for the result message) you want. For example, like this:
[DataObjectMethod(DataObjectMethodType.Delete, true)]
public static string DeleteHelthParameter
(HelthParameter helthParameter)
{
HelthParameterTableAdapter helthParameterAdapter =
new HelthParameterTableAdapter();
string result = string.Empty;
helthParameterAdapter.Delete
(helthParameter.HelthParameterID, out result);
if (result.Substring(0, (result.IndexOf(";"))).Equals("-1")
|| result.Substring((result.IndexOf(";")) + 1).Equals("-1"))
{
result=
"Process has been cancelled! " +
"There is a problem on the server!";
}
else
{
result = "HelthParameter table: " +
result.Substring(0, (result.IndexOf(";"))) +
" row(s) affected. CheckPapameter table: " +
result.Substring((result.IndexOf(";")) + 1) +
" row(s) affected.";
}
return result;
}
And at last, if you use some ObjectDataSource (suppose, named General), you can add to the Deleted event of this object the following code:
protected void ObjectDataSourceGeneral_Deleted
(object sender, ObjectDataSourceStatusEventArgs e)
{
e.ExceptionHandled = true;
LabelMessage.Visible = true;
if (e.Exception != null)
{
LabelMessage.Text = e.Exception.Message;
}
else
{
LabelMessage.Text = e.ReturnValue.ToString () ;
}
}
Now, if transaction was successful, you get message like this:
Figure 3.
or, if transaction was not committed (ROLLBACK TRANSACTION), like this:
Figure 4.
CONCLUSION
I hope that this article will help you in more details to trace complicated deleting process from several tables.
Good luck in programming !