Aggregate values of a column when using a group by in oracle

SELECT deptno, wm_concat(ename) AS employees
FROM   emp
GROUP BY deptno;

DEPTNO EMPLOYEES
---------- --------------------------------------------------
10 CLARK,KING,MILLER
20 SMITH,FORD,ADAMS,SCOTT,JONES
30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD

For detailed usage of string aggregation visit http://bit.ly/ixVwcS

Advertisements
Posted in Technology | Leave a comment

Lightweight Rich Text Editor Control in ASP.NET MVC 2 – Part 2

The last post I made was the most basic way of getting that RTE control rendered in ASP.NET MVC. I explored it more and have found a more appropriate way of doing this.

Below is the class for TextEditor Control. I have added two methods TextEditor and TextEditorFor with 2 overloads each. TextEditorFor methods takes expression as input and renders the control deducing name from expression also if the input model contains value in it, control is initialized with that value.

/// <summary>
/// Html helper extension for a rich text editor element
/// </summary>
public static class TextEditorExtention
{
	/// <summary>
	/// Returns a rich text editor element by using the specified HTML helper and the name of the form field.
	/// </summary>
	/// <param name="htmlHelper">The HTML helper instance that this method extends.</param>
	/// <param name="name">The name of the form field and the <see cref="System.Web.Mvc.ViewDataDictionary" /> key that is used to look up the validation errors.</param>
	/// <param name="value">The value to be set in the rich text editor element.</param>
	/// <returns></returns>
	public static MvcHtmlString TextEditor(this HtmlHelper htmlHelper, string name, string value = "")
	{
		return htmlHelper.TextEditor(name, (object)null, value);
	}

	/// <summary>
	/// Returns a rich text editor element by using the specified HTML helper, the name of the form field and the HTML attributes.
	/// </summary>
	/// <param name="htmlHelper">The HTML helper instance that this method extends.</param>
	/// <param name="name">The name of the form field and the <see cref="System.Web.Mvc.ViewDataDictionary" /> key that is used to look up the validation errors.</param>
	/// <param name="htmlAttributes">An object that contains the HTML attributes for the element. The attributes are retrieved through reflection by examining the properties of the object. The object is typically created by using object initializer syntax.</param>
	/// <param name="value">The value to be set in the rich text editor element.</param>
	/// <returns></returns>
	public static MvcHtmlString TextEditor(this HtmlHelper htmlHelper, string name, object htmlAttributes, string value = "")
	{
		return htmlHelper.TextEditor(name, new RouteValueDictionary(htmlAttributes), value);
	}

	/// <summary>
	/// Returns a rich text editor element by using the specified HTML helper, the name of the form field and the HTML attributes.
	/// </summary>
	/// <param name="htmlHelper">The HTML helper instance that this method extends.</param>
	/// <param name="name">The name of the form field and the <see cref="System.Web.Mvc.ViewDataDictionary" /> key that is used to look up the validation errors.</param>
	/// <param name="htmlAttributes">An object that contains the HTML attributes for the element. The attributes are retrieved through reflection by examining the properties of the object. The object is typically created by using object initializer syntax.</param>
	/// <param name="value">The value to be set in the rich text editor element.</param>
	/// <returns></returns>
	public static MvcHtmlString TextEditor(this HtmlHelper htmlHelper, string name, IDictionary<String, Object> htmlAttributes, string value = "")
	{
		var tagBuilder = new TagBuilder("textarea");
		tagBuilder.MergeAttributes(htmlAttributes);
		tagBuilder.MergeAttribute("name", name, true);
		tagBuilder.MergeAttribute("class", "rte", true);
		tagBuilder.GenerateId(name);

		if (!string.IsNullOrEmpty(value))
		{
			tagBuilder.SetInnerText(value);
		}

		ModelState modelState;
		if (htmlHelper.ViewData.ModelState.TryGetValue(name, out modelState))
		{
			if (modelState.Errors.Count > 0)
			{
				tagBuilder.AddCssClass(HtmlHelper.ValidationInputCssClassName);
			}
		}

		string textArea = tagBuilder.ToString(TagRenderMode.Normal);
		return MvcHtmlString.Create(@"<script type=""text/javascript"">
							$(document).ready(function () {
								var arr = $('.rte1').rte({
									css: ['default.css'],
									controls_rte: rte_toolbar,
									controls_html: html_toolbar
								});

								$('.rte').rte({
									css: ['default.css'],
									width: 450,
									height: 200,
									controls_rte: rte_toolbar,
									controls_html: html_toolbar
								}, arr);
							});
							</script>" + textArea);
	}

	/// <summary>
	/// Returns a rich text editor element by using the specified HTML helper and the name of the form field as an expression.
	/// </summary>
	/// <typeparam name="TModel">The type of the model.</typeparam>
	/// <typeparam name="TValue">The type of the value.</typeparam>
	/// <param name="htmlHelper">The HTML helper instance that this method extends.</param>
	/// <param name="expression">The expression that resolves to the name of the form field and the <see cref="System.Web.Mvc.ViewDataDictionary" /> key that is used to look up the validation errors.</param>
	/// <param name="value">The value to be set in the rich text editor element.</param>
	/// <returns></returns>

	public static MvcHtmlString TextEditorFor<TModel, TValue>(this HtmlHelper<TModel> htmlHelper, Expression<Func<TModel, TValue>> expression, string value = "")
	{
		return htmlHelper.TextEditorFor<TModel, TValue>(expression, (object)null, value);
	}

	/// <summary>
	/// Returns a rich text editor element by using the specified HTML helper and the name of the form field as an expression.
	/// </summary>
	/// <typeparam name="TModel">The type of the model.</typeparam>
	/// <typeparam name="TValue">The type of the value.</typeparam>
	/// <param name="htmlHelper">The HTML helper instance that this method extends.</param>
	/// <param name="expression">The expression that resolves to the name of the form field and the <see cref="System.Web.Mvc.ViewDataDictionary" /> key that is used to look up the validation errors.</param>
	/// <param name="htmlAttributes">An object that contains the HTML attributes for the element. The attributes are retrieved through reflection by examining the properties of the object. The object is typically created by using object initializer syntax.</param>
	/// <param name="value">The value to be set in the rich text editor element.</param>
	/// <returns></returns>
	public static MvcHtmlString TextEditorFor<TModel, TValue>(this HtmlHelper<TModel> htmlHelper, Expression<Func<TModel, TValue>> expression, object htmlAttributes, string value = "")
	{
		return htmlHelper.TextEditorFor<TModel, TValue>(expression, new RouteValueDictionary(htmlAttributes), value);
	}

	/// <summary>
	/// Returns a rich text editor element by using the specified HTML helper and the name of the form field as an expression.
	/// </summary>
	/// <typeparam name="TModel">The type of the model.</typeparam>
	/// <typeparam name="TValue">The type of the value.</typeparam>
	/// <param name="htmlHelper">The HTML helper instance that this method extends.</param>
	/// <param name="expression">The expression that resolves to the name of the form field and the <see cref="System.Web.Mvc.ViewDataDictionary" /> key that is used to look up the validation errors.</param>
	/// <param name="htmlAttributes">An object that contains the HTML attributes for the element. The attributes are retrieved through reflection by examining the properties of the object. The object is typically created by using object initializer syntax.</param>
	/// <param name="value">The value to be set in the rich text editor element.</param>
	/// <returns></returns>
	public static MvcHtmlString TextEditorFor<TModel, TValue>(this HtmlHelper<TModel> htmlHelper, Expression<Func<TModel, TValue>> expression, IDictionary<String, Object> htmlAttributes, string value = "")
	{
		var name = ExpressionHelper.GetExpressionText(expression);
		object model = ModelMetadata.FromLambdaExpression(expression, htmlHelper.ViewData).Model;
		if (string.IsNullOrEmpty(value))
		{
			value = model.ToString();
		}

		return htmlHelper.TextEditor(name, htmlAttributes, value);
	}
}

We can use it as follows

<%= Html.TextEditor("txtContent") %>
<%= Html.TextEditorFor(model => model.Content) %>

You can download Lightweight Rich Text Editor plug-in from here.

Posted in Technology | Tagged , , , | Leave a comment

Lightweight Rich Text Editor Control in ASP.NET MVC 2

There is a Lightweight Rich Text Editor Control on jQuery. I used this in a personal project in MVC, it made my otherwise clean pages messed up so I extented it to a control.

Following are the files which came in jQuery RTE plug-in

I added these script reference to master page

public static class TextEditorExtention
{
	public static string TextEditor(this HtmlHelper helper, string ID, string CssClass)
	{
		string textArea = string.Format(@"
							<textarea id=""{0}"" name=""{0}"" class=""{1}""></textarea>",
														ID, CssClass);
		return string.Concat(@"<script type=""text/javascript"">
							$(document).ready(function () {
								var arr = $('.rte').rte({
									css: ['default.css'],
									controls_rte: rte_toolbar,
									controls_html: html_toolbar
								});

								$('.rte').rte({
									css: ['default.css'],
									width: 450,
									height: 200,
									controls_rte: rte_toolbar,
									controls_html: html_toolbar
								}, arr);
							});
							</script>", textArea);
	}
}

This is the code of helper class we write a method TextEditor as an extension method for HtmlHelper class

We use it as follows in aspx pages

<%= Html.TextEditor("txtContent","rte") %>
Posted in Technology | Tagged , , , | Leave a comment

Creating, Installing, Debugging Windows Services

When I started working on windows services, I had some difficulty with how to install it, what are necessary functionalities, how to debug it etc. I am sharing my leaning from a beginner’s point of view that how to create a service from scratch.

Part 1 – Creating a Windows Service Project

You can select Windows Service template on the new project screen in the windows section to create a windows service project.

Create Project Dialog

This is what it first looks like after you create the project.

This is what it first looks like after you create the project.

I changed the default code in the class to the code below, and added a timer as the functionality is to be performed at regular intervals. and custom event log entry to track service actions.

Timer serviceTimer = new Timer();
public PracticeService()
{
    InitializeComponent();
    if (!System.Diagnostics.EventLog.SourceExists("PracticeSource"))
    {
        System.Diagnostics.EventLog.CreateEventSource("PracticeSource","PracticeLog");
    }
    eventLog1.Source = "PracticeSource";
    eventLog1.Log = "PracticeLog";
}

protected override void OnStart(string[] args)
{
    eventLog1.WriteEntry("Practice Service Started", EventLogEntryType.Information);
    InitializeTimer();
}

protected override void OnStop()
{
    eventLog1.WriteEntry("Practice Service Stopped", EventLogEntryType.Information);
    serviceTimer.Dispose();
}

private void InitializeTimer()
{
    try
    {
        if (serviceTimer != null)
        {
            serviceTimer.AutoReset = true;
            serviceTimer.Interval = Convert.ToDouble(60 * 1000);
            serviceTimer.Enabled = true;
            serviceTimer.Elapsed += serviceTimer_Elapsed;
            eventLog1.WriteEntry("Service Timer Initialized", EventLogEntryType.Information);
        }
    }
    catch (Exception ex)
    {
        eventLog1.WriteEntry(ex.Message, EventLogEntryType.Error);
    }
}
protected void serviceTimer_Elapsed(object sender,ElapsedEventArgs e)
{
    //Your Functionality
    eventLog1.WriteEntry("Elapsed Event Called", EventLogEntryType.Information);
}

To install the service with necessary information in itself we need to add an installer file to this project. An installer class can be found under General tab while adding items to the project.

Installer Class

Add the components ServiceProcessInstaller and ServiceInstaller to the Installer Class from the toolbox and initialize their properties

Installer Class Design View

//
// PracticeServiceInstaller
//
this.PracticeServiceInstaller.Description = "This service is created for practice purposes.";
this.PracticeServiceInstaller.DisplayName = "Practice Service";
this.PracticeServiceInstaller.ServiceName = "PracticeService";
this.PracticeServiceInstaller.StartType = System.ServiceProcess.ServiceStartMode.Automatic;
//
// PracticeServiceProcessInstaller
//
this.PracticeServiceProcessInstaller.Account = System.ServiceProcess.ServiceAccount.LocalSystem;
this.PracticeServiceProcessInstaller.Password = null;
this.PracticeServiceProcessInstaller.Username = null;

Above is the code in the designer file of installer class after initializing the properties. If the service requires special permissions you can use a user with same permissions.

Now build the project in Release mode and we are ready for deployment. For debugging build the project in Debug mode.

Part 2 – Installing a Windows Service

To install the service copy the contents of project output folder(debug/release) to the deployment folder in my case it is “G:\Deployments\PracticeService”

Deployment Folder

Now to install the service open command prompt go to .NET framework folder. As we have entered all the required information regarding service in installer, we won’t be needed to enter any information here and installation will be smooth. We run InstallUtil.exe program to install our service, it takes ‘/i’ as first argument for installation and ‘/u’ for uninstall. The second argument is the path to the service, in my case it is “G:\Deployments\PracticeService\PracticeService.exe”.

After installation our service is now visible in Services Manager Screen

Services Manager

When we start the service and let it run for few minutes we can see the PracticeLog created in the Event Viewer. Here we can see the event happening where we have written entry in the event log like serviceTimer_Elpased event.

Event Viewer

Part 3 – Debugging windows service.

Debugging windows service is as easy as debugging any other program. You just have to attach the Visual Studio Debugger to your service. The service may not get displayed at first because by default it shows the service running of current user. To display you service in the debugger just check the checkbox labeled “Show processes from all users”, then you can debug your code. Make sure that the service deployed should be built in Debug mode not the release mode.

Debugging

If you want to debug the service code without deploying it, there is another way by adding a module to the project and calling the service “OnStart” function in it. Set it as the start-up object and you can debug by running the project the usual way.

The information provided here is easily available on the internet if you search for it, I just wanted to accumulate all the this information at one place so as to have a step by step way for a beginner to start working with Windows Services.

Posted in Technology | Tagged , , | Leave a comment

Useful Links Feb-2010

Some useful links that I found this month wandering on the internet thought it would be interesting to share.

  1. 101 Time-Saving Google Tricks for Work, Play and Learning
  2. Gmail Power Tips: How To Integrate Your Facebook, Twitter, Yahoo, Friendfeed In Your Gmail
  3. Why Web Developers Should Switch to Google Chrome
  4. 64 Things Every Geek Should Know
  5. Best Open Source Resources for Web Developers
  6. Six Things Your Mom Never Told You About Debugging Javascript
  7. 15 Essential Checks Before Launching Your Website
Posted in Technology | Tagged , | Leave a comment

Remove Special Characters from a string in SQL Server

Here is a sql script which I found on some forum while searching for the same which I thought is interesting enough to share. This script can be used to remove special characters from a string in SQL Server –

/*********************************
Removes any special characters from 
@inputString that do not meet the 
provided criteria.
*********************************/
CREATE FUNCTION [dbo].[udfGetCharacters](@inputString VARCHAR(MAX), @validChars VARCHAR(100))
RETURNS VARCHAR(500) AS
BEGIN
 
	WHILE @inputString like '%[^' + @validChars + ']%'
		SELECT @inputString = REPLACE(@inputString,SUBSTRING(@inputString,PATINDEX('%[^' + @validChars + ']%',@inputString),1),'')

	RETURN @inputString
END

This function takes input string and the valid characters and replaces all the characters other than those in valid characters string. This function can be used as follows–

--Usage of the function
select [dbo].udfGetCharacters('utkarsh puranik`s blog' ,'0-9a-z ')

--output
utkarsh puraniks blog
Posted in Technology | Tagged , , , , | 3 Comments

Retrieve Values From Querystring on Client-Side

function PageQuery(q) {
    if (q.length > 1) this.q = q.substring(1, q.length);
    else this.q = null;
    this.keyValuePairs = new Array();
    if (q) {
        for (var i = 0; i < this.q.split("&").length; i++) {
            this.keyValuePairs[i] = this.q.split("&")[i];
        }
    }
    this.getKeyValuePairs = function() { return this.keyValuePairs; }
    this.getValue = function(s) {
        for (var j = 0; j < this.keyValuePairs.length; j++) {
            if (this.keyValuePairs[j].split("=")[0] == s)
                return this.keyValuePairs[j].split("=")[1];
        }
        return false;
    }
    this.getParameters = function() {
        var a = new Array(this.getLength());
        for (var j = 0; j < this.keyValuePairs.length; j++) {
            a[j] = this.keyValuePairs[j].split("=")[0];
        }
        return a;
    }
    this.getLength = function() { return this.keyValuePairs.length; }
}

function queryString(key) {
    var page = new PageQuery(window.location.search);
    return unescape(page.getValue(key));
}

Above code can be used to retrieve values from Querystring on the client-side i.e. via JavaScript. Use queryString function to retrieve a value.
e.g.

var value = queryString("Key")
alert(value);
Posted in Technology | Tagged , , | Leave a comment