Tag Archives: WF

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)    {    }}

C# string comparison

Console.WriteLine(TestEquals(null));

static bool TestEquals(string value){    if (value.Equals("test"))    {        return true;    }    else    {        return false;    }}

Result:

Unhandled Exception: System.NullReferenceException: Object reference not set to an instance of an object.

Corrected version:

static bool TestEquals(string value){    bool isEqual = false;    if (!string.IsNullOrEmpty(value))    {        if (value.Equals("test"))        {            isEqual = true;        }        else        {            isEqual = false;        }    }    return isEqual;}

Another approach using string.Equals:

static bool TestEquals(string value){    if (string.Equals(value, "test"))    {        return true;    }    else    {        return false;    }}

Short version:

static bool TestEquals(string value){    return string.Equals(value, "test");}

Result:

False

Test once more:

Console.WriteLine(TestEquals("test"));

Result:

True

C# ternary operator (?:) and Null Coalescing Operator (??)

Using if statement

if (value != null){    litCurrentSurveyName.Text = value;}else{    litCurrentSurveyName.Text = string.Empty;}

Using ternary operator (?:)

litCurrentSurveyName.Text = value != null ? value : string.Empty;

null-coalescing operator (??) which is a shortcut for the ternary operator (?:)

litCurrentSurveyName.Text = value ?? string.Empty;

SQL Multiple REPLACE

ALTER FUNCTION [dbo].[ReplacePattern](     @P_Pattern VARCHAR(50)) RETURNS NVARCHAR(255)AS BEGIN                         SELECT                     @P_Pattern = REPLACE(@P_Pattern,  '1', 'Sundays'),                    @P_Pattern = REPLACE(@P_Pattern,  '2', 'Mondays'),                    @P_Pattern = REPLACE(@P_Pattern,  '3', 'Tuesdays'),                    @P_Pattern = REPLACE(@P_Pattern,  '4', 'Wednesdays'),                    @P_Pattern = REPLACE(@P_Pattern,  '5', 'Thursdays'),                    @P_Pattern = REPLACE(@P_Pattern,  '6', 'Fridays'),                    @P_Pattern = REPLACE(@P_Pattern,  '7', 'Saturdays')    RETURN          @P_PatternEND

Usage:

SELECT dbo.ReplacePattern('1,2,3,4')

Output:

C# How to round fraction to 15

private double RoundOffset(double offset){    double value = Math.Truncate(offset);    double fraction = offset - Math.Truncate(offset);    if (fraction < 0.25) {        return value + 0.0;    } else if (fraction >= 0.25 && fraction < 0.5) {        return value + 0.25;    } else if (fraction >= 0.5 && fraction < 0.75) {        return value + 0.5;    } else if (fraction >= 0.75 && fraction < 1) {        return value + 0.75;    }    return fraction;}

Usage:

double test = 1.17; Console.WriteLine(test + ": " + RoundOffset(test));

Output:

1.17: 1.15

 

SQL Select rows with date within another date range

CREATE TABLE TestTable(   ID           INT PRIMARY KEY,   StartDate    SMALLDATETIME,   EndDate      SMALLDATETIME);insert into TestTable(ID,StartDate,EndDate) values (1,'2000-11-11 10:11:11', '2000-11-11 13:11:11');insert into TestTable(ID,StartDate,EndDate) values (2,'2000-11-11 10:22:22', '2000-11-11 14:11:11');insert into TestTable(ID,StartDate,EndDate) values (3,'2000-11-11 10:32:22', '2000-11-11 14:11:11');insert into TestTable(ID,StartDate,EndDate) values (4,'2000-11-11 11:01:11', '2000-11-11 15:11:11');insert into TestTable(ID,StartDate,EndDate) values (5,'2011-02-02 22:22:22', '2011-02-22 22:22:22');insert into TestTable(ID,StartDate,EndDate) values (6,'2000-11-11 11:32:22', '2000-11-11 14:11:11');SELECT * FROM TestTable