Monthly Archives: April 2011

SQL Dynamic ORDER BY (ORDER BY CASE clause)

ORDER BY CASE     WHEN @OrderBy = 0 THEN LastUpdated    WHEN @OrderBy = 1 THEN (SELECT COUNT(*) FROM Threads WHERE Id = @Id)     WHEN @OrderBy = 2 THEN IsStickyEND DESC

For WHEN @OrderBy = 2 I wanted to add second column but adding it after a comma didn’t work:

WHEN @OrderBy = 2 THEN IsSticky, LastUpdated

The solution was to create separate CASE block after a comma

ORDER BY CASE     WHEN @OrderBy = 0 THEN LastUpdated    WHEN @OrderBy = 1 THEN (SELECT COUNT(*) FROM Threads WHERE Id = @Id)     WHEN @OrderBy = 2 THEN IsStickyEND DESC,CASE     WHEN @OrderBy=2 THEN LastUpdatedEND DESC

ASP.NET Localization: Web Page Content Form title

Web Page directive:

<%@ Page Language="C#" MasterPageFile="~/Pages/MasterPage.master" AutoEventWireup="true" CodeBehind="Page.aspx.cs"     Inherits="Page"     Culture="auto" meta:resourcekey="PageResource" UICulture="auto"%><asp:Content ID="Content1" ContentPlaceHolderID="head" runat="server"></asp:Content>

Master Page’s Placeholder:

<asp:Content ID="Content1" ContentPlaceHolderID="head" runat="server">    <asp:ContentPlaceHolder ID="head" runat="server">    </asp:ContentPlaceHolder></asp:Content>

  1. Create a resource file. Name must much with Web Page file name, ie: if our page is Page.aspx, resource file name will be named as Page.aspx.resx
  2. Create new key for Page title, which maches resourcekey value set in the Web Page (meta:resourcekey=”PageResource”)
  3. Set its Title attribute and value, ie:

      <data name="PageResource.Title" xml:space="preserve">    <value>Page title goes here</value>  </data>

jQuery simple AJAX call (XML as output)

<script type="text/javascript">    function AddTag()    {           $('#<%= btnAddTag.ClientID %>').text('loading...');        $.ajax({                 url: "/webService/Content.asmx/SuggestKeywordsForTopicContent",                 //type: "POST",                 dataType: "xml",                data: {                        content: $('#<%= DetailsTextBox.ClientID %>').val(),                        recordCount: 20                },                success: function(xmlResponse)                {                     $('#<%= btnAddTag.ClientID %>').text('Add Tag');                    $(xmlResponse).find('Text').each(function(){                        var arrayVal =     $(this).text();                        alert(arrayVal);                    });                }              });    }</script>

SQL Merge Multiple Rows Into Single String

SELECT   Column1 FROM     SampleTableWHERE    ID = 12

Output:

Source: SQL Server Forums

SQL Replace multiple spaces with one

DECLARE         @C_REPLACEMENT VARCHAR(10),        @P_Content VARCHAR(MAX)SELECT             @C_REPLACEMENT = 'þ',        @P_Content = 'Lorizzle         ipsizzle     phat    sit amet   '/*    Latin1_General_CI_AI is used to replace a space with werid character    another approach:    use "XXX" to represent a @C_REPLACEMENT     and then replace "something XXX weird" to "something weird"*/SELECT Replace(Replace(Replace(MyCollation, ' ', ' ' + @C_REPLACEMENT), @C_REPLACEMENT + ' ', ''), @C_REPLACEMENT, '')FROM (        SELECT @P_Content        COLLATE Latin1_General_CI_AI AS MyCollation     ) AS Result

SQL Replace special, punctuation characters

DECLARE @T_NoiseCharacters TABLE        (            NoiseChar VARCHAR(10),             ReplaceChar VARCHAR(10)        )DECLARE @P_String VARCHAR(MAX)SET     @P_String = '$!Maecenas & sure mi. Nam (erizzle)'INSERT INTO @T_NoiseCharacters(NoiseChar, ReplaceChar)SELECT '_',SPACE(1) UNION ALL SELECT '@',SPACE(1) UNION ALLSELECT '!',SPACE(0) UNION ALL SELECT '#',SPACE(0) UNION ALL SELECT '$',SPACE(0) UNION ALLSELECT '%',SPACE(0) UNION ALL SELECT '^',SPACE(0) UNION ALLSELECT '&',SPACE(1) UNION ALL SELECT '*',SPACE(1) UNION ALLSELECT '(',SPACE(1) UNION ALL SELECT ')',SPACE(1) UNION ALLSELECT '{',SPACE(1) UNION ALL SELECT '}',SPACE(1) UNION ALLSELECT '<',SPACE(1) UNION ALL SELECT '>',SPACE(1) UNION ALLSELECT ':',SPACE(1)SELECT @P_String = REPLACE(@P_String, NoiseChar, ReplaceChar) FROM @T_NoiseCharactersSELECT @P_String Result

SQL Using CASE Statement to SET variable

DECLARE         @UserName as NVARCHAR(50),        @Count AS INTSET         @UserName = 'test'SET @Count =     (        SELECT             CASE                 WHEN @UserName <> ''                THEN (SELECT COUNT(*) FROM dbo.GetUserFavourites(@UserName))                ELSE 0        END    )SELECT @Count AS [Count]   

SQL Select Only Date Part From DateTime

Query

SELECT GETDATE()

returns date with time

C# System.IO.Path

Combine a path name and file name problem: either you expect or do not expect the path to have a trailing path separator (‘’) character. To make life easier use the Path.Combine() static method that can take care of that:

// combines a series of path name components into one pathstring fullPath = Path.Combine(directoryName, fileName);

Get path name to a file, the file name, the path itself, the extension:

string fullPath = "c:\Music\Christina_Aguilera\Fighter.mp3";// gets "c:"string pathPart = Path.GetPathRoot(fullPath);// gets "Fighter.mp3"string filePart = Path.GetFileName(fullPath);// gets ".mp3"string extPart = Path.GetExtension(fullPath);// gets "c:MusicChristina_Aguilera"string dirPart = Path.GetDirectoryName(fullPath);

C#/ASP.NET simple error logging

ErrorLogging.cs

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.IO;    public class ErrorLogging    {        public void WriteError(string ErrorMessage)        {            try            {                //for ASP.NET website                //string errorsDirectory = HttpContext.Current.Server.MapPath("/log/");                string errorsDirectory = string.Format("{0}\errors", Directory.GetCurrentDirectory());                if (!Directory.Exists(errorsDirectory))                {                    System.IO.Directory.CreateDirectory(errorsDirectory);                }                string LogName = DateTime.Now.Year.ToString() + "-" + DateTime.Now.Month.ToString() + "-" + DateTime.Now.Day.ToString() + ".txt";                StreamWriter sw = new StreamWriter(string.Format("{0}\{1}", errorsDirectory, LogName));                sw.WriteLine(string.Format("{0} {1}", DateTime.Now.ToShortDateString().ToString(), DateTime.Now.ToLongTimeString().ToString()));                sw.WriteLine("===================================================================================================================");                sw.WriteLine(ErrorMessage);                sw.WriteLine("===================================================================================================================");                sw.Flush();                sw.Close();            }            catch            {            }        }    }

Usage within Console Application:

catch (Exception ex){    ErrorLogging err = new ErrorLogging();    err.WriteError(ex.Message + " TRACE: " + ex.StackTrace);}

Usage within ASP.NET website: add Global.ashx

using System;using System.Collections.Generic;using System.Linq;using System.Web;using System.Web.Security;using System.Web.SessionState;public class Global : System.Web.HttpApplication{    protected void Application_Start(object sender, EventArgs e)    {    }    protected void Session_Start(object sender, EventArgs e)    {    }    protected void Application_BeginRequest(object sender, EventArgs e)    {    }    protected void Application_AuthenticateRequest(object sender, EventArgs e)    {    }    protected void Application_Error(object sender, EventArgs e)    {        try        {            Exception currentException = Server.GetLastError();            ErrorLogging ErrorLog = new ErrorLogging();            ErrorLog.WriteError(currentException.GetBaseException().Message + " TRACE: " + currentException.GetBaseException().StackTrace, Request.RawUrl);        }        catch         { }    }    protected void Session_End(object sender, EventArgs e)    {    }    protected void Application_End(object sender, EventArgs e)    {    }}