Default values in ASP Textbox with JQuery

Sometimes you will need to add instructions to textboxes to provide input information to the user. We can use default text to do this. This is the text that will appear in the textbox when it is empty.


Step 1

If you don't have jQuery then download latest library from its website and include in the script section of your page.

<script src="jquery.js" type="text/javascript"></script>

 

Step 2

Next you need to create two CSS classes - defaultText and defaultTextActive as shown below. These are described in Step 3.

<style media="screen" type="text/css">

   .defaultText { width: 300px; }

   .defaultTextActive { color: #a1a1a1; font-style: italic; }

</style>

Step 3

Add the following JavaScript to you page. This associates the CSS classes with the appropriate JQuery methods.

<script language="javascript">

$(document).ready(function()

{

    $(".defaultText").focus(function(srcc)

    {

        if ($(this).val() == $( this)[0].title)

        {

            $(this).removeClass("defaultTextActive");

            $(this).val("");

        }

    });

   

    $(".defaultText").blur(function()

    {

        if($(this).val() == "")

        {

            $(this).addClass("defaultTextActive");

            $(this).val($(this)[0].title);

        }

    });

    $(".defaultText").blur();       

});

</script>

Step 4

To every text field or text area where you want to apply default text, add "defaultText" as its css class and specify the default text in the title attribute. e.g.
 

<input class="defaultText" title="e.g. someone@example.com" type="text" />

Data Modelling - Slowly Changing Dimensions (SCD)

Dimension is a term in data management and data warehousing. It's the logical groupings of data such as geographical location, customer or product information. With Slowly Changing Dimensions (SCDs) data changes slowly, rather than changing on a time-based, regular schedule. Some examples:

• product prices changing over time
• people changing their name
• country/state names may change over time

A slowly changing dimension is generally categorised into one of three possible types; Type 1, Type 2 and Type 3. In the examples that follow, I explain each type.

This is our initial example data:

Product ID (PK)	Year	Product Name	Product Price
1	2004	Product 1	£150

Type 1: Overwriting the old values

In the year 2005, if the price of the product changes to £250, then the old values of the columns "Year" and "Product Price" have to be updated and replaced with the new values. In this type, there is no way to find out the old value of the product "Product 1" in the year 2004 since the table now contains only the new price and year information.

Product ID (PK)	Year	Product Name	Product Price
1	2005	Product 1	£250

Type 2: Creating another additional record

In this type, the old values will not be replaced but a new row containing the new values will be added to the product table. So at any point of time, the difference between the old values and the new values can be retrieved and easily be compared. This would be very useful for reporting purposes.

Product ID (PK)	Year	Product Name	Product Price
1	2004	Product 1	£150
1	2005	Product 1	£250

The problem with this approach is "Product ID" cannot store duplicate values of Product 1 since "Product ID" is the primary key. Also, the current data structure doesn't clearly specify the effective date and expiry date of Product 1 (that is, when the change to it's price happened). So, it would be better to change the current data structure to overcome the above primary key violation.

Product ID (PK)	Effective DateTime (PK)	Year	Product Name	Product Price	Expiry DateTime
1	01-01-2004 12.00 AM	2004	Product 1	£150	12-31-2004 11.59 PM
1	01-01-2005 12.00 AM	2005	Product 1	£250

In the changed table structure, "Product ID" and "Effective DateTime" are composite primary keys. So there would be no violation of primary key constraint. Addition of new columns, "Effective DateTime" and "Expiry DateTime" provides the information about the product's effective date and expiry date which adds more clarity and enhances the scope of the table. This type 2 approach will require additional space in the database due to the additional record being stored, but this should not be an issue as dimensions are not that big in the real world.

Type 3: Creating new fields

In this type, the latest update to the changed values can be seen. New columns are added which allow us to keep track of the changes.

Product ID (PK)	Current Year	Product Name	Current Product Price	Old Product Price	Old Year
1	2005	Product 1	£250	£150	2004

The problem with Type 3 is that the complete history will not be stored if there are several continuous changes over the years (it retains a limited history). For example, in 2006, if the price changes to £350, we would not be able to see the complete history of 2004 prices, since the old values would have been updated with the 2005 product information.

Product ID (PK)	Current Year	Product Name	Current Product Price	Old Product Price	Old Year
1	2006	Product 1	£350	£250	2005

SSIS Transactions without MSDTC - PART 2 - Excluding Error Logging Event Handler From Transaction

In my previous post, I implemented explicit transaction logic into a SSIS package when the MSDTC service was not available.

One issue this may raise is around the default SSIS event handlers. My package has an 'OnError' event handler, which is called when any error occurs within the package. The event handler simply logs the error to my SQL Error Logging table. This handler was using the same data connection as my main package. Unfortunately this meant that if an error occurred within the package, everything was rolled back, including the logged error message! I needed to find some way of maintaining the logged error but rolling back everything else.

The solution is simple - create a new datasource for the error logging handler:

All other properties should be the same as the data connection used in the main package.
This creates separation between the datasource within the transaction logic and the event handler's datasource.

SQL Server Integration Services SSIS Transactions without MSDTC - PART 1

Sometimes you might required transaction logic within your SSIS packages, in the case of a failure during the process which requires a rollback. SSIS has transaction logic built in, provided the Distributed Transaction Coordinator (MSDTC) is running on the deployment environment. Our deployment servers do not have this process running, therefore I had to apply my transaction logic manually.

Luckily the solution is pretty straightforward: for every task using a database connection, we can use database transactions. See the following example:

We have a very basic SSIS package. It reads employee data from a source and stores the new employees in the employee dimension.



After the data flow has finished, the Execute SQL Task issues an update statement against the dimension that will update the parent-child relationship. In other words, it will set the foreign key relationship from a child pointing to the primary key of the parent. The update statement also allows employees to change who their manager is for example.

The requirement is that when the update statement fails, the inserts from the data flow are rolled back. Since we cannot use MSDTC, we need an alternative.


Using SQL Server Database Transactions

If the tasks that need transactions all use the same connection manager, we can leverage database transactions to achieve our requirement. Let's start by putting an Execute SQL task before the data flow and connect both of them with a Success precedence constraint (the green arrow). This task uses the same connection manager as the data flow and the Execute SQL Task. The task specifies one simple SQL statement:

BEGIN TRANSACTION;

This statement will start an explicit transaction on the current connection. After the Execute SQL Task we put another Execute SQL Task and we connect both of them again with a Success precedence constraint. Again, this task uses the same connection manager as the other tasks. The tasks has the following SQL statement:

COMMIT TRANSACTION;

This statement will commit the explicit transaction on the current connection if the Execute SQL Task with the update statement has finished successfully.




But what if the update statement fails? In that case, the SSIS package will fail because an error occurred and it will stop running. Any remaining open connections will be closed when the SSIS package is shut down. The SQL Server database engine will roll back the transaction when the connection is closed. If you prefer to have more control over when the transaction is rolled back, another Execute SQL Task can be added to the control flow. This task uses a Failure precedence constraint (the red arrow). This task explicitly rolls back the transaction with the following statement:

ROLLBACK TRANSACTION;

This approach is more robust and it allows you to add some possible clean-up code.




SQL Server Integration Services Connection Manager

However, when we run the package, the following error is returned by SSIS:

Executing the query "COMMIT TRAN;" failed with the following error: "The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION."




This is caused by the fact that the connection manager actually doesn't create one single connection. When running the package, multiple connections are created for the different tasks. You can look at the connection manager as if it is a little factory, creating connections for all the tasks. This means that when SSIS reaches the final Execute SQL Task, the connection from the first Execute SQL Task - the one that starts the transaction - is already closed. Hence, SQL Server doesn't find the corresponding BEGIN TRAN, so it returns an error. How can we solve this issue? The connection manager has a very useful property called RetainSameConnection. By default this property is set to false. When we change it to true, we tell the connection manager to create only a single connection which will be shared amongst the different tasks.



This will now run successfully.

Using SQL Common Table Expressions (CTE)

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

A CTE can be used to:

•Create a recursive query.
•Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.
•Reference the resulting table multiple times in the same statement.

Using a CTE offers the advantages of improved readability and ease in maintenance of complex queries. The query can be divided into separate, simple, logical building blocks. These simple blocks can then be used to build more complex, interim CTEs until the final result set is generated.

CTEs can be defined in user-defined routines, such as functions, stored procedures, triggers, or views.

A CTE is made up of an expression name representing the CTE, an optional column list, and a query defining the CTE. After a CTE is defined, it can be referenced like a table or view can in a SELECT, INSERT, UPDATE, or DELETE statement. A CTE can also be used in a CREATE VIEW statement as part of its defining SELECT statement.

The basic syntax structure for a CTE is:


WITH expression_name [ ( column_name [,...n] ) ]
AS
( CTE_query_definition )


The list of column names is optional only if distinct names for all resulting columns are supplied in the query definition.

The statement to run the CTE is:


SELECT
FROM expression_name;


Heres a full example:

USE AdventureWorks2008R2;
GO
-- Define the CTE expression name and column list.
WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS
-- Define the CTE query.
(
    SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
)
-- Define the outer query referencing the CTE name.
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID
ORDER BY SalesPersonID, SalesYear;
GO

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.