Asp.net C# , Sqlserver Professional

Get all non-clustered indexes

2020-01-24T04:08:00.000-08:00

DECLARE cIX CURSOR FOR
SELECT OBJECT_NAME(SI.Object_ID), SI.Object_ID, SI.Name, SI.Index_ID
FROM Sys.Indexes SI
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON SI.Name = TC.CONSTRAINT_NAME AND OBJECT_NAME(SI.Object_ID) = TC.TABLE_NAME
WHERE TC.CONSTRAINT_NAME IS NULL
AND OBJECTPROPERTY(SI.Object_ID, 'IsUserTable') = 1
ORDER BY OBJECT_NAME(SI.Object_ID), SI.Index_ID

DECLARE @IxTable SYSNAME
DECLARE @IxTableID INT
DECLARE @IxName SYSNAME
DECLARE @IxID INT

-- Loop through all indexes
OPEN cIX
FETCH NEXT FROM cIX INTO @IxTable, @IxTableID, @IxName, @IxID
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE @IXSQL NVARCHAR(4000) SET @IXSQL = ''
SET @IXSQL = 'CREATE '

-- Check if the index is unique
IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsUnique') = 1)
SET @IXSQL = @IXSQL + 'UNIQUE '
-- Check if the index is clustered
IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsClustered') = 1)
SET @IXSQL = @IXSQL + 'CLUSTERED '

SET @IXSQL = @IXSQL + 'INDEX ' + @IxName + ' ON ' + @IxTable + '('

-- Get all columns of the index
DECLARE cIxColumn CURSOR FOR
SELECT SC.Name
FROM Sys.Index_Columns IC
JOIN Sys.Columns SC ON IC.Object_ID = SC.Object_ID AND IC.Column_ID = SC.Column_ID
WHERE IC.Object_ID = @IxTableID AND Index_ID = @IxID
ORDER BY IC.Index_Column_ID

DECLARE @IxColumn SYSNAME
DECLARE @IxFirstColumn BIT SET @IxFirstColumn = 1

-- Loop throug all columns of the index and append them to the CREATE statement
OPEN cIxColumn
FETCH NEXT FROM cIxColumn INTO @IxColumn
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (@IxFirstColumn = 1)
SET @IxFirstColumn = 0
ELSE
SET @IXSQL = @IXSQL + ', '

SET @IXSQL = @IXSQL + @IxColumn

FETCH NEXT FROM cIxColumn INTO @IxColumn
END
CLOSE cIxColumn
DEALLOCATE cIxColumn

SET @IXSQL = @IXSQL + ')'
-- Print out the CREATE statement for the index
IF @IXSQL != '' BEGIN PRINT @IXSQL END

FETCH NEXT FROM cIX INTO @IxTable, @IxTableID, @IxName, @IxID
END

CLOSE cIX
DEALLOCATE cIX

SQL SERVER – ReIndexing Database Tables

2020-01-24T04:07:00.001-08:00

DECLARE @MyTable VARCHAR(255)
DECLARE myCursor
CURSOR FOR
SELECT '['+TABLE_SCHEMA+'].['+TABLE_NAME+']'
FROM information_schema.tables
WHERE table_type = 'base table'
OPEN myCursor
FETCH NEXT
FROM myCursor INTO @MyTable
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Reindexing Table: ' + @MyTable
DBCC DBREINDEX(@MyTable, '', 80)
FETCH NEXT
FROM myCursor INTO @MyTable
END
CLOSE myCursor
DEALLOCATE myCursor
EXEC sp_updatestats

Calculate number of months between two dates using javascript

2019-05-06T03:06:00.000-07:00

var dtFrom= new Date('01/05/2019');
var dtTo= new Date('01/06/2021');

var fromYear;
var fromMonth = dtFrom.getMonth() + 1;
var toYear;
var toMonth = dtTo.getMonth() + 1;

if ((fromYear = dtFrom.getFullYear()) < (toYear = dtTo.getFullYear())) {
toMonth += (toYear - fromYear) * 12;
}
var diffMonths = toMonth - fromMonth;
if (dtFrom.getDate() > dtTo.getDate()) diffMonths--;

-----------------------------------------------------------------------------
For get only the month diff

var dtFrom= new Date('01/05/2019');
var dtTo= new Date('01/06/2021');

var monthCount = 0;
while ((dtFrom.getMonth() + '' + dtFrom.getFullYear()) != (dtTo.getMonth() + '' + dtTo.getFullYear())) {
monthCount++;
dtFrom.setMonth(dtFrom.getMonth() + 1);
}
monthCount = monthCount + 1;

Remove special characters in a particular column

2019-04-23T00:28:00.001-07:00

DECLARE @myString varchar(500), @validChars varchar(100)
SELECT @myString = 'sA123-\[]{}',@validChars = 'a-z0-9'

While @myString like '%[^' + @validChars + ']%'
Select @myString = replace(@myString,substring(@myString,patindex('%[^' + @validChars + ']%',@myString),1),'')

SELECT @myString AS OutPutString

How can I parse JSON with C#?

2018-12-18T04:16:00.000-08:00

var jData = "{\"validationErrors\":[{\"description\":\"Employer Registration Number not found.\",\"code\":\"1003\",\"path\":\"employerRegistrationNumber\"}]}";

var varData = JObject.Parse(jData);
var varValidationErrorDetails = varData["validationErrors"];
var varValidationErrorDescription = varValidationErrorDetails[0]["description"];
string strValidationErrorDescription = Convert.ToString(varValidationErrorDescription).Replace("\r\n", "").Trim();

Auto Query Generator In MSSQL Server

2018-07-10T22:16:00.000-07:00

Before explaining the script, I want to share the reason to write this script and how it is helping my peers. We have code standard on the database side. Below points are standards.

Need to maintain a separate stored procedure to every table
Don’t use * in the query instead specify the column
Use the correct data type and size of a column
Every parameter should be nullable in a stored procedure.

CREATE PROC [dbo].[USP_QueryCreationSupport] (@table_Name varchar(100))
AS
BEGIN
DECLARE @InserCols NVARCHAR(max)
DECLARE @Inserparam NVARCHAR(max)
DECLARE @Insertquery NVARCHAR(max)
DECLARE @Selectquery NVARCHAR(max)
DECLARE @Update NVARCHAR(max)
DECLARE @DeleteQuery NVARCHAR(max)

SELECT '@' + c.NAME + Space(1) + Case cast(t.Name as nvarchar(40))
WHEN 'nvarchar' THEN
t.NAME + '(' + cast(c.max_length as nvarchar(30)) + ')'
WHEN 'varchar' THEN
t.NAME + '(' + cast(c.max_length as nvarchar(30)) + ')'
WHEN 'char' THEN
t.NAME + '(' + cast(c.max_length as nvarchar(30)) + ')'
WHEN 'decimal' THEN
t.NAME + '(18,2)'
ELSE
t.name
end + ' = null,' AS Params
FROM sys.columns c
inner join sys.types t
ON c.user_type_id = t.user_type_id
WHERE c.object_id = object_id(@table_Name)

SET @InserCols =
(
select distinct
(
select sc.NAME + ','
FROM sys.tables st
inner join sys.columns sc
ON st.object_id = sc.object_id
WHERE st.NAME = @table_Name
for xml path(''), type
).value('.', 'NVARCHAR(MAX)')
)
SELECT @InserCols = LEFT(@InserCols, Len(@InserCols) - 1)

SET @Inserparam =
(
select distinct
(
select '@' + sc.NAME + ','
FROM sys.tables st
inner join sys.columns sc
ON st.object_id = sc.object_id
WHERE st.NAME = @table_Name
for xml path(''), type
).value('.', 'NVARCHAR(MAX)')
)

SELECT @Inserparam = LEFT(@Inserparam, Len(@Inserparam) - 1)

SET @Insertquery = 'insert into ' + @table_Name + ' (' + @InserCols + ')' + ' values ' + ' (' + @Inserparam + ')'

SELECT @Insertquery AS InsertQuery

SET @Update =
(
select distinct
(
select sc.NAME + '= @' + sc.NAME + ','
FROM sys.tables st
inner join sys.columns sc
ON st.object_id = sc.object_id
WHERE st.NAME = @table_Name
for xml path(''), type
).value('.', 'NVARCHAR(MAX)')
)

SELECT @Update = LEFT(@Update, Len(@Update) - 1)

SET @Update = 'Update ' + @table_Name + ' set ' + @Update

SELECT @Update AS UpdateQuery

SET @Selectquery = 'select ' + @InserCols + ' from ' + @table_Name

SELECT @Selectquery AS SelectQuery
END

--Execute the stored procedure and pass your table name as a parameter.
EXECUTE dbo.[USP_QueryCreationSupport] @table_Name = 'settings'

SQL SERVER – Custom TRIM Function

2018-07-08T23:27:00.001-07:00

ALTER FUNCTION [dbo].[fn_Trim](@String NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
BEGIN
RETURN LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@String,CHAR(10),'[]'),CHAR(13),'[]'),char(9),'[]'),CHAR(32),'[]'),'][',''),'[]',CHAR(32))));
END

Get All Primary Key In SQL

2018-07-02T20:58:00.001-07:00

-- Let's get the columns of the Primary key into a CTE
;WITH mycte
AS (SELECT Schema_name(o.schema_id) AS 'Schema',
Object_name(i2.object_id) AS 'TableName',
Stuff((SELECT ',' + Col_name(ic.object_id, ic.column_id)
FROM sys.indexes i1
INNER JOIN sys.index_columns ic
ON i1.object_id = ic.object_id
AND i1.index_id = ic.index_id
WHERE i1.is_primary_key = 1
AND i1.object_id = i2.object_id
AND i1.index_id = i2.index_id
FOR xml path('')), 1, 1, '') AS PK
FROM sys.indexes i2
INNER JOIN sys.objects o
ON i2.object_id = o.object_id
WHERE i2.is_primary_key = 1
AND o.type_desc = 'USER_TABLE')

-- Use this select to get the count, join to the CTE and get the column list

SELECT Schema_name(o.schema_id) AS 'Schema',
Object_name(i.object_id) AS 'TableName',
Count(Col_name(ic.object_id, ic.column_id)) AS 'Primary_Key_Column_Count'
,
mycte.pk AS
'Primary_Key_Columns'
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.object_id = ic.object_id
AND i.index_id = ic.index_id
INNER JOIN sys.objects o
ON i.object_id = o.object_id
INNER JOIN mycte
ON mycte.tablename = Object_name(i.object_id)
WHERE i.is_primary_key = 1
AND o.type_desc = 'USER_TABLE'
GROUP BY Schema_name(o.schema_id),
Object_name(i.object_id),
mycte.pk
ORDER BY 'TableName' ASC

Ajax Call Freezes UI, Locked UI During Ajax Call

2018-03-13T00:32:00.001-07:00

Solutions:
1. Change the logic to use asynchronous ajax calls.

This may require some re-factoring of the code and be time consuming 

but is the best solution.



2. If decided to keep the ajax call synchronous then put this ajax call into 

setTimeout to call it with delay and be able to show the animation of 

any type (e.g. loading spinner) , this can be shown for some short moment 

like 300 milliseconds before the UI will be locked by sync ajax call.



$('#go').click(function() {
    $(this).after('<div id="loading">Loading...</div>');
    setTimeout(function() {
        $.ajax({
            url: '/echo/html/',
            type: 'post',
            async: false,
            data: {
                html: '<p>AJAX DONE</p>',
                delay: 3
            },
            success: function(data) {
                console.log(data);
                $('#go').after(data);
            },
            error: function() {
                alert('Error!');
            },
            complete: function() {
                $('#loading').remove();
            }
        });

    }, 0);
});



help URL http://jsfiddle.net/ambiguous/zLnED/

Truncate Database All Table In SqlServer

2018-02-09T01:10:00.003-08:00

DECLARE @tablas TABLE(tableName nvarchar(100))

INSERT INTO @tablas
SELECT t.TABLE_SCHEMA+ '.'+t.TABLE_NAME FROM INFORMATION_SCHEMA.TABLES T

DECLARE @loopCounter INT
SELECT @loopCounter = 0
SELECT @loopCounter = COUNT(*) FROM INFORMATION_SCHEMA.TABLES

WHILE @loopCounter>0 
BEGIN
    SELECT TOP 1 @tableName = tableName FROM @tablas 
    DECLARE @sql NVARCHAR(500) = ''

    SET @sql = @sql +'Truncate table  '+ @tableName
    EXECUTE (@sql)
    SELECT @sql
    SET @loopCounter = @loopCounter-1   
    DELETE TOP (1) @tablas 
END

Return Newly Inserted Row In Sql Server

2018-01-31T02:51:00.002-08:00

CREATE TABLE #TT
(
ID uniqueidentifier NOT NULL
DEFAULT newID(),
name varchar(20),
AreaCode INT
)

INSERT INTO #TT
(
Name,
AreaCode
)
OUTPUT INSERTED.ID,
INSERTED.Name,
INSERTED.AreaCode
SELECT 'SS',
1
UNION ALL
SELECT 'SS1',
2

DROP TABLE #TT

Recompile All Table, Stored Procedures and Function In SQL Server

2018-01-31T02:31:00.000-08:00

CREATE PROCEDURE [dbo].[spEXECsp_RECOMPILE]
AS
BEGIN
DECLARE @TableName varchar(128)
DECLARE @OwnerName varchar(128)
DECLARE @CMD1 varchar(8000)
DECLARE @TableListLoop int
DECLARE @TableListTable table
(
UIDTableList int IDENTITY(1, 1),
OwnerName varchar(128),
TableName varchar(128)
)

INSERT INTO @TableListTable
(
OwnerName,
TableName
)
SELECT u.[Name],
o.[Name]
FROM sys.objects o
INNER JOIN sys.schemas u
ON o.schema_id = u.schema_id
WHERE o.type IN ( 'U', -- table
'P', -- stored procedures
'FN' -- scalar functions
)
ORDER BY u.[Name],
o.[Name]

SELECT @TableListLoop = MAX(UIDTableList)
FROM @TableListTable

WHILE @TableListLoop > 0
BEGIN
SELECT @TableName = TableName,
@OwnerName = OwnerName
FROM @TableListTable
WHERE UIDTableList = @TableListLoop

SELECT @CMD1 = 'EXEC sp_recompile ' + '[' + @OwnerName + '.' + @TableName + ']' + char(13)

--PRINT @CMD1
EXECUTE (@CMD1)

SELECT @TableListLoop = @TableListLoop - 1
END

SELECT *
FROM @TableListTable

SET NOCOUNT OFF

END
------------------------------------------------------
--To run used
EXECUTE [dbo].spEXECsp_RECOMPILE

Custom serial number key generator in C# .NET

2018-01-25T04:13:00.000-08:00

Create one class named RandomSNKGenerator

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

namespace AppCode

{

public enum SNKeyLength

{

SN16 = 16, SN20 = 20, SN24 = 24, SN28 = 28, SN32 = 32

}
/// <summary>
/// Supports Only 12 Digit
/// </summary>

public enum SNKeyNumLength

{

SN4 = 4, SN8 = 8, SN12 = 12

}

public static class RandomSNKGenerator

{

private static string AppendSpecifiedStr(int length, string str, char[] newKey)

{

string

newKeyStr = "";

int k = 0;

for (int i = 0; i < length; i++)

{

for(k = i; k < 4 + i; k++)

{

newKeyStr += newKey[k];

}

if (k == length)

{

break;

}

else

{

i = (k) - 1;

newKeyStr += str;

}

return newKeyStr;

}

public static string GetSerialKeyAlphaNumaric(SNKeyLength keyLength)

{

Guid newguid = Guid.NewGuid();

string randomStr = newguid.ToString("N");

string tracStr = randomStr.Substring(0,(int)keyLength);

tracStr = tracStr.ToUpper();

char[] newKey = tracStr.ToCharArray();

string newSerialNumber = "";

switch (keyLength)

{

case SNKeyLength.SN16:newSerialNumber = AppendSpecifiedStr(16, "-", newKey);

break;

case SNKeyLength.SN20: newSerialNumber = AppendSpecifiedStr(20, "-", newKey);

break;

case SNKeyLength.SN24: newSerialNumber = AppendSpecifiedStr(24, "-", newKey);

break;

case SNKeyLength.SN28: newSerialNumber = AppendSpecifiedStr(28, "-", newKey);

break;

case SNKeyLength.SN32: newSerialNumber = AppendSpecifiedStr(32, "-", newKey);

break;

}

return newSerialNumber;

}

public static string GetSerialKeyNumaric(SNKeyNumLength keyLength)

{

Random rn = new Random();

double sd = Math.Round(rn.NextDouble() * Math.Pow(10, (int)keyLength) + 4);

return sd.ToString().Substring(0, (int)keyLength);

}

------------------------------------------------------------

string serialNumber = RandomSNKGenerator.GetSerialKeyAlphaNumaric(SNKeyLength.SN16);

or if you want to generate random number only :

string serialNumberNumberOnly = RandomSNKGenerator.GetSerialKeyNumaric(SNKeyNumLength.SN12);

string serialNumber = AppCode.RandomSNKGenerator.GetSerialKeyAlphaNumaric(SNKeyLength.SN20);

Add a datarow in datatable at specified index

2018-01-16T01:42:00.001-08:00

DataTable existingDataTable = GetMeDataFromSomeWhere();
//Add a new row to table
DataRow newRow = existingDataTable.NewRow();

newRow["ID"] = 999;
newRow["SomeColumn"] = "Manas Bhardwaj";

existingDataTable.Rows.Add(newRow);

//OR If nedd row add in a specific row

existingDataTable.Rows.InsertAt(dr, 0);

Indian Currency To Words In Sql Server

2017-12-12T23:14:00.000-08:00

CREATE FUNCTION [dbo].[GetNumberToWords] (@intNumberValue INTEGER)
RETURNS VARCHAR(2000)
AS
BEGIN
DECLARE @strNumberString VARCHAR(9)
DECLARE @strReturn VARCHAR(2000)
DECLARE @intUnits SMALLINT

-- Alter table of number groups
DECLARE @tblNumberGroups TABLE
(
Units SMALLINT,
Hundreds SMALLINT,
Tens SMALLINT
)

-- Handle errors and 'quick wins'
IF @intNumberValue IS NULL
RETURN NULL
IF ISNUMERIC(@intNumberValue) = 0
RETURN NULL
IF @intNumberValue = 0
RETURN 'ZERO'

IF @intNumberValue < 0
BEGIN
SET @strReturn = 'MINUS '
SET @intNumberValue = ABS(@intNumberValue)
END

SET @intUnits = 0

-- Populate table of number groups
WHILE (@intNumberValue % 1000) > 0 OR (@intNumberValue / 1000) > 0
BEGIN
INSERT INTO @tblNumberGroups
(
Units,
Hundreds,
Tens
)
VALUES
(@intUnits, (@intNumberValue % 1000) / 100, (@intNumberValue % 1000) % 100)
SELECT @intNumberValue = CAST(@intNumberValue / 1000 AS INTEGER)
SET @intUnits = @intUnits + 1
END

-- Remove last unit added
SET @intUnits = @intUnits - 1

-- Concatenate text number by reading number groups in reverse order
SELECT @strReturn
= ISNULL(@strReturn, ' ')
+ ISNULL(
ISNULL((CASE Hundreds
WHEN 1 THEN
'ONE HUNDRED '
WHEN 2 THEN
'TWO HUNDRED '
WHEN 3 THEN
'THREE HUNDRED '
WHEN 4 THEN
'FOUR HUNDRED '
WHEN 5 THEN
'FIVE HUNDRED '
WHEN 6 THEN
'SIX HUNDRED '
WHEN 7 THEN
'SEVEN HUNDRED '
WHEN 8 THEN
'EIGHT HUNDRED '
WHEN 9 THEN
'NINE HUNDRED '
END
),
' '
) + CASE
WHEN (
Hundreds > 0
OR Units < @intUnits
)
AND Tens > 0 THEN
' AND '
ELSE
' '
END + ISNULL( (CASE Tens / 10
WHEN 2 THEN
'TWENTY '
WHEN 3 THEN
'THIRTY '
WHEN 4 THEN
'FORTY '
WHEN 5 THEN
'FIFTY '
WHEN 6 THEN
'SIXTY '
WHEN 7 THEN
'SEVENTY '
WHEN 8 THEN
'EIGHTY '
WHEN 9 THEN
'NINETY '
END
),
' '
) + ISNULL( (CASE Tens
WHEN 10 THEN
'TEN '
WHEN 11 THEN
'ELEVEN '
WHEN 12 THEN
'TWELVE '
WHEN 13 THEN
'THIRTEEN '
WHEN 14 THEN
'FOURTEEN '
WHEN 15 THEN
'FIFTEEN '
WHEN 16 THEN
'SIXTEEN '
WHEN 17 THEN
'SEVENTEEN '
WHEN 18 THEN
'EIGHTEEN '
WHEN 19 THEN
'NINETEEN '
END
),
' '
)
+ COALESCE(
CASE
WHEN Tens % 10 = 1
AND Tens / 10 <> 1 THEN
'ONE '
END,
CASE
WHEN Tens % 10 = 2
AND Tens / 10 <> 1 THEN
'TWO '
END,
CASE
WHEN Tens % 10 = 3
AND Tens / 10 <> 1 THEN
'THREE '
END,
CASE
WHEN Tens % 10 = 4
AND Tens / 10 <> 1 THEN
'FOUR '
END,
CASE
WHEN Tens % 10 = 5
AND Tens / 10 <> 1 THEN
'FIVE '
END,
CASE
WHEN Tens % 10 = 6
AND Tens / 10 <> 1 THEN
'SIX '
END,
CASE
WHEN Tens % 10 = 7
AND Tens / 10 <> 1 THEN
'SEVEN '
END,
CASE
WHEN Tens % 10 = 8
AND Tens / 10 <> 1 THEN
'EIGHT '
END,
CASE
WHEN Tens % 10 = 9
AND Tens / 10 <> 1 THEN
'NINE '
END, ' '
)
+ COALESCE(
CASE
WHEN Units = 1
AND (
Hundreds > 0
OR Tens > 0
) THEN
'THOUSAND '
END,
CASE
WHEN Units = 2
AND (
Hundreds > 0
OR Tens > 0
) THEN
'MILLION '
END,
CASE
WHEN Units = 3
AND (
Hundreds > 0
OR Tens > 0
) THEN
'BILLION '
END,
CASE
WHEN Units = 4
AND (
Hundreds > 0
OR Tens > 0
) THEN
'TRILLION '
END, ' '),
' '
)
FROM @tblNumberGroups
ORDER BY units DESC

-- Get rid of all the spaces
WHILE CHARINDEX(' ', @strReturn) > 0
BEGIN
SET @strReturn = REPLACE(@strReturn, ' ', ' ')
END

SET @strReturn = LTRIM(RTRIM(@strReturn))

RETURN @strReturn
END

GO
----------------------------------********************************----------------------------------------

CREATE FUNCTION [dbo].[GetNumericToRupees] (@RUPEES AS DECIMAL(30, 2))
RETURNS VARCHAR(2000)
AS
BEGIN
DECLARE @INNTBL_01 TABLE
(
RECNO INT IDENTITY(1, 1),
MTEXT NVARCHAR(50)
)

DECLARE @INNTBL_02 TABLE
(
RECNO INT IDENTITY(1, 1),
MTEXT NVARCHAR(50)
)

DECLARE @AMOUNT NUMERIC(38, 0)

DECLARE @PAISE AS INT

SET @AMOUNT = FLOOR(@RUPEES)

SET @PAISE = CAST(CONVERT(DECIMAL(10, 0), (@RUPEES % 1) * 100) AS INT)

--STEP 2:

--NOW ADD UP THE SALT AND PEPPER ONTO TABLE I.E. INSERT THE WORDS INTO IT

INSERT INTO @INNTBL_01
SELECT 'One '

INSERT INTO @INNTBL_01
SELECT 'Two '

INSERT INTO @INNTBL_01
SELECT 'Three '

INSERT INTO @INNTBL_01
SELECT 'Four '

INSERT INTO @INNTBL_01
SELECT 'Five '

INSERT INTO @INNTBL_01
SELECT 'Six '

INSERT INTO @INNTBL_01
SELECT 'Seven '

INSERT INTO @INNTBL_01
SELECT 'Eight '

INSERT INTO @INNTBL_01
SELECT 'Nine '

INSERT INTO @INNTBL_01
SELECT 'Ten '

INSERT INTO @INNTBL_01
SELECT 'Eleven '

INSERT INTO @INNTBL_01
SELECT 'Twelve '

INSERT INTO @INNTBL_01
SELECT 'Thirteen '

INSERT INTO @INNTBL_01
SELECT 'Fourteen '

INSERT INTO @INNTBL_01
SELECT 'Fifteen '

INSERT INTO @INNTBL_01
SELECT 'Sixteen '

INSERT INTO @INNTBL_01
SELECT 'Seventeen '

INSERT INTO @INNTBL_01
SELECT 'Eighteen '

INSERT INTO @INNTBL_01
SELECT 'Nineteen '

INSERT INTO @INNTBL_01
SELECT 'Twenty '

-- SIMILARY, INSERT THE MULTIPLES

INSERT INTO @INNTBL_02
SELECT 'Ten '

INSERT INTO @INNTBL_02
SELECT 'Twenty '

INSERT INTO @INNTBL_02
SELECT 'Thirty '

INSERT INTO @INNTBL_02
SELECT 'Forty '

INSERT INTO @INNTBL_02
SELECT 'Fifty '

INSERT INTO @INNTBL_02
SELECT 'Sixty '

INSERT INTO @INNTBL_02
SELECT 'Seventy '

INSERT INTO @INNTBL_02
SELECT 'Eighty '

INSERT INTO @INNTBL_02
SELECT 'Ninety '

--STEP 3:

----CHECK FOR THE LIMIT OF THE AMOUNT I.E. WHAT IS THE PLACE VALUE OF DIGITS -- LACS, THOUSANDS OR HUNDREDS

DECLARE @WORD VARCHAR(2000)

SELECT @WORD = ''

DECLARE @M_AMT01 BigInt,
@M_AMT02 BigInt

IF @AMOUNT >= 10000000
BEGIN

SET @M_AMT01 = @AMOUNT

SELECT @AMOUNT = (@AMOUNT % 10000000)

SET @M_AMT01 = (@M_AMT01 - @AMOUNT) / 10000000

DECLARE @WORD1 VARCHAR(300)

SET @WORD1 = ''

IF @M_AMT01 <= 20
AND @M_AMT01 <> 0
BEGIN

SET @WORD1 =
(
SELECT @WORD1 + MTEXT FROM @INNTBL_01 WHERE RECNO = @M_AMT01
)

END

SET @WORD = dbo.GetNumberToWords(@M_AMT01) + ' Crore '

END

IF @AMOUNT < 10000000
AND @AMOUNT >= 100000
BEGIN

SET @M_AMT01 = @AMOUNT

SELECT @AMOUNT = (@AMOUNT % 100000)

SET @M_AMT01 = (@M_AMT01 - @AMOUNT) / 100000

SET @WORD1 = ''

IF @M_AMT01 < 100
AND @M_AMT01 > 20
BEGIN

SET @M_AMT02 = @M_AMT01

SET @M_AMT01 = (@M_AMT01 % 10)

SET @M_AMT02 = (@M_AMT02 - @M_AMT01) / 10

SET @WORD1 =
(
SELECT @WORD1 + MTEXT FROM @INNTBL_02 WHERE RECNO = @M_AMT02
)

END

IF @M_AMT01 <= 20
AND @M_AMT01 <> 0
BEGIN

SET @WORD1 =
(
SELECT @WORD1 + MTEXT FROM @INNTBL_01 WHERE RECNO = @M_AMT01
)

END

SET @WORD = @WORD + @WORD1 + 'Lakh '

END

IF @AMOUNT < 100000
AND @AMOUNT >= 1000
BEGIN

SET @M_AMT01 = @AMOUNT

SET @AMOUNT = (@AMOUNT % 1000)

SET @M_AMT01 = (@M_AMT01 - @AMOUNT) / 1000

SET @WORD1 = ''

IF @M_AMT01 < 100
AND @M_AMT01 > 20
BEGIN

SET @M_AMT02 = @M_AMT01

SET @M_AMT01 = (@M_AMT01 % 10)

SET @M_AMT02 = (@M_AMT02 - @M_AMT01) / 10

SET @WORD1 =
(
SELECT @WORD1 + MTEXT + '' FROM @INNTBL_02 WHERE RECNO = @M_AMT02
)

END

IF @M_AMT01 <= 20
AND @M_AMT01 <> 0
BEGIN

SET @WORD1 =
(
SELECT @WORD1 + MTEXT + '' FROM @INNTBL_01 WHERE RECNO = @M_AMT01
)

END

SET @WORD = @WORD + @WORD1 + 'Thousand '

END

IF @AMOUNT < 1000
AND @AMOUNT >= 100
BEGIN

SET @M_AMT01 = @AMOUNT

SET @AMOUNT = (@AMOUNT % 100)

SET @M_AMT01 = (@M_AMT01 - @AMOUNT) / 100

SET @WORD =
(
SELECT @WORD + ' ' + MTEXT + 'Hundred ' FROM @INNTBL_01 WHERE RECNO = @M_AMT01
)

END

IF @AMOUNT < 100
AND @AMOUNT > 20
BEGIN

SET @M_AMT01 = @AMOUNT

SET @AMOUNT = (@AMOUNT % 10)

SET @M_AMT01 = (@M_AMT01 - @AMOUNT) / 10

SET @WORD =
(
SELECT @WORD + MTEXT + '' FROM @INNTBL_02 WHERE RECNO = @M_AMT01
)

END

IF @AMOUNT <= 20
AND @AMOUNT >= 1
BEGIN

SET @WORD =
(
SELECT @WORD + MTEXT + '' FROM @INNTBL_01 WHERE RECNO = @AMOUNT
)

END

SET @WORD = @WORD + ' '

-- STEP 4:

-- CALCULATE THE PAISE ALSO.

DECLARE @WORDP VARCHAR(300)

SET @WORDP = ''

IF @PAISE <> 0
BEGIN

IF @PAISE < 100
AND @PAISE > 20
BEGIN

DECLARE @PAISE_01 VARCHAR(300)

SET @PAISE_01 = @PAISE

SET @PAISE = (@PAISE % 10)

SET @PAISE_01 = (@PAISE_01 - @PAISE) / 10

SET @WORDP =
(
SELECT @WORDP + MTEXT FROM @INNTBL_02 WHERE RECNO = @PAISE_01
)

END

IF @PAISE <= 20
AND @PAISE >= 1
BEGIN

SET @WORDP =
(
SELECT @WORDP + MTEXT FROM @INNTBL_01 WHERE RECNO = @PAISE
)

END

SET @WORD = @WORD + 'and ' + @WORDP + 'paisa'

END

IF @AMOUNT >= 1000000000
BEGIN

SET @WORD = ''

END

SELECT @WORD = REPLACE(@WORD COLLATE Latin1_General_BIN, ' ', ' ');

Return UPPER(@WORD + ' ONLY')

END
------------------------------------
Go

SELECT dbo.GetNumericToRupees(625)

Generate Class Object From SQL Table

2017-07-07T05:06:00.003-07:00

declare @TableName sysname = 'TABLE_Name'
declare @result Nvarchar(max) = ''

select @result = @result + '
private ' + ColumnType + ' ' + ' m_' + stuff(replace(ColumnName, '_', ''), 1, 1, lower(left(ColumnName, 1))) + ';'
from
(
select
replace(col.name, ' ', '_') ColumnName,
column_id,
case typ.name
when 'bigint' then 'long'
when 'binary' then 'byte[]'
when 'bit' then 'bool'
when 'char' then 'string'
when 'date' then 'DateTime'
when 'datetime' then 'DateTime'
when 'datetime2' then 'DateTime'
when 'datetimeoffset' then 'DateTimeOffset'
when 'decimal' then 'decimal'
when 'float' then 'float'
when 'image' then 'byte[]'
when 'int' then 'int'
when 'money' then 'decimal'
when 'nchar' then 'char'
when 'ntext' then 'string'
when 'numeric' then 'decimal'
when 'nvarchar' then 'string'
when 'real' then 'double'
when 'smalldatetime' then 'DateTime'
when 'smallint' then 'short'
when 'smallmoney' then 'decimal'
when 'text' then 'string'
when 'time' then 'TimeSpan'
when 'timestamp' then 'DateTime'
when 'tinyint' then 'byte'
when 'uniqueidentifier' then 'Guid'
when 'varbinary' then 'byte[]'
when 'varchar' then 'string'
else 'UNKNOWN_' + typ.name
end ColumnType
from sys.columns col
join sys.types typ on
col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id
where object_id = object_id(@TableName)
) t
order by column_id

SET @result = @result + '
'

select @result = @result + '
public ' + ColumnType + ' ' + ColumnName + ' { get { return m_' + stuff(replace(ColumnName, '_', ''), 1, 1, lower(left(ColumnName, 1))) + ';} set {m_' + stuff(replace(ColumnName, '_', ''), 1, 1, lower(left(ColumnName, 1))) + ' = value;} }' from
(
select
replace(col.name, ' ', '_') ColumnName,
column_id,
case typ.name
when 'bigint' then 'long'
when 'binary' then 'byte[]'
when 'bit' then 'bool'
when 'char' then 'string'
when 'date' then 'DateTime'
when 'datetime' then 'DateTime'
when 'datetime2' then 'DateTime'
when 'datetimeoffset' then 'DateTimeOffset'
when 'decimal' then 'decimal'
when 'float' then 'float'
when 'image' then 'byte[]'
when 'int' then 'int'
when 'money' then 'decimal'
when 'nchar' then 'char'
when 'ntext' then 'string'
when 'numeric' then 'decimal'
when 'nvarchar' then 'string'
when 'real' then 'double'
when 'smalldatetime' then 'DateTime'
when 'smallint' then 'short'
when 'smallmoney' then 'decimal'
when 'text' then 'string'
when 'time' then 'TimeSpan'
when 'timestamp' then 'DateTime'
when 'tinyint' then 'byte'
when 'uniqueidentifier' then 'Guid'
when 'varbinary' then 'byte[]'
when 'varchar' then 'string'
else 'UNKNOWN_' + typ.name
end ColumnType
from sys.columns col
join sys.types typ on
col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id
where object_id = object_id(@TableName)
) t
order by column_id

SELECT CONVERT(NTEXT,@result)

How To Track Database Changes

2017-03-31T03:59:00.001-07:00

CREATE DATABASE Audit

USE [Audit]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[RunDbAudit]

(
@DataBaseName VARCHAR(100)
)

AS
BEGIN

DECLARE @AuditDbName VARCHAR(100);
DECLARE @BaseTable VARCHAR(100);
DECLARE @SqlCmd NVARCHAR(MAX);
DECLARE @ParamDef NVARCHAR(MAX);

SET @BaseTable = '__DB__'+ @DataBaseName ;
SET @AuditDbName = DB_NAME();

DECLARE @AuditExcludedObjects NVARCHAR(MAX);
SET @AuditExcludedObjects = 'fn_diagramobjects,sysdiagrams,'+@BaseTable+'';

DECLARE @BaseTableCount INT;

SET @SqlCmd = 'SELECT @BaseTableCount = COUNT(*) FROM '+@AuditDbName+'.INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG = @AuditDbName
AND TABLE_NAME = @BaseTable';
SET @ParamDef = '@BaseTable VARCHAR(100), @DataBaseName VARCHAR(100), @AuditDbName VARCHAR(100), @BaseTableCount INT OUTPUT';

EXECUTE sp_executesql @SqlCmd,
@ParamDef,
@BaseTable = @BaseTable, @DataBaseName = @DataBaseName, @AuditDbName = @AuditDbName, @BaseTableCount = @BaseTableCount OUTPUT;

--SELECT @SqlCmd;
--SELECT @BaseTableCount;

IF (@BaseTableCount = 0)
BEGIN

SET @SqlCmd = '
SELECT
@DataBaseName AS [DB_NAME]
, name AS [OBJECT_NAME]
, object_id AS [OBJECT_ID]
, type AS [OBJECT_TYPE]
, type_desc AS [TYPE_DESC]
, create_date AS [CREATED_ON]
, modify_date AS [MODIFIED_ON]
, CONVERT(DATETIME,NULL) AS [DELETED_ON]
, CONVERT(BIT,0) AS [MASTER_TABLE]
, CONVERT(BIT,0) AS [AUDIT_EXCLUDED]
, CONVERT(VARCHAR(MAX),'''') AS [CHANGE_INFO]
, CONVERT(DECIMAL(18,2), 1.00) AS [VERSION]
INTO [dbo].['+@BaseTable+']
FROM ['+@DataBaseName+'].sys.objects WHERE type in (''P'',''U'',''FN'',''V'');

UPDATE [dbo].['+@BaseTable+'] SET [AUDIT_EXCLUDED] = 1 WHERE [OBJECT_NAME] IN (@AuditExcludedObjects)
';

SET @ParamDef = '@AuditExcludedObjects NVARCHAR(MAX), @DataBaseName VARCHAR(100)';
EXECUTE sp_executesql @SqlCmd,
@ParamDef,
@AuditExcludedObjects = @AuditExcludedObjects, @DataBaseName = @DataBaseName
;

END
ELSE
BEGIN

------------------------------------------------------------ Audit--------------------------------------------------------------

SET @SqlCmd = '

DECLARE @PrevVer DECIMAL(18,2);
DECLARE @PrevVerDt DATETIME;

DECLARE @NextVer DECIMAL(18,2);
DECLARE @NextVerDt DATETIME;

SET @NextVerDt = GETDATE();
SET @PrevVerDt = (SELECT MAX(MODIFIED_ON) FROM [dbo].['+@BaseTable+'] WHERE [AUDIT_EXCLUDED] = 0);
SET @PrevVer = (SELECT MAX([VERSION]) FROM [dbo].['+@BaseTable+'] WHERE [AUDIT_EXCLUDED] = 0);

SET @NextVer = @PrevVer + 0.01;

IF OBJECT_ID(''tempdb..#temp__DB_OBJECT'') IS NOT NULL DROP TABLE #temp__DB_OBJECT

SELECT
@DataBaseName AS [DB_NAME]
, name AS [OBJECT_NAME]
, object_id AS [OBJECT_ID]
, type AS [OBJECT_TYPE]
, type_desc AS [TYPE_DESC]
, create_date AS [CREATED_ON]
, modify_date AS [MODIFIED_ON]
, CONVERT(DATETIME,NULL) AS [DELETED_ON]
, CONVERT(BIT,0) AS [MASTER_TABLE]
, CONVERT(BIT,0) AS [AUDIT_EXCLUDED]
, CONVERT(VARCHAR(MAX),'''') AS [CHANGE_INFO]
, @NextVer AS [VERSION]
INTO #temp__DB_OBJECT
FROM ['+@DataBaseName+'].sys.objects WHERE type in (''P'',''U'',''FN'',''V'')
AND name NOT IN ((SELECT [OBJECT_NAME] FROM [dbo].['+@BaseTable+'] WHERE [AUDIT_EXCLUDED]=1))

UPDATE P
SET P.MODIFIED_ON = tmp.MODIFIED_ON, P.[VERSION] = tmp.[VERSION]
FROM [dbo].['+@BaseTable+'] P
INNER JOIN #temp__DB_OBJECT tmp ON
(P.[OBJECT_NAME] = tmp.[OBJECT_NAME] AND P.TYPE_DESC = tmp.TYPE_DESC)
WHERE P.AUDIT_EXCLUDED = 0
AND P.MODIFIED_ON <> tmp.MODIFIED_ON

INSERT INTO [dbo].['+@BaseTable+']
SELECT t.* FROM #temp__DB_OBJECT t
LEFT OUTER JOIN [dbo].['+@BaseTable+'] DO ON t.[OBJECT_ID] = DO.[OBJECT_ID]
WHERE DO.[OBJECT_NAME] IS NULL

UPDATE P
SET P.DELETED_ON = @NextVerDt, P.[VERSION] = @NextVer
FROM [dbo].['+@BaseTable+'] P
INNER JOIN (
SELECT
DO.[OBJECT_NAME],
DO.[OBJECT_ID],
DO.[OBJECT_TYPE],
DO.[TYPE_DESC]
FROM [dbo].['+@BaseTable+'] DO
LEFT OUTER JOIN #temp__DB_OBJECT t ON t.[OBJECT_ID] = DO.[OBJECT_ID]
WHERE t.[OBJECT_NAME] IS NULL AND DO.AUDIT_EXCLUDED = 0
) Q ON (P.[OBJECT_NAME] = Q.[OBJECT_NAME] AND P.OBJECT_TYPE = Q.OBJECT_TYPE AND P.TYPE_DESC = Q.TYPE_DESC)
WHERE P.[DELETED_ON] IS NULL AND P.AUDIT_EXCLUDED = 0

DECLARE @CurrentVer DECIMAL(18,2);
SET @CurrentVer = (SELECT MAX(VERSION) FROM [dbo].['+@BaseTable+'] WHERE AUDIT_EXCLUDED = 0)

SELECT CASE WHEN @CurrentVer = @NextVer
THEN ''Current Database Version : '' + CONVERT(VARCHAR,@CurrentVer) + '' :: Changes info given below.''
ELSE ''Current Database Version : '' + CONVERT(VARCHAR,@CurrentVer) + '' :: Changes in last version given below''
END AS [Message];

SELECT * FROM [dbo].['+@BaseTable+'] WHERE [VERSION] = @CurrentVer;

IF OBJECT_ID(''tempdb..#temp__DB_OBJECT'') IS NOT NULL DROP TABLE #temp__DB_OBJECT
';

SET @ParamDef = '@DataBaseName VARCHAR(100)';

EXECUTE sp_executesql @SqlCmd
,@ParamDef
,@DataBaseName = @DataBaseName;

------------------------------------------------------------/Audit--------------------------------------------------------------
END

END

exec RunDbAudit 'DATABASE NAME' --RUN 2 TIMES FOR 1st RUN

Convert HTML Tables To DataSet In C#

2017-03-09T21:28:00.001-08:00

private DataSet ConvertHTMLTablesToDataSet(string HTML) {
// Declarations
DataSet ds = new DataSet();
DataTable dt = null;
DataRow dr = null;
DataColumn dc = null;
string TableExpression = "<table[^>]*>(.*?)</table>";
string HeaderExpression = "<th[^>]*>(.*?)</th>";
string RowExpression = "<tr[^>]*>(.*?)</tr>";
string ColumnExpression = "<td[^>]*>(.*?)</td>";
bool HeadersExist = false;
int iCurrentColumn = 0;
int iCurrentRow = 0;
// Get a match for all the tables in the HTML
MatchCollection Tables = Regex.Matches(HTML, TableExpression, RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.IgnoreCase);
// Loop through each table element
foreach(Match Table in Tables) {
// Reset the current row counter and the header flag
iCurrentRow = 0;
HeadersExist = false;
// Add a new table to the DataSet
dt = new DataTable();
//Create the relevant amount of columns for this table (use the headers if they exist, otherwise use default names)
// if (Table.Value.Contains("<th"))
if (Table.Value.Contains("<th")) {
// Set the HeadersExist flag
HeadersExist = true;
// Get a match for all the rows in the table
MatchCollection Headers = Regex.Matches(Table.Value, HeaderExpression, RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.IgnoreCase);
// Loop through each header element
foreach(Match Header in Headers) {
dt.Columns.Add(Header.Groups[1].ToString());
}
} else {
for (int iColumns = 1; iColumns <= Regex.Matches(Regex.Matches(Regex.Matches(Table.Value, TableExpression, RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.IgnoreCase)[0].ToString(), RowExpression, RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.IgnoreCase)[0].ToString(), ColumnExpression, RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.IgnoreCase).Count; iColumns++) {
dt.Columns.Add("Column " + iColumns);
}
}
//Get a match for all the rows in the table
MatchCollection Rows = Regex.Matches(Table.Value, RowExpression, RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.IgnoreCase);
// Loop through each row element
foreach(Match Row in Rows) {
// Only loop through the row if it isn't a header row
if (!(iCurrentRow == 0 && HeadersExist)) {
// Create a new row and reset the current column counter
dr = dt.NewRow();
iCurrentColumn = 0;
// Get a match for all the columns in the row
MatchCollection Columns = Regex.Matches(Row.Value, ColumnExpression, RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.IgnoreCase);
// Loop through each column element
foreach(Match Column in Columns) {
// Add the value to the DataRow
dr[iCurrentColumn] = Column.Groups[1].ToString();
// Increase the current column
iCurrentColumn++;
}
// Add the DataRow to the DataTable
dt.Rows.Add(dr);
}
// Increase the current row counter
iCurrentRow++;
}
// Add the DataTable to the DataSet
ds.Tables.Add(dt);
}
return ds;
}

Find out the SQL Database Actual Size and used size

2017-01-31T02:16:00.001-08:00

set nocount on
select
[FileSizeMB] =
convert(numeric(10,2),sum(round(a.size/128.,2))),
[UsedSpaceMB] =
convert(numeric(10,2),sum(round(fileproperty( a.name,'SpaceUsed')/128.,2))) ,
[UnusedSpaceMB] =
convert(numeric(10,2),sum(round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2))) ,
[Type] =
case when a.groupid is null then '' when a.groupid = 0 then 'Log' else 'Data' end,
[DBFileName] = isnull(a.name,'*** Total for all files ***')
from
sysfiles a
group by
groupid,
a.name
with rollup
having
a.groupid is null or
a.name is not null
order by
case when a.groupid is null then 99 when a.groupid = 0 then 0 else 1 end,
a.groupid,
case when a.name is null then 99 else 0 end,
a.name

Query to produce time pattern with 30 minutes interval

2017-01-05T01:57:00.001-08:00

DECLARE @lTimeinterval INT = 30;

WITH mycte
AS (SELECT 1 AS Time_ID,
RIGHT(CONVERT(VARCHAR(16), Dateadd(day, Datediff(day, 0, Getdate()), 0), 120), 5) AS Time_Slot
UNION ALL
SELECT Time_ID + 1,
RIGHT(CONVERT(VARCHAR(16), Dateadd(minute, Time_ID * @lTimeinterval, Dateadd(day, Datediff(day, 0, Getdate()), 0)), 120), 5)
FROM mycte
WHERE Dateadd(minute, Time_ID * @lTimeinterval, Dateadd(day, Datediff(day, 0, Getdate()), 0)) < Dateadd(day, Datediff(day, 0, Getdate()) + 1, 0))
SELECT Time_Slot
FROM mycte

Data Encryption and Decryption in SQL Server

2016-12-29T03:21:00.001-08:00

--Step 1: Create a Master Key
SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101

CREATE MASTER KEY ENCRYPTION
BY PASSWORD ='Password!2';

GO
--Step 2: Create Certificate
CREATE CERTIFICATE Cert_Password
ENCRYPTION BY PASSWORD = 'Password!2'
WITH SUBJECT = 'Password protection',
EXPIRY_DATE = '12/31/2099';

--Step 3: Create Symmetric Key
CREATE SYMMETRIC KEY Sym_password
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE Cert_Password;

SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 256

GO
--Step 4: Encrypt Data

OPEN SYMMETRIC KEY Sym_password
DECRYPTION BY CERTIFICATE Cert_Password WITH PASSWORD = 'Password!2';

DECLARE @tempSecurity AS TABLE (UserID VARCHAR(200), Password NVARCHAR(1000))
INSERT INTO @tempSecurity (UserID, Password)
VALUES ('schinna',ENCRYPTBYKEY(KEY_GUID(N'Sym_password'), 'PASSWORD'))
CLOSE SYMMETRIC KEY Sym_password;

SELECT * FROM @tempSecurity

--Step 5: Decrypt Data
OPEN SYMMETRIC KEY Sym_password
DECRYPTION BY CERTIFICATE Cert_Password WITH PASSWORD = 'Password!2';
SELECT UserID, CAST(DECRYPTBYKEY([Password]) as varchar(200))
FROM @tempSecurity
CLOSE SYMMETRIC KEY Sym_password;

SELECT * FROM @tempSecurity

GO
--DROP ALL THE KEY
DROP SYMMETRIC KEY Sym_password
GO
DROP CERTIFICATE Cert_Password
GO
DROP MASTER KEY

Proper case in sql server

2016-11-15T04:04:00.000-08:00

CREATE Function [dbo].[fn_get_ProperCase](@InputString as varchar(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Index INT
DECLARE @Char CHAR(1)
DECLARE @OutputString VARCHAR(255)

SET @OutputString = LOWER(@InputString)
SET @Index = 2
SET @OutputString = STUFF(@OutputString, 1, 1,UPPER(SUBSTRING(@InputString,1,1)))

WHILE @Index <= LEN(@InputString)
BEGIN
SET @Char = SUBSTRING(@InputString, @Index, 1)

IF @Char IN (' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&','''','(')
IF @Index + 1 <= LEN(@InputString)
BEGIN
IF @Char != '''' OR UPPER(SUBSTRING(@InputString, @Index + 1, 1)) != 'S'
SET @OutputString = STUFF(@OutputString, @Index + 1, 1,UPPER(SUBSTRING(@InputString, @Index + 1, 1)))
END
SET @Index = @Index + 1
END

RETURN ISNULL(@OutputString,'')

END

Force browsers to get latest js and css files in asp.net

2016-09-23T04:30:00.002-07:00

1. Crete a class named FileUtility

using System.Web;

public class FileUtility
{
public static string SetJsVersion(HttpContext context, string filename)
{
string version = GetJsFileVersion(context, filename);
return filename + version;
}

private static string GetJsFileVersion(HttpContext context, string filename)
{
if (context.Cache[filename] == null)
{
string filePhysicalPath = context.Server.MapPath(filename);

string version = "?v=" + GetFileLastModifiedDateTime(context, filePhysicalPath, "yyyyMMddhhmmss");

return version;
}
else
{
return string.Empty;
}
}

public static string GetFileLastModifiedDateTime(HttpContext context, string filePath, string dateFormat)
{
return new System.IO.FileInfo(filePath).LastWriteTime.ToString(dateFormat);
}
}

2.Rendered HTML

For Css : <link href="<%=FileUtility.SetJsVersion(Context,"css/bootstrap.css")%>" rel="stylesheet" />

For js : <script type="text/javascript" src='<%=FileUtility.SetJsVersion(Context,"/js/json.js") %>'></script>

SQL Server thousands separator for a column

2016-09-23T03:18:00.001-07:00

SELECT CONVERT(VARCHAR,CONVERT(MONEY, 234234234),1)

Filter or Search ASP.Net DropDownList Using JavaScript

2016-08-30T02:31:00.001-07:00

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="DemoChosen.aspx.cs" Inherits="Demo.DemoChosen" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.2.0/css/bootstrap.min.css" rel="stylesheet" />
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.2.0/js/bootstrap.min.js"></script>

<link href="http://cdnjs.cloudflare.com/ajax/libs/chosen/1.1.0/chosen.min.css" rel="stylesheet" />
<script src="http://cdnjs.cloudflare.com/ajax/libs/chosen/1.1.0/chosen.jquery.min.js"></script>

<title></title>

<style>
.panel-heading
{
cursor: pointer;
}

select.form-control + .chosen-container.chosen-container-single .chosen-single
{
display: block;
width: 100%;
height: 34px;
padding: 6px 12px;
font-size: 14px;
line-height: 1.428571429;
color: #555;
vertical-align: middle;
background-color: #fff;
border: 1px solid #ccc;
border-radius: 4px;
-webkit-box-shadow: inset 0 1px 1px rgba(0,0,0,0.075);
box-shadow: inset 0 1px 1px rgba(0,0,0,0.075);
-webkit-transition: border-color ease-in-out .15s,box-shadow ease-in-out .15s;
transition: border-color ease-in-out .15s,box-shadow ease-in-out .15s;
background-image: none;
}

select.form-control + .chosen-container.chosen-container-single .chosen-single div
{
top: 4px;
color: #000;
}

select.form-control + .chosen-container .chosen-drop
{
background-color: #FFF;
border: 1px solid #CCC;
border: 1px solid rgba(0, 0, 0, 0.15);
border-radius: 4px;
-webkit-box-shadow: 0 6px 12px rgba(0, 0, 0, 0.175);
box-shadow: 0 6px 12px rgba(0, 0, 0, 0.175);
background-clip: padding-box;
margin: 2px 0 0;
}

select.form-control + .chosen-container .chosen-search input[type=text]
{
display: block;
width: 100%;
height: 34px;
padding: 6px 12px;
font-size: 14px;
line-height: 1.428571429;
color: #555;
vertical-align: middle;
background-color: #FFF;
border: 1px solid #CCC;
border-radius: 4px;
-webkit-box-shadow: inset 0 1px 1px rgba(0, 0, 0, 0.075);
box-shadow: inset 0 1px 1px rgba(0, 0, 0, 0.075);
-webkit-transition: border-color ease-in-out 0.15s, box-shadow ease-in-out 0.15s;
transition: border-color ease-in-out 0.15s, box-shadow ease-in-out 0.15s;
background-image: none;
}

select.form-control + .chosen-container .chosen-results
{
margin: 2px 0 0;
padding: 5px 0;
font-size: 14px;
list-style: none;
background-color: #fff;
margin-bottom: 5px;
}

select.form-control + .chosen-container .chosen-results li,
select.form-control + .chosen-container .chosen-results li.active-result
{
display: block;
padding: 3px 20px;
clear: both;
font-weight: normal;
line-height: 1.428571429;
color: #333;
white-space: nowrap;
background-image: none;
}

select.form-control + .chosen-container .chosen-results li:hover,
select.form-control + .chosen-container .chosen-results li.active-result:hover,
select.form-control + .chosen-container .chosen-results li.highlighted
{
color: #FFF;
text-decoration: none;
background-color: #428BCA;
background-image: none;
}

select.form-control + .chosen-container-multi .chosen-choices
{
display: block;
width: 100%;
min-height: 34px;
padding: 6px;
font-size: 14px;
line-height: 1.428571429;
color: #555;
vertical-align: middle;
background-color: #FFF;
border: 1px solid #CCC;
border-radius: 4px;
-webkit-box-shadow: inset 0 1px 1px rgba(0, 0, 0, 0.075);
box-shadow: inset 0 1px 1px rgba(0, 0, 0, 0.075);
-webkit-transition: border-color ease-in-out 0.15s, box-shadow ease-in-out 0.15s;
transition: border-color ease-in-out 0.15s, box-shadow ease-in-out 0.15s;
background-image: none;
}

select.form-control + .chosen-container-multi .chosen-choices li.search-field input[type="text"]
{
height: auto;
padding: 5px 0;
}

select.form-control + .chosen-container-multi .chosen-choices li.search-choice
{
background-image: none;
padding: 3px 24px 3px 5px;
margin: 0 6px 0 0;
font-size: 14px;
font-weight: normal;
line-height: 1.428571429;
text-align: center;
white-space: nowrap;
vertical-align: middle;
cursor: pointer;
border: 1px solid #ccc;
border-radius: 4px;
color: #333;
background-color: #FFF;
border-color: #CCC;
}

select.form-control + .chosen-container-multi .chosen-choices li.search-choice .search-choice-close
{
top: 8px;
right: 6px;
}

select.form-control + .chosen-container-multi.chosen-container-active .chosen-choices,
select.form-control + .chosen-container.chosen-container-single.chosen-container-active .chosen-single,
select.form-control + .chosen-container .chosen-search input[type=text]:focus
{
border-color: #66AFE9;
outline: 0;
-webkit-box-shadow: inset 0 1px 1px rgba(0, 0, 0, 0.075),0 0 8px rgba(102, 175, 233, 0.6);
box-shadow: inset 0 1px 1px rgba(0, 0, 0, 0.075),0 0 8px rgba(102, 175, 233, 0.6);
}

select.form-control + .chosen-container-multi .chosen-results li.result-selected
{
display: list-item;
color: #ccc;
cursor: default;
background-color: white;
}
</style>

</head>
<body>
<form id="form1" runat="server">
<div>
<div class="form-group">
<label for="id_tipo_equipo" class="control-label">Tipo</label>
<select name="id_tipo_equipo" id="id_tipo_equipo" class="form-control chosen" data-placeholder="[Tipo]">
<option value="1">Access Point</option>
<option value="2">Adaptador</option>
<option value="3">Adaptador DVI a VGA</option>
<option value="4">Adaptador Electrico para Discos</option>
<option value="5">Aire Acondicionado</option>
<option value="6">Amplificador</option>
<option value="7">Antena</option>
<option value="8">Antena Poe</option>
<option value="9">Bases de Monitor</option>
<option value="10">Bluetooth</option>
<option value="11">Cable de Poder</option>
<option value="12">Cable USB</option>
<option value="13">Cable UTP</option>
<option value="14">Cables Conectores</option>
<option value="15">Camara de Fotos</option>
<option value="16">Camara de Video</option>
</select>

</div>

<button onclick=" return getVal();">Get Selected Value</button>
</div>
</form>
</body>
<script>
$(document).ready(function () {
// $('select').chosen({ width: '100%' });
var config = {
'#id_tipo_equipo': {},
'.chosen-select-deselect': { allow_single_deselect: true },
'.chosen-select-no-single': { disable_search_threshold: 10 },
'.chosen-select-no-results': { no_results_text: 'Oops, nothing found!' },
'.chosen-select-width': { width: "95%" }
}

for (var selector in config) {
$(selector).chosen(config[selector]);
}
});

function getVal() {
alert(document.getElementById("id_tipo_equipo").value);
return false;
}
</script>
</html>