Monday, August 31, 2009

Using common table expressions (CTE) to generate sequences

Hello frnds,
One of the best enhancements in T-SQL with SQL Server 2005 was Common Table Expressions(CTEs). CTEs are very helpful in writing more readable and manageable queries. The good things don’t end here; self-referencing CTEs are a very powerful method of implementing recursion in SQL queries. In this post, I will present a few examples of generating sequences using CTEs.
The following statements create a number sequence from 1 to 10.

Example:-


   1:  Declare @start int, @end int

   2:  Select @start=1, @end=10

   3:   

   4:   

   5:  ;WITH CTESequence(number)

   6:  AS

   7:  (

   8:  SELECT @start

   9:  UNION ALL

  10:  SELECT number +1 FROM CTESequence

  11:  WHERE number<@end

  12:  )

  13:  SELECT * FROM CTESequence



Output is :-

Friday, August 28, 2009

ASP.NET MasterPages and User Defined Events

ASP.NET MasterPages and User Defined Events

Master pages were introduced in ASP.NET 2.0 and are a great way to create a consistent layout for the pages throughout your application. I thought a good article would be to demonstrate how to create a custom event that web content pages can raise if they click on web server controls located in the master page. If you want to raise an event and pass data to an event handler, you need to create a class that inherits from System.EventArgs . EventsArgs is the base class for classes containing event data. It contains no event data. It is used by events to pass data to an event handler when an event is raised.

To begin with open Visual Studio 2008 and choose File > New > Web > ASP.NET Web Application. Add a master page to the project and name it Site.Master. Before going any further add a new class to the project and name it CustomArgs. Add the following code to the newly created class:
C#

   1:  public class CustomArgs : EventArgs

   2:  {

   3:  public bool Cancel { get; set; }

   4:        public string Message { get; set; }

   5:        public string NavigateTo { get; set; }

   6:  }



In the code above, CustomArgs inherits EventArgs. This means that when we raise an event in the master page, a reference to CustomArgs will be passed to our event handler, so we can set these properties and have the master page determine what to do next.
Go back to the master page and add the following code to create a new event:
C#

   1:  public EventHandler ButtonClick;



We have just created a new event called ButtonClick that accepts CustomArgs as the argument. Now add a Button to the page and add the following code to the Button’s Click event:
C#
---------------------

   1:  protected void Button1_Click(object sender, EventArgs e)

   2:  {

   3:  lblValue.Text = null;

   4:        if (ButtonClick != null)

   5:        {

   6:              CustomArgs args = new CustomArgs();

   7:              ButtonClick(sender, args);

   8:              if (args.Cancel)

   9:              {

  10:                    lblValue.Text = args.Message;

  11:  }

  12:              else

  13:              {

  14:                    Response.Redirect(args.NavigateTo);

  15:              }

  16:  }            

  17:  }



What’s happening in the code above is when a user clicks on the Button, which resides in the master page, it will check to see if there’s code that raises the ButtonClick event. If there is, the event handler will be executed. Once the event handler has completed, the master page will check the value of CustomArgs.Cancel property, and if it is false, it will display what is in the CustomArgs.Message property; otherwise it will navigate to the CustomArgs.NavigateTo page.
To see this in action add two web content forms to the project and select the master page added earlier. Leave these pages as WebForm1.aspx and WebForm2.aspx:



To be able to call the event from the master page, you’ll need to add a new MasterType directive to the both pages:


   1:  <%@ MasterType VirtualPath="~/Site.Master" %>



This provides a way to create a strongly typed reference to the master page when the master page is accessed from the Master() property. Now we must create an event handler for the ButtonClick event. Add the following code to WebForm1.aspx:
C#
-----------------------------


   1:  protected void Page_Load(object sender, EventArgs e)

   2:  {

   3:       Master.ButtonClick += new EventHandler<CustomArgs>(MasterButtonClick);

   4:  }

   5:   

   6:  private void MasterButtonClick(object sender, CustomArgs e)

   7:  {

   8:  if (!string.IsNullOrEmpty(TextBox1.Text))

   9:        {

  10:              e.Cancel = false;

  11:              e.NavigateTo = "~/WebForm2.aspx";

  12:  }

  13:        else

  14:        {

  15:              e.Cancel = true;

  16:              e.Message = "There was no text entered...";

  17:  }            

  18:  }

  19:   



In the code above an event handler called MasterButtonClick has been created. This event handler will be raised when the user clicks the Button in the master page. To simulate a business application I’ve added a TextBox to the page so if the user leaves it blank, validation will fail and they should be alerted to this. This is made possible by setting the CustomArgs.Cancel property. If the user does enter data then they will be allowed to navigate to the next page.
Open the WebForm2.aspx page and add the following code to the code behind:
C#
---------------------------------

   1:  protected void Page_Load(object sender, EventArgs e)

   2:  {

   3:  Master.ButtonClick = delegate(object s, CustomArgs c)

   4:        {

   5:              c.Cancel = false;

   6:              c.NavigateTo = "~/WebForm1.aspx";

   7:  };

   8:  }



In the code above I have created an anonymous method that handles the ButtonClick event. This page will not validate anything and therefore will direct the user back to the WebForm1.aspx page.

If you run the application and step through the code you’ll see that when you click on the button in the master page, it will check and execute the event handlers in the web content forms. This is a nice way of being able to create a wizard style application, but having the flexibility of raising events in the child pages when you need to.

Wednesday, August 12, 2009

Solve TSQL Challenge9

Hello friends,
Here I have solve TSQL challenge9. Please read the challenge from the below url.
http://beyondrelational.com/blogs/tc/archive/2009/06/04/tsql-challenge-9.aspx

Solution:-
-------------


   1:  DECLARE @tc9 TABLE(

   2:  ID INT IDENTITY(1,1),

   3:  CreationDate DATETIME,

   4:  Content NVARCHAR(10),

   5:  SendState BIT,

   6:  AckState BIT

   7:  )

   8:   

   9:  INSERT INTO @tc9 (CreationDate,Content,SendState,AckState)

  10:  SELECT GETDATE()-1.0,'Msg #1',0,0 UNION

  11:  SELECT GETDATE()-0.9,'Msg #2',0,0 UNION

  12:  SELECT GETDATE()-0.8,'Msg #3',1,1 UNION

  13:  SELECT GETDATE()-0.7,'Msg #4',1,1 UNION

  14:  SELECT GETDATE()-0.6,'Msg #5',1,1 UNION

  15:  SELECT GETDATE()-0.5,'Msg #6',1,0 UNION

  16:  SELECT GETDATE()-0.4,'Msg #7',1,0 UNION

  17:  SELECT GETDATE()-0.3,'Msg #8',1,0 UNION

  18:  SELECT GETDATE()-0.2,'Msg #9',1,0 UNION

  19:  SELECT GETDATE()-0.1,'Msg #10',1,1

  20:   

  21:  SELECT * FROM @tc9

  22:   

  23:  ;WITH CTE

  24:  AS

  25:  (

  26:  SELECT *,ID-ROW_NUMBER() over( partition by sendstate,ackstate order by ID) as GroupID FROM @tc9

  27:  )

  28:  SELECT MIN(ID) FirstIDInclusive,MAX(ID) LastIDInclusive,SendState,AckState FROM CTE GROUP BY GroupID,SendState,AckState

  29:  ORDER BY MIN(ID)