Recursive Delete in SQL Server

The Problem

Although you can define a foreign key with CASCADE DELETE in SQL Server, recursive cascading deletes are not supported (i.e. cascading delete on the same table).

If you create an INSTEAD OF DELETE trigger, this trigger only fires for the first DELETE statement, and does not fire for records recursively deleted from this trigger.

This behavior is documented on MSDN for SQL Server 2000 and SQL Server 2005.

The Solution

Suppose you have a table defined like this:

CREATE TABLE MyTable (
    OID    INT,        -- primary key
    OID_Parent INT,    -- recursion
    ... other columns
)

then the delete trigger looks like this:

CREATE TRIGGER del_MyTable ON MyTable INSTEAD OF DELETE
AS
    CREATE TABLE #Table(
        OID    INT
    )
    INSERT INTO #Table (OID)
    SELECT  OID
    FROM    deleted

    DECLARE @c INT
    SET @c = 0

    WHILE @c <> (SELECT COUNT(OID) FROM #Table) BEGIN
        SELECT @c = COUNT(OID) FROM #Table

        INSERT INTO #Table (OID)
        SELECT  MyTable.OID
        FROM    MyTable
        LEFT OUTER JOIN #Table ON MyTable.OID = #Table.OID
        WHERE   MyTable.OID_Parent IN (SELECT OID FROM #Table)
        AND     #Table.OID IS NULL
    END

    DELETE  MyTable
    FROM    MyTable
    INNER JOIN #Table ON MyTable.OID = #Table.OID

GO

The trigger first inserts all records from the deleted pseudo table into the record collection table #Table. Then it collects all detail records which are not already in the table of collected records (LEFT OUTER JOIN … WHERE IS NULL). The loop stops if no new records (i.e. no new detail records) are found. Finally, all collected records are deleted.

Handling Catastrophic Errors in ASP.Net

Occasionally it happens that you pick up information from different independent sources, and you suddenly become aware, “Hey, I gotta do something about that”.

As a developer, I really like the error page in ASP.Net, since it shows you the exception (type and message), the lines in the source code that generated it, and the stack trace that led to it (the stack trace is really a plus compared to earlier programming languages I used).

Then I ran across Crash Responsibly at Coding Horror, and I remembered I recently read something on that topic at Scott’s blog. The point is:

I’m talking about catastrophic errors — real disasters. Cases where a previously unknown bug in your code causes the application to crash and burn in spectacular fashion. (…)

If users have to tell you when your app crashes, and why, you have utterly failed your users. I cannot emphasize this enough.

I think, Jeff is right. For the average user, the stack dump that I value so much is of little use. So I should handle error messages resulting from programming errors more sensibly.

The web is full of instructions how to generically handle ASP.Net exception. CodeProject has some nice articles here, here, and here.

Since I want to have my C# code in .cs files, I chose to keep code in the Global.asax to a minimum, and handle everything else in the error.aspx.cs code instead.

The key elements of exception handling are always the same:

web.config

Set up web.config to display the default ASP.Net error page if you develop on localhost, but display a “nice” error page for remote users:

<customErrors mode="RemoteOnly" defaultRedirect="error.aspx" />

The defaultRedirect value is the name of your error handling aspx page.

global.asax

In global.asax, process the exception. In my implementation, I store the exception and the request that generated it in the Session object:

void Application_Error(object sender, EventArgs e)
{
    Exception ex = Server.GetLastError();
    Session["Application_Error"] = ex;
    Session["Application_Request"] = Request;
}

Exception Handling in error.aspx

The user should get at least *some* information what happened, because I find that a constant message “Some error occurred” is little informative. So I display the exception type and message, and whether the application succeeds in notifying an administrator.

If you handle errors and exceptions, you should take care that you do not raise another exception. So everything should be triple-checked here

I generate an email containing every information I can get to track down the error condition:

• Application: host and application path
• Exception object: type, message, trigger (TargetSite), stack trace
• User: current user, application-specific user information

You can fill your web.config with appSettings entries to provide additional information.

Finally, I generate an email (System.Net.Mail.MailMessage) and send it to configurable addressees.

Bugzilla uses special email headers (X-Bugzilla-*) that can be used for filtering rules in an email client. So you may choose to provide some of the collected information as email headers, using the Headers.Add method (be careful: values must not be empty strings!).

Step through every code path during development and debugging, to avoid errors in your error handling!

Displaying PRINTed SQL Output in ASP.Net

The SqlConnection, SqlCommand and SqlDataReader classes are mainly used to query resultsets from the database. But they also support retrieving the output of SQL Server’s PRINT statements.

The printed output can be processed in the SqlConnection event InfoMessage:

SqlConnection cn = OpenMyConnection();
cn.InfoMessage += new SqlInfoMessageEventHandler(cn_InfoMessage);

So if you have to display the result of PRINT statements in an ASP.Net multiline TextBox, try this code:

sbLog = new System.Text.StringBuilder();
SqlCommand cmd = new SqlCommand("PRINT 'Hello World'", cn);
cmd.ExecuteNonQuery();
TextBox1.Text = sbLog.ToString();

and additionally

void cn_InfoMessage(object sender, SqlInfoMessageEventArgs e)
{
    sbLog.AppendLine(e.Message);
}

You can also let .Net execute the SQL statements automatically by calling them from the SelectCommand of a SqlDataSource which is assigned to a GridView:

ds.Selecting += new
    SqlDataSourceSelectingEventHandler(ds_Selecting);

void ds_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
{
    SqlConnection cn = (SqlConnection)e.Command.Connection;
    cn.InfoMessage += new SqlInfoMessageEventHandler(cn_InfoMessage);
}

In this solution, we can access the SqlConnection object in the Selecting event to add our InfoMessage event handler, which captures PRINT output.

The Connection value in the event arguments has to be cast to SqlConnection to support InfoMessage.

If your SQL statement never returns a recordset, set GridView1.Visible = false after binding.

Generating Database Scripts

When we hear the phrase “database script generation”, or “scripting a database”, we use to think, oh that’s easy, simply right-click in Enterprise Manager or Management Studio, select “Generate Script”, and that’s it.

But there is more to scripting.

First of all, the built-in utilities occasionally fail because they do not analyze dependencies. If a view depends on another view, whose name is alphanumerically, you’re out of luck. Or you run across some kind of internal error.

So let’s have a look at what possible types of scripts we have to deal with:

• Database Object Creation and Deletion (and re-generation)
• Schema Migration (change scripts for database objects)
• Table Data
• Record Identifiers (“magic values” linking your code with the database)

All these kinds of scripts are necessary for development, versioning, and deployment.

Replicate String in C#

My original posting on string repetition caused a couple responses, and is currently among the Top Posts, which indicates to me that this seems to be a frequent and non-trivial problem.

The .Net API requires that we need to handle two different cases:

Replicating Chars

If you need to replicate a character value, reader Chris points out correctly that the string constructor

s = new string(’*', count);

is to be used.

Replicating Strings

I found the code used in the original posting to replicate an array and convert it to a string afterwards somewhere on the web. What I did not like abound that one-liner was that it allocated first the array, and then the string, resulting in double the memory which should be necessary.

Reader StewartFip posted the StringBuilder.Insert method as a solution, which seems to do the job:

new StringBuilder().Insert(0,”myString”,count).ToString()

Comparison

My guess was that allocating enough memory in the StringBuilder constructor should speed up the Insert(). Actually, it was NOT!

A small benchmarking application showed consistently, that the fastest way is to use StringBuilder.Insert(), a couple percent faster than new StringBuilder(totalsize).Insert(), and the array-to-string conversion taking twice as long.

Thank you everybody for your feedback!

Introducing SchemaFind

A couple of years ago I wrote this small utility to search definitions of tables, views and stored procedures in a live MSSQL or Oracle database. I recently updated the MSSQL interface to support the new object types of SQL Server 2005.

The search functionality covers object names, column names, and object definitions in T-SQL and PL/SQL code, respectively.

You can read more about SchemaFind and download it here.