Validate SQL Syntax without Committing

Previously, when I had written a SQL query and needed to validate the syntax without actually executing the query, I would wrap the query in a transaction and omit the COMMIT statement (I would use a ROLLBACK instead). This worked fine, but then I discovered the NOEXEC setting in SQL.

The NOEXEC setting allows you to set the context of your execute to On or Off. For example:

USE AdventureWorks2012
GO
-- Change Setting of NoEXEC to ON
SET NOEXEC ON;

-- INSERT Statement
INSERT INTO HumanResources.Shift
(Name,StartTime, EndTime,ModifiedDate)
VALUES  ('Test', GETDATE(), GETDATE()+1, GETDATE());

-- Change Setting of NoEXEC to OFF
SET NOEXEC OFF;
GO

This statement will insert zero rows into the Shift table.

Even though we have an INSERT statement right before SELECT statement, there is no impact of the INSERT statement because we have executed SET NOEXEC ON before the INSERT. When Setting NOEXEC is set to ON, it will validate the syntax of the statement but will not execute it. If there is an error it will display the error in the console.

Remember to set the value of NOEXEC statement to OFF right after your test or otherwise all of your statements will not execute on SQL Server.

Simple SQL Server Search

Here is a quick way of searching your SQL Server database objects for a specific string. This statement will search stored procedures, functions, triggers and views for a string you provide in the tag.

DECLARE @SEARCHSTRING VARCHAR(255)
SELECT @SEARCHSTRING = '<search string>'
SELECT DISTINCT sysobjects.name AS [Object Name] ,
case when sysobjects.xtype = 'P' then 'Stored Proc'
when sysobjects.xtype = 'TF' then 'Function'
when sysobjects.xtype = 'TR' then 'Trigger'
when sysobjects.xtype = 'V' then 'View'
end as [Object Type]
FROM sysobjects,syscomments
WHERE sysobjects.id = syscomments.id
AND sysobjects.type in ('P','TF','TR', 'V')
AND sysobjects.category = 0
AND CHARINDEX(@SEARCHSTRING,syscomments.text)>0

Note that the statement doesn't search through cells, just object definitions (excluding tables). Happy debugging!

Flipping a Sql Server Bit value

In a language like C# you can use ! for a bool to make it become true if it was false and vice versa, for example:

bool a = true;
a = !a;
Console.WriteLine(a.ToString());
a = !a;
Console.WriteLine(a.ToString());
Console.ReadLine();

The output will be of the above statement will be:

False
True

How can you do this in SQL Server? It is pretty easy and there are several ways. Lets take a look...

Method 1 (most efficient & recommended) - Bitwise NOT Operator:

SELECT ~CONVERT(bit,0)
SELECT ~CONVERT(bit,1)

The ~ symbol is the Bitwise NOT operator, here is what books on line has to say about the Bitwise NOT operator:

The ~ bitwise operator performs a bitwise logical NOT for the expression, taking each bit in turn. If expression has a value of 0, the bits in the result set are set to 1; otherwise, the bit in the result is cleared to a value of 0. In other words, ones are changed to zeros and zeros are changed to ones.

Method 2 - Brute Force method:

There is the brute force method. I like this method primarily because it is somewhat self-documenting and there is absolutely no question what the coder intends to do here.

USE Northwind
UPDATE products SET discontinued = CASE WHEN discontinued = 1 THEN 0 ELSE 1 END

Method 3 - Bitwise XOR Operator:

This option is to use the bitwise XOR operator with either a ’0′ or a ’1′. What does this do? The XOR operator returns ’0′ if the two arguments match and ’1′ if they don’t.

USE Northwind
UPDATE products SET discontinued = discontined ^ 1

Method 4 - Use MOD:

Finally, one may use MOD:

USE Northwind
UPDATE products SET discontinued = ((discontinued + 1) % 2)

Event Bubbling from ASP.Net User Control

When working with an ASP.Net application that has child controls nested on a page, you may need to perform some action on the parent page depending on an event which occurs on one of its child controls. For example, if a button is clicked on a child control, you may want a gridview to refresh on the parent page.

As user controls are self-contained (and possibly dynamically loaded at runtime), we cannot directly reference the ID of any element that exists on a different page. One solution would be to subscribe to the child's button click event from the parent page, but this would break some of the object oriented rules of encapsulation.

A neater solution would be to publish an event in the user control. The parent page can then subscribe to this event. With ASP.Net, this functionality already exists, via the OnBubbleEvent and RaiseBubbleEvent methods. 'Event bubbling' using these methods allows a child control to propagate events up its containment hierarchy. I found these methods extremely easy to use, and it prevents the coder from having to explicity raise custom events and subscribe to these in different levels.

Lets see how it works...

 

In my child control, the 'OnClick' code behind of my button contains the following line:

RaiseBubbleEvent(sender, new CustomClickEventArgs());

RaiseBubbleEvent sends the event data up the hierarchy to the control's parent. I've also included a custom EventArgs class - more on this in a bit.

To handle (or to further propagate the bubbled event, upwards) a control must override the OnBubbleEvent method.  A control that has an event bubbled to it does one of the following three things.

• It does nothing, in which case the event is automatically bubbled up to its parent.
• It does some processing before continuing to bubble the event. To accomplish this, a control must override OnBubbleEvent and invoke RaiseBubbleEvent from OnBubbleEvent.
• It stops bubbling the event and handles the event.
  

In the example code below, I am catchting and handling the bubble event in my parent by overriding OnBubbleEvent:
 

    /// <summary>

    /// Handles the RaiseBubbleEvent event

    /// raised by a control

    /// </summary>

    /// <param name="source"></param>

    /// <param name="e"></param>

    /// <returns>True if event is handled, false if event needs passed to parent</returns>

    protected override bool OnBubbleEvent(object source, EventArgs e)

    {

      bool handled = false;

 

      if (e is CustomClickEventArgs)

      {

        handled = true;

        RefreshParentGridView();

        }

      }

 

      return handled;

    }

 By returning true, I am preventing the event from bubbling up any further. Alternatively, I could execute some code in the method above and then return false. This would mean the event would bubble up to the next level which overrides OnBubbleEvent, and I would have performed some logic at each level.

In the parent class, we will only have one instance of OnBubbleEvent being overriden. This poses a slight problem if we are calling RaiseBubbleEvent for multiple different events in different child controls - how do we know what logic to perform at the parent level that is specific to the event that has been raised? Easy...
   
In the above OnBubbleEvent method, I have added a clause which checks the type of EventArgs passed from the RaiseBubbleEvent call in the child. Based on the EventArgs, we can determine which logic to execute. I created a custom EventArgs class for the type of event I was raising:

    /// <summary>

    /// Custom Event Arguments class

    /// </summary>

    public class CustomClickEventArgs: EventArgs

    {

        public CustomClickEventArgs()

        {

        }

    }

 

We could even parameterize this custom event args class to expand the logic - for example - we could use the same class for one entire user control but have a parameter, passed to the constructor, which specifies which button on the user control was clicked, and then check this value in the OnBubbleEvent method to perform separate logic.