C#.Net Basic Design Patterns

.Net Basic Design Patterns:

Creational Patterns
Abstract Factory	Creates an instance of several families of classes
Builder	Separates object construction from its representation
Factory Method	Creates an instance of several derived classes
Prototype	A fully initialized instance to be copied or cloned
Singleton	A class of which only a single instance can exist

Structural Patterns
Adapter	Match interfaces of different classes
Bridge	Separates an object’s interface from its implementation
Composite	A tree structure of simple and composite objects
Decorator	Add responsibilities to objects dynamically
Facade	A single class that represents an entire subsystem
Flyweight	A fine-grained instance used for efficient sharing
Proxy	An object representing another object

Behavioral Patterns
Chain of Resp.	A way of passing a request between a chain of objects
Command	Encapsulate a command request as an object
Interpreter	A way to include language elements in a program
Iterator	Sequentially access the elements of a collection
Mediator	Defines simplified communication between classes
Memento	Capture and restore an object's internal state
Observer	A way of notifying change to a number of classes
State	Alter an object's behavior when its state changes
Strategy	Encapsulates an algorithm inside a class
Template Method	Defer the exact steps of an algorithm to a subclass
Visitor	Defines a new operation to a class without change

How to Convert XML string into SQL XML object

Convert XML string into SQL XML object

Create a proc in the Data base like..

create proc InsertXMLValue 

(@XMLString XML)
as
begin
-- Code to insert into the specific table which actually have the XML datatype column

end

And call this proc from the applicatio, use the SQL command object to call this proc and create a parameter like...
SqlParameter sqlParam = new SqlParameter("@XMLString", SqlDbType.Xml);
sqlParam.Value = strXML;

Pass the string value to the strXML variable.

How to sort a page or chapter index numbers stored in a nvarchar fileld?

SQL - How to sort a page or chapter index numbers stored in a varchar fileld

1.3.1
1.3.2
1.3.3
1.1.3
1.10
1.10.1
1.10.2
1.10.3
1.2
1.2.1
1.2.10
1.2.11


Here is the solution for this...

-- Sample table with page or chapeter index to sort

SELECT

'1.1.0' PageIndex INTO #MY_TESTUNION SELECT '1.3.1' UNION SELECT '1.3.2' UNION SELECT '1.3.3' UNION SELECT '1.1.3' UNION SELECT '1.10' UNION SELECT '1.10.1' UNION SELECT '1.10.2' UNION SELECT '1.10.3' UNION SELECT '1.2' UNION SELECT '3.10.1'UNION SELECT '3.9.1'UNION SELECT '11.1.0'

-- UD Function to sort the index numbers
create function dbo.SortNumber(@indexnum varchar(50))returns varchar(50)as
begindeclare @curlength int, @length intdeclare @rtnstring varchar(50)select @rtnstring = '', @curlength = 0while (@curlength < len(@indexnum)) -- loop until reach the last "." markbeginset @length = charindex('.', @indexnum, @curlength + 1)if @length = 0set @length = len(@indexnum) + 1-- pading with leading zeroset @rtnstring = @rtnstring + right('0000' + substring(@indexnum, @curlength + 1, @length - @curlength - 1), 4) + '.'set @curlength = @lengthendreturn @rtnstringend

--sample output of this function
SELECT dbo.SortNumber('1.10.1') = 0001.0010.0001.SELECT dbo.SortNumber('1.1.2') = 0001.0001.0002.  

-- final result
SELECT PageIndex,dbo.SortNumber(PageIndex) SortedIndex FROM #MY_TEST ORDER BY SortedIndex

SQL - Get Fiscal Year From Date

In SQL Reporting often we have the scenario where we need to do the reporting based on financial  year. Fiscal year may vary from one organization to another, so feel free to update the function, Currently it set to Australian Financial Year.

 SQL Function:

CREATE FUNCTION [dbo].[fnGetFiscalYear](
@CalenderDate DATETIME)
RETURNS NVARCHAR(10)
AS
BEGIN

DECLARE @FYYear INT
DECLARE @ResultVar NVARCHAR(10)

SET @ResultVar = ''

SELECT @FYYear = CASE WHEN MONTH(@CalenderDate) > 6 --New Fiscal Year stars on the fist day of July
THEN YEAR(@CalenderDate) + 1
ELSE YEAR(@CalenderDate)
END

SELECT @ResultVar  = 'FY-' + RIGHT(@FYYear,2)

RETURN @ResultVar
END


Example:

SELECT GETDATE(), DBO.fnGetFiscalYear(GETDATE())


Result:

2014-11-28 13:52:21.603 FY-15

How to Remove Special Characters Using SQL

Remove Special Characters Using TSQL


CREATE FUNCTION [dbo].[RemoveSpecialCharacters](@StrValue NVARCHAR(4000))
RETURNS NVARCHAR(4000)
AS
BEGIN

    Declare @KeepValues as varchar(50)

SET @StrValue = LTRIM(RTRIM(@StrValue))

    SET @KeepValues = '%[^a-z0-9]%'

    WHILE PATINDEX(@KeepValues, @StrValue) > 0
        SET @StrValue = STUFF(@StrValue, PATINDEX(@KeepValues, @StrValue), 1, '')

    Return @StrValue
END

Result:

SELECT DBO.RemoveSpecialCharacters('Welcome %To $Axil - 007')

WelcomeToAxil007

Remove / Trim Leading Zeros - SQL Server

TSQL - Removing leading zeros from varchar value:

How to remove leading zeros from a number using TSQL

ALTER FUNCTION [dbo].[RemoveLeadingZero] (@StrValue NVARCHAR(4000))
RETURNS NVARCHAR(4000)
AS
BEGIN
SET @StrValue = ISNULL(@StrValue,'')

SET @StrValue =  SUBSTRING(@StrValue, PATINDEX('%[^0]%', @StrValue+'.'), LEN(@StrValue))
   
Return @StrValue
END

Result:

SELECT DBO.RemoveLeadingZero('00050407')

50407

SELECT DBO.RemoveLeadingZero('00050470')

50470

SELECT DBO.RemoveLeadingZero('000.5430')

.5430