SQL SERVER – 2008 – Enhenced TRIM() Function – Remove Trailing Spaces, Leading Spaces, White Space, Tabs, Carriage Returns, Line Feeds

CREATE FUNCTION dbo.LTrimX(@str VARCHAR(MAX)) RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @trimchars VARCHAR(10)
SET @trimchars = CHAR(9)+CHAR(10)+CHAR(13)+CHAR(32)
IF @str LIKE '[' + @trimchars + ']%' SET @str = SUBSTRING(@str, PATINDEX('%[^' + @trimchars + ']%', @str), 8000)
RETURN @str
END
GO
CREATE FUNCTION dbo.RTrimX(@str VARCHAR(MAX)) RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @trimchars VARCHAR(10)
SET @trimchars = CHAR(9)+CHAR(10)+CHAR(13)+CHAR(32)
IF @str LIKE '%[' + @trimchars + ']'
SET @str = REVERSE(dbo.LTrimX(REVERSE(@str)))
RETURN @str
END
GO
CREATE FUNCTION dbo.TrimX(@str VARCHAR(MAX)) RETURNS VARCHAR(MAX)
AS
BEGIN
RETURN dbo.LTrimX(dbo.RTrimX(@str))
END
GO
/* Run the created function */
SELECT dbo.TRIMX('         word leading trailing spaces           ') AS 'TrimmedWord'
GO

Create ASP.NET Server Controls from Scratch

Introduction

ASP.NET comes with its own set of server-side controls, so why create our own?

By creating our own controls, we can then build powerful, reusable visual components for our Web application’s user interface.

This tutorial will introduce you to the process of ASP.NET server control development. You’ll also see how creating your own controls can simultaneously improve the quality of your Web applications, make you more productive and improve your user interfaces.
ASP.NET custom controls are more flexible than user controls. We can create a custom control that inherits from another server-side control and then extend that control. We can also share a custom control among projects. Typically, we will create our custom control in a web custom control library that is compiled separately from our web application. As a result, we can add that library to any project in order to use our custom control in that project.

---

HTML5 Video Overview

Until now, there has never been a native way to display video on a web page. Today, most videos are shown, via the use of a plugin (like Flash or Silverlight). However, not all browsers have the same plugins. HTML5 specifies a standard, native way to include video, with the video element.
Currently, there are two widely supported video formats for the video element: Ogg files [encoded with Theora and Vorbis for video and audio respectively] and MPEG 4 files [encoded with H.264 and AAC].
To show a video in HTML5, this is all we need:

1. <video width="320" height="240" controls="controls">  
2.   <source src="movie.ogg" type="video/ogg" />  
3.   <source src="movie.mp4" type="video/mp4" />  
4. </video>  
5.           

The controls attribute is for adding play, pause and volume controls. Without this attribute, your video would appear to be only an image. It is also always a good idea to include both the width and height attributes. The following table shows all attributes of the <video> element:

• autoplay: Specifies that the video will start playing as soon as it is ready
• controls: Specifies that controls will be displayed, such as a play button
• height: The height of the video player
• 
  
• loop: Specifies that the media file will start over again, every time it is finished
• preload: Specifies that the video will be loaded at page load, and ready to run. Ignored if “autoplay” is present
• src: The URL of the video to play
• width: The width of the video player
• poster: The URL of the image to show while no video data is available

---

Step 0: Getting Started

All that is required to get started is a copy of Visual Studio of Visual Web Developer Express. If you don't have the full version of Visual Studio, you can grab the free Express Edition.
The HTML5 video player that we will create here is only a simple video player that will render whatever native interface the browser provides. Browsers that support HTML5 video have video players built in, including a set of controls (play/pause etc.), so you will see a different interface for each browser when running this control.

The HTML5 video player in Firefox 3.6.8.

---

Step 1: Creating a Custom Control Project

First, we need to create a new class library project to hold our custom controls. By creating the custom control in a separate class library, we can compile the project into a separate DLL and use the custom control in any application that requires it.
Open your ASP.NET project with Visual Studio or Visual Web Developer. In Solution Explorer, right click the solution name, and select Add New Project from the context menu. In the Add New Project dialog box, choose the project type to be a Web project, and select ASP.NET Server Control as the template, like so:

Name the project CustomControls. Click OK. The new ASP.NET Server Control project is created, and Visual Studio also provides you with a simple Web control to start with. Delete this custom control because we don't need it.

---

Step 2: Adding a Web Custom Control to the Project

In Solution Explorer, right click the CustomControls project, and select Add New Item from the context menu. In the Add New Item dialog box, choose the category type to be a Web category, and select ASP.NET Server Control in the templates.

Name the new custom control VideoPlayer. Click Add. The new custom control (VideoPlayer.cs) is created and added to the CustomControls project.

1. using System;  
2. using System.Collections.Generic;  
3. using System.ComponentModel;  
4. using System.Linq;  
5. using System.Text;  
6. using System.Web;  
7. using System.Web.UI;  
8. using System.Web.UI.WebControls;  
9.   
10. namespace CustomControls  
11. {  
12.     [DefaultProperty("Text")]  
13.     [ToolboxData("<{0}:VideoPlayer runat=server></{0}:VideoPlayer>")]  
14.     public class VideoPlayer : WebControl  
15.     {  
16.         [Bindable(true)]  
17.         [Category("Appearance")]  
18.         [DefaultValue("")]  
19.         [Localizable(true)]  
20.         public string Text  
21.         {  
22.             get  
23.             {  
24.                 String s = (String)ViewState["Text"];  
25.                 return ((s == null) ? "[" + this.ID + "]" : s);  
26.             }  
27.   
28.             set  
29.             {  
30.                 ViewState["Text"] = value;  
31.             }  
32.         }  
33.   
34.         protected override void RenderContents(HtmlTextWriter output)  
35.         {  
36.             output.Write(Text);  
37.         }  
38.     }  
39. }  
40.           

The code above is the default code generated by Visual Studio for a web control library. To start working with VideoPlayer.cs, we need to modify the code above. The first thing that we should do is delete everything between the class declaration line and the end of the class. That leaves us with this code:

1. using System;  
2. using System.Collections.Generic;  
3. using System.ComponentModel;  
4. using System.Linq;  
5. using System.Text;  
6. using System.Web;  
7. using System.Web.UI;  
8. using System.Web.UI.WebControls;  
9.   
10. namespace CustomControls  
11. {  
12.     [DefaultProperty("Text")]  
13.     [ToolboxData("<{0}:VideoPlayer runat=server></{0}:VideoPlayer>")]  
14.     public class VideoPlayer : WebControl  
15.     {  
16.           
17.     }  
18. }  
19.           

As you see above, the VideoPlayer class derives from the System.Web.UI.WebControl class. In fact, all ASP.NET server-side controls derive from the WebControl class.

---

Step 3: Modifying the Class Declaration Line

The class declaration line in the default code also specifies the default property for the VideoPlayer control as the Text property. The VideoPlayer control that we create here doesn't have a property called Text. So, delete the reference to Text as the default property. After all the modifications, the VideoPlayer.cs code file should look like this:

1. using System;  
2. using System.Collections.Generic;  
3. using System.ComponentModel;  
4. using System.Linq;  
5. using System.Text;  
6. using System.Web;  
7. using System.Web.UI;  
8. using System.Web.UI.WebControls;  
9.   
10. namespace CustomControls  
11. {  
12.     [ToolboxData("<{0}:VideoPlayer runat=server></{0}:VideoPlayer>")]  
13.     public class VideoPlayer : WebControl   
14.     {  
15.   
16.     }  
17. }  
18.           

---

Step 4: Adding Properties

In this step, we will add some properties to the VideoPlayer control to handle the control's behaviour. The following is the list of properties that we will add to the VideoPlayer.cs code file:

• VideoUrl: A string property which specifies the URL of the video to play.
• PosterUrl: A string property which specifies the address of an image file to show while no video data is available.
• AutoPlay: A boolean property to specify whether the video should automatically start playing or not, when the webpage is opened.
• DisplayControlButtons: A boolean property that specifies whether the player navigation buttons are displayed or not.
• Loop: A boolean property that specifies whether the video will start over again or not, every time it is finished.

Add the following code to the VideoPlayer class:

1. private string _Mp4Url;  
2. public string Mp4Url  
3. {  
4.     get { return _Mp4Url; }  
5.     set { _Mp4Url = value; }  
6. }  
7.   
8. private string _OggUrl = null;  
9. public string OggUrl  
10. {  
11.     get { return _OggUrl; }  
12.     set { _OggUrl = value; }  
13. }  
14.   
15. private string _Poster = null;  
16. public string PosterUrl  
17. {  
18.     get { return _Poster; }  
19.     set { _Poster = value; }  
20. }  
21.   
22. private bool _AutoPlay = false;  
23. public bool AutoPlay  
24. {  
25.     get { return _AutoPlay; }  
26.     set { _AutoPlay = value; }  
27. }  
28.   
29. private bool _Controls = true;  
30. public bool DisplayControlButtons  
31. {  
32.     get { return _Controls; }  
33.     set { _Controls = value; }  
34. }  
35.   
36. private bool _Loop = false;  
37. public bool Loop  
38. {  
39.     get { return _Loop; }  
40.     set { _Loop = value; }  
41. }  
42.           

After we have added the above properties, the VideoPlayer class should look like

1. using System;  
2. using System.Collections.Generic;  
3. using System.ComponentModel;  
4. using System.Linq;  
5. using System.Text;  
6. using System.Web;  
7. using System.Web.UI;  
8. using System.Web.UI.WebControls;  
9.   
10. namespace CustomControls  
11. {  
12.     [ToolboxData("<{0}:VideoPlayer runat=server></{0}:VideoPlayer>")]  
13.     public class VideoPlayer : WebControl   
14.     {  
15.         private string _Mp4Url;  
16.         public string Mp4Url  
17.         {  
18.             get { return _Mp4Url; }  
19.             set { _Mp4Url = value; }  
20.         }  
21.   
22.         private string _OggUrl = null;  
23.         public string OggUrl  
24.         {  
25.             get { return _OggUrl; }  
26.             set { _OggUrl = value; }  
27.         }  
28.   
29.         private string _Poster = null;  
30.         public string PosterUrl  
31.         {  
32.             get { return _Poster; }  
33.             set { _Poster = value; }  
34.         }  
35.   
36.         private bool _AutoPlay = false;  
37.         public bool AutoPlay  
38.         {  
39.             get { return _AutoPlay; }  
40.             set { _AutoPlay = value; }  
41.         }  
42.   
43.         private bool _Controls = true;  
44.         public bool DisplayControlButtons  
45.         {  
46.             get { return _Controls; }  
47.             set { _Controls = value; }  
48.         }  
49.   
50.         private bool _Loop = false;  
51.         public bool Loop  
52.         {  
53.             get { return _Loop; }  
54.             set { _Loop = value; }  
55.         }  
56.     }  
57. }  
58.           

---

Step 5: Creating the RenderContents Method

The primary job of a server control is to render some type of markup language to the HTTP output stream, which is returned to and displayed by the client. It is our responsibility as the control developer to tell the server control what markup to render. The overridden RenderContents method is the primary location where we tell the control what we want to render to the client.
Add the following override RenderContents method to the VideoPlayer class:

1. protected override void RenderContents(HtmlTextWriter output)  
2. {  
3.               
4. }  
5.           

Notice that the RenderContents method has one method parameter called output. This parameter is an HtmlTextWriter object, which is what the control uses to render HTML to the client. The HtmlTextwriter class has a number of methods you can use to render your HTML, including AddAttribute and RenderBeginTag.

---

Step 6: Adding Tag Attributes

Before we write the code to render the <video> element, the first thing to do is add some attributes for it. We can use the AddAttribute method of the HtmlTextWriter object to add attributes for HTML tags.
Append the following code into the RenderContents method:

1. output.AddAttribute(HtmlTextWriterAttribute.Id, this.ID);  
2. output.AddAttribute(HtmlTextWriterAttribute.Width, this.Width.ToString());  
3. output.AddAttribute(HtmlTextWriterAttribute.Height, this.Height.ToString());  
4.   
5. if (DisplayControlButtons == true)  
6. {  
7.     output.AddAttribute("controls", "controls");  
8. }  
9.               
10. if (PosterUrl != null)  
11. {  
12.     output.AddAttribute("poster", PosterUrl);  
13. }  
14.               
15. if (AutoPlay == true)  
16. {  
17.     output.AddAttribute("autoplay", "autoplay");  
18. }  
19.               
20. if (Loop == true)  
21. {  
22.     output.AddAttribute("loop", "loop");  
23. }  
24.           

You can see that, by using the AddAttribute method, we have added seven attributes to the tag. Also notice that we are using an enumeration, HtmlTextWriterAttribute, to select the attribute we want to add to the tag.
After we have added the code above, the RenderContents method should look like so:

1. protected override void RenderContents(HtmlTextWriter output)  
2. {  
3.     output.AddAttribute(HtmlTextWriterAttribute.Id, this.ID);  
4.     output.AddAttribute(HtmlTextWriterAttribute.Width, this.Width.ToString());  
5.     output.AddAttribute(HtmlTextWriterAttribute.Height, this.Height.ToString());  
6.       
7.     if (DisplayControlButtons == true)  
8.     {  
9.         output.AddAttribute("controls", "controls");  
10.     }  
11.               
12.     if (PosterUrl != null)  
13.     {  
14.         output.AddAttribute("poster", PosterUrl);  
15.     }  
16.               
17.     if (AutoPlay == true)  
18.     {  
19.         output.AddAttribute("autoplay", "autoplay");  
20.     }  
21.               
22.     if (Loop == true)  
23.     {  
24.         output.AddAttribute("loop", "loop");  
25.     }  
26. }  
27.           

---

Step 7: Rendering the <video> Element

After adding some tag attributes for the video element, it's time to render the <video> tag with its attributes onto the HTML document. Add the following code into the RenderContents method:

1. output.RenderBeginTag("video");  
2. if (OggUrl != null)  
3. {  
4.     output.AddAttribute("src", OggUrl);  
5.     output.AddAttribute("type", "video/ogg");  
6.     output.RenderBeginTag("source");  
7.     output.RenderEndTag();  
8. }  
9.   
10. if (Mp4Url != null)  
11. {  
12.     output.AddAttribute("src", Mp4Url);  
13.     output.AddAttribute("type", "video/mp4");  
14.     output.RenderBeginTag("source");  
15.     output.RenderEndTag();  
16. }  
17. output.RenderEndTag();  
18.           

We use the RenderBeginTag method of output object to render the opening tag of the video element, and RenderEndTag to render its closing tag. We also added the <source> element between the <video> element. The video element allows multiple source elements. Source elements can link to different video files. The browser will use the first recognized format.
The RenderContents method should look like this after we have added the code above

1. protected override void RenderContents(HtmlTextWriter output)  
2. {  
3.     output.AddAttribute(HtmlTextWriterAttribute.Id, this.ID);  
4.     output.AddAttribute(HtmlTextWriterAttribute.Width, this.Width.ToString());  
5.     output.AddAttribute(HtmlTextWriterAttribute.Height, this.Height.ToString());  
6.       
7.     if (DisplayControlButtons == true)  
8.     {  
9.         output.AddAttribute("controls", "controls");  
10.     }  
11.               
12.     if (PosterUrl != null)  
13.     {  
14.         output.AddAttribute("poster", PosterUrl);  
15.     }  
16.               
17.     if (AutoPlay == true)  
18.     {  
19.         output.AddAttribute("autoplay", "autoplay");  
20.     }  
21.               
22.     if (Loop == true)  
23.     {  
24.         output.AddAttribute("loop", "loop");  
25.     }  
26.       
27.     output.RenderBeginTag("video");  
28.     if (OggUrl != null)  
29.     {  
30.         output.AddAttribute("src", OggUrl);  
31.         output.AddAttribute("type", "video/ogg");  
32.         output.RenderBeginTag("source");  
33.         output.RenderEndTag();  
34.         }  
35.   
36.     if (Mp4Url != null)  
37.     {  
38.         output.AddAttribute("src", Mp4Url);  
39.         output.AddAttribute("type", "video/mp4");  
40.         output.RenderBeginTag("source");  
41.         output.RenderEndTag();  
42.     }  
43.     output.RenderEndTag();  
44. }  
45.           

Notice that the order in which we place the AddAttributes methods is important. We place the AddAttributes methods directly before the RenderBeginTag method in the code. The AddAttributes method associates the attributes with the next HTML tag that is rendered by the RenderBeginTag method, in this case the video tag.

---

Step 8: Removing the Span Tag

By default, ASP.NET will surround the control tag with a <span> element when rendering the control's HTML markup. If we have provided an ID value for our control, then the Span tag will also, by default, render an ID attribute. Having the tags can sometimes be problematic, so if we want to prevent this in ASP.NET, we can simply override the Render method and call the RenderContents method directly. Here's how to do that:

1. protected override void Render(HtmlTextWriter writer)  
2. {  
3.     this.RenderContents(writer);  
4. }  
5.           

After we have added the code above, the VideoPlayer class should look like this:

1. using System;  
2. using System.Collections.Generic;  
3. using System.ComponentModel;  
4. using System.Linq;  
5. using System.Text;  
6. using System.Web;  
7. using System.Web.UI;  
8. using System.Web.UI.WebControls;  
9.   
10. namespace CustomControls  
11. {  
12.     [ToolboxData("<{0}:VideoPlayer runat=server></{0}:VideoPlayer>")]  
13.     public class VideoPlayer : WebControl   
14.     {  
15.         private string _Mp4Url;  
16.         public string Mp4Url  
17.         {  
18.             get { return _Mp4Url; }  
19.             set { _Mp4Url = value; }  
20.         }  
21.   
22.         private string _OggUrl = null;  
23.         public string OggUrl  
24.         {  
25.             get { return _OggUrl; }  
26.             set { _OggUrl = value; }  
27.         }  
28.   
29.         private string _Poster = null;  
30.         public string PosterUrl  
31.         {  
32.             get { return _Poster; }  
33.             set { _Poster = value; }  
34.         }  
35.   
36.         private bool _AutoPlay = false;  
37.         public bool AutoPlay  
38.         {  
39.             get { return _AutoPlay; }  
40.             set { _AutoPlay = value; }  
41.         }  
42.   
43.         private bool _Controls = true;  
44.         public bool DisplayControlButtons  
45.         {  
46.             get { return _Controls; }  
47.             set { _Controls = value; }  
48.         }  
49.   
50.         private bool _Loop = false;  
51.         public bool Loop  
52.         {  
53.             get { return _Loop; }  
54.             set { _Loop = value; }  
55.         }  
56.   
57.         protected override void RenderContents(HtmlTextWriter output)  
58.         {  
59.             output.AddAttribute(HtmlTextWriterAttribute.Id, this.ID);  
60.             output.AddAttribute(HtmlTextWriterAttribute.Width, this.Width.ToString());  
61.             output.AddAttribute(HtmlTextWriterAttribute.Height, this.Height.ToString());  
62.             if (DisplayControlButtons == true)  
63.             {  
64.                 output.AddAttribute("controls", "controls");  
65.             }  
66.               
67.             if (PosterUrl != null)  
68.             {  
69.                 output.AddAttribute("poster", PosterUrl);  
70.             }  
71.               
72.             if (AutoPlay == true)  
73.             {  
74.                 output.AddAttribute("autoplay", "autoplay");  
75.             }  
76.               
77.             if (Loop == true)  
78.             {  
79.                 output.AddAttribute("loop", "loop");  
80.             }  
81.               
82.             output.RenderBeginTag("video");  
83.             if (OggUrl != null)  
84.             {  
85.                 output.AddAttribute("src", OggUrl);  
86.                 output.AddAttribute("type", "video/ogg");  
87.                 output.RenderBeginTag("source");  
88.                 output.RenderEndTag();  
89.             }  
90.   
91.             if (Mp4Url != null)  
92.             {  
93.                 output.AddAttribute("src", Mp4Url);  
94.                 output.AddAttribute("type", "video/mp4");  
95.                 output.RenderBeginTag("source");  
96.                 output.RenderEndTag();  
97.             }  
98.             output.RenderEndTag();  
99.         }  
100.   
101.         protected override void Render(HtmlTextWriter writer)  
102.         {  
103.             this.RenderContents(writer);  
104.         }  
105.     }  
106. }  
107.           

Our control is now finished! All we have left to do is build the project before we use it on a ASP.NET web page.

---

Step 9: Building the Project

It's time to build the project. Select Build, and then click Build Solution from the main menu.

After building the project, the next step is to add the VideoPlayer control into the Toolbox Explorer.

---

Step 10: Adding VideoPlayer Control to the Visual Studio Toolbox

• To add the VideoPlayer control to the Toolbox, right click in the Toolbox Explorer
• Choose Items from the context menu
• Click the Browse button in the Choose Toolbox Items dialog box
• Navigate to the ASP.NET project directory
• Go to the CustomControls directory
• Open the Bin\Debug directory (Visual Studio builds debug versions by default.)
• Select the CustomControls.DLL assembly and click on the Open button

VideoPlayer will appear in the Choose Toolbox Items dialog box as shown in the image above. The check box will show it as selected. As soon as you click the OK button in the Choose Toolbox Items dialog box, the new VideoPlayer control will appear in the toolbox.

---

Step 11: Placing the VideoPlayer Control on ASP.NET Web Page

To see how the control works, we need to give it a home. Add a new page to the website. Right click the ASP.NET project from the Solution Explorer. Select Add New Item, and add a Web Form. Name the Web Form VideoPlayerTest.aspx.
To place the control on the page, switch to Design mode. Drag the VideoPlayer control from the Toolbox and drop it onto the VideoPlayerTest.aspx design view.
The following Listing shows how the control is declared on the page:

1. <cc1:VideoPlayer ID="VideoPlayer1" runat="server" Mp4Url="videos/movie.mp4" OggUrl="videos/movie.ogg" Width="400" Height="300" />  
2.           

The following line of code is what Visual Studio added to the ASPX file to accommodate the control. You can see it by selecting the Source tab from the bottom of the code window in Visual Studio. The Register directive tells the ASP.NET runtime where to find the custom control (which assembly) and maps it to a tag prefix.

1. <%@ Register assembly="CustomControls" namespace="CustomControls" tagprefix="cc1" %>  
2.           

We can now test the control.

VideoPlayer control running on Google Chrome.

---

Summary

In this tutorial, you learned how to create your own ASP.NET custom server control from scratch. You now know every step of the process – from how to create a web custom control library project, how to add properties to a custom control, how to render the HTML markup of the control to the client, and, finally, how to use the ASP.NET custom control in a web form.
Hopefully, you now have the skills to create custom controls that have all the functionality of the standard ASP.NET server-side controls. Thank you so much for reading!

Remove rows having swapped column values in SQL Server

I have a table like + ----------------------- + | RowID | FromCol | toCol | + ----------------------- + | 1 | a | b | | 2 | b | c | | 3 | c | d | | 4 | c | b | | 5 | b | a | + ----------------------- + I would like to remove the rows that has FromCol --> ToCol same value as ToCol --> FromCol For eg. RowID 1 is a-->b and RowID 5 has b-->a so rowID 5 should be removed. Similarly RowID 4 should be removed because it has a swapped value like RowID 2. My expected result Table is: + ----------------------- + | RowID | FromCol | toCol | + ----------------------- + | 1 | a | b | | 2 | b | c | | 3 | c | d | + ----------------------- + Above can be achieved by the below query   select t1.* FROM dbo.MyTest t1 WHERE (SELECT COUNT(t2.rowid) FROM dbo.MyTest t2 WHERE t2.toCol= t1.fromCol AND t2.fromCol= t1.toCol AND t1.rowid> t2.rowid) = 0