Using OUTPUT Clause for Getting Deleting Information from Several Tables.

In our programming practice we can face the situation, when deleting of a row (or rows) from some table (for example, Table_0) will necessarily cause deleting several rows from another table (Table_1), etc. This is situation when one of the columns of the Table_1 is the foreign key, which refers to the column (primary key) of the Table_0. And very often we have to know how much rows were deleted from Table_0, Table_1 ( and may be Table_2 , etc.). In this article I will explain how you can use such a T-SQL (SQL Server 2005) feature as OUTPUT clause in order to get information about rows, which were affected, when deleting process is executed for several tables, "connected" by foreign keys. The examples are written using T-SQL and C#.

For simplicity we will consider a case only with two tables: HelthParameter and CheckPapameter (fig. 1). Don't look for some "deep sense" for these tables, they are just for example.

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];

CREATE PROCEDURE [dbo].[usp_HelthParameter_Delete]

(

@Original_HelthParameterID smallint,

@DeletedCount nvarchar(200) OUTPUT

)

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;

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 !